Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Query for delivery logs and account activation date

Avatar

Level 2

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 ?

 

1 Accepted Solution

Avatar

Correct answer by
Level 5

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). 

View solution in original post

3 Replies

Avatar

Employee Advisor

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.

David__Garcia_0-1642633334876.png

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.

 

 

Avatar

Level 2

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?

Avatar

Employee Advisor

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...)

Avatar

Correct answer by
Level 5

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). 

Avatar

Level 2

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. 

Avatar

Level 5

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.

 

dloyd_0-1643209207437.png

 

Opens and clicks are on a completely different table.

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

dloyd_1-1643209329592.png

 

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

Avatar

Level 2

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 ?

Avatar

Administrator

Hi @vinay16,

You can refer to the following links

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!