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.
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.
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.
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.