Case study · Data engineering · APIs · Cloudflare D1
Seven sources,
one database.
Every marketing tool had its own dashboard and its own idea of the truth. I put them in one database the team can query in plain words. The hard part wasn't connecting them. It was proving the numbers were right.
- 7
- sources in one database
- 1
- wrong field name behind “0 rows” since day one
- 5/5
- Instagram metrics matching hand-kept numbers
Summary
Problem
Data spread across seven tools, and pipelines that reported “success” while writing nothing at all.
Solution
One pattern for every source (a small script fetches, a Worker validates, the database stores), each pipeline checked against data someone had kept by hand.
One pattern for every source
Search Console, keyword rankings, GA4, leads, coworking sign-ups, Instagram and Google Business Profile all follow the same path: a scheduled script fetches the data, a Cloudflare Worker checks a shared secret and writes it, and an upsert on a natural key makes re-runs safe. On top sit a read-only connector so the team can ask the data questions in plain language, and a semantic search over leads and queries.
Pipelines that said “success” and wrote nothing
- Google Business Profile: every run logged “0 rows” with HTTP 200. The first theory was data delay. A raw dump of the response ruled it out and showed the real cause: the parser read
datesAndValues, the API returnsdatedValues. One word, broken since the first run. - Instagram insights: the request asked for one response shape and parsed another, so it produced nothing, silently, every night.
- Instagram Stories: Meta was sending a webhook every time a Story expired, to a script with no code to receive it. Every Story since the subscription was lost. The script platform can't read request headers, so Meta's usual signature check was impossible; I used a shared secret in the callback URL instead, and tested both the reject path and the accept path.
- Lead attribution: the click ID was captured on the site but dropped in four places on its way to the database: form, sheet script, sync allowlist, and an
INSERThard-coded to seven columns.
Bugs only real data could show
- Timezones: the day window was anchored at UTC midnight while days were Madrid dates, so a run after midnight asked for a day that hadn't started.
- Spreadsheet dates: Sheets turned “2026-09-14” into a date in its own timezone, which slid a day when read back. The upsert key never matched, so every run added duplicates.
- Human notes: the nightly write would have blanked the team's own notes column. It now writes only its columns and never touches theirs.
- Renamed headers: renaming a sheet column created a new database column and froze the old one. I checked the data was fully duplicated, backed it up, then dropped four orphans.
Proof
The rebuilt Instagram history was compared with the numbers the team had been copying by hand: views, reach, interactions, profile views and new followers matched exactly on every day compared. Two differences, both explained: one figure noted before the day closed, and link clicks the manual tab had always recorded as zero.
Meta serves no history for total followers, only today's value. That column starts now and can't be backfilled. Rebuilding it from daily new followers would drift, because unfollows aren't exposed.
““It ran” is not the same as “it worked”. Compare it with something a person counted.”