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 WindowThe time after an ad interaction in which a conversion can still be credited to it. 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 GroupingRules that assign each visit or conversion to a channel, such as organic search or email, from its source, medium and campaign values., but the same table should feed every model.
- The SQL patterns from the SQL guide: common table expressions, joins at the right grainWhat one row of a table represents, such as one order, one order line, one session or one customer per day; queries and metric definitions must match it. and window functions.
- A dataset for the model's tables, here called
attribution, so the intermediate tables can be inspected.
Build a session table with its source
Each row is one SessionA group of one user's interactions with a site or app in one visit, ended by inactivity.: 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_timestampcolumn, 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.mediumBefore 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.
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) = 1Count 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.
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 TouchpointAny interaction between a person and a brand, paid, owned or earned, before, during or after a purchase. on one conversion's path. The converting session itself is included, since it started before the conversion.
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.
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.pathsKeep 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.
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 DESCCheck 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) > @toleranceAn 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.
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.
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.
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.
| Channel | Last non-direct | First touch | Linear | Position-based |
|---|---|---|---|---|
| Paid search | 412 | 318 | 371.4 | 366.2 |
| Organic search | 286 | 302 | 291.8 | 293.5 |
| Paid social | 98 | 241 | 166.3 | 172.9 |
| 176 | 92 | 138.6 | 135.0 | |
| Direct | 118 | 141 | 120.2 | 121.6 |
| Referral | 74 | 70 | 75.7 | 74.8 |
| No session in window | 76 | 76 | 76 | 76 |
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.
| Check | How | What a failure means |
|---|---|---|
| Credit sums to conversions | The per-conversion check above, on every run | A duplicate, a non-unique key or a rule that misses a path length |
| Unmapped share | Credit landing in Unmapped as a share of the total | New source or medium values the mapping table does not know |
| No-session share | Conversions with no session in the window, tracked month to month | A consent change, a tagging break or a window that is too short |
| Last-click agreement | Last non-direct against GA4's paid and organic last click for a closed period | A difference in key event definition, period or traffic-source record |
| Stability | Channel shares month to month under a fixed rule | Real change in the mix, or a change in the data that needs explaining |
| Version record | Window, weights, mapping version and identifier stored with each run | Without 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.
