eCommerce Case Study Capstone

Case brief:
A merchandising lead wants a monthly country view of order performance and a clear read on how net revenue is trending month over month.

Your job is to produce reliable KPI outputs using fanout-safe query design.

Return one row per month_start x country with:

• orders
• revenue
• return_amount
• net_revenue
• aov
• return_share

Build a monthly net-revenue trend for completed orders. Attribute returns to the month of the original order (not the return month). Return one row per month with:

• month_start
• net_revenue
• prev_net_revenue
• net_revenue_change

For the first month, prev_net_revenue and net_revenue_change are NULL.

Findings contract: once the query passes, state in one sentence which month moved the most and one plausible driver you would investigate first.

Explain briefly:

• why you chose your aggregation grain
• where fanout could have happened and how you prevented it
• which KPI definitions require product/finance sign-off before production use