Paid media through to revenue

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.

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.

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.

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.

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.

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.

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.

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).

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.

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.