Client: Demo, made-up shop
Why the best-selling product loses money online
A demo on made-up data. Sales were spread across three Excel files. Once I joined them into one report, it turned out that candles lost €10,448 in the online shop over a year.
Result
−€10,448
candle profit in the online shop over a year
Problem
Vaškas ir Molis is a made-up home goods shop selling candles, ceramics, linen, home fragrance, gift sets and kitchen items. It sells online and has one store in Kaunas, with about €412,000 in yearly sales excluding VAT. The data sat in three places: a webshop export with prices stored as text, dates in two formats and duplicate rows; a cash register log the shop assistant fills in by hand; and an Excel sheet with ad and shipping costs. The owner could see what was selling, but not what each product earned, because cost of goods, shipping and ads were never linked to sales.
What I built
I joined the three sources into one data model: order lines, a product list with cost prices, ad spend by campaign and shipping costs. I cleaned up the prices and dates and removed duplicates. Shipping, payment fees and ad spend were assigned to individual orders, so every product has its own profit after all direct costs. On top of that I built a Power BI report with three pages: overview, products, and ads and store. It can be filtered by channel, category and month, and when new data comes in, a refresh is all it takes.
Result
Candles looked like the best seller, since they were in 45% of online orders. Online they sold for €40,892. After cost of goods €26,080 was left. Shipping and payment fees took €8,929, and candle ads cost €27,599, more than half of the whole ad budget. The end result was a €10,448 loss over the year. 1,592 orders contained only candles, averaging €16.43, and the shop paid for free delivery on each one. The same candles made €12,483 in the physical store. What the owner can do next: offer free delivery only above a minimum order, cut candle ads online, and sell candles in bundles with other products. This is a demo, so there are no results from those changes.
Tell me where things get stuck
In 30 minutes we'll go through your situation. If I can't help, I'll say so.
Book a free callor email: rytis@numeva.lt