ServicesAboutNotesContact Get in touch →
EN FR
Note

GA4 Acquisition Performance Mart

A daily x source/medium grain mart for GA4 acquisition reporting — aggregating sessionized events into dashboard-ready metrics with conversion rates and revenue.

Planted
ga4dbtbigquerydata modelinganalytics

The acquisition performance mart provides channel-level performance: which sources and mediums drive sessions, conversions, and revenue, and how those metrics change over time. It is the GA4 equivalent of the Traffic Acquisition report in the GA4 interface, built from sessionized data with user-controlled definitions.

The key design decision is the grain. Not one row per session (that’s nearly as large as your intermediate model and too granular for dashboards). Not one row per user (that’s a different question). The acquisition mart aggregates to daily x source x medium, producing roughly 500 rows per day instead of 50,000 sessions. Dashboards query fast, storage costs stay minimal, and the pre-calculated rates eliminate repeated computation.

Why Not Aggregate Directly from Raw Events?

You could skip the intermediate sessionized model and aggregate directly from the base model. Some teams do this, and for a single dashboard it works. But it means every mart that needs session context (landing page, session duration, conversion flags) has to rebuild that context independently. The sessionized intermediate model computes session context once; every mart downstream inherits it for free.

The acquisition mart demonstrates this payoff. Session-level conversion flags like session_has_purchase already exist on every event row. The mart just counts them. Without the sessionized model, the mart would need its own window functions to determine which sessions converted — duplicating logic that the session mart, user mart, and funnel analysis all need too.

The Two-Stage Aggregation

The mart uses a two-stage approach: first aggregate to session grain, then aggregate sessions to daily x channel grain.

Stage 1: Session-Level Deduplication

WITH sessions AS (
    SELECT
        ga4__event__session_key,
        ga4__event__user_pseudo_id,
        MIN(ga4__event__date) AS session_date,
        MAX(ga4__event__session_source) AS session_source,
        MAX(ga4__event__session_medium) AS session_medium,
        MAX(ga4__event__session_campaign) AS session_campaign,
        MAX(CAST(event__session_has_purchase AS INT64)) AS has_purchase,
        MAX(CAST(event__session_has_add_to_cart AS INT64)) AS has_add_to_cart,
        MAX(CAST(event__session_has_begin_checkout AS INT64)) AS has_begin_checkout,
        SUM(ga4__event__purchase_revenue_usd) AS session_revenue_usd,
        COUNT(*) AS event_count
    FROM {{ ref('int__event__sessionized') }}
    GROUP BY ga4__event__session_key, ga4__event__user_pseudo_id
)

This CTE collapses the event-grain sessionized table to one row per session. The MAX on boolean flags works because every event in a session carries the same flag value (it was set via a window function over the session partition). SUM on revenue collects all purchase amounts in the session.

Including ga4__event__user_pseudo_id in the GROUP BY isn’t strictly necessary (the session key already uniquely identifies a session), but it makes the user count in Stage 2 possible without a subquery.

Stage 2: Daily x Channel Aggregation

aggregated AS (
    SELECT
        session_date AS acquisition__date,
        COALESCE(session_source, '(direct)') AS acquisition__source,
        COALESCE(session_medium, '(none)') AS acquisition__medium,
        COALESCE(session_campaign, '(not set)') AS acquisition__campaign,


        -- Volume metrics
        COUNT(*) AS acquisition__session_count,
        COUNT(DISTINCT ga4__event__user_pseudo_id) AS acquisition__user_count,


        -- Conversion metrics
        SUM(has_purchase) AS acquisition__purchase_count,
        SUM(has_add_to_cart) AS acquisition__add_to_cart_count,
        SUM(has_begin_checkout) AS acquisition__begin_checkout_count,


        -- Revenue
        SUM(session_revenue_usd) AS acquisition__revenue_usd,


        -- Engagement
        AVG(event_count) AS acquisition__avg_events_per_session


    FROM sessions
    GROUP BY 1, 2, 3, 4
)

The COALESCE patterns handle null attribution gracefully. Sessions without a source become (direct), matching GA4’s own convention. This prevents null values from creating aggregation problems downstream.

Pre-Calculated Rates

The final layer adds derived metrics that dashboards would otherwise compute repeatedly:

enriched AS (
    SELECT
        *,
        SAFE_DIVIDE(acquisition__purchase_count, acquisition__session_count)
            AS acquisition__conversion_rate,
        SAFE_DIVIDE(acquisition__revenue_usd, acquisition__session_count)
            AS acquisition__revenue_per_session_usd,
        SAFE_DIVIDE(acquisition__revenue_usd, acquisition__purchase_count)
            AS acquisition__avg_order_value_usd
    FROM aggregated
)

SAFE_DIVIDE prevents division-by-zero errors when a source/medium combination has zero sessions or zero purchases on a given day. BigQuery returns NULL instead of failing, which BI tools handle cleanly (showing blank or zero depending on configuration).

These rates belong in the mart, not in the BI tool. Calculating conversion rate as purchases / sessions sounds simple, but if three different dashboards implement it slightly differently (one uses NULLIF, another uses CASE WHEN, a third uses IF), you get three different numbers. Pre-calculating in the mart ensures everyone sees the same rate.

Model Configuration

{{ config(
    materialized='table',
    partition_by={
        "field": "acquisition__date",
        "data_type": "date",
        "granularity": "day"
    },
    cluster_by=['acquisition__source', 'acquisition__medium'],
    tags=['mart', 'reporting', 'ga4']
) }}

Materialized as table, not incremental. Mart models are small enough (hundreds of rows per day) that full rebuilds are cheap. The complexity of incremental logic isn’t worth the marginal performance gain at this grain.

Partitioned by date. Dashboard queries that filter to “last 30 days” scan only 30 partitions. BigQuery’s minimum billing unit is 10 MB per partition, so even with 500 rows per day, partitioning is worthwhile because it controls cost on downstream queries.

Clustered by source and medium. The most common filter pattern in acquisition dashboards is “show me paid search” or “compare organic vs paid.” Clustering by these fields means BigQuery reads only the relevant row groups within each partition.

Column Naming Convention

The acquisition__ prefix serves two purposes. First, it makes the mart self-documenting — every column clearly belongs to the acquisition domain. Second, it prevents naming collisions when the mart is joined with other tables in a BI tool. date is ambiguous; acquisition__date is not.

This follows the same entity__attribute convention used throughout the base and intermediate models.

Why the Intermediate Layer Still Matters

The acquisition mart gives you aggregate channel performance. But it can’t answer event-sequence questions. “Sessions where the user viewed a product, then added to cart, then purchased, in that order” requires event-level data that this mart has aggregated away.

-- This query needs the sessionized intermediate model, not the mart
WITH sequenced AS (
    SELECT
        ga4__event__session_key,
        MAX(CASE WHEN ga4__event__name = 'view_item'
            THEN event__session_event_number END) AS view_item_position,
        MAX(CASE WHEN ga4__event__name = 'add_to_cart'
            THEN event__session_event_number END) AS add_to_cart_position,
        MAX(CASE WHEN ga4__event__name = 'purchase'
            THEN event__session_event_number END) AS purchase_position
    FROM {{ ref('int__event__sessionized') }}
    WHERE event__session_has_purchase
    GROUP BY ga4__event__session_key
)


SELECT COUNT(*) AS proper_funnel_sessions
FROM sequenced
WHERE view_item_position < add_to_cart_position
    AND add_to_cart_position < purchase_position

The intermediate model makes event-sequence questions answerable. The mart makes dashboards fast and cheap. They serve different purposes. Use both — the event-grain sessionization pattern is what makes this layered approach work.

Relationship to Other Marts

The acquisition mart is one of typically three GA4 marts:

  • Acquisition mart (this note): Daily x source/medium grain. Answers “which channels perform best?”
  • User mart: User grain. Answers “what does the full customer journey look like?”
  • Session mart: Session grain. Answers detailed session-level questions. Often the widest mart, derived from the sessionized model with a simple GROUP BY.

Each mart serves a different audience and a different grain. All three derive from the same sessionized intermediate model, which is the single source of truth.