Real-Time CDP Insights data model B2B Edition
- 主题:
- 功能板
创建对象:
- 开发人员
- 用户
The Real-Time CDP Insights data model for the B2B Edition exposes the data models and SQL that power the insights for account profiles. You can customize these SQL query templates to create Real-Time CDP reports for your B2B marketing and key performance indicator (KPI) use cases. These insights can then be used as custom widgets for your dashboards.
Prerequisites
This guide requires a working understanding of custom dashboards. Read the documentation on how to create a custom dashboard before continuing with this guide.
Real-Time CDP B2B insight reports and use cases
Real-Time CDP B2B reporting provides insights into your account profiles data and the relationship between accounts and opportunities. The following star schema models were developed to answer a variety of common marketing use cases and each data model can support several use cases.
Account Profile model
The Account Profile model is comprised of eight datasets:
adwh_dim_industry
adwh_dim_account_name
adwh_dim_geo
adwh_dim_account_type
adwh_fact_account
account_revenue_employee
The diagram below displays the relevant data fields in each dataset, their data type, and the foreign keys linking the datasets together.
The new accounts by industry use case
The logic used for the New accounts by industry insight returns the top five industries according to their number of account profiles and their relative size to each other. See the New accounts By Industry widget documentation for more information.
The SQL that generates the New accounts by industry insight is seen in the collapsible section below.
WITH RankedIndustries AS (
SELECT
i.industry,
SUM(f.counts) AS total_accounts,
ROW_NUMBER() OVER (ORDER BY SUM(f.counts) DESC) AS industry_rank
FROM
adwh_fact_account f
INNER JOIN adwh_dim_industry i ON f.industry_id = i.industry_id
WHERE f.accounts_created_date between UPPER(COALESCE('$START_DATE', '')) and UPPER(COALESCE('$END_DATE', ''))
GROUP BY
i.industry
)
SELECT
CASE
WHEN industry_rank <= 5 THEN industry
ELSE 'Others'
END AS industry_group,
SUM(total_accounts) AS total_accounts
FROM
RankedIndustries
GROUP BY
CASE
WHEN industry_rank <= 5 THEN industry
ELSE 'Others'
END
ORDER BY
total_accounts DESC
LIMIT 5000;
The New accounts by type use case
The logic used for the New accounts by type insight returns the numerical breakdown of accounts by their type. This insight can help to guide business strategy and operations, including resource allocation or marketing strategies. See the New accounts by type widget documentation for more information.
The SQL that generates the New accounts by type insight is seen in the collapsible section below.
SELECT t.account_type,
Sum(f.counts) AS account_count
FROM adwh_fact_account f
JOIN adwh_dim_account_type t
ON f.account_type_id = t.account_type_id
WHERE accounts_created_date BETWEEN Upper(Coalesce('$START_DATE', '')) AND
Upper(
Coalesce('$END_DATE', ''))
GROUP BY t.account_type
LIMIT 5000;
Opportunity model
The Opportunity model is comprised of seven datasets:
adwh_dim_opportunity_stage
adwh_dim_person_role
adwh_dim_opportunity_source_type
adwh_dim_opportunity_name
adwh_fact_opportunity
adwh_opportunity_amount
adwh_fact_opportunity_person
The diagram below displays the relevant data fields in each dataset.