A retail-media dashboard can show perfectly plausible revenue numbers while answering the wrong question. If the same order follows a search click, a social click, and another search click, which campaign receives credit?
A recent public commit in my retail-media analytics pipeline adds a deterministic seven-day last-touch attribution model and six SQLite contract tests. This walkthrough explains the actual implementation, the edge cases it covers, and the important business questions it does not answer.
The contract before the query
We start with two synthetic tables:
-
orders(order_id, customer_id, order_ts, revenue): one row per order. -
ad_touches(touch_id, customer_id, touch_ts, channel, campaign_id): one row per advertising interaction.
The model attributes an order to the most recent eligible paid touch for the same customer, occurring no later than the order and no earlier than seven days before it. The seven-day boundary is inclusive. An order with no eligible touch receives no attribution.
Two details make the output reproducible: every order has a unique ID, and ties at the same timestamp are broken using the greatest touch ID. The second rule is a deterministic convention, not a claim that the lexicographically greatest ID represents a more influential advertisement.
For this SQLite example, timestamps are consistently formatted as UTC YYYY-MM-DD HH:MM:SS. Mixing time zones or formats would invalidate comparisons. Negative revenue rows are excluded; returns and refunds require their own accounting design.
The SQL implementation
The repository contains this executable SQLite-compatible query in sql/last_touch_attribution.sql:
WITH eligible AS (
SELECT o.order_id, o.customer_id, o.order_ts, o.revenue,
t.touch_id, t.channel, t.campaign_id, t.touch_ts,
ROW_NUMBER() OVER (
PARTITION BY o.order_id
ORDER BY t.touch_ts DESC, t.touch_id DESC
) AS touch_rank
FROM orders o
JOIN ad_touches t ON t.customer_id = o.customer_id
WHERE t.touch_ts <= o.order_ts
AND t.touch_ts >= datetime(o.order_ts, '-7 days')
AND o.revenue >= 0
),
last_touch AS (
SELECT order_id, channel, campaign_id, revenue
FROM eligible WHERE touch_rank = 1
)
SELECT channel, campaign_id,
COUNT(*) AS attributed_orders,
ROUND(SUM(revenue), 2) AS attributed_revenue
FROM last_touch
GROUP BY channel, campaign_id
ORDER BY channel, campaign_id;
The first CTE finds eligible order-touch pairs. The window function ranks touches within each order, newest first. Filtering touch_rank = 1 ensures at most one winning touch per order. The final query aggregates winning orders and their revenue by channel and campaign.
Notice that ROW_NUMBER() partitions by order ID, not customer ID. A returning customer can legitimately place two separate orders; each order needs its own attribution decision. Partitioning by customer would incorrectly discard one of those decisions.
Walk through a synthetic example
Suppose customer u1 has two touches:
| Touch | UTC time | Channel |
|---|---|---|
| a | Jan 2, 10:00 | search |
| b | Jan 3, 10:00 | social |
The customer places an $80 order on January 4 at 10:00 UTC. Both touches qualify, but b is later. The model therefore reports one attributed order and $80 for the social campaign. It does not report $80 for each touch, avoiding double-credit within this particular model.
Now move the search touch to exactly seven days before the order. It remains eligible. Move it one second earlier and it becomes ineligible. Add a touch after the order and it must never win. These are not cosmetic edge cases: small timestamp errors can change campaign-level revenue.
Contract tests as executable business rules
Rather than relying only on a dashboard screenshot, the repository includes six Python unittest cases. They create an in-memory SQLite database, insert synthetic rows, execute the real SQL file, and assert the resulting aggregates.
Run them from the repository root:
python -m unittest discover -s tests \
-p 'test_last_touch_attribution.py' -v
The cases cover latest eligible touch, future and too-old touches, the exact seven-day boundary, deterministic timestamp ties, multiple orders from one customer, and customers without matching touches.
Here is a small, independent illustration of the same testing technique:
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (
order_id TEXT PRIMARY KEY,
customer_id TEXT,
order_ts TEXT,
revenue REAL
);
CREATE TABLE ad_touches (
touch_id TEXT PRIMARY KEY,
customer_id TEXT,
touch_ts TEXT,
channel TEXT,
campaign_id TEXT
);
INSERT INTO orders VALUES
('o1','u1','2026-01-04 10:00:00',80);
INSERT INTO ad_touches VALUES
('a','u1','2026-01-02 10:00:00','search','s1'),
('b','u1','2026-01-03 10:00:00','social','m1');
""")
with open("sql/last_touch_attribution.sql") as f:
rows = db.execute(f.read()).fetchall()
assert rows == [("social", "m1", 1, 80.0)]
db.close()
This illustration assumes it is run from the checked-out repository root. The checked-in tests use a path relative to the test file, which is more robust.
The important principle is to test the actual production-intended query artifact, rather than rewriting its logic in Python and accidentally testing a different implementation. These six tests verify specified examples; they do not establish performance at advertising-platform scale.
What changes in a warehouse or lakehouse?
The business contract can survive migration to Spark SQL, Databricks, Snowflake, or BigQuery, but the SQL syntax cannot simply be copied unchanged. SQLite's datetime(order_ts, '-7 days') is dialect-specific. Replace it with the target engine's documented timestamp arithmetic, and verify the exact boundary behavior with the same fixtures.
For a medallion-style architecture, a reasonable proposed design is:
- Bronze: preserve source order and touch events, ingestion timestamps, and raw identifiers.
- Silver: validate timestamps, unique IDs, currency, consent rules, deduplication, and eligible identity mappings.
- Gold: calculate one winning touch per order and aggregate campaign reporting metrics.
This is an architectural recommendation, not a claim that the SQLite example currently runs on Databricks. At scale, monitor join cardinality: one order can match many touches before the window filter. Partitioning, clustering, incremental recomputation, and retention policies need workload-specific measurement.
Late-arriving touches also matter. If yesterday's order was attributed before all eligible touch events arrived, today's recomputation could select a different winner. Define a lateness watermark and a restatement policy rather than silently treating early results as immutable.
The limitations matter more than the dashboard
Last-touch attribution is descriptive, not causal. It answers which eligible touch received credit under a rule. It does not prove that the touch generated an incremental sale. A customer may have purchased anyway.
The current synthetic model also omits consent management, identity resolution, cross-device matching, privacy thresholds, bot filtering, viewability, organic interactions, refunds, and currency conversion. Using raw customer identifiers without an approved privacy and access-control design would be inappropriate in a real deployment.
For revenue reporting, consider integer minor units or fixed-precision decimals rather than floating-point values. The SQLite example uses REAL for simplicity and rounds the final sum; that is not a general-purpose financial ledger strategy.
Practical takeaways
Specify the attribution contract before optimizing SQL. Decide who is eligible, how long the window lasts, how ties break, and what happens to unmatched orders.
Test boundaries, not just happy paths. An exact seven-day touch, a future touch, and a repeated customer are excellent contract tests.
Keep descriptive credit separate from incrementality. Attribution reports can support analysis, but causal lift requires appropriate experimental or quasi-experimental methods.
The complete, inspectable implementation is in the public repository, with model documentation and the November 14 implementation commit.
Further reading
- SQLite window functions
- SQLite date and time functions
- Google Analytics attribution overview
- Apache Spark SQL window syntax
A reliable metric is not merely one that executes successfully. It is one whose definition, boundary cases, and limitations are explicit enough to audit.
Top comments (0)