Connecting analytics to revenue without building a warehouse

The standard advice for revenue attribution is to build a warehouse, join clickstream to billing, and model multi-touch. For a team of five that advice is a trap: six weeks of plumbing, a dbt project nobody maintains, and a dashboard that answers a question from last quarter.

There is a smaller version that gets eighty percent of the value in an afternoon. It rests on one idea: attach attribution to the lead record at capture time, and never reconstruct it later.

The join key is the lead, not the visitor

Visitor identifiers are unstable — they reset, they are blocked, they do not cross devices. A lead record is the first stable identity you get, and it already has an email address. Everything you need to attribute lives on that row: source, medium, campaign, landing page, first and last touch, the day it was created.

Once that row exists, revenue attribution becomes a single join: lead in your analytics, email or customer id in Stripe, sum the amounts.

select l.source,
       count(*)                as leads,
       count(s.id)             as customers,
       sum(s.amount_cents)     as revenue
from leads l
left join subscriptions s on s.customer_email = l.email
group by l.source
order by revenue desc nulls last;

Store attribution once, at capture

The temptation is to keep raw events and derive attribution later, when the model is better. In practice the session context is gone by then — cookies expire, session storage clears, and the campaign that introduced the customer is unrecoverable. Write it down at the moment of capture, even if the model is crude.

Store both first and last touch on the lead. First touch answers which channel finds customers; last touch answers which channel closes them. Keeping both costs two columns and prevents the argument that comes with picking one.

Keep the identifiers clean

1. 1.Normalise emails to lowercase and trim whitespace before writing the lead. Case-mismatched duplicates break every join later.
2. 2.Never overwrite the original source when a known lead returns. Their second visit is a new campaign, not a new origin.
3. 3.Send an immutable customer id into your payment provider so a later email change does not orphan the revenue.
4. 4.Backfill cautiously. If you import old leads, mark them as imported rather than merging them into organic.

What you give up

This approach is lead-based, so it says nothing about visitors who never convert, and nothing about expansion revenue months after signup. Multi-touch credit assignment across a long enterprise cycle also needs more than two columns. If those questions matter, you will eventually want the warehouse — but you will want it for those questions, not to discover that LinkedIn produces your best customers.

Start with the join, answer the budget question this quarter, and let the model grow only when a real decision depends on it.