Online Retail Scorecard (FMCG)

Situation: Our client was receiving weekly online sales data from the major UK supermarket chains via 5 different providers, with data on over 8000 products. They needed to report on this data to show manufacturer, brand, product and SKU performance in respect to KPIs utilising differing time periods as well as against key competitors. The data received was not only in quite varied formats, but also with no standardised SKU definitions or UIDs. There were often multiple text descriptors of SKU within a single data-set (up to 18 different descriptions of the same product!), resulting in data that was very hard to organise & run analysis on.

Solution: Working with the client team we initially worked through a snap-shot of the data to classify and group the multitude of products and standardise the different data-sources so that a proof-of-concept interactive scorecard could be built and shared with internal stakeholders. After the success of this stage we then moved on to develop an extensive database utilising 4 years of back-data, standardising different product descriptors into amalgamated SKUs and applying the classification framework we’d developed during the POC phases. In tandem with the database build, we developed import scripts using Java data-frames and a bespoke .NET app which identifies key elements of the product text descriptions to enable automated product matching. The app also incorporated a GUI allowing users to check and manage product matching, as well as import history – including the ability to roll-back imports or subsets of data and recording an audit trail of changes.

We liaised further with the immediate client team and their stakeholders, developing the POC dashboard so that specific analytical questions could be addressed as well as the original scorecard reporting. This incorporated dynamic comparison of time periods such as last 4 weeks, last 12 weeks, YTD and MAT as well as variable time periods against equivalent periods from previous years and retailer, manufacturer, brand and product performance tracking. We built additional data integrity reports for the core team and responded to ad-hoc requests and data mining as they occurred.

Outcome:Enabled analysis that previously wasn’t possible due to time-constraints of cleaning up the data, becoming an established part of the company’s reporting suite. This project was used by the client as an example of best practice, culminating in us being invited to be involved in an official company-wide guidebook project for visual analytics standards. This is an ongoing project, having been continually used over the last 7 years with all data updates and reporting supplied as a managed service.

More Info: if you would like to learn more about this, or how we could help you with a similar project please contact us