Picking customers based on email address only once per coupon code

,
1

I have an table where customers form submissions are being added

API_TABLE

| SubscriberKey      | EmailAddress       | coupon_code | offer_prefix | submit_date             |
|--------------------|--------------------|-------------|---------------|-----------------------|
| email_1@domain.com | email_1@domain.com | ABC-101     | ABC           | 6/5/2022  7:03:48 AM  |
| email_1@domain.com | email_1@domain.com | ABC-101     | ABC           | 6/5/2022  7:03:50 AM  |
| email_1@domain.com | email_1@domain.com | ABC-101     | ABC           | 6/5/2022  7:03:50 AM  |
| email_2@domain.com | email_2@domain.com | ABC-102     | ABC           | 6/5/2022  10:01:20 AM |
| email_2@domain.com | email_2@domain.com | XYZ-101     | XYZ           | 6/6/2022  9:40:23 AM  |
| email_1@domain.com | email_1@domain.com | ABC-101     | ABC           | 6/6/2022  11:23:11 AM |

Based on these form submission, an automation is sending an email to these customer and those sent emails are being collected here

SENT_TABLE

| SubscriberKey      | EventDate             |
|--------------------|-----------------------|
| email_1@domain.com | 6/5/2022  7:03:49 AM  |
| email_1@domain.com | 6/5/2022  7:03:51 AM  |
| email_1@domain.com | 6/5/2022  7:03:52 AM  |
| email_2@domain.com | 6/5/2022  10:02:50 AM |
| email_2@domain.com | 6/6/2022  9:41:23 AM  |
| email_1@domain.com | 6/6/2022  11:23:12 AM |

I am creating an automation that queries customer to receive resend next day and currently I am doing this:

SELECT DISTINCT
a.SubscriberKey,
a.EmailAddress,
a.coupon_code,
a.online_prefix,
a.send_date
FROM API_TABLE a
INNER JOIN SENT_TABLE s on s.SubscriberKey = a.SubscriberKey
WHERE s.sendtype = 'API'
AND CAST(s.EventDate AS DATE) = CAST(GETDATE() - 1 AS DATE)

With this query, the issue is - if a customer like "email_2@domain.com" submitted form for 2 different offers (aka offer_prefix), sometimes with the above query, I get record with "submit_date" 6/5/2022 with coupon code from other offer - XYZ-101

like I am getting this
email_2@domain.com | email_2@domain.com | XYZ-101 | XYZ | 6/5/2022 10:01:20 AM

while the submission on 6/5 is from offer_prefix ABC (NOT XYZ)

Desired output:

  • Pull ONLY 1 customer record, if there are multiple entries / submissions on the same date for same offer_prefix
  • Pull respective offer code & prefix that was submitted originally (not allowing logic to mix coupon code from another offer with submission of different offer - since email address is used as key to join table)
  • Pull ONLY 1 customer per date per offer_prefix (if there are multiple entries for same offer, pull only 1 record)
2

Since you've been a member of this site for 7 years, you should know that we need usable data, i.e, CREATE TABLE and INSERT statements, not just an image of data. We can't write code against an image :-).

3

Thank you @ScottPletcher for the reminder :slight_smile:

Here you go:

API_TABLE

CREATE TABLE API_TABLE
(
SubscriberKey varchar(300),
EmailAddress varchar(300),
coupon_code varchar(255),
offer_prefix varchar(255),
submit_date datetime
);

INSERT INTO API_TABLE (SubscriberKey, EmailAddress, coupon_code, offer_prefix, submit_date)
VALUES 
('email_1@domain.com','email_1@domain.com','ABC-101','ABC','6/5/2022  7:03:48 AM'),
('email_1@domain.com','email_1@domain.com','ABC-101','ABC','6/5/2022  7:03:50 AM'),
('email_1@domain.com','email_1@domain.com','ABC-101','ABC','6/5/2022  7:03:50 AM'),
('email_2@domain.com','email_2@domain.com','ABC-102','ABC','6/5/2022  10:01:20 AM'),
('email_2@domain.com','email_2@domain.com','XYZ-102','XYZ','6/6/2022  9:40:23 AM'),
('email_1@domain.com','email_1@domain.com','ABC-101','ABC','6/6/2022  11:23:11 AM')
;

SENT_TABLE

CREATE TABLE SENT_TABLE
(
SubscriberKey varchar(300),
EventDate datetime
);

INSERT INTO SENT_TABLE (SubscriberKey, EventDate)
VALUES 
('email_1@domain.com','6/5/2022  7:03:49 AM'),
('email_1@domain.com','6/5/2022  7:03:51 AM'),
('email_1@domain.com','6/5/2022  7:03:52 AM'),
('email_2@domain.com','6/5/2022  10:02:50 AM'),
('email_2@domain.com','6/6/2022  9:41:23 AM'),
('email_1@domain.com','6/6/2022  11:23:12 AM')
;
4

Nice, thanks. Then something like this:


;WITH cte_distinct_offers AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY SubscriberKey, offer_prefix ORDER BY submit_date DESC) AS row_num
    FROM API_TABLE    
)
SELECT 
    a.SubscriberKey, 
    a.EmailAddress,
    a.coupon_code,
    a.offer_prefix,
    a.submit_date
FROM cte_distinct_offers a
WHERE a.row_num = 1 AND
    EXISTS(SELECT 1 FROM SENT_TABLE s 
        WHERE s.SubscriberKey = a.SubscriberKey /* AND 
            s.sendtype = 'API'*/ AND 
            CAST(s.EventDate AS DATE) = CAST(GETDATE() - 1 AS DATE))
5

Thanks @ScottPletcher

Just confirming if is it the complete query?

I am getting an error

An error occurred while checking the query syntax. Errors: Only SELECT queries are valid. Select must be the first word of the query. Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax.

6

Must be not be SQL Server.

Remove the leading ;.

If that doesn't work, you'll need to change the cte to be a derived table in the second SELECT.

7

Yes - NOT SQL server

Just looking for SQL query help / logic. Not sure if I am following "change the cte to be a derived table in the second SELECT"

8

Ok - I gave it a try. Please let me know your thoughts?


SELECT 
x.SubscriberKey,
x.EmailAddress,
x.coupon_code,
x.online_prefix,
x.submit_date,
x.EventDate
from (
SELECT a.*, s.EventDate,
ROW_NUMBER() OVER(PARTITION BY a.SubscriberKey, a.online_prefix ORDER BY a.submit_date DESC) AS row_num
   FROM API_TABLE a
INNER JOIN SENT_TABLE s on s.SubscriberKey = a.SubscriberKey
WHERE s.sendtype = 'API'
AND CAST(s.EventDate AS DATE) = CAST(GETDATE() - 6 AS DATE)
) x
where x.row_num=1
9

UPDATE- In my logic, I need to get audience for a particular day which is why I have the following in the WHERE clause

AND CAST(s.EventDate AS DATE) = CAST(GETDATE() - 6 AS DATE)

But, currently it is giving me duplicate records per subscriber key (which is email address), why?
I am getting one entry per offer per submit date which is good but x.EventDate is not showing correct value, it is showing same value for all records. Also, I am looking for records for specific date (today - 6), why query is returning ALL unique submissions of same customer as duplicate rows per customer?