Query for filtering recipient | Community
Skip to main content
V
vinay16
Level 4
January 17, 2022
Solved

Query for filtering recipient

  • January 17, 2022
  • 1 reply
  • 2284 views

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 ?

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by David_Loyd

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

1 reply

David_Loyd
Adobe Employee
David_LoydAdobe Employee
Adobe Employee
January 17, 2022

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.

 

    V
    vinay16Author
    Level 4
    January 17, 2022

    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 

    V
    vinay16Author
    Level 4
    January 18, 2022

    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)

     


    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 ?

    Terms & ConditionsAccessibility statement

    Loading footer...