--- id: casos/atribuicao-de-receita area: RevOps usage: common effort: high grain: Customer x billing month, with the customer's fixed acquisition channel --- # Recurring revenue attribution How much recurring revenue each acquisition channel brought over time, linking the ad to the CRM and the CRM to billing. ## Executive summary **For:** Recurring revenue companies that want to know which channels bring customers who pay more and for longer. **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. **Why it matters:** The channel that brings the most customers in a month does not always bring the ones who stay. The first payment hides the difference between a customer who cancels in two months and one who pays for years. Without linking acquisition, CRM and billing, CAC is compared with estimated revenue and the budget goes to the fastest channel, not the most profitable one. **Business questions it answers** - 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 ## Detailed ### 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. ### Business rules 1. **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. (Avoid: Attributing each invoice to the last channel the contact touched.) 2. **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. (Avoid: Changing the customer's channel when they return through another campaign.) 3. **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. (Avoid: Matching utm_campaign to the campaign name as it arrived.) 4. **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. (Avoid: Linking by company name or by unnormalized email.) 5. **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. (Avoid: Using the won deal's amount as the channel's revenue.) 6. **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. (Avoid: Linking the charge to the customer only by the payer's tax ID.) 7. **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. (Avoid: Comparing total revenue of 2 year old customers with that of 2 month old customers.) 8. **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. (Avoid: Dividing spend by all new customers, from every channel.) 9. **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. (Avoid: Showing only linked customers and calling it revenue by channel.) ### The link chain Each link uses the most stable key available, in this order. - **Ad to lead** → Ad or campaign id in the UTM (`utm_content · utm_campaign · hsa_ad · hsa_cam`) - **Ad to lead** → Click identifier (`gclid · fbclid · ctwa_clid`) - **Lead to deal** → CRM association or normalized email (`deal.associations.contacts`) - **Deal to paying customer** → Customer or charge id stored in the CRM - **Deal to paying customer** → CNPJ or CPF as digits only (`lpad 14 · lpad 11`) - **Deal to paying customer** → Lower case email, no free domain for companies (`customer.email`) - **Customer to revenue** → Paid recurring charges, normalized to a month (`invoice.amount_paid`) - **No link found** → Untracked, with its revenue ### Definitions | Metric | Definition | |---|---| | 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 - **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. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: 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. How to avoid: 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 - [ ] 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. ### Context document ```text 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: - : key between deal and billing: . Payer different from the account holder linked by . - : channels with no cost treated as . - : manual channel corrections recorded 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.