There's a question that ends a lot of quarterly reviews badly. The client looks at the campaign table, then at their bank statement, and asks which of these campaigns actually produced that money.
You know the answer directionally. You can't show it. Spend lives in the ad platform, leads in the CRM, closed deals in a pipeline someone drags cards around in, and the cash in Stripe or QuickBooks. Four systems, four ways of counting, no shared key. So you answer with a story instead of a join, and the client hears a story.
That's the diagnosis. This piece is the blueprint: the model that ends the argument by giving every number a place to sit and a key to join on.
Four layers. Click, person, deal, cash. In the agency accounts I've opened, three of them exist in some form, with no reliable connection between any two.
What is an attribution data model, and how is it different from an attribution model?
An attribution model is a credit rule: first touch, last touch, linear, data-driven. It decides which campaign gets the point once you already know which touches belong to which sale.
An attribution data model is the structure underneath that decision. The records you keep, the identifiers on them, and the keys that let one record find another. Without it no credit rule can run, because nothing tells you the Google click in March and the payment in May involved the same human being.
Agencies keep shopping for a tool that will "do attribution" when what they're missing is an identity spine, an unbroken chain of identifiers running from the click to the invoice. Buy the tool before you have the spine and you get a prettier version of the same argument.
The spine has three junctions: click to person, person to deal, deal to cash. Each has a join key, a specific way it breaks, and a design decision you have to make on purpose.
Layer one: what has to be captured at the click?
The click layer is the only one that expires. Attribution data arrives with a visitor, and if it isn't written down it's gone within the session.
Capture all of this at entry, into fields you control:
gclid(Google) andfbclid(Meta), plus the equivalent click identifiers for any other platform you run- The full UTM set: source, medium, campaign, content, term. Not just source
- Landing page URL and referrer, unmodified
- Timestamp of first touch, in a timezone you've declared canonical
The click ID is the field I most often find missing, and the one that carries the most weight. UTMs are what you wrote on the link. The click ID is what the platform assigned, and the only value that reconciles against what it charged you.
The join to the next layer is that click ID and UTM set, written onto the contact record when the contact is created. What breaks it is anything between the ad and the form that drops query parameters: redirects that don't preserve the query string, forms in an iframe that can't see the parent URL, a journey where one hop uses a clean link. And the unfixable one, a person who clicks on their phone, converts three days later by typing your domain, and arrives carrying nothing.
Then the decision that constrains everything downstream: do you store one touch or all of them? Most CRMs, GoHighLevel included, hold a first-touch and a last-touch source on the contact and overwrite as needed. That's a credit rule baked into your storage, so you can never change your mind. The better shape is a separate touch table, one row per touch, each carrying its own click ID, source, and timestamp, keyed to the contact. Moving to a multi-touch view then becomes a query change rather than a rebuild, which is how my multi-touch marketing attribution build is put together.
Layer two: how do many clicks become one person?
The person layer is where identity gets decided. One record per human, with an identifier that never changes for the life of that contact.
Emphasis on never changes. If your contact key is an email address, a person who changes jobs becomes two people and every report that counts humans starts drifting. Use a system-generated ID that survives edits to every other field, and make every touch row, opportunity, and payment record carry it.
The dedupe rule is what breaks this layer, in both directions. Allow duplicates and one person filling in two forms becomes two contacts, two lead counts, and half the touch history on each. Deduplicate and the second submission updates the first record, which is correct for headcount and destroys attribution if the merge overwrites the original source fields. Then the awkward cases, like a shared info@ inbox collapsing three people into one contact.
So write the dedupe rule down as a business rule before configuring anything. Mine, as a default worth arguing with: match on normalised email first (lowercased, trimmed, plus-tags stripped), then on phone in E.164 format. When two records merge, keep the older contact ID as the survivor and append the loser's touch rows to it. Keep an alias table mapping every retired ID to its survivor, so last quarter's report can still be reproduced.
Layer three: how does a person become a deal you can report on?
The deal layer is the opportunity: an amount, a pipeline stage, and a link to the person. Marketing data meets sales data here, and in the accounts I've opened it's the layer with the least discipline.
The join is the contact ID sitting on the opportunity. One contact can own several opportunities, but one opportunity should never own several contacts, however much a buying committee tempts you.
Three things break it. Opportunities created by hand with no contact attached, which happens whenever a deal arrives by referral and goes straight into the pipeline. Contacts that re-enter the pipeline months later, producing a second opportunity whose attribution should probably differ from the first. And pipeline stage stored as a single overwritten field: move a card from Proposal to Won and the CRM records that it is Won. It no longer records that it was ever in Proposal, or when.
That last point quietly removes velocity, conversion by stage, and any question phrased "what did the pipeline look like on the first of last month." I've covered the mechanics in GoHighLevel overwrites your pipeline history, and it's one of the three weaknesses in Your GHL Reporting Is Lying to You.
The decision here is to make stage history append-only. Every change writes a row with the opportunity ID, the old stage, the new stage, and a timestamp, and current stage becomes a derived value rather than a stored one. You also need a rule for what an opportunity's amount means, because a human types it before the deal closes and rarely corrects it. Treat it as a forecast. Never let it reach a client report labelled as revenue.
Layer four: how do you get from a won deal to collected cash?
The cash layer is the one clients can check against their own bank, so it has to be the strictest.
The join is a deal reference carried on the payment or invoice record. The invoice knows which opportunity it belongs to, rather than the reverse, because one deal can produce many payments and a single payment field on the opportunity loses everything after the first.
What breaks it, mostly, is payments that never reference a deal. Someone sends a Stripe link from their phone on a Friday and the payment exists with an email address attached and nothing else. Subscriptions renew for years against a deal closed once. Deposits leave the opportunity saying one number and the processor a fraction of it. Refunds are a negative line in the processor and usually no event at all in the CRM, because no pipeline stage means "was won, then reversed."
Three decisions sit here, and none can be deferred.
Date grain first. Every question gets one canonical date: spend on the click date, pipeline value on the close date, collected revenue on the settlement date, cohort performance on the lead-created date. That last view is the one that answers whether January's leads paid for January's spend. Mixing grains inside one table is how a report ends up defensible on every row and wrong in total.
Then refund handling. A refund can subtract from the period it happens in, or from the period of the original payment. Both are defensible. Pick one, put the choice in the metric definition next to the report, and stay consistent. The version that causes fights is the one where nobody chose.
Last, the credit rule. Now that every touch is stored, decide what the client-facing number does with them. First touch flatters awareness campaigns, last touch flatters retargeting and brand search. I default to showing both side by side, with a third column for the cohort's collected revenue. A client who sees the same revenue attributed two ways stops treating either number as truth.
What does one lead look like moving through all four layers?
One lead, all four layers. This is a labelled illustration rather than a client record: the identifiers are invented to show the shape.
Tuesday, 9:12pm. A prospect clicks a Google search ad. The landing URL carries gclid=EAIa...7Qk and utm_campaign=q3-brand, both captured into hidden fields on the funnel page and held in a first-party cookie. Nothing is in the CRM yet. If this is where your capture ends, everything below is guesswork.
Thursday, 11:40am. They submit the form. Contact C-4471 is created with the click ID, the full UTM set, the landing page, and Tuesday's timestamp as first touch. Touch row 1 is written.
Sixteen days later the same person clicks a Meta retargeting ad and fills in a second form using a plus-addressed variant of the same email. The dedupe rule normalises it, matches C-4471, and appends touch row 2 with fbclid and the Meta campaign. No overwrite, so the contact now carries two touches from two platforms.
Day 20, sales creates opportunity O-880 linked to C-4471, with an estimated value. Stage history row: null → Discovery. Day 34 it moves Discovery → Proposal, another row rather than an edit. Day 41, Won, third row. Reporting can now show this deal spent 14 days in Discovery and 7 in Proposal, which the CRM's current-stage field could never tell you.
Day 43, invoice INV-1902 is raised carrying O-880 as its deal reference. A deposit settles on day 45, the balance 30 days later: two payment rows, same reference, different settlement dates. On day 96 a partial refund writes a negative row against the same invoice, so net collected revenue drops without anyone editing history.
Now each question goes to the layer that owns it. Which campaigns touched this deal: the touch table, both of them. How long it took to close: the stage history. What was collected: the payment rows, net of the refund. Cost to acquire: spend for q3-brand joined through the click ID, over the cohort it produced.
None of that required a new tool. It required four record types and three join keys.
Do you need a warehouse to build this?
Eventually, probably. Not first. A crude version runs in a spreadsheet with four tabs and a rule that nobody types over a raw row.
What forces a database is history. Every layer here depends on records that append rather than overwrite: touch rows, stage-change rows, payment rows including negative ones. A CRM that overwrites and a sheet that gets refreshed both lose yesterday's state, and half the questions worth answering are comparisons across time. One path there is a scheduled export into a database you control, which I've written up in exporting GoHighLevel data to Supabase.
But notice the order. Dedupe rule, date grain, credit rule, refund treatment: your reporting tool has no opinion about any of them. Buy the warehouse before you've made those calls and you've paid to store an unresolved argument.
Frequently asked questions
What's the difference between an attribution model and an attribution data model?
The attribution model is the credit rule: first touch, last touch, linear, data-driven. The attribution data model is the record structure that makes any of those rules runnable. Change credit rules in an afternoon if the data model is sound. Apply none at all if it isn't.
What join key connects an ad click to a payment?
Three keys in a chain, not one. The click ID and UTM set write onto the contact at creation, the contact ID onto the opportunity, the opportunity ID onto the invoice. Break any link and everything downstream becomes unattributable, which is why one stripped query parameter can cost you a quarter of reporting.
Should agencies use first-touch or multi-touch attribution for client reporting?
Store every touch and report at least two views. First touch tells the client which campaigns start relationships, last touch which ones close, and showing one alone invites them to treat it as fact. The decision that matters more happens at the storage layer: if your CRM keeps one source field per contact, you've chosen a credit model permanently without meaning to.
How do you handle refunds and payment plans in attribution reporting?
As additional rows against the same deal reference, never as edits. A payment plan is several payment rows with different settlement dates. A refund is a negative row keyed to the original invoice. Then declare which period a refund lands in, and put that definition on the report. Both choices are defensible. Leaving it undefined is not.
Can I build this inside GoHighLevel, or do I need something else?
The capture half belongs in the CRM and should be fixed there: click IDs into custom fields, a documented dedupe rule per location, consistent timezone settings. GoHighLevel captures utm_source, utm_medium, utm_campaign, utm_term, and utm_content as native contact fields, plus click IDs like gclid, gbraid, wbraid, fbclid, and msclkid when a visitor arrives with them, so the capture side is a mapping problem more than a build problem. The history half doesn't fit, because CRM records overwrite by design and this model depends on append-only rows. The usual landing point is GoHighLevel as the operational system, with the reporting model somewhere the past survives.
Map your attribution spine before you build anything
If you can't trace a closed deal backwards to the click that started it, what you're missing is a set of modelling decisions, and no tool sells those. Four record types, three join keys, seven decisions written down in plain language.
Mapping them is the first half of the agency CRM and reporting diagnostic I run: a paid, bounded engagement that finds where your identity chain breaks between click and cash and specifies the model your reporting should sit on. You leave with the map and the decisions documented, whether or not you build it with me.
Setting this up for a new client rather than retrofitting an existing one? Tracking and attribution setup is one of twelve boundary crossings I map in client onboarding automation, and it's the handoff most likely to fail silently.
Start with the diagnostic, or see how I work with performance-marketing agencies.
About the author. Ahmed Abdelkhalek is a Data Automation and Reporting Consultant and the founder of ChromiumData, a founder-led consultancy building reliable reporting and connected data workflows for performance-marketing agencies. He is an AWS Certified Solutions Architect (Associate), holds Microsoft's PL-300, and works with clients directly from diagnosis through delivery. Most of what he publishes comes out of builds he's shipped: CRM data that won't reconcile, reports assembled by hand every month, pipelines that break quietly after launch. More at chromiumdata.com/author/ahmed-abdelkhalek.