Optimizing data extraction and reporting for a leading US energy drink brand

Seeking to streamline their data extraction techniques and reporting process and cut down the time it took to get actionable business insights, a US-based gaming and energy drink manufacturer was looking for a suitable solution provider.

The company was facing issues with outdated data, inconsistent KPIs and dimensions, and a fragmented, multi-platform codebase among a host of other challenges.

They approached Netscribes for a comprehensive and sustainable solution, to which we designed and developed a single source of truth of data through robust data extraction techniques. The entire ETL process along with a reporting process was fully automated with no manual intervention.

Objective

A US-based gaming and energy drink manufacturer wanted to optimize their entire data extraction & reporting process to be able to reduce the turnaround time from data to actionable business insights.

Business challenges

Overall BI & reporting time consumption
The current process of extracting, transforming, and loading the data from the client’s infrastructure to the Netscribes setup consumed around 5-6 days. The process also involved extracting data in batches at the start of every month – an overhead for the analytical team.

Stale data
Given the 5-6 days time of the data loading into the Netscribes environment, the data present for analysis had a lag of a week’s time which led to incorrect/partial representation in business performance numbers and the analysis was done based on stale data.

Unstandardized KPIs & dimensions
The reporting KPIs and dimensions were not uniform across data sources which led to mismatches in the transaction numbers.

Scattered and multi-platform codebase
Multiple codebases residing on multiple platforms (platform, MySQL, excel) led to a high amount of heuristic efforts for data analysis and reporting.

Performance challenges
This occurred due to huge amounts of data, data pulling, and querying for analysis.

Excel-based reports
All reports were sent as flat files at different points of time and hence, was difficult to manage.

Approach and solution

• Netscribes designed the architecture and created a real-time data warehouse on Databricks using 20+ data sources with 3 layers-raw, intermediate, and reporting. This led to structured and quick consumption of each of the client’s data sources for business insights
• The Databricks cloud infrastructure along with Python affording more flexibility increased process performance by 90%
• The overall BI & reporting process was streamlined and led to a 95% reduction in turnaround time of critical business reports
• The almost real-time data provided an accurate view of the business performance metrics and shorter delivery time due to cleansed and pre-processed data availability in the warehouse
• The KPIs and dimensions were standardized across all data layers reducing mismatch in the reporting numbers
• The entire report was transitioned from Excel to a more intelligently designed and easy-to-consume PowerBI dashboard

Results delivered

Through our collaboration, the firm witnessed:

• 95% reduction in turnaround time of the entire reporting process
• 100% improvement in the accuracy of business performance KPIs due to almost no lag in data from the client to the Netscribes infrastructure
• Faster data to insights cycle time with standardized data warehouse setup on Databricks cloud