Automating My Book Ad Analysis in a Day

For six months I’ve been doing what most indie authors running Meta ads do: downloading CSVs. Meta exports. Amazon Attribution exports. KDP royalty reports. Then hours hand-assembling them in a spreadsheet, squinting at the result, and asking the only question that matters (are my ads actually making money?) without ever getting to a straight answer. I knew that the answer was in there. But extracting it was a part-time analyst’s job, and I already have two full-time jobs: writing books and consulting.

This week I sat down with Claude (Anthropic’s AI, running in its Cowork desktop app) and, in one working day, replaced this whole mess with a system that, for the most part, runs itself and provides far better accuracy than I could ever achieve. This post is about what we built, why we built it that way, and, more interestingly, what six months of my own data had been trying to tell me.

Spoiler alert: It turned out that I’d been both too optimistic and too pessimistic, simultaneously, about different ads.

The problem statement that got me started

I write the Old Blood Saga: five books, UK and US markets, roughly a 50/50 split between Kindle Unlimited page reads and straight sales. That mix is exactly the kind of thing that makes ad measurement so difficult and miserable.

• Amazon Attribution undercounts. It tracks clicks to purchases, but KU borrows are invisible to it, read-through happens weeks later, and conversions lag. Attribution isn’t the truth. It’s a floor. But it’s the best tracker we’ve got from Facebook clicks to landing on your books product page.
• Royalties arrive without provenance. KDP tells me what I earned, not which ad earned it, or whether I’d have earned it anyway.
• The data lives in three places that don’t talk to each other, on three different schedules.

So all of my prior attempts at “analysis” felt like vibes, magic and guessing with spreadsheets. I suspected some ads were winners and some were bleeding my spend, but I couldn’t prove which was which.

What we built

The architecture ended up surprisingly simple. Three layers:

1. Windsor.ai (£19/month) holds the live connection to my Meta ad account. It’s just a pipe: when queried, it answers with fresh ad data.
2. Airtable is the memory: six tables covering ads, daily metrics, attribution, royalties, books, and report history. Claude built the whole base and seeded it with my last 90 days of ads, pulling names, copy, budgets and launch dates straight from Meta. The manual spreadsheet I’ve previously written about became obsolete in about an hour.
3. Claude’s scheduled tasks are the engine. Every Monday it syncs the week’s Meta metrics into Airtable. On the 1st and 15th it runs a full profit analysis and writes me a Word report. I was going to use Make for this but it turned out to be unnecessary with Claude in the loop–though as I scale some of these automations, I may well incorporate Make in a future iteration.

My job going forward is two touches a month, about fifteen minutes total: export Amazon Attribution fortnightly and a KDP royalty report monthly, and upload them. The system does everything else, including checking whether its own previous recommendations actually worked, which is the part no dashboard ever does.

That last point is the real intent. I didn’t want prettier charts (though being a lifelong designer I do love a pretty chart). I wanted a system that ends every report with an objective, rational, data-driven decision (kill, cut, or scale) measured against a rule.

The rule the data produced

Here’s where things got interesting. Cross-referencing six months of royalties against ad history gave us a number I’d never had: my organic baseline. In January, before ads, the series earned £407/month on its own. That means roughly £400 of every month’s royalties would arrive with zero ad spend, so my raw “royalties vs spend” comparison had been flattering the ads all along.

But the same analysis cut the other way too. Because of read-through and KU, a converted reader isn’t worth one book. They’re worth £3.54 lifetime, and £5.90 measured on recent months. Read-through multiplies attributed value by 1.6–2.6×. Attribution had been underselling my best ads as badly as the royalty totals had been overselling the account.

Those two numbers combine into a permanent decision rule: pay up to ~£4.50 per converted reader; never above £6.

Measured against it, the fog cleared instantly:

• My best US ad acquires readers at £3.26. Genuinely profitable. Scale it (in ≤20% budget steps, so Meta’s learning phase doesn’t reset).
• My main UK ad was profitable at £15/day (£4.56/reader) but a loser at its scaled £38/day budget (£8.20/reader). The cut wasn’t damage control. £15 is the profitable setting.
• Three other ads costing £6.89, £9.86 and £25.62 per reader: kills, confirmed three different ways.

Post-cuts, the portfolio models out at a true ROAS of roughly 1.1–1.6 on ~£35/day. That’s the first time the account has been configured to make money rather than lose it politely.

The finding I didn’t expect

The royalty data held one more revelation. Normalizing KU reads by each book’s length, my series funnel looks like this: I lose 40–45% of readers between Books 1 and 2, and then almost nobody. From Book 2 onward, 80–95% of readers continue at every step.

Which means my single biggest profit lever isn’t advertising at all. It’s the Book 1 → Book 2 transition: the back-matter link, the ending hook, and the box set (a bundle buyer pre-commits through Book 3 and skips the leaky step entirely). Lifting continuation from 60% to 70% raises the value of every reader by about 15%, and my tolerable acquisition cost with it. Six months of ad anxiety, and the answer was partly in my own hands.

Takeaways if you want to do this

Treat Amazon Attribution as a floor, but never as the final word on your progress. If you’re in KU with a series, your true return is likely 1.6–2.6× what Attribution shows. “Kill decisions” that are made on raw attribution numbers may be killing profitable ads.

Find your organic baseline. Without knowing what you’d earn with ads off, “royalties minus spend” is fiction. One clean pre-ads month is worth more than a year of ROAS screenshots.

Convert everything to £-per-reader. ROAS varies with price points and KU mix; cost-per-reader against reader lifetime value is a rule you can apply to any ad, in any market, forever.

Automate the cadence, not just the collection. The value isn’t that a robot downloads my data. It’s that every fortnight, without fail, something honest (not something subjective like me) asks: did the last set of decisions work?

You don’t need to code. I didn’t write a line of code. The stack is Windsor.ai, an Airtable base, and a Claude subscription. One day to build, fifteen minutes a month to run.

August will be the first full month under the new regime: ad kills made, UK budget stepped down, US winner climbing, a box-set ad experiment queued. The system will judge it on the 1st and the 15th whether I like the answer or not. That’s the thing I was actually aiming for…an objective analyst to support my publishing business.

I’ll report back and let you know how it goes.

Software used:

Claude: £13/month - also used for social media generation and analysis, business intelligence, research, business development and sales pipeline

Windsor.ai: £19/month

Airtable: £20/month - also used for tracking business subscriptions, social media automations / agent, business development pipeline for my consultancy, digital asset management system for marketing/sales/pr/promotional image tracking

Questions about any piece of this (the Airtable schema, the Windsor setup, the read-through maths) are welcome in the comments, and I’ll cover the popular ones in a follow-up.