Insights - Data Structure

Updated 1 year ago by Elena Yurkova

Data Structure

Before creating custom dashboards it's imperative that a designer understands the business process tracked in their CRM and the data structure that supports this business logic on the database level. Business logic may vary for every single database and must be approached individually.

In this article, we focus on the data structure, i.e. the world behind the dashboards.

Relationship types in the data model

To understand how different fields are connected in Insights, let's discuss the variety of possible connections. Maximizer CRM is a highly customizable software. Organization with fuzzy business processes and overwhelming amount of User-Defined Fields may set their users and dashboard designers for a failure. To avoid unnecessary frustration, please don't skip this part.

1:1 relationship

In most cases, connection between fields is straight forward. For example, each opportunity can have only one close date and only one opportunity leader. In this scenario, both sides of the relationship have unique values for every row. This is an example of one to one relationships between the fields (1:1). All fields in the main tables and in the User-Defined Fields tables follow this 1:1 relationship rule.

1:M relationship

Depending on the business process, one opportunity can belong to multiple Categories or have multiple products attached which creates one-to-many (1:M) relationships.

In this scenario, one side of the relationship will contain unique values for every row, but the other side of the relationship will contain duplicate values for any or all of the corresponding values in the first table.

Why it is important to distinguish this type of relationship?

Example: we have three opportunities in progress for the same amount, $1000. Opportunity 1 and 3 sell only one product, while opportunity 2 considers all three products.

Opportunity

Product

Revenue

Opportunity 1

A

1000

Opportunity 2

A,B,C

1000

Opportunity 3

C

1000

Due to Opportunity 2 and no $ break-down per product, if you create a column chart, e.g. 'Revenue in progress by product', you get misleading results:

In reality, this chart says that Product A is a part of the Opportunities that may result in total of $2000 or simply that Product B is a less popular one. But this chart could be interpreted that you have $5000 in progress, not $3000.

Solution:

1) If tracking sales by product is important in your company, the only way to do it properly is by changing the process so one opportunity has only one product attached (1:1 relationship). Otherwise, Products/Services will serve as an informational field you can't report on. However, this is not always applicable.

2) Create a separate widget, 'Revenue in progress' without including 'Products/Services' field. Rename the current widget to 'Most popular products in progress' and count number of Opportunities instead of $.

3) Also, if 'Products/Services' is used as multi-select field in your company, e.g. one opportunity can have multiple products selected from the drop down, don't use this field as a dashboard filter.

M:M relationship or pitfall of table fields

In databases, a Many-to-Many relationship exists when the value in each field used to create a relationship between tables is included multiple times in each table.

To translate to CRM user language, let's say we think of analyzing the Opportunities from the example above by 'External Resources Required' (a table user-defined field with multi-select values). A possible widget example: show me 'Product' all 'External Resources Required'.

In this scenario, both sides of the relationship can hold duplicated values, causing excessive calculations for every query run against it.

Needless to say, it is very unlikely that building this chart (a chart based on at least two multi-select fields) will result in a meaningful visualization. Furthermore, it might work now, but it is not sustainable in the long run.

Solution:

Rethink your approach and divide this widget into reliable pieces.

Summary

When you add data to create a widget or add a dashboard filter that effects all widgets, evaluate possible outcomes. If you have only 1:1 connections, you don't need to give your dashboard a second thought. If there is a risk of 1:M, take precautions. If there is a risk of M:M stop and come up with a different approach.

To avoid wrong results, evaluate if the fields you need to use might be connected by 1:M and M:M relationships before building your dashboard.
Never use multi-select table fields as a dashboard filter, it may lead to unreliable results.

Fields & modules supported in Insights

Data model in Insights reflects business logic of the main application and comes with additional benefits.

Modules

Maximizer modules currently supported by Insights are Leads, Opportunities, Address Book (divided to Contacts and Company/Individual) and Customer Service cases. Each module is a data island protected by data security.

Company/Individual and Contacts are now separated and connected. This data model adjustment allows designers to access company information (Basic fields and UDFs) on the contact level and build cross level widgets.

Also, since Customer Service doesn't have separate security settings, it is now connected to Company/Individual.

High level data model:

  • You can only create widgets based on the fields from one island. If you picked fields from a disconnected table, you receive an error message.

Example: error message, when I've picked 'Country' from 'Leads' and 'City/Town' from 'Company/Individual' table by mistake:

Data islands

Each data island consists of the tables that start with the module name. You can use any fields that belong to the same island to create a widget as long as you are aware of 1:M and M:M relationships.

Leads island has a main 'Leads' table, 'Leads User-Defined Fields' and a separate 'Leads User-Defined Table Fields'.

Also, Leads island comes with the benefit of Opportunity fields as a part of the main table. These fields allow you to analyze ROI of your marketing by displaying Opportunity Status and Revenue for the leads.

Opportunity island has a main 'Opportunities' table and 'Opportunity User-Defined Fields' table. Other tables are table fields. The reason why they are outside the main table is because of their multi-select nature that can be a cause of 1:M and M:M relationships (i.e. they can cause duplicates in aggregations if not handled accordingly).

Address Book island is the most complex one but allows you to do reports that you can't do in the main product, such as see company level data on the contact level.

Customer Service island has a similar structure.


How did we do?