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


1 Answer 1


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. Aug 7, 2019 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. Aug 7, 2019 at 16:42

You must log in to answer this question.

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