Unified Project Profitability for a Digital Agency

The Challenge

Despite being a data-driven agency for their clients, the company struggled with its own internal metrics. Project profitability was calculated manually once a quarter, often revealing losses only after projects were closed. Conflicting data between their CRM and time-tracking tools meant that no one trusted the reported margins.

The Approach

I stepped in as the A-to-Z specialist to bridge the gap between their disparate systems. I designed a modern data stack centered around Google BigQuery and Looker Data Studio. By prioritizing simplicity and maintainability, I ensured the organization could eventually manage the system independently.

The Solution

I built a centralized data repository where everything was written in SQL by design to keep the architecture transparent. I implemented the ETL process using scheduled queries within BigQuery, avoiding the complexity of external orchestration tools. The resulting suite of KPI dashboards in Looker Data Studio provided project managers with weekly profitability views.

The Results

For the first time in the company’s history, leadership had a clear, trustworthy view of their business:

  • Accuracy: First accurate margin analysis achieved by reconciling three years of legacy data.
  • Speed: Quarterly manual audits were replaced by automated weekly insights.
  • Independence: Extensive documentation and in-depth handover training ensured the team could maintain the system themselves.

Technical Stack & Methodology

  • Data Warehousing: Google BigQuery served as the high-performance, serverless data warehouse.
  • BI Visualization: Looker Data Studio was implemented for its user-friendly interface and seamless BigQuery integration.
  • ETL Strategy: Scheduled SQL queries were used to keep the transformation layer simple and easy to audit.
  • Knowledge Transfer: Delivered comprehensive documentation and training sessions to ensure long-term adoption.