--- id: agencias/plano-de-midia-realizado industry: Marketing agencies usage: common effort: medium grain: Client x channel x plan line x day, closed by month --- # Media plan vs. actuals The plan the client approved compared with actual spend every day, by channel and line, on the same tax basis, with how much is left and where pacing is off. ## Executive summary **For:** Agencies that execute an approved media plan and answer to the client for how the budget is used. **What it solves** - Planned and actual sit on the same basis, with or without tax, on every channel. - Each plan line matches real campaigns and ad sets, and what does not match shows up. - The team knows every day how much to spend per day to close the month at the cap. **Why it matters:** Misallocated budget is the most expensive complaint: overspending breaks the agreement, underspending leaves results on the table. The plan lives in a spreadsheet, with versions, swapped labels and tax in a column only for Meta. Without a reading rule, each analyst reads a different number and the client finds the gap at month end. **Business questions it answers** - Is client X on pace with the October budget? - How much is left to spend per channel until month end? - How much do we need to spend per day to close at the cap? - Which plan line has no campaign running? - Which campaign is spending without being in the plan? **What you need** - Each client's approved plan in a spreadsheet or document with line, channel, period and budget - The portfolio's media table already unified - Each client's tax or markup factor and which plan column is the comparable one **What you get** - A daily planned vs. actual table by plan line - Pace, balance and required daily spend by channel and line - Alerts for lines with no delivery, spend outside the plan and pace outside tolerance - An AI agent that answers about budget using the current plan version ## Detailed ### The problem The plan arrives as a spreadsheet, with several versions, numbers as text in Brazilian format and the with-tax column filled only for Meta, sometimes with the label swapped. The platform reports net spend, and the plan may be gross. Campaigns are renamed, paused and duplicated mid-month, and part of the plan is media that has no connector at all. ### Business rules 1. **Only the current plan version.** Use the month's approved version, the tab or document marked as current. Old versions stay as history, flagged, for month-over-month comparison, and never enter the month's planned amount. (Avoid: Summing every tab in the spreadsheet.) 2. **Same tax basis on both sides.** The platform reports net spend. If the plan is gross and the client pays a markup on Meta spend (12.15% and 13.83% are real cases), convert planned to net (gross x 0.8785, or the factor in the client's spreadsheet) or actual to gross (net x 1.1215), and state which. Google and other channels usually have no factor. (Avoid: Comparing a gross plan with net spend.) 3. **Use the value, not the label.** In real spreadsheets, the column labeled with tax held the net value and the one without tax held the gross. Net is the smaller of the two and is what the platform reports. Check against a few days of actual spend before publishing the reading. (Avoid: Trusting the column name.) 4. **Spreadsheet text becomes numbers.** Values arrive as text in Brazilian format: remove the thousands dot and swap the comma for a dot before converting, or 1.456 becomes 1. Dates may come as text, a spelled-out month or a serial number; convert them and store each line's start and end. (Avoid: Converting the text straight to a number.) 5. **Each plan line has a key.** Declare how each line matches media: ad set on Meta, ad group on Google, campaign on LinkedIn, or a pattern in the campaign name (region, product, funnel stage). Sum every campaign and ad set that matches the line, active, paused or renamed: that is what matches the This month column in Ads Manager. A published line with no matched campaign is an alert; a campaign spending with no line is off plan. (Avoid: Matching the line to a single campaign ID.) 6. **Pace by each line's calendar.** Planned to date = line budget x line days elapsed / line total days, in the client's time zone, with yesterday as the last full day. A line with a partial period (a fortnight, two weeks) has its own calendar. Required daily spend = (budget - actual) / line days remaining. (Avoid: Dividing the month's budget by 30 for every line.) 7. **Media with no connector stays out of the comparison.** Programmatic, Pinterest, offline media or any channel with no connector shows up in the plan with a no connector status and stays out of the comparable total. It never shows up as a stopped campaign. (Avoid: Showing programmatic with zero spend.) 8. **Reallocated budget is a new version.** When the team moves budget between networks mid-month, the plan gets a new version with a cutoff date. The pace alert reminds the team to update the spreadsheet when one network's actual rises and another's falls by the same proportion. (Avoid: An overspend alert on a network that received budget from another.) 9. **Tolerance and preparation per client.** Pace within the client's tolerance is normal. Outside it, the alert states channel, line, pace, balance and required daily spend. Do not alert pace in a line's first days; a line that should have started and is not yet published gets a preparation alert. (Avoid: A pace alert on the second day of the month.) ### Plan alerts One row per alert per day, crossing the current plan with media. - **PREPARACAO** → Line should have started and is not published - **SEM_VINCULO** → Published line with no matching campaign - **SEM_ENTREGA** → Matched line with no spend in recent days - **RITMO_VERBA** → Actual outside tolerance of planned to date - **FORA_PERIODO** → Spend before the line starts or after it ends - **FORA_PLANO** → Campaign spending with no line in the plan - **KPI_ACIMA_META** → Line KPI cost above the plan target - **SEM_CONECTOR** → Plan channel with no source in Nekt, out of the comparable total ### Definitions | Metric | Definition | |---|---| | Monthly planned | sum of the budgets of the current version's lines, on the declared tax basis | | Actual | sum of spend of the campaigns matched to the line, from the line's first day through yesterday, in the client's time zone | | Planned to date | line budget x line days elapsed / line total days | | Pace | actual / planned to date | | Balance | line budget - actual | | Required daily spend | balance / line days remaining | | Comparable total | planned and actual only for channels with a connector in Nekt | | Net spend | what the platform reports; gross = net x the client's markup factor | ### Pitfalls - **Swapped labels.** In one client's spreadsheet, the net value sat in the column labeled with tax and the gross in the one without. Meta pacing came out wrong by the tax factor. How to avoid: Use the value: net is the smaller and matches spend. - **A thousand became one.** Budgets in text like 1.456 became 1 because the thousands dot was read as a decimal. How to avoid: Remove the thousands dot and swap the comma before converting. - **Actual below Ads Manager.** Summing only active campaigns left out the ones paused and renamed mid-month, and actual came in below the This month column. How to avoid: Sum everything that matches the line's key. - **Programmatic as a stopped campaign.** Lines for media with no connector showed zero spend and fired a no delivery alert every day. How to avoid: No connector status and a separate comparable total. - **Inherited dates.** The date columns in the month's plan still carried the previous month's period, inherited from the copied version. How to avoid: The document's reference period prevails; check dates before publishing. ### Acceptance test - [ ] For each client and month, planned equals the sum of the current version's lines on the declared basis. - [ ] Each line's actual matches the This month column in Ads Manager for the matched campaigns (difference below 1%). - [ ] Every campaign with spend in the month is matched to a line or shows up as off plan. - [ ] Lines for channels with no connector do not enter the comparable total. - [ ] Actual + balance = budget, on every line. ### Context document ```text Media plan vs. actuals - Planned comes only from the current version of the month's plan. Old versions are history. - The platform reports spend net of tax. Compare on the same basis: say whether planned was converted to net or actual to gross, and with which factor. - In the spreadsheet, use the value, not the label: net is the smaller of the two values. - Line actual = sum of every campaign and ad set that matches the line's key, including paused and renamed ones. - Pace = actual / (budget x days elapsed / line total days). Required daily spend = balance / days remaining. - Reference day = yesterday, in the client's time zone. - Channels with no connector in Nekt show as no connector and stay out of the comparable total. Per-client exceptions: - : Meta with a % markup; planned converted to net with factor . - : regions separated by the term in the campaign name, within the same account. - : pace tolerance of %; new budget starting . ``` ### Variations - **Plan in the context:** Clients without a structured spreadsheet keep the approved plan in a monthly context document, with a current or history status. - **Plan funnel:** Planned and actual by top, middle and bottom of funnel, with each line's KPI. - **By region or unit:** An account with several regions is split by campaign name, and each region has its own plan.