In questa sezione sono elencati diversi esempi comunemente utilizzati per eseguire query sugli eventi dei passaggi del Percorso in Data Lake.
Questa query ti consente di elencare ogni errore rilevato nei percorsi durante l’esecuzione di un messaggio/azione.
Query 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
Esempio
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
Questa query restituisce tutti i diversi errori che si sono verificati durante l'esecuzione di un'azione in un percorso insieme al conteggio del numero di errori che si sono verificati.
Query 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>'
Esempio
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'
Il risultato deve essere maggiore di 0. Questa query restituisce il numero esatto di volte in cui un profilo ha inserito un percorso.
Metodo 1: se il nome del messaggio non è univoco nel percorso (viene utilizzato in più posizioni).
Query 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>'
Esempio
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'
Il risultato deve essere maggiore di 0. Questa query indica solo se l’azione del messaggio è stata eseguita correttamente sul lato percorso.
Metodo 2: se il nome del messaggio è univoco nel percorso.
Query 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>'
Esempio
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'
La query restituisce l’elenco di tutti i messaggi insieme al relativo conteggio richiamato per il profilo selezionato.
Query 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
Esempio
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
La query restituisce l’elenco di tutti i messaggi insieme al relativo conteggio richiamato per il profilo selezionato.
Query 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
Esempio
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
La query restituisce l’elenco di tutti i nomi di percorso insieme al numero di volte in cui il profilo interrogato è entrato nel percorso.
Query 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
Esempio
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
La query restituisce, per il periodo definito, il numero di profili immessi ogni giorno nel percorso. Se un profilo è stato inserito tramite più identità, verrà conteggiato due volte. Se è abilitata la reintroduzione, il conteggio dei profili potrebbe essere duplicato in giorni diversi se è stato reinserito nel percorso in un giorno diverso.
Query 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;
Esempio
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;
La query restituisce la differenza di tempo, in minuti, tra il momento in cui il processo di esportazione del segmento è stato messo in coda e il momento in cui è terminato.
Query 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'
Esempio
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'
La query restituisce tutti gli ID profilo che sono stati scartati dal percorso perché erano duplicati.
Query 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'
Esempio
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'
La query restituisce tutti gli ID profilo scartati dal percorso perché il relativo spazio dei nomi non è valido o non è presente alcuna identità per tale spazio dei nomi.
Query 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'
Esempio
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'
La query restituisce tutti gli ID profilo che sono stati scartati dal percorso perché la mappa di identità era mancante.
Query 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'
Esempio
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'
La query restituisce tutti gli ID profilo che sono stati scartati dal percorso perché il processo di esportazione è stato eseguito in modalità di test ma l'attributo testProfile non aveva il profilo impostato su true.
Query 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'
Esempio
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'
La query restituisce tutti gli ID profilo che sono stati scartati dal percorso a causa di un errore interno.
Query 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'
Restituisce tutti gli eventi di servizio relativi alla versione di percorso specificata. Possiamo seguire la catena di operazioni:
È inoltre possibile rilevare problemi quali:
IMPORTANTE: se non viene restituito alcun evento da questa query, potrebbe essere dovuto a uno dei motivi seguenti:
Query 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'
)
Query 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'
)
Se non viene restituito alcun record, ciò significa che:
Query 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
Query 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
Questa query è diversa da quella precedente.
Restituisce le metriche complessive per una determinata versione di percorso, indipendentemente dai processi che possono essere stati eseguiti per essa (nel caso di percorsi ricorrenti, gli eventi aziendali attivati quelli che sfruttano il riutilizzo degli argomenti).
Query 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.dispatcher.eventCode = 'ERROR_INSTANCE_WRONG_SEGMENT_REALIZATION'
Esempio
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.dispatcher.eventCode = 'ERROR_INSTANCE_WRONG_SEGMENT_REALIZATION'
Questa query restituisce tutti gli ID profilo che sono stati scartati dalla versione del percorso a causa di una realizzazione del segmento errata.
Query 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)
Esempio
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)
Query 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
Esempio
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
La query restituisce, per il periodo definito, il numero di percorsi univoci attivati ogni giorno. Viene conteggiato un singolo percorso che si attiva su più giorni una volta al giorno.
Query 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
Esempio
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
Query 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
Esempio
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
Query 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
Esempio
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