- Dashboards overview
- Dashboard access permissions
- Dashboards inventory
- Standard dashboards
- Download dashboards to PDF
- SQL insights: query pro mode
- Available dashboards
- Available Insights
- Customize dashboards
- Insights use cases
- Available data models
- Application integrations
- Query dashboard datasets
- View insight SQL
- Platform release notes
Account Profile insights
Account profiles are used to consolidate account information from various sources, including multiple marketing channels and organizational systems. This unified view enables a comprehensive understanding of customer accounts, enhancing B2B marketing campaigns. The insights derived from the analysis of your data model make your Adobe Real-Time CDP B2B data more accessible, understandable, and impactful for decision-making.
With access to the SQL that powers your insights, you can better understand your B2B data and generate your own highly customized reusable insights to further explore your customer account information. Transform your raw data into new actionable insights by using the existing Real-Time CDP data model SQL as inspiration to create queries for your unique business needs.
The following insights are all available for you to use as part of the Account Profiles dashboard or a custom dashboard. See the customization overview for instructions on how to customize your dashboard or create and edit new widgets in the widget library and user-defined dashboard.
Account profiles added
Questions answered by this insight:
- How many account profiles have been added over a given period?
Select to reveal the SQL that generates this insight
WITH accounts_by_mm_dd AS
(
SELECT d.date_key,
COALESCE(Sum(a.counts), 0) AS account_counts
FROM adwh_b2b_date d
LEFT JOIN adwh_fact_account a
ON d.date_key = a.accounts_created_date
WHERE d.date_key BETWEEN Upper(COALESCE('$START_DATE', '')) AND Upper(COALESCE('$END_DATE', ''))
GROUP BY d.date_key)
SELECT date_key,
account_counts
FROM accounts_by_mm_dd
ORDER BY date_key limit 5000;
New accounts by industry
Questions answered by this insight:
- What are the top five industries that the account profiles belong to?
Select to reveal the SQL that generates this insight
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;
New accounts by type
Questions answered by this insight:
- What is the count of accounts by their type?
Select to reveal the SQL that generates this insight
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;
Opportunities added
Questions answered by this insight:
- How many opportunities have been added over a given period?
Select to reveal the SQL that generates this insight
SELECT d.date_key,
Coalesce(Sum(o.counts), 0) AS opportunity_counts
FROM adwh_b2b_date d
LEFT JOIN adwh_fact_opportunity o
ON d.date_key = o.opportunities_created_date
WHERE d.date_key BETWEEN Upper(Coalesce('$START_DATE', '')) AND
Upper(Coalesce('$END_DATE', ''))
GROUP BY d.date_key
ORDER BY d.date_key
LIMIT 5000;
New opportunities by person role
Questions answered by this insight:
- What is the relative size and count of the various roles in an opportunity?
Select to reveal the SQL that generates this insight
SELECT p.person_role,
Sum(f.counts) AS opportunity_counts
FROM adwh_fact_opportunity_person f
JOIN adwh_dim_person_role p
ON f.person_role_id = p.person_role_id
WHERE f.opportunity_person_created_date BETWEEN
Upper(Coalesce('$START_DATE', '')) AND Upper(Coalesce('$END_DATE', ''))
GROUP BY p.person_role
LIMIT 5000;
New opportunities by revenue
Questions answered by this insight:
- What are the top 20 opportunities ranked by their revenue (in USD)?
Select to reveal the SQL that generates this insight
WITH ranked_opportunities AS
(
SELECT n.opportunity_name,
a.expected_revenue,
t.source_type,
Row_number() OVER (ORDER BY a.expected_revenue DESC) AS rank
FROM adwh_opportunity_amount a
INNER JOIN adwh_dim_opportunity_name n
ON a.name_id = n.name_id
INNER JOIN adwh_dim_opportunity_source_type t
ON n.source_type_id = t.source_type_id
WHERE a.opportunity_created_date BETWEEN Upper(COALESCE('$START_DATE', '')) AND Upper(COALESCE('$END_DATE', ''))
AND a.isclosed='false' )
SELECT
CASE
WHEN rank <= 20 THEN opportunity_name
ELSE 'Others'
END AS opportunity_name,
Sum(expected_revenue) AS total_expected_revenue
FROM ranked_opportunities
GROUP BY
CASE
WHEN rank <= 20 THEN opportunity_name
ELSE 'Others'
END,
source_type
ORDER BY total_expected_revenue DESC limit 5000;
New opportunities by status & stage
Questions answered by this insight:
- What open opportunities are there and at which stage of the sales or marketing funnel are they?
- What closed opportunities are there and at which stage of the sales or marketing funnel are they?
Select to reveal the SQL that generates this insight
WITH opportunities_by_isclosed AS
(
SELECT f.isclosed,
Sum(f.counts) AS opportunity_counts,
COALESCE(s.stage, 'null') AS stage
FROM adwh_fact_opportunity f
JOIN adwh_dim_opportunity_stage s
ON f.stage_id = s.stage_id
WHERE opportunities_created_date BETWEEN Upper(COALESCE('$START_DATE', '')) AND Upper(COALESCE('$END_DATE', ''))
GROUP BY f.isclosed,
s.stage)
SELECT
CASE
WHEN isclosed='true' THEN 'Closed'
ELSE 'Open'
END AS opportunity_closed,
stage,
opportunity_counts
FROM opportunities_by_isclosed limit 5000;
New opportunities won
Questions answered by this insight:
- What is the count of opportunities that have been successfully closed or finalized?
Select to reveal the SQL that generates this insight
WITH opportunities_by_iswon AS
(
SELECT iswon,
Sum(counts) AS opportunity_counts
FROM adwh_fact_opportunity
WHERE opportunities_created_date BETWEEN Upper(COALESCE('$START_DATE', '')) AND Upper(COALESCE('$END_DATE', ''))
GROUP BY iswon)
SELECT
CASE
WHEN iswon ='true' THEN 'True'
ELSE 'False'
END AS opportunity_won,
opportunity_counts
FROM opportunities_by_iswon limit 5000;
Opportunities won (line graph)
Questions answered by this insight:
- How many opportunities have been successfully closed or finalized (won) over a given period?
Select to reveal the SQL that generates this insight
WITH opportunities_won_counts AS
(
SELECT opportunities_created_date,
Sum(counts) AS opportunities_counts
FROM adwh_fact_opportunity
WHERE iswon='true'
AND opportunities_created_date BETWEEN Upper(COALESCE('$START_DATE', '')) AND Upper(COALESCE('$END_DATE', ''))
GROUP BY opportunities_created_date)
SELECT d.date_key,
COALESCE(o.opportunities_counts, 0) AS opportunity_won_counts
FROM adwh_b2b_date d
LEFT JOIN opportunities_won_counts o
ON d.date_key = o.opportunities_created_date
WHERE d.date_key BETWEEN Upper(COALESCE('$START_DATE', '')) AND Upper(COALESCE('$END_DATE', ''))
ORDER BY d.date_key limit 5000;
Customers per Account Overview
NOTE
The Customers per Account Overview chart includes three drill-through insights: Customers per Account Detail, Opportunities per Account Overview, and Opportunities per Account Detail. These drill-throughs provide more granular insights, breaking down customer and opportunity counts by categories (such as direct and indirect customers) and ranges (like customer and opportunity count bands). These charts are unaffected by any global date filters you may have set.
Questions answered by this insight:
- What is the distribution of accounts based on whether they have direct or indirect customers?
Select to reveal the SQL that generates this insight
WITH LatestDate AS (SELECT MAX(inserted_date) AS max_inserted_date FROM adwh_b2b_account_person_association),
CategorizedData AS (
SELECT CASE
WHEN is_direct = 'true' AND person_count = 0 THEN 'Accounts without Direct Customers'
WHEN is_direct = 'false' AND person_count = 0 THEN 'Accounts without Indirect Customers'
WHEN is_direct = 'true' AND person_count > 0 THEN 'Accounts with Direct Customers'
WHEN is_direct = 'false' AND person_count > 0 THEN 'Accounts with Indirect Customers'
END AS Account_Category,
account_count
FROM adwh_b2b_account_person_association
WHERE inserted_date = (SELECT max_inserted_date FROM LatestDate)
),
AggregatedData AS (
SELECT Account_Category, SUM(account_count) AS Accounts
FROM CategorizedData
GROUP BY Account_Category
),
AllCategories AS (
SELECT 'Accounts without Direct Customers' AS Account_Category
UNION ALL SELECT 'Accounts without Indirect Customers'
UNION ALL SELECT 'Accounts with Direct Customers'
UNION ALL SELECT 'Accounts with Indirect Customers'
)
SELECT ac.Account_Category AS Account_Category, COALESCE(ad.Accounts, 0) AS Accounts
FROM AllCategories ac
LEFT JOIN AggregatedData ad ON ac.Account_Category = ad.Account_Category
ORDER BY ac.Account_Category;
Customers per Account Detail
NOTE
This insight is unaffected by global date filters.
Questions answered by this insight:
- How many accounts have different ranges of direct or indirect customers?
Select to reveal the SQL that generates this insight
WITH customer_ranges AS (
SELECT 'Direct Customer' AS customer_type, '1-10 Customers' AS person_range
UNION ALL
SELECT 'Direct Customer', '11-100 Customers'
UNION ALL
SELECT 'Direct Customer', '101-1000 Customers'
UNION ALL
SELECT 'Direct Customer', '1000+ Customers'
UNION ALL
SELECT 'Indirect Customer', '1-10 Customers'
UNION ALL
SELECT 'Indirect Customer', '11-100 Customers'
UNION ALL
SELECT 'Indirect Customer', '101-1000 Customers'
UNION ALL
SELECT 'Indirect Customer', '1000+ Customers'
)
SELECT
cr.customer_type,
cr.person_range,
COALESCE(SUM(ap.account_count), 0) AS Accounts
FROM customer_ranges cr
LEFT JOIN (
SELECT
CASE
WHEN is_direct = 'true' THEN 'Direct Customer'
ELSE 'Indirect Customer'
END AS customer_type,
CASE
WHEN person_count BETWEEN 1 AND 10 THEN '1-10 Customers'
WHEN person_count BETWEEN 11 AND 100 THEN '11-100 Customers'
WHEN person_count BETWEEN 101 AND 1000 THEN '101-1000 Customers'
WHEN person_count > 1000 THEN '1000+ Customers'
END AS person_range,
SUM(account_count) AS account_count
FROM adwh_b2b_account_person_association
WHERE inserted_date = (SELECT MAX(inserted_date) FROM adwh_b2b_account_person_association)
GROUP BY
CASE
WHEN is_direct = 'true' THEN 'Direct Customer'
ELSE 'Indirect Customer'
END,
CASE
WHEN person_count BETWEEN 1 AND 10 THEN '1-10 Customers'
WHEN person_count BETWEEN 11 AND 100 THEN '11-100 Customers'
WHEN person_count BETWEEN 101 AND 1000 THEN '101-1000 Customers'
WHEN person_count > 1000 THEN '1000+ Customers'
END
) ap ON cr.customer_type = ap.customer_type AND cr.person_range = ap.person_range
GROUP BY cr.customer_type, cr.person_range
ORDER BY cr.customer_type,
CASE cr.person_range
WHEN '1-10 Customers' THEN 1
WHEN '11-100 Customers' THEN 2
WHEN '101-1000 Customers' THEN 3
WHEN '1000+ Customers' THEN 4
END;
Opportunities per Account Overview
NOTE
This insight is unaffected by global date filters.
Questions answered by this insight:
- What is the distribution of accounts based on whether they have associated opportunities?
Select to reveal the SQL that generates this insight
WITH LatestDate AS (
SELECT MAX(inserted_date) AS max_inserted_date
FROM adwh_b2b_account_opportunity_association
),
CategorizedData AS (
SELECT
CASE
WHEN opportunity_count = 0 THEN 'Accounts without Opportunities'
WHEN opportunity_count > 0 THEN 'Accounts with Opportunities'
END AS Opportunity_Category,
account_count
FROM adwh_b2b_account_opportunity_association
WHERE inserted_date = (SELECT max_inserted_date FROM LatestDate)
),
AggregatedData AS (
SELECT
Opportunity_Category,
SUM(account_count) AS Accounts
FROM CategorizedData
GROUP BY Opportunity_Category
),
AllCategories AS (
SELECT 'Accounts without Opportunities' AS Opportunity_Category
UNION ALL
SELECT 'Accounts with Opportunities'
)
SELECT
ac.Opportunity_Category AS Opportunity_Category,
COALESCE(ad.Accounts, 0) AS Accounts
FROM AllCategories ac
LEFT JOIN AggregatedData ad
ON ac.Opportunity_Category = ad.Opportunity_Category
ORDER BY ac.Opportunity_Category;
Opportunities per Account Detail
NOTE
This insight is unaffected by global date filters.
Questions answered by this insight:
- How many accounts have different ranges of associated opportunities?
Select to reveal the SQL that generates this insight
WITH opportunity_ranges AS (
SELECT '1-10 Opportunities' AS opportunity_range
UNION ALL
SELECT '11-50 Opportunities'
UNION ALL
SELECT '51-100 Opportunities'
UNION ALL
SELECT '100+ Opportunities'
)
SELECT opportunity_ranges.opportunity_range AS OPPORTUNITIES,
COALESCE(SUM(accounts.total_accounts), 0) AS ACCOUNTS
FROM opportunity_ranges
LEFT JOIN (
SELECT
CASE
WHEN opportunity_count BETWEEN 1 AND 10 THEN '1-10 Opportunities'
WHEN opportunity_count BETWEEN 11 AND 50 THEN '11-50 Opportunities'
WHEN opportunity_count BETWEEN 51 AND 100 THEN '51-100 Opportunities'
WHEN opportunity_count > 100 THEN '100+ Opportunities'
END AS opportunity_range,
SUM(account_count) AS total_accounts
FROM adwh_b2b_account_opportunity_association
WHERE inserted_date = (SELECT MAX(inserted_date) FROM adwh_b2b_account_opportunity_association)
AND opportunity_count > 0
GROUP BY
CASE
WHEN opportunity_count BETWEEN 1 AND 10 THEN '1-10 Opportunities'
WHEN opportunity_count BETWEEN 11 AND 50 THEN '11-50 Opportunities'
WHEN opportunity_count BETWEEN 51 AND 100 THEN '51-100 Opportunities'
WHEN opportunity_count > 100 THEN '100+ Opportunities'
END
) AS accounts ON opportunity_ranges.opportunity_range = accounts.opportunity_range
GROUP BY opportunity_ranges.opportunity_range
ORDER BY CASE opportunity_ranges.opportunity_range
WHEN '1-10 Opportunities' THEN 1
WHEN '11-50 Opportunities' THEN 2
WHEN '51-100 Opportunities' THEN 3
WHEN '100+ Opportunities' THEN 4
END;
Next steps
By reading this document, you now understand the SQL that generates account profile dashboard insights and what common questions this analysis solves. You can now edit and iterate on the SQL to generate your own insights. Refer to the Query Pro Mode overview to learn how to generate custom insights with SQL.
You can also read and understand the SQL that generates insights for the Profiles, Audiences, and Destinations dashboards.
Change language
Copyright © 2025 Adobe. All Rights Reserved.
