CaliberMind Entity Relations and System Tables
Data Model Overview
CaliberMind's data model is designed to be fluid, open, yet structured. Regardless of which CRM, Marketing Automation Tool, or priority data source, data are mapped and transformed to work with your B2B business. At its heart CaliberMind is a Customer Data Platform (CDP). That is:
- A data warehouse and data lake to store all types, shapes, and volumes of marketing data.
- A reporting & segmentation tool set.
- A workflow engine to bi-directionally sync data to and from the CDP.
One of the benefits of centralizing analytics and segmentation in CaliberMind is that we automate maintenance of creating a "marketing data lake". For example, an important question is: What was an account's engagement score last month vs this month? What is our pipeline month-over-month, etc.
This is only possible if you copy certain data as a snapshot each day-- which this model enables.
It also allows interoperability. Our customers can leverage our best practice reporting templates on top of our system tables and snapshots regardless of custom objects, Salesforce, Marketo, HubSpot, SAP... or even Google Sheets.
One system integrated to CaliberMind such as Salesforce.com looks like this:
CaliberMind Entity Relationship Diagram (ERD)
At a higher-level, many systems can integrate into this data backbone and it can be used to drive decisions and actions. For example you can import marketing campaign budgets from your CMO's spreadsheet and the Google AdWords API, and match ids in that spreadsheet to campaign ids in your CRM via `v_campaign`. This can be used in our Analytics Module to calculate ROI for each campaign.
Each CaliberMind view is prefixed with v_. Admin user can modify system views in Settings >> System Views. Since system views are often complex, we copy the data from each view into CaliberMind system tables prefixed with `cm_`. This allows reporting to be much faster. There are cases when it makes sense to use 1 vs the other in queries, and lists.
CaliberMind System Views & Tables
There are two categories of system views: parents and children. Parent system views end up as cm tables (see above). Children roll up date into parents.
Union of data sets in system parents
A UNION just mean adding data with the same columns vertically, to a data table. For example a parent v_event is a master view of events from other systems.
(data from system 1)
UNION / APPEND
(data from system 2)
UNION / APPEND
(data from system 3)
This concept can be applied to statistical models too, such as in v_attribution:
(data from model 1)
UNION / APPEND
(data from model 2)
UNION / APPEND
(data from model 3)
v_lead -- > cm_lead
A copy of your CRM leads table, however adds in Lead-to-Account matching.
v_opportunity -- > cm_opportunity
A copy of your CRM opportunity table, however adds in custom calculations for amount and probability.
v_person_company -- > cm_person_company
This is the primary profile table in CaliberMind. It's usually loosely based on CRM Accounts, Contacts, Leads, and Opportunities.
FIELD / COLUMN | DATA DESCRIPTION |
id | person'd id in CRM (lead of contact) |
createddate | created date of person |
lastmodifieddate | last modified date of person |
name | full name of person |
firstname | first name of person |
lastname | last name of person |
title | job title |
title_clean | Normalized title (e.g "Vice President" becomes "VP") |
job_level | Inferred org level based on title_clean. |
department | Inferred dept based on title_clean. |
parent_id | Accounts without parents are their own parents. Leads matched to an account show the account id. |
company_id | Account or Lead Id |
website | Normalized domain |
company_name | Account or Lead name |
industry | Account or Lead industry |
industry_short | Normalized Industry (e.g. "specialty stores" in would be "Retail"). |
is_customer | Does this person have a closed opportunity. |
customer_status | Taken from the from your CRM Account Type field (by default -- can be changed). |
customer_date | Date of first closed/won opp. |
customer_initial_value | Value of first closed/won opp. |
customer_ltv | Value of all won opps at the parent and all children accounts. |
customer_ltv_rank | Ranks 1 - to - n where 1st is the parent + children accounts with the most valuable as #1. |
account_initial_value | Value of first won opp at an account. |
account_ltv | Value of all won opps at an account. |
account_ltv_rank | Ranks 1 - to -n where 1 is the p. |
pipeline | Adds open opps at the account. |
weighted_pipeline | Adds open opps at an account X the probability it will close. |
v_event -- > cm_event
A unionized view / table of all events from all systems and their respective unique ids. This is the foundational timeline used for other models like scoring and attribution.
FIELD / COLUMN | DATA DESCRIPTION |
id | event id (ex marketo activity id, campaignmember id, task id, etc.) |
event_datetime | time of the touch / event |
event_name | campaign name or event name from table being imported |
campaign_id | when appropriate has campaign id (to join to v_campaign) |
company | parent id |
company_id | lead or account id |
company_domain | business domain associated with account |
person | email address of person related if applicable |
person_id | lead or contact id of person |
person_domain | domain from email address (personal or business) |
event_system | Ex) "Salesforce", "HubSpot", "Zendesk" etc. |
event_type | [Optional] if there's a "type" field on the data coming in. |
event_detail | [Optional] if there's a description or referring url field in the event |
v_scoring -- > cm_scoring
A unionized view/table of scoring model data, taking into account. Out of the box there are several scoring model templates in your instance of CaliberMind (Inbound90, Inbound180, etc.):
- persona of touch
- age of touch
- categories of touch (type, class, system, details, etc)
v_attribution -- > cm_attribution
Underlying table of revenue attribution, based on cm_event. All models are stored in the this table as union of child views. For example this table is:
FIELD / COLUMN | DATA DESCRIPTION |
model_name | Ex) "Even365" (or custom name for model used), other models will get added to this table too. |
model_style | Even (implies that touchpoint credit is split evenly) |
lookback | cm_event.event_datetime > cm_person_company.customer_date - XXX (only shows events that happened X days before customer_date |
company | we'll add company_id to the cm_attribution table soon to get around this, some tables such as cm_event and cm_engagement already have this |
customer_date | cm_person_company.customer_date |
attrib date | date of attribution (typically date of an opportunity close) |
event_date | cm_event.event_datetime |
person | cm_event.person |
days_to_customer | cm_person_company.customer_date - cm_event.event_datetime |
order | order of cm_event.event_datetime (1st touch, 2nd touch, etc) |
type | cm_event.event_type (can be from Marketo or SFDC) |
total_value | cm_person_company.customer_initial_value |
influence | 100 / count(cm_event.id) |
value | cm_person_company.customer_initial_value / count (cm_event.id) |
source | cm_event.event_system |
event_id | cm_event.id |
event_detail | cm_event.event_detail |
v_engagement --> cm_engagement --> cm_engagement___snapshot
A point in time aggregation of engagement score by Account. This table is snapshotted so as to spot trends in the data.
FIELD / COLUMN | DATA DESCRIPTION |
model_name | The friendly name available for filter. Ex) Inbound90, Inbound365, etc. |
lookback | The number of days in the data set (ex - "90" would be 90 days rolling from the present day. |
company | Defaults to parent_id on the cm_person_company table. |
company_id | The lead or account id. |
account_last_engagement | Last Inbound touch at the account. |
account_engaged_contacts | Number of contacts with scored touches in the lookback (e.g. last 90 days. |
account_touches | Number of touches on the account |
account_touches_rank | The account with most touches is #1 |
account_score | The sum of all scores at a company_id |
account_score_rank | The account with the highest score is #1 |
account_score_index | Percentile of score, a score of 88 is top 12% (out of 100) |
account_ltv | Sum of won opps at an account |
pipeline | Sum of open opps at an account |
weighted_pipeline | Sum of open opps at an account X probability of close |
account_status | Pulled from cm_person_company. |
company_last_engagement | Last touch at account, parent or any related lead. |
company_engaged_contacts | Number of contacts at an account, parent or any other leads related that are engaged. |
company_touches | Total touches at an account, parent, and any other leads. |
company_touches_rank | Top ranked account, lead, and parent account, #1 is best. |
company_score | Sum of scores at an account, parent, and any related leads. |
company_score_rank | Top scoring account, lead, and parent accounts rank, #1 is best. |
company_score_index | 1-100 percentile of company_score. |
company_status | Status of parent account |
v_engagement___trend -- > cm_engagement___trend
Calculates day-over-day, week-over-week, and month-over-month comparisons of engagement score by account.
v_funnel -- > cm_funnel --> cm_funnel___snapshot
Summarizes and snapshots each account or person_id in each stage of your funnel model created in the Funnel Builder.
