0

I'm attempting to set up a drip campaign that will push recipients forward in the process dependent on whether or not they open an email. Essentially there will be three tiers of emails. Each tier will contain three emails. Each email will be automatically sent 15 days after the last one. But, during the process, if a recipient opens one of the emails they will jump ahead to the next tier. So if i opened the 2nd email in the 1st tier i would skip the 3rd email in tier 1 and jump ahead to the 1st email in tier 2.

The problem I'm having is getting info on opens and adding that back to the data extension that I plan on sending the list to.

Improve this question
3
  • Welcome to SFSE. Please post a specific question that you'd like the community to help solve. What queries have you written so far?
    – Adam Spriggs
    Commented Sep 30, 2015 at 15:54
  • Please look over the System Data View schemas. If you need to factor any subscriber activity into a campaign, you'll find the info in these data views.
    – Adam Spriggs
    Commented Oct 2, 2015 at 17:02
  • Sorry, i'm very new to this and didn't know to even look at queries. That was very helpful and I got it up and running now. Thank you!
    – fandresu
    Commented Oct 5, 2015 at 12:57

1 Answer 1

Reset to default
1

I realize it's been 7 months since the question was asked and you appear to have resolved your own issue but I came across a very similiar situation recently where a 3-Tier Drip Campaign process was necessary and thought i would share my workflow.

It is a 3 level membership daily drip campaign where 3 emails exist in each tier and those 3 emails are sent every 15 days. Initially, all recipients are placed on the Tier 1 data extension and immediately started on the Tier 1 - Email 1. At any point during the Tier 1 series if they open any of those emails sent they are immediately promoted to Tier 2 where they immediately start Email 1 of the Tier 2 series and get suspended from receiving emails from Tier 1. Ofcourse, if they open any email on Tier 2 series they are immediately promoted to Tier 3 and will receive Email 1 there and will get suspended from receiving emails from Tier 1 and Tier 2. Once on Tier 3 series they will receive all 3 emails (unless they should unsubscribe).

Here is what that looks like. 

Tier 1 - Bronze Member
- Email 1 (day 0)
- Email 2 (day 15)
- Email 3 (day 30)
Tier 2 - Silver Member
- Email 1 (day 0)
- Email 2 (day 15)
- Email 3 (day 30)
Tier 3 - Gold Member
- Email 1 (day 0)
- Email 2 (day 15)
- Email 3 (day 30)

Part I - Setting up the Data Extensions

(a) I setup 3 data extensions for holding the recipient groups:

Tier 1 - Recipients
Tier 2 - Recipients
Tier 3 - Recipients

I setup 2 data extensions for exclusions lists (*optionally):

Tier 1 Exclusions - Excluding Tier 2 and Tier 3
Tier 2 Exclusions - Excluding Tier 3

note: The Tier 3 Recipients will have no exclusions since there is no higher level that they'll get promoted to once they reach a Gold membership level.

*optionally: It's possible to do exclusions with just the Tier 2 and Tier 3 data extensions directly but I like to make it super clear what is being excluded when I add these in the User Initiated sends that will be created later.

I setup 9 data extensions for the actual target lists of what each email across the 3 series should be sending to. These were put into 3 separate sub folders named - Tier 1 Emails, Tier 2 Emails, Tier 3 Emails

Tier 1 - Recipients - Send Email 1
Tier 1 - Recipients - Send Email 2
Tier 1 - Recipients - Send Email 3

Tier 2 - Recipients - Send Email 1
Tier 2 - Recipients - Send Email 2
Tier 2 - Recipients - Send Email 3

Tier 3 - Recipients - Send Email 1
Tier 3 - Recipients - Send Email 2
Tier 3 - Recipients - Send Email 3

For all 14 of the data extensions above, the data schema for these are:

[emailAddress] (datatype as emailaddress - 254 - primary checked)
[Opt-in Date] (datetype as date - nullable checked) - Use current date as default

Part II - Setting up the Import Activity

I setup a import activity using a csv file with email addresses of all of the recipients that I wish to onboard to the [Tier 1 - Recipients] data extension with the Update Type: Add and Update.

If the initial membership data is coming from a web collect or smart capture form that directly passes into a list or data extension with an Opt-In Date data time stamp already present then this step could be eliminated.

Part III - Setting up the Emails

tier1_email1
tier1_email2
tier1_email3
tier2_email1
tier2_email2
tier2_email3
tier3_email1
tier3_email2
tier3_email3

Setup the above emails. Later in Part V, you will setup the corresponding User-initiated sends.

Part IV - Setting up the Queries to pull the recipients into the designated data extensions.

With the initial data in the [Tier 1 - Recipients] data extension we now need the queries to pull the data across to the respective locations. For each of the data extensions, the following queries are setup:

(a) Query name: Promote from Tier 1 to Tier 2 Recipients

target data extension: Tier 2 - Recipients

Select sb.emailaddress as 'EmailAddress'
FROM _open o
INNER JOIN _job j ON o.jobid = j.jobid
INNER JOIN ent._subscribers sb on o.subscriberid = sb.subscriberid
INNER JOIN [Tier 1 - Recipients] t1 on sb.emailaddress = t1.emailAddress
where j.emailname IN ('tier1_email1','tier1_email2','tier1_email3')
and convert(date, o.eventdate) >= CONVERT(varchar(10), DATEADD(day, 0, GETDATE()), 101) 
and o.isunique = 1
GROUP BY sb.emailaddress
HAVING count(sb.emailaddress) > 0

update type: Update

(b) Query name: Promote from Tier 2 to Tier 3 Recipients

target data extension: Tier 3 - Recipients

Select sb.emailaddress as 'EmailAddress'
FROM _open o
INNER JOIN _job j ON o.jobid = j.jobid
INNER JOIN ent._subscribers sb on o.subscriberid = sb.subscriberid
INNER JOIN [Tier 2 - Recipients] t2 on sb.emailaddress = t2.emailAddress
where j.emailname IN ('tier2_email1','tier2_email2','tier2_email3')
and convert(date, o.eventdate) >= CONVERT(varchar(10), DATEADD(day, 0, GETDATE()), 101) 
and o.isunique = 1
GROUP BY sb.emailaddress
HAVING count(sb.emailaddress) > 0

update type: Update

(c) Query name: Tier 1 Exclusions - Excluding Tier 2 and Tier 3

Select [emailAddress] as 'EmailAddress'
FROM [Tier 2 - Recipients]
UNION
Select [emailAddress] as 'EmailAddress'
FROM [Tier 3 - Recipients]

Update Type: Overwrite

(d) Query name: Tier 2 Exclusions - Excluding Tier 3

Select [emailAddress] as 'EmailAddress'
FROM [Tier 3 - Recipients]

Update Type: Overwrite

(e) Tier 1 - Recipients - Send Email 1

Select [emailAddress] as 'EmailAddress'
FROM [Tier 1 - Recipients]
WHERE [Opt-In Date] >= CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 101) 
and [Opt-In Date]  < CONVERT(varchar(10), DATEADD(day, 0, GETDATE()), 101)

Update Type: Overwrite

(f) Tier 1 - Recipients - Send Email 2

Select [emailAddress] as 'EmailAddress'
FROM [Tier 1 - Recipients]
WHERE [Opt-In Date] >= CONVERT(varchar(10), DATEADD(day, -15, GETDATE()), 101) 
and [Opt-In Date]  < CONVERT(varchar(10), DATEADD(day, -14, GETDATE()), 101)

Update Type: Overwrite

(g) Tier 1 - Recipients - Send Email 3

Select [emailAddress] as 'EmailAddress'
FROM [Tier 1 - Recipients]
WHERE [Opt-In Date] >= CONVERT(varchar(10), DATEADD(day, -30, GETDATE()), 101) 
and [Opt-In Date]  < CONVERT(varchar(10), DATEADD(day, -29, GETDATE()), 101)

Update Type: Overwrite

(h) Tier 2 - Recipients - Send Email 1

Select [emailAddress] as 'EmailAddress'
FROM [Tier 2 - Recipients]
WHERE [Opt-In Date] >= CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 101) 
and [Opt-In Date]  < CONVERT(varchar(10), DATEADD(day, 0, GETDATE()), 101)

Update Type: Overwrite

(i) Tier 2 - Recipients - Send Email 2

Select [emailAddress] as 'EmailAddress'
FROM [Tier 2 - Recipients]
WHERE [Opt-In Date] >= CONVERT(varchar(10), DATEADD(day, -15, GETDATE()), 101) 
and [Opt-In Date]  < CONVERT(varchar(10), DATEADD(day, -14, GETDATE()), 101)

Update Type: Overwrite

(j) Tier 2 - Recipients - Send Email 3

Select [emailAddress] as 'EmailAddress'
FROM [Tier 2 - Recipients]
WHERE [Opt-In Date] >= CONVERT(varchar(10), DATEADD(day, -30, GETDATE()), 101) 
and [Opt-In Date]  < CONVERT(varchar(10), DATEADD(day, -29, GETDATE()), 101)

Update Type: Overwrite

(k) Tier 3 - Recipients - Send Email 1

Select [emailAddress] as 'EmailAddress'
FROM [Tier 3 - Recipients]
WHERE [Opt-In Date] >= CONVERT(varchar(10), DATEADD(day, -1, GETDATE()), 101) 
and [Opt-In Date]  < CONVERT(varchar(10), DATEADD(day, 0, GETDATE()), 101)

Update Type: Overwrite

(l) Tier 3 - Recipients - Send Email 2

Select [emailAddress] as 'EmailAddress'
FROM [Tier 3 - Recipients]
WHERE [Opt-In Date] >= CONVERT(varchar(10), DATEADD(day, -15, GETDATE()), 101) 
and [Opt-In Date]  < CONVERT(varchar(10), DATEADD(day, -14, GETDATE()), 101)

Update Type: Overwrite

(m) Tier 3 - Recipients - Send Email 3

Select [emailAddress] as 'EmailAddress'
FROM [Tier 3 - Recipients]
WHERE [Opt-In Date] >= CONVERT(varchar(10), DATEADD(day, -30, GETDATE()), 101) 
and [Opt-In Date]  < CONVERT(varchar(10), DATEADD(day, -29, GETDATE()), 101)

Update Type: Overwrite

Part V - Setting up the 9 User-Initiated Sends

Setup the following User-initiated sends

a) Tier 1 - Email 1

Email: tier1_email1
Target Data Extension: Tier 1 - Recipients - Send Email 1
Exclusion List: Tier 1 Exclusions - Excluding Tier 2 and Tier 3

b) Tier 1 - Email 2

Email: tier1_email2
Target Data Extension: Tier 1 - Recipients - Send Email 2
Exclusion List: Tier 1 Exclusions - Excluding Tier 2 and Tier 3

c) Tier 1 - Email 3

Email: tier1_email3
Target Data Extension: Tier 1 - Recipients - Send Email 3
Exclusion List: Tier 1 Exclusions - Excluding Tier 2 and Tier 3

d) Tier 2 - Email 1

Email: tier2_email1
Target Data Extension: Tier 2 - Recipients - Send Email 1
Exclusion List: Tier 2 Exclusions - Excluding Tier 3

e) Tier 2 - Email 2

Email: tier2_email2
Target Data Extension: Tier 2 - Recipients - Send Email 2
Exclusion List: Tier 2 Exclusions - Excluding Tier 3

f) Tier 2 - Email 3

Email: tier2_email3
Target Data Extension: Tier 2 - Recipients - Send Email 3
Exclusion List: Tier 2 Exclusions - Excluding Tier 3

g) Tier 3 - Email 1

Email: tier3_email1
Target Data Extension: Tier 3 - Recipients - Send Email 1
Exclusion List: (none)

h) Tier 3 - Email 2

Email: tier3_email2
Target Data Extension: Tier 3 - Recipients - Send Email 2
Exclusion List: (none)

i) Tier 3 - Email 3

Email: tier3_email3
Target Data Extension: Tier 3 - Recipients - Send Email 3
Exclusion List: (none)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .