Why did revenue fall?

In short: A data analysis project from the question to the report: cleaning, charts, a check for noise, breaking the revenue drop down by order count, category, day and channel, and a model for the loss with an interval.

Problem

“Why is September so weak?”

The manager has seen the report: revenue for September 2026 is €7,128.80, and for August €8,812.70. That is 19.1% down. He has three ideas on the table: stop the ads because they “do not work”, change the dairy supplier, or run discounts.

Each idea answers a different cause. If the drop is noise, you do nothing. If it comes from a product, you look at the supplier. If people buy less, you think about prices. So the question of the analysis is not “how big is the drop” but “where is the drop, so we know what to fix”.

In this project you walk the whole path of the course: the data, cleaning, the chart, the check for noise, the breakdown, the model and the report. The numbers come from the shop’s year of orders, and the answer is not where the manager’s ideas expect it.

The idea

The data and the cleaning

Two tables: year_orders.csv (order, customer, time, channel, status) and year_items.csv (the lines of the orders). The value of an order is the sum of its lines. Cancelled orders are not revenue:

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()

print(len(orders), len(sold))
print(orders['status'].value_counts())
print(sold['value'].isna().sum(), round(sold['value'].min(), 2))
print(sold['day'].min().date(), sold['day'].max().date(), sold['day'].nunique())
print(round(sold['value'].sum(), 2))

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() print(len(orders), len(sold)) print(orders['status'].value_counts()) print(sold['value'].isna().sum(), round(sold['value'].min(), 2)) print(sold['day'].min().date(), sold['day'].max().date(), sold['day'].nunique()) print(round(sold['value'].sum(), 2))

▶ RunRestore the code

There are 11,116 orders, and without the 657 cancelled ones 10,459 are left. Before you compute anything, check the cleaning: every order has a value (0 missing), the smallest is €0.80, and the days are 365, from 1 November 2025 to 31 October 2026. Revenue for the year is €111,186.20, the same number as in the module “Wrangling data”.

new, paid and shipped are orders from the last week that are still on their way. You count them: they are paid or will be. The column channel says where the order came from: the website (web), the app (app) or the phone (phone).

Write the cleaning yourself. orders['ordered_at'] should be a date, items['revenue'] the revenue of every line, orders['value'] the value of every order, and sold the orders without the cancelled ones.

Pythondata: the shop tables

import pandas as pd

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = ...
items['revenue'] = ...
orders['value'] = ...
sold = ...
print(len(sold), round(sold['value'].sum(), 2))

import pandas as pd orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = ... items['revenue'] = ... orders['value'] = ... sold = ... print(len(sold), round(sold['value'].sum(), 2))

Check▶ Run

The idea

The chart first

Before you look for a cause, see the drop in the context of the year:

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
daily = sold.groupby('day')['value'].sum()
monthly = sold.groupby('month')['value'].sum()

aug, sep = monthly['2026-08'], monthly['2026-09']
print(round(aug, 2), round(sep, 2), round((sep / aug - 1) * 100, 1))
print(round(monthly['2026-10'], 2))

fig, ax = plt.subplots(figsize=(8, 3.5))
ax.bar(monthly.index.str[2:], monthly.values, color=['C3' if m == '2026-09' else 'C0' for m in monthly.index])
ax.set_title('Revenue by month, €')
ax.set_xlabel('month')
ax.set_ylabel('€')

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() daily = sold.groupby('day')['value'].sum() monthly = sold.groupby('month')['value'].sum() aug, sep = monthly['2026-08'], monthly['2026-09'] print(round(aug, 2), round(sep, 2), round((sep / aug - 1) * 100, 1)) print(round(monthly['2026-10'], 2)) fig, ax = plt.subplots(figsize=(8, 3.5)) ax.bar(monthly.index.str[2:], monthly.values, color=['C3' if m == '2026-09' else 'C0' for m in monthly.index]) ax.set_title('Revenue by month, €') ax.set_xlabel('month') ax.set_ylabel('€')

▶ RunRestore the code

September is €7,128.80 against €8,812.70 in August: −19.1%. October is €9,920.40, so the drop does not go on. The chart shows something else too: December is over €15,000, and January and February are also low. September is a drop in the middle of normal months, not part of a season.

September has 30 days and August 31. Could that explain part of the drop? Store in per_day the revenue per day for every month, and in change the change in revenue per day from August to September, in percent.

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
daily = sold.groupby('day')['value'].sum()
monthly = sold.groupby('month')['value'].sum()

per_day = ...
change = ...
print(per_day.round(2))
print(round(change, 1))

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() daily = sold.groupby('day')['value'].sum() monthly = sold.groupby('month')['value'].sum() per_day = ... change = ... print(per_day.round(2)) print(round(change, 1))

Check▶ Run

The idea

Is it noise?

Months always differ a little. Before you look for a cause, check whether the drop is bigger than the usual noise. You compare the days: 31 days of August against 30 of September, with the permutation test from the lesson on hypotheses. Below is the change in revenue per day from month to month:

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
daily = sold.groupby('day')['value'].sum()
monthly = sold.groupby('month')['value'].sum()

aug = daily.loc['2026-08'].to_numpy()
sep = daily.loc['2026-09'].to_numpy()
gap = aug.mean() - sep.mean()
print(len(aug), len(sep), round(aug.mean(), 2), round(sep.mean(), 2), round(gap, 2))

rng = np.random.default_rng(42)
both = np.concatenate([aug, sep])
shuffled = rng.permuted(np.tile(both, (10_000, 1)), axis=1)
gaps = shuffled[:, :len(aug)].mean(axis=1) - shuffled[:, len(aug):].mean(axis=1)
print(round(np.abs(gaps).max(), 2), (np.abs(gaps) >= abs(gap)).mean())

per_day = daily.groupby(daily.index.strftime('%Y-%m')).mean().rename_axis('month')
(per_day.pct_change() * 100).round(1).rename('change_%').loc['2026-02':]

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() daily = sold.groupby('day')['value'].sum() monthly = sold.groupby('month')['value'].sum() aug = daily.loc['2026-08'].to_numpy() sep = daily.loc['2026-09'].to_numpy() gap = aug.mean() - sep.mean() print(len(aug), len(sep), round(aug.mean(), 2), round(sep.mean(), 2), round(gap, 2)) rng = np.random.default_rng(42) both = np.concatenate([aug, sep]) shuffled = rng.permuted(np.tile(both, (10_000, 1)), axis=1) gaps = shuffled[:, :len(aug)].mean(axis=1) - shuffled[:, len(aug):].mean(axis=1) print(round(np.abs(gaps).max(), 2), (np.abs(gaps) >= abs(gap)).mean()) per_day = daily.groupby(daily.index.strftime('%Y-%m')).mean().rename_axis('month') (per_day.pct_change() * 100).round(1).rename('change_%').loc['2026-02':]

▶ RunRestore the code

A September day brings €46.65 less on average. Out of 10,000 shuffles of the days only 74 give a gap at least this big: p = 0.0074. The normal months from March to August move between −6.5% and +5.8%, and September is −16.4%. February (+16.8%) is the return after the holidays, and October (+34.7%) is the return after September.

The drop is not noise. It makes sense to look for a cause.

1. The permutation test for the days of August against the days of September gives p = 0.0074. What follows from that?

The drop is bigThe drop is 99.26% sure to come from something in SeptemberIf the month did not matter, a gap of €46.65 per day would rarely come up by chance: the drop is real and it makes sense to look for a cause

Check

The idea

Break the drop down

Revenue is the number of orders times the value of an order. The first breakdown is which of the two falls:

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()

summer = sold[sold['month'].isin(['2026-08', '2026-09'])]
summer.groupby('month').agg(orders=('id', 'count'), avg_order=('value', 'mean')).round(2)

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() summer = sold[sold['month'].isin(['2026-08', '2026-09'])] summer.groupby('month').agg(orders=('id', 'count'), avg_order=('value', 'mean')).round(2)

▶ RunRestore the code

Orders fall from 854 to 701 (−17.9%), and the average order is almost the same: €10.32 and €10.17. The cart is not smaller; there are fewer orders. Discounts are not the answer.

Could it be a product, say the dairy from the new supplier? Revenue by category:

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()

products = pd.read_csv('products.csv')
categories = pd.read_csv('categories.csv')
lines = items.merge(sold[['id', 'month']], left_on='order_id', right_on='id')
cat_of = products.set_index('id')['category_id']
lines['category'] = lines['product_id'].map(cat_of).map(categories.set_index('id')['name']).fillna('Gift card')
table = lines.pivot_table(index='category', columns='month', values='revenue', aggfunc='sum')[['2026-08', '2026-09']]
table['change_%'] = ((table['2026-09'] / table['2026-08'] - 1) * 100).round(1)
table.round(2)

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() products = pd.read_csv('products.csv') categories = pd.read_csv('categories.csv') lines = items.merge(sold[['id', 'month']], left_on='order_id', right_on='id') cat_of = products.set_index('id')['category_id'] lines['category'] = lines['product_id'].map(cat_of).map(categories.set_index('id')['name']).fillna('Gift card') table = lines.pivot_table(index='category', columns='month', values='revenue', aggfunc='sum')[['2026-08', '2026-09']] table['change_%'] = ((table['2026-09'] / table['2026-08'] - 1) * 100).round(1) table.round(2)

▶ RunRestore the code

Every category falls, from −9.0% (fruit and vegetables) to −34.3% (drinks). The gift card is +14.3%, but that is €160 against €140 in August, a few cards. If one product were to blame, the other categories would be normal. The drop is everywhere: it is not the product.

Is the drop on one day of the week, say the shop was closed on Thursdays? Store in by_wd the average revenue per day for each day of the week (rows 0–6) for August and September (columns), and a column change_% with the change in percent.

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
daily = sold.groupby('day')['value'].sum()
monthly = sold.groupby('month')['value'].sum()

two = daily.loc['2026-08':'2026-09']
by_wd = ...
by_wd['change_%'] = ...
print(by_wd.round(1))

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() daily = sold.groupby('day')['value'].sum() monthly = sold.groupby('month')['value'].sum() two = daily.loc['2026-08':'2026-09'] by_wd = ... by_wd['change_%'] = ... print(by_wd.round(1))

Check▶ Run

The idea

The channel

One column is left that you have not looked at yet: channel. Revenue by month and channel:

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()

table = sold.pivot_table(index='month', columns='channel', values='value', aggfunc='sum')
table.loc['2026-06':].round(2)

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() table = sold.pivot_table(index='month', columns='channel', values='value', aggfunc='sum') table.loc['2026-06':].round(2)

▶ RunRestore the code

There it is. The website (web) is €4,517.00 in August and €4,660.80 in September. The phone (phone) is €999.70 and €1,028.70. They are normal. The app (app) falls from €3,296.00 to €1,439.30 and comes back to €3,755.40 in October.

The app’s drop is €1,856.70, more than the whole drop of the shop (€1,683.90): the other two channels even grow a little. That is why every category and every day fall together: the app sells everything, every day.

How much of the revenue comes from the app in an ordinary month? Store in share the share of app in the revenue of every month, in percent.

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()

table = sold.pivot_table(index='month', columns='channel', values='value', aggfunc='sum')
share = ...
print(share.round(1))

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() table = sold.pivot_table(index='month', columns='channel', values='value', aggfunc='sum') share = ... print(share.round(1))

Check▶ Run

The idea

When exactly?

“September” is rough. The app team will ask for a date. The app orders day by day, with the 7-day rolling average, and the days around both ends:

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
counts = sold.pivot_table(index='day', columns='channel', values='id', aggfunc='count', fill_value=0)

app = counts['app'].loc['2026-08-01':'2026-10-31']
roll = app.rolling(7, center=True).mean()
fig, ax = plt.subplots(figsize=(8, 3.5))
ax.plot(app.index, app.values, lw=0.8, color='C0', alpha=0.5, label='per day')
ax.plot(roll.index, roll.values, lw=2, color='C0', label='7-day average')
ax.set_title('App orders per day')
ax.set_xlabel('day')
ax.set_ylabel('orders')
ax.set_ylim(bottom=0)
ax.legend()

edges = pd.concat([counts.loc['2026-08-31':'2026-09-05'], counts.loc['2026-09-29':'2026-10-04']])
edges.index = edges.index.strftime('%d.%m')
edges[['app', 'web', 'phone']]

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() counts = sold.pivot_table(index='day', columns='channel', values='id', aggfunc='count', fill_value=0) app = counts['app'].loc['2026-08-01':'2026-10-31'] roll = app.rolling(7, center=True).mean() fig, ax = plt.subplots(figsize=(8, 3.5)) ax.plot(app.index, app.values, lw=0.8, color='C0', alpha=0.5, label='per day') ax.plot(roll.index, roll.values, lw=2, color='C0', label='7-day average') ax.set_title('App orders per day') ax.set_xlabel('day') ax.set_ylabel('orders') ax.set_ylim(bottom=0) ax.legend() edges = pd.concat([counts.loc['2026-08-31':'2026-09-05'], counts.loc['2026-09-29':'2026-10-04']]) edges.index = edges.index.strftime('%d.%m') edges[['app', 'web', 'phone']]

▶ RunRestore the code

The line drops at the start of September and comes back at the start of October. The table shows the ends. On 2 September the app has 9 orders, on 3 September only 3, and on 5 September zero, while the website has 25 and 15. At the end: 6 orders on 1 October, 8 on 2 October and 12 on 3 October.

The rolling average smears the ends by a few days, so you look for the dates in the days. The start is clear: 3 September. The end is murkier: 2 October is already halfway back, and from 3 October the app is as before. The drop runs from 3 September to 2 October, give or take a day.

Find the dates with a rule, not by eye. start is the first day from 1 August 2026 on when the app has under 20% of the day’s orders. back is the first day after start when the app orders are at least the median of the normal days (up to 31 August).

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
counts = sold.pivot_table(index='day', columns='channel', values='id', aggfunc='count', fill_value=0)

share = counts['app'] / counts.sum(axis=1)
normal = counts.loc[:'2026-08-31', 'app']
start = ...
after = counts.loc[start:, 'app']
back = ...
print(start.date(), back.date(), normal.median())

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() counts = sold.pivot_table(index='day', columns='channel', values='id', aggfunc='count', fill_value=0) share = counts['app'] / counts.sum(axis=1) normal = counts.loc[:'2026-08-31', 'app'] start = ... after = counts.loc[start:, 'app'] back = ... print(start.date(), back.date(), normal.median())

Check▶ Run

The idea

What did the drop cost?

The manager will ask how much money it cost. The naive answer is “August minus September”: €1,683.90. But that assumes September should have been like August. October is stronger than August, so September may have been meant to be stronger too.

The channels that work give a better comparison. In normal times the app brings a steady share relative to the website and the phone. That share k is a model with one number: it predicts the app’s revenue from the revenue of the other two channels on the same days. You learn it from the days before the drop and check it on days it has not seen: October after the drop.

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
by_day = sold.pivot_table(index='day', columns='channel', values='value', aggfunc='sum', fill_value=0)
by_day['rest'] = by_day['web'] + by_day['phone']
day = by_day.index
before = day < '2026-09-03'
drop = (day >= '2026-09-03') & (day <= '2026-10-02')
after = day > '2026-10-02'

k = by_day.loc[before, 'app'].sum() / by_day.loc[before, 'rest'].sum()
print('k:', round(k, 3))

# A check on days the model has not seen: October after the drop.
print('October:', round(k * by_day.loc[after, 'rest'].sum(), 2), round(by_day.loc[after, 'app'].sum(), 2))

expected = k * by_day.loc[drop, 'rest'].sum()
actual = by_day.loc[drop, 'app'].sum()
print('drop:', round(expected, 2), round(actual, 2), round(expected - actual, 2))

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() by_day = sold.pivot_table(index='day', columns='channel', values='value', aggfunc='sum', fill_value=0) by_day['rest'] = by_day['web'] + by_day['phone'] day = by_day.index before = day < '2026-09-03' drop = (day >= '2026-09-03') & (day <= '2026-10-02') after = day > '2026-10-02' k = by_day.loc[before, 'app'].sum() / by_day.loc[before, 'rest'].sum() print('k:', round(k, 3)) # A check on days the model has not seen: October after the drop. print('October:', round(k * by_day.loc[after, 'rest'].sum(), 2), round(by_day.loc[after, 'app'].sum(), 2)) expected = k * by_day.loc[drop, 'rest'].sum() actual = by_day.loc[drop, 'app'].sum() print('drop:', round(expected, 2), round(actual, 2), round(expected - actual, 2))

▶ RunRestore the code

For every €1 from the website and the phone the app brings €0.641. For October after the drop the model says €3,688.92, and the truth is €3,614.60: an error of 2.1%. For the days from 3 September to 2 October the model expects €3,713.41, and the app brought €1,292.60. The loss is €2,420.81, €737 more than the naive comparison.

How exact is that? The share k over 30 days is not always the same. With the bootstrap you draw 30 normal days, 10,000 times, and see how much the loss moves:

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
by_day = sold.pivot_table(index='day', columns='channel', values='value', aggfunc='sum', fill_value=0)
by_day['rest'] = by_day['web'] + by_day['phone']
day = by_day.index
before = day < '2026-09-03'
drop = (day >= '2026-09-03') & (day <= '2026-10-02')
after = day > '2026-10-02'

app = by_day.loc[before, 'app'].to_numpy()
rest = by_day.loc[before, 'rest'].to_numpy()
actual = by_day.loc[drop, 'app'].sum()

rng = np.random.default_rng(42)
idx = rng.choice(len(app), size=(10_000, 30))
ks = app[idx].sum(axis=1) / rest[idx].sum(axis=1)
lost = ks * by_day.loc[drop, 'rest'].sum() - actual
low, high = np.quantile(lost, [0.025, 0.975])
print(round(ks.min(), 3), round(ks.max(), 3))
print(round(low, 2), round(high, 2))

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() by_day = sold.pivot_table(index='day', columns='channel', values='value', aggfunc='sum', fill_value=0) by_day['rest'] = by_day['web'] + by_day['phone'] day = by_day.index before = day < '2026-09-03' drop = (day >= '2026-09-03') & (day <= '2026-10-02') after = day > '2026-10-02' app = by_day.loc[before, 'app'].to_numpy() rest = by_day.loc[before, 'rest'].to_numpy() actual = by_day.loc[drop, 'app'].sum() rng = np.random.default_rng(42) idx = rng.choice(len(app), size=(10_000, 30)) ks = app[idx].sum(axis=1) / rest[idx].sum(axis=1) lost = ks * by_day.loc[drop, 'rest'].sum() - actual low, high = np.quantile(lost, [0.025, 0.975]) print(round(ks.min(), 3), round(ks.max(), 3)) print(round(low, 2), round(high, 2))

▶ RunRestore the code

Over 30 random normal days k is between 0.457 and 0.906. The 95% interval for the loss is from €1,802.46 to €3,142.14. In the report that is “about €2,400, between €1,800 and €3,150”. The estimate is rough, but even the lower bound is above the naive €1,683.90.

More precisely, if you are curious

The model app = k × (web + phone) has one feature and one number, like a linear regression without an intercept. Its check is as in the module on models: you learn it on some days (before 3 September) and measure it on others (after 2 October). It assumes the channels move together, and the check on October confirms it. If app customers had moved to the website in September, the model would overestimate the loss: it would count as lost orders that actually came through the website. The website in September is +3.2% on August, within the usual movement, so most of the lost orders did not go elsewhere.

How many orders did the app lose? Build the same model for the number of orders: k is app / (web + phone) on the days before 3 September, expected is how many app orders there would have been from 3 September to 2 October, actual how many there were, and lost_pct what part of the expected ones was lost, in percent.

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
counts = sold.pivot_table(index='day', columns='channel', values='id', aggfunc='count', fill_value=0)

counts['rest'] = counts['web'] + counts['phone']
day = counts.index
before = day < '2026-09-03'
drop = (day >= '2026-09-03') & (day <= '2026-10-02')
k = ...
expected = ...
actual = ...
lost_pct = ...
print(round(expected, 1), actual, round(lost_pct, 1))

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() counts = sold.pivot_table(index='day', columns='channel', values='id', aggfunc='count', fill_value=0) counts['rest'] = counts['web'] + counts['phone'] day = counts.index before = day < '2026-09-03' drop = (day >= '2026-09-03') & (day <= '2026-10-02') k = ... expected = ... actual = ... lost_pct = ... print(round(expected, 1), actual, round(lost_pct, 1))

Check▶ Run

The idea

The report

An analysis ends with a story someone can read in a minute. Three sentences, one chart, the limits.

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
by_day = sold.pivot_table(index='day', columns='channel', values='value', aggfunc='sum', fill_value=0)
by_day['rest'] = by_day['web'] + by_day['phone']
day = by_day.index
before = day < '2026-09-03'
drop = (day >= '2026-09-03') & (day <= '2026-10-02')
after = day > '2026-10-02'

roll = by_day[['web', 'app', 'phone']].rolling(7, center=True).mean().loc['2026-06-01':]
fig, ax = plt.subplots(figsize=(8, 3.5))
for ch in ['web', 'app', 'phone']:
    ax.plot(roll.index, roll[ch], label=ch)
ax.axvspan(pd.Timestamp('2026-09-03'), pd.Timestamp('2026-10-02'), color='C3', alpha=0.15)
ax.set_title('The app loses orders from 3 Sep to 2 Oct')
ax.set_xlabel('day')
ax.set_ylabel('revenue per day, € (7-day average)')
ax.set_ylim(bottom=0)
ax.legend(loc='lower left')

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() by_day = sold.pivot_table(index='day', columns='channel', values='value', aggfunc='sum', fill_value=0) by_day['rest'] = by_day['web'] + by_day['phone'] day = by_day.index before = day < '2026-09-03' drop = (day >= '2026-09-03') & (day <= '2026-10-02') after = day > '2026-10-02' roll = by_day[['web', 'app', 'phone']].rolling(7, center=True).mean().loc['2026-06-01':] fig, ax = plt.subplots(figsize=(8, 3.5)) for ch in ['web', 'app', 'phone']: ax.plot(roll.index, roll[ch], label=ch) ax.axvspan(pd.Timestamp('2026-09-03'), pd.Timestamp('2026-10-02'), color='C3', alpha=0.15) ax.set_title('The app loses orders from 3 Sep to 2 Oct') ax.set_xlabel('day') ax.set_ylabel('revenue per day, € (7-day average)') ax.set_ylim(bottom=0) ax.legend(loc='lower left')

▶ RunRestore the code

1. What: revenue for September is 19.1% below August, because from 3 September to 2 October the app had about two thirds fewer orders; the website and the phone are normal, and from 3 October the app is normal too.
2. How much: the loss is about €2,400 (between €1,800 and €3,150), about 240 orders.
3. What to do: check what changed in the app around 3 September (an update, the cart, the payment) and add an alert for when one channel’s share drops.

The limits. The data says when and where: in the app, from 3 September. It does not say why. An app update around 3 September is a hypothesis, not a fact: the app team checks it, with the cart logs and the payment errors for that date. It could be something else too: the app store, an expired certificate, a change at the payment provider. The chart cannot tell these causes apart. That is why the report says “check”, not “this was it”.

The ads, the supplier and the prices are not the cause. None of the manager’s three ideas would have brought the revenue back.

1. Which sentence belongs in the report?

“The new version of the app from 3 September broke the cart.”“Revenue fell by 19.1%, p = 0.0074, k = 0.641.”“From 3 September to 2 October the app had about 2/3 fewer orders; web and phone are normal. The cause is not in the data: check the update around 3 September.”

Check

Exercise

An alert for next time

The drop lasted a month because nobody was watching the channel. An alert rule should catch the drop fast and stay quiet in normal times. An alert that fires every week, nobody watches.

Build the alert. week is the number of orders per channel over the last 7 days (rolling(7).sum()), app_share the share of app in them, and alerts the days when app_share is under 0.25. How many alarms are there before 3 September, and when is the first one?

Pythondata: the shop tables

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

orders = pd.read_csv('year_orders.csv')
items = pd.read_csv('year_items.csv')
orders['ordered_at'] = pd.to_datetime(orders['ordered_at'])
items['revenue'] = items['quantity'] * items['unit_price']
orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum())
# Revenue does not count cancelled orders.
sold = orders[orders['status'] != 'cancelled']
sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m')
sold['day'] = sold['ordered_at'].dt.normalize()
counts = sold.pivot_table(index='day', columns='channel', values='id', aggfunc='count', fill_value=0)

week = ...
app_share = ...
alerts = ...
print(len(alerts.loc[:'2026-09-02']), alerts.index[0].date())

import numpy as np import pandas as pd import matplotlib.pyplot as plt orders = pd.read_csv('year_orders.csv') items = pd.read_csv('year_items.csv') orders['ordered_at'] = pd.to_datetime(orders['ordered_at']) items['revenue'] = items['quantity'] * items['unit_price'] orders['value'] = orders['id'].map(items.groupby('order_id')['revenue'].sum()) # Revenue does not count cancelled orders. sold = orders[orders['status'] != 'cancelled'] sold['month'] = sold['ordered_at'].dt.strftime('%Y-%m') sold['day'] = sold['ordered_at'].dt.normalize() counts = sold.pivot_table(index='day', columns='channel', values='id', aggfunc='count', fill_value=0) week = ... app_share = ... alerts = ... print(len(alerts.loc[:'2026-09-02']), alerts.index[0].date())

Check▶ Run

The idea

What next

The course ends here, and the work with data goes on. Where to:

• Notebooks. Do this project in JupyterLab, on your own computer: cell by cell, with the output under each. The notebook is the report too: code, charts and text in one place.
• Bigger data. pandas keeps everything in memory and works on one core. Polars has the same ideas (groups, joins, dates), but uses all cores and reads files bigger than memory: pl.read_csv('year_orders.csv').group_by('channel').len() counts the orders by channel.
• Dashboards. The alert from the exercise is best on a dashboard that updates by itself. Streamlit and Dash make a web dashboard from a Python script, with charts and filters.
• Charts. seaborn and plotly are layers over the ideas of matplotlib: seaborn gives statistical charts in one line, and plotly interactive ones, with zoom and tooltips.
• Databases. In a real shop the orders are in a database, not in a CSV. pd.read_sql reads the result of an SQL query straight into a DataFrame. SQL is taught in the course Databases and SQL.

The path stays the same: the question, the data, the cleaning, the exploration, the conclusion with its limits, and the story.

Test

Check yourself

1. Why is the first thing in the project the question “what will we decide with the answer”?

Because the manager wants a reportDifferent causes lead to different decisions: ads, prices, the supplier or a fix; the question tells you what to look forBecause without a question the code does not run

2. The sum of revenue on Wednesdays is +18.0% from August to September, and the average revenue on a Wednesday is −5.6%. Which one do you trust?

The average: September has 5 Wednesdays and August 4, and the sum compares a different number of daysThe sum: it is the whole revenueBoth: Wednesday was strong

3. Why does the model predict the app from web and phone, instead of just assuming September should have been like August?

Because August was wrongBecause the model is more complexWeb and phone are not broken and carry everything else for the same days: the season, the weekday, the growth; “like August” gives €1,683.90 and the model €2,420.81

4. The loss is €2,420.81, with a 95% interval from €1,802.46 to €3,142.14. How do you say it in the report?

“€2,420.81”“About €2,400, between €1,800 and €3,150”“Somewhere between €0 and €10,000”

5. The alert is “the share of app in the orders of the last 7 days is under 0.25”. Why 7 days and not every day on its own?

Because the alarm comes earlierOne day is noisy: with the daily share there would have been 11 false alarms before 3 September, and with 7 days not oneBecause 7 is the usual number

Check

Start the lesson over

Related lessons

• Builds on: Confidence intervals · Testing a hypothesis · Correlation and cause · Models: learning from examples
• Tasks on this topic: Project: why revenue fell