Media platforms, sales, forms and campaign registry
The campaign code ties spend, lead and sale together
CAC, ROAS and sign-ups by campaign, course and launch
Dashboard by course, AI agent and launch spreadsheet
What it solves
- One total spend per campaign and course, adding up every platform, that matches each ads manager.
- CAC and ROAS calculated campaign by campaign, with the share of spend the number covers written next to it.
- Launches evaluated over the full campaign, so the capture month does not look like a hole and the cart month does not look like a miracle.
Business questions it answers
Ask your agent these questions once the case is live.
What you need
- Media platforms and the sales source connected to Nekt
- A campaign code in campaign names and a campaign registry with course and type
- A launches table with code, course, and opening and cart dates
What you get
- A media table by day, platform, account and campaign, with course and campaign type
- CAC and ROAS by campaign, month, course and launch, with coverage
- Sign-ups and survey responses by launch, ready for a spreadsheet
- An AI agent that answers media questions with the right rules
Where to activate
Spend, CAC with coverage, and monthly and full-campaign ROAS.
Traffic managers and coordinators ask by campaign and course, with the rules already applied.
Sign-ups and survey by temperature sent through reverse ETL during capture.
Several platforms and a naming convention that has to be followed. The campaign registry is what needs the most maintenance. The detailed view has the business rules, the pitfalls and the acceptance test. Or send everything straight to your agent with the Nekt MCP.
Spend is scattered across several accounts and platforms, and the account name does not say the course. The sale does not trace back to the ad, so whoever calculates CAC matches spend and sales by campaign text and divides things that do not correspond. In a launch, capture spend lands in one month and cart sales in the next, and monthly ROAS misleads in both directions.
How it is usually done, and the rule
Each row is a mistake that already happened in practice.
spend.Total spend = sum of spend across all platforms in the same period (for example Meta + Google + YouTube), and every metric also shows each platform's share. Each source only counts within its data range: outside it the value is null, not zero. A month in which a platform's collection was turned off has no information, and that is written in the answer.
Put a code in the name of every campaign (for example `[ID][CUR0826L]`: course prefix, month, year and funnel type) and carry the same code into the UTM and the sales offer. Normalize with a single expression in every table: uppercase and trimmed. Spend without a code and codes missing from the registry stay on their own rows, never added to a course.
The ad account name does not say the course: a launch account carries campaigns for several courses. The course of the spend comes from the campaign registry or the code prefix, testing long prefixes before short ones (the shorter prefix swallows the longer one). The campaign's course (used by targets and spend) may differ from the course of the product sold (used by revenue): this is by design, not an error, and is not reconciled.
Sum spend by month x campaign and sales by month x campaign separately, and only then join the two with a full outer join. That way campaigns with spend and no sales show up, as do sales without spend. CTR, CPC and CPM are recalculated from totals, never as an average of averages.
spend / traffic sales, without saying that only a few campaigns flag the source.CAC = the campaign's total spend / sales attributed to the same campaign's traffic. A traffic sale is one flagged by the sale's source (for example, a tracking code starting with `trafego`). Show how many campaigns with spend had any traffic sale and how much of the spend they represent. CAC by month, course or type adds up different campaigns: also look campaign by campaign. Spend / all of the campaign's sales is cost per sale, labeled as supporting, never as CAC.
In a launch, capture spend lands in one month and cart sales in the next. Besides monthly ROAS, deliver the full-campaign ROAS: all spend for the code / all revenue for the code, across all dates. A course whose spend is only from a launch in capture shows as in capture and leaves the consolidated view, instead of a ROAS near zero. A launch with no history does not enter efficiency comparisons.
Renewals and recurring charges have no media of their own and inherit the source of the first sale. For campaign and course ROAS, use revenue without renewals; for the overall monthly ROAS, if renewals are included, say so. Renewal sales flagged as traffic also leave the CAC.
The lead the platform counts (pixel event or form) is not the company's lead or MQL, which usually comes from marketing automation or the CRM. Platform CPL only applies to capture campaigns (launch, webinar, lead magnet). Application or direct-sale campaigns generate almost no platform leads and the CPL comes out absurd. Do not add Meta leads, Google conversions and TikTok results into a single conversions column.
Anyone who sells or advertises in more than one currency keeps fixed views: currency (BRL, EUR) x with and without tax. Conversion uses the Banco Central's closing PTAX rate for the day (selling rate); a day with no bulletin uses the last prior business day; a day with two closing bulletins uses the last one. The tax on Meta budget (for example 12.15%) only changes spend. Every query filters one view, otherwise the sum is multiplied by the number of views.
Do not copy one query per launch with a hard-coded code and date. Keep a launches table (code, course, opening, cart) and a single capture table with a launch column, read from the launch field, the domain, the page path or the UTM. A sign-up is the entry on the capture page; a survey response is the submission on the thank-you page, with profile (age, income, education) and temperature (hot, warm, cold). Count people per launch by normalized email or phone, with the first entry date.
Code suffix and funnel type
Example convention <course><MM><YY><type>. The suffix states the funnel format, not the strategy or the target.
Definitions
spend across all platforms by day, campaign and account, on the Brasília calendar, with each platform's share alongsidespend in the month / traffic sales of the same campaign in the monthspend of campaigns that have traffic sales / total spend for the monthspend / all non-renewal sales with the campaign code; not CACspend for the month, with the same slice and the same course rulespend across all dates for the codespend of capture campaigns / leads counted by the platform, always named this wayPitfalls that already happened
The media table normalized the campaign name to lowercase and the attribution table to uppercase. The join matched zero campaigns and nobody noticed for weeks.
A single documented normalization expression in every table.
Unregistered campaigns fell into the same group as registered ones without a type. In one month, this hid the spend of an entire launch and made the launch type look much smaller.
Separate no code, code missing from the registry and registered without a type; check coverage before concluding.
The application campaigns with the most budget had no sales flagged as traffic. The official CAC measured only a few evergreen funnels and none of the large campaigns.
Show coverage and cost per sale as supporting metrics, and fix the source flag on the offer.
The dashboard showed Meta spend a few percent below the database because it excluded boosted posts and content distribution.
Decide whether the official measure adds everything and offer the as-in-dashboard view separately.
Hundreds of campaigns appeared in lead UTMs with no spend on any platform: older than the API history, no-cost channels (email, organic, WhatsApp) or platforms not connected.
Flag the attribution status and calculate CPL and ROI only for campaigns with both ends.
In marketing automation with several accounts, the same qualification field has a different id in each account. Filtering by a list with the ids from every account brought back answers from the wrong fields.
Map account x field id in a reference table and filter by pair.
Acceptance test
The case is done when every item passes.
- For a closed month, each platform's spend by campaign matches its ads manager (difference below 1%), and months with no collection appear as null.
- Spend by course + no code + code missing from the registry equals total spend for the month.
- For a finished launch, the full-campaign ROAS equals all non-renewal revenue for the code divided by all spend for the code.
- Every reported CAC comes with spend coverage, and no cost-per-sale row appears under the name CAC.
- A launch's sign-ups count each person once, and sign-ups + people who only answered the survey match the launch's total distinct people.
Agent package
Paste into Claude, ChatGPT or any client with the Nekt MCP connected.
Paid media by course and launch - Total spend = sum of spend across <platforms> in the same period. Always show each platform's share. Outside a source's data range the value is null, not zero. - Key: campaign code in the name (pattern [ID][<code>]), normalized with UPPER(TRIM()) in every table. Spend without a code and codes missing from the registry stay on their own rows. - Course of spend: campaign registry; if missing, code prefix, from longest to shortest. The account name does not indicate the course. The campaign's course (targets and spend) may differ from the product's course (revenue): do not reconcile. - Aggregate spend and sales by month x campaign separately and join with a full outer join. CTR, CPC and CPM recalculated from totals. - CAC = campaign spend / campaign traffic sales. Traffic sale = <source rule>. Report coverage (percentage of spend in campaigns with traffic sales). Spend / all sales = cost per sale (supporting), never CAC. - Launch: deliver monthly ROAS and full-campaign ROAS (all dates). Course only in capture = in capture, left out of the consolidated view. - Campaign and course ROAS use revenue without renewals. - The platform lead is not the official lead or MQL. Platform CPL only for capture campaigns. Do not add conversions from different platforms. - Views: currency <BRL/EUR> x tax <yes/no>. Always filter one view. Default: <view>. Day's closing PTAX rate, last business day when there is no bulletin. - Launches: table <launches table> with code, course, opening and cart. Sign-up = capture page; survey = thank-you page with answers and temperature (q, m, f). Person = normalized email or phone, first entry date. - The current day is partial. State the last load of each platform. Client-specific exceptions: - <client>: account <account> stays out of spend until currency and scope are confirmed. - <client>: tax of <x>% on Meta budget only enters the with-tax view. - <client>: the dashboard excludes spend from <category>; the official measure adds everything.
Build the "Paid media by course and launch" case in my Nekt workspace following the rules at https://academy.nekt.com/en/industrias/educacao/midia-por-curso-lancamento.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
People who went through a call buy at a higher price: compare ticket with and without a call, matching schedule and buyer by email.
Operation in two countries with four views: BRL and EUR, with and without tax.
CPL, hand-raisers and ROAS by evergreen funnel, with a cohort of who entered in the period.