Digital Marketing Data Pipeline for Apparel Retailer

Organization

The organization is a well-recognized retail apparel chain headquartered in Texas and operating 1,500+ stores in the US and Canada. The company has acquired many big market players as its subsidiaries. They offer men’s and women’s clothing, footwear, tuxedo rentals, and suit pressing with quality, fashion, and innovation as central parts of each product. The company has many warehouse facilities with a reliable supply chain mechanism. Globally, around 22,500 employees provide high-touch, high-quality shopping experiences across their brands.

Challenge

Our client wanted to streamline the process of sharing data with various digital marketing vendors such as Cheetah Digital, Adnet, Starcom, Critio, Rakuten, ShopperTrak, and RTB Creative stats and receive responses from vendors to understand the expense and lead generation of each campaign. Each vendor had different data requirements and format specifications. This required data to be extracted from multiple sources (sales, customer master, product data, campaign details, etc.)

In addition to the above requirement, our client needed to bring Google Analytics data into the digital marketing data mart. Google Analytics data was required to perform an in-depth analysis to understand the customer better, identify and fix the leakages in the customer journey, improve the customer experience, and understand how the marketing efforts affect the website traffic and apps performance. Executive leadership needed key metrics to be published for their information and decision-making.

Techincal Solution

XTIVIA analyzed the data needs (attributes, format, frequency, etc.), profiled the data to find data quality issues, and worked with the source teams to have as many issues fixed as possible at the source. We developed ETL jobs to ingest the data into the data lake. We had to negotiate with the vendors to get an agreement on the timing of their responses (enrich and send us back the data we sent them). The marketing tables were updated with this enriched data. We ensured that the same customer data was not sent to the vendors.

We defined the schema, configured the options to export data from the Google Analytics account, and placed the data in the GCP storage. We used the Snowflake stage to load the raw files from Google Analytics, performed flattening, applied transformations, and loaded the data mart (we used surrogate keys and reference keys for common dimensions such as customer, product, store, campaign, vendor, etc.). Each customer record in the data mart now has key metrics (top purchases, page visits, RFM, loyalty points, transaction count, total discount $).

The data pipelines (data going to the vendors and data received from the vendor) were developed using a generic framework making it flexible to add new vendors or discontinue existing ones. We implemented a process to notify the load stats and alert if the data was not sent or received as expected.

BUSINESS RESULT

• Adding new vendors and deleting existing ones is easier because of the automated, flexible, and generic pipeline.
• Informed decision-making due to the availability of KPIs for executives.
• Better understanding of customer behavior.

Key Words

Snowflake, HVR, SQL, Python, Google Analytics, Tableau, AWS, Unix, Windows

XTIVIA needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at anytime, read our Privacy Policy here.