D2C E-commerce Analytics — Shopify Store

Business Question

A direct-to-consumer store generates thousands of orders across states, couriers and payment modes — but raw order exports answer none of the questions that actually matter: where is revenue concentrated, which products carry the store, how much is lost to cancellations and RTO, and — most importantly — who comes back to buy again?

Data Sources

Order-level data from the Shopify store, including product, city, pincode, courier, payment mode and order status, combined with courier/freight information for delivery-behaviour analysis. Customer contact identifiers are used only to link repeat orders — and are masked in every reporting view.

KPIs

Sales are tracked month-to-date and against the prior month and year, alongside geographic spread (states, cities, pincodes), product concentration, average order value, prepaid-vs-COD mix, cancellation percentage, and courier-wise return rates. On the customer side, the suite tracks repeat-order counts per masked customer across financial years.

Dashboard Design

The suite is organised as a tabbed Power BI report: an overview page, product-wise and courier-wise views, freight-slab analysis, a sales register, and a dedicated repeat-customer page.

The views below are recreations with fully synthetic data — every value randomized and all customer identifiers masked.

Key Responsibilities

• Defining the reporting questions with the business team — concentration, repeat behaviour, delivery performance
• Designing and building the full Power BI suite across its tabbed views
• Modelling order-status logic (delivered, in transit, RTO, cancelled) consistently across pages
• Linking repeat orders per customer across financial years, with contact identifiers masked in all views
• Designing the RFM segmentation approach for customer-value analysis
• Maintaining the dashboard as order data grows

Technologies I Personally Used

Insights

The dashboard made concentration visible in both directions: a small set of cities and pincodes carrying a large share of sales, and a meaningful base of customers ordering repeatedly rather than once. It also separated genuine demand signals from noise — distinguishing cancellations and RTO patterns by courier and region rather than treating all non-delivery as one number.

Business Decisions

Repeat-customer visibility shifted the conversation from pure acquisition to retention — which customers to re-engage and which regions justified better courier arrangements. The RFM segmentation approach was designed to take this further, grouping customers into actionable segments (from champions to at-risk); the segmentation framework is designed and the chart above demonstrates the approach, with full implementation on live data as the next step.

Business Impact

Business impact is currently being quantified and will be updated as validated metrics become available. Qualitatively, the suite replaced manual order-export analysis with a single always-current view used for sales and operations review.

Lessons Learned

• Order-status definitions matter more than they look — "delivered", "RTO" and "cancelled" must reconcile exactly with the store's own numbers or trust evaporates
• Masking customer identifiers from the start avoids every privacy question later, at zero analytical cost
• Repeat behaviour is invisible in standard e-commerce reports until you deliberately build for it

Reflection

What I learned: e-commerce analytics is less about revenue charts and more about behaviour — repeat rates, delivery outcomes and payment-mode risk tell you what the business will look like next quarter.

What I would improve today: implement the RFM segmentation fully on live data and automate segment refresh, so retention campaigns can run off the dashboard directly.

Future enhancements: cohort retention curves by first-purchase month, courier SLA scoring, and automated alerts when RTO rates spike in a region.