Client Introduction
A prominent creative marketing agency based in New York, USA, specializing in creative production, social media marketing, SEO, website development, and advertising, sought to enhance its operational efficiency with advanced data analytics solutions.
With a diverse portfolio of clients and multiple ongoing projects, the agency required a streamlined solution to analyze project performance, human resource efficiency, and financial tracking across various initiatives.
Business Problem
The agency relied on Teamwork to assign tasks and projects to its resources while tracking financial and operational data. However, due to a manual and time-consuming data extraction process, the CFO faced challenges in analyzing productivity and efficiency. The finance team had to manually transfer data from Teamwork to Google Sheets, making the workflow prone to errors, inefficiencies, and delays in decision-making.
As a Business intelligence service provider, Quilytics suggested developing an advanced data analytics solution- an automated data pipeline to seamlessly fetch data from Teamwork’s API and update Google Sheets using Google Cloud Platform (GCP) service keys. The project aimed to automate the extraction, transformation, and analysis of operational and financial data from Teamwork into Google Sheets.
Project Goals
Assess Current Setup and analyze existing Google Sheets to identify key metrics from Teamwork.
Review KPI formulas for project profit/loss, time utilization, and timeline.
How Quilytics Helped?
1) Assessment & Planning
- Conducted a detailed analysis of the client’s data requirements to provide visibility into key fields such as date of creation, project manager, status, and task breakdown.
- Identified the need for insights into estimated vs. actual time consumed, along with the billed and unbilled breakdown of projects.
- Reviewed Teamwork API documentation to determine relevant endpoints, including Project API, Task API, and Timelogs API for data extraction.
- Validated necessary data fields to align with business requirements, ensuring the extracted data could support time tracking at both task and project levels.
- Tested API endpoints to confirm their ability to provide the required data.
2) Data Integration
- Developed an automated pipeline to retrieve data from Teamwork’s API.
- Built a Python-based ETL (Extract, Transform, Load) process using libraries such as Pandas, NumPy, and Datetime for efficient data processing.
- Utilized Pandas to create and manage data frames, NumPy for calculations and handling missing values, and Datetime to standardize time functions.
- Implemented a workflow to load transformed data into Google Sheets, leveraging gspread and gspread_dataframe libraries for seamless data updates.
- Scheduled daily automation using Google Cloud Scheduler (cron jobs) to ensure up-to-date reporting.
- Applied data validation checks to maintain data integrity by matching data types of dimensions and metrics exported from Teamwork with those in Google Sheets.
3) Data Storage & Management
- Stored processed data in Google Sheets as per client preferences for easy accessibility.
- Scheduled the script to run twice daily using Google Cloud Scheduler and execute via Google Cloud Functions.
- Created a failure alert system on Google Cloud Scheduler to notify stakeholders if the script failed to execute.
- Initially tested and ran the script on a local PC, using a log file to track execution status and log errors before migrating to the cloud environment.
4) Data Reporting
- Integrated Google Sheets data into additional sheets for further formula-based calculations.
- Generated insights based on client-defined metrics, enabling data-driven decision-making and operational efficiency improvements.
Result
By implementing this automated pipeline, Quilytics transformed the client’s data management process with a flexible solution
This led to:
- Development of a future-proof system capable of adapting to the agency’s expanding data needs.
- Elimination of the manual data integration process of one hour daily
- Allowed the client to focus on analysis rather than data handling.
- Enabled real-time insights into project performance, resource utilization, and financial tracking.