I have a Data Extension in SFMC where I've captured customer purchase data, let's call it "cust_purch_12mo" and right now it only contains the customer email address and purchase date.

I am trying to create a query that will populate another data extension, called "6mo_engage", where it references "cust_purch_12mo" and returns only those customers that have open/clicked (engaged) with an email from the past 6 months.

I'm fairly new to SQL as a whole and was able to get results using the following SQL, However, I don't feel as if it's actually referencing "cust_purch_12mo" to get its results and instead is returning results based on all customer data.

,   Max(c.EventDate) as Customer_Click_Date

_Click c

Inner Join
[cust_purch_12mo] a

c.SubscriberKey = a.OrderHeader_EmailAddress

CAST(c.eventdate AS DATE) >= CAST(DATEADD(day, -183, GETDATE()) AS DATE)

Group By 
,   a.OrderHeader_EmailAddress


So assuming that you have your email address as your primary key this is what I would do:

SELECT OrderHeader_EmailAddress
FROM cust_purch_12mo
WHERE OrderHeader_EmailAddress IN
    SELECT SubscriberKey
    FROM _Open

You mentioned that you wanted to measure engagement, that's why I used the open instead of click data view. You don't need the where clause for the dates as the data views only hold 6 months of data anyways. Also if you're in a child account use the ENT. prefix for ENT._Open. Lmk if it works.

  • Thank you @intern. I will try this out but I do need to filter down to clicks, rather than just opens for this specific request. – Jason Taylor Aug 7 '19 at 12:46
  • Thanks again @intern. I was able to get this to work both with seeing what _Open produced as well as _Click as they are pretty different results. – Jason Taylor Aug 7 '19 at 16:42

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.