TheLook eCommerce Analysis
An end-to-end SQL audit of a fictional online store: where revenue is growing, who comes back, which categories actually make money after returns, and whether the tracking data can be trusted.
TheLook is a fictional online fashion store, but the dataset behind it is large and realistic. I treated it like a real business and asked the questions a company actually cares about: is revenue growing, are customers coming back, which products make money after returns, and can we even trust what the tracking is telling us? The goal was a single end-to-end read of the store's health, backed by SQL I could defend line by line.
- Source
- bigquery-public-data.thelook_ecommerce
- Span
- 2019 to 2026
- Scope
- Completed orders
A public, multi-year synthetic dataset of a fictional fashion store: customers, orders, order items, products, and web events. I worked across these tables with joins and held every revenue query to the same completed-order scope so the figures reconcile across sections.
- 01
Frame the questions
Decided up front what a healthy store looks like: steady growth, repeat customers, profitable categories, and tracking data that holds together.
- 02
Model the revenue trend
Aggregated completed orders by month and computed month-over-month growth with the LAG() window function.
- 03
Profile customers and categories
Split new versus returning buyers using a percent-of-total window, then joined order items to products to rank categories by profit and by return rate with COUNTIF.
- 04
Audit the funnel
Counted unique users at each event stage to check the journey held together before trusting any of the downstream numbers.
- 05
Visualize in Looker Studio
Surfaced the headline trends in an interactive Looker Studio report so the findings are easy to explore.
Completed-order revenue by month, 2019 to 2026. Hover any point for that month's orders and average order value. The latest month is the dataset's in-progress period.
Growth is real, but only in the mature phase
Monthly revenue climbs from a few hundred rupees in early 2019 to ₹1.56L by mid-2026. The early months are noisy: an eye-catching +125% jump in April 2019 is really just 7 orders becoming 13. I read the trend from the mature period instead, where 2026 grows a steady 0.5% to 16% month over month, which is a far more reliable base for forecasting and stock planning.
Most customers buy once and leave
Of 27,470 customers with a completed order, 88% placed exactly one. Leaning that hard on first-time acquisition keeps customer-acquisition cost under constant pressure, so even a small lift in the repeat rate is worth real money.
Revenue and profit don't rank the same
Outerwear & Coats and Jeans lead on profit (₹1.84L and ₹1.46L all-time), but the more useful find was on the cost side. Clothing Sets are returned 13% of the time and Plus sizes 10.5%, well above everything else. That points at a sizing-and-fit problem, not a demand problem.
The funnel didn't add up, so I stopped and checked
More users 'purchased' (80,151) than ever visited the home page (63,206), and the cart and purchase counts matched exactly. In a real store that is impossible. It turned out to be an artifact of how this synthetic dataset generates events, but flagging it mattered more than glossing over it: numbers get validated before they get reported.
- Stand up a post-purchase email flow (a check-in plus a tailored recommendation within 30 days) to convert one-time buyers into repeat customers.
- Audit sizing on Clothing Sets and Plus ranges. Clearer size guides and real fit feedback on the product page should pull those return rates down.
- Plan off the mature-phase trend, not the sparse launch months, so forecasts aren't thrown off by early small-sample swings.
See the full dashboard
The headline trends are live in an interactive Looker Studio report you can explore yourself.
Open the Looker Studio dashboard