link CustomerID with Order Date for every product SKU

Question

I'm trying to link some tables to create an audience for a campaign but the way I link them doesn't give me the right audience. When I check from the last week, I get many more products over £1000 then I now we sold. It looks like it doesn't link the sku(productid) to the right date.

Do you see anything wrong with the way I'm connectiont the tables? Thanks!

Answer 1

You could construct your query this way to retrieve orders placed yesterday:

select
  ordhdr.customerID
, ordhdr.orderID
, ordhdr.orderDate
, orddtl.sku 
, products.productName
from Table4 ordhdr
inner join Table5 orddtl on orddtl.customerid = ordhdr.customerid
inner join Table2 products on products.sku = orddtl.sku
where ordhdr.orderDate >= convert(date, getDate()-1)
and ordhdr.orderDate < convert(date, getDate())
/* target: OrdersPlacedYesterday */

Then if you took the results of this and referenced a Master data extension you could get a distinct list of subscribers for your sending audience:

select
m.emailaddress
, m.customerid
from MasterDataExtension m
where exists (
 select top 1.* 
  from OrdersPlacedYesterday orders
  where orders.customerid = m.customerid
) 
/* target: OrdersPlacesYesterday_Send */