Online Store Sales and Customer Retention Dashboard

The problem small online stores have with their numbers

Small online sellers, whether on their own Shopify or WooCommerce store, on a marketplace like Daraz or Amazon, or selling through Instagram and WhatsApp, usually know today's orders and not much else. They do not know which customers came back, which categories drive repeat buying, or whether last month's discount brought new customers or just gave money to existing ones. Monthly revenue goes up or down and nobody can say why.

Most analyst jobs involve some version of this report, which is why it is a better portfolio piece than a generic chart gallery. It forces you to define metrics, join several tables correctly and turn numbers into a recommendation.

What already exists and the gap

• Shopify Analytics and WooCommerce Analytics show sales and basic customer reports inside the store admin.
• Google Analytics tracks visits and conversions, but not repeat purchase behaviour from order data.
• Looker Studio and Metabase can connect to store data for custom dashboards, if someone builds them.
• Marketplace seller centres show orders and payouts, with little on retention.

Built-in reports rarely answer "did customers come back", and sellers who use several channels have no single view. The gap is a small, well-defined retention dashboard with metrics the owner understands, which is exactly what you practise here on public data before offering it to real sellers.

What you build

Use a public dataset with several related tables, such as the Brazilian e-commerce dataset published by Olist on Kaggle, with orders, items, customers, payments and reviews. Avoid single-table "superstore" files: joining tables and handling their keys is part of the exercise. Check the licence and cite the source.

1. Metric definitions first: what counts as revenue, an order, an active customer and a repeat customer, and which time zone a month uses.
2. Profiling: every table, with row counts, keys, nulls, duplicates and date range, and a log of what you clean.
3. SQL answers: monthly revenue, average order value, top categories and a monthly cohort table, before touching a chart tool.
4. Model: a fact table of order lines joined to customer, product and date dimensions.
5. Dashboard: at most six visuals and a KPI row.
6. Write-up: three findings, each with the chart that proves it and one action.

The technically interesting part

The cohort retention table is the heart of it. Group customers by the month of their first order, then count what share ordered again in each following month. It sounds simple and is easy to get wrong: customer IDs that change per order (a known quirk in some public datasets), cancelled orders counted as purchases, and time zones shifting orders across month boundaries. Show your supervisor how you found the right customer key, how you handled cancellations, and how the retention curve changes if you define "customer" differently.

How to evaluate it

• Reconciliation: one month's revenue and order count computed by hand match the dashboard.
• Traceability: a reviewer can pick any headline number and find the SQL that produced it.
• Reader test: 3 people who are not analysts, ideally one who runs a small shop, read the three findings in two minutes and say what they would do next.
• Performance: the dashboard loads in a few seconds on the full dataset.

Suggested plan

1. Week 1: definitions, profiling and cleaning log.
2. Week 2: SQL for the core questions and the cohort table.
3. Week 3: data model and dashboard.
4. Week 4: reconciliation, findings and write-up.
5. Week 5: reader test and revision.

To make it a final year project, connect it to a real store's exported orders (with permission), automate a monthly refresh, and add the RFM segmentation with suggested actions.

Path to a product

Once you can do this on public data, offer it to a real seller. Many small stores would pay a monthly fee for a retention dashboard that refreshes from their order exports, with a short monthly note on what changed. Your first customer might be a family business or a friend's online brand. Later, templates for Shopify, WooCommerce and marketplace exports make each new client faster.

Scope warning

Do not build a store, a recommendation engine or a customer lifetime value model in the first version. Use only public data for the portfolio version, and never publish a real client's data, even anonymised, without written permission.

Learn what you need on Sefism

• Data analytics roadmap : SQL, modelling and dashboard skills in the right order.
• Data analytics cheat sheet : joins, grouping and window functions for the cohort table.
• SQL Tutorial for Data Analysis : practical SQL for exactly this kind of analysis.
• Microsoft Learn: Power BI training : building the model and visuals in Power BI.
• Tableau Public : a free way to publish the dashboard.

Get help with this idea

Most of the marks here come from clear definitions and honest reconciliation. Tauseef can help you pick a dataset, define metrics a store owner would accept, and plan how to turn it into work for a real seller. If you are new here, create a free account first.

• Discuss this idea with a mentor
• Send a query to Tauseef
• Book a 1:1 session