Red Pill Analytics helps client pull data from multiple sources for easier and more thorough reporting

Our client, a Government Administration and Construction Company, a fast-growing organization, needed a timely means of collecting, organizing, and reporting data for their Office of Finance and Operations teams. This customer was spending countless hours pulling together information from multiple departments, users, and datasets into complex excel workbooks and still making decisions on gut-feel. Their data was available but needed to be curated, managed, and delivered into a dashboard that was accessible to both executives and higher-level analysts. Red Pill was engaged to build a cloud-friendly and scalable process using modern technologies to address their critical business challenges.

Dashboard Delivery Demands

The Client’s Finance and Operations teams needed a dashboard to integrate 12 core KPI metrics to display company health to business stakeholders. They lacked an aggregated view of key metrics across multiple lines of Business and needed this data to be combined into one location. The Company’s acquisitions had to be completed within 30 days of the close of the transaction, so the dashboard had to deliver insight that enabled them to react quickly to integrating and measuring performance.

Both teams had specific needs that needed to be met. The Finance Team needed a way to compare Inspection Services revenue to the Cost of Goods Sold (COGS) to determine which services needed to be restructured or removed. Operations wanted to see how the number of inspections performed on a daily basis impacted inspector utilization. In addition to these needs, HR wanted a better way to communicate turnover rates by the office with regard to the number of employees per office and utilization rate. This way they could correlate contract renewal rates with average contract value to determine an optimal renewal rate percentage.

Modern Cloud-First Technology

First, the Client was looking for recommendations on the best cloud providers and options that would decide the best path forward and meet their team needs. Red Pill began by building a data lake and then building models in Looker to have a common business/metadata mode. The final solution leveraged a modern set of cloud-first technology to pull data from over a dozen systems to present data in Looker and was based upon two driving factors: no fixed infrastructure costs, and it had to be easily used by end users, power users, and executives. A unified data model was built to organize the data in Snowflake and Looker. Looker’s drag-and-drop functionality made it easy to get at that data, but most importantly, had a level of complexity that an analyst or engineer could use to get the specificity they needed. The standard metadata model allowed consistency across metrics by leveraging LookML Models & Views. Overall, the project was completed in less than four months.

One More Thing

For the first time, the Customer was able to look at departmental time reporting and revenue generation on a single report on a real-time basis without manual intervention. This meant new insight, and ultimately, better business decisions.

WHAT DO THEY THINK?

“Our team has freed up headcount to devote to M&A related activities and not on cumbersome manual reporting” – CFO

THE GOALS

  • Create a dashboard to integrate 12 core KPI metrics to display company health to business stakeholders
  • Give insight to multiple teams by combining data into one location
  • Measure performance and inspect services comparison for the Finance team
  • Inspect utilization benefits, turnover rate, and renewal rate percentage for the Operations Team
  • Provide HR with a turnover rate review

THE APPROACH

  • Leverage cloud-first technology to pull data from over a dozen systems
  • Build a unified data model to organize the data in Snowflake and Looker]
  • Build a data lake and then build models in Looker to have a common business/metadata model
  • Six short sprints to establish the foundation and the hiring of support by the customer to continue the solution

THE RESULTS

  • Modern cloud-first technology
  • A tool that is easy for executives and end users but provides enough data exploration capabilities for analysts and data engineers
  • Looker’s Drag-and-Drop
  • Dashboards that are consumed by multiple user types that aggregate data across multiple sources

THE PRODUCTS