0

I'm trying to identify email subscribers from within a specified data extension who have not clicked since 2/1/2013, but my query isn't working.

I posted a similar topic yesterday, and received some great info, so I'm hoping that if someone here has a free second today, I could get some advice on my query.

SELECT b.Email
FROM BRAND_PREPOLICY b
JOIN _Click c
    ON c.SubscriberID = b.Email
WHERE c.EventDate < '2013-3-1'

I want this query to say: From the data extension titled BRAND_PREPOLICY, give me all email addresses that haven't clicked since before 3/1/2013. Email address (field name = Email) is set as the subscriber key for the BRAND_PREPOLICY data extension.

I've tried a few tweaks, but I can't figure out why my resulting data extension is showing up empty after I run this query. We definitely had clicks in ET before 3/1/2013.

Thanks!

  • Can you post a sample of the EventDate column from _Click? If it is a Datetime column, I'm inclined to think that you may need to change the statement to: SELECT b.Email FROM BRAND_PREPOLICY b JOIN _Click c ON c.SubscriberID = b.Email WHERE c.EventDate < '2013-3-1T00:00:00Z' – ITAdminNC Jan 6 '16 at 20:02
1

System Data Views such as _Click only go back 6 months.

Source: http://help.exacttarget.com/en-US/documentation/exacttarget/interactions/activities/query_activity/

1

Mac is correct, the contents of the system data views only go back 6 months. To access click data older than this, you would need to preform a tracking extract. You could theoretically extract the data; import in a data extension; and then modify your query to reference the new data extension. However if you are needing to retrieve 2+ years of click activity (2013-3-1), you might be better off processing this data offline.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.