A global cosmetics company wanted to consolidate customer purchase behavior information across in-store and online. This allowed the client to create more targeted marketing campaigns, increase average order value (AOV), perform basket analysis and cart abandons, more quickly pivot on products that were unsuccessful or invest in those that were.

The Problem

Our client was receiving flat files of information from their in-store data vendor and their website, which was consolidated into one database by a single outside contractor. There was no insight into how the various calculations took place, and the data was only available on a monthly basis. They needed a cleaner and quicker way of giving their business users access to the data directly so that they can make quicker and more informed marketing and product decisions.

Our Solution

Our team built a data hub using Azure’s various resources to allow the ingestion of data from multiple sources, which was ultimately integrated into one set of tables that allowed our client a single definition of a customer across all channels. The in-store data lived on a multi-tenant MySQL database, to which our team only had access via a secure virtual machine (VM). A custom application was built on this VM to create CSV files from the database, which were then stored in a mounted Azure File Store. From there, an Azure Webjob paired with Azure Data Factory ingested this data to the database. For the e-commerce data, there were a series of PSV files made available by their website vendor via an SFTP site, all of which contained various levels of data. These files were parsed as necessary to gain one level of data per table, and they were ingested into the database. Google Analytics data, available through the API, was extracted and loaded into the database via a tool called Fivetran. All of this data was then transformed and combined according to business logic in a separate layer that now contained a single point of reference for our client. Our team then added PowerBI on top of this solution to allow the business users quick and easy access to the data and powerful visuals. Our team also sent the consolidated and cleaned data to the client’s email marketing platform by sending files via an STFP site to be used in campaigns.

The Outcome

Our client was able to provide data to their business users and executive suite on a daily basis, which allowed their marketing and product teams to pivot strategy more frequently. The consolidation of data across in-store and online allowed their marketing teams to more easily target the same customer across channels with personalized campaigns that spoke to their individual purchasing habits.

Retail Sidebar
Featured Tech 8
Advanced Analytics Strategy Session