Unified Data & Analytics for a 100+ Store Ethnic Wear Retailer

1. The client had a significant offline retail presence alongside a growing D2C business, but business and marketing data was distributed across multiple systems and platforms
2. 
   
3. The marketing team lacked a continuous analytics capability to deeply understand customer behaviour, marketing performance and business trends
4. 
   
5. Different stakeholders also needed different views of the business, making a unified and automated reporting framework important.
6. 
   
7. 
   
8. The objective was to create a reliable analytics foundation that could bring together online, offline, marketing, traffic and inventory data and turn it into actionable insights.

We designed and implemented an automated data and analytics ecosystem using QlikView, Python, APIs and SQL-based data pipelines

Data sources included:

• Magento — Order Management
• Unicommerce — Inventory Management
• Microsoft Dynamics AX — Point of Sale
• Meta Ads
• Google Ads
• Google Analytics

1. Unified data pipeline

A data pipeline was built from the Magento backend MS SQL database into QlikView to extract and process order-management data.

Python scripts were developed to retrieve Meta Ads and Google Ads data through their respective APIs, with the extracted data stored in Amazon S3.

Google Analytics data was accessed through QlikView's native connector to capture key website traffic and behavioural metrics.

QlikView's ETL capabilities were then used to transform and consolidate the datasets into QVD files, creating a structured and reusable data layer.

2. Business intelligence & reporting

A separate QlikView application acted as the consumption layer.

Dashboards were developed for different stakeholder groups, including:

• 
  

1. Marketing
2. CXO / Leadership
3. Procurement
4. Strategy

The reporting framework covered four major areas:

• Sales: Revenue, customers, ATV, ASP, ABS, frequency, latency and LTV
• Marketing: Impressions, clicks, conversions, spend, ROAS, CPC, CPM and CTR
• Traffic: Visitors, sessions, page views, bounce/exit rates, conversions and funnel performance
• Inventory: Stock on Hand (SOH) and Days Inventory Outstanding (DIO)

Recurring reports were also automated through QlikView, including customized email content and report snapshots for different stakeholders.

3. Advanced customer segmentation

Beyond conventional RFM segmentation, we developed a more granular segmentation approach using 10–15 behavioural and transactional variables.

A decision-tree-based approach was used to identify customer groups that were:

• Statistically meaningful
• Easy for business teams to understand
• Actionable for marketing
• Trackable over time

These segments could then be used for targeted marketing and customer analysis.

4. Test-Control framework

A permanent Test-Control framework was established to improve the measurement of marketing effectiveness.

The control group was deliberately excluded from targeting, allowing performance to be compared against targeted customers.

Because individual campaign-level attribution was limited by the available Magento and Google Analytics implementations, the analysis focused on segment-level performance and aggregate incremental outcomes rather than claiming individual customer-level campaign attribution.

This enabled the team to evaluate differences between Test and Control groups and use those observations to assess marketing ROI and customer LTV at a segment level.