Query for filtering recipient

[vinay16]

Hi everyone,

I'm really new to ACM and trying to understand few things. Scenario is I want to configure the Taregting query block as per below condition

1. Recipient should have registered within 30 days ( I have used registration date with "on or after" and "30 days before")

2. Recipient should be active ( this is also done)

Issue is, today, I want to get only recipient who activated his/her account yesterday. I have tried to use "Event Date" - "on or after" and "1 day before". I'm getting recipients who registered and activated account today. 

1. What should I to have only recipients who activated their account yesterday ? Does it require to change condition in Event Date ? or any other condition ? 

2. Is event date associated with account activation date ?

Can anyone please help ?

[David_Loyd]

Have you double checked you have accounts created for the past 30 days? Can you see that in the data schema of the table? Unless someone created that specific data field for your instance you may need to use a different field. The date a recipient profile is created is called creation date. Event dates are usually open or click dates (of an email).

Is Event Date you are using on the trackinglogsRcp? Or is it a custom schema? What Event Date are you using?

When using the DaysAgo() function be aware it is using the current timestamp hh:mm:ss. Meaning if you are looking for stuff from yesterday, you are only pulling back the last 24 hours to the hour and min. If you want everything from yesterday at 00:01 use the DateOnly() function.

See the timestamps example below.

[vinay16]

DateOnly(@eventDate) equal to DateOnly(DaysAgo(3)) worked for me. I'm not sure what is the eventDate? Is it the date on which recipient was added to a segment ?

On a separate note, I run first workflow at 4pm and I want to run second workflow on next day at 3pm but only recipients who got email from first workflow should receive email from second workflow.

I have used all necessary conditions along with delivery recipient code. However, I'm confused about event date of recipient delivery logs. 

1 Should it be "on or after" as operator and "DaysAgo(1) as value for expression event date of recipient delivery logs(@eventDate) or DateOnly(event date of recipient delivery logs(@eventDate) equal to DateOnly(DaysAgo(1))?

2 Can I run second workflow at 3pm - first workflow time is 4pm. or should there be any changes based on above condition ?

2 

[David_Loyd · Accepted solution]

Delivery-> Delivery Scheduling -> Contact Date = is the date the email was sent to the recipient.

broadLogRcp -> Event Date = is the date of an Open or a Click. 

broadLogRcp -> Url - > Type = Lets you pick between Opens or Clicks

If you use DateOnly(@eventDate) equal to DateOnly(DaysAgo(3)), this is only getting you that 1 day 3 days ago. If you want all records in the last 3 days DateOnly(@eventDate) is on or after DateOnly(DaysAgo(3))

If you are not sure of the event date, that can be an issue. You could potentially be using that date field incorrectly.

If you want to query people who have opened yesterdays email it would be like

1.Delivery Internal Name is equal to DM123456

2.Tracking logs exist such that 

  a.delivery/URL/Type = Open

  b.logDate =  DateOnly(@eventDate) is on or after DateOnly(DaysAgo(1))

[vinay16]

Thank you so much for detailed reply and clarification.

I understood all the points.

For DateOnly(DaysAgo(1)) as you mentioned it will take data from yesterday's 00:01am. so it is like all the data from yesterday ? i.e. Yesterday's 00:01am to 11:59pm ? I'm using equal to as an operator.. 

[David_Loyd]

DateOnly(@eventDate) is on or after DateOnly(DaysAgo(1)) 

-If today is the 18th, above should get all records from 17th starting at 00:00 TO the moment of the query being ran (today the 18th at 10:26am).

DateOnly(@eventDate) is equal to DateOnly(DaysAgo(1)) 

-if today is the 18th, above would only get you records that happened on the 17th (00:00-23:59)

[vinay16]

Thank you for explanation. This cleared many things.

I just witnessed weird issue. Not sure why it happened.

When I ran workflow at 11, I was getting 23 results and now, I'm getting 2 results only. I haven't made changes to any conditions.

Condition is DateOnly(@eventdate) is equal to DateOnly(DaysAgo(1)). 

What might be reason behind low results now as compared to previous one? Nothing has been changed with regards to recipients. 

Is there anyway to track such issue? and reason behind this ?

[vinay16]

DateOnly(@eventdate) is equal to DateOnly(DaysAgo(0))  -- this is giving me results now. I ran this and recipients whose accounts were created yesterday are being shown based on above query.

I'm not sure why. Shouldn't it be DaysAgo(1) for that one ? I noticed that event date of few records were updated to today's date. So this might have created this issue. 

On a separate note, 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 ?