此部分列出了几个在Data Lake中查询历程步骤事件的常用示例。
确保查询中使用的字段在相应架构中具有关联值。
id、instanceid和profileid之间有何区别
在特定时间范围内进入历程的用户档案数
此查询提供在给定时间范围内进入给定历程的不同用户档案的数量。
Data Lake查询
SELECT count(distinct _experience.journeyOrchestration.stepEvents.profileID)
FROM journey_step_events WHERE _experience.journeyOrchestration.stepEvents.journeyVersionID = '<journeyVersionID>'
AND _experience.journeyOrchestration.stepEvents.nodeType='start'
AND _experience.journeyOrchestration.stepEvents.instanceType = 'unitary'
AND DATE(timestamp) > (now() - interval '<last x hours>' hour);
特定历程中一段时间内每个节点发生的错误数
Data Lake查询
SELECT
_experience.journeyOrchestration.stepEvents.nodeName,
count(distinct _experience.journeyOrchestration.stepEvents.profileID)
FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.journeyVersionID='<journeyVersiionID>'
AND DATE(timestamp) > (now() - interval '<last x hours>' hour)
AND
(_experience.journeyOrchestration.stepEvents.actionExecutionError not NULL
OR _experience.journeyOrchestration.stepEvents.actionExecutionErrorCode not NULL
OR _experience.journeyOrchestration.stepEvents.actionExecutionOriginCode not NULL
OR _experience.journeyOrchestration.stepEvents.actionExecutionOriginError not NULL
OR _experience.journeyOrchestration.stepEvents.fetchError not NULL
OR _experience.journeyOrchestration.stepEvents.fetchErrorCode not NULL
)
GROUP BY _experience.journeyOrchestration.stepEvents.nodeName;
在特定时间范围内从特定历程中丢弃了多少事件
Data Lake查询
SELECT
count(_id) AS NUMBER_OF_EVENTS_DISCARDED
FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.journeyVersionID='<journeyVersiionID>'
AND DATE(timestamp) > (now() - interval '<last x hours>' hour);
在特定时间范围内特定历程中的特定用户档案会发生什么情况
Data Lake查询
此查询按时间顺序返回给定用户档案和历程在指定时间内的所有步骤事件和服务事件。
SELECT
timestamp,
_experience.journeyOrchestration.stepEvents.journeyVersionID,
_experience.journeyOrchestration.stepEvents.profileID,
_experience.journeyOrchestration.stepEvents.nodeName,
_experience.journeyOrchestration.stepEvents.journeyNodeProcessed,
_experience.journeyOrchestration.serviceType,
to_json(_experience.journeyOrchestration.profile),
to_json(_experience.journeyOrchestration.serviceEvents)
FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.journeyVersionID='<journeyVersionID>'
AND DATE(timestamp) > (now() - interval '<last x hours>' hour)
AND
(
_experience.journeyOrchestration.stepEvents.profileID='<profileID>'
OR _experience.journeyOrchestration.profile.ID='<profileID>'
);
ORDER BY timestamp;
两个节点之间经过的时间
例如,可以使用这些查询来估计等待活动所花费的时间。 这允许您确保正确配置等待活动。
Data Lake查询
WITH
START_NODE_INFO AS (
SELECT
timestamp AS TS_START,
_experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
_experience.journeyOrchestration.stepEvents.instanceID AS INSTANCE_ID
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey version id>' AND
_experience.journeyOrchestration.stepEvents.nodeName = '<name of node before wait activity>' AND
_experience.journeyOrchestration.stepEvents.journeyNodeProcessed = true
),
END_NODE_INFO AS (
SELECT
timestamp AS TS_END,
_experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
_experience.journeyOrchestration.stepEvents.instanceID AS INSTANCE_ID
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey version id>' AND
_experience.journeyOrchestration.stepEvents.nodeName = '<name of wait activity node>' AND
_experience.journeyOrchestration.stepEvents.journeyNodeProcessed = true
)
SELECT
T1.INSTANCE_ID AS INSTANCE_ID,
T1.NODE_NAME AS START_NODE_NAME,
T2.NODE_NAME AS END_NODE_NAME,
DATEDIFF(millisecond,T1.TS_START,T2.TS_END) AS ELAPSED_TIME_MS
FROM
START_NODE_INFO AS T1,
END_NODE_INFO AS T2
WHERE
T1.INSTANCE_ID = T2.INSTANCE_ID
Data Lake查询
WITH
START_NODE_INFO AS (
SELECT
timestamp AS TS_START,
_experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
_experience.journeyOrchestration.stepEvents.instanceID AS INSTANCE_ID
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey version id>' AND
_experience.journeyOrchestration.stepEvents.nodeName = '<name of node before wait activity>' AND
_experience.journeyOrchestration.stepEvents.journeyNodeProcessed = true
),
END_NODE_INFO AS (
SELECT
timestamp AS TS_END,
_experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
_experience.journeyOrchestration.stepEvents.instanceID AS INSTANCE_ID
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey version id>' AND
_experience.journeyOrchestration.stepEvents.nodeName = '<name of wait activity node>' AND
_experience.journeyOrchestration.stepEvents.journeyNodeProcessed = true
)
SELECT
AVG(DATEDIFF(millisecond,T1.TS_START,T2.TS_END)) AS AVERAGE_ELAPSED_TIME,
MIN(DATEDIFF(millisecond,T1.TS_START,T2.TS_END)) AS MIN_ELAPSED_TIME,
MAX(DATEDIFF(millisecond,T1.TS_START,T2.TS_END)) AS MAX_ELAPSED_TIME
FROM
START_NODE_INFO AS T1,
END_NODE_INFO AS T2
WHERE
T1.INSTANCE_ID = T2.INSTANCE_ID
如何检查serviceEvent的详细信息
历程步骤事件数据集包含所有stepEvents和serviceEvents。 stepEvents在报告中使用,因为它们与活动(事件、操作等)相关 历程中的用户档案。 serviceEvents存储在同一数据集中,它们表示用于调试的其他信息,例如体验事件丢弃的原因。
以下是检查serviceEvent详细信息的查询示例:
Data Lake查询
SELECT
_experience.journeyOrchestration.profile.ID,
_experience.journeyOrchestration.journey.versionID,
to_json(_experience.journeyOrchestration.serviceEvents)
FROM journey_step_event
WHERE _experience.journeyOrchestration.serviceType is not null;
历程中遇到的每个错误的列表
此查询允许您列出执行消息/操作时历程中遇到的每个错误。
Data Lake查询
SELECT _experience.journeyOrchestration.stepEvents.actionExecutionError, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.nodeName=<'message-name'>
AND _experience.journeyOrchestration.stepEvents.actionExecutionError IS NOT NULL
AND _experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>'
GROUP BY _experience.journeyOrchestration.stepEvents.actionExecutionError
示例
SELECT _experience.journeyOrchestration.stepEvents.actionExecutionError, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.nodeName='Message - 100KB Email with Gateway and Kafkav2'
AND _experience.journeyOrchestration.stepEvents.actionExecutionError IS NOT NULL
AND _experience.journeyOrchestration.stepEvents.journeyVersionID = '67b14482-143e-4f83-9cf5-cfec0fca3d26'
GROUP BY _experience.journeyOrchestration.stepEvents.actionExecutionError
此查询返回在历程中执行操作时发生的所有不同错误以及发生的次数。
查找配置文件是否输入了特定历程
Data Lake查询
SELECT count(distinct _id) FROM journey_step_events
where
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>'
示例
SELECT count(distinct _id) FROM journey_step_events
where
_experience.journeyOrchestration.stepEvents.journeyVersionID = 'ec9efdd0-8a7c-4d7a-a765-b2cad659fa4e' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com'
结果应大于0。 此查询返回用户档案进入历程的确切次数。
查找是否向用户档案发送了特定消息
方法1:如果您的消息名称在历程中不是唯一的(会在多个位置使用)。
Data Lake查询
SELECT count(distinct _id) FROM journey_step_events WHERE
_experience.journeyOrchestration.stepEvents.nodeID='<NodeId in the UI corresponding to the message>' AND
_experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>'
示例
SELECT count(distinct _id) FROM journey_step_events WHERE
_experience.journeyOrchestration.stepEvents.nodeID='17ae65a1-02dd-439d-b54e-b56a78520eba' AND
_experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.journeyVersionID = '67b14482-143e-4f83-9cf5-cfec0fca3d26' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com'
结果应大于0。 此查询仅告诉我们是否在历程端成功执行了消息操作。
方法2:如果消息的名称在历程中是唯一的。
Data Lake查询
SELECT count(distinct _id) FROM journey_step_events WHERE
_experience.journeyOrchestration.stepEvents.nodeName='<NodeName in the UI corresponding to the message>' AND
_experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>'
示例
SELECT count(distinct _id) FROM journey_step_events WHERE
_experience.journeyOrchestration.stepEvents.nodeID='Message- 100KB Email' AND
_experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.journeyVersionID = '67b14482-143e-4f83-9cf5-cfec0fca3d26' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com'
查询会返回所有消息的列表以及为所选用户档案调用的消息计数。
查找用户档案在过去30天内收到的所有邮件
Data Lake查询
SELECT _experience.journeyOrchestration.stepEvents.nodeName, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.nodeType = 'action' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>' AND
timestamp > (now() - interval '30' day)
GROUP BY _experience.journeyOrchestration.stepEvents.nodeName
示例
SELECT _experience.journeyOrchestration.stepEvents.nodeName, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.actionExecutionError IS NULL AND
_experience.journeyOrchestration.stepEvents.nodeType = 'action' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com' AND
timestamp > (now() - interval '30' day)
GROUP BY _experience.journeyOrchestration.stepEvents.nodeName
查询会返回所有消息的列表以及为所选用户档案调用的消息计数。
查找用户档案在过去30天内输入的所有历程
Data Lake查询
SELECT _experience.journeyOrchestration.stepEvents.journeyVersionName, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.nodeType = 'start' AND
_experience.journeyOrchestration.stepEvents.profileID = '<profileID corresponding to the namespace used>' AND
timestamp > (now() - interval '30' day)
GROUP BY _experience.journeyOrchestration.stepEvents.journeyVersionName
示例
SELECT _experience.journeyOrchestration.stepEvents.journeyVersionName, count(distinct _id) FROM journey_step_events
WHERE _experience.journeyOrchestration.stepEvents.nodeType = 'start' AND
_experience.journeyOrchestration.stepEvents.profileID = 'saurgarg@adobe.com' AND
timestamp > (now() - interval '30' day)
GROUP BY _experience.journeyOrchestration.stepEvents.journeyVersionName
查询会返回所有历程名称的列表,以及被查询的用户档案进入历程的次数。
符合每日历程资格的用户档案数
Data Lake查询
SELECT DATE(timestamp), count(distinct _experience.journeyOrchestration.stepEvents.profileID) FROM journey_step_events
WHERE DATE(timestamp) > (now() - interval '<last x days>' day)
AND _experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>'
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) desc
示例
SELECT DATE(timestamp), count(distinct _experience.journeyOrchestration.stepEvents.profileID) FROM journey_step_events
WHERE DATE(timestamp) > (now() - interval '100' day)
AND _experience.journeyOrchestration.stepEvents.journeyVersionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1'
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) desc
在定义的周期内,查询将返回每天进入历程的用户档案数。 如果通过多个身份输入的用户档案,则将被计算两次。 如果启用了重新进入,并且用户档案计数是在不同日期重新进入历程,则可能会跨不同日期复制用户档案计数。
完成区段导出作业所用的时间
Data Lake查询
select DATEDIFF (minute,
(select timestamp
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status = 'queued') ,
(select timestamp
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status = 'finished')) AS export_job_runtime;
示例
select DATEDIFF (minute,
(select timestamp
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status = 'queued') ,
(select timestamp
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status = 'finished')) AS export_job_runtime;
查询返回区段导出作业排队时间和最终结束时间之间的时间差(以分钟为单位)。
因重复而被历程丢弃的用户档案数
Data Lake查询
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_DUPLICATION'
示例
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_DUPLICATION'
查询返回历程因重复而被丢弃的所有用户档案ID。
由于命名空间无效而被历程丢弃的用户档案数
Data Lake查询
SELECT count(*) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_BAD_NAMESPACE'
示例
SELECT count(*) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_BAD_NAMESPACE'
查询将返回历程丢弃的所有配置文件ID,原因是这些ID具有无效的命名空间或没有该命名空间的标识。
历程因无标识映射而被丢弃的配置文件数
Data Lake查询
SELECT count(*) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_NO_IDENTITY_MAP'
示例
SELECT count(*) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_NO_IDENTITY_MAP'
查询返回因缺少身份映射而被历程丢弃的所有用户档案ID。
由于历程在测试节点中并且用户档案不是测试用户档案而被历程丢弃的用户档案数
Data Lake查询
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_NOT_A_TEST_PROFILE'
示例
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_NOT_A_TEST_PROFILE'
查询返回旅程丢弃的所有配置文件ID,因为导出作业在测试模式下运行,但配置文件的testProfile属性未设置为true。
历程因内部错误而丢弃的用户档案数
Data Lake查询
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_INTERNAL'
示例
SELECT count(distinct _experience.journeyOrchestration.profile.ID) FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '180ad071-d42d-42bb-8724-2a6ff0a109f1' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode = 'ERROR_INSTANCE_INTERNAL'
查询返回由于某些内部错误而被历程丢弃的所有用户档案ID。
给定历程版本的读取区段概述
Data Lake查询
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode AS EVENT_CODE,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportSegmentID AS SEGMENT_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status AS EXPORTJOB_STATUS,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal AS TOTAL_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized AS SUCCESS_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator'
它将返回与给定历程版本相关的所有服务事件。 我们可以遵循操作链:
我们还可以检测以下问题:
重要信息:如果此查询未返回任何事件,则可能是由于以下原因之一:
获取给定历程版本的读取区段错误
Data Lake查询
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode AS EVENT_CODE,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportSegmentID AS SEGMENT_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status AS EXPORTJOB_STATUS,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal AS TOTAL_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized AS SUCCESS_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'ERROR_TOPIC_CREATION',
'ERROR_EXPORTJOB_APICALL',
'ERROR_EXPORTJOB_APICALL_TIMEOUT',
'ERROR_EXPORTJOB_FAILED'
)
获取导出作业处理状态
Data Lake查询
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode AS EVENT_CODE,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportSegmentID AS SEGMENT_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.status AS EXPORTJOB_STATUS,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal AS TOTAL_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized AS SUCCESS_EXPORTED_PROFILES_COUNT,
_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'INFO_EXPORTJOB_SUCCEEDED',
'ERROR_EXPORTJOB_FAILED'
)
如果未返回任何记录,则意味着:
获取导出用户档案的量度,包括每个导出作业的放弃和导出作业量度
Data Lake查询
WITH
DISCARDED_EXPORTED_PROFILES AS (
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
count(distinct _experience.journeyOrchestration.profile.ID) AS DISCARDED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'ERROR_INSTANCE_DUPLICATION',
'ERROR_INSTANCE_BAD_NAMESPACE',
'ERROR_INSTANCE_NO_IDENTITY_MAP',
'ERROR_INSTANCE_NOT_A_TEST_PROFILE',
'ERROR_INSTANCE_INTERNAL'
)
GROUP BY
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID
),
SEGMENT_EXPORT_METRICS AS (
SELECT
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID AS EXPORTJOB_ID,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal) AS TOTAL_EXPORTED_PROFILES_COUNT,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized) AS SUCCESS_EXPORTED_PROFILES_COUNT,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed) AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'INFO_EXPORTJOB_SUCCEEDED',
'ERROR_EXPORTJOB_FAILED'
)
GROUP BY
_experience.journeyOrchestration.serviceEvents.segmentExportJob.ID
)
SELECT
sum(T2.TOTAL_EXPORTED_PROFILES_COUNT),
sum(T2.SUCCESS_EXPORTED_PROFILES_COUNT),
sum(T2.FAILED_EXPORTED_PROFILES_COUNT),
sum(T1.DISCARDED_PROFILES_COUNT)
FROM
DISCARDED_EXPORTED_PROFILES AS T1,
SEGMENT_EXPORT_METRICS AS T2
WHERE T1.EXPORTJOB_ID = T2.EXPORTJOB_ID
获取所有导出作业的汇总量度(区段导出作业和放弃作业)
Data Lake查询
WITH
DISCARDED_EXPORTED_PROFILES AS (
SELECT
_experience.journeyOrchestration.journey.versionID AS JOURNEYVERSION_ID,
count(distinct _experience.journeyOrchestration.profile.ID) AS DISCARDED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'ERROR_INSTANCE_DUPLICATION',
'ERROR_INSTANCE_BAD_NAMESPACE',
'ERROR_INSTANCE_NO_IDENTITY_MAP',
'ERROR_INSTANCE_NOT_A_TEST_PROFILE',
'ERROR_INSTANCE_INTERNAL'
)
GROUP BY
_experience.journeyOrchestration.journey.versionID
),
SEGMENT_EXPORT_METRICS AS (
SELECT
_experience.journeyOrchestration.journey.versionID AS JOURNEYVERSION_ID,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountTotal) AS TOTAL_EXPORTED_PROFILES_COUNT,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountRealized) AS SUCCESS_EXPORTED_PROFILES_COUNT,
sum(_experience.journeyOrchestration.serviceEvents.segmentExportJob.exportCountFailed) AS FAILED_EXPORTED_PROFILES_COUNT
FROM
journey_step_events
WHERE
_experience.journeyOrchestration.journey.versionID = '<journey-version-id>' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventType = 'segmenttrigger-orchestrator' AND
_experience.journeyOrchestration.serviceEvents.segmentExportJob.eventCode IN (
'INFO_EXPORTJOB_SUCCEEDED',
'ERROR_EXPORTJOB_FAILED'
)
GROUP BY
_experience.journeyOrchestration.journey.versionID
)
SELECT
sum(T2.TOTAL_EXPORTED_PROFILES_COUNT),
sum(T2.SUCCESS_EXPORTED_PROFILES_COUNT),
sum(T2.FAILED_EXPORTED_PROFILES_COUNT),
sum(T1.DISCARDED_PROFILES_COUNT)
FROM
DISCARDED_EXPORTED_PROFILES AS T1,
SEGMENT_EXPORT_METRICS AS T2
WHERE T1.JOURNEYVERSION_ID = T2.JOURNEYVERSION_ID
此查询与上一个查询不同。
它会返回给定历程版本的整体量度,而不考虑可以为其运行的作业(在重复历程的情况下,业务事件会触发利用主题重用的事件)。
配置文件已丢弃,因为区段实现与配置的区段实现不同
Data Lake查询
SELECT DATE(timestamp), _experience.journeyOrchestration.profile.ID
FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = '<journey-version id>' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventType = 'ERROR_SEGMENT_REALISATION_CONDITION_MISMATCH'
示例
SELECT DATE(timestamp), _experience.journeyOrchestration.profile.ID
FROM journey_step_events
where
_experience.journeyOrchestration.journey.versionID = 'a868f3c9-4888-46ac-a274-94cdf1c4159d' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventType = 'ERROR_SEGMENT_REALISATION_CONDITION_MISMATCH'
此查询返回由于区段实现错误而被历程版本丢弃的所有用户档案ID。
因特定配置文件的任何其他原因而丢弃的区段资格事件
Data Lake查询
SELECT DATE(timestamp), _experience.journeyOrchestration.profile.ID, _experience.journeyOrchestration.serviceEvents.dispatcher.projectionID
FROM journey_step_events
where
_experience.journeyOrchestration.profile.ID = '<profile-id>' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventCode = 'discard' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventType = 'ERROR_SERVICE_INTERNAL';
示例
SELECT DATE(timestamp), _experience.journeyOrchestration.profile.ID, _experience.journeyOrchestration.serviceEvents.dispatcher.projectionID
FROM journey_step_events
where
_experience.journeyOrchestration.profile.ID = 'mandee@adobe.com' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventCode = 'discard' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventType = 'ERROR_SERVICE_INTERNAL';
此查询返回由于用户档案的任何其他原因而放弃的所有事件(外部事件/区段资格事件)。
检查是否收到历程的业务事件
Data Lake查询
SELECT DATE(timestamp), count(distinct _id)
FROM journey_step_events
where
_experience.journeyOrchestration.stepEvents.journeyVersionID = '<journey-version-id>' AND
_experience.journeyOrchestration.stepEvents.nodeName = '<node-name-corresponding-to-business-event>' AND
_experience.journeyOrchestration.stepEvents.nodeType = 'start' AND
WHERE DATE(timestamp) > (now() - interval '<last x hours>' hour)
示例
SELECT DATE(timestamp), count(distinct _id)
FROM journey_step_events
where
_experience.journeyOrchestration.stepEvents.journeyVersionID = 'b1093bd4-11f3-44cc-961e-33925cc58e18' AND
_experience.journeyOrchestration.stepEvents.nodeName = 'TEST_MLTrainingSession' AND
_experience.journeyOrchestration.stepEvents.nodeType = 'start' AND
WHERE DATE(timestamp) > (now() - interval '6' hour)
检查配置文件的外部事件是否因未找到相关历程而被丢弃
Data Lake查询
SELECT _experience.journeyOrchestration.profile.ID, DATE(timestamp) FROM journey_step_events
where
_experience.journeyOrchestration.serviceEvents.dispatcher.eventID = '<eventId>' AND
_experience.journeyOrchestration.profile.ID = '<profileId>' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventCode = 'discard' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventType = 'EVENT_WITH_NO_JOURNEY'
示例
SELECT _experience.journeyOrchestration.profile.ID, DATE(timestamp) FROM journey_step_events
where
_experience.journeyOrchestration.serviceEvents.dispatcher.eventID = '515bff852185e434ca5c83bcfc4f24626b1545ca615659fc4cfff91626ce61a6' AND
_experience.journeyOrchestration.profile.ID = 'mandee@adobe.com' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventCode = 'discard' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventType = 'EVENT_WITH_NO_JOURNEY'
检查配置文件的外部事件是否因任何其他原因而被丢弃
Data Lake查询
SELECT _experience.journeyOrchestration.profile.ID, DATE(timestamp), _experience.journeyOrchestration.serviceEvents.dispatcher.eventID, _experience.journeyOrchestration.serviceEvents.dispatcher.eventCode
FROM journey_step_events
where
_experience.journeyOrchestration.profile.ID='<profileID>' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventID='<eventID>' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventCode = 'discard' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventType = 'ERROR_SERVICE_INTERNAL';
示例
SELECT _experience.journeyOrchestration.profile.ID, DATE(timestamp), _experience.journeyOrchestration.serviceEvents.dispatcher.eventID, _experience.journeyOrchestration.serviceEvents.dispatcher.eventCode
FROM journey_step_events
where
_experience.journeyOrchestration.profile.ID='mandee@adobe.com' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventID='81c51be978d8bdf9ef497076b3e12b14533615522ecea9f5080a81c736491656' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventCode = 'discard' AND
_experience.journeyOrchestration.serviceEvents.dispatcher.eventType = 'ERROR_SERVICE_INTERNAL';
通过errorCode检查stateMachine丢弃的所有事件的计数
Data Lake查询
SELECT _experience.journeyOrchestration.serviceEvents.stateMachine.eventCode, COUNT() FROM journey_step_events
where
_experience.journeyOrchestration.serviceEvents.stateMachine.eventType = 'discard' GROUP BY _experience.journeyOrchestration.serviceEvents.stateMachine.eventCode
示例
SELECT _experience.journeyOrchestration.serviceEvents.stateMachine.eventCode, COUNT() FROM journey_step_events
where
_experience.journeyOrchestration.serviceEvents.stateMachine.eventType = 'discard' GROUP BY _experience.journeyOrchestration.serviceEvents.stateMachine.eventCode
检查所有已丢弃的事件,因为不允许重新进入
Data Lake查询
SELECT DATE(timestamp), _experience.journeyOrchestration.profile.ID,
_experience.journeyOrchestration.journey.versionID,
_experience.journeyOrchestration.serviceEvents.stateMachine.eventCode
FROM journey_step_events
where
_experience.journeyOrchestration.serviceEvents.stateMachine.eventType = 'discard' AND _experience.journeyOrchestration.serviceEvents.stateMachine.eventCode='reentranceNotAllowed'
示例
SELECT DATE(timestamp), _experience.journeyOrchestration.profile.ID,
_experience.journeyOrchestration.journey.versionID,
_experience.journeyOrchestration.serviceEvents.stateMachine.eventCode
FROM journey_step_events
where
_experience.journeyOrchestration.serviceEvents.stateMachine.eventType = 'discard' AND _experience.journeyOrchestration.serviceEvents.stateMachine.eventCode='reentranceNotAllowed'
每日活动历程数
Data Lake查询
SELECT DATE(timestamp), count(distinct _experience.journeyOrchestration.stepEvents.journeyVersionID) FROM journey_step_events
WHERE DATE(timestamp) > (now() - interval '<last x days>' day)
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) desc
示例
SELECT DATE(timestamp), count(distinct _experience.journeyOrchestration.stepEvents.journeyVersionID) FROM journey_step_events
WHERE DATE(timestamp) > (now() - interval '100' day)
GROUP BY DATE(timestamp)
ORDER BY DATE(timestamp) desc
查询会返回在定义的周期内每天触发的唯一历程计数。 每天将计算一次触发多天的单个历程。
特定时间内处于特定状态的配置文件数
Data Lake查询
WITH
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS (
SELECT
STEP_EVENTS.timestamp AS TS,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID AS ID
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = '<journey version name>'
),
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS (
SELECT
T1.TS AS TS,
T1.ID AS ID
FROM
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS T1
WHERE
T1.NODE_NAME = '<specific node name>' AND
<filter on time for profile in specific node>
),
INSTANCES_PASSED_IN_NEXT_NODES AS (
SELECT
T1.TS AS TS,
T1.ID AS ID
FROM
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS T1
WHERE
T1.NODE_NAME in (<list of next node names from the specific node>)
),
INSTANCES_PASSED_IN_NODE_NOT_PASSED_IN_NODES AS (
SELECT
distinct T1.ID AS ID
FROM
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS T1
EXCEPT
SELECT
distinct T1.ID AS ID
FROM
INSTANCES_PASSED_IN_NEXT_NODES AS T1
)
SELECT
DATE_FORMAT(T1.TS,'<date pattern>') AS DATETIME,
count(T1.ID) AS INSTANCES_COUNT
FROM
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS T1,
INSTANCES_PASSED_IN_NODE_NOT_PASSED_IN_NODES AS T2
WHERE
T1.ID = T2.ID
GROUP BY
DATETIME
ORDER BY
DATETIME DESC
示例
WITH
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS (
SELECT
STEP_EVENTS.timestamp AS TS,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID AS ID
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = 'Journey20009'
),
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS (
SELECT
T1.TS AS TS,
T1.ID AS ID
FROM
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS T1
WHERE
T1.NODE_NAME = 'slack_bso_tests - test1' AND
T1.TS > (now() - interval '18 hour')
),
INSTANCES_PASSED_IN_NEXT_NODES AS (
SELECT
T1.TS AS TS,
T1.ID AS ID
FROM
INSTANCES_PASSED_IN_ALL_NODES_WITH_DETAILS AS T1
WHERE
T1.NODE_NAME in ('slack_bso_tests - test2')
),
INSTANCES_PASSED_IN_NODE_NOT_PASSED_IN_NODES AS (
SELECT
distinct T1.ID AS ID
FROM
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS T1
EXCEPT
SELECT
distinct T1.ID AS ID
FROM
INSTANCES_PASSED_IN_NEXT_NODES AS T1
)
SELECT
DATE_FORMAT(T1.TS,'yyyy/MM/dd HH:mm') AS DATETIME,
count(T1.ID) AS INSTANCES_COUNT
FROM
INSTANCES_PASSED_IN_NODE_WITH_DETAILS AS T1,
INSTANCES_PASSED_IN_NODE_NOT_PASSED_IN_NODES AS T2
WHERE
T1.ID = T2.ID
GROUP BY
DATETIME
ORDER BY
DATETIME DESC
在特定时间段内退出历程的用户档案数
Data Lake查询
SELECT
DATE_FORMAT(STEP_EVENTS.timestamp,'yyyy/MM/dd HH:mm') AS DATETIME,
count(STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID) AS EXITED_INSTANCES_COUNT
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = '<journey version name>' AND
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus in ('endStep', 'error', 'timedOut', 'cappingError') AND
<timestamp filter>
GROUP BY
DATETIME
ORDER BY
DATETIME DESC
示例
SELECT
DATE_FORMAT(STEP_EVENTS.timestamp,'yyyy/MM/dd HH:mm') AS DATETIME,
count(STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID) AS EXITED_INSTANCES_COUNT
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = 'Journey20009' AND
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus in ('endStep', 'error', 'timedOut', 'cappingError') AND
STEP_EVENTS.timestamp > (now() - interval '22 hour')
GROUP BY
DATETIME
ORDER BY
DATETIME DESC
在特定时间段内有多少具有节点/状态的用户档案退出历程
Data Lake查询
SELECT
DATE_FORMAT(STEP_EVENTS.timestamp,'yyyy/MM/dd HH:mm') AS DATETIME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus AS EXIT_STATUS,
count(STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID) AS EXITED_INSTANCES_COUNT
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = '<journey version name>' AND
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus in ('endStep', 'error', 'timedOut', 'cappingError') AND
<timestamp filter>
GROUP BY
DATETIME, NODE_NAME, EXIT_STATUS
ORDER BY
DATETIME DESC
示例
SELECT
DATE_FORMAT(STEP_EVENTS.timestamp,'yyyy/MM/dd HH:mm') AS DATETIME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.nodeName AS NODE_NAME,
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus AS EXIT_STATUS,
count(STEP_EVENTS._experience.journeyOrchestration.stepEvents.instanceID) AS EXITED_INSTANCES_COUNT
FROM
journey_step_events AS STEP_EVENTS
WHERE
STEP_EVENTS._experience.journeyOrchestration.stepEvents.journeyVersionName = 'Journey20009' AND
STEP_EVENTS._experience.journeyOrchestration.stepEvents.stepStatus in ('endStep', 'error', 'timedOut', 'cappingError') AND
STEP_EVENTS.timestamp > (now() - interval '22 hour')
GROUP BY
DATETIME, NODE_NAME, EXIT_STATUS
ORDER BY
DATETIME DESC