Tabellab

Campaign, Ad Set, Ad: Reading the Account Structure and Rolling Ad Results Up with SUMIFS

Lesson 1 of 20Lab

All lessons
  1. Campaign, Ad Set, Ad: Reading the Account Structure and Rolling Ad Results Up with SUMIFSLabFree
  2. The Delivery Chain: CPM, Link CTR and Conversion Rate and How They Multiply into the Cost per PurchaseLab Locked
  3. Diagnosing a Weak Ad Set: Account Rates from Totals, CPM, CTR and CVR Indexes and an IFS DiagnosisLab Locked
  4. Audiences: Reach, Frequency, Reach % of the Audience and the Cost per 1,000 People ReachedLab Locked
  5. Frequency and Creative Fatigue: Weekly Frequency, CTR Change Against Week 1 and a Refresh RuleLab Locked
  6. Audience Overlap: Shared People, Overlap % of the Smaller Audience, Unique Reach and When to MergeLab Locked
  7. The Learning Phase: Weekly Optimisation Events, Days Since the Last Edit and the Budget an Ad Set NeedsLab Locked
  8. Budgets: Campaign Budget (CBO) vs Ad Set Budgets, Budget Shares and the Blended Cost per PurchaseLab Locked
  9. Placement Breakdowns: Facebook vs Instagram, Feed vs Stories vs Reels, Shares of Spend and Cost per PurchaseLab Locked
  10. Creative Testing: Hook Rate, Hold Rate, Cost per Purchase and Ranking Video Ads with RANK.EQLab Locked
  11. Reporting and Attribution: 7-Day Click vs 1-Day View, View-Through Share, ROAS and the Gap to Shop AnalyticsLab Locked
  12. ROAS in Context: Margin Lookup with XLOOKUP, Break-Even ROAS and Profit After Ad SpendLab Locked
  13. Scaling a Budget: Rising Cost per Purchase, Profit per Day and the Cost of Each Extra PurchaseLab Locked
  14. Seasonal CPM: Trend with SLOPE, a FORECAST.LINEAR Plan and What the Same Budget Buys in Peak WeeksLab Locked
  15. Split Tests: Conversion Rates, Relative Lift, the z-Score and an Honest VerdictLab Locked
  16. Quality Checks Before Reporting: Naming Codes with LEFT, Impossible Rows, Duplicate Ads and Reconciling SpendLab Locked
  17. Funnel Stages: Prospecting, Retargeting and Retention by Share of Spend, Cost per Purchase and New CustomersLab Locked
  18. Budget Pacing: Cumulative Spend, the Plan to Date with DAY and EOMONTH, Month-End Projection and the Corrected Daily BudgetLab Locked
  19. Scenario Grid: Cost per Purchase for Every CPM and Conversion Rate with Mixed References and a Heat MapLab Locked
  20. Final Project: The Monthly Meta Ads Report by Funnel Stage with Spend, CPM, Cost per Purchase, ROAS, a Verdict and a ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

How a Meta ads account is organised, and why it matters for your numbers

Open the ads manager of a Facebook and Instagram advertiser for the first time and you see three tabs: campaigns, ad sets and ads. They are not three lists of different things. They are three levels of one tree, and every dollar you spend and every purchase you get is recorded at the bottom of that tree and added up on the way to the top. If you understand that one idea, most reports stop being confusing.

This matters for a practical reason. The question is this working? has a different answer at each level. An account can look healthy while one campaign burns money. A campaign can look mediocre while one of its ad sets is excellent. An ad set can look fine while a single video carries it and two other ads waste a third of its budget. People who read only the top line make slow and often wrong decisions; people who can move between the levels in a spreadsheet find the problem in minutes.

In this free lesson you take an ad-level export, calculate the cost per purchase of every ad, and roll the results up to ad sets and campaigns with one Excel function, SUMIFS. The example is Saltmarsh Candle Co., a fictional online shop for candles and home fragrance. All campaigns, audiences and numbers are invented for learning, and any benchmark you read here is illustrative, not official platform data.

The three levels: campaign, ad set, ad

Each level of the tree holds different settings. Knowing which setting lives where tells you what a comparison between two rows of a report actually compares.

  • Campaign. The campaign holds the objective: what you ask the delivery system to get for you, for example sales, leads or traffic. A campaign can also hold one shared budget for all of its ad sets (lesson 8 covers that choice). Two campaigns usually differ in purpose: finding new customers versus reminding recent visitors.
  • Ad set. The ad set decides who sees the ads (the audience), where they appear (placements such as feed, Stories or Reels on Facebook and Instagram), when they run, and, unless the campaign manages it, how much is spent per day. Two ad sets in one campaign usually differ in audience.
  • Ad. The ad is what people see: a video or image, the text, the headline and the link. Two ads in one ad set differ in creative and compete for the same audience.

One account, three levels: campaign, ad set, adCampaignobjectiveAd setaudience,placements, budgetAdcreative, text, linkProspecting$7,800 · 167 purchasesRetargeting$1,840 · 94 purchasesBroad US$3,700 · 77$1,84046$1,21022$6509Lookalike buyers$2,800 · 71$1,42041$98024$4006Interest home decor$1,300 · 19$76012$5407Site visitors 30d$1,000 · 41$62027$38014Cart abandoners 14d$840 · 53$54036$30017Each ad box shows its spend and, in bold, its purchases.Spend and purchases add up from the ads to the ad set and from the ad sets to the campaign.
Figure 1: The fictional September account of Saltmarsh Candle Co. Two campaigns, five ad sets, twelve ads. Results are recorded at ad level and add up along the lines.

Read the figure from the bottom. Each of the twelve ads has its own spend and its own purchases. An ad set has no results of its own: its spend is the sum of its ads' spend, and its purchases are the sum of its ads' purchases. The same holds one level up. That is why a spreadsheet can rebuild every level from a single ad-level export, and why the roll-up in this lesson is the foundation for the rest of the course.

Platform menus, product names and default settings change regularly. This course does not describe where to click. It teaches the durable part: what the levels mean and the arithmetic that connects them, which works on any export from any year.

Why the level you look at changes the answer

Saltmarsh spent $9,640 on Meta ads in September and the ads manager reports 261 purchases. The cost per purchase of the account is 9,640 ÷ 261 = $36.93. Suppose a candle order leaves about $30 after product, packaging and shipping. Is $36.93 good news or bad news? At account level you cannot tell, because the number mixes two very different jobs.

One level down, the picture splits. The Prospecting campaign, which shows ads to people who have never visited the shop, pays $46.71 per purchase. The Retargeting campaign, which shows ads to recent visitors and people who left something in the cart, pays $19.57. Neither number is visible in the account average, and the action you would take is different for each.

Same data, three levels of detailAccount$36.93 ($9,640 ÷ 261)Prospecting campaign$46.71 ($7,800 ÷ 167)Retargeting campaign$19.57 ($1,840 ÷ 94)The account figure of $36.93 hides a campaign at $46.71 and one at $19.57.
Figure 2: One number for the account, two for the campaigns. Every level answers a different question.

Go down one more level and you find that inside Prospecting one ad set buys at under $40 and another at almost $70, and inside each ad set one ad is clearly cheaper than the others. Each level answers a different question: the campaign level tells you whether a purpose is worth funding, the ad set level which audience works, the ad level which creative works. Mixing them up is the most common reason for bad decisions, for example pausing an audience because of one weak ad.

The worked example: Saltmarsh Candle Co. in September

Here is the export that the lab above uses: one row per ad, with the campaign and the ad set it belongs to. The last column is the first thing you calculate. Everything else in this article is derived from these twelve rows.

CampaignAd setAdSpendPurchasesCost per purchase
ProspectingBroad USVideo unboxing$1,84046$40.00
ProspectingBroad USCarousel bestsellers$1,21022$55.00
ProspectingBroad USStatic autumn scent$6509$72.22
ProspectingLookalike buyersVideo unboxing$1,42041$34.63
ProspectingLookalike buyersCarousel bestsellers$98024$40.83
ProspectingLookalike buyersStatic autumn scent$4006$66.67
ProspectingInterest home decorVideo unboxing$76012$63.33
ProspectingInterest home decorStatic autumn scent$5407$77.14
RetargetingSite visitors 30dCarousel bestsellers$62027$22.96
RetargetingSite visitors 30dStatic reminder$38014$27.14
RetargetingCart abandoners 14dDynamic product ad$54036$15.00
RetargetingCart abandoners 14dStatic free shipping$30017$17.65
Total$9,640261$36.93

The structure is typical for a small shop. Prospecting has three ad sets: a broad audience in the United States, a lookalike audience built from past buyers, and an interest audience around home decor. Retargeting has two: people who visited the site in the last 30 days and people who abandoned a cart in the last 14 days. Audiences like these are defined by behaviour (visited, bought, watched) or by broad interests. They are never built on protected characteristics, and nothing in this course asks you to target or exclude people that way.

Step 1: the cost per purchase of each ad

The cost per purchase answers one question: how many dollars of advertising did one order cost? The formula is spend divided by purchases. With the spend in column D and the purchases in column E, the first data row is =D4/E4. Copy it down to row 15 and every ad has its value.

Take the ad Static autumn scent in the Broad US ad set, row 6 of the sheet. It spent $650 and brought 9 purchases: 650 ÷ 9 = $72.22. The video in the same ad set spent $1,840 for 46 purchases, which is exactly $40.00. Same audience, same days, same budget pool, and one creative costs almost twice as much per order as the other. That is a finding at ad level: it says something about the creative, not about the audience.

Check the direction of a ratio with a rough estimate before you trust it. $650 for 9 orders must be around $70 each. If your cell shows 0.014 you divided purchases by spend; that is orders per dollar, a valid number but not the one a report calls cost per purchase.

Step 2: rolling ads up to ad sets with SUMIFS

To judge an audience you need the totals of its ad set. You could add the right cells by hand, but that breaks as soon as a row is added or sorted. SUMIFS does it by name.

=SUMIFS(sum_range, criteria_range, criterion) looks at every row. Where the cell in the criteria range equals the criterion, it adds the cell from the sum range. In the lab the ad set names are listed once more in column H, so the spend of the first ad set is =SUMIFS($D$4:$D$15,$B$4:$B$15,H4): add the spend in D for all rows whose ad set in B equals the name in H4.

  1. Type the five ad set names into H4:H8 (in the lab they are already there). They must be spelled exactly like the names in column B.
  2. In I4 enter =SUMIFS($D$4:$D$15,$B$4:$B$15,H4). The dollar signs make both ranges absolute, so they stay on rows 4 to 15 when the formula is copied; H4 has no dollar signs, so it moves down to H5, H6 and so on.
  3. Copy I4 down to I8. Lookalike buyers shows $2,800: 1,420 + 980 + 400.
  4. In J4 enter the same formula with the purchases as the sum range: =SUMIFS($E$4:$E$15,$B$4:$B$15,H4), and copy it down. Site visitors 30d shows 41: 27 + 14.
  5. Check the roll-up: the five spend values must add up to the account total of $9,640 and the purchases to 261. If they do not, a name is misspelled or a row is outside the ranges.

Ad setSpendPurchasesCost per purchase (correct)Average of its ads (wrong)
Broad US$3,70077$48.05$55.74
Lookalike buyers$2,80071$39.44$47.38
Interest home decor$1,30019$68.42$70.24
Site visitors 30d$1,00041$24.39$25.05
Cart abandoners 14d$84053$15.85$16.32

Step 3: the cost per purchase of an ad set

With the two sums next to each other, the cost per purchase of an ad set is one division: =I4/J4. Broad US: 3,700 ÷ 77 = $48.05. Lookalike buyers: 2,800 ÷ 71 = $39.44. Interest home decor: 1,300 ÷ 19 = $68.42. In Retargeting, Site visitors 30d costs $24.39 and Cart abandoners 14d $15.85 per purchase.

There is a tempting shortcut that gives a wrong answer: averaging the ad-level values from step 1. For Broad US the three ads cost $40.00, $55.00 and $72.22 per purchase; their average is $55.74. The true value is $48.05. The average is too high because it treats the three ads as equally important, while the cheap video accounts for half of the ad set's spend and 46 of its 77 purchases. The right-hand column of the table shows the size of this error for every ad set.

Cost per purchase: ad sets (bars) and their ads (dots)Ad set = total spend ÷ total purchasessingle ad$0$20$40$60$80$48.05Broad US$39.44Lookalike buyers$68.42Interest homedecor$24.39Site visitors30d$15.85Cart abandoners14dThe bar does not sit at the plain average of the dots: ads with more spend pull it towards their own value.
Figure 3: Cost per purchase per ad set, with the individual ads as dots. Broad US sits at $48.05, not at the $55.74 average of its three ads.

Rule for every roll-up in this course: add first, divide afterwards. Sum the spend, sum the purchases, then divide the sums. An average of ratios is only correct when every row has the same weight, which in advertising data is never the case.

Step 4: one level higher, the campaign

The campaign roll-up is the same formula with a different criteria range. The campaign names are in column A, so the Prospecting spend is =SUMIFS($D$4:$D$15,$A$4:$A$15,H12), which returns $7,800; the purchases come to 167, and the cost per purchase is $46.71. Retargeting: $1,840 ÷ 94 = $19.57.

A typical slip at this step is to keep column B as the criteria range. Column B holds ad set names, none of which is "Retargeting", so both sums are zero and the division shows an error. When a SUMIFS returns 0 although you expect a value, look at the criteria range first, then at the spelling of the criterion.

How to read the result

You now have the same month at three levels of detail. Here is what each level says about Saltmarsh, and what it does not say.

  • Campaign level. Retargeting buys at $19.57, Prospecting at $46.71. That does not make Retargeting the better campaign. It reaches people who already know the shop, some of whom would have ordered anyway, and its audiences are small. Prospecting pays more because it does the harder job of finding new customers. Later lessons measure both effects: attribution in lesson 11, new-customer share in lesson 17.
  • Ad set level. Within Prospecting, Lookalike buyers ($39.44) beats Broad US ($48.05), and Interest home decor ($68.42) is far behind on only 19 purchases. This is a statement about audiences, and the first candidate for a budget change.
  • Ad level. In all three prospecting ad sets the unboxing video is the cheapest ad and the static autumn image the most expensive. That pattern across audiences is strong evidence about the creative: make more videos like the first, and stop paying for the image.
  • Sample size. Interest home decor has 19 purchases, and its static ad only 7. One or two orders more or less would move those numbers a lot. Treat small rows as hints and wait for more data before a final decision; lesson 15 shows how to test whether a difference is real.

Notice that the three findings lead to three different actions (rethink a campaign's role, shift budget between audiences, replace a creative) and that none of them is visible in the account total of $36.93.

Common mistakes

  • Averaging ratios. Using AVERAGE on the ad-level cost per purchase instead of dividing summed spend by summed purchases. The error grows with the difference in size between the ads.
  • Comparing across levels. Setting the cost of one ad against the cost of a whole campaign. Compare ads with ads in the same ad set, ad sets with ad sets in the same campaign.
  • Relative ranges. Writing SUMIFS(D4:D15,B4:B15,H4) without dollar signs. In the first row it works; copied down, the ranges slide and silently leave out rows.
  • Wrong criteria range. Summing by campaign while the criteria range still points at the ad set column, or the other way round.
  • Inconsistent names. "Broad US" and "Broad US" with two spaces are two different texts for SUMIFS. A fixed naming scheme prevents this; lesson 16 adds checks for duplicates and impossible rows.
  • Judging on a handful of purchases. A cost per purchase from 6 or 7 orders is a rough hint, not a measurement.
  • Reading a cheap retargeting result as proof. Low cost at the bottom of the funnel partly reflects who is in the audience, not only how good the ads are.

A structure that keeps roll-ups meaningful

The arithmetic works for any account, but the result is only useful if the structure itself is tidy. A roll-up by ad set says something about audiences only when each ad set stands for one clearly different audience. If three ad sets target nearly the same people, their numbers describe the same audience three times, split by chance (lesson 6 measures such overlap). In the same way, a roll-up by campaign is informative when each campaign has one purpose.

Simple accounts tend to be easier to read and to run. Many small ad sets spread the budget thin, so that none of them collects enough purchases for the delivery system to settle (the learning phase of lesson 7), and each row of your report rests on a handful of orders. A few ad sets with clearly different audiences, each with several ads, give you rows that are large enough to compare.

Before you build any report, write down in one sentence what each campaign is for and what distinguishes each ad set from its neighbours. If you cannot, the structure needs attention before the numbers do.

Check your work

  1. The ad set spends add up to the account spend ($9,640) and the ad set purchases to 261. The two campaign rows add up to the same totals.
  2. Every ad set's cost per purchase lies between the cheapest and the most expensive of its own ads. Broad US at $48.05 lies between $40.00 and $72.22. A value outside that span means a wrong range.
  3. Change one input, for example the purchases of one ad, and watch the ad set and campaign rows follow. If a number does not move, it was typed in, not calculated. The lab checks exactly this: it accepts formulas, not typed results.

What the rest of the course covers

The roll-up tells you where the cost per purchase is high. The remaining nineteen labs explain why, and what to do about it, always with a new data set from Saltmarsh Candle Co. and with every cell checked as you type.

  • Lessons 2 and 3: the delivery chain. CPM, link CTR and conversion rate multiply into the cost per purchase; indexes against the account show which link is weak.
  • Lessons 4 to 6: audiences. Reach, frequency and saturation, creative fatigue over twelve weeks, and overlap between audiences.
  • Lessons 7 and 8: the learning phase and what it demands of a budget; one campaign budget (CBO) compared with ad set budgets.
  • Lessons 9 to 12: reading reports. Placement breakdowns, hook and hold rates of video ads, attribution windows and the gap to shop analytics, break-even ROAS and profit after ads.
  • Lessons 13 to 19: decisions. The marginal cost of scaling, a seasonal CPM forecast, split-test significance, quality checks on exports, funnel stages, budget pacing and a scenario grid.
  • Lesson 20: a final project, the monthly report by funnel stage, followed by a 20-question exam and a certificate.

Key formulas at a glance

  • Cost per purchase of an ad: =D4/E4 (spend ÷ purchases).
  • Spend of an ad set: =SUMIFS($D$4:$D$15,$B$4:$B$15,H4).
  • Purchases of an ad set: =SUMIFS($E$4:$E$15,$B$4:$B$15,H4).
  • Cost per purchase of an ad set: =I4/J4, the sum of spend divided by the sum of purchases.
  • Campaign level: the same three formulas with $A$4:$A$15 as the criteria range.

Saltmarsh Candle Co., its campaigns and all figures in this article are fictional and serve only as a teaching example. Real accounts will show different costs; the method of adding first and dividing afterwards stays the same.

Frequently asked questions

What is the difference between a campaign, an ad set and an ad on Meta?

The campaign holds the objective, for example sales. Each ad set inside it defines the audience, the placements, the schedule and usually the budget. Each ad inside an ad set is the creative people see: video or image, text and link. Results are recorded per ad and add up to the ad set and the campaign.

How do I calculate the cost per purchase of an ad set in Excel?

Add the spend of all its ads, add their purchases, then divide. With SUMIFS: =SUMIFS(spend_range, ad_set_range, name) divided by =SUMIFS(purchase_range, ad_set_range, name). In the example Broad US spent $3,700 for 77 purchases, which is $48.05 per purchase.

Why can't I average the cost per purchase of the ads?

An average gives every ad the same weight, although the ads spent very different amounts. In the example the three Broad US ads cost $40.00, $55.00 and $72.22 per purchase, an average of $55.74, while the ad set really pays $48.05. Always divide the summed spend by the summed purchases.

What do the dollar signs in $D$4:$D$15 do?

They make the reference absolute. When you copy the formula down, an absolute range stays on rows 4 to 15, while a relative reference such as H4 moves to H5, H6 and so on. Without the dollar signs the ranges slide down and leave out rows.

Is retargeting better than prospecting because its cost per purchase is lower?

Not necessarily. Retargeting reaches people who already visited the shop, and some of them would have bought anyway. Its audiences are small and are refilled by prospecting. A lower cost per purchase therefore does not prove a higher contribution; the course looks at attribution and new-customer share to judge that.

Are the numbers in this lesson real Meta benchmarks?

No. Saltmarsh Candle Co. and all campaigns, audiences and figures are fictional and chosen for teaching. Costs differ widely by market, product and season, and platform features change. The lesson teaches the arithmetic and the logic, which stay the same.

Keep going: Meta Ads (Facebook & Instagram): Campaign Structure, Audiences & Overlap, the CPM-CTR-CVR Chain, Frequency, Learning Phase, Budgets & Reporting

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. The Delivery Chain: CPM, Link CTR and Conversion Rate and How They Multiply into the Cost per Purchase
  2. Diagnosing a Weak Ad Set: Account Rates from Totals, CPM, CTR and CVR Indexes and an IFS Diagnosis
  3. Audiences: Reach, Frequency, Reach % of the Audience and the Cost per 1,000 People Reached
  4. Frequency and Creative Fatigue: Weekly Frequency, CTR Change Against Week 1 and a Refresh Rule
  5. Audience Overlap: Shared People, Overlap % of the Smaller Audience, Unique Reach and When to Merge
  6. The Learning Phase: Weekly Optimisation Events, Days Since the Last Edit and the Budget an Ad Set Needs
  7. Budgets: Campaign Budget (CBO) vs Ad Set Budgets, Budget Shares and the Blended Cost per Purchase
  8. Placement Breakdowns: Facebook vs Instagram, Feed vs Stories vs Reels, Shares of Spend and Cost per Purchase
  9. Creative Testing: Hook Rate, Hold Rate, Cost per Purchase and Ranking Video Ads with RANK.EQ
  10. Reporting and Attribution: 7-Day Click vs 1-Day View, View-Through Share, ROAS and the Gap to Shop Analytics
  11. ROAS in Context: Margin Lookup with XLOOKUP, Break-Even ROAS and Profit After Ad Spend
  12. Scaling a Budget: Rising Cost per Purchase, Profit per Day and the Cost of Each Extra Purchase
  13. Seasonal CPM: Trend with SLOPE, a FORECAST.LINEAR Plan and What the Same Budget Buys in Peak Weeks
  14. Split Tests: Conversion Rates, Relative Lift, the z-Score and an Honest Verdict
  15. Quality Checks Before Reporting: Naming Codes with LEFT, Impossible Rows, Duplicate Ads and Reconciling Spend
  16. Funnel Stages: Prospecting, Retargeting and Retention by Share of Spend, Cost per Purchase and New Customers
  17. Budget Pacing: Cumulative Spend, the Plan to Date with DAY and EOMONTH, Month-End Projection and the Corrected Daily Budget
  18. Scenario Grid: Cost per Purchase for Every CPM and Conversion Rate with Mixed References and a Heat Map
  19. Final Project: The Monthly Meta Ads Report by Funnel Stage with Spend, CPM, Cost per Purchase, ROAS, a Verdict and a Chart

Buy course — $9.90 See the full course