Query based on click of certain link but within time frame

Question

I'm looking for some help on writing a query where I want to find all of the email addresses who clicked LINK X during the past week and upload them into a data extension.

Currently I have:

Select SubscriberKey, EventDate
From _click
Where URL = 'LINK X'

However, with the "EventDate" portion, it takes anyone who has clicked the link before the time the query is run, when I need it to run only for a given time frame (the past week).

Any thoughts here? Thank you in advance!

Answer 1

Based on your question it appears that you are interested in using SQL activity in ExactTarget to "bookend the reporting date range" to 1 week so your query will be consistent in containing non-overlapping or repeating click dates range. If you're looking at data over the previous week the easiest way to do this is to define your date range to look at the data greater than or equal to 7 days ago but less than today's date and then setup a scheduled automation or program to run the query 7 days from now. What is excluded today when be included in the next query report. Using this approach at any given query run you are just reporting on data from the previous 6 days and you will maintain the consistency catching each day for every week going forward as long as you stay with running the automation every 7 days.

So for today in April 2016 you could run it on the 12th and it will capture the previous 6 days. Then again on the 19th and it will capture the 12-18 and so on.

As far as targeting a specific link in a particular email you can do that by joining several of the data views.

Here is an example of how to setup a query targetting link X, i am using a survey link (clk.URL) defined in for a particular email (j.emailname). Note: this email 'email_retention_day10' from my daily drip campaign goes out daily to each of our subscribers on the list after the 10th day. I added some extra fields to gain greater visibility to how the clicks relate to the other fields. It helps to see this comparison.

select clk.jobid as 'JobID', clk.eventdate as 'Event Date' , j.emailname as 'Email Name', j.emailSubject as 'Email Subject', sb.subscriberid as 'SubscriberID', sb.emailaddress as 'Email Address', sb.domain as 'Domain', clk.URL as 'URL'
FROM _click clk 
INNER JOIN _job j  ON clk.jobid = j.jobid INNER JOIN ent._subscribers sb on clk.subscriberid = sb.subscriberid 
AND j.emailname = 'email_retention_Day10'
and clk.isunique = 1
AND clk.URL LIKE  '%www.surveymonkey.com%' 
AND convert(date, clk.eventdate) >= CONVERT(varchar(10), DATEADD(day, -7, GETDATE()), 101) 
AND convert(date, clk.eventdate) < CONVERT(varchar(10), DATEADD(day, 0, GETDATE()), 101) 

Alternatively, if you are trying to include a "full 7 day" reporting period each time your query runs AND also make sure that your query does not repeat any prior click data dates that was captured from a previous run then you're going to have to adjust the schedule of how frequent the automation runs from every 7 days to every 8 days in addition to adjusting the query from -7 to -8. However, this will be very inconsistent and confusing in gathering a report such as this as you will lose the luxury of it always falling on the same day of the week. If you wanted to run it on "Monday's" as a priority each week you could start on Monday but every week..the report will be pushed back by another day perpetually for each successive week thereafter until it falls on the weekends and you find it impractical. It's easier to filter for a weekly filter based on the past 6 days just as you would apply a daily filter based on the day prior when automating a shopping cart abandonment email that runs each day based on who abandoned the previous day.

A third alternative would be to just remove any data range specifications from within SQL and simply do your date filtering requirements in Excel after you extract the data from the data extension.