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 

    David_Loyd
    Adobe Employee
    David_LoydAdobe EmployeeAccepted solution
    Adobe Employee
    January 18, 2022

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

      Terms & ConditionsAccessibility statement

      Loading footer...