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

  • What should the resulting audience data extension look like? – Adam Spriggs Mar 6 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 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. – Adam Spriggs Mar 6 at 17:35
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 */
| improve this answer | |

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.