The following content is still under review by the Marketing and Sales data SSOT group (internal link)
This page describes facts and dimensions for sales funnel metrics and analysis. These models provide common dimensions for our crm data, such as
dim_crm_persons, which is a union of Salesforce leads and contacts. As of now this includes the following objects (hyperlinks are to the detailed documentation for each object):
These facts represent conversion events in the Sales funnel. For mor information on Marketing Qualification please refer to the Marketing Operations handbook.
With the exception of some the data in
dim_crm_accounts, which is Orange, this data should be anonymized/masked in the case of
dim_crm_persons or generally only relevant to our internal business operational performance in the Sales Funnel. If a user needs access to the personal data related to
dim_crm_persons they would need to submit an access request for approval. Such data is out of scope for this sales funnel program and so is not covered here.
The following example queries are meant to demonstrate how to use these facts and dimensions. The Single Source of Truth for Metrics and KPIs is in Sisense
To get MQLs you use the
fct_crm_marketing_qualification table, which containts all MQLs on leads and contacts. Since you can have multiple converted leads per contact, you'll want to select the first or last MQL using
MAX() as in:
WITH first_mql AS ( SELECT fct_crm_marketing_qualification.crm_person_id, min(date_id) AS first_mql FROM analytics_staging.fct_crm_marketing_qualification GROUP BY 1 ) SELECT dim_dates.first_day_of_month, count(*) mqls FROM first_mql INNER JOIN analytics_staging.dim_dates ON first_mql.first_mql = dim_dates.date_id WHERE fiscal_year = 2021 GROUP BY 1 ORDER BY 1
You can also join this to
dim_crm_persons do add any of the person dimension columns to that data.
WITH mql AS ( SELECT crm_person_id, MIN(event_timestamp) AS first_mql, MAX(event_timestamp) AS last_mql FROM analytics_staging.fct_crm_marketing_qualification GROUP BY 1 ) SELECT first_mql, dim_crm_persons.* FROM analytics_staging.dim_crm_persons INNER JOIN mql ON dim_crm_persons.crm_person_id = mql.crm_person_id
Sales Accepted Opportunities can be calculated using
fct_crm_opportunities and either the
is_sao or the
is_sdr_sao flag/boolean (
is_sdr_sao is a subset of
is_sao). With this fact table, however, you can also join directly to
dim_crm_accounts to add any columns from that dimension as well.
WITH sao AS ( SELECT crm_account_id, -- using crm_person_id is also available min(sales_accepted_date_id) AS first_sao, max(sales_accepted_date_id) AS last_sao FROM analytics_staging.fct_crm_opportunities WHERE is_sao = TRUE GROUP BY 1 ) SELECT first_sao_dates.first_day_of_month, count(*) saos FROM sao INNER JOIN dim_dates first_sao_dates ON sao.first_sao = first_sao_dates.date_id GROUP BY 1 ORDER BY 1
Or if you wanted all SAOs and not just the first or last per account or person you could get that easily with:
SELECT dim_dates.first_day_of_month , count(*) FROM analytics_staging.fct_crm_opportunities INNER JOIN dim_dates ON fct_crm_opportunities.sales_accepted_date_id = dim_dates.date_id WHERE is_sao = TRUE GROUP BY 1 ORDER BY 1
Both of these facts can be joined to the
dim_crm_persons dimension table to get conversion data:
WITH first_sdr_sao AS ( -- get SAOs SELECT crm_person_id, min(sales_accepted_date) AS first_sao FROM analytics_staging.fct_crm_opportunities WHERE is_sdr_sao = TRUE GROUP BY 1 ), first_mql AS ( -- get first mql SELECT crm_person_id, date_trunc(day, min(event_timestamp))::date AS first_mql -- match the SAO type FROM analytics_staging.fct_crm_marketing_qualification GROUP BY 1 ) SELECT first_mql.first_mql, first_sao.first_sao, datediff(day,first_mql, first_sao) AS mql_to_sao, * FROM analytics_staging.dim_crm_persons crm_persons INNER JOIN first_mql ON first_mql.crm_person_id = crm_persons.crm_person_id LEFT JOIN first_sao ON first_sao.crm_person_id = crm_persons.crm_person_id