Audiences insights
Last update: Wed Nov 27 2024 00:00:00 GMT+0000 (Coordinated Universal Time)
The insights derived from the analysis of your data model make your Adobe Real-Time CDP data more accessible, understandable, and impactful for decision-making.
Understand your audience insights by accessing the SQL that powers them, then generate your own insights to further explore the identities and profiles that make up your audiences. 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.
See the View SQL documentation for more information on how to adapt your insights’ SQL directly through the PLatform UI.
The following insights are all available for you to use as part of the Audiences dashboard or a custom user-defined 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.
The following insights are all available for you to use as part of the Audiences dashboard or a custom dashboard.
Audience overlap report audience-overlap-report
Questions answered by this insight:
- What are the top 50 overlapping audiences of a particular filtered audience?
- What are the 50 least overlapping audiences of a particular filtered audience?
- How does the overlapping pattern change for a different filtered audience?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT source_segment_name,
source_segment_id,
overlap_segment_name,
overlap_segment_id,
max(source_segment_audience_count) source_segment_audience_count,
max(overlap_segment_audience_count) overlap_segment_audience_count,
max(overlap_audience_count) overlap_audience_count,
CASE
WHEN (max(source_segment_audience_count) + max(overlap_segment_audience_count) - max(overlap_audience_count)) > 0 THEN (cast(max(overlap_audience_count) AS DECIMAL(18, 2)) / cast((max(source_segment_audience_count) + max(overlap_segment_audience_count) - max(overlap_audience_count)) AS DECIMAL(18, 2))) * 100::DECIMAL(9, 2)
ELSE 100.00
END overlapping_percentage
FROM
(SELECT adwh_fact_profile_overlap_of_segments.Segment1 source_segment_id,
adwh_fact_profile_overlap_of_segments.Segment2 overlap_segment_id,
Sum(count_of_overlap) overlap_audience_count
FROM qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments
WHERE qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.date_key = '2024-01-10'
GROUP BY qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.Segment2 ,
qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.Segment1) a
INNER JOIN
(SELECT sum(count_of_profiles) source_segment_audience_count,
adwh_dim_segments.segment_name source_segment_name,
adwh_fact_profile_by_segment_trendlines.merge_policy_id,
adwh_fact_profile_by_segment_trendlines.segment_Id segment1
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
JOIN qsaccel.profile_agg.adwh_dim_segments ON qsaccel.profile_agg.adwh_dim_segments.segment_id = qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_Id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = '2024-01-10'
GROUP BY qsaccel.profile_agg.adwh_dim_segments.segment_name,
qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id,
qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_id) b ON a.source_segment_id = b.segment1
INNER JOIN
(SELECT sum(count_of_profiles) overlap_segment_audience_count,
adwh_dim_segments.segment_name overlap_segment_name,
adwh_fact_profile_by_segment_trendlines.merge_policy_id,
adwh_fact_profile_by_segment_trendlines.segment_Id segment2
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
JOIN qsaccel.profile_agg.adwh_dim_segments ON adwh_dim_segments.segment_id = adwh_fact_profile_by_segment_trendlines.segment_Id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = '2024-01-10'
GROUP BY qsaccel.profile_agg.adwh_dim_segments.segment_name,
qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id,
qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_id) c ON a.overlap_segment_id = c.segment2
GROUP BY source_segment_name,
source_segment_id,
overlap_segment_name,
overlap_segment_id
ORDER BY overlapping_percentage DESC
LIMIT 5;
|
See the Audience overlap report widget documentation for information on the appearance and functionality of this insight.
Audience overlap audience-overlap
Questions answered by this insight:
- Which profiles are common to both audiences?
- How does the overlap impact engagement or conversion rates?
- How can marketing strategies be tailored for the overlapping segment?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT Sum(overlap_col1) overlap_col1,
Sum(overlap_col2) overlap_col2,
Sum(overlap_count) Overlap_count
FROM
(SELECT 0 overlap_col1,
0 overlap_col2,
sum(count_of_overlap)Overlap_count
FROM qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments
WHERE qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.merge_policy_id = 1133248113
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.date_key = '2024-01-10'
AND ((qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.segment1=1870062812
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.segment2=2080256533)
OR (qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.segment1=2080256533
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.segment2=1870062812))
UNION ALL SELECT sum(count_of_profiles) overlap_col1,
0 overlap_col2,
0 overlap_count
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
LEFT JOIN qsaccel.profile_agg.adwh_dim_segments ON qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_Id = qsaccel.profile_agg.adwh_dim_segments.segment_Id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id = 1133248113
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = '2024-01-10'
AND qsaccel.profile_agg.adwh_dim_segments.segment_Id = 1870062812
UNION ALL SELECT 0 overlap_col1,
sum(count_of_profiles) overlap_col2,
0 Overlap_count
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
JOIN qsaccel.profile_agg.adwh_dim_segments ON qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_Id = qsaccel.profile_agg.adwh_dim_segments.segment_Id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id = 1133248113
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = '2024-01-10'
AND qsaccel.profile_agg.adwh_dim_segments.segment_Id = 2080256533 ) a;
|
See the Audience overlap widget documentation for information on the appearance and functionality of this insight.
Audience size change trend audience-size-change-trend
Questions answered by this insight:
- Are there any significant spikes or dips in audience size within the last 30 days, 90 days, or 12 months?
- How does audience size change during specific days?
- Were there any anomalies or repeating patterns of spikes or dips detected over the past 12 months?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT date_key,
Profiles_added
FROM
(SELECT rn_num,
date_key,
(count_of_profiles-lag(count_of_profiles, 1, 0) over(
ORDER BY date_key))Profiles_added
FROM
(SELECT date_key,
sum(x.count_of_profiles)count_of_profiles,
row_number() OVER (
ORDER BY date_key) rn_num
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines x
INNER JOIN
(SELECT MAX(process_date) last_process_date,
merge_policy_id
FROM qsaccel.profile_agg.adwh_lkup_process_delta_log
WHERE process_name = 'FACT_TABLES_PROCESSING'
AND process_status = 'SUCCESSFUL'
GROUP BY merge_policy_id) y ON x.merge_policy_id = y.merge_policy_id
WHERE segment_id = 1333234510
AND x.date_key >= dateadd(DAY, -30 -1, y.last_process_date)
GROUP BY x.date_key) a)b
WHERE rn_num > 1;
|
See the Audience size change trend widget documentation for information on the appearance and functionality of this insight.
Audience size trend by identity audience-size-trend-by-identity
Questions answered by this insight:
- Is my audience consistently growing, stabilizing, or experiencing fluctuations?
- Is there any specific identity that has spikes or dips in audience growth over time?
- Are there any anomalies in my identity growth over time?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT sum(count_of_profiles) AS identities,
date_key
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines x
INNER JOIN
(SELECT MAX(process_date) last_process_date,
merge_policy_id
FROM qsaccel.profile_agg.adwh_lkup_process_delta_log
WHERE process_name = 'FACT_TABLES_PROCESSING'
AND process_status = 'SUCCESSFUL'
GROUP BY merge_policy_id) y ON x.merge_policy_id = y.merge_policy_id
INNER JOIN qsaccel.profile_agg.adwh_dim_namespaces z ON x.namespace_id = z.namespace_id
AND x.merge_policy_id = z.merge_policy_id
WHERE x.date_key >= dateadd(DAY, -30, y.last_process_date)
AND x.segment_id = 1333234510
AND z.namespace_description = 'crmid'
GROUP BY date_key;
|
See the Audience size trend by identity widget documentation for information on the appearance and functionality of this insight.
Audience size trend audience-size-trend
Questions answered by this insight:
- How has the audience size changed over time, including any anomalies?
- How can I find the overall trend in audience size over the periods: 30 days, 90 days, and 12 months?
- What are the key characteristics of the audience that contribute to its size? For example, spikes due to an email marketing campaigns.
Select to reveal the SQL that generates this insight
code language-sql |
SELECT date_key,
sum(count_of_profiles) AS audience_size
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines x
INNER JOIN
(SELECT MAX(process_date) last_process_date,
merge_policy_id
FROM qsaccel.profile_agg.adwh_lkup_process_delta_log
WHERE process_name = 'FACT_TABLES_PROCESSING'
AND process_status = 'SUCCESSFUL'
GROUP BY merge_policy_id) y ON x.merge_policy_id = y.merge_policy_id
WHERE date_key >= dateadd(DAY, -30, y.last_process_date)
AND x.segment_id = 1333234510
GROUP BY date_key,
segment_id;
|
See the Audience size trend widget documentation for information on the appearance and functionality of this insight.
Audience size audience-size
Questions answered by this insight:
- What is the current total audience size?
- How does the current audience size compare to previous periods or specific audiences?
- What is the impact of recent marketing campaigns on the audience size?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT
sum(
qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.count_of_profiles
) count_of_profiles
FROM
qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_segments ON qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_id = qsaccel.profile_agg.adwh_dim_segments.segment_id
WHERE
qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.segment_id = -1323307941
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id = 1914917902
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = '2024-01-12';
|
See the Audience size widget documentation for information on the appearance and functionality of this insight.
Customer AI distribution of scores customer-ai-distribution-of-scores
Questions answered by this insight:
- What is the scoring distribution for each bucket of my Customer AI model, filtered by a selected audience?
- What is the scoring distribution of high, medium, and low for a particular audience?
- What is the breakdown of scoring distribution by various audiences of interest?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT b.model_name,
b.model_type,
c.segment_name,
c.segment_id,
CASE
WHEN score >= 0
AND score < 25 THEN 'LOW'
WHEN score >= 25
AND score < 75 THEN 'MEDIUM'
WHEN score >= 75
AND score <= 100 THEN 'HIGH'
END bucket_name,
CASE
WHEN score >= 0
AND score < 5 THEN '02.50'
WHEN score >= 5
AND score < 10 THEN '07.50'
WHEN score >= 10
AND score < 15 THEN '12.50'
WHEN score >= 15
AND score < 20 THEN '17.50'
WHEN score >= 20
AND score < 25 THEN '22.50'
WHEN score >= 25
AND score < 30 THEN '27.50'
WHEN score >= 30
AND score < 35 THEN '32.50'
WHEN score >= 35
AND score < 40 THEN '37.50'
WHEN score >= 40
AND score < 45 THEN '42.50'
WHEN score >= 45
AND score < 50 THEN '47.50'
WHEN score >= 50
AND score < 55 THEN '52.50'
WHEN score >= 55
AND score < 60 THEN '57.50'
WHEN score >= 60
AND score < 65 THEN '62.50'
WHEN score >= 65
AND score < 70 THEN '67.50'
WHEN score >= 70
AND score < 75 THEN '72.50'
WHEN score >= 75
AND score < 80 THEN '77.50'
WHEN score >= 80
AND score < 85 THEN '82.50'
WHEN score >= 85
AND score < 90 THEN '87.50'
WHEN score >= 90
AND score < 95 THEN '92.50'
WHEN score >= 95
AND score <= 100 THEN '97.50'
END score_bins,
Sum(CASE
WHEN score >= 0
AND score < 25 THEN count_of_profiles
WHEN score >= 25
AND score < 75 THEN count_of_profiles
WHEN score >= 75
AND score <= 100 THEN count_of_profiles
END) count_of_profiles
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_ai_models a
JOIN qsaccel.profile_agg.adwh_dim_ai_models b ON a.merge_policy_id = b.merge_policy_id
AND a.model_id = b.model_id
JOIN qsaccel.profile_agg.adwh_dim_segments c ON a.segment_id = c.segment_id
WHERE a.merge_policy_id = 1133248113
AND a.model_id = 1829081696
AND a.segment_id = 1870062812
AND score_date =
(SELECT MAX(score_date)
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_ai_models d
WHERE d.model_id = a.model_id) GROUP BY b.model_name,
b.model_type,
c.segment_name,
c.segment_id,
CASE
WHEN score >= 0
AND score < 25 THEN 'LOW'
WHEN score >= 25
AND score < 75 THEN 'MEDIUM'
WHEN score >= 75
AND score <= 100 THEN 'HIGH'
END,
CASE
WHEN score >= 0
AND score < 5 THEN '02.50'
WHEN score >= 5
AND score < 10 THEN '07.50'
WHEN score >= 10
AND score < 15 THEN '12.50'
WHEN score >= 15
AND score < 20 THEN '17.50'
WHEN score >= 20
AND score < 25 THEN '22.50'
WHEN score >= 25
AND score < 30 THEN '27.50'
WHEN score >= 30
AND score < 35 THEN '32.50'
WHEN score >= 35
AND score < 40 THEN '37.50'
WHEN score >= 40
AND score < 45 THEN '42.50'
WHEN score >= 45
AND score < 50 THEN '47.50'
WHEN score >= 50
AND score < 55 THEN '52.50'
WHEN score >= 55
AND score < 60 THEN '57.50'
WHEN score >= 60
AND score < 65 THEN '62.50'
WHEN score >= 65
AND score < 70 THEN '67.50'
WHEN score >= 70
AND score < 75 THEN '72.50'
WHEN score >= 75
AND score < 80 THEN '77.50'
WHEN score >= 80
AND score < 85 THEN '82.50'
WHEN score >= 85
AND score < 90 THEN '87.50'
WHEN score >= 90
AND score < 95 THEN '92.50'
WHEN score >= 95
AND score <= 100 THEN '97.50'
END;
|
See the Customer AI distribution of scores widget documentation for information on the appearance and functionality of this insight.
Customer AI scoring summary customer-ai-scoring-summary
Questions answered by this insight:
- What is the scoring summary for each of my Customer AI models for a particular audience?
- How do my Customer AI propensity scores change for different audiences?
- How does my scoring summary compare to the other KPIs in the audience overview?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT model_name,
model_type,
segment_name,
CASE
WHEN score BETWEEN 0 AND 24 THEN 'LOW'
WHEN score BETWEEN 25 AND 74 THEN 'MEDIUM'
WHEN score BETWEEN 75 AND 100 THEN 'HIGH'
END score_buckets,
sum(count_of_profiles) count_of_profiles
FROM QSAccel.profile_agg.adwh_fact_profile_by_segment_ai_models a
JOIN QSAccel.profile_agg.adwh_dim_ai_models b ON a.merge_policy_id=b.merge_policy_id
AND a.model_id=b.model_id
JOIN QSAccel.profile_agg.adwh_dim_segments c ON a.segment_id=c.segment_id
WHERE a.merge_policy_id=1133248113
AND a.model_id =1829081696
AND a.segment_id=1870062812
AND score_date=
(SELECT max(score_date)
FROM QSAccel.profile_agg.adwh_fact_profile_by_segment_ai_models d
WHERE d.model_id=a.model_id)
GROUP BY model_name,
model_type,
segment_name,
CASE
WHEN score BETWEEN 0 AND 24 THEN 'LOW'
WHEN score BETWEEN 25 AND 74 THEN 'MEDIUM'
WHEN score BETWEEN 75 AND 100 THEN 'HIGH'
END;
|
See the Customer AI scoring summary widget documentation for information on the appearance and functionality of this insight.
Identity overlap identity-overlap
Questions answered by this insight:
- What is the common intersection between Identity Type A and Identity Type B for a filtered audience?
- How do I refine customer audiences based on the overlap of specific identity types, to enhance the targeted marketing strategies?
- What insights can be gained from evaluating the campaign performance within the intersecting areas?
- Based on those insights, how can future marketing efforts be optimized?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT Sum(overlap_col1) overlap_col1,
Sum(overlap_col2) overlap_col2,
Sum(overlap_count) Overlap_count
FROM
(SELECT 0 overlap_col1,
0 overlap_col2,
Sum(count_of_profiles) Overlap_count
FROM qsaccel.profile_agg.adwh_fact_profile_overlap_of_namespace_by_segment
WHERE qsaccel.profile_agg.adwh_fact_profile_overlap_of_namespace_by_segment.segment_id = 1333234510
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_namespace_by_segment.merge_policy_id = 1709997014
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_namespace_by_segment.date_key = '2024-01-10'
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_namespace_by_segment.overlap_id IN
(SELECT a.overlap_id
FROM
(SELECT qsaccel.profile_agg.adwh_dim_overlap_namespaces.overlap_id overlap_id,
count(*) cnt_num
FROM qsaccel.profile_agg.adwh_dim_overlap_namespaces
WHERE qsaccel.profile_agg.adwh_dim_overlap_namespaces.merge_policy_id = 1709997014
AND qsaccel.profile_agg.adwh_dim_overlap_namespaces.overlap_namespaces in ('crmid',
'email')
GROUP BY qsaccel.profile_agg.adwh_dim_overlap_namespaces.overlap_id)a
WHERE a.cnt_num>1 )
UNION ALL SELECT count_of_profiles overlap_col1,
0 overlap_col2,
0 Overlap_count
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_namespaces ON qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id
WHERE qsaccel.profile_agg.adwh_dim_namespaces.namespace_description = 'crmid'
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.segment_id = 1333234510
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.merge_policy_id = 1709997014
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.date_key = '2024-01-10'
UNION ALL SELECT 0 overlap_col1,
count_of_profiles overlap_col2,
0 Overlap_count
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_namespaces ON qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id
WHERE qsaccel.profile_agg.adwh_dim_namespaces.namespace_description = 'email'
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.segment_id = 1333234510
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.merge_policy_id = 1709997014
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.date_key = '2024-01-10' ) a;
|
See the Identity overlap widget documentation for information on the appearance and functionality of this insight.
Profiles by identity profiles-by-identity
Questions answered by this insight:
- Which identity type has the highest proportion within the total count of profiles for a selected audience?
- Are there significant disparities among identity types for a selected audience?
- What is the overall distribution of identity types by audience?
- Are there any significant disparities or anomalies in identity counts for various audiences?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT qsaccel.profile_agg.adwh_dim_namespaces.namespace_description,
sum(qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.count_of_profiles) count_of_profiles
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_namespaces ON qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.segment_id = 1333234510
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.merge_policy_id = 1709997014
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_and_namespace_trendlines.date_key = '2024-01-10'
GROUP BY qsaccel.profile_agg.adwh_dim_namespaces.namespace_description
ORDER BY count_of_profiles DESC;
|
See the Profiles by identity widget documentation for information on the appearance and functionality of this insight.
Scheduled activations scheduled-activations
Questions answered by this insight:
- What are the start and end dates of the top-performing activations for a particular audience on a specific platform?
- Which platforms were used the most for scheduled activations of a particular audience?
- Are there any patterns in platform usage that could guide decisions on prioritizing or diversifying activation strategies for a specific audience?
Select to reveal the SQL that generates this insight
code language-sql |
SELECT p.destination_platform ,
p.destination_platform_name AS platform ,
d.destination_name ,
d.destination ,
br.start_date ,
CASE
WHEN br.end_date = '9999-12-31' THEN 'Ongoing'
ELSE br.end_date
END AS end_date
FROM qsaccel.profile_agg.adwh_dim_br_segment_destinations br
JOIN qsaccel.profile_agg.adwh_dim_destination d ON br.destination_id = d.destination_id
JOIN qsaccel.profile_agg.adwh_dim_destination_platform p ON d.destination_platform_id = p.destination_platform_id
JOIN
(SELECT MAX(process_date) AS last_process_date
FROM qsaccel.profile_agg.adwh_lkup_process_delta_log
WHERE process_name = 'FACT_TABLES_PROCESSING'
AND process_status = 'SUCCESSFUL' ) lpd ON lpd.last_process_date BETWEEN br.start_date AND br.end_date
AND br.segment_id = 1333234510;
|
See the Scheduled activations widget documentation for information on the appearance and functionality of this insight.
Next steps
By reading this document, you now understand the SQL that generates dashboard insights and what common questions this analysis solves. You can now edit and iterate on the SQL to generate your own insights.
See the View SQL documentation for more information on how to adapt your insights’ SQL directly through the PLatform UI.
You can also read and understand the SQL that generates insights for the Profiles, Account Profiles and Destinations dashboards.
ececc77d-ff44-4382-85ee-a087c8834323