SQL Templates
🧠 Data Normalization Templates (AudienceSync)
This guide walks you through two templates for exploding comma-separated fields inside AudienceSync. Both templates help you normalize messy audience data for upload to platforms like Facebook Ads, TikTok, or CRMs.
Note: Make sure to leave no unnecessary spaces when pasting in the queries.
📦 Template 1: Explode Values Template
Use this when:
You have a single column with comma-separated values (e.g., hash1,hash2,hash3
) and want to split it into multiple rows, one value per row.
✅ How It Works
This template:
- Uses
STRING_SPLIT(..., ',')
to break values into an array.
- Uses
UNNEST(...)
to explode them into rows.
- Uses
TRIM(...)
andWHERE
filters to clean empty/blank values.
🧪 Example Input
SHA256_PERSONAL_EMAIL
hash1,hash2,hash3
📤 Output
sha256_personal_email
hash1
hash2
hash3
✅ Copy & Paste Version (for AudienceSync)
SELECT
TRIM(value) AS sha256_personal_email
FROM (
SELECT
UNNEST(STRING_SPLIT(SHA256_PERSONAL_EMAIL, ',')) AS value
FROM all_sources
)
WHERE value IS NOT NULL
AND value <> '';
🔁 Replace SHA256_PERSONAL_EMAIL with your column name
📦 Template 2: Facebook Expansion
Use this when:
You have multiple fields (e.g., SHA256_PERSONAL_EMAIL
, MOBILE_PHONE
, etc.) containing comma-separated values, and you need:
- One value per row
- All emails collapsed into a single field
- All phone numbers collapsed into a single field
- Static fields (like
FIRST_NAME
) repeated for each row
🔍 How It Works
- Splits each multi-value field using
split(...)
- Generates an index list using
range(...)
- Explodes the list with
UNNEST(...)
- Combines aligned values using
idx
- Uses
COALESCE(...)
to select the first non-empty email or phone
- Retains static fields without splitting
🧪 Example Input
SHA256_PERSONAL_EMAIL | SHA256_BUSINESS_EMAIL | MOBILE_PHONE | FIRST_NAME |
hash1,hash2 | hash3 | 1111,2222,3333 | Alice |
📤 Output
normal_email | normal_phone | FIRST_NAME |
hash1 | 1111 | Alice |
hash2 | 2222 | Alice |
hash3 | 3333 | Alice |
✅ Copy & Paste Version (for AudienceSync)
SELECT
-- Combined Email Field
COALESCE(
NULLIF(TRIM(split_sha256[idx]), ''),
NULLIF(TRIM(split_sha256_business_email[idx]), '')
) AS normal_email,
-- Combined Phone Field
COALESCE(
NULLIF(TRIM(split_mobile_phone[idx]), ''),
NULLIF(TRIM(split_personal_phone[idx]), ''),
NULLIF(TRIM(split_direct_number[idx]), '')
) AS normal_phone,
-- Static fields (not exploded)
FIRST_NAME,
LAST_NAME,
PERSONAL_CITY,
PERSONAL_STATE,
PERSONAL_ZIP,
GENDER
FROM (
SELECT
-- Splitting multi-value fields
split(SHA256_PERSONAL_EMAIL, ',') AS split_sha256,
split(PERSONAL_EMAILS, ',') AS split_emails,
split(DIRECT_NUMBER, ',') AS split_direct_number,
split(MOBILE_PHONE, ',') AS split_mobile_phone,
split(PERSONAL_PHONE, ',') AS split_personal_phone,
split(SHA256_BUSINESS_EMAIL, ',') AS split_sha256_business_email,
-- Static fields passed through
FIRST_NAME,
LAST_NAME,
PERSONAL_CITY,
PERSONAL_STATE,
PERSONAL_ZIP,
GENDER,
-- Index list for row explosion
range(
0,
GREATEST(
array_length(split(SHA256_PERSONAL_EMAIL, ',')),
array_length(split(PERSONAL_EMAILS, ',')),
array_length(split(DIRECT_NUMBER, ',')),
array_length(split(MOBILE_PHONE, ',')),
array_length(split(PERSONAL_PHONE, ',')),
array_length(split(SHA256_BUSINESS_EMAIL, ','))
)
) AS idx_list
FROM all_sources
) t,
UNNEST(idx_list) AS idx_struct(idx)
WHERE (
(split_sha256[idx] IS NOT NULL AND split_sha256[idx] <> '')
OR (split_sha256_business_email[idx] IS NOT NULL AND split_sha256_business_email[idx] <> '')
)
AND (
(split_mobile_phone[idx] IS NOT NULL AND split_mobile_phone[idx] <> '')
OR (split_personal_phone[idx] IS NOT NULL AND split_personal_phone[idx] <> '')
OR (split_direct_number[idx] IS NOT NULL AND split_direct_number[idx] <> '')
);
🎯 Why this works
This template:
- Combines SHA256_PERSONAL_EMAIL and SHA256_BUSINESS_EMAIL into a single column called
normal_email
- Merges MOBILE_PHONE, PERSONAL_PHONE, and DIRECT_NUMBER into a single
normal_phone
- Retains static fields like
FIRST_NAME
,ZIP
, andGENDER
- Ensures one clean value per row — exactly what Facebook, TikTok, and Google expect
🧠 Template 3: State and Field Selection Template (AudienceSync)
This template shows you how to select specific fields based on geography or attribute filters, like PERSONAL_STATE = 'NY'
.
It’s designed for simple but powerful selections inside AudienceSync.
📦 Template 3: State and Field Selection
Use this when:
You need to pull a specific subset of records based on a filter condition like "State = NY", "State = CA", or any other field match.
✅ How It Works
- Pick the fields you want to SELECT (e.g., emails, names, phones).
- Use a simple
WHERE
clause to filter by state, gender, or any attribute.
- AudienceSync will return only matching rows — clean and ready for export or processing.
✅ Copy & Paste Version (for AudienceSync)
SELECT
FIELD1,
FIELD2,
FIELD3
FROM all_sources
WHERE FILTER_COLUMN = 'your_filter_value';
🔁 Replace FIELD1, FIELD2, FILTER_COLUMN, and 'your_filter_value'
📚 Example 1: Select Emails and Names Where State is NY
Goal:
Pull PERSONAL_EMAILS
, FIRST_NAME
, LAST_NAME
only for users in New York.
🧪 Example Input
PERSONAL_EMAILS | FIRST_NAME | LAST_NAME | PERSONAL_STATE |
alice@example.com | Alice | Johnson | NY |
bob@example.com | Bob | Smith | CA |
dave@example.com | Dave | Wilson | NY |
📤 Output
PERSONAL_EMAILS | FIRST_NAME | LAST_NAME |
alice@example.com | Alice | Johnson |
dave@example.com | Dave | Wilson |
✅ Copy & Paste Version (for AudienceSync)
SELECT
PERSONAL_EMAILS,
FIRST_NAME,
LAST_NAME
FROM all_sources
WHERE PERSONAL_STATE = 'NY';
🎯 Why this is useful
- Build New York-only audience lists.
- Prep for geographically targeted outreach.
📚 Example 2: Select Mobile Phones Where State is CA
Goal:
Pull MOBILE_PHONE
and FIRST_NAME
only for users in California.
🧪 Example Input
MOBILE_PHONE | FIRST_NAME | PERSONAL_STATE |
1234567890 | Alice | CA |
0987654321 | Bob | NY |
5551234567 | Carol | CA |
📤 Output
MOBILE_PHONE | FIRST_NAME |
1234567890 | Alice |
5551234567 | Carol |
✅ Copy & Paste Version (for AudienceSync)
SELECT
MOBILE_PHONE,
FIRST_NAME
FROM all_sources
WHERE PERSONAL_STATE = 'CA';
🎯 Why this is useful
- Prepare California-focused SMS campaigns.
- Quickly segment users by state for regional marketing pushes.
📦 Template 4: First Email from Raw Text (AudienceSync)
Purpose:
Extract the first email address from a messy, comma-separated list in the PERSONAL_EMAILS
field — even when formatting is inconsistent (spaces, no quotes, weird separators, etc.).
This version is fast, reliable, and does not require splitting arrays or cleaning input manually.
✅ When to Use
Use this template when:
PERSONAL_EMAILS
contains multiple emails in one string
- You only want the first valid email
- Formatting is inconsistent (e.g.,
email1, email2
, or"email1" , email2
)
🧪 Example Input
PERSONAL_EMAILS
srmatthews1967@gmail.com, marhodes34@gmail.com, sharon.matthews@att.net
first@example.com , second@example.com , third@example.com
NULL
or empty string
📤 Output
first_personal_email
srmatthews1967@gmail.com
first@example.com
✅ Copy & Paste Version (for AudienceSync)
SELECT
FIRST_NAME,
LAST_NAME,
PERSONAL_ADDRESS,
PERSONAL_CITY,
PERSONAL_STATE,
PERSONAL_ZIP,
-- Extract the first valid email pattern
REGEXP_EXTRACT(PERSONAL_EMAILS, '[^, ]+@[^, ]+') AS first_personal_email,
SKIPTRACE_WIRELESS_NUMBERS,
SKIPTRACE_DNC
FROM all_sources
WHERE SKIPTRACE_WIRELESS_NUMBERS IS NOT NULL
AND SKIPTRACE_WIRELESS_NUMBERS <> ''
AND PERSONAL_EMAILS IS NOT NULL
AND PERSONAL_EMAILS <> '';
🧠 Why This Works
REGEXP_EXTRACT(..., '[^, ]+@[^, ]+')
finds the first valid-looking email — skipping spaces and commas
- Works even if
PERSONAL_EMAILS
is sloppy, inconsistent, or has mixed delimiters
- More stable than splitting and trimming manually
🛠 Optional Customizations
- 🔄 Want to get the second email instead? Use
regexp_extract_all(...)[1]
- 🧼 Want to force lowercase? Wrap with
LOWER(...)
- 💡 Want to use this on another field? Just swap
PERSONAL_EMAILS
with any column
Last updated on August 6, 2021