数据中的每一行都表示一个转化,在该转化中,相关接触点的信息将存储为touchpointsDetail
列下的结构数组。
接触点信息 | 栏目 |
---|---|
接触点名称 | touchpointsDetail. touchpointName |
接触点渠道 | touchpointsDetail.touchPoint.mediaChannel |
接触点Attribution AI算法得分 | touchpointsDetail.scores.algorithmicSourced touchpointsDetail.scores.algorithmicInfluenced |
在Adobe Experience Platform UI的左侧导航中,选择Datasets。 此时会显示Datasets页面。 接下来,选择Browse选项卡,并查找您的Attribution AI得分的输出数据集。
选择输出数据集。 此时会显示数据集活动页面。
在数据集活动页面中,选择右上角的预览数据集 ,以预览数据并确保按预期摄取数据。
预览数据后,选择右边栏中的架构。 此时会出现一个弹出窗口,其中包含架构名称和描述。 选择架构名称超链接可重定向到评分架构。
使用评分架构,您可以选择或搜索值。 选择字段属性侧边栏后,将打开该边栏,以便复制路径以用于创建查询。
要从Platform UI中访问查询服务,请首先在左侧导航中选择Querys ,然后选择Browse选项卡。 将加载之前保存的查询列表。
接下来,选择右上角的创建查询。 加载查询编辑器。 使用查询编辑器,您可以开始使用评分数据创建查询。
有关查询编辑器的详细信息,请访问查询编辑器用户指南。
以下查询可用作不同分数分析方案的模板。 您需要将_tenantId
和your_score_output_dataset
替换为在评分输出架构中找到的正确值。
根据数据的摄取方式,下面使用的值(如timestamp
)可能采用不同的格式。
按转化事件(在转化窗口内)划分的转化总数
SELECT conversionName,
SUM(scores.firstTouch) as total_conversions,
SUM(scores.algorithmicSourced) as total_attributed_conversions
FROM
(SELECT
_tenantId.your_score_output_dataset.conversionName
as conversionName,
inline(_tenantId.your_score_output_dataset.touchpointsDetail),
timestamp as conversion_timestamp
FROM
your_score_output_dataset
)
WHERE
conversion_timestamp >= '2020-07-16'
AND
conversion_timestamp < '2020-10-14'
GROUP BY
conversionName
仅转化事件的总数(在转化窗口内)
SELECT
_tenantId.your_score_output_dataset.conversionName as conversionName,
COUNT(1) as convOnly_cnt
FROM
your_score_output_dataset
WHERE
_tenantId.your_score_output_dataset.touchpointsDetail.touchpointName[0] IS NULL AND
timestamp >= '2020-07-16' AND
timestamp < '2020-10-14'
GROUP BY
conversionName
每日转化数
SELECT conversionName,
DATE(conversion_timestamp) as conversion_date,
SUM(scores.firstTouch) as convertion_cnt
FROM
(SELECT
_tenantId.your_score_output_dataset.conversionName as conversionName,
inline(_tenantId.your_score_output_dataset.touchpointsDetail),
timestamp as conversion_timestamp
FROM
your_score_output_dataset
)
GROUP BY
conversionName, DATE(conversion_timestamp)
ORDER BY
conversionName, DATE(conversion_timestamp)
LIMIT 20
按定义类型(在转化窗口内)划分的转化路径上的接触点数量
SELECT conversionName,
touchpointName,
COUNT(1) as tp_count
FROM
(SELECT
_tenantId.your_score_output_dataset.conversionName as conversionName,
inline(_tenantId.your_score_output_dataset.touchpointsDetail),
timestamp as conversion_timestamp
FROM
your_score_output_dataset
)
WHERE
conversion_timestamp >= '2020-07-16' AND
conversion_timestamp < '2020-10-14' AND
touchpointName IS NOT NULL
GROUP BY
conversionName, touchpointName
ORDER BY
conversionName, tp_count DESC
按接触点和转化日期划分的增量单位(在转化窗口内)
SELECT conversionName,
touchpointName,
DATE(conversion_timestamp) as conversion_date,
SUM(scores.algorithmicSourced) as incremental_units
FROM
(SELECT
_tenantId.your_score_output_dataset.conversionName as conversionName,
inline(_tenantId.your_score_output_dataset.touchpointsDetail),
timestamp as conversion_timestamp
FROM
your_score_output_dataset
)
WHERE
conversion_timestamp >= '2020-07-16' AND
conversion_timestamp < '2020-10-14' AND
touchpointName IS NOT NULL
GROUP BY
conversionName, touchpointName, DATE(conversion_timestamp)
ORDER BY
conversionName, touchpointName, DATE(conversion_timestamp)
按接触点和接触点日期划分的增量单位(在转化窗口内)
SELECT conversionName,
touchpointName,
DATE(touchpoint.timestamp) as touchpoint_date,
SUM(scores.algorithmicSourced) as incremental_units
FROM
(SELECT
_tenantId.your_score_output_dataset.conversionName as conversionName,
inline(_tenantId.your_score_output_dataset.touchpointsDetail),
timestamp as conversion_timestamp
FROM
your_score_output_dataset
)
WHERE
conversion_timestamp >= '2020-07-16' AND
conversion_timestamp < '2020-10-14' AND
touchpointName IS NOT NULL
GROUP BY
conversionName, touchpointName, DATE(touchpoint.timestamp)
ORDER BY
conversionName, touchpointName, DATE(touchpoint.timestamp)
LIMIT 20
所有评分模型的特定接触点类型的汇总得分(在转化窗口内)
SELECT
conversionName,
touchpointName,
SUM(scores.algorithmicSourced) as total_incremental_units,
SUM(scores.algorithmicInfluenced) as total_influenced_units,
SUM(scores.uShape) as total_uShape_units,
SUM(scores.decayUnits) as total_decay_units,
SUM(scores.linear) as total_linear_units,
SUM(scores.lastTouch) as total_lastTouch_units,
SUM(scores.firstTouch) as total_firstTouch_units
FROM
(SELECT
_tenantId.your_score_output_dataset.conversionName as conversionName,
inline(_tenantId.your_score_output_dataset.touchpointsDetail),
timestamp as conversion_timestamp
FROM
your_score_output_dataset
)
WHERE
conversion_timestamp >= '2020-07-16' AND
conversion_timestamp < '2020-10-14' AND
touchpointName = 'display'
GROUP BY
conversionName, touchpointName
ORDER BY
conversionName, touchpointName
高级 — 路径长度分析
获取每个转化事件类型的路径长度分布:
WITH agg_path AS (
SELECT
_tenantId.your_score_output_dataset.conversionName as conversionName,
sum(size(_tenantId.your_score_output_dataset.touchpointsDetail)) as path_length
FROM
your_score_output_dataset
WHERE
_tenantId.your_score_output_dataset.touchpointsDetail.touchpointName[0] IS NOT NULL AND
timestamp >= '2020-07-16' AND
timestamp < '2020-10-14'
GROUP BY
_tenantId.your_score_output_dataset.conversionName,
eventMergeId
)
SELECT
conversionName,
path_length,
count(1) as conversionPath_count
FROM
agg_path
GROUP BY
conversionName, path_length
ORDER BY
conversionName, path_length
高级 — 转化路径分析中的接触点数量不同
获取每个转化事件类型的转化路径上不同接触点数量的分布:
WITH agg_path AS (
SELECT
_tenantId.your_score_output_dataset.conversionName as conversionName,
size(array_distinct(flatten(collect_list(_tenantId.your_score_output_dataset.touchpointsDetail.touchpointName)))) as num_dist_tp
FROM
your_score_output_dataset
WHERE
_tenantId.your_score_output_dataset.touchpointsDetail.touchpointName[0] IS NOT NULL AND
timestamp >= '2020-07-16' AND
timestamp < '2020-10-14'
GROUP BY
_tenantId.your_score_output_dataset.conversionName,
eventMergeId
)
SELECT
conversionName,
num_dist_tp,
count(1) as conversionPath_count
FROM
agg_path
GROUP BY
conversionName, num_dist_tp
ORDER BY
conversionName, num_dist_tp
此查询会将struct列拼合为多个单数列,并将数组分解为多行。 这有助于将归因得分转换为CSV格式。 此查询的输出具有一行中的一个转化,以及与该转化对应的接触点之一。
在此示例中,除了_tenantId
和your_score_output_dataset
之外,还需要替换{COLUMN_NAME}
。 COLUMN_NAME
变量可采用配置Attribution AI实例期间添加的可选传递列名称(报表列)的值。 请查看您的评分输出架构,以查找完成此查询所需的{COLUMN_NAME}
值。
SELECT
segmentation,
conversionName,
scoreCreatedTime,
aaid, _id, eventMergeId,
conversion.eventType as conversion_eventType,
conversion.quantity as conversion_quantity,
conversion.eventSource as conversion_eventSource,
conversion.priceTotal as conversion_priceTotal,
conversion.timestamp as conversion_timestamp,
conversion.geo as conversion_geo,
conversion.receivedTimestamp as conversion_receivedTimestamp,
conversion.dataSource as conversion_dataSource,
conversion.productType as conversion_productType,
conversion.passThrough.{COLUMN_NAME} as conversion_passThru_column,
conversion.skuId as conversion_skuId,
conversion.product as conversion_product,
touchpointName,
touchPoint.campaignGroup as tp_campaignGroup,
touchPoint.mediaType as tp_mediaType,
touchPoint.campaignTag as tp_campaignTag,
touchPoint.timestamp as tp_timestamp,
touchPoint.geo as tp_geo,
touchPoint.receivedTimestamp as tp_receivedTimestamp,
touchPoint.passThrough.{COLUMN_NAME} as tp_passThru_column,
touchPoint.campaignName as tp_campaignName,
touchPoint.mediaAction as tp_mediaAction,
touchPoint.mediaChannel as tp_mediaChannel,
touchPoint.eventid as tp_eventid,
scores.*
FROM (
SELECT
_tenantId.your_score_output_dataset.segmentation,
_tenantId.your_score_output_dataset.conversionName,
_tenantId.your_score_output_dataset.scoreCreatedTime,
_tenantId.your_score_output_dataset.conversion,
_id,
eventMergeId,
map_values(identityMap)[0][0].id as aaid,
inline(_tenantId.your_score_output_dataset.touchpointsDetail)
FROM
your_score_output_dataset
)