Client Hours & Revenue Data Dashboard

Overview: The Client Hours & Revenue Data Dashboard was developed to empower Ziggy's senior leadership with clear insights into the time spent on each client across weekly, monthly, and quarterly views. Prior to the dashboard’s implementation, the team lacked visibility into time allocation, making it difficult to determine whether clients were being over-serviced or under-serviced relative to their contracted hours. The primary goal of the dashboard was to provide a comprehensive, real-time solution that connected data from the project management tool, Asana, with billable hours, ensuring precise and actionable time management.

Objective: The main objective was to create a live, interactive dashboard that merged Asana’s task and time tracking data with Ziggy’s allocated and billable hours for each client. This integration allowed the team to continuously monitor and compare actual time spent against contracted hours, making it possible to adjust resources proactively. The key performance indicators (KPIs) focused on the accurate tracking of hours per client and alignment with billing targets.

Role & Responsibilities: As the sole architect of this project, I was responsible for every aspect of the dashboard's development. My tasks included planning the structure and layout, setting up data exports, and designing the data visualisations. I managed the end-to-end process from conceptualization to execution, ensuring the final product was both functional and user-friendly for the account directors and managers.

Approach & Tools Used: To build the dashboard, I first aggregated data from Asana, which involved exporting task and time logs. This data was then combined with Ziggy’s internal targets for billable hours. Using Google Sheets, I created an aggregate table that served as the source for the dashboard in Looker Studio. Formulas and lookups were utilized to ensure that the data was accurate and updated in real-time, providing a seamless flow of information from the initial data sources to the visual outputs.

Challenges & Solutions: One of the major challenges was managing the volume of data exported from Asana, which frequently overloaded the system. To overcome this, I segmented data exports by client, using Google Sheets functions like =IMPORTRANGE and =FILTER to consolidate the data into a single, comprehensive table. This approach allowed for efficient data handling and ensured that the dashboard remained functional and responsive.

Results & Impact: The dashboard quickly became a critical tool for Ziggy’s account directors and managers. It featured over six pages of detailed data visualizations, including graphs and tables that allowed the team to track hours spent per client and adjust strategies accordingly. One of the standout features was a monetary value graph, which translated hours worked into billable amounts, providing a clear financial perspective on resource allocation. This enabled the team to make strategic decisions that optimized client servicing and enhanced overall performance.

Key Learnings & Reflections: This project significantly enhanced my skills in data integration and dashboard design, particularly in using advanced formulas and visualization techniques to create dynamic, user-friendly tools. A key takeaway was the importance of flexible data structures that accommodate real-time updates and the need for streamlined data flows to maintain dashboard efficiency. Looking back, optimizing the initial data extraction process earlier could have saved time and further improved the dashboard’s responsiveness.