Query on a rolling schedule | Community
Skip to main content
J
juliak39390008
Level 2
November 6, 2018
Solved

Query on a rolling schedule

  • November 6, 2018
  • 3 replies
  • 3707 views

Hi All,

I am trying to develop a query that delivers emails on a rolling basis for event campaigns that are running in parallel. Here are the details:

Need to query off of an "Event date" within a data table and send out an email with dynamic content

  • 30 days before the event date- to a certain segment of event ticket holders
  • 14 days before the event date- to a certain segment of event ticket holders
  • 1 day before the event date- to all event ticket holders (dynamic content based on type of ticket holder as well)
  • 1 day after the event date- to all event attendees

What is the best way to set up a rolling schedule based off an event table? There are about 30 events that happen within a time period and each event has the 4 emails that need to go out.

Additionally, need to pull the event name into the email to indicate the images to include in the email.

Any thoughts/help is greatly appreciated!

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 juliak39390008

Created a workflow for the T-30 & T-14 emails. Have 2 queries in the workflow- first one pulls the T-30 until an event and then also anyone who bought a ticket for that event.

Formula for  T-14 query: @eventdatetime >= DateOnly(DaysAgo(-14)) AND @eventdatetime < DateOnly(DaysAgo(-15))

Did the same for all the others, except a positive 1 and 2 for the T+1 event.

Hope that helps.

3 replies

vraghav
Adobe Employee
vraghavAdobe Employee
Adobe Employee
November 6, 2018

Hi juliak39390008,

The best way is to define independent workflows. Soe of the explanation below will hold true if you are on Adobe Campaign Classic.

1. Create a Campaign

2. Develop the first workflow which targets people 30 days before the event. This workflow should have a scheduler which runs it every day.

3. Develop the second workflow to target people 14 days before the event. Should have a scheduler to run every day

4. Third workflow to target event ticket holder one day before the event. Should have a scheduler to run every day

5. Develop final workflow to target the attendees one day after the event. Scheduler to run every day.

You can add resilience to these workflows by maintaining an OPTION per workflow, storing the date when a particular workflow ran successfully. Imagine one of these workflows failed on Saturday morning. Your team found it out on Monday and if you restart the workflow it will not target people who were supposed to be contacted on Saturday and Sunday.

If you store the last successful date, On Monday it will pull all eligible people included from Saturday, Sunday and Monday. Please design your logic carefully.

I'm guessing you will have many to many link between Event and Recipient. So start all your queries from  Event and then change the dimension to Recipient.

Regards,
Vipul

El_Loco
El_Loco
Level 5
November 7, 2018

I’d love to have a chat about how you achieve this if you are using Campaign Standard as we are in a similar position currently.

J
juliak39390008AuthorAccepted solution
Level 2
November 15, 2018

Created a workflow for the T-30 & T-14 emails. Have 2 queries in the workflow- first one pulls the T-30 until an event and then also anyone who bought a ticket for that event.

Formula for  T-14 query: @eventdatetime >= DateOnly(DaysAgo(-14)) AND @eventdatetime < DateOnly(DaysAgo(-15))

Did the same for all the others, except a positive 1 and 2 for the T+1 event.

Hope that helps.

Terms & ConditionsAccessibility statement

Loading footer...