1

I'm trying to find an open rate for a short series of dates. I was given subscriber keys and thought it would be best to create a query.

I figured if I could create a data extension then run the query against the data extension to populate whether or not they opened an email on either of the send dates.

I've drawn a blank of what to do and where to go. Help would be greatly appreciated. Sorry if this seems vague.

EDIT: I used a _Open combined with a Opener_FLAG to get who opened the email. This has now evolved into getting the DATE that the customer opened the email. I'm hitting a wall with that now.

Once again,any and all help would be appreciated.

3
  • Hi Eric, are you trying to find the "open rate" (% of subscribers who opened the email), or do you want to build a Sendable Data Extension to re-target customers who opened the first email/s? Mar 6, 2017 at 22:58
  • Have you considered using data view _open with query? Mar 8, 2017 at 10:38
  • I actually have a list of how many people opened. My problem has evolved into when they first opened the email. I used a _Open query and thought I could modify it to produce a date as well, but haven't gotten anywhere.
    – Eric L
    Mar 8, 2017 at 17:19

1 Answer 1

0

I figured out how to do this. I used an Open flag, by referencing [_Open] as a boolean. I was able to get the date and time of when the customer got the date by referencing the name of the email.

Looked like:

Select distinct ct.subscriberkey, 'Y' as [Opener_Flag] 
From [Data Extension name with subkeys] ct with(nolock)
Join [_Open] op with(nolock) on ct.subscriberkey = op.subscriberkey
Join [_job] jb with(nolock) on op.jobid = jb.jobid
Where ct.[Opener_Flag] is null
and jb.[EmailName] like '%Email Name%'

Change the first line to: Select distinct ct.subscriberkey, EventDate as [Date_Opened]

and "Opener_Flag" to "Date_Opened" and you get the date when the customer opened their email!

I hope this can help someone, cause it was a hard one to figure out :D

You must log in to answer this question.

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