Build a Facebook Ads Report in Google Sheets

The workbook contains one sheet named Ads Report:

• A2:L2: reporting dates, targets, and configured budget
• A4:P5: calculated blended KPIs and overall signal
• A9:T16: campaign summaries, decision rules, owners, and actions
• A19:O36: daily campaign and ad-set records

Set the period and targets at the top. Then use the campaign table to assign each calculated performance result an owner, next action, and review date.

Don’t paste over B4:P5 or D10:O12. These ranges contain formulas, labels, or interpretation notes.

Two displayed labels should match the underlying calculations:

• Rename M4 from Link clicks to Clicks and O4 from Link CTR to CTR.
• Rename F9 from Link clicks to Clicks and I9 from Link CTR to CTR.

The calculated click fields use record-log column K. Although column L stores link clicks, it isn’t used in those summary calculations.

Also rename K2 from Active budget to Configured budget. Cell L2 sums campaign budgets regardless of delivery state, so enter zero for any inactive campaign that should be excluded.

Start by entering these example controls:

Format B2, D2, and F2 as dates, H2 as a number or 0.00x, and J2 as USD. If a date appears as a serial such as 46266, apply a date format instead of changing its value.

Cells B2 and D2 determine which records are included. By contrast, F2 indicates how current the data is but doesn’t filter the calculations.

Use this guarded overall-signal formula in F5:

=LET(
  dates_ok,IFERROR(AND(ISNUMBER($B$2),ISNUMBER($D$2),$B$2<=$D$2),FALSE),
  target_ok,IFERROR(AND(ISNUMBER($H$2),$H$2>0),FALSE),
  IF(
    NOT(dates_ok),"Check dates",
    IF(
      NOT(target_ok),"Check target",
      IF(
        B4=0,"No spend",
        IF(H4>=$H$2,"Blended ROAS on target","Blended ROAS below target")
      )
    )
  )
)

This formula prevents a blank, text, or reversed reporting period from being treated as a valid period with no spending. To check its behavior, temporarily test:

• Blank B2: expect Check dates.
• Text in D2: expect Check dates.
• A start date later than the end date: expect Check dates.
• Valid dates with H2 set to zero: expect Check target.

These guard cases are formula acceptance checks. They weren’t part of the supplied native execution record.

Next, enter the example campaign setup in A10:C12:

Campaign names must match record-log column B. Google Sheets SUMIFS text matching is generally case-insensitive, but extra spaces, punctuation changes, or different wording can prevent a record from matching. See Google’s SUMIFS documentation.

Use only ROAS or CPA in B10:B12:

• ROAS compares campaign ROAS with H2; higher is better.
• CPA compares cost per result with J2; lower is better.
• An unsupported or space-padded rule returns Check rule.

Place this formula in O10, then fill it down through O12:

=LET(
  decision_rule,B10,
  campaign_budget,C10,
  campaign_spend,D10,
  campaign_results,G10,
  campaign_cpa,K10,
  campaign_roas,L10,
  dates_ok,IFERROR(
    AND(ISNUMBER($B$2),ISNUMBER($D$2),$B$2<=$D$2),
    FALSE
  ),
  rule_ok,OR(decision_rule="ROAS",decision_rule="CPA"),
  target_ok,IF(
    decision_rule="ROAS",
    IFERROR(AND(ISNUMBER($H$2),$H$2>0),FALSE),
    IF(
      decision_rule="CPA",
      IFERROR(AND(ISNUMBER($J$2),$J$2>0),FALSE),
      FALSE
    )
  ),
  data_ok,IFERROR(
    AND(
      ISNUMBER(campaign_budget),campaign_budget>=0,
      ISNUMBER(campaign_spend),campaign_spend>=0,
      ISNUMBER(campaign_results),campaign_results>=0,
      ISNUMBER(campaign_cpa),campaign_cpa>=0,
      ISNUMBER(campaign_roas),campaign_roas>=0
    ),
    FALSE
  ),
  IF(
    NOT(dates_ok),"Check dates",
    IF(
      NOT(rule_ok),"Check rule",
      IF(
        NOT(target_ok),"Check target",
        IF(
          NOT(data_ok),"Check data",
          IF(
            campaign_spend=0,"No spend",
            IF(
              decision_rule="ROAS",
              IF(campaign_roas>=$H$2,"On target","Below target"),
              IF(
                campaign_results=0,"Insufficient results",
                IF(campaign_cpa<=$J$2,"On target","Above target")
              )
            )
          )
        )
      )
    )
  )
)

With this formula, a positive-spend CPA campaign with zero results can’t pass simply because its displayed cost per result is zero. The replacement formula’s additional guard behavior wasn’t separately covered by the supplied execution record.

Finally, complete the manual fields in P10:T12:

• Owner
• Next action
• Next-review date
• Audience
• Decision note

Format R10:R12 as dates. These management fields aren’t generated from the record log.

Delete or overwrite only the sample inputs in A20:O34. Then enter one aggregate row per date, campaign, and ad set in A20:O36.

Apply these formats:

• A20:A36 as dates
• I20:I36 and N20:N36 as USD
• J20:M36 as whole numbers

You may retain a paused row with zero spending for audit history.

The source log records spend, delivery metrics, results, revenue, and exceptions. Date filters exclude the labeled prior- and next-period rows.

Use one attribution basis for every amount in column N. If a lead campaign doesn’t have an assigned monetary value, enter 0 and use the CPA decision rule. Here, zero means no value was assigned. It doesn’t establish that the leads have no business value.

Before pasting an export, standardize its campaign names. The Google Sheets data-cleaning guide covers a broader cleanup process.

For September 1-30, 2026, the supplied native spreadsheet execution returned:

The control arithmetic is:

Spend:              1,960 + 1,300 + 880 = 4,140
Attributed revenue: 7,150 + 6,240 + 0 = 13,390
Results:            77 + 78 + 49 = 204
Blended ROAS:       13,390 / 4,140 = 3.23x
Cost per result:    4,140 / 204 = $20.29
Remaining budget:   6,000 - 4,140 = $1,860
CTR:                9,881 / 599,000 = 1.65%

The campaign rows should show:

The 204 blended results combine purchases and leads. Therefore, treat the blended cost per result as informational. Base decisions on each campaign’s result unit and selected ROAS or CPA rule.

Follow this process for each weekly update:

1. Append the new records at the first empty row. In the supplied fixture, the first empty row is initially A35:O35.
2. Change F2 to the latest data date.
3. Inspect the decisions in O10:O12.
4. Update next actions in Q10:Q12.
5. Update review dates in R10:R12.

To reproduce the supplied append case, begin with the unchanged September fixture. Set F2 to September 16, 2026, select A35, and paste this tab-delimited row:

2026-09-16	Retargeting - Catalog	Add-to-cart 14d	Sales	Purchases	Active	Add-to-cart users 14d	Feeds | Dynamic catalog	200	20000	600	540	12	960	New daily row

The values should fill A35:O35. If they remain in one cell, use Data > Split text to columns and select Tab as the separator.

After the update, the recorded execution returned:

• $4,340 total spend
• $14,350 attributed revenue
• 216 results
• 3.31x blended ROAS
• $1,660 remaining budget
• 10,481 clicks
• 1.69% CTR
• $1,500 retargeting spend
• 90 retargeting results
• On target for the retargeting campaign

You can also run an independent no-activity check. First, remove the appended row and reset the untouched baseline. Then enter:

Keep H2 at 2.5, J2 at $45, C10 at $3,000, and C11 at $1,800.

In the supplied execution record, this test returned a $4,800 configured budget, zero performance metrics, No spend in O10:O12, and no division errors. Remaining budgets were $3,000, $1,800, and $0.

Afterward, restore the September dates and set C12 back to $1,200.

When starting a new period, change B2 and D2 while retaining the historical records. The supplied execution confirmed that labeled August and October exceptions were excluded from the September totals.

The formulas use inclusive >=B2 and <=D2 criteria. However, exact start- and end-date rows weren’t separately isolated in the recorded test.

Before sharing the report, confirm:

• B2, D2, and F2 contain valid dates.
• The start date isn’t later than the end date.
• Applicable ROAS and CPA targets are positive numbers.
• Campaign budgets and delivery metrics are nonnegative numbers.
• Campaign names have consistent spelling, spacing, and punctuation.
• Every decision rule is ROAS or CPA.
• Attributed revenue follows one documented basis.
• Unvalued lead campaigns use zero revenue and a CPA rule.
• A positive-spend CPA campaign with zero results shows Insufficient results.
• Invalid dates and targets remain visible instead of producing No spend.
• No data has overwritten B4:P5 or D10:O12.
• Records outside the selected period are excluded.
• A valid period with no matching records shows zero metrics and No spend.

ModelMonkey can help read these ranges, analyze their contents, report formula errors, and organize spreadsheet data. It doesn’t provide a verified Facebook Ads synchronization, so check its output against the source export.

For example, use this task-specific request:

Review Ads Report!A2:T12 and Ads Report!A20:O36 without changing cells.
Report formula errors, invalid or reversed dates, nonpositive targets,
negative or nonnumeric budgets and delivery metrics, unsupported campaign
rules, and campaign names with inconsistent spacing or punctuation.
Flag any CPA campaign with positive spend and zero results. Summarize
decisions in O10:O12 and remaining budgets in M10:M12.