Capstone Part 2: Reconcile Channels, Months, and Customer Segments

Module 8: Complete Retail Analysis Capstone · Lecture 31 of 32

Build the analytical core of the project. Produce channel, monthly, and regional summaries, reconcile every additive amount, and use cohort methods only for customers whose identities are actually known.

What you will learn

Produce a complete validated report bundle.
Reconcile several views to one revenue total.
Distinguish additive amounts from overlapping customer counts.

Understand the problem

Establish one reference total

The fixed fixture's accepted net merchandise revenue is 24,112,420 paise, or 241,124.20 INR. Treat that as a reviewed test baseline for these exact synthetic bytes, not a target for future exports. The total follows the declared discount, refund, and cancellation rules and excludes the three rejected records.

Build views without changing the population

Channel totals retain unknown attribution. Regional totals use the validated customer join and retain Unknown region for the anonymous checkout. Monthly totals use local Asia/Kolkata calendar boundaries. Each view partitions accepted order revenue differently, so each must sum to the same reference total. The source does not contain advertising costs or item costs, so none of these tables is a profit or return-on-spend analysis.

Interpret rates separately

Channel AOV uses non-cancelled orders. Cancellation share uses all accepted orders. Customer retention uses identified, non-cancelled purchasing customers and first-observed cohorts. Those denominators are intentionally different. Preserve their counts in the output rather than exporting only a ratio column that cannot be audited.

Explain apparent disagreements

Customer counts across channels or months can overlap, so summing them does not yield a valid unique-customer total. Monthly totals differ partly because calendar months have different lengths and the generator creates deterministic patterns. If a regional view loses revenue, inspect unmatched keys and null-group policy before concluding that the source total is wrong.

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 31 --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

from pipeline import run_pipeline
result = run_pipeline(ROOT / 'data' / 'orders_raw.csv', OUT / 'capstone',
                      ROOT / 'data' / 'customers.csv')
orders = result['orders']
channels = result['channels']
monthly = result['monthly']
regions = result['regions']
reference = int(orders['net_paise'].sum())
assert reference == 24112420
assert int(channels['net_paise'].sum()) == reference
assert int(monthly['net_paise'].sum()) == reference
assert int(regions['net_paise'].sum()) == reference
print('Channel KPI table:')
print(channels.round(2).to_string(index=False))
print('Monthly KPI table:')
print(monthly.to_string(index=False))
print('All three views reconcile to INR', reference / 100)

Verified output

Channel KPI table:
channel  orders  net_paise  identified_customers  net_aov_inr
  email      75    8045280                    47       1072.7
organic      75    8311030                    47      1108.14
   paid      73    7596310                    47      1040.59
unknown       1     159800                     1       1598.0
Monthly KPI table:
  month  accepted_orders  eligible_orders  net_paise
2026-01               83               78    8664690
2026-02               75               70    6749790
2026-03               81               76    8697940
All three views reconcile to INR 241124.2

Walk through the result

The pipeline returns both analytical tables and written artifacts, making the same result usable for review, tests, and delivery. Four exact monetary assertions protect the common total across different partitions. A regional join error would be visible even if the channel view remained correct. The printed channel table keeps numerator and denominator beside AOV; the monthly table retains accepted and eligible counts so status policies remain inspectable.

Common mistakes and diagnosis

Do not sum regional AOVs or channel customer counts. Do not exclude Unknown solely to make a chart cleaner. Do not compare March to February without mentioning month length and source coverage. A revenue leader is not automatically the most profitable or most effective acquisition channel. Avoid applying the cohort denominator to an order-level rate.

Practice assignment

Add a month-by-channel revenue table and verify its row sums against monthly totals and column sums against channel totals. Then calculate revenue per calendar day by month, explicitly using 31, 28, and 31 days for this 2026 reporting window.

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.

local = orders['ordered_at'].dt.tz_convert('Asia/Kolkata')
cross = orders.assign(month=local.dt.strftime('%Y-%m')).pivot_table(
    index='month', columns='channel', values='net_paise', aggfunc='sum', fill_value=0, observed=True)
assert int(cross.to_numpy().sum()) == reference
for row in monthly.itertuples():
    assert int(cross.loc[row.month].sum()) == row.net_paise
days = pd.Series({'2026-01':31, '2026-02':28, '2026-03':31})
per_day = cross.sum(axis=1).astype(float).div(days) / 100
assert per_day.notna().all()
print('Net INR per calendar day:', per_day.round(2).to_dict())

Check your understanding

Why should all revenue partitions reconcile while customer partitions may not add up?

Answer guide

Each order contributes its amount to exactly one channel, region label, and month in the declared views. The amounts are additive across those disjoint partitions. A customer can place orders in several channels and months, so distinct-customer populations overlap. Recompute the overall unique count from customer IDs instead of summing subgroup distinct counts.

Reference and next step

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