Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics › Attribution

Building an Attribution Model in SQL

Build rule-based attribution from the Google Analytics BigQuery export: sessions and sources, ordered touchpoints, lookback windows, credit rules, checks.
Tutorialadvanced10 min readUpdated

Why build attribution yourself

GA4's interface shares key-event credit under the reporting attribution model, and the GA4 attribution settings guide explains the model setting, the lookback windows and the reports where they take effect. The attribution models comparison records that Google has retired first click, linear, time decay and position-based from GA4 and Google Ads reporting, so where those rules are still wanted they have to come from your own analysis of exported paths. The basics guide ends on the same point: its key events query becomes a custom attribution model once each user's touchpoints are ordered in time.

Building the model yourself buys four things. Transparency: every rule, weight and window is in the query and can be read, challenged and versioned. Choice of rules: first-touch and position-based views, which show the channels that open paths, are available again. Joins: credit can be computed on conversions defined with data the interface does not have, such as orders that were not refunded or leads that became opportunities. Stability: the model does not change under you when the interface's model is retrained; it changes when you change it.

What it does not buy is truth. A rule is an assumption about how people decide, and the SQL applies it faithfully to the paths the export recorded. The last step of this tutorial writes that limit down so the model is never read as more than it is.

Before you start

  • The export linked and populated for the period you want to model, plus the lookback window before it, with permission to query the dataset and create tables. The basics guide covers the link and notes that nothing before the link date exists.
  • The list of key events the model will credit, kept as a small reference table, because the export does not flag key events; they are ordinary events selected by name.
  • A lookback window chosen deliberately, either matching the property's setting so that comparisons with the interface are fair, or set from your own time-to-conversion data. The Lookback Window is a parameter in every query below.
  • A channel mapping table that turns source and medium values into channel names. The attribution settings guide summarises the default channel group rules; your table can follow them or define your own Channel Grouping, but the same table should feed every model.
  • The SQL patterns from the SQL guide: common table expressions, joins at the right grain and window functions.
  • A dataset for the model's tables, here called attribution, so the intermediate tables can be inspected.
  1. Build a session table with its source

    Each row is one Session: the pseudonymous user identifier with the session identifier parameter, as the basics guide explains, since sessions are not rows in the export. Take the session's start as its earliest event time and its source and medium from the session last-click record, the nearest match to the interface's session dimensions. The export stores each event's time as a timestamp in microseconds; the query reads it from the event_timestamp column, which you should confirm in your own dataset's schema. Build the table from the start of the lookback window before the reporting period, not from the start of the period itself.

    CREATE OR REPLACE TABLE attribution.sessions AS
    WITH s AS (
      SELECT
        user_pseudo_id,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
        MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start,
        MAX(session_traffic_source_last_click.manual_campaign.source) AS source,
        MAX(session_traffic_source_last_click.manual_campaign.medium) AS medium
      FROM `project.dataset.events_*`
      WHERE _TABLE_SUFFIX BETWEEN @sessions_start AND @period_end
      GROUP BY user_pseudo_id, session_id
    )
    SELECT s.*, COALESCE(m.channel, 'Unmapped') AS channel
    FROM s
    LEFT JOIN attribution.channel_map AS m
      ON m.source = s.source AND m.medium = s.medium
    

    Before trusting the result, look at the distinct source and medium pairs that land in Unmapped. The basics guide describes the last-click record as covering manual and Google campaign fields, so check how auto-tagged paid search sessions and direct sessions appear in your data and extend the mapping to match. The attribution settings guide notes that GA4's default rules treat a source of "(direct)" with a medium of "(none)" or "(not set)" as Direct; the mapping table should say the same.

  2. Build the conversions table

    Each row is one conversion: a key event by name, with its user, time and value. For purchases, the transaction identifier in the e-commerce record is the natural key, and duplicates on it should be removed so that a purchase sent twice is credited once. Other key events need a key built from the user and the event time.

    CREATE OR REPLACE TABLE attribution.conversions AS
    SELECT
      user_pseudo_id,
      COALESCE(ecommerce.transaction_id,
               CONCAT(user_pseudo_id, '-', CAST(event_timestamp AS STRING))) AS conversion_id,
      event_name,
      TIMESTAMP_MICROS(event_timestamp) AS converted_at,
      ecommerce.purchase_revenue AS value
    FROM `project.dataset.events_*`
    WHERE _TABLE_SUFFIX BETWEEN @period_start AND @period_end
      AND event_name IN (SELECT event_name FROM attribution.key_event_list)
    QUALIFY ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY converted_at) = 1
    

    Count the rows and compare them with the interface's key event count for the same closed period. They should be close; if they are not, resolve that first, because every later number inherits the gap.

  3. Join conversions to earlier sessions within the window

    Join each conversion to the same user's sessions that started before it and within the lookback window. Use a left join so that conversions with no recorded session in the window are kept rather than silently dropped; they become their own row in the output.

    CREATE OR REPLACE TABLE attribution.touchpoints AS
    SELECT
      c.conversion_id,
      c.value,
      s.session_id,
      s.session_start,
      COALESCE(s.channel, 'No session in window') AS channel
    FROM attribution.conversions AS c
    LEFT JOIN attribution.sessions AS s
      ON s.user_pseudo_id = c.user_pseudo_id
      AND s.session_start <= c.converted_at
      AND s.session_start >= TIMESTAMP_SUB(c.converted_at, INTERVAL @lookback_days DAY)
    

    Each row is now one Touchpoint on one conversion's path. The converting session itself is included, since it started before the conversion.

  4. Order the touchpoints

    Number each conversion's touchpoints in time, count them, and mark the position of the last touch that was not direct. Ties on start time are broken by the session identifier so the order is repeatable.

    CREATE OR REPLACE TABLE attribution.paths AS
    SELECT
      *,
      COUNT(*) OVER (PARTITION BY conversion_id) AS path_length,
      MAX(IF(channel != 'Direct', position, NULL)) OVER (PARTITION BY conversion_id) AS last_non_direct_position
    FROM (
      SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY session_start, session_id) AS position
      FROM attribution.touchpoints
    )
    

    Look at the distribution of path lengths before going further. It shows how much the multi-touch rules can differ from single-touch ones: where most paths have one touch, every rule gives nearly the same answer.

  5. Apply the credit rules

    Compute each rule's share of the conversion for every touchpoint. Last non-direct gives the whole conversion to the last touch that was not direct, or to the last touch when the whole path was direct, mirroring the way GA4's paid and organic last click ignores direct arrivals. First gives it to the first touch. Linear splits it equally. Position-based gives a fixed weight to each end and spreads the rest across the middle; the weight is a parameter, and a two-touch path splits equally.

    CREATE OR REPLACE TABLE attribution.credit AS
    SELECT
      conversion_id,
      channel,
      value,
      IF(position = COALESCE(last_non_direct_position, path_length), 1, 0) AS last_non_direct,
      IF(position = 1, 1, 0) AS first_touch,
      1 / path_length AS linear,
      CASE
        WHEN path_length = 1 THEN 1
        WHEN path_length = 2 THEN 0.5
        WHEN position = 1 OR position = path_length THEN @end_weight
        ELSE (1 - 2 * @end_weight) / (path_length - 2)
      END AS position_based
    FROM attribution.paths
    

    Keep every weight and window in parameters rather than in the query text, and record the values used in the model's description, so that two runs can be compared knowing exactly what differed.

  6. Total credit by channel

    Sum each rule's credit by channel, as counts and as value. Credit is fractional under the multi-touch rules, so report it with a decimal place rather than rounding it away.

    SELECT
      channel,
      SUM(last_non_direct) AS last_non_direct,
      SUM(first_touch) AS first_touch,
      ROUND(SUM(linear), 1) AS linear,
      ROUND(SUM(position_based), 1) AS position_based,
      ROUND(SUM(position_based * value), 2) AS position_based_value
    FROM attribution.credit
    GROUP BY channel
    ORDER BY last_non_direct DESC
    
  7. Check that credit sums to conversions

    Every rule must distribute exactly one conversion per conversion. Check it at two levels: in total against the conversions table, and per conversion, listing any conversion whose credit under a rule does not add up to one.

    SELECT conversion_id,
      SUM(last_non_direct) AS lnd, SUM(first_touch) AS ft,
      SUM(linear) AS lin, SUM(position_based) AS pb
    FROM attribution.credit
    GROUP BY conversion_id
    HAVING SUM(last_non_direct) != 1 OR SUM(first_touch) != 1
      OR ABS(SUM(linear) - 1) > @tolerance OR ABS(SUM(position_based) - 1) > @tolerance
    

    An empty result is a pass. Failures usually come from duplicated sessions, a conversion key that is not unique, or a credit rule that misses a path length. Fix the cause and rerun from the step where it entered.

  8. Compare the models side by side

    Put the four rules in adjacent columns, with each channel's share of total credit and its change against last non-direct. Channels that gain under first touch and position-based open paths that other channels close; channels that lose are closers. That shift is the same signal assisted conversions give in the interface, made explicit. Then compare the last non-direct column with GA4's paid and organic last click view for the same key event and closed period. They should be close but will not be identical, and the diagnosis page for an export that does not match GA4 explains the expected gaps, from modelled data and estimation in the interface to the choice of traffic-source record.

  9. Write down what the model cannot see

    Write a short statement that travels with every chart the model feeds. It should say that the model only contains sessions the export recorded, so visits from people who declined analytics consent are missing; that paths are built on a device-level identifier, so a journey across a phone and a laptop appears as two partial paths; that impressions, views without clicks, offline media and word of mouth are invisible; and that the rules assign credit, they do not measure cause. Add the window, the weights, the channel mapping version and the identifier choice. Data-driven credit is not rebuilt here; the data-driven attribution guide covers what the interface's model does and why it still cannot see the counterfactual.

Four models on one month (illustrative)

Running the steps on a single month shows how far the rules move credit, and where.

Illustrative example — not a real result

Lumenhatch Lighting rebuilds four models from its export

Lumenhatch Lighting, an invented lighting retailer, models its October purchases with a 30-day lookback window and position-based end weights of 0.4. The conversions table holds 1,240 purchases after 9 duplicated transaction identifiers are removed. The first run of the credit check fails: position-based credit sums to 1,198.6, because 207 two-touch paths received only the two end weights. Adding the explicit two-touch rule fixes it, and every rule then sums to 1,240.

ChannelLast non-directFirst touchLinearPosition-based
Paid search412318371.4366.2
Organic search286302291.8293.5
Paid social98241166.3172.9
Email17692138.6135.0
Direct118141120.2121.6
Referral747075.774.8
No session in window76767676

Paid social gains most when early touches count: from 98 purchases under last non-direct to 241 under first touch and about 173 under position-based. Email moves the other way, from 176 to 92 under first touch, which fits its role in bringing back people who already know the brand. The 76 purchases with no session in the window are a reminder that some paths start on another device or outside the export's view. The team reports position-based beside last non-direct in its monthly channel review, tracks the no-session row each month, and proposes a holdout on paid social prospecting to test whether the credit it gains reflects demand it actually creates.

Keeping the model honest

A model built once and left running drifts as the site, the tagging and the channel mapping change. A few standing checks keep it trustworthy.

CheckHowWhat a failure means
Credit sums to conversionsThe per-conversion check above, on every runA duplicate, a non-unique key or a rule that misses a path length
Unmapped shareCredit landing in Unmapped as a share of the totalNew source or medium values the mapping table does not know
No-session shareConversions with no session in the window, tracked month to monthA consent change, a tagging break or a window that is too short
Last-click agreementLast non-direct against GA4's paid and organic last click for a closed periodA difference in key event definition, period or traffic-source record
StabilityChannel shares month to month under a fixed ruleReal change in the mix, or a change in the data that needs explaining
Version recordWindow, weights, mapping version and identifier stored with each runWithout it, two runs cannot be compared

When the mapping or a rule changes, restate history under the new version rather than splicing two versions into one series, the same rule the comparison guide gives for changing models in the interface. The rule-based models themselves are described in the attribution models framework, and the attribution hub sets them beside experiments and mix modelling, which answer the causal questions this model cannot. The academy capstone on a finance-grade measurement system for a builders' merchant practises placing a rule-based model built in SQL inside one system with a warehouse model, tested loads, a reviewed mix model and agreed lead values.

Common mistakes

  • Building sessions from the session identifier alone. A session is the user identifier and the session identifier together.
  • Starting the session table at the reporting period. Paths for early conversions are cut short; start it a full lookback window earlier.
  • Using an inner join. Conversions with no session in the window disappear and the totals stop reconciling.
  • Leaving duplicates in the conversions table. A purchase sent twice is credited twice.
  • Forgetting the two-touch case. Position-based credit no longer sums to one.
  • Hard-coding weights and windows. Nobody can tell later which version produced which number.
  • Reading rule-based credit as cause. It describes recorded paths; a test measures effect.
  • Expecting the interface to match. Different models, windows and data; reconcile last click, explain the rest.

Frequently asked questions

Why does my SQL model not match GA4's attribution reports?

Because it is a different calculation on different data. GA4's reports apply the property's reporting attribution model, which is data-driven by default, with its own lookback windows, and the interface can include modelled data and estimation that the export does not contain. Your model applies your rules to the sessions the export recorded. Expect last-click views on both sides to be close over a closed period and other views to differ; the diagnosis page for an export that does not match GA4 sorts the expected gaps from query errors.

Can I rebuild GA4's data-driven attribution in SQL?

Not as Google runs it. Google describes its data-driven model as comparing converting and non-converting paths with a counterfactual approach that weighs factors such as timing, device and order of exposure, and the details of the method and its training are not something the export lets you reproduce. A rule-based model in SQL is a transparent alternative with known assumptions, not a replica. If you need path-based credit estimated from data, the data-driven attribution guide explains what the interface's model does and where it stops.

Should I use the pseudonymous identifier or the User-ID to build paths?

Start with the pseudonymous identifier, which every event carries and which is device-level, so paths stop at the edge of a device. Where the site sets a User-ID for signed-in people, using it for those users joins their sessions across devices and lengthens their paths, but only for the people who sign in, which makes paths for members and anonymous visitors behave differently. Whichever you choose, apply it consistently, record it beside the model, and compare channel credit under both before reporting either.

Terms used on this page

Frameworks

Where this page is used

Part of these learning paths

Sources

  1. [GA4] Set up BigQuery Export — Google Analytics Help
  2. Google Analytics Help — Google Analytics Help

Related