From Session to Stripe: Designing a Revenue Attribution Pipeline

Connecting a visit to a payment sounds like a join. Two tables, one key, done by lunchtime.

It is not a join. It is a distributed systems problem wearing a marketing hat, and it has the unpleasant property that its failure modes are silent. A broken attribution pipeline does not throw. It reports a number — plausible, well-formatted, in the right order of magnitude — that is wrong by 20%, and nobody notices for two quarters because the number moved in the direction everyone hoped it would.

This is an architectural walkthrough of the general shape of the problem: what has to happen between a session and a payment, where the association breaks, and which of those breakages are invisible.

The two streams

Everything follows from the fact that you have two sources of truth that do not know about each other.

The session stream is high-volume, anonymous, browser-originated, and unreliable. It arrives over the public internet from clients you do not control, some of which are on hotel wifi and some of which are bots. It is keyed by whatever visitor identifier you persist in a first-party cookie.

The payment stream is low-volume, authenticated, server-originated, and authoritative. It arrives as webhooks from Stripe or Shopify, is signed, and represents money that actually moved. It is keyed by a customer or order identifier that has nothing to do with your visitor cookie.

The entire pipeline exists to build a defensible bridge between those two key spaces.

Problem one: webhooks are at-least-once

Both Stripe and Shopify retry webhooks. This is correct behaviour on their part — if they cannot confirm you received an event, the safe assumption is that you did not.

The consequence is that your endpoint will receive the same logical event multiple times, and your pipeline must produce the same state whether it sees an event once or five times.

The standard mechanism is an idempotency key derived from the provider's own event identifier. Record it before processing; if it is already present, acknowledge and stop.

Two subtleties trip people up:

Record the key in the same transaction as the effect. If you write the idempotency key and the attribution in separate transactions, a crash between them leaves a key recorded with no attribution written. The retry is now suppressed and the revenue is lost permanently — a failure that fails closed and is therefore invisible.

One event is not one order. Several distinct webhook events can describe the same order at different lifecycle stages. Deduplicating on the event identifier prevents processing the same event twice; it does nothing to prevent two different events about the same order both creating an attribution. That requires deduplication at the order level as well.

Problem two: events arrive out of order

At-least-once delivery says nothing about ordering. A refund can arrive before the charge it refunds. An order update can arrive before the creation event.

If your pipeline assumes causal order, it will occasionally process a refund for an order it has never heard of, and the handling of that case determines whether you quietly lose money or quietly invent it.

The robust approach is to make handlers order-independent: each event carries enough state to describe the world, and processing computes the current truth rather than applying a delta to an assumed prior. Where that is not possible, an explicit pending state for orphaned events, reconciled later, is far better than dropping them — a dropped refund means your reported revenue never comes down.

Problem three: aggregates double-count even when tables do not

This is the subtle one, and it is where most silent revenue inflation actually comes from.

Analytical databases commonly offer table engines that deduplicate rows sharing a key — you insert the same order twice and a background merge collapses the duplicates. It is easy to conclude that this makes the pipeline idempotent.

It does not, if you have aggregate materialised views on top.

A materialised view computing a running revenue sum typically fires on every insert, not on the post-merge state. Insert the same order twice and the base table eventually holds one row while the aggregate holds two contributions. The detail view is correct. The dashboard is inflated. The two disagree, and the dashboard is the one people look at.

The only reliable fix is to guarantee exactly one insert per order, upstream of the storage layer, and to treat the database's deduplication as a safety net rather than the mechanism.

There is a further trap. Some analytical systems deduplicate identical inserts automatically as a delivery-safety feature — a genuinely identical retried batch is silently discarded. That behaviour is helpful for retries and disastrous for legitimate repeated writes, because a real second event that happens to serialise identically will vanish with no error. Attaching a unique token per insert is what distinguishes "this is a retry" from "this is a new fact."

Problem four: attribution is mutable

An order is not a fact that happens once. It gets refunded, partially refunded, edited, cancelled, or has line items changed.

So an attribution record is not append-only. If you model it as an immutable event, your reported revenue can only ever go up, which is a very comfortable bug to have and a completely wrong one.

Two design consequences:

Model the attribution as current state, keyed by order. Re-processing an order should replace the attribution, not add to it. Whether that is an upsert or a versioned record with a "current" flag depends on your storage, but the key is that revenue for an order is a value you can revise, not a sum you accumulate.

Store gross and net separately. Refunds arrive later, sometimes much later, and the question "how much did this channel produce" has two legitimate answers depending on whether you have subtracted them. Collapsing them into one number means somebody will use the wrong one for a decision.

Problem five: the identity bridge

The hardest part, and the one with the most consequential design decision in it.

Discovery happens anonymously. Someone asks an assistant, clicks through, and browses with nothing but a first-party visitor identifier. Conversion happens after authentication, under an account identifier. Nothing intrinsically connects them.

Bridging them requires the application to tell the analytics layer, at the moment of login, that this anonymous visitor and this account are the same person. Every analytics system has some version of this call.

The design question is what you store as a result — and it is the point where an attribution product can quietly become an identity company. There is a narrow version of this join that stays inside the scope of attribution, and a broad version that becomes cross-site profiling. The difference is not a matter of degree; it is a matter of what the stored data enables. That argument is worth having in full, because it is the single most consequential architectural choice in this entire pipeline.

Problem six: which touch gets the credit

Once the join works, a visitor arrives with a history: possibly several sessions, from several sources, over several weeks.

Attribution model selection is genuinely a business decision rather than an engineering one, and it should be explicit and changeable rather than baked into the write path. The engineering requirement is to store every touch and apply the model at read time. A pipeline that applies last-touch at write time has destroyed the information needed to ever ask a different question.

This matters more for AI traffic than for any other channel, because the AI touch is characteristically early — the assistant is where discovery happens, and the final touch before purchase is usually a branded search or a direct visit. A last-touch model applied at write time will therefore erase almost the entire channel, permanently. Every standard model has a version of this problem.

The thing that saves you: reconciliation

Every failure above is silent. None throws an exception; all produce a number.

The only real defence is a scheduled reconciliation job that compares your attributed totals against the payment provider's own reporting for the same period, and alerts on divergence beyond a threshold.

This is boring, unglamorous, and the single highest-value component in the system. It is also the one most often skipped, because when you build the pipeline it works, and the failures that reconciliation catches are the ones that develop later — a webhook handler that starts timing out, a schema change that breaks a join, a retry storm during an incident.

Order count is the better tripwire than revenue total, incidentally. Revenue moves for legitimate business reasons and a drift is easy to rationalise. A mismatch in the number of orders is unambiguous: either you are missing orders or you are counting some twice, and both are bugs.

The shape of a correct pipeline

Pulling it together, the invariants worth designing around:

• Verify webhook signatures against the raw body, before parsing.
• Acknowledge quickly; do the work asynchronously. Payment providers time out and retry, and a slow handler manufactures duplicates.
• Deduplicate at both the event level and the order level. They are different problems.
• Write the idempotency record and its effect in one transaction, or accept that you will lose events at crash boundaries.
• Guarantee exactly one insert per order upstream of storage. Never rely on the database's deduplication as the mechanism.
• Store every touch; apply the attribution model at read time.
• Model attribution as revisable state, not as an accumulating sum.
• Keep gross and net separate, and store the original currency alongside the converted amount.
• Reconcile on a schedule, and alert on order count rather than revenue.

None of this is exotic. It is ordinary distributed systems discipline. What makes it worth writing down is that the consequences of skipping any of it are invisible — and in a system whose entire purpose is to tell you which channel deserves your budget, an invisible error is not a bug in a report. It is a bad decision, made confidently, every quarter until someone checks.