E-Commerce Returns Analysis — ShopEase

ShopEase, an e-commerce seller, needs to analyze their product returns data to identify patterns and reduce losses. Students use Google Sheets functions like SORT, FILTER, UNIQUE, and conditional formatting to categorize return reasons, calculate refund amounts, and create actionable insights for the operations team.

CONTEXT: ShopEase has been experiencing high return rates in certain categories. The operations team needs a clear analysis of return patterns to take corrective action.

TASK 1 — DATASET: Create a 20-row table in tab 'Day4' with columns: Order ID, Product Name, Category, Return Reason (Damaged/Wrong Item/Not as Expected/Changed Mind), Refund Amount, Return Date, Customer City.

TASK 2 — SORT & FILTER: Use =SORT(FILTER(data, condition)) to show only returns above ₹500, sorted by amount descending.

TASK 3 — UNIQUE COUNTS: Use =UNIQUE() and =COUNTIF() to count returns by reason and by category.

TASK 4 — CONDITIONAL FORMATTING: Apply color scales to refund amounts (green=low, red=high). Highlight duplicate Order IDs.

TASK 5 — SUMMARY DASHBOARD: Create a one-page summary with: Total Returns, Total Refund Amount, Top Return Reason, Top Return Category, Average Refund per Return.

BONUS: Use =SPARKLINE() to show daily return trend.