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(...) and WHERE 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, and GENDER
  • 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
Did this answer your question?
😞
😐
🤩

Last updated on August 6, 2021