配置文件分析
- 主题:
- 功能板
创建对象:
- 开发人员
- 用户
通过分析数据模型而获得的见解,可使您的Adobe Real-Time CDP数据更易于访问、理解并影响决策。
通过访问为配置文件提供支持的SQL来了解配置文件洞察,然后生成您自己的洞察以进一步探索您的客户及其构成您配置文件的消费者体验。 通过使用现有的Real-Time CDP数据模型SQL作为灵感,根据独特的业务需求创建查询,将原始数据转换为新的可操作洞察。
有关如何直接通过Experience Platform UI调整您的分析的SQL的更多信息,请参阅查看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 = 2027892989
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=1333234510
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.segment2=1559754729)
OR (qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.segment1=1559754729
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_segments.segment2=1333234510))
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 = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = '2024-01-10'
AND qsaccel.profile_agg.adwh_dim_segments.segment_Id = 1333234510
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 = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = '2024-01-10'
AND qsaccel.profile_agg.adwh_dim_segments.segment_Id = 1559754729 ) a;
有关此分析的外观和功能的信息,请参阅按合并策略构件划分的受众重叠文档。
受众重叠报告
通过此洞察回答的问题:
- 50个最重叠的受众是什么?
- 50个最不重叠的受众是哪个?
- 重叠模式如何因合并策略而变化?
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;
有关此分析的外观和功能的信息,请参阅受众重叠报表构件文档。
受众(计数)
通过此洞察回答的问题:
- 哪种合并策略主要用于分段?
- 受众在合并策略中的分布情况如何?
- 随着时间的推移,特定合并策略的受众数量是否有任何重大变化?
SELECT count(DISTINCT a.segment_id) count_of_segments
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines a
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) b ON a.merge_policy_id= b.merge_policy_id
AND a.date_key = b.last_process_date
WHERE a.merge_policy_id= 2027892989;
- 受众在映射目标和未映射目标之间的总体分布情况如何?
- 哪些特定目标的映射受众数量最多?
- 未映射的总受众比例如何?
- 在这些未映射的受众中,是否存在模式或相关趋势?
SELECT COUNT(DISTINCT (y.segment_id)) AS count_mapped_segments,
COUNT(DISTINCT (x.segment_id)) - COUNT(DISTINCT (y.segment_id)) AS count_unmapped_segments,
COUNT(DISTINCT (x.segment_id)) AS total_segments
FROM qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines x
LEFT JOIN qsaccel.profile_agg.adwh_dim_br_segment_destinations y ON x.segment_id = y.segment_id
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) z ON x.merge_policy_id = z.merge_policy_id
AND x.date_key = z.last_process_date
WHERE x.merge_policy_id = 2027892989;
有关此分析的外观和功能的信息,请参阅映射到目标状态小组件文档。
受众规模
通过此洞察回答的问题:
- 哪个受众区段规模最大?
- 最大的五个受众是什么?
- 主要受众的受众规模分布会随着时间发生何种变化?
SELECT qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key,
qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_name,
qsaccel.profile_agg.adwh_dim_segments.segment,
qsaccel.profile_agg.adwh_dim_segments.segment_name,
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
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_merge_policies ON qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id=adwh_dim_merge_policies.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key = '2024-01-10'
AND qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.merge_policy_id= 2027892989
GROUP BY qsaccel.profile_agg.adwh_fact_profile_by_segment_trendlines.date_key,
qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_name,
qsaccel.profile_agg.adwh_dim_segments.segment,
qsaccel.profile_agg.adwh_dim_segments.segment_name
ORDER BY count_of_profiles DESC
LIMIT 20;
有关此分析的外观和功能的信息,请参阅受众大小小组件文档。
客户人工智能得分分布
通过此洞察回答的问题:
- 我的每个Customer AI模型在各存储桶中的得分分布情况如何?
- 按高、中、低得分分布情况如何?
- 按合并策略划分得分分布的划分是什么?
SELECT b.model_name,
b.model_type,
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_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
WHERE a.merge_policy_id = 2027892989
AND a.model_id = 1829081696
AND score_date =
(SELECT Max(score_date)
FROM qsaccel.profile_agg.adwh_fact_profile_ai_models d
WHERE d.model_id = a.model_id) GROUP BY b.model_name,
model_type,
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;
有关此洞察的外观和功能的信息,请参阅得分构件的Customer AI分发文档。
客户人工智能评分汇总
通过此洞察回答的问题:
- 我的每个Customer AI模型的得分摘要是什么?
- 我的客户人工智能倾向分数如何针对不同受众发生变化?
- 与用户档案概述中的其他KPI相比,我的得分摘要有何变化?
SELECT model_name,
model_type,
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_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
WHERE a.merge_policy_id=2027892989
AND a.model_id =1829081696
AND score_date=
(SELECT max(score_date)
FROM QSAccel.profile_agg.adwh_fact_profile_ai_models d
WHERE d.model_id=a.model_id)
GROUP BY model_name,
model_type,
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;
- 标识类型A和标识类型B之间的公共交叉点是什么?
- 如何根据特定身份类型之间的重叠来优化客户受众,以增强有针对性的营销策略?
- 评估交叉区域内的营销活动绩效可以获得哪些见解?
- 利用这一营销活动效果洞察,如何优化未来的营销工作?
SELECT Sum(overlap_col1) overlap_col1,
Sum(overlap_col2) overlap_col2,
coalesce(Sum(overlap_count), 0) 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
WHERE qsaccel.profile_agg.adwh_fact_profile_overlap_of_namespace.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_namespace.date_key = '2024-01-10'
AND qsaccel.profile_agg.adwh_fact_profile_overlap_of_namespace.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 = 2027892989
AND qsaccel.profile_agg.adwh_dim_overlap_namespaces.overlap_namespaces in ('avid',
'crmid')
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_namespace_trendlines
JOIN qsaccel.profile_agg.adwh_dim_namespaces ON qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key = '2024-01-10'
AND qsaccel.profile_agg.adwh_dim_namespaces.namespace_description = 'avid'
UNION ALL SELECT 0 overlap_col1,
count_of_profiles overlap_col2,
0 Overlap_count
FROM qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines
JOIN qsaccel.profile_agg.adwh_dim_namespaces ON qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key = '2024-01-10'
AND qsaccel.profile_agg.adwh_dim_namespaces.namespace_description = 'crmid' )a;
有关此分析的外观和功能的信息,请参阅身份重叠构件文档。
轮廓计数
通过此洞察回答的问题:
- Adobe Real-Time Customer Data Platform中的总配置文件计数是多少?
- 如何根据合并策略分发用户档案?
- 哪个合并策略具有最高的配置文件计数?
生成这些分析的SQl如下所示:
SELECT qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_name,
sum(qsaccel.profile_agg.adwh_fact_profile.count_of_profiles) CNT
FROM qsaccel.profile_agg.adwh_fact_profile
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_merge_policies ON qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_id=adwh_fact_profile.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile.date_key='2024-01-10'
AND qsaccel.profile_agg.adwh_fact_profile.merge_policy_id = 2027892989
GROUP BY qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_name;
- 总体配置文件计数变化的趋势是什么?
- 哪些原因导致配置文件计数显着激增或骤减?
- 是否有特定合并策略可驱动配置文件计数更改?
SELECT (sum(count_of_profiles) - sum(count_of_profiles_days_ago)) profiles_added
FROM
(SELECT sum(qsaccel.profile_agg.adwh_fact_profile.count_of_profiles) count_of_profiles,
0 count_of_profiles_days_ago
FROM qsaccel.profile_agg.adwh_fact_profile
WHERE qsaccel.profile_agg.adwh_fact_profile.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile.date_key = '2024-01-10'
UNION ALL SELECT 0 count_of_profiles,
CASE
WHEN sum(cntondatediff) =0 THEN sum(cntmin)
ELSE sum(cntondatediff)
END AS count_of_profiles_days_ago
FROM
(SELECT coalesce(sum(qsaccel.profile_agg.adwh_fact_profile_by_trendlines.count_of_profiles), 0) cntondatediff,
0 cntmin
FROM qsaccel.profile_agg.adwh_fact_profile_by_trendlines
WHERE qsaccel.profile_agg.adwh_fact_profile_by_trendlines.merge_policy_id =2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key =dateadd(DAY, - 30, '2024-01-10')
UNION ALL SELECT 0 cntondatediff,
sum(qsaccel.profile_agg.adwh_fact_profile_by_trendlines.count_of_profiles) countMin
FROM qsaccel.profile_agg.adwh_fact_profile_by_trendlines
WHERE qsaccel.profile_agg.adwh_fact_profile_by_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key =
(SELECT min(qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key) col
FROM qsaccel.profile_agg.adwh_fact_profile_by_trendlines
WHERE qsaccel.profile_agg.adwh_fact_profile_by_trendlines.merge_policy_id =2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key >= dateadd(DAY, - 30, '2024-01-10')
AND qsaccel.profile_agg.adwh_fact_profile_by_trendlines.count_of_profiles IS NOT NULL) )b) a;
有关此分析的外观和功能的信息,请参阅配置文件计数更改构件文档。
配置文件计数变化趋势
通过此洞察回答的问题:
- 根据合并策略,过去12个月内用户档案计数变化的总体趋势是什么?
- 过去30天内用户档案计数是否有特定的变化模式或波动需要引起注意?
- 与总体趋势相比,过去90天内用户档案计数有何变化?
SELECT date_key,
profiles_count_change
FROM
(SELECT rn_num,
date_key,
(count_of_profiles-lag(count_of_profiles, 1, 0) over(
ORDER BY date_key))profiles_count_change
FROM
(SELECT qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key,
sum(qsaccel.profile_agg.adwh_fact_profile_by_trendlines.count_of_profiles) count_of_profiles,
row_number() OVER (
ORDER BY qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key) rn_num
FROM qsaccel.profile_agg.adwh_fact_profile_by_trendlines
WHERE qsaccel.profile_agg.adwh_fact_profile_by_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key >=dateadd(DAY, - 30 -1, '2024-01-10')
GROUP BY qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key)a)b
WHERE rn_num > 1;
- 过去30天内基于合并策略的配置文件计数的整体趋势是什么?
- 根据此趋势,与长期趋势(例如,90天和12个月)有何异同?
- 在指定的时间段(30天、90天和12个月)内,哪个合并策略对配置文件计数增加或减少的贡献最大?
- 用户档案计数中是否有任何特定的峰值或峰值与30天时间范围内的某些事件或期间相关联?
SELECT date_key,
sum(count_of_profiles) AS count_of_profiles
FROM qsaccel.profile_agg.adwh_fact_profile_by_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, -365, y.last_process_date)
AND x.merge_policy_id = 2027892989
GROUP BY date_key;
有关此分析的外观和功能的信息,请参阅配置文件计数趋势构件文档。
按身份标识列出的轮廓
通过此洞察回答的问题:
- 在配置文件总数中,哪种身份类型所占的比例更高?
- 身份类型之间是否存在显着差异?
- 身份类型的总体分布情况如何?
- 身份计数是否存在任何显着差异或异常?
SELECT qsaccel.profile_agg.adwh_dim_namespaces.namespace_description,
sum(qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.count_of_profiles) count_of_profiles
FROM qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_namespaces ON qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key = '2024-01-10'
GROUP BY qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key,
qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id,
qsaccel.profile_agg.adwh_dim_namespaces.namespace_description
ORDER BY count_of_profiles DESC;
- 根据合并策略,过去12个月内用户档案计数变化的总体趋势是什么?
- 过去30天内用户档案计数变化是否存在特定的模式或波动需要引起注意?
- 与总体趋势相比,过去90天内用户档案数量的变化如何?
SELECT date_key,
profiles_count_change
FROM
(SELECT rn_num,
date_key,
(count_of_profiles-lag(count_of_profiles, 1, 0) over(
ORDER BY date_key))profiles_count_change
FROM
(SELECT qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key,
sum(qsaccel.profile_agg.adwh_fact_profile_by_trendlines.count_of_profiles) count_of_profiles,
row_number() OVER (
ORDER BY qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key) rn_num
FROM qsaccel.profile_agg.adwh_fact_profile_by_trendlines
WHERE qsaccel.profile_agg.adwh_fact_profile_by_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key >=dateadd(DAY, - 30 -1, '2024-01-10')
GROUP BY qsaccel.profile_agg.adwh_fact_profile_by_trendlines.date_key)a)b
WHERE rn_num > 1;
有关此分析的外观和功能的信息,请参阅配置文件计数更改趋势构件文档。
按身份标识分类的轮廓计数变化趋势
通过此洞察回答的问题:
- 过去12个月中不同身份配置文件计数变化的总体趋势是什么?
- 是否有任何特定的身份趋势显示过去30天内发生了显着变化?
- 比较特定身份的30天、90天和12个月趋势时,用户档案计数的变化有何不同?
SELECT date_key,
namespace_description,
profiles_count_change
FROM
(SELECT rn_num,
date_key,
namespace_description,
(count_of_profiles - lag(count_of_profiles, 1, 0) over(PARTITION BY namespace_description
ORDER BY date_key)) profiles_count_change
FROM
(SELECT qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key,
qsaccel.profile_agg.adwh_dim_namespaces.namespace_description,
sum(qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.count_of_profiles) count_of_profiles,
row_number() OVER (PARTITION BY qsaccel.profile_agg.adwh_dim_namespaces.namespace_description
ORDER BY qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key) rn_num
FROM qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_namespaces ON qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.namespace_id= -1042977439
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key >= dateadd(DAY, - 30 -1, '2024-01-10')
GROUP BY qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key,
adwh_dim_namespaces.namespace_description)a)b
WHERE rn_num > 1;
有关此分析的外观和功能的信息,请参阅按身份构件列出的配置文件计数变化趋势。
单一身份标识轮廓
通过此洞察回答的问题:
- 我的客户身份数据是否一致地用单个身份表示?
- 我的用户群中有多少百分比只包含单一身份类型的配置文件?
- 对于仅具有单一身份类型的配置文件,这会如何影响配置文件完整性?
- 最常见的身份类型与单个身份配置文件计数之间是否存在关联?
SELECT qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_name,
sum(qsaccel.profile_agg.adwh_fact_profile.count_of_Single_Identity_profiles) CNT
FROM qsaccel.profile_agg.adwh_fact_profile
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_merge_policies ON qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_id=adwh_fact_profile.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile.date_key='2024-01-10'
AND qsaccel.profile_agg.adwh_fact_profile.merge_policy_id = 2027892989
GROUP BY qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_name;
- 有多少独特客户使用单一身份(例如,电子邮件或电话号码)注册?
- 单一身份配置文件在不同身份类型(如电子邮件或电话号码)之间的分布情况如何?
- 单一身份配置文件中是否存在新兴的身份模式或转变?
SELECT qsaccel.profile_agg.adwh_dim_namespaces.namespace_description,
sum(qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.count_of_Single_Identity_profiles) count_of_Single_Identity_profiles
FROM qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_namespaces ON qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.namespace_id = qsaccel.profile_agg.adwh_dim_namespaces.namespace_id
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = qsaccel.profile_agg.adwh_dim_namespaces.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id = 2027892989
AND qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key = '2024-01-10'
GROUP BY qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.date_key,
qsaccel.profile_agg.adwh_fact_profile_by_namespace_trendlines.merge_policy_id,
qsaccel.profile_agg.adwh_dim_namespaces.namespace_description;
有关此分析的外观和功能的信息,请参阅按身份构件列出的单一身份配置文件。
未分段的轮廓
通过此洞察回答的问题:
- 有多少用户档案不属于某个受众?
- 未分段配置文件占总受众的百分比是多少?
- 任何合并策略是否都会产生大量未分段的配置文件?
SELECT qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_name,
sum(qsaccel.profile_agg.adwh_fact_profile.count_of_Orphan_profiles) CNT
FROM qsaccel.profile_agg.adwh_fact_profile
LEFT OUTER JOIN qsaccel.profile_agg.adwh_dim_merge_policies ON qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_id=adwh_fact_profile.merge_policy_id
WHERE qsaccel.profile_agg.adwh_fact_profile.date_key='2024-01-10'
AND qsaccel.profile_agg.adwh_fact_profile.merge_policy_id = 2027892989
GROUP BY qsaccel.profile_agg.adwh_dim_merge_policies.merge_policy_name;
有关此分析的外观和功能的信息,请参阅未分段配置文件小组件文档。
后续步骤
通过阅读本文档,您现在了解了生成仪表板分析的SQL以及此分析可以解决哪些常见问题。 您现在可以对SQL进行编辑和迭代,以生成您自己的见解。
有关如何直接通过PLatform UI调整分析的SQL的详细信息,请参阅查看SQL文档。