GA4 Models

Copy-paste SQL models for turning raw GA4 (Google Analytics 4) data into business-ready traffic and transaction attribution reporting.

Copy and Paste these GA4 Models into your Data Layer to quickly go from raw data sources to business ready GA4 (Google Analytics 4) Reporting.

Please note:

Models must be added chronological order from top to bottom (so that they can reference each other).
When adding your own models you must update the reference to include your source names. For example, in the model templates below.

Base

Transforms Raw Data to Clean Tables focused on Core Concepts.

ga4_traffic_acquisition_session_campaign_base

select
    _fivetran_id::varchar as id --pk (composite date + property + source row id; EXAMPLE ONLY — use your sync tool's row id)
  , date::date as date
  , TRIM(property) as property
  , NULLIF(TRIM(session_campaign_name), '(not set)') as session_campaign_name
  , event_count as event_count
  , engaged_sessions as engaged_sessions
  , sessions as sessions
  , total_users as total_users
  , key_events as key_events
  , engagement_rate as engagement_rate
  , user_engagement_duration as user_engagement_duration_seconds
  , events_per_session as events_per_session
  , total_revenue as total_revenue
  , _fivetran_synced as __updated_at -- EXAMPLE ONLY: your sync tool's last-synced timestamp
from sources.ga4.traffic_acquisition_session_campaign_report
-- EXAMPLE ONLY: filter to your GA4 property if you sync more than one
where property = 'properties/XXXXXXXXX'

ga4_transaction_attribution_session_base

-- Session-scoped (last-click-like) attribution: credits the SESSION's source/medium
-- to each transaction. One row per session-attributed transaction per day.
select
    _fivetran_id::varchar as id --pk (composite date + property + source row id; EXAMPLE ONLY — use your sync tool's row id)
  , date::date as date
  , TRIM(property) as property
  , NULLIF(TRIM(session_campaign_name), '(not set)') as session_campaign_name
  -- EXAMPLE ONLY paid-search reclassification: GA4 records sessions as 'google / organic'
  -- when Google Ads auto-tagging (gclid) is present but manual UTMs are not. Reclassify to
  -- 'google / cpc' when the campaign name matches YOUR paid-campaign naming. REPLACE the
  -- LIKE patterns below with your own, or remove this CASE entirely if you don't need it.
  , CASE
      WHEN NULLIF(TRIM(session_manual_source_medium), '(not set)') = 'google / organic'
        AND NULLIF(TRIM(session_campaign_name), '(not set)') is not null
        AND NULLIF(TRIM(session_campaign_name), '(not set)') != '(organic)'
        AND (
          LOWER(TRIM(session_campaign_name)) LIKE 'g-%'      -- EXAMPLE ONLY pattern
          OR LOWER(TRIM(session_campaign_name)) LIKE 'shopping%' -- EXAMPLE ONLY pattern
          OR LOWER(TRIM(session_campaign_name)) LIKE 'pmax%'     -- EXAMPLE ONLY pattern
        )
      THEN 'google / cpc'
      ELSE NULLIF(TRIM(session_manual_source_medium), '(not set)')
    END as session_source_medium
  , NULLIF(TRIM(session_manual_ad_content), '(not set)') as session_ad_content
  , NULLIF(TRIM(session_manual_term), '(not set)') as session_term
  , NULLIF(TRIM(transaction_id), '(not set)')::varchar as transaction_id
  , _fivetran_synced as __updated_at -- EXAMPLE ONLY: your sync tool's last-synced timestamp
from sources.ga4.transaction_attribution_session_manual
-- EXAMPLE ONLY: filter to your GA4 property if you sync more than one
where property = 'properties/XXXXXXXXX'

ga4_transaction_attribution_events_base

-- Event-scoped (GA4 data-driven, cross-session) attribution. One row per
-- event-attributed transaction per day. event_value is GA4's attributed revenue.
select
    _fivetran_id::varchar as id --pk (composite date + property + source row id; EXAMPLE ONLY — use your sync tool's row id)
  , date::date as date
  , TRIM(property) as property
  , NULLIF(TRIM(manual_campaign_name), '(not set)') as campaign_name
  -- EXAMPLE ONLY paid-search reclassification — see note in the session base above.
  , CASE
      WHEN NULLIF(TRIM(manual_source_medium), '(not set)') = 'google / organic'
        AND NULLIF(TRIM(manual_campaign_name), '(not set)') is not null
        AND NULLIF(TRIM(manual_campaign_name), '(not set)') != '(organic)'
        AND (
          LOWER(TRIM(manual_campaign_name)) LIKE 'g-%'
          OR LOWER(TRIM(manual_campaign_name)) LIKE 'shopping%'
          OR LOWER(TRIM(manual_campaign_name)) LIKE 'pmax%'
        )
      THEN 'google / cpc'
      ELSE NULLIF(TRIM(manual_source_medium), '(not set)')
    END as source_medium
  , NULLIF(TRIM(manual_ad_content), '(not set)') as ad_content
  , NULLIF(TRIM(manual_term), '(not provided)') as term
  , NULLIF(TRIM(transaction_id), '(not set)')::varchar as transaction_id
  , event_value as event_value
  , _fivetran_synced as __updated_at -- EXAMPLE ONLY: your sync tool's last-synced timestamp
from sources.ga4.transaction_attribution_events
-- EXAMPLE ONLY: filter to your GA4 property if you sync more than one
where property = 'properties/XXXXXXXXX'

ga4_landing_page_conversions_base

-- Daily landing-page x event conversions. High-volume table — grain is
-- date x landing_page x event_name x session source/medium/channel.
select
    _fivetran_id::varchar as id --pk (composite; EXAMPLE ONLY — use your sync tool's row id)
  , date::date as date
  , TRIM(property) as property
  , NULLIF(TRIM(landing_page), '(not set)') as landing_page
  , landing_page_plus_query_string as landing_page_plus_query_string
  , NULLIF(TRIM(event_name), '(not set)') as event_name
  , NULLIF(TRIM(session_source), '(not set)') as session_source
  , NULLIF(TRIM(session_medium), '(not set)') as session_medium
  , NULLIF(TRIM(session_campaign_name), '(not set)') as session_campaign_name
  , NULLIF(TRIM(session_default_channel_group), '(not set)') as session_channel_group
  , new_users as new_users
  , total_users as total_users
  , sessions as sessions
  , event_count as event_count
  , key_events as key_events
  , total_revenue as total_revenue
  , _fivetran_synced as __updated_at -- EXAMPLE ONLY: your sync tool's last-synced timestamp
from sources.ga4.landing_page_conversions
-- EXAMPLE ONLY: filter to your GA4 property if you sync more than one
where property = 'properties/XXXXXXXXX'

Creates a “Fact” about a core concept. For GA4, the core concept is the transaction: raw attribution tables can hold multiple sessions/events per transaction, so each fact model dedups to the single best-attributed source/medium per transaction (prefer paid/non-organic, then most recent). This one-row-per-transaction fact is what gets joined downstream in output. A daily campaign-performance fact is also provided for spend/engagement rollups.

ga4_transaction_attribution_session_facts

-- One row per transaction: the best session-scoped attribution.
-- Tie-break: prefer non-direct/non-organic sessions, then the most recent session.
with ranked as (
    select
        transaction_id
      , date
      , session_campaign_name
      , session_source_medium
      , session_ad_content
      , session_term
      , ROW_NUMBER() over (
            partition by transaction_id
            order by
              case
                when session_source_medium is null then 2
                when LOWER(session_source_medium) like '%/ organic' then 2
                when LOWER(session_source_medium) = '(direct) / (none)' then 2
                else 1
              end asc
              , date desc
        ) as rn
    from models.ga4_transaction_attribution_session_base
    where transaction_id is not null
)
select
    transaction_id --pk
  , date as ga4_session_date
  , session_campaign_name as ga4_session_utm_campaign
  , session_source_medium as ga4_session_utm_source_medium
  , session_ad_content as ga4_session_utm_content
  , session_term as ga4_session_utm_term
from ranked
where rn = 1

ga4_transaction_attribution_events_facts

-- One row per transaction: the best event-scoped (data-driven) attribution.
with ranked as (
    select
        transaction_id
      , date
      , campaign_name
      , source_medium
      , ad_content
      , term
      , event_value
      , ROW_NUMBER() over (
            partition by transaction_id
            order by
              case
                when source_medium is null then 2
                when LOWER(source_medium) like '%/ organic' then 2
                when LOWER(source_medium) = '(direct) / (none)' then 2
                else 1
              end asc
              , date desc
        ) as rn
    from models.ga4_transaction_attribution_events_base
    where transaction_id is not null
)
select
    transaction_id --pk
  , date as ga4_event_date
  , campaign_name as ga4_event_utm_campaign
  , source_medium as ga4_event_utm_source_medium
  , ad_content as ga4_event_utm_content
  , term as ga4_event_utm_term
  , event_value as ga4_event_attributed_revenue
from ranked
where rn = 1

ga4_campaign_performance_facts

-- Daily campaign traffic + engagement + revenue rollup, one row per date x campaign.
select
    date::varchar || '-' || COALESCE(session_campaign_name, '(none)') as id --pk (composite date + session_campaign_name)
  , date
  , session_campaign_name
  , SUM(sessions) as sessions
  , SUM(engaged_sessions) as engaged_sessions
  , SUM(total_users) as total_users
  , SUM(event_count) as event_count
  , SUM(key_events) as key_events
  , SUM(user_engagement_duration_seconds) as user_engagement_duration_seconds
  , SUM(total_revenue) as total_revenue
  , CASE when SUM(sessions) > 0
         then SUM(engaged_sessions)::float / SUM(sessions)
         else null end as engagement_rate
from models.ga4_traffic_acquisition_session_campaign_base
group by 1, 2, 3

Output

Final tables ready for use in explores in Switchboard. Thin, denormalized, transaction- and campaign-grain tables. These stay standalone GA4; to attribute orders, join the transaction outputs to your own orders model downstream on transaction_id = order_id.

ga4_transaction_attribution_output

-- One row per transaction with BOTH attribution views side by side, so downstream
-- reporting can compare session-scoped (last-click-like) vs event-scoped (data-driven).
-- Join to your orders model on transaction_id = order_id.
select
    COALESCE(s.transaction_id, e.transaction_id) as transaction_id --pk
  -- session-scoped (last-click-like)
  , s.ga4_session_date
  , s.ga4_session_utm_campaign
  , s.ga4_session_utm_source_medium
  , s.ga4_session_utm_content
  , s.ga4_session_utm_term
  -- event-scoped (data-driven, cross-session)
  , e.ga4_event_date
  , e.ga4_event_utm_campaign
  , e.ga4_event_utm_source_medium
  , e.ga4_event_utm_content
  , e.ga4_event_utm_term
  , e.ga4_event_attributed_revenue
from {{models.ga4_transaction_attribution_session_facts}} s
full outer join {{models.ga4_transaction_attribution_events_facts}} e
  on s.transaction_id = e.transaction_id

ga4_channel_performance_output

-- Daily channel/campaign performance, dashboard-ready.
select
    cp.id as id --pk (composite date + session_campaign_name)
  , cp.date
  , cp.session_campaign_name
  , cp.sessions
  , cp.engaged_sessions
  , cp.total_users
  , cp.event_count
  , cp.key_events
  , cp.user_engagement_duration_seconds
  , cp.engagement_rate
  , cp.total_revenue
from {{models.ga4_campaign_performance_facts}} cp

GA4 Models

On this page