Retail Client

Understanding Omni-Channel Customer Behavior with Data.

An Overview

The Challenge

A retail client was receiving once-monthly flat files of information from their in-store and website data vendor, which lacked insight and didn’t give business users direct access to the data.

The Solution

2nd Watch leveraged Azure to create a data hub that allowed ingestion of data from multiple sources into one set of tables. We also added Power BI to this solution to provide quick access to the data and powerful visuals.

The Outcome

The solution allowed our client to provide data to business users on a daily basis, which helped them pivot their marketing strategy more frequently. With their understanding of omni-channel customer behavior, they could better target customers across all channels with personalized campaigns.


The Challenge

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 they could make quicker and more informed marketing and product decisions.


The 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 gave 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 was a series of PSV files made available by our client’s 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 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 Power BI 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 SFTP 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 in-store and online data enabled their marketing team to more easily target the same customer based on omni-channel customer behavior, using personalized campaigns that spoke to their individual purchasing habits.