Revenue source audit

Source

CRM deals and contacts, billing charges

Rule

One source per deal, a sale only with confirmed payment

Outcome

Lead, win, paid sale and revenue by source and vintage

Activation

Budget review, revenue committee and AI agent

What it solves

Each deal has a single source, chosen by the same rule, and the total by source ties to the grand total.
Each source's sale counts only when billing confirms it, not when someone marked it won in the CRM.
Sources that generate many leads and little revenue appear side by side with the ones that close.

Business questions it answers

Ask your agent these questions once the case is live.

Which sources brought the most paid revenue in the last six months, and which brought the most leads?

What is each source's win rate, by lead creation vintage?

How many of the sales marked won in the CRM were actually paid, by source?

How much of the funnel has no source or came from a list import?

How much did each paid sale cost for the paid media sources?

What you need

CRM connected to Nekt, with source fields, UTMs and stage history
Billing or ERP connected, with a key to tie each charge to the deal
The agreed list of sources, and which ones are inbound and which are outbound

What you get

A table with one row per deal, the resolved source and the method used to resolve it
Funnel by source and vintage: lead, deal, CRM win, paid sale and confirmed revenue
Ranking of sources by revenue, win rate and cost per paid sale
An AI agent that answers where revenue comes from with the same rule

Where to activate

Budget review

Monthly ranking of sources by confirmed revenue, win rate and cost per paid sale, with immature vintages flagged.

AI agent via MCP

Answers where revenue comes from with the source cascade and proof of payment already applied.

Quality alert

Notice when the share with no source or of wins without payment rises above the agreed level.

Ready to build?

The source cascade and the link to billing take work once. After that, the audit runs on its own and only the source list needs upkeep. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.

See detailed view

The problem

The deal's source is scattered: the CRM source field, the form UTM, the source typed by the rep and an import tag, often disagreeing. The CRM win is declared by the rep and does not always become a payment. Whoever counts leads by source and sums the value of CRM wins ends up with a list of winning channels that finance does not recognize.

How it is usually done, and the rule

Each row is a mistake that already happened in practice.

The common wayThe rule

Counting the deal in every source that appears in the fields.

One source per deal, by a declared cascade

Resolve the source in this order: a recorded manual correction, a click identifier or paid UTM (gclid, fbclid, paid utm_medium), the deal UTM, the UTM inherited from the lead or contact, the CRM source field, the source declared by the lead, and finally no source. The first one that matches wins. Store the method that resolved the source alongside it, to audit the quality of each row.

Treating OFFLINE or import as an acquisition channel.

Imports and integrations are not a channel

In HubSpot, hs_analytics_source = OFFLINE means the contact was created by import, integration, extension or manual entry (the detail is in hs_analytics_source_data_1). In RD Station, batch import conversions and integration events show up as conversions. Classify these as imported base or manual entry and look for the real source in the other cascade rules.

CPL with every CRM deal in the denominator.

Inbound and outbound in separate rows

Outbound prospecting (SDR, cold calls, WhatsApp blasts, bought lists) and partner referrals have different cost and cycle from inbound. Keep a source group (paid inbound, organic inbound, outbound, referral, partner, base) above the detailed source, and never divide media cost by outbound deals.

Summing the value of won CRM deals as revenue.

A sale counts when payment confirms it

Tag each deal in layers: CRM win, paid sale (there is a paid charge linked to it) and active or delivered sale, when that data exists. The source's revenue is the sum of the amount paid in billing, not the deal value typed by the rep. Show the layers side by side: the gap between won and paid by source is already a finding of the audit.

Counting any won status as a sale.

A win is the sales stage, in the sales pipeline

In many CRMs, the won status of a pre-sales pipeline means a qualified lead, not a sale. Define which pipelines and stages count as a sale and leave out recruiting, support, events and re-engagement pipelines. A deal that was lost and reopened counts as won on the date it was won.

Dividing wins closed in the month by leads created in the month.

Vintage and close date do not mix

To compare sources, count everything by vintage: the lead or deal creation month, and track how much of that vintage became a win and a payment to date. The close month view is for cash, not for source efficiency. Never add the two views together.

Comparing last month's vintage win rate with one from six months ago.

Recent vintages are still maturing

Sources with long cycles look worse in recent vintages because deals are still open. Show the vintage age and the share still open. A vintage with no sale yet appears as maturing, not as a minus 100% return.

Hiding or prorating deals with no source.

No source stays visible

A deal with no resolved source goes to the no source row and is never spread across the others. The share with no source is the audit's first indicator. Agree with the team whether unknown counts as digital; that decision is recorded and applies to every period.

Counting the test lead and the re-import as leads of the source.

Tests, duplicates and internal records go first

Integration test leads, internal accounts, duplicate deals from the same contact on the same day and old base loads distort the win rate of whatever source they land in. Flag and exclude them before aggregating, with a maintained, versioned list.

Layers of proof of a sale

Each deal climbs to the layer the data can prove.

Lead or deal createdVolumecreatedate

Moved to the qualified stageOpportunity

Won in the sales pipelineDeclared salestatus won

Paid charge linked to the dealPaid saleinvoice.status = paid

Active contract, delivered order or confirmed enrollmentProven sale

No link to billingUnconfirmed sale, investigate

Definitions

Source

result of the source cascade for each deal, only one per deal, with the method that resolved it

Source leads

leads or deals created in the month with that source, excluding tests, internal records and duplicates

CRM wins

deals of the vintage that reached the sales stage in the sales pipeline, up to the cutoff date

Paid sales

wins of the vintage with at least one paid charge linked in billing

Confirmed revenue

sum of the amount paid in billing linked to the source's deals, from the vintage up to the cutoff date

Win rate

paid sales of the vintage / leads of the vintage, by source

Confirmation rate

paid sales / CRM wins, by source and vintage

Cost per paid sale

media spend of the paid source in the vintage month / paid sales of that vintage; null for sources with no recorded cost

Pitfalls that already happened

Platform leads instead of CRM leads

CPL by channel used the leads reported by the Meta and Google APIs, which came in as zero or divergent, and the paid channel looked like it had no results.

Leads and sales come from the CRM; the platforms provide only cost.

Import became the biggest channel

Three quarters of the conversion events in a marketing automation account were batch loads, and import showed up as the channel generating the most leads.

Classify loads, integrations and manual entries as base, outside the acquisition channels.

Pre-sales win counted as a sale

The won status of the SDR pipeline, which means a qualified lead, was counted as a sale and inflated the win rate of inbound sources.

Declare the pipeline and stage that count as a sale.

The sum by source did not tie out

Deals with more than one source filled in were counted in each, and the sum of the sources exceeded the period total.

One source per deal by the cascade; check that the sum of the sources equals the total.

Lead Ads with no UTM

Leads from Meta's native forms do not pass through the website and reached the CRM with no UTM at all, falling into no source or organic.

Use the form identifier, the first visit URL parameters or the Lead Ads integration to tag the paid source.

Validated on real data. In Nekt's own HubSpot, two out of three customers have OFFLINE as their original source, and the detail shows they came from integration, import, extension or manual entry. Treating OFFLINE as a channel would make it the biggest revenue channel while saying nothing about acquisition.

Acceptance test

The case is done when every item passes.

Each deal has exactly one source and one resolution method.
The sum of leads, wins and revenue across all sources, including no source, equals the period total.
Total confirmed revenue for the period matches billing linked to deals, with a difference below 1%.
No deal from a pipeline outside the sales scope appears as won.
The share of deals with no source and from imported base appears in the report, by month.

Agent package

Paste into Claude, ChatGPT or any client with the Nekt MCP connected.

Full Markdown

Revenue source audit
- Each deal has a single source, resolved by the cascade: manual correction > click identifier or paid UTM > deal UTM > lead or contact UTM > CRM source field > declared source > no source.
- OFFLINE in HubSpot and batch loads in RD Station are not channels. They are imported base or manual entry.
- Source groups: paid inbound, organic inbound, outbound, referral, partner, base. Media cost only divides paid source deals.
- Declared sale = won in the sales pipeline. Paid sale = won with a paid charge in billing. Revenue = amount paid in billing, never the deal value in the CRM.
- Compare sources by vintage (creation month). Recent vintages are maturing; report the share still open.
- No source is its own row, never prorated.
- Tests, internal records and duplicates stay out of every count.
Client exceptions:
- <client>: pipelines that count as a sale: <list>. Pre-sales pipeline: <name>.
- <client>: unknown source <counts|does not count> as digital.
- <client>: key between deal and billing: <field>.

Build the "Revenue source audit" case in my Nekt workspace following the rules at https://academy.nekt.com/en/casos/auditoria-de-receita.md. 1) Find the source tables and confirm the columns the rules use. 2) Create a transformation at the stated grain applying every rule. 3) Run the acceptance test and show me the result of each item. 4) Create the case's context document in the workspace, leaving my clients' exceptions open for me to fill in.

Variations

No integrated billing

Use the CRM win as the top layer and state clearly that revenue is declared, not confirmed.

By product or unit

The same audit broken down by product line, course, development or branch.

Agency for the client

One audit per end client, with the source cascade adjusted to each one's CRM.