0

I'm facing a challenge with my SQL query in Marketing Cloud and would appreciate the community's help in resolving it.

Recently, I created three separate Data Extensions as part of a segmentation. To achieve this, I ran three different SQL queries. The goal is to merge these three queries into a single query to store the resulting data in a single Data Extension.

Here are the three queries I used:

  1.  Select FirstName, LastName, city, Subscriberkey, 
     EmailAdress
    
     FROM [Segment1]
    
     WHERE City IS NULL 
    
     OR City NOT LIKE '%Denver%'
    
  1.  Select FirstName, LastName, city, Subscriberkey, EmailAdress
     FROM [Segment1]
     WHERE City LIKE '%Denver%'
     and EmailAddress NOT IN 
      (Select EmailAddress from Contacts_segment)
    
  2.   Select FirstName, LastName, city, Subscriberkey, EmailAddress
      from [Segment2]
      WHERE 
      EmailAddress NOT IN 
      (Select EmailAddress from [Contacts_segment]
      UNION
      Select EmailAddress from [Contact_data])
    

Here is my attempt to combine these 3 queries into a single query:

    SELECT
    
    FirstName, LastName, city, Subscriberkey, EmailAddress         
    
    'Segment1' AS Segment 
    
    FROM [Segment1]
    
    WHERE City IS NULL 
    
    OR City NOT LIKE '%Denver%'

     UNION

    SELECT

     FirstName, LastName, city, Subscriberkey, EmailAddress
 
    'Segment2' AS Segment 

    FROM [Segment1]

   WHERE City LIKE '%Denver%'

   UNION 

    SELECT 

    FirstName, LastName, city, Subscriberkey, EmailAddress
 
   'Segment3' AS Segment 

    FROM [Segment2]

Note: The fields in my target Data Extension are the same as the ones I'm selecting in my SELECT statement (FirstName, LastName, City, Subscriberkey, EmailAddress), with the only difference being the inclusion of a field (Segment) to identify which segment the contacts are coming from. My only primary key is the Subscriberkey to ensure that they are unique contacts.

I'm facing an issue where the final numbers in the resulting Data Extension do not match the sum of the three original Data Extensions. There seems to be some problem with my final query, but I can't identify it. The situation may be a bit confusing, but I'm confident that someone here can help me resolve this issue. Any suggestions or guidance would be greatly appreciated. Thank you for your patience, I'm new to Marketing Cloud and I'm trying to learn more.

5
  • I only see two data extensions referenced in your queries, not three -- Segment1 and Segment2. Sep 3, 2023 at 2:08
  • Also, not sure how you verified the counts as the combined query has a couple of syntax errors that would prevent it from running. And is EmailAdress a typo? Sep 3, 2023 at 2:12
  • Please update your question to include the details about the target data extension, including the primary key fields. Sep 3, 2023 at 2:21
  • @Adam Spriggs Yes, I'm using only 2 Data Extensions as a source, but I've created 3 segments. Segment 3 is the one that doesn't have a corresponding email address in the other two tables, [Contacts_segment] and [Contact_data].
    – Mik
    Sep 3, 2023 at 2:22
  • And yes, I wrote EmailAddress wrong. So it's a typo, I'll correct it.
    – Mik
    Sep 3, 2023 at 2:23

1 Answer 1

1

I'd suggest that you build a simple model outside of Marketing Cloud first -- instead of trying match counts. It's easier to look at the data and determine the desired outcomes before writing the query.

Here's a simplified mock-up query using dbfiddle:

1. Prepare the sample tables and add rows to them

CREATE TABLE Segment1 (City varchar(40), Subscriberkey varchar(254), EmailAddress varchar(254));
CREATE TABLE Segment2 (City varchar(40), Subscriberkey varchar(254), EmailAddress varchar(254));
CREATE TABLE Contacts_segment (EmailAddress varchar(254));
CREATE TABLE Contact_Data (EmailAddress varchar(254));
INSERT INTO  Segment1 (City,Subscriberkey,EmailAddress) VALUES 
  (null,'SK1','[email protected]')
, ('Boulder','SK2','[email protected]')
, ('Aurora','SK3','[email protected]')
, ('Lakewood','SK4','[email protected]')
, ('Denver','SK5','[email protected]')
INSERT INTO  Segment2 (City,Subscriberkey,EmailAddress) VALUES 
  ('Durango','SK6','[email protected]')
, ('Denver','SK7','[email protected]')
, ('Aurora','SK8','[email protected]')
, ('Aspen','SK9','[email protected]')
, ('Denver','SK0','[email protected]')
INSERT INTO  Contacts_segment (EmailAddress) VALUES 
 ('[email protected]')
INSERT INTO  Contact_data (EmailAddress) VALUES 
 ('[email protected]')

2. Run your test queries against the sample data:

SELECT
  City
, Subscriberkey
, EmailAddress
, 'Segment1' AS Segment
FROM Segment1
WHERE
City IS NULL 
OR City NOT LIKE '%Denver%'

UNION

SELECT
  City
, Subscriberkey
, EmailAddress
, 'Segment2' AS Segment
FROM Segment1
WHERE 
City LIKE '%Denver%'
and EmailAddress NOT IN (
  Select
  EmailAddress
  from Contacts_segment
)

UNION

SELECT
  City
, Subscriberkey
, EmailAddress
, 'Segment3' AS Segment
FROM Segment2
where
EmailAddress NOT IN (

  Select
  EmailAddress
  from Contacts_segment

  UNION

  Select
  EmailAddress
  from Contact_data

)

3. Verify the results are as you expected:

City Subscriberkey EmailAddress Segment
null SK1 [email protected] Segment1
Aspen SK9 [email protected] Segment3
Aurora SK3 [email protected] Segment1
Boulder SK2 [email protected] Segment1
Denver SK0 [email protected] Segment3
Denver SK5 [email protected] Segment2
Durango SK6 [email protected] Segment3
Lakewood SK4 [email protected] Segment1

Try it yourself on dbfiddle

1
  • This tool is really useful. I'm still getting the hang of Marketing Cloud, but when I need to work on more robusts tasks, I'll definitely give this tool a try. Thanks.
    – Mik
    Sep 3, 2023 at 12:41

You must log in to answer this question.

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