--- id: saas/midia-paga-ate-receita industry: SaaS and technology usage: widely used effort: medium grain: Platform x campaign x day for cost; campaign x month for efficiency --- # Paid media through to revenue What each campaign cost per lead, per trial, per new customer and per real of MRR, tying the ad to the CRM and the CRM to billing. ## Executive summary **For:** SaaS companies that invest in paid media to generate trials, demos and customers. **What it solves** - CPL and CAC use the right conversion and only paid leads in the denominator. - Every real of new MRR has a channel and a campaign, or shows up as untracked. - Upgrades from existing customers stop inflating media efficiency. **Why it matters:** In SaaS, a cheap lead can become the trial that never pays, and CPL alone rewards the wrong campaign. Tying spend to MRR in billing shows which campaigns bring customers who stay, and how many months media takes to pay back. It is the basis for forecasting by channel and for the budget conversation with leadership. **Business questions it answers** - What was the CAC by channel and by campaign in September? - How much did each self-service trial cost last month? - How much new MRR did paid media bring this quarter, and how many months does it take to pay back? - How much did we spend on Google and on Meta, and what is the CPL of each? - How many leads came from ads with no tracked campaign? **What you need** - Ad platforms, CRM and billing connected to Nekt - An explicit key between the won deal and the billing customer - The UTM taxonomy and the conversion event for each motion (self-service and assisted sales) **What you get** - A platform x campaign x day table with additive measures only - An efficiency funnel by campaign and month with CPL, CAC, payback and ROAS - An acquisition dashboard with paid and organic kept apart - An AI agent that answers about media with the right rule ## Detailed ### The problem The campaign name in Meta and Google does not match the utm_campaign that reaches the CRM, and Google's conversions column adds page views to submitted forms. Whoever divides spend by those conversions, or by every lead in the CRM, reports a CPL that is far too low. And CAC mixes upgrades from old customers with new customers, so nobody can say which campaign brought MRR. ### Business rules 1. **The campaign key is the normalized utm_campaign.** Standardize the name on both sides before joining: uppercase, trimmed, '+' replaced by a space and repeated spaces collapsed. When the UTM carries the campaign id, join by id, only on 1 to 1 pairs. Ambiguous pairs (one id with several UTMs, or the reverse) stay on a separate line, never prorated. The best path is a single campaign naming taxonomy, with a closed list of utm_medium values, used by ads, landing pages and the CRM. (Avoid: Joining the ad's campaign_name to the lead's raw utm_campaign.) 2. **A Google conversion is the conversion action, not the total column.** metrics_conversions in the campaign performance table adds up every conversion action configured in the account, including micro conversions such as page views and button clicks. In a real account, that total was about six times the number of Meta leads in the same period. Read the per-action conversions table (campaign_conversion_actions) and keep only the action that represents the result: lead form, trial signup or booked demo. (Avoid: CPL = cost / metrics_conversions.) 3. **In SaaS, the top-of-funnel conversion is a trial, signup or demo.** Declare one canonical event per motion. In self-service, the trial signup (a GA4 custom event or the signup in the product database). In assisted sales, the lead or the booked demo in the CRM. Keep self-service (PLG) and assisted sales apart in every metric, because they are different populations with different costs. A product user who becomes a CRM contact is not a media lead. (Avoid: Adding product signups and CRM leads into the same funnel.) 4. **Paid and organic always apart.** A lead is paid when it has an ad UTM (utm_medium cpc, paid_social or display), a click identifier (gclid, fbclid) or a paid flag in a CRM field. Show paid in two parts, with UTM and without UTM, because only the part with UTM joins to a campaign. A lead with source Google or Meta, no UTM and no paid flag is organic by rule and is highlighted, since it may be an untracked ad. CPL, CAC and ROAS use only paid leads in the denominator. (Avoid: Dividing spend by every lead in the month.) 5. **Channel by cascade, with the method recorded.** Resolve each contact's channel in a fixed order: UTM or click identifier; then the conversion identifier (a product signup becomes PLG, an event form becomes Event); then the session referrer (a search engine becomes Organic, a social network becomes Organic social, chatgpt, perplexity and gemini become AI, your own domain becomes Direct); finally, Untracked. Store the channel and the method that resolved it. In RD Station, UTM and referrer live in payload.traffic_source, base64 encoded. (Avoid: Classifying the channel by UTM only and dumping the rest into Direct.) 6. **CAC counts only new customers.** CAC = channel spend in the month / new paying customers attributed to the channel, by the official sale date. Upgrades, cross-sell and renewals from existing customers go only into expansion MRR, never into the CAC denominator or into the count of sales, SQLs and demos. In SaaS sold per unit (seats, properties, stores), also publish the cost per unit sold. (Avoid: Counting upgrades as sales and lowering CAC.) 7. **Media revenue comes from billing.** ROAS and payback use the customer's paid MRR in billing, linked to the won deal by an explicit key (billing customer id on the deal, or a 14-digit CNPJ (company tax ID)). Do not use the deal value or the revenue the ad platform reports. Payback in months = CAC / average new MRR per customer of the channel. A paying customer with no linked deal goes in as untracked, with their revenue. (Avoid: ROAS from Meta's purchase_roas or the deal amount.) 8. **Only add what is additive.** Store spend, impressions, clicks and conversions at the platform x campaign x day grain. CTR, CPC, CPM, CPL and CAC are recalculated from the sums at every aggregation, and so is the total row, never as a sum or average of ratios. Meta arrives per ad and must be aggregated to the campaign. Google cost comes in micros (cost_micros / 1,000,000). (Avoid: Averaging daily CPC or adding up each channel's CPL.) 9. **Incomplete months stay out of comparisons.** A month or channel with partial data (for example, fewer than 25 days of synced spend) is flagged and kept out of comparisons. With no attribution, revenue and ROAS are null, never zero. Meta restates conversions for about 28 days: reprocess a rolling window (for example, 90 days) by campaign and day, without duplicating. (Avoid: Comparing a month with half of its days loaded.) 10. **Account and currency checked.** Accounts by country or region are identified by account id, not by a typed label, because swapped labels have already mixed countries. Each account carries its source currency and is converted to the reporting currency at a declared rate before adding up. New platforms, such as ads inside AI assistants, often bill in dollars. (Avoid: Adding USD and BRL spend in the same column.) ### How a contact's channel is resolved In order: the first rule that applies sets the channel, and the method is stored. - **Ad UTM, gclid or fbclid** → Paid media, with campaign (`utm_medium in (cpc, paid_social, display)`) - **Flagged as paid in the CRM, no UTM** → Paid media, no campaign - **Conversion is a product signup** → PLG / self-service - **Event form or event UTM** → Event - **Search engine referrer** → Organic - **Social network referrer** → Organic social - **chatgpt, perplexity or gemini referrer** → AI - **Own domain or session without referrer** → Direct - **None of the above** → Untracked ### Definitions | Metric | Definition | |---|---| | Spend | sum of spend (Meta) and cost_micros / 1,000,000 (Google) by campaign and day, in the reporting currency | | Paid lead | lead created in the period with an ad UTM, a click identifier or a paid flag in the CRM | | CPL | spend / paid leads in the period, by campaign or channel | | Cost per trial | spend on self-service campaigns / trial signups of paid origin in the period | | CAC | channel spend in the month / new paying customers attributed to the channel with a sale in the month, no upgrades | | Media new MRR | MRR of the first paid month, read from billing, of new customers attributed to paid channels | | Payback | CAC / average new MRR per customer of the channel, in months | | ROAS | revenue paid in billing by attributed customers, over the declared window, / spend; null without attribution | | Tracking rate | paid leads with a resolved campaign / paid leads, by month | ### Pitfalls - **Conversions six times higher.** Comparing Google's metrics_conversions with Meta leads lumped page views and clicks together with submitted forms, and Google looked six times more efficient. How to avoid: Use the per-action conversions table and only the result action. - **A CRM campaign is not a media campaign.** In Salesforce, the Campaign object represented the offer (for example, a free trial of a plan) and grouped thousands of leads from different channels. How to avoid: Join media by the lead's utm_campaign; treat the CRM Campaign as an offer dimension. - **Upgrades lowered CAC.** Upgrade sales were counted as sales and CAC dropped without a single extra new customer. How to avoid: Exclude upgrades from sales, SQLs and demos; they go only into expansion MRR. - **Product inferred from the channel.** An old rule said every Google lead belonged to one specific product, and CAC by product was wrong for months. How to avoid: Product by cascade: declared in the CRM, then the form, then the token in the campaign name. - **Ad leads without UTM.** Instant forms and click-to-WhatsApp ads reach the CRM without a UTM and fell into organic. How to avoid: Link by ad or form id, or by normalized phone, deduplicating by phone and lead date. - **A new account left out.** Each ad account is a source. An account opened mid-month stayed out of the report until someone noticed the gap. How to avoid: Keep the list of expected accounts by id and alert when spend shows up in an unmapped account. Validated on real data: In Nekt's own Google Ads account, about 94% of what the platform counts as conversions are page views, and lead form submissions are just over 6%. A CPL calculated on metrics_conversions would come out about 15 times lower than the real one. ### Acceptance test - [ ] Spend by platform and month matches each platform's ads manager (difference below 1%). - [ ] Paid + organic + other origins = total CRM leads in the period, with no lead in two categories. - [ ] No upgrade sale enters the CAC denominator, and media new MRR + new MRR from other channels = the month's new MRR in the movements table. - [ ] The total row CPL is total spend / total paid leads, not the sum or average of CPLs. - [ ] Every month carries the tracking rate, and months with fewer than 25 days of data are flagged. ### Context document ```text Paid media through to revenue - Campaign key: normalized utm_campaign (uppercase, trimmed, '+' becomes a space). Join by campaign id only on 1 to 1 pairs; ambiguous pairs stay separate. - Google: the result comes from the per-action conversions table (campaign_conversion_actions), only the result action. Never use total metrics_conversions. - Canonical conversion: self-service = trial signup; assisted sales = lead or demo in the CRM. PLG and B2B always apart. - Paid = ad UTM (cpc, paid_social, display), gclid or fbclid, or a paid flag in the CRM. Google or Meta source with no UTM and no flag = organic by rule, always highlighted. - CPL, CAC and ROAS use only paid leads in the denominator. - CAC = channel spend in the month / new paying customers of the channel in the month. Upgrades, cross-sell and renewals are excluded. - Revenue and ROAS come from paid MRR in billing, linked to the won deal by an explicit key. Never use the deal amount or the ad platform's revenue. - Ratios (CTR, CPC, CPM, CPL, CAC) are recalculated from sums. Never add up or average ratios. - A month with fewer than 25 days of data is partial. Without attribution, revenue and ROAS are null, not zero. - Reporting currency: , rate: . Client exceptions: - : the canonical self-service event is . - : account belongs to and bills in . ``` ### Variations - **Multi-touch attribution:** One row per contact conversion (first, middle and last) to compare first touch, last touch and linear models. - **Click-to-WhatsApp ads:** Ad, conversation and lead linked by the ad click identifier or by phone, with ad cost per conversation. - **In-person events:** The event's utm_campaign is the key; the event cost goes in as spend and the event CAC is compared with media.