Unique and last journeyID - ajo_entity_dataset

Question

Hi, I’m trying to build a master table of campaigns and journeys (including campaign ID/name and journey ID/name) using the ajo_entity_dataset.

However, I’ve run into an issue: there’s a journey whose name we recently updated (the current journey has the "v2" name, while the other two entries show the old names). Unfortunately, I can’t find a reliable way to filter for only the current journey name.

The only timestamp field I found is lastModifiedAt, but it shows the same timestamp for all three records, so it doesn’t help me isolate the latest version.

Is there another field available that would let me keep only the current journey with its updated name?

Thanks for your help!

Silvio6 · Accepted Answer

SOLVED: There's a hidden field called "_acp_system_metadata.ingestTime" where I can filter by date of modification:

SELECT
_acp_system_metadata.ingestTime AS last_ingest_time,
_experience.customerJourneyManagement.entities.journey.journeyNameAndVersion
FROM
ajo_entity_dataset
WHERE
_experience.customerJourneyManagement.entities.journey.journeyVersionId = 'xxxxxxxxxxxxxxxxxxxxx'
ORDER BY
_acp_system_metadata.ingestTime DESC
LIMIT 1

FilipeFreitas · Answer

HI Silvio6,

Let me tell you I faced a similar situation. I will share the approach I used, which I am aware it is not 100% correct, but it worked for me.

First, one quick note: if you cast any timestamp field as string, you will see the full timestamp, and this applies to lastModifiedAt. In other words, even when it looks like the timestamps are all the same, in fact, very often, they are not. And I say very often because yes, sometimes they will be all the same.

This said, I use a window function partitioned by JourneyVestionId order by lastModifiedAt to retrieve the most recent journeyName. I will share the snippet code here. Note that I left the where clause commented out, so the query reflects the shared screenshots. But it is all about filtering out rowNum = 1.

WITH journey_name AS(
    SELECT
      _experience.customerJourneyManagement.entities.journey.journeyVersionID
      , _experience.customerJourneyManagement.entities.journey.journeyName
      , _experience.customerJourneyManagement.entities.timestamp.lastModifiedAt
      , CAST(_experience.customerJourneyManagement.entities.timestamp.lastModifiedAt AS STRING) AS lastModifiedAtStr
      , ROW_NUMBER() OVER (
          PARTITION BY _experience.customerJourneyManagement.entities.journey.journeyVersionID
          ORDER BY _experience.customerJourneyManagement.entities.timestamp.lastModifiedAt DESC) AS row_num
    FROM ajo_entity_dataset
)

SELECT * FROM journey_name
WHERE row_num = 1

Note that strings timestamps are different:

But they might be the same

I hope this helps you to some extent - until you find a definite solution.

Best Regards,

Filipe C. Freitas

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded