Revenue Grew 15% Last Quarter: Was It Volume, Price, or Product Mix?

Imagine a message lands in your inbox: ““Revenue analysis SQL can tell you whether a 15% revenue increase came from more orders, higher prices, or product mix.”

I wanted to understand how an analyst should respond to that, because “revenue is up” is a headline, not an explanation. The same 15% can come from very different places, and each one leads to a different decision.

So this post content is my practical revenue analysis in SQL. We will take a real, public e-commerce dataset, the Brazilian Olist dataset from Kaggle, and break one revenue number into its parts using price volume mix analysis and a revenue bridge. By the end you will be able to answer three questions:

1. Did growth come from more orders, or bigger orders?
2. Within that, was it volume, price, or product mix?
3. Is this growth something worth spending more money on?

A note on the data and the scenario. The data is real: it is the public Olist Brazilian e-commerce dataset (orders from September 2016 to October 2018), and every number and output below comes from queries I ran on it. The scenario (a manager asking whether to double ad spend) is hypothetical, and I am not claiming it reflects any real decision at Olist. All amounts are in Brazilian reais (R$).

Why I’m writing this

I’m a beginner data aspirant myself, and I’m learning in public. Around me there are many others who are learning and exploring data, and one gap I keep noticing is business understanding. Many of us get comfortable with SQL syntax, Excel formulas, or Power BI visuals, but we’re rarely shown the business question behind the query: why does this number matter, and what decision could it change?

That gap is what I want to work on, for myself and for anyone else who is building analytical skills. So instead of only teaching a function, I try to start from a realistic business problem and then work out the data thinking step by step. This article is one example of that approach. If you’re at a similar stage, I hope it helps you see the why along with the how.

Revenue Analysis SQL: The Business Problem

Here is why the question matters. Three different companies could all report “+15% revenue”:

• Company A got more customers at the same prices. That is healthy, and more ad spend might make sense.
• Company B raised prices and sold about the same units. That is healthy if customers stay, but ads will not help much.
• Company C cut prices on a few products and sold many more of them. Revenue looks great, but profit may not have moved.

The decision at stake is where the next unit of ad budget and effort should go. To make that call, we need to split the change into its causes.

The KPIs we will use

Before writing any SQL, define the numbers. This is the step people skip, and it changes the answer.

Revenue definition used here: the sum of item price on delivered orders, by order purchase date. This excludes freight (shipping) and excludes orders that were cancelled, unavailable, or still in progress. There are no refunds or discounts in this dataset, so we cannot model them.

Olist has no quantity column: each row in order_items is one unit, so “items” and “units” mean the same thing here.

And the relationships we will lean on:

• Revenue = Orders × AOV
• AOV = Items per order × Average item price

The dataset: what is inside Olist

The Olist dataset is a set of related tables about orders placed on a Brazilian marketplace, with customers, sellers, products, and payments. Here is what I loaded, with the row counts I saw:

How they connect: a customer ID links to an order, an order links to its items and its payments, an item links to a product and a seller, and a product links to a category translation. The grain of our analysis, meaning what one row represents, is one item in an order. This is meaning is important where most beginners stuck during an interview.

A few things I checked before analysing anything:

• Order status: 96,478 of 99,441 orders (97.0%) are delivered. The rest are shipped, canceled, unavailable, invoiced, processing, created, or approved. We keep delivered orders only.
• Date range: orders run from 2016-09-04 to 2018-10-17. But 2016 has very few orders, and the last months of 2018 are mostly missing, so I only compare full quarters from 2017-Q1 to 2018-Q2.
• Missing categories: 610 products have no category name. I label them unknown instead of dropping them.
• Untranslated categories: two categories have no English translation, so I fall back to the Portuguese name.

Setting up a reusable view

Instead of repeating joins in every query, I put them in one view. This is a habit worth building: write the join logic once, test it, and let every query reuse it.

CREATE VIEW delivered_items AS
SELECT o.order_id,
       c.customer_unique_id,
       c.customer_state,
       o.order_purchase_timestamp AS purchased_at,
       oi.seller_id,
       oi.price,
       oi.freight_value,
       COALESCE(t.product_category_name_english,
                NULLIF(p.product_category_name, ''),
                'unknown') AS category,
       CASE WHEN o.order_purchase_timestamp >= '2017-01-01' AND o.order_purchase_timestamp < '2017-04-01' THEN '2017-Q1'
            WHEN o.order_purchase_timestamp >= '2017-04-01' AND o.order_purchase_timestamp < '2017-07-01' THEN '2017-Q2'
            WHEN o.order_purchase_timestamp >= '2017-07-01' AND o.order_purchase_timestamp < '2017-10-01' THEN '2017-Q3'
            WHEN o.order_purchase_timestamp >= '2017-10-01' AND o.order_purchase_timestamp < '2018-01-01' THEN '2017-Q4'
            WHEN o.order_purchase_timestamp >= '2018-01-01' AND o.order_purchase_timestamp < '2018-04-01' THEN '2018-Q1'
            WHEN o.order_purchase_timestamp >= '2018-04-01' AND o.order_purchase_timestamp < '2018-07-01' THEN '2018-Q2'
       END AS quarter
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
LEFT JOIN product_category_name_translation t
       ON t.product_category_name = p.product_category_name
WHERE o.order_status = 'delivered';

Notes on the choices:

• LEFT JOIN on the translation table keeps items whose category has no translation.
• Quarters are defined with explicit date ranges (>= start and < next start), which avoids mistakes with times on the last day of a quarter and works in both MySQL and SQLite.
• I ran everything in SQL with numeric columns for prices and payments. The syntax used (CTEs, CASE, window functions) is standard and should run on MySQL 8 as well, but test it on your setup.

A sample of delivered_items (first rows of Q1 2018):

Rows 2 and 3 are the same order and the same product: two units of one item, shown as two rows. That is why we count distinct order_id when we count orders, and count rows when we count items. Mixing those two up is a common way to get wrong AOV numbers.

Step 1: The baseline

Question: What are orders, revenue, AOV, items per order, and average item price in each full quarter?

What the query does: It groups the view by quarter and computes each KPI. It answers “what happened?” before we ask “why?”.

SELECT quarter,
       COUNT(DISTINCT order_id)                              AS orders,
       COUNT(*)                                              AS items,
       ROUND(SUM(price))                                     AS revenue,
       ROUND(SUM(price) / COUNT(DISTINCT order_id), 1)       AS aov,
       ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT order_id), 2)   AS items_per_order,
       ROUND(SUM(price) / COUNT(*), 1)                       AS avg_item_price
FROM delivered_items
WHERE quarter IS NOT NULL
GROUP BY quarter
ORDER BY quarter;

Output:

What I noticed: Revenue went from R$2,362,046 in Q4 2017 to R$2,704,438 in Q1 2018, which is +14.5%, our “about 15%”. That is the pair of quarters we will explain. Look at the pattern behind it: orders rose by 19.4% (17,280 to 20,627), but AOV fell from R$136.7 to R$131.1. Revenue grew slower than orders, so something is pulling the value of each order down.

Also notice how big the whole trend is: Q1 2017 revenue was only R$705,221. We will come back to this in the seasonality check.

Step 2: More orders, or bigger orders?

The idea: Since Revenue = Orders × AOV, we can split the change into two parts.

• Orders effect = (Orders₁ − Orders₀) × AOV₀. What the extra orders would have added at last quarter’s basket value.
• AOV effect = (AOV₁ − AOV₀) × Orders₁. What the change in basket value did across this quarter’s orders.

These two add up exactly to the total change. We can also split the AOV effect further, because AOV = Items per order × Average item price:

• Basket size effect: more or fewer items per order.
• Price per item effect: higher or lower price per item.

WITH agg AS (
  SELECT quarter, COUNT(DISTINCT order_id) AS orders, COUNT(*) AS items, SUM(price) AS revenue
  FROM delivered_items
  WHERE quarter IN ('2017-Q4', '2018-Q1')
  GROUP BY quarter
),
pv AS (
  SELECT
    MAX(CASE WHEN quarter = '2017-Q4' THEN orders END)                 AS o0,
    MAX(CASE WHEN quarter = '2018-Q1' THEN orders END)                 AS o1,
    MAX(CASE WHEN quarter = '2017-Q4' THEN revenue * 1.0 / orders END) AS aov0,
    MAX(CASE WHEN quarter = '2018-Q1' THEN revenue * 1.0 / orders END) AS aov1,
    MAX(CASE WHEN quarter = '2017-Q4' THEN items * 1.0 / orders END)   AS ipo0,
    MAX(CASE WHEN quarter = '2018-Q1' THEN items * 1.0 / orders END)   AS ipo1,
    MAX(CASE WHEN quarter = '2017-Q4' THEN revenue * 1.0 / items END)  AS p0,
    MAX(CASE WHEN quarter = '2018-Q1' THEN revenue * 1.0 / items END)  AS p1,
    MAX(CASE WHEN quarter = '2017-Q4' THEN revenue END)                AS r0,
    MAX(CASE WHEN quarter = '2018-Q1' THEN revenue END)                AS r1
  FROM agg
)
SELECT ROUND(r0) AS rev_2017_q4, ROUND(r1) AS rev_2018_q1, ROUND(r1 - r0) AS total_change,
       ROUND((r1 - r0) * 100.0 / r0, 1)  AS pct_change,
       ROUND((o1 - o0) * aov0)           AS orders_effect,
       ROUND((aov1 - aov0) * o1)         AS aov_effect,
       ROUND((ipo1 - ipo0) * p0 * o1)    AS basket_size_effect,
       ROUND((p1 - p0) * ipo1 * o1)      AS price_per_item_effect
FROM pv;

Output:

Check: 457,510 − 115,118 = 342,392, and −18,280 − 96,837 = −115,117 (the R$1 difference is rounding).

What this means: More orders added about R$457,500. Lower basket value took back about R$115,100. Within that drop, only R$18,280 came from smaller baskets (items per order barely moved, from 1.15 to 1.14). Most of it, R$96,837, came from a lower average price per item.

So growth was order-led, and each item sold for a bit less. Now we need to find out why the price per item went down. It could be cheaper products in the same category, a shift toward cheaper categories, or actual price cuts. This dataset has no discount or list-price column, so we can only look at the price customers actually paid. Let’s go to category level.

Step 3: Volume, price, and mix, category by category

This is the part that confuses people the first time, so let me show a tiny example before the SQL.

A worked example first

Two products. Q2: 10 units of A at R$100 and 2 units of B at R$500. Revenue is 1,000 + 1,000 = 2,000 on 12 units, so the average price per unit is 166.67.

Q3: still 10 units of A at R$100, but now 4 units of B at R$500. Revenue is 1,000 + 2,000 = 3,000 on 14 units.

Revenue rose 1,000. Now split it:

• Volume effect = extra units × old average price = (14 − 12) × 166.67 = 333. “If we simply sold 2 more of an average item.”
• Price effect = change in each product’s price × new units = 0. No prices moved.
• Mix effect = whatever is left = 1,000 − 333 − 0 = 667. We sold the expensive product more, so each extra unit was worth more than average.

So total units grew about 17%, but two thirds of the revenue gain came from which items were sold, not how many. That is mix. We define it as the remainder so the three effects always add up exactly to the total change.

Why category and not product

Olist has 32,951 products, and most sell only a few times a quarter, so product-level price comparisons would be very noisy. I use the product category (71 categories) as the unit instead. That is a trade-off: mix inside a category (say, cheaper phone cases within telephony) will show up in the price effect, not the mix effect.

Categories that exist in only one quarter

You cannot compute a price change for a category with no sales in one of the quarters. So they go into separate “new” and “discontinued” buckets, and we run volume, price, and mix on the continuing categories only. Let’s see which categories these are:

WITH cat AS (
  SELECT category,
         SUM(CASE WHEN quarter = '2017-Q4' THEN 1 ELSE 0 END) AS u0,
         SUM(CASE WHEN quarter = '2018-Q1' THEN 1 ELSE 0 END) AS u1
  FROM delivered_items
  WHERE quarter IN ('2017-Q4', '2018-Q1')
  GROUP BY category
)
SELECT category, u0 AS items_2017_q4, u1 AS items_2018_q1
FROM cat
WHERE u0 = 0 OR u1 = 0;

Output:

Three tiny categories: 68 of the 71 categories sold in both quarters. These are very small, and I would not call them real launches or exits. Some may be reclassifications or very slow sellers. That is exactly why keeping them in a separate bucket is useful: they do not distort the price comparison.

The decomposition

WITH cat AS (
  SELECT category,
         SUM(CASE WHEN quarter = '2017-Q4' THEN 1 ELSE 0 END)     AS u0,
         SUM(CASE WHEN quarter = '2018-Q1' THEN 1 ELSE 0 END)     AS u1,
         SUM(CASE WHEN quarter = '2017-Q4' THEN price ELSE 0 END) AS r0,
         SUM(CASE WHEN quarter = '2018-Q1' THEN price ELSE 0 END) AS r1
  FROM delivered_items
  WHERE quarter IN ('2017-Q4', '2018-Q1')
  GROUP BY category
),
cont AS (SELECT * FROM cat WHERE u0 > 0 AND u1 > 0),
tot AS (
  SELECT SUM(u0) AS U0, SUM(u1) AS U1, SUM(r0) AS R0, SUM(r1) AS R1,
         SUM(r0) * 1.0 / SUM(u0) AS P0
  FROM cont
),
eff AS (
  SELECT (U1 - U0) * P0 AS volume_effect,
         (SELECT SUM((r1 * 1.0 / u1 - r0 * 1.0 / u0) * u1) FROM cont) AS price_effect,
         (R1 - R0) AS continuing_change
  FROM tot
),
np AS (
  SELECT COALESCE(SUM(CASE WHEN u0 = 0 THEN r1 END), 0)  AS new_categories,
         COALESCE(-SUM(CASE WHEN u1 = 0 THEN r0 END), 0) AS discontinued_categories
  FROM cat
)
SELECT ROUND(volume_effect) AS volume_effect,
       ROUND(price_effect)  AS price_effect,
       ROUND(continuing_change - volume_effect - price_effect) AS mix_effect,
       ROUND(new_categories) AS new_categories,
       ROUND(discontinued_categories) AS discontinued_categories,
       ROUND(continuing_change + new_categories + discontinued_categories) AS total_change
FROM eff, np;

What the query does: cat builds one row per category with units and revenue in each quarter. cont keeps continuing categories. Volume is the change in total units times the old average price. Price is the sum of each category’s price change times its new units. Mix is the remainder. np handles new and discontinued categories.

Output:

Adding across: 438,213 − 101,515 − 5,417 + 11,416 − 305 = 342,392. It matches the total change from Step 2, which is our sanity check that the logic is right.

Reading the bridge

Here are the same numbers as a revenue bridge (waterfall), which is how I would show this to a stakeholder.

Show Image

In plain words:

• Volume: +R$438,213. We sold many more items across continuing categories. This is the engine of growth.
• Price: −R$101,515. The average price paid per item within categories went down, which took back about a quarter of the volume gain.
• Mix: −R$5,417. Almost nothing. The shift between categories barely changed revenue per item.
• New and discontinued categories: +R$11,111 combined. Small, as expected.

So the answer to our title question: the 14.5% growth was volume, partly given back through lower prices, with mix almost neutral. Now let’s see which categories drove the volume and where prices dropped.

Step 4: Which categories drove the change?

Question: How much of the total change came from each category, and did prices move?

SUM() OVER () with no partition gives the grand total across all rows, so each category’s change can be shown as a share of the total change.

WITH cat AS (
  SELECT category,
         SUM(CASE WHEN quarter = '2017-Q4' THEN 1 ELSE 0 END)     AS u0,
         SUM(CASE WHEN quarter = '2018-Q1' THEN 1 ELSE 0 END)     AS u1,
         SUM(CASE WHEN quarter = '2017-Q4' THEN price ELSE 0 END) AS r0,
         SUM(CASE WHEN quarter = '2018-Q1' THEN price ELSE 0 END) AS r1
  FROM delivered_items
  WHERE quarter IN ('2017-Q4', '2018-Q1')
  GROUP BY category
)
SELECT category,
       u0 AS items_2017_q4, u1 AS items_2018_q1,
       ROUND(r0 / NULLIF(u0, 0), 1) AS avg_price_2017_q4,
       ROUND(r1 / NULLIF(u1, 0), 1) AS avg_price_2018_q1,
       ROUND(r1 - r0)               AS revenue_change,
       ROUND((r1 - r0) * 100.0 / SUM(r1 - r0) OVER (), 1) AS share_of_total_change_pct
FROM cat
ORDER BY revenue_change DESC
LIMIT 6;

Output: the six biggest gainers

The same query with ORDER BY revenue_change ASC LIMIT 4 gives the biggest decliners:

What I noticed:

• computers_accessories alone drove 33.9% of the net growth. Its items more than doubled (1,105 to 2,409, up 118%), while its average price fell 18% (R$134.9 to R$110.1). That is a big volume story with a price drop attached. This one category explains a large part of the negative price effect.
• The top three gainers (computers_accessories, sports_leisure, health_beauty) account for about 73% of the net change. Growth is fairly concentrated.
• toys fell by R$92,596 and sold less than half as many items. Q4 includes the holiday season, so a post-holiday drop is a reasonable explanation, but this data alone cannot prove that cause.
• computers (the expensive category, average price over R$1,000 in Q4) lost almost R$60,000 on just 57 versus 11 items. It is a low-volume, high-value category, so a few orders swing it a lot.
• baby went the other way: fewer than 1.3 times the items, but the average price rose 32% (R$120.7 to R$159.3). That may be product mix inside the category.

The shares add to 100% only when you include the decliners. That is normal in contribution analysis: when some parts shrink, the others must more than make up for it.

Step 5: Is that growth healthy?

Revenue alone does not tell us whether this growth is good for the business. We do not have cost or margin data in Olist, so we cannot compute profit. But we can check other signals that the dataset does contain: seller activity, shipping cost, delivery performance, and how customers pay.

Sellers, customers, and shipping cost

SELECT quarter,
       COUNT(DISTINCT order_id)                                              AS orders,
       COUNT(DISTINCT seller_id)                                             AS active_sellers,
       ROUND(COUNT(DISTINCT order_id) * 1.0 / COUNT(DISTINCT seller_id), 1)  AS orders_per_seller,
       COUNT(DISTINCT customer_unique_id)                                    AS unique_customers,
       ROUND(SUM(freight_value) * 100.0 / SUM(price), 1)                     AS freight_pct_of_item_price
FROM delivered_items
WHERE quarter IN ('2017-Q4', '2018-Q1')
GROUP BY quarter
ORDER BY quarter;

Output:

• Active sellers grew about 10% (1,230 to 1,351) while orders grew 19%, so each seller handled more orders.
• Unique customers are almost equal to orders (20,211 customers for 20,627 orders). Most buyers ordered once in the quarter, so this growth is driven by many customers each placing an order, not by a small group of repeat buyers. This dataset cannot tell us whether they come back later.
• Shipping cost rose slightly, from 16.3% to 17.0% of the item price. For low-priced items, shipping is a big share of what the customer pays.

Delivery performance

Here I define an order as late when its delivery date is after the estimated delivery date.

SELECT q.quarter,
       COUNT(*) AS delivered_orders,
       ROUND(SUM(CASE WHEN substr(o.order_delivered_customer_date, 1, 10)
                          > substr(o.order_estimated_delivery_date, 1, 10)
                      THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS late_delivery_pct
FROM orders o
JOIN (SELECT DISTINCT order_id, quarter
      FROM delivered_items
      WHERE quarter IN ('2017-Q4', '2018-Q1')) q
  ON q.order_id = o.order_id
WHERE o.order_delivered_customer_date IS NOT NULL
GROUP BY q.quarter
ORDER BY q.quarter;

Output:

(One Q4 order has no delivery date, which is why it shows 17,279 instead of 17,280. I compare dates only, not times, because the estimated delivery timestamp is always midnight.)

This is the finding I would flag first. The share of late deliveries went from 8.7% to 12.9%, a rise of 4.2 percentage points, while order volume grew 19%. Faster growth with slower delivery is a classic warning sign. Late deliveries are likely to hurt customer experience, though this dataset (as I loaded it) has no review table, so I cannot measure that effect here.

Payment behavior

SELECT d.quarter, p.payment_type,
       COUNT(DISTINCT d.order_id)            AS orders,
       ROUND(AVG(p.payment_installments), 1) AS avg_installments
FROM (SELECT DISTINCT order_id, quarter
      FROM delivered_items
      WHERE quarter IN ('2017-Q4', '2018-Q1')) d
JOIN order_payments p ON p.order_id = d.order_id
GROUP BY d.quarter, p.payment_type
ORDER BY d.quarter, orders DESC;

Output:

Payment behavior looks stable. About 77% of orders include a credit card in both quarters (an order can use more than one payment type, so the rows can add to slightly over 100%), and average installments dipped from 3.6 to 3.3. Payments do not seem to explain the change in average item price. It is useful to rule it out.

Step 6: Check seasonality with year over year

Quarter over quarter can mislead if one quarter is naturally stronger, and Q4 has holiday shopping. So we also compare Q1 2018 with Q1 2017.

WITH agg AS (
  SELECT quarter, COUNT(DISTINCT order_id) AS orders, SUM(price) AS revenue
  FROM delivered_items
  WHERE quarter IN ('2017-Q1', '2018-Q1')
  GROUP BY quarter
),
pv AS (
  SELECT
    MAX(CASE WHEN quarter = '2017-Q1' THEN orders END)                 AS o0,
    MAX(CASE WHEN quarter = '2018-Q1' THEN orders END)                 AS o1,
    MAX(CASE WHEN quarter = '2017-Q1' THEN revenue * 1.0 / orders END) AS aov0,
    MAX(CASE WHEN quarter = '2018-Q1' THEN revenue * 1.0 / orders END) AS aov1,
    MAX(CASE WHEN quarter = '2017-Q1' THEN revenue END)                AS r0,
    MAX(CASE WHEN quarter = '2018-Q1' THEN revenue END)                AS r1
  FROM agg
)
SELECT ROUND(r0) AS rev_2017_q1, ROUND(r1) AS rev_2018_q1,
       ROUND((r1 - r0) * 100.0 / r0, 1) AS yoy_pct,
       o0 AS orders_2017_q1, o1 AS orders_2018_q1,
       ROUND(aov0, 1) AS aov_2017_q1, ROUND(aov1, 1) AS aov_2018_q1,
       ROUND((o1 - o0) * aov0) AS orders_effect,
       ROUND((aov1 - aov0) * o1) AS aov_effect
FROM pv;

Output:

Year over year, revenue grew 283.5%. This is an entirely unique picture from the 14.5% quarterly figure, and it teaches an important lesson: the growth rate depends on the base you compare with. Q1 2017 was an early, small quarter for this marketplace, so the year-over-year figure mostly reflects a business that was scaling up quickly. The quarterly growth of 14.5% is a mid-ramp reading, and the following quarter grew only 3.8% (R$2,704,438 to R$2,807,157 in the Step 1 table).

The pattern underneath is the same in both comparisons: growth from many more orders, with AOV drifting down (R$142.5 to R$131.1 year over year).

One limit: with under two years of usable data and very little in 2016, we cannot properly separate seasonality from the growth trend. I would want at least two full years of a mature business to do that reliably.

What would I tell the manager?

If I had to answer that original message, I would say something like this:

“Revenue rose 14.5% from Q4 2017 to Q1 2018, and that growth was driven by volume: about 19% more delivered orders. Prices per item slipped a bit, which took back roughly a quarter of the volume gain, and category mix hardly mattered. One category, computers and accessories, provided about a third of the growth while its average price dropped 18%. But late deliveries rose from 8.7% to 12.9% over the same period. Before doubling the ad budget, I would resolve or at least understand the delivery slowdown, because more orders at slower delivery may cost us later.”

That answer connects the whole chain: question → data → analysis → insight → decision. Let me spell out the last two links properly, because this is where analysis becomes useful to a business.

Business impact

Here is what the numbers mean in business terms, all from the queries above.

Recommendations

If I were presenting this to a manager, I would suggest these steps, in this order:

1. Do not double the ad budget across the board yet. The growth is genuine, but delivery quality is getting worse at the same time. Scaling up demand into a slower fulfilment process is a risk.
2. Investigate the late-delivery rise first. Break late deliveries down by seller, seller state, customer state, and category to see whether the delay is concentrated (a few sellers or regions) or general. A concentrated problem is much cheaper to fix.
3. Look inside computers_accessories. Its average price fell 18%. Check whether sellers cut prices, or whether the mix moved toward cheaper accessories. That decides whether more ad spend would buy healthy volume or low-value volume.
4. Reduce dependence on a few categories. Test small budgets on categories with solid volume and steady or rising prices (for example sports_leisure, housewares, baby) instead of pushing only the leading category.
5. Review the shipping burden on low-priced items. With freight at 17% of item price, check whether free-shipping thresholds or bundling could raise basket value (this connects to the AOV threshold topic).
6. Report growth as a bridge, not a single number. Show volume, price, mix, and category effects together with delivery performance each quarter, and add year over year once there is enough history.

What I could not answer with this data: profit (there is no cost column), the effect of discounts (there is no discount or list-price column), ad spend or acquisition cost (not included), customer satisfaction (no review table loaded here), and repeat purchase behavior over time. A real analyst would ask for these before finalising a budget decision.

What would change my mind: if the delivery breakdown shows the delays come from one or two sellers that can be fixed or removed, the case for scaling spend gets stronger. If the delays are spread across the marketplace, I would hold off on extra spend until fulfilment improves.

Common mistakes to avoid

• Not defining revenue first. Item price only or price plus freight, delivered orders only or all orders. Different definitions give different growth rates.
• Forgetting the status filter. Only 97% of Olist orders are delivered. Including canceled or unavailable orders would inflate revenue.
• Counting rows as orders. In order_items, one order can have several rows. Use COUNT(DISTINCT order_id) for orders.
• Comparing only to the last quarter. Seasonality and a small base can create or hide growth.
• Treating mix as a mystery. Mix is just “which products did we sell”, computed as a remainder so everything adds up.
• Ignoring categories that appear or disappear. They break naive price comparisons. Give them their own bucket.
• Calling a price drop a “discount”. Without list-price data you can only say the average price paid fell.
• Stopping at revenue. Check delivery, shipping, and (when available) margin before recommending more spend.
• Confusing the decomposition with causality. The bridge tells you where the change came from, not why.

Turning this into a Power BI dashboard

If you want to build this as a portfolio project:

• Data model: a star schema with order_items (one row per item) as the fact table, and dimensions for orders and dates, customers, products with translated categories, and sellers.
• KPI cards: Revenue, Orders, AOV, Items per order, Late delivery %, each with a stated time window and change versus last quarter and last year.
• Main visual: a waterfall chart for the revenue bridge (Q4 2017, Volume, Price, Mix, New and discontinued, Q1 2018). A waterfall works better than a stacked bar because the reader can follow how each step moves the running total.
• Drilldown: category to product, with slicers for customer state and date.
• Page 2: a category contribution table with conditional formatting on average price change, and a delivery performance view by state.

I would do the calculations in SQL views (like delivered_items above) and let Power BI read them, so the logic is written once and can be tested.

Interview practice

Try answering these out loud before reading my notes.

1. Business understanding: Revenue is up 15%. What would you check before celebrating? (Definition, status filters, seasonality, orders vs AOV, price and mix, concentration, and operational quality such as delivery.)
2. KPI thinking: Why split revenue into orders × AOV first? (It is exact, simple, and tells you quickly whether growth is about customers or baskets.)
3. SQL: In order_items there is no quantity column. How do you count orders, units, and AOV correctly? (Distinct order IDs for orders, row count for units.)
4. SQL: How would you handle a category sold in only one of the two periods? (Separate new and discontinued buckets, price and mix only on continuing categories.)
5. Data interpretation: Volume is up, price is down, mix is neutral. What is your read? (Growth is coming from more sales at slightly lower prices, so check margin, what caused the price drop, and operational strain.)
6. Dashboard design: Why a waterfall instead of a stacked bar?
7. Stakeholder communication: How do you tell a manager that strong growth is coming with slower deliveries?

Try it yourself

1. Download the Olist dataset from Kaggle and load the tables into MySQL or SQLite. You can also explore other practice data in the BloomInData dataset library.
2. Create the delivered_items view and reproduce the baseline query. Check that orders × AOV equals revenue.
3. Reproduce the bridge and confirm that volume + price + mix + new + discontinued equals the total change.
4. Change the pair of quarters (for example 2018-Q1 vs 2018-Q2) and see how the story changes.
5. Change the revenue definition (include freight) and see how much the numbers shift.
6. Extend it: break late deliveries down by customer state, or run the same bridge at product level for one large category.

What to learn next

• How to analyse pricing and discounts properly when you do have discount data.
• Setting an AOV threshold for free shipping.
• Diagnosing any KPI change with a repeatable checklist.
• Cohort analysis: are the extra orders coming from new customers or repeat ones?
• Using the geolocation table to map delivery performance by region.

Frequently asked questions

Keep exploring with me

If you’re also learning data analytics and want to build real business understanding alongside your technical skills, you’re welcome to follow along. I share what I learn, the datasets I explore, and the projects I build.

• Website and dataset library: bloomindata.in and the dataset library
• YouTube: @bloominpriyanka
• GitHub: priyankadatacodes
• LinkedIn: Priyanka on LinkedIn
• Medium: @bloomindata

If this article helped, tell me which business question you’d like me to break down next.

Sources and external references

The analysis is BloomInData’s own, run on the public Olist Brazilian e-commerce dataset. All figures come from my SQL queries on that data. I would link to these once the exact URLs are verified:

About the author
I'm Priyanka Lakra, a self-taught data learner and  I explore the data ecosystem one concept, dataset and project at a time, and I share what I learn as I go, including the mistakes.. I share what I learn on BloomInData. Come explore it with me.