Checking how a site is doing usually means opening Google Analytics, opening Search Console, remembering that one lags the other by a few days, and doing arithmetic in your head. Most weeks you skip it.
So we built the thing we actually wanted: one spreadsheet that pulls both sources every morning and answers "how are we doing" in about ten seconds. Here is what is in it and what we learned building it.
Why a spreadsheet
Dashboard tools are better looking. A spreadsheet won on three things that mattered more.
It is already shared — no new login, no seat to pay for, and anyone we work with can open it. It is editable by the person reading it, so a question we did not anticipate becomes a formula rather than a feature request. And the data lands somewhere you can keep, which matters when an analytics platform only holds so much history.
The bar for a custom tool is not "could a product do this". It is "would we actually open the product".
What it tracks
Five tabs, in the order you would look at them.
- Dashboard — ten headline numbers, each shown as last 7 days against the previous 7, and last 28 against the previous 28. Users, sessions, page views, engagement rate, search clicks, impressions, click-through rate, average position. Four charts underneath.
- Daily — ninety days of raw rows from both sources, with 7-day rolling averages so a quiet Saturday does not read as a collapse.
- Pages and queries — top pages, top landing pages, top search queries, and the queries getting impressions with no clicks, which is the most actionable list on the sheet.
- Breakdowns — channel, source, country, device.
- Indexation — per-URL index status straight from the Search Console URL Inspection API, so "is this page even in Google" stops being a guess.
The detail that changes the numbers
Rates get computed over the whole period, not averaged across days.
It is tempting to take a click-through rate per day and average the column. That gives a quiet Sunday with nine impressions the same weight as a Tuesday with nine hundred, and the number it produces is wrong in a way that looks plausible. Total clicks divided by total impressions is the honest version. Average position gets weighted by impressions for the same reason.
Small decision, and it is the difference between a dashboard you trust and one you eventually stop believing.
The trap that cost us an hour
Google Apps Script projects run on a Cloud project that is created for you and hidden. That is fine until you call an API from it.
Calling the Search Console API returned a flat 403: the API has not been enabled on project number-you-have-never-seen. The error helpfully links to the Cloud console to enable it — and that project cannot be opened in the Cloud console, because it is not really yours.
Two fixes, depending on the API. For Google Analytics, use the built-in advanced services instead of hitting the REST endpoint yourself; enabling one in the manifest turns the API on for you. For Search Console, which has no advanced service, point the script at a standard Cloud project you control and enable the API there.
Worth writing down, because the error message sends you somewhere you cannot go.
The wider point
This took an afternoon, and it is not clever. It is a small, specific tool that removed a recurring annoyance, and it now runs every morning without anyone asking it to.
That is most of what useful automation looks like. Not a system that replaces your judgement — a system that has the numbers ready by the time you want them.
Agenloo builds this kind of thing for founders — the small systems that quietly remove the work you keep redoing. Explore our services or work with us.