E-commerce revenue and customer performance

Wadi Market is a fictional online retailer in Riyadh selling electronics, home goods, fashion and gourmet food through its website, its mobile app and a third-party marketplace. Order volume has grown every year since 2023, and the founders are preparing for a funding conversation in which investors will ask the classic questions: how fast is revenue really growing, how much does a typical basket bring in, which channel is doing the work, how dependent is the business on a handful of big customers, do customers come back, and how much revenue leaks out through returns and cancellations.

Until now every team has quoted its own numbers. Marketing counts every order placed, finance counts only money captured, and operations counts parcels. Nobody has written down a definition of revenue, and a well-meaning analyst once reported revenue that was 40% too high because the order lines were joined to the payments table and every split payment doubled the order. The data itself is honest but full of real-world traps: orders with two or three payment rows, cancelled orders that were refunded, partial returns whose refund is still being processed, business customers who buy in bulk, and a category tree three levels deep.

Your job is to build one consistent, defensible set of numbers from the raw tables, check that they tie out, and hand leadership a summary they can trust.

From: Noura Al-Harbi, Head of Strategy & Finance

“Before the investor meeting I need one version of the truth. Please (1) check the data and show me how order value ties to payments, (2) give me the monthly revenue trend with year-on-year growth, (3) break revenue and average order value down by channel, (4) show me what returns cost us by department and how often orders are cancelled, (5) tell me who our top customers are and how concentrated revenue is, (6) measure how many customers come back for a second order, and (7) finish with a one-row-per-year executive summary.

Use this definition everywhere: order value = sum of quantity * unit_price - discount_amount over the order's lines, plus shipping_fee. Revenue = order value of orders whose status is paid, shipped or delivered (cancelled, pending and fully returned orders are not revenue). Net revenue = revenue minus processed refunds (returns.refund_amount that is not NULL) on those orders.”