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:

  1. A data warehouse and data lake to store all types, shapes, and volumes of marketing data.
  2. A reporting & segmentation tool set.
  3. 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.

When building operational lists that need the most current data, use the views (v_), when building reports or bulk process where time-sensitivity is less important, use the tables (cm_) since they'll load faster and be less intensive on your data base.

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

email

email

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.


How did we do?


Powered by HelpDocs

Powered by HelpDocs