Recurring revenue attribution

Source

Media, CRM deals and contacts, billing charges

Rule

Ad to deal, deal to paying customer, channel fixed at acquisition

Outcome

Recurring revenue, LTV, CAC and payback by channel and vintage

Activation

Budget, board and AI agent

What it solves

Each paying customer has one acquisition channel, linked from the ad to the deal and from the deal to billing.
Each channel's revenue is what billing charged month after month, not the value estimated in the CRM.
CAC, LTV and payback by channel compare customers of the same age.

Business questions it answers

Ask your agent these questions once the case is live.

How much recurring revenue did each channel bring in the last 12 months?

What is the 6 and 12 month LTV of each channel's customers?

How many months does each channel's customer take to pay back its CAC?

Which channel brings customers with the highest ticket and which brings those who cancel earliest?

How much of the expansion revenue came from customers acquired through paid media?

What you need

Media, CRM and billing connected to Nekt
A key between the won deal and the billing customer (email, CPF or CNPJ, or a billing id stored in the CRM)
The channel cascade agreed between marketing and sales

What you get

A customer x month table with paid revenue and the customer's acquisition channel
LTV curves by channel and vintage, comparing customers of the same age
CAC and payback by paid channel
An AI agent that answers about channel return with the right rule

Where to activate

Budget planning

Revenue, LTV by age, CAC and payback by channel, to decide next quarter's budget.

AI agent via MCP

Answers about channel return with the customer's fixed channel and the link rate always stated.

Send back to the platforms

Send each customer's paid amount and vintage back to Meta and Google as an offline conversion, to optimize for revenue.

Ready to build?

There are three sources and two identity bridges, and the link rate defines what you can claim. Start by measuring how many paying customers already link to a deal with a channel. 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 channel lives in the CRM, the money lives in billing and the cost lives in the media platforms, and none of the three has the others' key. The deal value in the CRM is the rep's estimate, and a small contract at close can become much more revenue through expansion and renewal, or nothing if the customer cancels early. Whoever joins only CRM and media measures the channel by the close, not by what the customer paid.

How it is usually done, and the rule

Each row is a mistake that already happened in practice.

The common wayThe rule

Attributing each invoice to the last channel the contact touched.

The channel belongs to the customer and is fixed at acquisition

The customer's acquisition channel is the channel of the first won deal that became a payment, resolved by the channel cascade (click identifier or paid UTM, deal UTM, the contact's first touch UTM, CRM source field). It does not change afterwards. All of the customer's future revenue, including expansion and renewal, stays with that channel.

Changing the customer's channel when they return through another campaign.

First touch for acquisition, last touch for reactivation

A new customer uses first touch. A customer who cancelled and came back has the reactivation attributed to the last touch before returning, in a separate column, without changing the acquisition channel. Keep both and state which model each view uses. None of this is causality: it is observable evidence of where the customer came from.

Matching utm_campaign to the campaign name as it arrived.

The bridge from ad to deal

Link the lead to the ad by the most stable identifier available: ad or campaign id in the UTM, first visit URL parameters (such as Google's hsa_cam and hsa_ad), gclid or fbclid, or ctwa_clid on conversations started from a WhatsApp ad. Test each id against the platform's real ids. A campaign name only matches after normalization (upper case, no double spaces, + turned into a space), and ambiguous pairs stay separate, never forced.

Linking by company name or by unnormalized email.

The bridge from deal to paying customer

Link the won deal to the billing customer by an explicit key: customer or charge id stored in the CRM, CNPJ (company tax ID) or CPF (individual tax ID) as digits only with leading zeros (14 and 11 positions), or email in lower case with no spaces. A free email domain never becomes a company key. Store the method behind each link.

Using the won deal's amount as the channel's revenue.

Revenue is what billing charged

Revenue per customer and month is the recurring amount paid in billing, normalized to a month (annual plan divided by 12) and without onboarding, setup or one-off charges. The CRM deal value stays only as a reference. If the customer has more than one billing account with the same key, add the accounts together.

Linking the charge to the customer only by the payer's tax ID.

The payer is not always the customer

A parent paying for a student, a holding paying for a branch or a partner's card: the charge carries another CPF or email. When the source has the contract or enrollment id, link by it, never by the person. Without that id, link by tax ID, date and amount, and flag it as an inferred link.

Comparing total revenue of 2 year old customers with that of 2 month old customers.

LTV by age and by vintage

Compare channels by cumulative revenue per customer at 3, 6 and 12 months of life, within the same acquisition vintage. A customer who has not reached that age stays out of that point of the curve, and a cell with no eligible customer is null, never zero. Cumulative LTV without age favors the oldest channel.

Dividing spend by all new customers, from every channel.

CAC by channel and payback

Paid channel CAC = channel spend in the month / new paying customers acquired through the channel in the month. Payback = the first month in which the cumulative revenue of the channel's vintage covers that vintage's spend. A channel with no recorded cost (organic, referral, campaigns that exist only in the UTM) has a null CAC, not zero.

Showing only linked customers and calling it revenue by channel.

Untracked is a row, not a hole

A paying customer with no linked deal or no resolved channel goes in as untracked, with all of its revenue. The link rate (revenue with a channel / total revenue) is shown in every report and defines how far the conclusion holds.

The link chain

Each link uses the most stable key available, in this order.

Ad to leadAd or campaign id in the UTMutm_content · utm_campaign · hsa_ad · hsa_cam

Ad to leadClick identifiergclid · fbclid · ctwa_clid

Lead to dealCRM association or normalized emaildeal.associations.contacts

Deal to paying customerCustomer or charge id stored in the CRM

Deal to paying customerCNPJ or CPF as digits onlylpad 14 · lpad 11

Deal to paying customerLower case email, no free domain for companiescustomer.email

Customer to revenuePaid recurring charges, normalized to a monthinvoice.amount_paid

No link foundUntracked, with its revenue

Definitions

Acquisition channel

channel of the first won deal that became a payment, by the channel cascade; one per customer, fixed

Acquisition vintage

month of the customer's first recurring payment

Channel recurring revenue

sum of the recurring amount paid and normalized to a month by the channel's customers, by accrual month

LTV at N months

cumulative revenue per customer in the first N months of life, only for vintage customers who have already reached N months

Channel CAC

paid channel spend in the month / new paying customers of the channel whose vintage is that month

Payback

number of months until the cumulative revenue of the channel's vintage equals the vintage's spend

Link rate

recurring revenue with a resolved channel / total recurring revenue, in the period

Pitfalls that already happened

CRM value instead of billing

Revenue by channel was the sum of won deal values, self-reported by the rep at close and never reconciled with billing. Channels with a lot of expansion looked small.

Use the amount paid in billing and keep the CRM value only as a reference.

The CRM Campaign object is not a media campaign

The CRM campaign object grouped offers, such as a free trial, and thousands of leads sat in a campaign that did not exist on any ad platform.

Link to media by UTM or ad id, not by the CRM campaign object.

Campaigns with no cost

Hundreds of campaigns existed only in lead UTMs, with no spend recorded on any platform, and entered the ranking with zero CAC.

Null CAC when there is no cost, plus a list of campaigns with no cost for review.

Different email at capture and at purchase

The lead signed up with one email and paid with another, and the sale appeared outside any campaign. Paid channel return was understated.

Use more than one key (tax ID, phone, id stored in the CRM) and measure the link rate.

Rewritten cycle date

When a charge failed and was later paid, billing pushed the cycle start date forward, and old customers entered a new vintage with another channel.

Anchor the vintage on the customer's first confirmed payment.

Validated on real data. In Nekt's own HubSpot, about 3 in 10 customers have an original source different from their latest source. Switching the model from first to last touch changes the channel of almost a third of the base, which is why the acquisition channel must be fixed and declared.

Acceptance test

The case is done when every item passes.

Each paying customer has exactly one acquisition channel, or untracked.
The sum of recurring revenue across all channels, including untracked, equals the recurring revenue paid in billing for the period.
A customer's acquisition channel does not change between two runs, unless a recorded manual correction says so.
No point of the LTV curve includes a customer who has not yet reached that age.
The link rate appears in every revenue by channel report.

Agent package

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

Full Markdown

Recurring revenue attribution
- Each paying customer has one acquisition channel, the one from the first won deal that became a payment. It is fixed and takes all of the customer's future revenue, including expansion and renewal.
- Channel cascade: click identifier or paid UTM > deal UTM > contact's first touch UTM > CRM source field > untracked.
- Reactivation uses the last touch before returning, in a separate column. It does not change the acquisition channel.
- Revenue = recurring amount paid in billing, normalized to a month, without onboarding or one-off charges. The CRM deal value is not revenue.
- LTV compares customers at the same age (3, 6, 12 months) within the vintage. A cell with no eligible customer is null.
- CAC = channel spend in the month / new paying customers of the channel in the month. A channel with no cost has a null CAC.
- Always report the link rate (revenue with a channel / total revenue). Untracked is a row with its revenue.
- Attribution is evidence of where the customer came from, not causality.
Client exceptions:
- <client>: key between deal and billing: <field>. Payer different from the account holder linked by <contract|enrollment>.
- <client>: channels with no cost treated as <organic|referral|partner>.
- <client>: manual channel corrections recorded in <corrections table>.

Build the "Recurring revenue attribution" case in my Nekt workspace following the rules at https://academy.nekt.com/en/casos/atribuicao-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

Multi-touch

Keep every touch of the contact up to the close and compare first touch, last touch and linear without changing the official acquisition channel.

One-off sale with repurchase

In e-commerce and digital products, swap the monthly fee for paid orders and use repurchase and renewal instead of expansion.

Partner channel by coupon

When a partner sells with its own coupon or link, the coupon in billing becomes the channel, next to the CRM source.