R872K in Orders, R448K in Revenue: A Web and E-commerce Analytics Case Study for a Skincare Brand
| Type | Self-directed portfolio project |
|---|---|
| Scenario | Kasi Botanicals, a hypothetical natural skincare brand in South Africa that sells through a Shopify style store and advertises on Google and Meta |
| Data | 2,442 order lines (1,450 orders), 22,970 website sessions, 920 daily ad rows across 5 campaigns and 475 customer records, March to August 2026 |
| Tools | Python (Pandas) · Looker Studio · Google Ads and Meta Ads data structures |
| Focus | E-commerce analytics, funnel analysis, marketing efficiency |
| Outcome | 5-page Looker Studio dashboard and a one-page findings report |
The Brief
Most online store owners watch three numbers: revenue, ad spend and traffic. Those numbers rarely answer the questions that decide whether the business grows. Which ads actually make sales? Where do shoppers give up? And how much of the revenue on the dashboard is real?
To build around those questions, I modelled Kasi Botanicals, a hypothetical natural skincare brand. The data is synthetic, built to behave like real exports.
The goal: clean four linked data sources, find what is working and what is leaking, and deliver a dashboard and a one-page report a store owner could act on.
The Data
Four files that mirror what a real store would export:
- Orders: 2,442 line items across 1,450 orders
- Sessions: 22,970 website visits, each flagged for viewed product, added to cart, began checkout and purchased
- Ad spend: 920 daily rows across five campaigns on Google Ads and Meta Ads
- Customers: 475 customer records
As in real life, the files were messy and they did not agree with each other.
The Cleaning Process, and Why It Mattered
This is the part I think matters most, because it is invisible in a finished dashboard.
1. Line items are not orders
The orders file has one row per product, so 2,442 rows is really 1,450 orders. Counting rows would have overstated orders by 68%.
2. Shipping was repeated on every line
Adding it up gave R119,825. Counted once per order, it is R71,371.
3. A blank is not a customer
About 17% of orders were guest checkouts with no customer ID, worth R145,818. I labelled them Unknown instead of guessing, which means repeat purchase and lifetime value cannot be measured reliably for them. I also kept customer counts at the record level in this version. Some people appear twice under slightly different emails, so the count of 475 is a little overstated.
4. Averaging rates gives the wrong answer
Click through rate averaged across daily rows looked like 12.5%. Clicks divided by impressions gives 2.84%. Every rate in the dashboard is calculated from totals.
5. Two systems, two answers
Website analytics recorded 1,198 purchases. The store recorded 1,450 orders.
The Findings
Only half the order value was real revenue. Just 741 of 1,450 orders (51%) were completed. Refunds and cancellations alone wiped out about a third of order value, and the average completed order was R605.
The cheapest purchase cost R13.59. The most expensive cost R239.08. That is a gap of nearly 18 times across five campaigns. Facebook retargeting took 9% of the budget and produced 57% of the purchases linked to ads. It flatters the numbers because it targets people already close to buying, so it will not scale the way other campaigns can. Among the colder campaigns, Google Shopping cost about half as much per purchase as Google Search or Facebook prospecting.
The funnel leaks after the cart. Of 22,970 sessions, 12,463 viewed a product, 3,857 added to cart, 1,883 began checkout and 1,198 purchased. Cart abandonment was 69%. Only 49% of carts reached checkout, and a further 36% of checkouts were abandoned. Every point of cart abandonment recovered is worth roughly 39 purchases, or about R23,000 at the average order value.
Device was not the problem. Mobile brings just over half of all traffic, but conversion is almost identical across devices: 5.1% on mobile, 5.3% on desktop and 5.6% on tablet. Ruling out a suspect is a finding too. It saves a client from paying for a mobile redesign that would not fix the real leak.
Email is the quiet winner. Email brought 4.6% of sessions but converted at 25.7% and delivered 22.8% of all purchases, with no ad spend attached.
Three gift sets did a third of the work. Three of the 17 products generated R297,103, or 34% of order value. The best seller by units, a body care product, brought in about R181 a unit. The two biggest gift sets earn between R634 and R761 a unit. Volume and value tell different stories.
The Recommendations
- Move budget in steps from the two most expensive prospecting campaigns (Google Search and Facebook prospecting, both above R230 per purchase) toward Google Shopping, and watch cost per purchase as you go. Keep retargeting funded, but do not expect it to scale.
- Test the step between cart and checkout. Show delivery cost and date earlier, and add express payment options such as PayFast.
- Investigate refunds and cancellations before buying more traffic. Start with cash on delivery, which completes least often (44% against 48% to 55% for other payment methods).
- Protect and grow the email list. It is the cheapest high converting channel the store has.
The Deliverables
| Deliverable | What it is |
|---|---|
| 5-page Looker Studio dashboard | Overview, Sessions and funnel, Ad spend, Customers and acquisition, and Products and orders. Built with native calculated fields and data blending, with no scripting or add-ons beyond what a free Looker Studio account supports. |
| One-page findings report | Written for a store owner, not a fellow analyst: plain language, prioritised actions and an honest note on the limits of the data. |
What This Project Demonstrates
- Cleaning and reconciling multi-source e-commerce data: order versus line item grain, guest checkouts and systems that disagree
- Calculating rates correctly from totals instead of averaging averages
- Funnel analysis and ad efficiency analysis that end in specific, testable actions
- Ruling out explanations as well as finding them
- Telling a business owner which numbers to trust, and which to question
What This Means for Your Business
If you run an online store that already gets traffic and spends on ads, but you cannot yet say which of it is paying off, this is the analytics layer I build: clean source data, honest attribution, and a dashboard your team can open without me.