GroupBy, Weighted Ratios, and Reliable KPI Tables

Module 4: Combining and Summarizing Data · Lecture 15 of 32

Build a channel KPI table that retains numerators, denominators, and an explicit order population. Learn why an average of averages is often wrong even when every individual average was calculated correctly.

What you will learn

Aggregate several measures with named aggregations.
Calculate AOV from total revenue and eligible order counts.
Reconcile grouped totals and avoid average-of-averages errors.

Understand the problem

Split, aggregate, interpret

GroupBy partitions rows by keys and applies an aggregation to each group. Named aggregation gives the output a stable schema: orders, net_paise, refunds, or customers. size counts rows; count counts nonmissing values in a chosen column; nunique counts distinct values and usually excludes missing IDs. Choose the operation that matches the metric, not whichever one produces a convenient number.

Keep the denominator beside the rate

Net AOV is sum of net merchandise revenue divided by non-cancelled order count. Build the channel table from that eligible population. Retain orders and net_paise even if the final chart displays only AOV. A rate without its population size can make a tiny group look as persuasive as a large one.

Weighted ratios

If one group has one order and another has one hundred, averaging the two group AOVs gives equal weight to unequal populations. The overall AOV is the sum of group revenue divided by the sum of group orders. This is equivalent to weighting group AOVs by their order counts. The same principle applies to conversion rates, defect rates, and many other business ratios.

Totals do not establish attribution

A paid-channel revenue total is not return on advertising spend because this dataset contains no spend or incremental effect. Different customers and product mixes can explain differences between channels. The correct KPI table is a descriptive starting point for investigation, not a causal conclusion.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 15 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

orders = load_orders()
eligible = orders.loc[orders['status'].ne('cancelled')]
kpis = eligible.groupby('channel', dropna=False).agg(
    orders=('order_id', 'size'), net_paise=('net_paise', 'sum'),
    customers=('customer_id', 'nunique'), refunds_paise=('refunded_paise', 'sum'))
kpis['aov_inr'] = kpis['net_paise'] / kpis['orders'] / 100
overall = eligible['net_paise'].sum() / len(eligible) / 100
weighted = np.average(kpis['aov_inr'].astype(float), weights=kpis['orders'])
print(kpis.round(2).to_string())
print('Overall AOV INR:', round(float(overall), 2))
assert np.isclose(weighted, overall)
assert int(kpis['orders'].sum()) == len(eligible)
assert int(kpis['net_paise'].sum()) == int(orders['net_paise'].sum())

Verified output

         orders  net_paise  customers  refunds_paise  aov_inr
channel                                                      
email        75    8045280         47         826360   1072.7
organic      75    8311030         47         692120  1108.14
paid         73    7596310         47        1022800  1040.59
unknown       1     159800          1              0   1598.0
Overall AOV INR: 1076.45

Walk through the result

The eligible table includes completed and returned orders while removing cancellations. Channel unknown remains a real reporting group. Customer counts are not additive across channels because one customer can purchase through several channels; order counts and net revenue are additive under this grain. The weighted-average assertion verifies the ratio contract independently from the grouped presentation.

Common mistakes and diagnosis

Do not sum distinct-customer counts across overlapping groups. Do not use count(customer_id) as order count when anonymous orders exist. Do not average daily rates without weighting them by their denominators. A group with zero eligible orders needs an undefined ratio rather than an invented zero AOV. Rounding should happen only in presentation, after reconciliation.

Practice assignment

Construct two artificial groups: one order worth 100 INR and nine orders totaling 1800 INR. Compare the unweighted average of their AOVs with the correct overall AOV. Explain which population the unweighted result implicitly weights equally.

Worked solution and explanation

Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.

toy = pd.DataFrame({'orders': [1, 9], 'net_inr': [100, 1800]})
toy['aov'] = toy['net_inr'] / toy['orders']
wrong = toy['aov'].mean()
correct = toy['net_inr'].sum() / toy['orders'].sum()
assert wrong == 150 and correct == 190
print('Average of averages:', wrong, 'Correct AOV:', correct)

Check your understanding

Which columns in the channel table can be summed into a valid overall metric?

Answer guide

Order count, net revenue, and recorded refund amounts are additive because each accepted order belongs to one channel. Distinct customers are not additive because a customer may appear in several channels. AOV is a ratio and must be recomputed from summed revenue and summed eligible order counts, not summed or averaged blindly.

Reference and next step

pandas GroupBy guide

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next