You run ads from three ad accounts and want to see all the spend in one place. No off-the-shelf report shows that, because the data sits in different systems. The obvious answer: stack it into a Google Sheet every night and build charts on top.
#Why a spreadsheet is the right first step
There is no need to start with a database. A spreadsheet gives you what an off-the-shelf report cannot: the client sees the data right away and can change it. They add a column and write a formula of their own. A week of that makes it clear which numbers they actually need and which ones they asked for “just in case”.
It is the cheapest way to find out the requirements. We still start with a spreadsheet when a job is new.
But it has to be a draft, not the solution.
#Where a spreadsheet stops working
Volume. One ad account over a month is a few thousand rows. Three accounts over a year is well past a hundred thousand. A spreadsheet technically holds up, but at that size the formulas take tens of seconds to recalculate, and opening the file becomes a wait.
Fragility. The report rests on formulas that point at ranges. The client inserts a column in the middle, and half the formulas start counting the wrong thing. There is no error anywhere to see: the numbers simply change. The most expensive kind of breakage is the kind you cannot tell apart from normal operation.
History. A spreadsheet stores state, not history. If the campaign name has become the key — and it almost always does, because the name is visible and the id is not — then after a rename there is nothing left to line yesterday’s numbers up with today’s.
Permissions. Giving access to the dashboard usually means giving access to the raw data too: protected ranges and “view only” do not always save you, and there is nothing to build per-row permissions with inside one file.
#What to put in place of the spreadsheet
Three layers instead of one file.
Collection. A workflow pulls the data out of every ad account through the API each night and puts it into the database. Every row carries a date, an ad account, a campaign and a stable identifier — not a name. A rename no longer breaks anything.
Database. Postgres. History accumulates and old data is never overwritten. Comparing periods becomes an ordinary query instead of another sheet in the file.
Display. A separate page on your own domain: charts, filters, exports. The client sees the numbers and does not see the raw tables. Permissions are split: who gets to look, who gets to edit.
On top, where it is needed, an admin panel for the specific process: change a bid, flag a campaign, leave a comment against a day.
#What the replacement costs
More at the start and less afterwards.
A spreadsheet is a few hours. Your own solution is one or two days: the collection job, the database schema, the display page. After that the cost comes down to hosting.
The moment to switch has specific signs. If you recognise even two of these, the spreadsheet is already costing you more than the replacement would:
- the report takes more than ten seconds to open;
- someone has broken the formulas at least once by inserting a column;
- you cannot compare this month with the same month a year ago;
- access to the dashboard has to come with access to the raw data;
- the numbers in the spreadsheet and in the ad account disagree, and nobody knows why.
#Mistakes not to make
Do not carry the formulas over into the database. The logic of the report belongs in one place — in the query or in the code of the page. Half in SQL and half in formulas is the same fragility, only more complicated.
Do not store aggregates instead of raw data. You can always add things up; you cannot take them back apart. Write daily rows into the database and calculate the totals in the query.
Do not throw the spreadsheet out altogether. Exporting to it is a perfectly normal dashboard feature. The client is used to doing sums there, and there is no reason to take that away.
#Takeaways
- A spreadsheet is the cheapest way to find out which numbers are needed. As a draft it is the right call.
- It breaks on volume, on formulas, on the absence of history and on permissions.
- The replacement is three layers: nightly collection, a database with history, a separate display page.
- The key in the data is an identifier, not a campaign name.
- Keep the export to a spreadsheet: that is where the client is used to doing the sums.
#Common questions
#Can we stay on a spreadsheet if there is only one ad account?
Yes. One ad account and a few thousand rows is fine for a spreadsheet. The problems start with the second source and the first year of history.
#Wouldn’t an off-the-shelf BI service be simpler?
Often it is, and that is what we do when the job is a standard one. Your own solution wins when you need actions rather than viewing alone: change a bid, flag a campaign, tie ads to deals in the CRM.
#What about the data already sitting in the spreadsheet?
Move it into the database once. It is a one-off script: the history is kept, and comparing with past periods stays possible.
#Sources
- Google Sheets API — Limits — limits on requests and volume
- From our own experience