--- id: educacao/midia-por-curso-lancamento industry: Education usage: widely used effort: medium grain: Day x platform x account x campaign; sign-up per person per launch --- # Paid media by course and launch What each ad-driven sale cost and how much each R$ 1 brought back, by campaign, course and launch, with the number's coverage stated. ## Executive summary **For:** Schools, colleges and digital product creators that invest across several platforms and sell through launches and evergreen funnels. **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. **Why it matters:** In education sold online, media is the largest variable cost and budget decisions are weekly. A monthly ROAS on a launch leads to cutting the capture that was going to drive the cart. A CAC that divides one campaign's spend by another campaign's sales leads to scaling the wrong course. **Business questions it answers** - What is the CAC of each campaign for my course this month and last month? - What is the ROAS of the last launch, adding capture and cart? - How much did we invest across all platforms by course this month? - Which campaigns had spend and no sales attributed to traffic? - How many sign-ups and survey responses has the launch had so far, by temperature? - What is the income profile of the hot leads in the current launch? **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 ## Detailed ### The problem 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. ### Business rules 1. **Total spend is the stated sum of the platforms.** 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. (Avoid: Treating months with no Google collection as zero spend.) 2. **The campaign code ties spend, lead and sale together.** 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. (Avoid: Matching media and sales by the free-text campaign name.) 3. **Course comes from the campaign, not the account.** 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. (Avoid: Assigning the course by the ad account name.) 4. **Aggregate each side before joining.** 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. (Avoid: Joining the daily ads table with the sales table row by row.) 5. **CAC with coverage written next to it.** 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. (Avoid: Monthly CAC = total spend / traffic sales, without saying that only a few campaigns flag the source.) 6. **A launch is evaluated over the full campaign.** 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. (Avoid: Cutting budget because the capture month had a ROAS of 0.6.) 7. **Renewals stay out of media ROAS.** 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. (Avoid: Course ROAS inflated by renewals from people who bought the year before.) 8. **The platform lead is not the official lead.** 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. (Avoid: Calling the pixel lead an MQL and calculating CPL for an application campaign.) 9. **Currency and tax in fixed views.** 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. (Avoid: Summing the table without filtering currency and tax.) 10. **A launch is a row in a table; sign-up and survey are different events.** 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. (Avoid: Twenty-five identical transformations, one per launch, with the opening date written in the code.) ### Code suffix and funnel type Example convention . The suffix states the funnel format, not the strategy or the target. - **L** → Launch (`L2 = second wave`) - **AP** → Application (evergreen) (`AP`) - **SE** → Strategy session (`SE`) - **W** → Webinar (`W`) - **VSL** → VSL funnel (`VSL`) - **Q** → Quiz (`Q`) - **V** → Direct sale (`V`) - **FTV** → Sales team funnel (`FTV`) - **REC** → Recovery (`REC`) ### Definitions | Metric | Definition | |---|---| | Total spend | sum of spend across all platforms by day, campaign and account, on the Brasília calendar, with each platform's share alongside | | Traffic sale | approved sale whose flagged source indicates paid traffic (e.g. tracking code starting with trafego), counted by distinct transaction code | | Campaign CAC | the campaign's total spend in the month / traffic sales of the same campaign in the month | | CAC coverage | spend of campaigns that have traffic sales / total spend for the month | | Campaign cost per sale (supporting) | the campaign's spend / all non-renewal sales with the campaign code; not CAC | | Monthly ROAS | revenue for the month / total spend for the month, with the same slice and the same course rule | | Full-campaign ROAS | non-renewal revenue across all dates for the code / spend across all dates for the code | | Platform CPL | spend of capture campaigns / leads counted by the platform, always named this way | | Launch sign-ups | distinct people (normalized email or phone) with an entry on the launch capture page since opening | ### Pitfalls - **Normalization that matched zero.** 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. How to avoid: A single documented normalization expression in every table. - **Other hiding a missing registry entry.** 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. How to avoid: Separate no code, code missing from the registry and registered without a type; check coverage before concluding. - **Empty CAC on the largest campaigns.** 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. How to avoid: Show coverage and cost per sale as supporting metrics, and fix the source flag on the offer. - **Dashboard below the database.** The dashboard showed Meta spend a few percent below the database because it excluded boosted posts and content distribution. How to avoid: Decide whether the official measure adds everything and offer the as-in-dashboard view separately. - **Campaigns that only exist in the UTM.** 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. How to avoid: Flag the attribution status and calculate CPL and ROI only for campaigns with both ends. - **Automation field with a per-account id.** 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. How to avoid: Map account x field id in a reference table and filter by pair. ### Acceptance test - [ ] 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. ### Context document ```text Paid media by course and launch - Total spend = sum of spend across 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][]), 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 = . 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 x tax . Always filter one view. Default: . Day's closing PTAX rate, last business day when there is no bulletin. - 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: - : account stays out of spend until currency and scope are confirmed. - : tax of % on Meta budget only enters the with-tax view. - : the dashboard excludes spend from ; the official measure adds everything. ``` ### Variations - **Sales via call:** People who went through a call buy at a higher price: compare ticket with and without a call, matching schedule and buyer by email. - **Multi-currency:** Operation in two countries with four views: BRL and EUR, with and without tax. - **Evergreen funnels:** CPL, hand-raisers and ROAS by evergreen funnel, with a cohort of who entered in the period.