0

I'm trying to send one email per order number to a data extension. Data extension is set up so contact id relates to subscribers on subscriber key. No primary key. The data would look something like this:

Contact ID   Name   Order Number   Item Number   Email
123          John   001            1             john@example.com
123          John   002            2             john@example.com
321          Jane   003            3             jane@example.com
321          Jane   003            4             jane@example.com

I'm using this data to personalize the email with an order summary with AMPscript similar to this solution. As you can see, there will be multiples of the same contact, sometimes multiple items on the same order number. What I want is to send one email per order number. Right now, when I send to this data extension, a contact will receive one email per record, so contact 321 receives two emails, even though it's just one order number. I don't want to de-duplicate either, because then contact 123 wouldn't receive two emails for his two different order numbers. Is it possible to achieve what I want here? Is there a solution in email send settings or in data extension settings?

0

You could deduplicate based on order_number rather than Contact ID using below:

SELECT
CONTACT_ID,ORDER_NUMBER,EMAIL,ITEM_NUMBER,NAME
FROM
(SELECT CONTACT_ID,ORDER_NUMBER,EMAIL,ITEM_NUMBER,NAME,
ROW_NUMBER() OVER (PARTITION BY ORDER_NUMBER ORDER BY ITEM_NUMBER ASC)
AS Row
FROM "ORDER"
) as s
WHERE row=1

Considerations:

  1. Timeout: If your data is very large.
  2. This will pick up the first record as per item number, is there any business requirement on which record should be selected in case of same order number?
3
  • Hi, thanks for the quick response. This is probably an obvious question, but is this AMPscript that goes in the email itself? And on your consideration 2, the system would pick the record with item id 3 for contact 321, since that's the first item id for that same order number 003 -- am I understanding that right? – lydiaj Oct 20 '20 at 16:36
  • On closer look, is this an SQL query? Apologies, I wasn't familiar enough to recognize it. If there is possibly any solution that doesn't include it, that would be excellent, but I'm guessing there's not a great workaround without queries. – lydiaj Oct 20 '20 at 16:52
  • Yes it is a SQL Query, looking at your problem statement, this was the best solution I could think of :) – Swati Mishra Oct 21 '20 at 7:01

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.