How to Analyse Your Facebook Ad Results in Google Sheets with AI

Why Ads Manager Alone Is Not Enough

Meta Ads Manager is good at showing you numbers. It is not good at telling you what they mean. You can see that an ad has a 0.8% CTR and a cost per purchase of ₹640, but you still have to work out whether that is good for this account, whether it is getting worse, and which other ads look like it.

That interpretation is where most of the weekly reporting time goes. A spreadsheet is still the best place to do it, because you can keep weeks side by side, add your own columns, and share one link with a client or a teammate.

This guide walks through the workflow I use: get last week's ad data into Google Sheets, calculate the core metrics with normal formulas, then use AI formulas for the parts that need judgement. That means commenting on CTR and CPA, classifying ad copy by angle, flagging creative fatigue and writing the summary.

Step 1: Get the Data Into the Sheet

You need one row per ad, with at least these columns:

Option 1: Export a CSV from Ads Manager

1. Open Ads Manager and switch to the Ads tab, so you get one row per ad rather than per campaign.
2. Set the date range to last 7 days.
3. Click Columns → Customise columns and add Amount spent, Impressions, Link clicks, Purchases, Frequency and Primary text. Save it as a preset so you only do this once.
4. Click Reports → Export table data and choose CSV.
5. In Google Sheets, go to File → Import → Upload, pick the CSV and choose "Insert new sheet".

The column names Meta uses change slightly from time to time, so rename the headers to match the table above. Your formulas will then keep working week after week even if the export changes.

Option 2: Ask an AI assistant to fetch it

If you already use Claude or ChatGPT for work, you can skip the export. With a Meta ads connector installed you can ask Claude to pull last week's ad numbers in plain English ("spend, impressions, link clicks, purchases and frequency per ad for the last 7 days, as a table") and paste the table straight into the sheet. It is read-only reporting, so nothing in the account changes. Either route ends in the same place: one row per ad with the columns above.

Step 2: Calculate the Core Metrics with Plain Formulas

Do not use AI for arithmetic. Normal formulas are instant, free and always right. Add these in columns I to K:

I2 (CTR):  =IFERROR(E2/D2, 0)
J2 (CPC):  =IFERROR(C2/E2, 0)
K2 (CPA):  =IFERROR(C2/F2, "No purchases")

Format I as a percentage and J and K as currency, then drag all three down.

Here is a small example sheet for an online store with five ads. The numbers are made up for illustration but the pattern is a common one:

You can already see a few things from the numbers. The AI columns help you see them faster across 50 or 200 ads, and turn them into sentences you can send.

Step 3: Get a One-Line Comment on CTR and CPA

The useful question is not "what is my CPA" but "is this CPA good compared with the rest of the account". Give the AI the account average as context so its comment is relative, not generic.

First, put your account averages somewhere fixed, for example:

Q1: =AVERAGE(I2:I200)             (average CTR)
Q2: =SUM(C2:C200)/SUM(F2:F200)    (blended CPA)

Then in column L:

=SHEETAI("You are a performance marketer. In one short sentence, comment on this Facebook ad. CTR: " & TEXT(I2,"0.00%") & " (account average " & TEXT($Q$1,"0.00%") & "). CPA: " & K2 & " (account blended CPA " & TEXT($Q$2,"0") & "). Frequency: " & G2 & ". Say whether to keep, watch or cut it and why.")

Two things make this column far more reliable. First, pass numbers as formatted text: TEXT(I2,"0.00%") sends "0.70%" instead of 0.007, and models read the first version correctly far more often. Second, ask for one sentence that ends in a verdict. Long answers look thorough, but nobody scans 80 of them on a Monday morning, and you can't filter on a paragraph.

Step 4: Classify Each Ad by Creative Angle

Numbers tell you which ads work. Classifying the copy tells you which kind of message works, which is what you need when you brief the next round of creatives.

In column M:

=SHEETAI_CLASSIFY(B2, "Social proof, Discount/offer, Founder story, Problem-solution, Feature list, Before/after")

SHEETAI_CLASSIFY returns exactly one label from your list, so the column stays clean enough to pivot on. If you want more than one label per ad (an ad can be both a discount and social proof), use SHEETAI_TAG instead:

=SHEETAI_TAG(B2, "Social proof, Discount/offer, Urgency, Founder story, Problem-solution, Feature list")

Now build a pivot table with Insert → Pivot table: rows = the angle column, values = SUM of Spent, SUM of Purchases, and a calculated field for CPA (=SUM(Spent)/SUM(Purchases)).

On a real account with 40 or more ads this is usually the most useful view in the whole report. You stop arguing about individual ads and see that, say, social proof ads convert at half the CPA of feature-list ads. Now you know what kind of ad to brief next.

If you want a deeper look at writing category lists that give consistent results, the guide on classifying data automatically in Google Sheets covers it, and the SHEETAI_CLASSIFY docs list every option.

Step 5: Flag Creative Fatigue

Creative fatigue is when the same people have seen an ad too many times and stop responding. The usual signs are rising frequency and falling CTR at the same time. You can flag the obvious cases with a normal formula first:

N2: =IF(AND(G2>=3, ISNUMBER(H2), I2 < H2*0.8), "Fatigue risk", "OK")

That marks any ad with frequency of 3 or more and a CTR that dropped by more than 20% week on week. In the example sheet it catches "Founder story" (1.20% → 0.70%) and "Before/after reel" (1.90% → 1.20%).

The thresholds are rules of thumb, not laws. Prospecting ads to a broad audience usually tire later than retargeting ads to a small audience, so adjust the numbers to your account. For the borderline rows, let the AI weigh it up and suggest what to do:

=SHEETAI("Frequency " & G2 & ", CTR this week " & TEXT(I2,"0.00%") & ", CTR last week " & TEXT(H2,"0.00%") & ", ad angle: " & M2 & ". Is this Facebook ad showing creative fatigue? Answer 'Yes', 'No' or 'Too early', then one short reason and one specific next step.")

A practical note: fatigue is about trend, so this only works if you keep last week's CTR. The easiest way is to duplicate the tab every Monday, name it by date, and pull the previous CTR across with a VLOOKUP on ad name.

Step 6: Write the Weekly Summary

Once the columns are filled, you need a short paragraph for the client or your team. Join the key columns into one prompt with TEXTJOIN:

=SHEETAI("Write a 4-sentence weekly Facebook ads summary for a client. Be specific and plain, no jargon. Total spend: " & TEXT(SUM(C2:C200),"0") & ". Total purchases: " & SUM(F2:F200) & ". Blended CPA: " & TEXT($Q$2,"0") & ". Ads with verdicts: " & TEXTJOIN("; ", TRUE, FILTER(A2:A200 & " - " & L2:L200, A2:A200<>"")))

For the example sheet that gives something like: "We spent ₹25,600 last week for 31 purchases, a blended CPA of ₹826. The Priya UGC review and the 20% off carousel did most of the work at under ₹600 per purchase. The founder story and before/after reel are losing steam as the same people keep seeing them, so we will replace both with new edits this week. The feature list static ad had no purchases and should be paused."

Read it before sending. The model only knows what is in the sheet, so it will not know about the stock issue on Tuesday or the website being down for an hour. Add those yourself.

If the report goes to a client as slides rather than a link, you can paste this summary and your pivot table into MagicSlides to turn the sheet into a client report deck instead of building it by hand.

Keeping the Costs and Mistakes Down

A few habits keep this workflow cheap and accurate.

Keep arithmetic in normal formulas and judgement in AI formulas. CTR, CPC and CPA should never go through a model.

Freeze results once you have them. AI formulas can recalculate when the sheet changes, and you don't want last week's commentary quietly rewriting itself. After the weekly run, copy the AI columns and use Paste special → Values only.

Only run AI on ads that matter. Filter to ads with meaningful spend first. An ad that spent ₹150 does not need a paragraph of analysis.

Keep the category list fixed. If the angle labels change every week, you can't compare weeks in the pivot table.

Read a sample. I read about 10 rows of AI output each week. If the comments start to drift, fix the prompt rather than editing outputs one by one.

The Finished Sheet

At the end you have one tab per week with:

• Raw numbers per ad
• CTR, CPC and CPA from plain formulas
• A one-line verdict per ad (keep, watch, cut)
• The creative angle of each ad, with a pivot showing which angles convert
• A fatigue flag on ads that are wearing out
• A four-sentence summary ready to send

The first setup is the slow part, mostly renaming columns and tuning the prompts until the verdicts sound like you. After that, the weekly run is short: get the data in, drag the formulas down, freeze the values, read the summary, fix anything it got wrong. I'd rather spend the saved time on the next batch of ads, since that's what actually moves the numbers.