Kirsten was the company's only analyst. She was skilled, thorough and extremely dedicated to her work. And she spent three days every month copying numbers from six different platforms into an Excel sheet.
The ritual always began on the first Tuesday of the month. Open Meta Business Suite, navigate to the reporting section, select the right dates, export to CSV, open the CSV, remove the irrelevant columns, copy the relevant numbers into the master sheet. Repeat for Google Ads. Repeat for Google Analytics. Repeat for LinkedIn Ads. Repeat for Mailchimp. Repeat for the company's internal CRM.
Three days. Every month. Twelve times a year. That's 36 working days a year — nearly two months — spent on manual data collection. And the report that emerged at the other end was outdated the moment it was shared: data from the first Tuesday didn't necessarily match the reality leadership faced when they read the report three days later.
The problem wasn't Kirsten. The problem was the systems. None of the six platforms naturally talked to each other. Data existed in silos. And the only bridge between them was a human with an Excel sheet and three days of patience.
We started the project with a thorough needs analysis. What does the report actually need to show? Who uses it? What decisions should it support? These are questions that seem obvious but are rarely asked explicitly. The answer was surprisingly clear: the report was used by three people — Kirsten, the marketing director and the CEO — and all three used it for quite different things.
Kirsten used it to diagnose performance problems across channels. The marketing director used it to justify budget allocation in quarterly meetings. The CEO used it to understand overall traffic and conversion trends. Three users, three needs — but one report trying to cover all three. The result was a document with 47 columns and 12 tabs, optimal for no one.
The solution design started from this: we wouldn't just automate the existing report. We would build the right report and automate that. It's an important distinction. Automating something dysfunctional just creates dysfunction faster.
The architecture we designed was built on Google Cloud Platform. Google Cloud Functions run on schedule every night at 2am and fetch data via APIs from all six platforms — Meta Marketing API, Google Ads API, Google Analytics Data API, LinkedIn Marketing API, Mailchimp API and the company's CRM via a REST endpoint. The data is transformed, normalised and written to BigQuery.
Looker Studio connects directly to BigQuery and presents three dedicated dashboards: one for Kirsten (with granular campaign data and anomaly detection), one for the marketing director (with channel ROI and budget performance) and one for the CEO (with topline metrics and trends over time). All three update automatically in real time. No report needs to be built manually.
Implementation took four weeks and hit one unexpected problem: the LinkedIn Marketing API returned data in a format that didn't match the overall data structure. We spent two extra days writing a custom transformer that normalised the LinkedIn data to the common schema. That's the kind of friction that always arises in data projects and that you can't predict precisely — but that you can build time for in planning.
The first month Kirsten didn't have to spend three days building the report, she used them instead on something she had never had time for: she built a model predicting which campaign types would perform best in the coming month, based on historical data. That was the analysis everyone had known would be valuable — but no one had had time to do.
The marketing director described the transition as "turning the lights on." Not because there was no data before — but because data was now available when it was relevant, not three days delayed and 47 columns wide. Decisions that previously took a week to prepare are now made in a meeting.
The real gain isn't the four minutes. It's what the three days are used for now — and what it gives an organisation when its analyst is actually analysing instead of copying data. That's the kind of ROI that's hard to put a number on, but easy to feel.