Store Performance Scorecard 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 a 360-degree view of store performance, including various transaction counts, finance and operational dollars, discounts, counts by order fulfillment status, staff working hours and rates, sales area, comparative sales, sales plan numbers, etc.

It requires collecting data from various sources, verifying existing KPIs across the board, and dealing with missing or incomplete data. Consolidation of workflows was needed as the store scorecard logic runs on top of the core data load jobs such as transaction data, inventory data, store business days and square feet data, store hierarchy customer data, PeopleSoft, planning, and marketing data, etc. Seasonality, trends, holidays, and weather data influence retail performance. It is necessary to bring historical weather data from third-party APIs and integrate it to explain the fluctuations in the trends. Executives, analysts, and managers collaborated to agree on the KPI definition and update the Sharepoint document.

Techincal Solution

XTIVIA analyzed the data needs (attributes, format, frequency, transformation logic, etc.) and checked which attributes were already present in the Snowflake data warehouse and the raw layer. We profiled the data to find data quality issues and missing values. We worked with the source teams to replicate the data into the data lake using HVR.

Established a generic API workflow to pull and load the data into Snowflake based on the values we pass to the parameters, such as the application name, API endpoints, the keys, etc. It helped us load Weatherbit and ShopperTrak data. We designed a target data model for storing the Store Scorecard aggregates and developed data pipelines to load the target data model. We optimized the ETLs by revisiting the logic in the complex queries, changing to incremental loading wherever applicable, clustering the data, and using the materialization feature of Snowflake.

Multiple working sessions were arranged with executives and business analysts to record the changes in the definitions of the KPIs in case of the absence of data at the downstream systems.
We developed the data pipelines using a generic framework, making it flexible to add new KPIs. XTIVIA implemented a process to alert on the critical KPIs such as store traffic, transaction counts, and sales amount based on thresholds.

BUSINESS RESULT

XTIVIA’s solution resulted in the following benefits.

• Visibility into critical store KPIs in a single report (sales trends, top-selling products, and revenue growth opportunities)
• Integrating ShopperTrak data provided valuable insights (store operations, customer waiting times, etc.), helping Store Operations improve customer satisfaction.
• Improved accuracy of forecasts allowing the operations teams to allocate budgets and resources.
• Improved accuracy of inventory decisions and optimized inventory levels due to the visibility into store inventory data.

Key Words

ShopperTrak, Weatherbit, Snowflake, HVR, Oracle, SQL Server, Python, APIs, SFTP, Microstrategy, Tableau, UC4, Unix, AWS, 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.