SQLTeam.com | Weblogs | Forums

Picking customers based on email address only once per coupon code

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)

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 :-).

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')
;

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))

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.

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.

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"

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

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?