16.14. BigQuery & Looker Studio Case Study: Labor Performance
The Client's Challenge
Our client, a call center, needed a central place to monitor the performance of their employees and agents taking calls. They used Harvest for time tracking and RingCentral for managing calls and voiceover IP.
Integrating Data Sources
To bring all the necessary data together, we enlisted the help of a developer to extract data from RingCentral and stream it into BigQuery tables. This process was done manually using development skills and APIs from Harvest.
Luckily, there was a data connector available through Stitch Data for Harvest integration. By subscribing to Stitch Data and connecting it to Harvest, we were able to extract daily data from employee timesheets and export it directly to BigQuery.
Combining Datasets in BigQuery
Now in BigQuery, we had three datasets: data from RingCentral, data from Harvest, and another dataset with revenue information related to calls made by agents. We combined these datasets to calculate key performance indicators like revenue generated per agent, payable hours worked, number of calls handled per day, billable versus non-billable calls, etc.
Analyzing Performance Metrics in Looker Studio
Using Looker Studio connected with BigQuery allowed us to create insightful visualizations of the labor performance metrics we calculated. Managers could easily see different views of time spent by employees on various activities like breaks, lunches, personal meetings or technical issues.
This level of detail provided managers with valuable insights into resource allocation and hiring decisions based on intraday call volumes during different days of the week or hours within a day.
Delivering Value through Data-driven Decisions
The integration between BigQuery and Looker Studio brought tremendous value to our client by providing them with actionable insights that they couldn't get anywhere else. This helped them optimize their labor force—scaling up or down as needed—and cut down costs by gaining visibility into their operations.