🧠 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

AudienceLab Help Bot

SQL Templates

🧠 Data Normalization Templates (AudienceSync)

📦 Template 1: Explode Values Template

✅ How It Works

🧪 Example Input

📤 Output

✅ Copy & Paste Version (for AudienceSync)

📦 Template 2: Facebook Expansion

🔍 How It Works

🧪 Example Input

📤 Output

✅ Copy & Paste Version (for AudienceSync)

🎯 Why this works

🧠 Template 3: State and Field Selection Template (AudienceSync)

📦 Template 3: State and Field Selection

✅ How It Works

✅ Copy & Paste Version (for AudienceSync)

📚 Example 1: Select Emails and Names Where State is NY

🧪 Example Input

📤 Output

✅ Copy & Paste Version (for AudienceSync)

🎯 Why this is useful

📚 Example 2: Select Mobile Phones Where State is CA

🧪 Example Input

📤 Output

✅ Copy & Paste Version (for AudienceSync)

🎯 Why this is useful

📦 Template 4: First Email from Raw Text (AudienceSync)

✅ When to Use

🧪 Example Input

📤 Output

✅ Copy & Paste Version (for AudienceSync)

🧠 Why This Works

🛠 Optional Customizations