0

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!

enter image description here

3
  • What should the resulting audience data extension look like? Mar 6, 2020 at 16:20
  • The result that I get is wrong because the SKU(productid) doesn't match the OrderDate and I can't understand why as the data in the tables is definitely right. The resulting audience should have the right orderDate appended to the SKU
    – Duicug
    Mar 6, 2020 at 16:25
  • When it comes to the joins, I'd start with the order header data and work your way to the SKUs -- which is not what you have illustrated in your diagram. Mar 6, 2020 at 17:35

1 Answer 1

2

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 */

You must log in to answer this question.

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