0

I'm doing segments based on SQL and UNIONs, so ie.

  • first I determine everyone having interest in product group X based on 'website interaction'
  • next I determine everyone having interest in product group X based on 'social media interaction'

This functions nicely but so far I've only collected values for these fields
the id (primary key)
the email

using this SQL

SELECT
id,
email
FROM subscribers (...)

UNION

SELECT
id,
email
FROM subscribers (...)

Now I'd like to extend my Data Extension with a field for each source telling me why each subscriber actually qualifies - ending up with these fields
the id (primary key)
the email
the sourceWebsite
the sourceSocialMedia

using this SQL

SELECT
id,
email,
'YES' AS sourceWebsite,
'' AS sourceSocialMedia
FROM subscribers (...)

UNION

SELECT
id,
email,
'' AS sourceWebsite,
'YES' AS sourceSocialMedia        
FROM subscribers (...)

I'm not quite there yet. This gives me a YES in either sourceWebsite or in sourceSocialMedia.

Would I be able to re-write my SQL UNION exemplified above differently in order for me to know if a subscriber qualifies from both sourceWebsite and sourceSocialMedia?

Thank you so much :)

2
  • What is your primary key on the target data extension? May 31, 2023 at 12:31
  • Hi @LukasLunow The primary key for my segment Data Extension is the id May 31, 2023 at 12:38

1 Answer 1

1

Your issue is, that UNION only appends distinct values. So when you use UNION, the dataset is appended, and any rows in the appended table that are exactly identical to rows in the first table are dropped. So you will only write the first occurrence of the ID which comes up in the query.

If you'd like to append all the values from the second table, use UNION ALL. However this solution will violate your primary key, in case your id is occurring in both sources.

My best suggestion will be to use a staging data extension with a compound key, consisting of a combination of ID, sourceSocialMedia, sourceWebsite. You should then be able to do a UNION ALL without a primary key violation. But you will also need to do an additional query, going through the staging table, doing a GROUP BY ID, and selecting sourceSocialMedia and sourceWebsite respectively, into a single record.

1
  • 1
    This makes perfect sense, thank you. I suspected I needed to re-arrange rather than just re-write, but it was worth a try. And actually, I learned something too. I thought the last identical rows iwould be kept and the first one dropped, not the other way around as you describe it. That's nice to know too. GOOD STUFF! May 31, 2023 at 13:37

You must log in to answer this question.

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