Client
A wholesale fuel supplier provides safe and environmentally responsible energy solutions that exceed expectations in both quality and value. The fuel supplier runs loyalty programs and customers earn points for every purchase from the fuel supplier. Points can be redeemed for rewards such as discounts on fuel, free car washes, and other perks. Customers can track their points balance and redeem rewards through the providers app or website.
Problem Statement
The customer was facing significant challenges with manual intervention, repetitive effort, particularly with the calculation and classification of discounts, fuel management calculations for a large volume of transactions from various card platforms. This manual process is not only time-consuming but also prone to human error, leading to potential inaccuracies in discount calculations. The complexity of managing data from multiple sources further exacerbates the problem, as it requires consistent validation and reconciliation efforts. Additionally, the customer lacks comprehensive visibility into their data, making it difficult to identify and rectify errors promptly. These challenges highlighted the need for an automated solution to streamline the process and ensure data accuracy.
Challenge
The challenge we encountered was determining the correct data and master data files to derive logic for data cleansing and transformation. It required picking key metrics and KPIs from large volumes of raw data received from multiple card platforms. It was essential to create a robust data cleansing process (concerning the format of the data) along with transformation processes to deal with anomalies in data like missing values and duplicates using Azure Data Factory. Integration of cleansed data from multiple source files into the MySQL database and other optimization tasks to facilitate real-time visualization in Power BI is also a checkpoint for problem-solving and wider technical knowledge.
Our Strategy
We have designed Azure Data Factory pipelines to process and transform raw data from card processors (from raw data to ready-to-use data). This included comprehensive data cleansing, deriving master data references & transformation logic to ensure the accuracy and consistency of processing data. We have considered MySQL database and tables which improves your query and retrieval. For the analytics report we have identified Power BI which grants interactive, real-time data visualization for a detailed analytics approach using the dashboards. We have planned thorough data testing with comparison of manually generated reports and the automated pipeline output.
Our Solution
We implemented an end-to-end solution with the use of Azure Data Factory to process and transform raw data from the Excel files into cleansed data. The solution represents the Azure Data Factory pipeline that reads the raw data from the storage container folder. Once the source excel files are uploaded, a trigger immediately begins the data processing. Then, the pipeline cleanses the data thereafter the data is transformed and loaded. The data processing involves various steps like merchant chain management, customer management, store type management and discount classification and calculations. Next, the processed data are stored in the MySQL database that is connected to Power BI allowing for visualization and specifying various points for real-time reporting. The final step in this data conversion pipeline is the final transformation using the stored views and tables, which deliver timely and accurately cleansed data to the Power BI dashboard through an automated process. The analytics dashboard provides easy access to data, spot trends of gallon sales, discounts, discounted transactions, non-discounted transactions, state wise potential gallons, ability to export data, analysis with respect to each merchant, merchant super chain, customer and card platforms. Provides accuracy and classification of discounts with a reference to the customer discount table provided by the customer. The analytics dashboard includes fuel management report for each customer for all merchant chains along with an option to view all transaction details.
Business Outcome
The following significant business impacts the implementation of the project has delivered:
- Easy access to data with various type of visualisation.
- Almost 100% reduction in manual effort with respect to calculations and reporting.
- Monitor gallon sales trends month on month
- Quick export of respective data from dashboard
- Identify inconsistencies of discount in the transactions resulting in better experience to customers.
- Highlight transactions of missing discounts as per discount eligibility.
- Classification of discount with respect to wholesaler and card platform.
- Enhanced Decision-Making: Stakeholders can make informed decisions using the dashboard, which displays clean and accurate data and reliable insights.
- Increased Efficiency: Data processing and cleaning have been automated, which frees the professionals from these mundane parts of work and saves their time and salary.
- Real-Time Data Access: Information available on Power BI is updated, and users can use it for analysis and reporting right away.
- Improved Data Quality: High integrity and reduction of human errors – all these are provided by robust data transformation processes and enable better use of data.
- Scalability: The solution is flexible, and it can be easily adjusted to a larger number of Excel files and the needs of the growing business.
- Client Satisfaction: The least possible required user input in the form of Excel files contributes to a more positive user experience and, potentially, satisfying clients.
Tech Stack
Azure Data Factory, Azure Blob Storage, MySQL Database, Power BI