E-commerce Order & Revenue Analytics

Why this project

Most "e-commerce analytics" tutorials start from a single clean orders table. Real operations data never looks like that — an order moves through vendor intake, quality control, purchasing, and shelving before it's actually fulfilled, and each stage is its own timestamped event. This project builds the reporting layer for that reality: a repeatable ETL job plus a Power BI report that tells leadership what revenue looks like today, not just at month-end.

You can rebuild this end-to-end with a free MySQL instance and about an afternoon.

The data model

Two tables carry the whole project:

create table orders (
  order_id      int primary key,
  customer_id   int not null,
  vendor_id     int not null,
  order_date    datetime not null,
  status        enum('placed','processing','shelved','cancelled') not null,
  amount        decimal(10,2) not null
);
 
create table order_status_log (
  order_id   int not null,
  stage      enum('vendor_intake','qc','purchased','shelved') not null,
  entered_at datetime not null,
  foreign key (order_id) references orders(order_id)
);

order_status_log is the important part — one row per order per stage, so you can measure how long an order actually takes to move through fulfillment, not just whether it eventually finished.

Step 1 — Extract with SQL

Start with the cancellation funnel, since it's the single query that tends to surprise stakeholders the most:

select
  date_format(order_date, '%Y-%m') as month,
  count(*)                                                as total_orders,
  sum(status = 'cancelled')                                as cancelled_orders,
  round(100.0 * sum(status = 'cancelled') / count(*), 1)   as cancellation_rate_pct,
  sum(case when status = 'cancelled' then amount else 0 end) as cancelled_revenue
from orders
group by 1
order by 1;

Run this before building anything else — it's usually what tells you whether cancellations are worth a whole dashboard section or a single KPI card.

Step 2 — Transform with Python

Pull both tables into pandas and pivot the status log so each order gets one row with a timestamp per stage:

import pandas as pd
from sqlalchemy import create_engine
 
engine = create_engine(DATABASE_URL)
 
orders = pd.read_sql("select * from orders", engine, parse_dates=["order_date"])
status_log = pd.read_sql("select * from order_status_log", engine, parse_dates=["entered_at"])
 
stage_times = status_log.pivot(index="order_id", columns="stage", values="entered_at")
orders = orders.merge(stage_times, on="order_id", how="left")
 
orders["fulfillment_days"] = (orders["shelved"] - orders["order_date"]).dt.days

That last column — days from order to shelved — is what turns this from "a report" into something an ops manager will actually act on, because it points at where in the pipeline orders are getting stuck.

Step 3 — Revenue run-rate forecasting

Three forecast models, from simplest to most useful:

1. Flat run-rate — average daily revenue so far this month, projected across the full month.
2. Actuals + run-rate — revenue already booked, plus a flat forecast for the remaining days only.
3. Day-of-week weighted — accounts for the fact that a retailer's Thursday almost never looks like its Monday.

-- Historical revenue share by weekday, used to weight the remaining-days forecast
select
  dayname(order_date) as weekday,
  round(100.0 * sum(amount) / (select sum(amount) from orders), 2) as pct_of_total_revenue
from orders
group by 1
order by 2 desc;

Step 4 — Model it in Power BI

Bring the staging table in and build these as DAX measures rather than calculated columns, so they respond to whatever filter context the report is in:

Placed Revenue = SUM(Orders[Amount])
 
Realized Revenue =
CALCULATE(
    [Placed Revenue],
    NOT ( Orders[Status] = "Cancelled" )
)
 
Run Rate Forecast =
VAR DaysElapsed = DAY(TODAY())
VAR DaysInMonth = DAY(EOMONTH(TODAY(), 0))
RETURN
    DIVIDE([Placed Revenue], DaysElapsed) * DaysInMonth
 
Cancellation Rate % =
DIVIDE(
    CALCULATE(COUNTROWS(Orders), Orders[Status] = "Cancelled"),
    COUNTROWS(Orders)
)

Build one page for the revenue trend + forecast, and a second for the cancellation funnel by stage — that split is what makes the report readable to someone who isn't going to open the model.

How to build your own version

1. Generate synthetic order data (or use any public order-level dataset) and load it into a free MySQL instance — db4free.net or a local Docker container both work.
2. Write the cancellation-funnel query first; it's the fastest way to sanity-check your data.
3. Build the pandas pivot for stage timestamps and compute fulfillment_days.
4. Load the result into Power BI and add the four DAX measures above.
5. Build two report pages: Revenue & Forecast, Fulfillment & Cancellations.

Talking about it in an interview

Be ready to explain why three forecast models instead of one — the honest answer is that a flat run-rate is naive early in the month and a day-of-week model needs enough history to be trustworthy, so you show whichever is appropriate given how much of the month has elapsed. That's a stronger answer than claiming one model is "best."