Query for delivery logs and account activation date

[vinay16]

I have been trying to filter the records based on few conditions and I'm stuck with 2 different things.

1. DateOnly ( event date of recipient delivery logs)  equal to DateOnly(DaysAgo(1))  and delivery code equal to "xyz" -- will this give records who were sent email labelled as "xyz" yesterday ? or this event date of recipient delivery logs also gets updated ? 

2. Is there anyway to get account activation date of a recipient? As of now, I can see registration date, creation date. 
       -- I have tried to search if I can pull off activation date. I can't find that entry

       -- Alternative approach, I found that activation email is sent out to the recipient but that one is transactional email. I have tried to fetch records based on "label" of that email but couldn't fetch any records. 

Can anyone help for these 2 things ?

[david--garcia]

1- Will fetch deliveries whose delivery code is xyz (not delivery label), so if multiple deliveries happen to be configured with the same delivery code, it will pull records for them also.

2- There is no such thing as recipient activation date ootb, unless you add it as a custom field and setup a custom process to update the flag based on a particular action such as email confirmation, double optin, etc etc.

[vinay16]

Thank you for reply.

Can you please confirm what will be the outcome of below?

1 Recipient Delivery logs (@broadLog) exists such as

        DateOnly(event date of recipient delivery logs) equal to DateOnly(DaysAgo(1))

        Delivery code equal to "xyz"

-- Will this give list of people who received email with delivery code "xyz" 1 day ago or will this give list of people who either opened or clicked on email with delivery code "xyz" 1 day ago?

[vinay16]

@david--garcia Can you please share your expertise for above queries ?

[david--garcia]

I can only suggest that you try running the query and see what you get, only by trying, trial and error, you will learn these different queries. perhaps try on a staging environment, learn the schema attributes available in the delivery schema and look at the list of available functions (https://experienceleague.adobe.com/docs/campaign-standard/using/managing-processes-and-data/filterin...)

[David_Loyd · Accepted solution]

1. EventDate is the date of the open or click. The contactDate is the day the email was SENT to the recipient. Try DateOnly ( contactDate )  equal to DateOnly(DaysAgo(1)) AND Delivery Label == "Delivery Label Name"

2. Transactional email logs are found on the archive event delivery logs table (nms:broadLogEventHisto). 

[vinay16]

Thank you for reply.

So even if I use

Recipient Delivery logs (@broadLog) exists such as

        DateOnly(event date of recipient delivery logs) equal to DateOnly(DaysAgo(1))

        Delivery code equal to "xyz"

it depends on whether recipient has opened or clicked the email and it won't give the actual delivery date for comparison ? I need to use contact date ?

Sorry for asking his again. I'm just trying to understand the difference. 

[vinay16]

@David_Loyd Can you please share your expertise for above queries ?

[David_Loyd]

Contact date is the date the actual delivery was SENT to the person.

Event Date (on broadlogRcp) is just the date of the status update.

Opens and clicks are on a completely different table.

Event date on this table is the date of the open or click.

I'd suggest spend time reading through the different data schemas and the data structure.

[vinay16]

Thank you so much for sharing insight. I would definitely want to read through this.

Just 2 questions:

1. By status update, you mean any interaction with the email - it can be open or click - right or status update means something else - can you please share?

2. Can you please point me to the location / page / url for reading through the data schemas and data structures ?

[Sukrity_Wadhwa]

Hi @vinay16,

You can refer to the following links

• Data schemas
• Structure of a data schema
• Schema structure

You may also search any other topics that you might need a guideline document for in the search bar on top of these URLs.

Thanks!

[vinay16]

Thank you so much.