GA4

Create person_ga_ids

CREATE OR REPLACE TABLE person_ga_ids AS
SELECT DISTINCT
    person_id,
    TRIM(tag_value) AS ga_id
FROM person_tags
WHERE tag_type = 'ga_id'
  AND tag_value IS NOT NULL
  AND TRIM(tag_value) <> '';

Create person_ga_ids_clean

This is a one-to-one person_id to ga_id.

CREATE OR REPLACE TABLE person_ga_ids_clean AS
WITH ga_id_counts AS (
    SELECT
        ga_id,
        COUNT(DISTINCT person_id) AS person_count
    FROM person_ga_ids
    GROUP BY ga_id
)
SELECT
    pgi.person_id,
    pgi.ga_id
FROM person_ga_ids pgi
JOIN ga_id_counts gic
  ON pgi.ga_id = gic.ga_id
WHERE gic.person_count = 1;

GA4 Detail Tables with person_id

Landing Sessions

CREATE OR REPLACE TABLE ga4_person_landing_sessions AS
SELECT
    pgi.person_id,
    ls.crm_id AS ga_id,

    ls.date,
    ls.landing_page,
    ls.session_source,
    ls.session_medium,
    ls.session_campaign_name,
    ls.session_manual_term,
    ls.session_manual_ad_content,

    ls.engaged_sessions,
    ls.active_users
FROM ga4_landing_sessions ls
JOIN person_ga_ids pgi
  ON ls.crm_id = pgi.ga_id
WHERE ls.crm_id <> '';

Page Views

CREATE OR REPLACE TABLE ga4_person_page_views AS
SELECT
    pgi.person_id,
    pv.crm_id AS ga_id,

    pv.date,
    pv.page_path,
    pv.session_source,
    pv.session_medium,
    pv.session_campaign_name,
    pv.session_manual_term,
    pv.session_manual_ad_content,

    pv.screen_page_views,
    pv.active_users,
    pv.event_count
FROM ga4_page_views pv
JOIN person_ga_ids pgi
  ON pv.crm_id = pgi.ga_id
WHERE pv.crm_id <> '';

GA4 Summary Tables

Person + Landing Page Summary

This is useful for questions like:

Which applicants first landed on program pages?
Which landing pages are associated with later applications?
Which people repeatedly entered through visit/apply/cost pages?

CREATE OR REPLACE TABLE ga4_person_landing_page_summary AS
SELECT
    person_id,
    landing_page,

    MIN(date) AS first_landing_date,
    MAX(date) AS last_landing_date,
    COUNT(DISTINCT date) AS active_days,

    SUM(engaged_sessions) AS engaged_sessions,
    SUM(active_users) AS active_users,

    COUNT(DISTINCT session_campaign_name) AS distinct_campaigns,
    COUNT(DISTINCT ga_id) AS ga_ids_seen

FROM ga4_person_landing_sessions
GROUP BY
    person_id,
    landing_page;

ANALYZE ga4_person_landing_page_summary;

Person + Campaign Summary

One row per person/source/medium/campaign. This is useful for campaign attribution-style analysis, especially when joined to application/confirmation dates.

CREATE OR REPLACE TABLE ga4_person_landing_campaign_summary AS
SELECT
    person_id,
    session_source,
    session_medium,
    session_campaign_name,

    MIN(date) AS first_landing_date,
    MAX(date) AS last_landing_date,
    COUNT(DISTINCT date) AS active_days,

    SUM(engaged_sessions) AS engaged_sessions,
    SUM(active_users) AS active_users,

    COUNT(DISTINCT landing_page) AS distinct_landing_pages,
    COUNT(DISTINCT ga_id) AS ga_ids_seen

FROM ga4_person_landing_sessions
GROUP BY
    person_id,
    session_source,
    session_medium,
    session_campaign_name;

ANALYZE ga4_person_landing_campaign_summary;

GA4 Person Features

A combined person-level feature table from both page and landing data.

CREATE OR REPLACE TABLE ga4_person_features AS
WITH landing AS (
    SELECT
        person_id,
        MIN(date) AS first_landing_date,
        MAX(date) AS last_landing_date,
        COUNT(DISTINCT date) AS landing_active_days,
        SUM(engaged_sessions) AS engaged_sessions,
        COUNT(DISTINCT landing_page) AS distinct_landing_pages,
        COUNT(DISTINCT session_campaign_name) AS landing_distinct_campaigns
    FROM ga4_person_landing_sessions
    GROUP BY person_id
),
pages AS (
    SELECT
        person_id,
        MIN(date) AS first_pageview_date,
        MAX(date) AS last_pageview_date,
        COUNT(DISTINCT date) AS pageview_active_days,
        SUM(screen_page_views) AS screen_page_views,
        SUM(event_count) AS event_count,
        COUNT(DISTINCT page_path) AS distinct_pages,
        COUNT(DISTINCT session_campaign_name) AS pageview_distinct_campaigns
    FROM ga4_person_page_views
    GROUP BY person_id
)
SELECT
    COALESCE(l.person_id, p.person_id) AS person_id,

    l.first_landing_date,
    l.last_landing_date,
    l.landing_active_days,
    l.engaged_sessions,
    l.distinct_landing_pages,
    l.landing_distinct_campaigns,

    p.first_pageview_date,
    p.last_pageview_date,
    p.pageview_active_days,
    p.screen_page_views,
    p.event_count,
    p.distinct_pages,
    p.pageview_distinct_campaigns,

    1 AS has_ga4_behavior

FROM landing l
FULL OUTER JOIN pages p
  ON l.person_id = p.person_id;

ANALYZE ga4_person_features;

Ambiguous GA IDs

When a ga_id is attached to multiple Slate persons.

SELECT
    ga_id,
    COUNT(DISTINCT person_id) AS person_count,
    STRING_AGG(DISTINCT person_id, ', ') AS person_ids
FROM person_ga_ids
GROUP BY ga_id
HAVING COUNT(DISTINCT person_id) > 1
ORDER BY person_count DESC, ga_id;

Do not throw them away permanently. Put them in an exclusion table:

CREATE OR REPLACE TABLE person_ga_ids_ambiguous AS
WITH ga_id_counts AS (
    SELECT
        ga_id,
        COUNT(DISTINCT person_id) AS person_count
    FROM person_ga_ids
    GROUP BY ga_id
)
SELECT
    pgi.person_id,
    pgi.ga_id,
    gic.person_count
FROM person_ga_ids pgi
JOIN ga_id_counts gic
  ON pgi.ga_id = gic.ga_id
WHERE gic.person_count > 1;

Then summarize how bad the contamination is:

SELECT
    COUNT(*) AS rows,
    COUNT(DISTINCT ga_id) AS ambiguous_ga_ids,
    COUNT(DISTINCT person_id) AS affected_persons,
    MAX(person_count) AS max_persons_per_ga_id,
    AVG(person_count) AS avg_persons_per_ga_id
FROM person_ga_ids_ambiguous;

And inspect the worst offenders:

SELECT
    ga_id,
    person_count,
    COUNT(*) AS bridge_rows
FROM person_ga_ids_ambiguous
GROUP BY ga_id, person_count
ORDER BY person_count DESC, ga_id
LIMIT 50;