Extending my use of SQL and UNION to add information about source

Question

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

Answer 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.