Customer Profile
A multinational bakery product manufacturing company that sells its products to clients through a network of different agencies.
Customer Situation
- Customer had months of unstructured sales data, but without the required insights from the data
- Customer required a solution that will give them critical insights on the sales performance by geography, agency, channel, product and other dimensions
- The solution should help the customer forecast the inventory demand for effective planning
Congruent's Solution
- Data Extraction - Extracted the data from Excel sheets and stored it into SQL database using SSIS
- Data Profiling - Used SQL scripts, Text analytic tool (Voyant) and VBA scripts for data profiling. Profiling included identifying word frequency, word collocation, translating words from Spanish to English, extracting longitudinal / latitude coordinates of Agencies and identifying channel & client attributes
- Data cleansing - Profiled data was scrutinized closely for data quality. All blank and duplicate records were removed from data. All irrelevant or orphaned records were removed from the database
- Identified 128 attributes of the product
- Developed data dimensional model (OLAP) using SSAS
- Integrated the data source to Power BI to create KPIs and measures. Reports were created using tableau to analyze and gain insights on sales figures, quantity, returns based on geographies, agencies, channels, time period (week wise) across different product categories
- Created various intuitive, interactive and customized visualization reports
- Predictive Analytics - Congruent employed R language to perform statistical computing / forecasting
- Used linear regression model to forecast the sales value and inventory demand for the subsequent months
- Integrated R language with power BI and Tableau for creating reports and dashboards
Benefits
- Turned unstructured data into beautiful visual reports that gave deeper insights on their key performance areas
- Reports helped the customer analyze their sales figures, quantity and returns against each geography, agency, channel, time period (week wise) across different product categories, helping them make informed decisions for the future
- With the help of predictive analysis the company was able to forecast the sales figures and inventory demand for subsequent months
Congruent's Key Challenges
- The unstructured data comprised 90 million sales transaction records of different agencies in Excel sheets
- Transforming the raw data into a structured form, so that it results in efficient data analysis & design
- Data originally consisted of Spanish words, which required translation into English for analysis
Know more about our Data Analytics Consulting Services