Streamlining Operations: A Case Study in Data Automation

The Challenge

A growing e-commerce and services business with 18 people had more data than they knew what to do with. They used Shopify for sales, a separate tool for customer service tickets, HubSpot for their B2B pipeline, Google Analytics for web traffic, and payroll in a standalone system.

Every Monday, the operations lead spent 3-4 hours pulling numbers from each platform. They copied the data into a master spreadsheet and formatted a report for the weekly management meeting. By the time it was ready, some of the data was already a day old. Decisions were being made on last week's numbers. Anomalies went unnoticed until they showed up in the monthly close. The person doing the reporting spent a quarter of their working week on a task that added no direct value to the business.

The question wasn't whether they needed better reporting. The question was why it hadn't been fixed already. The answer: nobody knew where to start.

The Approach

Week 1 - Audit and Architecture

We started by sitting with the operations lead for half a day. We went through every number that appeared in the weekly report. For each metric, we asked: who uses this? What decision does it inform? How often does it need to be current? That conversation cut the report from 34 metrics to 11 that actually drove decisions.

Next, we mapped the data sources. We identified which platforms had APIs, which had CSV exports, and which needed Make as a bridge. Shopify, HubSpot, and Google Analytics connected directly. The payroll system and customer service tool required automated CSV extracts on a schedule.

Week 2 - Data Pipeline and Warehouse

We built a lightweight data pipeline using Make. Each source pushed its key metrics into a central Google Sheet on a defined schedule. Shopify and Analytics updated hourly, HubSpot twice daily, and others nightly. The sheet acted as a simple data warehouse—clean, consistent, and always current.

Every metric was defined once and calculated the same way every time. No more discrepancies between what sales reported and what ops saw. We created one source of truth.

Week 3 - Dashboard Build

We built the management dashboard in Google Looker Studio, connected directly to the central sheet. The dashboard had four views: executive summary (the 11 core metrics, always current), sales pipeline (HubSpot deal stages and conversion), operations (order volume, fulfillment times, ticket resolution), and financial overview (revenue vs target, margin by channel).

Each view was designed to answer one question: is this area performing as expected? Green, amber, red. No digging required.

Week 4 - Alerts and Handover

We added a Make automation that checked key thresholds every morning. It sent a Slack message if anything was outside the expected range. This included a daily orders drop of more than 20%, a ticket backlog above a set threshold, or a pipeline stage conversion below target. We focused on proactive alerts instead of retrospective discovery.

The weekly report still existed, but now it took 10 minutes to review instead of 4 hours to produce. We ran both systems in parallel for two weeks, then retired the spreadsheet.

The Result

The immediate impact was time. The operations lead got back roughly 3 hours per week. Over a year, that's more than 150 hours returned to higher-value work.

The less obvious impact was the speed of decisions. Issues that previously surfaced in the monthly close were now visible within hours. In one instance, an unusual drop in conversion rate was spotted on a Tuesday morning. It was traced to a broken checkout flow before the end of the day. Previously, it would have been discovered three weeks later.

The management team stopped asking, "What are the numbers?" in meetings. They started asking, "What are we doing about it?" because the numbers were already visible before anyone sat down.

The data didn't change. The speed at which the team could act on it did.

Tools Used

• Make: Data pipeline automation and scheduled extracts

• Google Sheets: Central data warehouse and calculation layer

• Google Looker Studio: Dashboard and visualization

• Shopify API: E-commerce sales and order data

• HubSpot API: B2B pipeline and deal data

• Google Analytics 4: Web traffic and conversion data

• Slack: Automated threshold alerts

  
  

Timeline

The project took 4 weeks from audit to live dashboard, followed by 2 weeks of parallel running. We provided full handover, including documentation and a training session for the operations lead.

The improvements made in this case study highlight the importance of having a streamlined data process. By automating reporting and integrating systems, businesses can focus on what truly matters: making informed decisions and driving growth. If you're tired of drowning in manual work, consider how workflow automation can transform your operations.