Esempi di query query-examples
In questa sezione sono elencati diversi esempi comunemente utilizzati per eseguire query sugli eventi dei passaggi di Percorso in Data Lake.
Assicurati che i campi utilizzati nelle query abbiano valori associati nello schema corrispondente.
Qual è la differenza tra ID, ID istanza e ID profilo
- id: univoco per tutte le voci evento del passaggio. Due eventi di passaggio diversi non possono avere lo stesso ID.
- instanceId: instanceID è lo stesso per tutti gli eventi di passaggio associati a un profilo all’interno di un’esecuzione di percorso. Se un profilo torna nel percorso, verrà utilizzato un instanceId diverso. Questo nuovo instanceId sarà lo stesso per tutti gli eventi di passaggio dell’istanza reinserita (dall’inizio alla fine).
- profileID: l’identità del profilo che corrisponde allo spazio dei nomi del percorso.
NOTE
Per la risoluzione dei problemi, si consiglia di utilizzare journeyVersionID invece di journeyVersionName durante la query sui percorsi.
Casi d’uso di base/query comuni common-queries
Quanti profili sono stati inseriti in un percorso in un determinato intervallo di tempo
Questa query fornisce il numero di profili distinti che sono entrati nel percorso specificato nell’intervallo di tempo specificato.
Query 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);
Quanti errori si sono verificati in ogni nodo di un percorso specifico per un certo periodo di tempo
Query 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;
Quanti eventi sono stati eliminati da un percorso specifico in un determinato intervallo di tempo
Query 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);
Cosa succede a un profilo specifico in un percorso specifico in un intervallo di tempo specifico
Query Data Lake
Questa query restituisce tutti gli eventi di passaggio e di servizio per il profilo e il percorso specificati per il tempo specificato in ordine cronologico.
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;
Tempo trascorso tra due nodi
Queste query possono essere utilizzate, ad esempio, per stimare il tempo trascorso in un’attività Attendi. Questo ti consente di verificare che l’attività Attendi sia configurata correttamente.
Query 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
Query 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
Controllare i dettagli di un serviceEvent
Il set di dati Eventi passaggio di Percorso contiene tutti gli eventi stepEvents e serviceEvents. stepGli eventi vengono utilizzati nel reporting in quanto si riferiscono ad attività (evento, azioni, ecc.) di profili in un percorso. serviceEvents sono memorizzati nello stesso set di dati e indicano informazioni aggiuntive a scopo di debug, ad esempio il motivo dell’eliminazione di un evento esperienza.
Di seguito è riportato un esempio di query per verificare i dettagli di un serviceEvent:
Query 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;
Errori messaggio/azione message-action-errors
Elenco di ogni errore riscontrato nei percorsi
Questa query ti consente di elencare ogni errore riscontrato 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 volte in cui si sono verificati.
Query basate su profili profile-based-queries
Trovare se un profilo è stato immesso in un Percorso specifico
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 è entrato in un percorso.
Trovare se un profilo ha ricevuto un messaggio specifico
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 con il relativo conteggio richiamato per il profilo selezionato.
Trova tutti i messaggi ricevuti da un profilo negli ultimi 30 giorni
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 con il relativo conteggio richiamato per il profilo selezionato.
Trova tutti i percorsi inseriti da un profilo negli ultimi 30 giorni
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 sottoposto a query è entrato nel percorso.
Numero di profili idonei per un percorso giornaliero
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 che sono entrati nel percorso ogni giorno. Se un profilo immesso tramite più identità, verrà conteggiato due volte. Se è abilitato il rientro, il conteggio dei profili potrebbe essere duplicato in giorni diversi se è rientrato nel percorso in un giorno diverso.
Query relative al pubblico di lettura read-segment-queries
Tempo impiegato per completare un processo di esportazione del pubblico
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, espressa in minuti, tra il momento in cui il processo di esportazione del pubblico è stato messo in coda e il momento in cui è stato finalmente terminato.
Numero di profili scartati dal percorso perché duplicati
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.
Numero di profili eliminati dal percorso a causa di uno spazio dei nomi non valido
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é presentavano uno spazio dei nomi non valido o nessuna identità per tale spazio dei nomi.
Numero di profili scartati dal percorso perché nessuna mappa di identità
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 scartati dal percorso perché manca la mappa di identità.
Numero di profili scartati dal percorso perché il percorso si trovava nel nodo di test e il profilo non era un profilo di test
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 del profilo non è stato impostato su true.
Numero di profili eliminati dal percorso a causa di un errore interno
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 scartati dal percorso a causa di un errore interno.
Panoramica del pubblico di lettura per una determinata versione del percorso
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'
Verranno restituiti tutti gli eventi di servizio relativi alla versione di percorso specificata. Possiamo seguire la catena di operazioni:
- creazione di argomenti
- creazione di processi di esportazione
- terminazione del processo di esportazione (con metriche sui profili esportati)
- cessazione elaborazione lavoratore
Possiamo anche rilevare problemi come:
- errori nella creazione di processi di esportazione o argomento (inclusi timeout nelle chiamate API di esportazione del pubblico)
- processi di esportazione che possono essere bloccati (nel caso in cui per una determinata versione del percorso, non si verifichino eventi relativi alla chiusura del processo di esportazione)
- problemi del lavoratore, se abbiamo ricevuto l'evento di cessazione del processo di esportazione ma nessun lavoratore ne elabora l'elaborazione uno
IMPORTANTE: se questa query non restituisce alcun evento, la causa potrebbe essere uno dei seguenti:
- la versione del percorso non ha raggiunto la pianificazione
- se la versione del percorso deve aver attivato il processo di esportazione chiamando l’orchestratore, si è verificato un errore nel flusso a monte: problema di implementazione del percorso, evento di business o problema con il modulo di pianificazione.
Errori di lettura del pubblico per una determinata versione del percorso
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'
)
Ottieni stato elaborazione processo di esportazione
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, significa che:
- si è verificato un errore durante la creazione dell’argomento o del processo di esportazione
- il processo di esportazione è ancora in esecuzione
Ottieni metriche sui profili esportati, compresi gli scarti e le metriche dei processi di esportazione per ogni processo di esportazione
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
Ottieni metriche aggregate (processi di esportazione e scarti del pubblico) su tutti i processi di esportazione
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 dalla precedente.
Restituisce le metriche complessive per una determinata versione del percorso, indipendentemente dai processi che possono essere stati eseguiti per essa (in caso di percorsi ricorrenti, eventi di business attivati da quelli che sfruttano il riutilizzo degli argomenti).
Query relative alla qualifica del pubblico segment-qualification-queries
Profilo scartato a causa di una realizzazione del pubblico diversa da quella configurata
Query 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'
Esempio
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'
Questa query restituisce tutti gli ID profilo scartati dalla versione del percorso a causa di una realizzazione errata del pubblico.
Eventi di qualificazione del pubblico eliminati per qualsiasi altro motivo per un profilo specifico
Query 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';
Esempio
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';
Questa query restituisce tutti gli eventi (eventi esterni/eventi di qualificazione del pubblico) che sono stati eliminati a causa di qualsiasi altro motivo per un profilo.
Query basate su eventi event-based-queries
Verifica se è stato ricevuto un evento di business per un percorso
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)
Controlla se un evento esterno di un profilo è stato eliminato perché non è stato trovato alcun percorso correlato
Query 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'
Esempio
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'
Controlla se un evento esterno di un profilo è stato scartato per qualsiasi altro motivo
Query 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';
Esempio
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';
Controllare il conteggio di tutti gli eventi eliminati da stateMachine tramite errorCode
Query 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
Esempio
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
Controlla tutti gli eventi scartati perché il rientro non era consentito
Query 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'
Esempio
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'
Query comuni basate su percorso journey-based-queries
Numero di percorsi attivi giornalieri
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. Un singolo percorso che si attiva su più giorni verrà conteggiato una volta al giorno.
Query su istanze di percorso journey-instances-queries
Numero di profili in uno stato specifico per un tempo specifico
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
Quanti profili sono usciti dal percorso nello specifico periodo di tempo
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
Quanti profili sono usciti dal percorso nello specifico periodo di tempo con nodo/stato
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
recommendation-more-help
b22c9c5d-9208-48f4-b874-1cefb8df4d76