Overview

Analyze the UCI Online Retail II dataset (UK-based online store transactions, 2009–2011) and build a professional Excel dashboard that answers key business questions: Which countries and products drive profit? Which customers are most valuable? What are the YoY trends? Your work must be 100% Excel-based (Power Query allowed for cleaning; analysis via Pivot Tables/Charts/Slicers/Formulas).

Dataset

• Name: UCI Online Retail II
• Fields (typical): Invoice, StockCode, Description, Quantity, InvoiceDate, Price, Customer ID, Country
• Download (official): UCI — Online Retail II
• Import the Excel/CSV into your workbook and keep all analysis inside a single .xlsx.

What you must build (Excel only)

• Data Cleaning (Power Query or formulas): remove blanks/dupes/negatives (handle returns), unify dates, add calculated column Revenue = Quantity * Price.
• KPIs: Total Revenue, Total Orders, Total Customers, Avg. Order Value (AOV), Profit Proxy* (optional, documented), YoY Revenue & Customers.
• Pivot Analysis: by Country, Product (Description/StockCode), Month/Year, and Customer ID.
• Slicers/Filters: at least Country, Year, and Product Category (you may derive categories from Description keywords).
• Dashboard (single page): KPIs row, monthly trend chart, Top/Bottom 10 products, Country performance table with conditional formatting, Customer segments.
• NO external tools: strictly Excel (no Python, R, Power BI, Tableau, SQL).

*If cost data is not available, use Revenue-driven insights or clearly state your profit assumption/model.

Advanced (recommended)

• RFM Segmentation: build Recency/Frequency/Monetary scores per Customer (via formulas + Pivot), classify into segments (e.g., Champions / At Risk).
• Cohort Retention: monthly cohorts by first purchase month (Pivot with helper columns) to visualize retention over time.
• ABC Analysis: categorize products A/B/C by cumulative revenue contribution.
• Basket Check (basic): top product pairs (co-occurrence) using Pivot (no data mining add-ins required).

Required workbook structure

• 01_Raw — raw import (read-only)
• 02_Clean — cleaned table (Power Query output or formula-based)
• 03_Helper — calculated columns (Revenue, Month, Year, Category, RFM scores)
• 04_Pivots — all pivot tables/views
• 05_Dashboard — final dashboard (Slicers + Charts + KPIs)

Business questions to answer

• Which countries and products contribute most to revenue?
• Top/Bottom 10 products and customers by revenue — are discounts/returns affecting them?
• YoY/MoM trends for revenue and customers — seasonality patterns?
• What customer segments (RFM) should we prioritize next quarter?
• Two actionable recommendations for the next 90 days (pricing, inventory, targeting).

Submission package

• Workbook: online_retail_II_analysis.xlsx (≤ 30 MB) — all steps inside.
• One-pager (PDF/DOC): insights & recommended actions + notes on assumptions (e.g., profit).
• Optional (GIF/short video): 1–2 min walkthrough of the dashboard.