{"metadata":{"kernelspec":{"name":"python3","display_name":"Python 3","language":"python"},"language_info":{"name":"python","version":"3.12.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"colab":{"provenance":[]}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport duckdb\nimport warnings\nwarnings.filterwarnings(\"ignore\")","metadata":{"id":"ybxcCdeAWP4c","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:33:18.583464Z","iopub.execute_input":"2026-09-15T10:33:18.583754Z","iopub.status.idle":"2026-09-15T10:33:19.915473Z","shell.execute_reply.started":"2026-09-15T10:33:18.583729Z","shell.execute_reply":"2026-09-15T10:33:19.914446Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import os\n\nfor dirname, dirnames, filenames in os.walk('/kaggle/input'):\n    if 'images' in dirnames:\n        dirnames.remove('images')\n        \n    for filename in filenames:\n        if filename.endswith('.csv'):\n            print(os.path.join(dirname, filename))","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:33:19.916840Z","iopub.execute_input":"2026-09-15T10:33:19.917224Z","iopub.status.idle":"2026-09-15T10:33:19.923833Z","shell.execute_reply.started":"2026-09-15T10:33:19.917205Z","shell.execute_reply":"2026-09-15T10:33:19.923210Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import os\ndef load_dataset(file_name):\n    kaggle_path = f\"/kaggle/input/competitions/h-and-m-personalized-fashion-recommendations/{file_name}\"\n    local_path = f\"dataset/{file_name}\"\n\n    if os.path.exists(kaggle_path):\n        print(f\"Loading '{file_name}' from Kaggle Cloud Environment...\")\n        df = pd.read_csv(kaggle_path)\n    elif os.path.exists(local_path):\n        print(f\"Loading '{file_name}' from Local Environment...\")\n        df = pd.read_csv(local_path)\n    else:\n        raise FileNotFoundError(f\"Dataset '{file_name}' not found. Please ensure it is in the correct directory.\")\n\n    print(f\"Successfully loaded '{file_name}' ({df.shape[0]} rows, {df.shape[1]} columns).\")\n    return df\n\narticles = load_dataset(\"articles.csv\")\ncustomers = load_dataset(\"customers.csv\")\ntransactions = load_dataset(\"transactions_train.csv\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:33:19.924570Z","iopub.execute_input":"2026-09-15T10:33:19.924735Z","iopub.status.idle":"2026-09-15T10:34:43.381925Z","shell.execute_reply.started":"2026-09-15T10:33:19.924718Z","shell.execute_reply":"2026-09-15T10:34:43.381280Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"pd.set_option('display.max_columns', None)\npd.options.display.float_format = '{:.2f}'.format","metadata":{"id":"x3LCoS9q5r4V","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.404756Z","iopub.execute_input":"2026-09-15T10:34:43.405750Z","iopub.status.idle":"2026-09-15T10:34:43.410693Z","shell.execute_reply.started":"2026-09-15T10:34:43.405724Z","shell.execute_reply":"2026-09-15T10:34:43.409698Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"print(articles.shape)\nprint(customers.shape)\nprint(transactions.shape)","metadata":{"id":"Jo_xfg-Gv9wE","outputId":"b3b4cf86-dd4f-4463-9935-02b1246af94b","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.413129Z","iopub.execute_input":"2026-09-15T10:34:43.413470Z","iopub.status.idle":"2026-09-15T10:34:43.433207Z","shell.execute_reply.started":"2026-09-15T10:34:43.413441Z","shell.execute_reply":"2026-09-15T10:34:43.432178Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"articles.head(2)","metadata":{"id":"DWfLlAyepvsz","outputId":"240268b8-cbc9-489c-cc78-c7a60bc1e264","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.434213Z","iopub.execute_input":"2026-09-15T10:34:43.434456Z","iopub.status.idle":"2026-09-15T10:34:43.487124Z","shell.execute_reply.started":"2026-09-15T10:34:43.434432Z","shell.execute_reply":"2026-09-15T10:34:43.485802Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"customers.head(2)","metadata":{"id":"8eHKWd78sV7j","outputId":"8699dcfe-b065-401b-a2ad-3735d5fa133d","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.488188Z","iopub.execute_input":"2026-09-15T10:34:43.488440Z","iopub.status.idle":"2026-09-15T10:34:43.498097Z","shell.execute_reply.started":"2026-09-15T10:34:43.488415Z","shell.execute_reply":"2026-09-15T10:34:43.496956Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"transactions.head(2)","metadata":{"id":"57swKfICv5ns","outputId":"bc73362f-2283-42e7-a80a-7b315f9692e5","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.499127Z","iopub.execute_input":"2026-09-15T10:34:43.499424Z","iopub.status.idle":"2026-09-15T10:34:43.519319Z","shell.execute_reply.started":"2026-09-15T10:34:43.499397Z","shell.execute_reply":"2026-09-15T10:34:43.518508Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"articles.dtypes","metadata":{"id":"ub5ePLuabxyi","outputId":"c71958c8-eaa5-40d1-85c6-b3479c275757","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.520158Z","iopub.execute_input":"2026-09-15T10:34:43.520357Z","iopub.status.idle":"2026-09-15T10:34:43.543579Z","shell.execute_reply.started":"2026-09-15T10:34:43.520339Z","shell.execute_reply":"2026-09-15T10:34:43.542219Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"transactions.dtypes","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.544606Z","iopub.execute_input":"2026-09-15T10:34:43.544848Z","iopub.status.idle":"2026-09-15T10:34:43.567699Z","shell.execute_reply.started":"2026-09-15T10:34:43.544805Z","shell.execute_reply":"2026-09-15T10:34:43.566750Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"articles.dtypes","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.568592Z","iopub.execute_input":"2026-09-15T10:34:43.568976Z","iopub.status.idle":"2026-09-15T10:34:43.591236Z","shell.execute_reply.started":"2026-09-15T10:34:43.568952Z","shell.execute_reply":"2026-09-15T10:34:43.590413Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# Data Cleaning","metadata":{}},{"cell_type":"code","source":"# Filter and retain business-relevant attributes from the articles dataset\n\narticles = articles[['article_id', 'prod_name', 'product_type_name',\n                      'product_group_name', 'graphical_appearance_name',\n                      'colour_group_name', 'perceived_colour_value_name',\n                      'perceived_colour_master_name', 'department_name',\n                      'index_name', 'index_group_name', 'section_name',\n                      'garment_group_name', 'detail_desc']]","metadata":{"id":"f0PN661V-Yx6","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.592113Z","iopub.execute_input":"2026-09-15T10:34:43.592329Z","iopub.status.idle":"2026-09-15T10:34:43.631887Z","shell.execute_reply.started":"2026-09-15T10:34:43.592306Z","shell.execute_reply":"2026-09-15T10:34:43.630216Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"customers = customers.drop(columns=[\"FN\", \"Active\", \"postal_code\"])","metadata":{"id":"Qfiw_aSyD_2p","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.633212Z","iopub.execute_input":"2026-09-15T10:34:43.633696Z","iopub.status.idle":"2026-09-15T10:34:43.688770Z","shell.execute_reply.started":"2026-09-15T10:34:43.633668Z","shell.execute_reply":"2026-09-15T10:34:43.688030Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## Filter Data\n> ### **Temporal Scope:** Filtered to the latest 12 months (~14.93M rows) to optimize RAM while preserving a full annual seasonality cycle.","metadata":{}},{"cell_type":"code","source":"# Cast raw transaction timestamps to datetime format for temporal analysis\n\ntransactions[\"t_dat\"] = pd.to_datetime(transactions[\"t_dat\"])\ntransactions.dtypes","metadata":{"id":"cN1x4W8l875D","outputId":"8cd4f92e-8329-43ed-a40f-579de3d4a6b1","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:43.691969Z","iopub.execute_input":"2026-09-15T10:34:43.692207Z","iopub.status.idle":"2026-09-15T10:34:46.061018Z","shell.execute_reply.started":"2026-09-15T10:34:43.692188Z","shell.execute_reply":"2026-09-15T10:34:46.060135Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Establish 1-year trailing observation window to preserve full annual seasonality\nmax_date = transactions['t_dat'].max()                                              # latest date\nstart_date = max_date - pd.DateOffset(years=1)\n\n# Subset transactions to the trailing 12-month period to optimize memory without seasonal distortion\ntransactions = transactions[transactions[\"t_dat\"] >= start_date]\n\ntransactions.shape","metadata":{"id":"TzGhF0tP8eyh","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:46.061847Z","iopub.execute_input":"2026-09-15T10:34:46.062084Z","iopub.status.idle":"2026-09-15T10:34:46.658179Z","shell.execute_reply.started":"2026-09-15T10:34:46.062066Z","shell.execute_reply":"2026-09-15T10:34:46.657411Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Standardize unassigned color descriptors to uniform 'Unknown' string labels\n\narticles[\"perceived_colour_value_name\"] = articles[\"perceived_colour_value_name\"].replace(\"Undefined\", \"Unknown\")\narticles[\"perceived_colour_master_name\"] = articles[\"perceived_colour_master_name\"].replace(\"undefined\", \"Unknown\")","metadata":{"id":"7jri0cAEBTpb","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:46.658986Z","iopub.execute_input":"2026-09-15T10:34:46.659179Z","iopub.status.idle":"2026-09-15T10:34:46.679693Z","shell.execute_reply.started":"2026-09-15T10:34:46.659164Z","shell.execute_reply":"2026-09-15T10:34:46.678282Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Cast string 'None' literals to nulls and harmonize fragmented department categories\n\narticles = articles.replace({None: np.nan, \"None\": np.nan})\narticles[\"department_name\"] = articles[\"department_name\"].replace(\"Trousers\", \"Trouser\")\n\narticles[\"department_name\"] = articles[\"department_name\"].replace({\n    'dress': 'Dresses',\n    'Dress': 'Dresses',\n    'dresses': 'Dresses'\n})","metadata":{"id":"9X_jgHtTN6Bt","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:46.680836Z","iopub.execute_input":"2026-09-15T10:34:46.681183Z","iopub.status.idle":"2026-09-15T10:34:46.871575Z","shell.execute_reply.started":"2026-09-15T10:34:46.681154Z","shell.execute_reply":"2026-09-15T10:34:46.870712Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Convert non-standard 'NONE' string values to null identifiers across customer attributes\n\ncustomers[\"fashion_news_frequency\"] = customers[\"fashion_news_frequency\"].replace(\"NONE\", np.nan)\ncustomers = customers.replace({None: np.nan})","metadata":{"id":"buArmEtfBTsM","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:46.873054Z","iopub.execute_input":"2026-09-15T10:34:46.873309Z","iopub.status.idle":"2026-09-15T10:34:47.362604Z","shell.execute_reply.started":"2026-09-15T10:34:46.873292Z","shell.execute_reply":"2026-09-15T10:34:47.361862Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Cast customer age to nullable integer and confirm dataset dimensions\n\ncustomers[\"age\"] = customers[\"age\"].astype(\"Int64\")\ncustomers.shape","metadata":{"id":"ZP9wVA30N6Nm","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:47.363305Z","iopub.execute_input":"2026-09-15T10:34:47.363538Z","iopub.status.idle":"2026-09-15T10:34:47.501246Z","shell.execute_reply.started":"2026-09-15T10:34:47.363520Z","shell.execute_reply":"2026-09-15T10:34:47.499941Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"customers.isnull().sum()","metadata":{"id":"2RzMTjQDy3G_","outputId":"debc77d8-3417-476b-bcef-d5db0afb82fb","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:47.502488Z","iopub.execute_input":"2026-09-15T10:34:47.502723Z","iopub.status.idle":"2026-09-15T10:34:47.641649Z","shell.execute_reply.started":"2026-09-15T10:34:47.502700Z","shell.execute_reply":"2026-09-15T10:34:47.640578Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Impute negligible club status nulls with mode ('ACTIVE') and classify missing newsletter preferences as 'Unsubscribed'\n\ncustomers[\"club_member_status\"] = customers[\"club_member_status\"].fillna(customers[\"club_member_status\"].mode()[0])\ncustomers[\"fashion_news_frequency\"] = customers[\"fashion_news_frequency\"].fillna(\"Unsubscribed\")","metadata":{"id":"Wmhi2Z3vDDDG","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:47.643113Z","iopub.execute_input":"2026-09-15T10:34:47.643721Z","iopub.status.idle":"2026-09-15T10:34:47.910951Z","shell.execute_reply.started":"2026-09-15T10:34:47.643688Z","shell.execute_reply":"2026-09-15T10:34:47.910040Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# FEATURE ENGINEERING","metadata":{"id":"8I8ZIc_yT7-e"}},{"cell_type":"code","source":"customers.isnull().sum()","metadata":{"id":"leYMxnUrELz-","outputId":"f58b6684-5d93-4769-bef1-2f53881bb22e","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:47.911816Z","iopub.execute_input":"2026-09-15T10:34:47.912082Z","iopub.status.idle":"2026-09-15T10:34:48.060821Z","shell.execute_reply.started":"2026-09-15T10:34:47.912063Z","shell.execute_reply":"2026-09-15T10:34:48.060093Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Discretize continuous age into business cohorts and label unrecorded profiles as 'Unknown'\nbins = [0, 17, 25, 35, 50, 100]\nlabels = [\"Teenagers\", \"Young Adults\", \"Adults\", \"Middle-Aged\", \"Seniors\"]\n\ncustomers[\"age_group\"] = pd.cut(customers[\"age\"], bins=bins, labels=labels)\ncustomers[\"age_group\"] = customers[\"age_group\"].astype(str).replace(\"nan\", \"Unknown\")","metadata":{"id":"VWgWigQjT12d","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:48.061688Z","iopub.execute_input":"2026-09-15T10:34:48.061850Z","iopub.status.idle":"2026-09-15T10:34:48.195512Z","shell.execute_reply.started":"2026-09-15T10:34:48.061834Z","shell.execute_reply":"2026-09-15T10:34:48.194543Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Denormalize normalized transaction pricing into standard currency units rounded to 2 decimals\ntransactions[\"price\"] = (transactions[\"price\"] * 1000).round(2)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:48.196447Z","iopub.execute_input":"2026-09-15T10:34:48.196697Z","iopub.status.idle":"2026-09-15T10:34:48.266576Z","shell.execute_reply.started":"2026-09-15T10:34:48.196670Z","shell.execute_reply":"2026-09-15T10:34:48.265589Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Decompose purchase timestamps into granular calendar dimensions for trend and seasonality analysis\ntransactions[\"year\"] = transactions[\"t_dat\"].dt.year\ntransactions[\"month\"] = transactions[\"t_dat\"].dt.month\n\n# Fast vector mapping\nmonth_map = {1: 'January', 2: 'February', 3: 'March', 4: 'April', 5: 'May', 6: 'June', \n             7: 'July', 8: 'August', 9: 'September', 10: 'October', 11: 'November', 12: 'December'}\nday_map = {0: 'Monday', 1: 'Tuesday', 2: 'Wednesday', 3: 'Thursday', 4: 'Friday', 5: 'Saturday', 6: 'Sunday'}\n\ntransactions[\"month_name\"] = transactions[\"month\"].map(month_map)\ntransactions[\"day_name\"] = transactions[\"t_dat\"].dt.dayofweek.map(day_map)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:48.267624Z","iopub.execute_input":"2026-09-15T10:34:48.267889Z","iopub.status.idle":"2026-09-15T10:34:49.529902Z","shell.execute_reply.started":"2026-09-15T10:34:48.267848Z","shell.execute_reply":"2026-09-15T10:34:49.529041Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"duckdb.query(\"DESCRIBE transactions\").df()","metadata":{"id":"NFvU26xCHLGF","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:49.530787Z","iopub.execute_input":"2026-09-15T10:34:49.531053Z","iopub.status.idle":"2026-09-15T10:34:49.775660Z","shell.execute_reply.started":"2026-09-15T10:34:49.531028Z","shell.execute_reply":"2026-09-15T10:34:49.774931Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"transactions.describe()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:49.776776Z","iopub.execute_input":"2026-09-15T10:34:49.777007Z","iopub.status.idle":"2026-09-15T10:34:51.348631Z","shell.execute_reply.started":"2026-09-15T10:34:49.776985Z","shell.execute_reply":"2026-09-15T10:34:51.347757Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# 📌 ***Problem Statement***\n\n---\n\n## ***H&M's business team wants to analyze sales growth, seasonal demand patterns, and customer demographic performance across its product categories and sales channels. This analysis aims to optimize inventory planning, evaluate online vs offline channel performance, and improve customer retention strategies.***\n---","metadata":{"id":"t9tyTUZ8gdJo"}},{"cell_type":"markdown","source":"## *Ques1. Calculate fundamental e-commerce performance metrics Total Revenue, Total Orders, and Average Order Value (AOV).*","metadata":{"id":"iClIDbfYgw6b"}},{"cell_type":"code","source":"pd.options.display.float_format = '{:.2f}'.format\n\noverview = \"\"\"\n  SELECT\n    ROUND(SUM(price), 2) AS total_revenue,\n    COUNT(article_id) AS total_orders,\n    ROUND(AVG(price), 2) AS avg_order_value,\n    COUNT(DISTINCT customer_id) AS total_active_customers\n  FROM\n    transactions;\n  \"\"\"\noverview = duckdb.query(overview).df()\noverview","metadata":{"id":"dxdm5JoEg3Kv","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:51.349444Z","iopub.execute_input":"2026-09-15T10:34:51.349632Z","iopub.status.idle":"2026-09-15T10:34:51.923343Z","shell.execute_reply.started":"2026-09-15T10:34:51.349614Z","shell.execute_reply":"2026-09-15T10:34:51.922421Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 📊 Executive Summary: Core Performance KPIs\n\n---\n\n* **Revenue Scale:** Total revenue reached **\\$421.46M** across **14.93M** total transactions.\n* **Customer Engagement:** A strong customer base of **~995K active unique buyers** recorded across the analyzed timeframe.\n* **Basket Value:** The business maintains an **Average Order Value (AOV) of \\$28.22**, reflecting high-volume, budget-friendly retail basket sizes.\n\n---\n\n> 💡 **Strategic Action**  \n> Focus on upselling and product bundling strategies (e.g., \"Complete the Look\" or minimum cart threshold for free shipping) to drive the **\\$28.22 AOV** toward the \\$35+ range, leveraging the massive **14.93M** transaction volume.","metadata":{}},{"cell_type":"markdown","source":"## *Ques2. Analyze revenue distribution by product color to identify the top 10 most profitable master colors.*","metadata":{}},{"cell_type":"code","source":"color_p = \"\"\"\n    SELECT \n        a.perceived_colour_master_name as color,\n        ROUND(SUM(t.price), 2) AS price\n    FROM \n        transactions t\n    JOIN \n        articles a\n        ON a.article_id = t.article_id\n    GROUP BY\n        a.perceived_colour_master_name\n    ORDER BY\n        price DESC\n    LIMIT\n        10\n    \"\"\"\n\ncolor_p = duckdb.query(color_p).df()\ncolor_p","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:51.924668Z","iopub.execute_input":"2026-09-15T10:34:51.925240Z","iopub.status.idle":"2026-09-15T10:34:52.260833Z","shell.execute_reply.started":"2026-09-15T10:34:51.925215Z","shell.execute_reply":"2026-09-15T10:34:52.259795Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = color_p.sort_values('price', ascending=True).reset_index(drop=True)\nrev_m = df['price'] / 1e6\ntotal_rev = rev_m.sum()\n\ncolor_map = {\n    'Black': '#09090B', 'Blue': '#1D4ED8', 'White': '#CBD5E1',\n    'Beige': '#C2A684', 'Grey': '#6B7280', 'Pink': '#DB2777',\n    'Green': '#15803D', 'Red': '#B91C1C', 'Khaki green': '#556B2F', 'Brown': '#78350F'\n}\ndot_colors = [color_map.get(c, '#64748B') for c in df['color']]\n\nfig, ax = plt.subplots(figsize=(9.5, 4.8), dpi=120)\n\n# Stems & Markers\nax.hlines(y=df['color'], xmin=0, xmax=rev_m, color='#E2E8F0', lw=2.5, zorder=2)\nax.scatter(rev_m, df['color'], color=dot_colors, s=110, edgecolor='#1E293B', lw=0.8, zorder=3)\n\n# Data Labels: Revenue ($M) + Share (%)\nfor i, val in enumerate(rev_m):\n    pct = (val / total_rev) * 100\n    ax.text(val + 2.5, i, f\"${val:.1f}M ({pct:.1f}%)\", va='center', \n            fontsize=8.5, fontweight='bold', color='#1E293B')\n\nax.set_xlim(0, rev_m.max() * 1.22)\nax.set_xlabel('Total Revenue ($ Millions)', fontweight='bold', fontsize=9)\nax.spines[['top', 'right', 'left']].set_visible(False)\nax.grid(axis='x', ls=':', alpha=0.3)\n\nplt.title('H&M Top 10 Product Colors: Revenue Distribution & Share', fontweight='bold', fontsize=11, loc='left', pad=12)\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:52.261855Z","iopub.execute_input":"2026-09-15T10:34:52.262089Z","iopub.status.idle":"2026-09-15T10:34:52.533763Z","shell.execute_reply.started":"2026-09-15T10:34:52.262067Z","shell.execute_reply":"2026-09-15T10:34:52.532863Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 🎨 Color Palette Analysis: Revenue Distribution\n\n---\n\n* **Core Driver:** **Black** dominates inventory demand, generating **$146.8M (38.1% share)** of total top-10 color revenue—nearly **2.7x** the second-highest color.\n* **Neutral Dominance:** Neutral tones (**Black, Blue, White, Beige, Grey**) collectively contribute over **80%** of all major apparel sales.\n* **Accent Colors:** Seasonal and bold shades (**Pink, Green, Red, Khaki, Brown**) represent long-tail volume, each contributing **under 5%**.\n\n---\n\n> 💡 **Strategic Action**  \n> Protect baseline margins by prioritizing inventory allocation for neutral shades (especially Black & Blue) to avoid stockouts, while using accent shades primarily as seasonal, limited-run collections to minimize discount markdown risks.","metadata":{}},{"cell_type":"markdown","source":"## *Ques3. Analyze the total revenue contribution across different index names to identify the highest revenue-generating product categories.*","metadata":{}},{"cell_type":"code","source":"top_index = \"\"\"\n    SELECT \n        a.index_name,\n        ROUND(SUM(t.price) / 1000000.0, 2) AS total_revenue_million\n    FROM \n        transactions t\n    JOIN \n        articles a\n        ON a.article_id = t.article_id\n    GROUP BY\n        index_name\n    ORDER BY\n        total_revenue_million DESC\n\"\"\"\n\ntop_index = duckdb.query(top_index).df()\ntop_index","metadata":{"execution":{"iopub.status.busy":"2026-09-15T10:34:52.534617Z","iopub.execute_input":"2026-09-15T10:34:52.534841Z","iopub.status.idle":"2026-09-15T10:34:52.901357Z","shell.execute_reply.started":"2026-09-15T10:34:52.534822Z","shell.execute_reply":"2026-09-15T10:34:52.900497Z"},"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Cumulative share calculation\ndf_pareto = top_index.copy()\ndf_pareto[\"cumulative_pct\"] = (\n    df_pareto[\"total_revenue_million\"].cumsum() / df_pareto[\"total_revenue_million\"].sum()\n) * 100\n\nfig, ax1 = plt.subplots(figsize=(10, 4.5))\n\n# Primary Axis: Bars (Volume/Revenue)\nbars = ax1.bar(\n    df_pareto[\"index_name\"],\n    df_pareto[\"total_revenue_million\"],\n    color=\"#2B5C8F\",\n    width=0.55,\n    label=\"Revenue ($M)\",\n)\nax1.set_ylabel(\"Revenue ($ Millions)\", fontsize=10, fontweight=\"bold\")\nax1.set_xticklabels(df_pareto[\"index_name\"], rotation=30, ha=\"right\")\n\n# Secondary Axis: Line (% Contribution)\nax2 = ax1.twinx()\nax2.plot(\n    df_pareto[\"index_name\"],\n    df_pareto[\"cumulative_pct\"],\n    color=\"#D95F0E\",\n    marker=\"o\",\n    linewidth=2,\n    label=\"Cumulative Share %\",\n)\nax2.set_ylabel(\"Cumulative Share (%)\", fontsize=10, fontweight=\"bold\")\nax2.set_ylim(0, 110)\n\n# 80% Benchmark Line\nax2.axhline(80, color=\"gray\", linestyle=\"--\", alpha=0.7)\nax2.text(\n    0,\n    82,\n    \"80% Revenue Cutoff\",\n    color=\"gray\",\n    fontsize=9,\n    style=\"italic\",\n    fontweight=\"bold\",\n)\n\nplt.title(\n    \"H&M Revenue Contribution & Pareto Curve by Index\",\n    fontsize=12,\n    fontweight=\"bold\",\n    pad=15,\n)\nax1.grid(axis=\"y\", linestyle=\"--\", alpha=0.3)\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:52.902553Z","iopub.execute_input":"2026-09-15T10:34:52.903013Z","iopub.status.idle":"2026-09-15T10:34:53.141762Z","shell.execute_reply.started":"2026-09-15T10:34:52.902975Z","shell.execute_reply":"2026-09-15T10:34:53.140749Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 👗 Category Portfolio Analysis: Revenue & Pareto Distribution\n\n---\n\n* **Core Revenue Drivers:** **Ladieswear (\\$207.4M)** and **Divided ($89.3M)** collectively generate over **70%** of total business turnover.\n* **80/20 Concentration:** The top 3 divisions (**Ladieswear, Divided, and Lingeries/Tights**) account for **~83%** of aggregate revenue, passing the critical 80% Pareto threshold.\n* **Underperforming Long-Tail:** Children's and Baby categories together contribute less than **2% combined**, indicating minimal revenue impact despite inventory overhead.\n\n---\n\n> 💡 **Strategic Action**  \n> Streamline retail square footage and catalog bandwidth away from low-velocity Children/Baby segments and reinvest capital into high-turnover fast-fashion lines (**Divided & Ladieswear**) to maximize return on floor space.","metadata":{}},{"cell_type":"markdown","source":"## *Ques4. Track monthly revenue dynamics and compute Month-over-Month (MoM) revenue growth percentages to identify seasonal sales spikes, demand slumps, and overall growth momentum.*","metadata":{"id":"CxtV7yA0h2CW"}},{"cell_type":"code","source":"mom_growth_rate = \"\"\"\n  WITH monthly_revenue_summary AS (\n    SELECT\n      year,\n      month,\n      month_name,\n      ROUND(SUM(price), 2) AS total_revenue\n    FROM transactions\n    GROUP BY year, month, month_name\n),\nmonthly_lagged AS (\n  SELECT\n    year,\n    month,\n    month_name,\n    total_revenue,\n    LAG(total_revenue) OVER (ORDER BY year, month) AS prev_month_revenue\n  FROM monthly_revenue_summary\n)\n\nSELECT\n  year,\n  month_name,\n  total_revenue,\n  COALESCE(prev_month_revenue, 0) AS prev_month_revenue,\n  ROUND(COALESCE(\n      ((total_revenue - prev_month_revenue)*100) / prev_month_revenue, 0), 2) AS mom_growth_pct\nFROM monthly_lagged\nORDER BY year, month;\n\"\"\"\n\ndf_mom = duckdb.query(mom_growth_rate).df()\ndf_mom","metadata":{"id":"rwYe-HzNz_u1","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:53.142738Z","iopub.execute_input":"2026-09-15T10:34:53.142987Z","iopub.status.idle":"2026-09-15T10:34:53.417108Z","shell.execute_reply.started":"2026-09-15T10:34:53.142962Z","shell.execute_reply":"2026-09-15T10:34:53.416218Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Construct standardized 'YYYY-Mon' temporal identifier for MoM trend aggregation\n\ndf_mom[\"year_month\"] = df_mom[\"year\"].astype(str) + \"-\" + df_mom[\"month_name\"].str[:3]","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:53.418371Z","iopub.execute_input":"2026-09-15T10:34:53.418746Z","iopub.status.idle":"2026-09-15T10:34:53.427394Z","shell.execute_reply.started":"2026-09-15T10:34:53.418724Z","shell.execute_reply":"2026-09-15T10:34:53.426494Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"fig, ax1 = plt.subplots(figsize=(9, 3.8), dpi=120)\n\nbars = ax1.bar(\n    df_mom[\"year_month\"],\n    df_mom[\"total_revenue\"] / 1e6,\n    color=\"#94A3B8\",\n    width=0.5,\n    alpha=0.7,\n    label=\"Revenue ($M)\",\n)\nax1.set_ylabel(\"Revenue ($M)\", fontweight=\"bold\", color=\"#475569\")\nax1.set_xticks(range(len(df_mom[\"year_month\"])))\nax1.set_xticklabels(df_mom[\"year_month\"], rotation=35, ha=\"right\")\nax1.set_ylim(0, (df_mom[\"total_revenue\"] / 1e6).max() * 1.25)\n\n# 2. MoM Growth Line\nax2 = ax1.twinx()\n(line,) = ax2.plot(\n    df_mom[\"year_month\"][1:],\n    df_mom[\"mom_growth_pct\"][1:],\n    color=\"#D97706\",\n    marker=\"o\",\n    linewidth=2,\n    label=\"MoM Growth (%)\",\n)\nax2.axhline(0, color=\"black\", linestyle=\"--\", linewidth=0.7, alpha=0.6)\nax2.set_ylabel(\"MoM Growth (%)\", fontweight=\"bold\", color=\"#D97706\")\nax2.tick_params(axis=\"y\", labelcolor=\"#D97706\")\n\n# 3. Combined Legend (Bars + Line ek hi box me)\nlines1, labels1 = ax1.get_legend_handles_labels()\nlines2, labels2 = ax2.get_legend_handles_labels()\nax1.legend(\n    lines1 + lines2,\n    labels1 + labels2,\n    loc=\"upper right\",\n    frameon=True,\n    facecolor=\"#F8FAFC\",\n    edgecolor=\"none\",\n    fontsize=8.5,\n)\nax1.spines[\"top\"].set_visible(False)\nax2.spines[\"top\"].set_visible(False)\nplt.title(\n    \"Monthly Revenue Trend & MoM Growth %\",\n    fontweight=\"bold\",\n    loc=\"left\",\n    pad=12,\n)\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:53.428655Z","iopub.execute_input":"2026-09-15T10:34:53.428962Z","iopub.status.idle":"2026-09-15T10:34:53.832408Z","shell.execute_reply.started":"2026-09-15T10:34:53.428937Z","shell.execute_reply":"2026-09-15T10:34:53.831686Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 📈 Revenue Dynamics: Monthly Seasonality & MoM Growth\n\n---\n\n* **Summer Surge Peak:** **June 2020** registered the all-time high revenue at **\\$43.1M (+15.9% MoM)**, driven by peak summer apparel demand.\n* **Post-Peak Slumps:** Severe contraction occurs immediately following peak cycles, notably in **July 2020 (-24.8% MoM)** and **December 2019 (-19.5% MoM)**.\n* **Baseline Stability:** Across non-peak operational months (Jan–Mar), revenue stabilizes consistently around the **~$28.8M – $30.3M** baseline.\n* **Data Boundary Note:** The initial spike in **October 2019 (+128.6%)** is an artifact of **September 2019** containing partial-month transaction data ($16.2M).\n\n---\n\n> 💡 **Strategic Action**  \n> Implement aggressive mid-season promotional campaigns and clearance push events in early July and December to smooth out the severe ~20-25% MoM demand drops following peak seasonal runs.","metadata":{}},{"cell_type":"markdown","source":"## *Ques5. Compare monthly revenue and percentage contribution between physical stores and online channels.*","metadata":{"id":"jJru8_VOllg3"}},{"cell_type":"code","source":"revenue_camparison = \"\"\"\n  WITH monthly_channel_sales AS (\n    SELECT\n      year,\n      month,\n      month_name,\n      ROUND(SUM(price), 2) AS total_revenue,\n      ROUND(SUM(CASE WHEN sales_channel_id = 1 THEN price ELSE 0 END), 2) AS store_revenue,\n      ROUND(SUM(CASE WHEN sales_channel_id = 2 THEN price ELSE 0 END), 2) AS online_revenue\n    FROM\n      transactions\n    GROUP BY\n      year, month, month_name\n  )\n\n  SELECT\n    year,\n    month_name,\n    total_revenue,\n    store_revenue,\n    online_revenue,\n    ROUND((store_revenue / total_revenue) * 100, 2) AS store_contrib_pct,\n    ROUND((online_revenue / total_revenue) * 100, 2) AS online_contrib_pct\n  FROM\n    monthly_channel_sales\n  ORDER BY\n    year, month;\n    \"\"\"\n\nchannel_monthly_contrib = duckdb.query(revenue_camparison).df()\nchannel_monthly_contrib","metadata":{"id":"NpMYvGjAwJ3H","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:53.833284Z","iopub.execute_input":"2026-09-15T10:34:53.833508Z","iopub.status.idle":"2026-09-15T10:34:54.132048Z","shell.execute_reply.started":"2026-09-15T10:34:53.833488Z","shell.execute_reply":"2026-09-15T10:34:54.131052Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = channel_monthly_contrib.copy()\nperiod = df[\"year\"].astype(str) + \"-\" + df[\"month_name\"].str[:3]\nx = np.arange(len(period))\nw = 0.38\n\nfig, ax = plt.subplots(figsize=(11, 4.5), dpi=120)\n\n# Bars\nb1 = ax.bar(\n    x - w / 2,\n    df[\"online_contrib_pct\"],\n    width=w,\n    label=\"Online\",\n    color=\"#1E3A8A\",\n)\nb2 = ax.bar(\n    x + w / 2, df[\"store_contrib_pct\"], width=w, label=\"Store\", color=\"#F59E0B\"\n)\n\n# Value labels on top of bars\nax.bar_label(b1, fmt=\"%.0f%%\", padding=2, fontsize=7.5, fontweight=\"bold\")\nax.bar_label(b2, fmt=\"%.0f%%\", padding=2, fontsize=7.5, fontweight=\"bold\")\n\n# Formatting\nax.set_xticks(x)\nax.set_xticklabels(period, rotation=35, ha=\"right\", fontsize=9)\nax.set_ylim(0, 115)\nax.set_ylabel(\"Contribution (%)\", fontweight=\"bold\", fontsize=9)\nax.spines[[\"top\", \"right\"]].set_visible(False)\nax.grid(axis=\"y\", linestyle=\":\", alpha=0.4)\nax.legend(frameon=False, loc=\"upper right\", ncol=2)\nplt.title(\n    \"Option 2: Direct Channel Comparison (Grouped Bars)\",\n    fontweight=\"bold\",\n    fontsize=11,\n    loc=\"left\",\n    pad=10,\n)\n\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:54.132977Z","iopub.execute_input":"2026-09-15T10:34:54.133232Z","iopub.status.idle":"2026-09-15T10:34:54.378912Z","shell.execute_reply.started":"2026-09-15T10:34:54.133208Z","shell.execute_reply":"2026-09-15T10:34:54.378165Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 🌐 Channel Dynamics: Online vs Physical Store Performance\n\n---\n\n* **Digital Dominance:** Online channels represent the primary revenue backbone, maintaining a consistent **~70% – 80%** revenue share across all operational quarters.\n* **Store Share Baseline:** Physical store locations contribute a steady baseline of **~23% – 34%**, peaking during holiday and summer shopping months (notably July at **33.85%**).\n* **April 2020 Operational Shock:** Physical store revenue dropped to **0.0% (100% Online)** in April 2020, reflecting complete retail store closures during global COVID-19 pandemic lockdowns.\n\n---\n\n> 💡 **Strategic Action**  \n> Maintain aggressive investment in omnichannel fulfillment (e.g., Click-and-Collect, in-store returns for online purchases) to convert the heavy 70%+ digital user base into foot traffic for high-margin store experiences.","metadata":{}},{"cell_type":"markdown","source":"## *Ques6. Identify the top 10 departments by total revenue and order volume, alongside their average unit price, to evaluate core product performance*","metadata":{"id":"9uZ6MEIVzoR_"}},{"cell_type":"code","source":"top_10_dept = \"\"\"\n  SELECT\n    a.department_name,\n    ROUND(SUM(t.price) / 1000000, 2) AS total_revenue_millions,\n    COUNT(*) AS total_orders,\n    ROUND(AVG(t.price), 2) AS avg_unit_price\n  FROM\n    transactions t\n  JOIN\n    articles a\n    ON a.article_id = t.article_id\n  GROUP BY\n    a.department_name\n  ORDER BY\n    total_revenue_millions DESC\n  LIMIT 10 \n\"\"\"\n\ntop_10_dept = duckdb.query(top_10_dept).df()\ntop_10_dept","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:54.379846Z","iopub.execute_input":"2026-09-15T10:34:54.380132Z","iopub.status.idle":"2026-09-15T10:34:54.789123Z","shell.execute_reply.started":"2026-09-15T10:34:54.380105Z","shell.execute_reply":"2026-09-15T10:34:54.787985Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = top_10_dept.sort_values(\"total_revenue_millions\", ascending=True)\navg_benchmark = df[\"avg_unit_price\"].mean()\n\nfig, (ax1, ax2) = plt.subplots(\n    1, 2, figsize=(11, 4.8), sharey=True, dpi=120, gridspec_kw={\"wspace\": 0.15}\n)\n\n# Left: Total Revenue ($M)\nbars1 = ax1.barh(\n    df[\"department_name\"],\n    df[\"total_revenue_millions\"],\n    color=\"#1E3A8A\",\n    height=0.55,\n)\nax1.bar_label(bars1, fmt=\"$%.1fM\", padding=3, fontsize=8, fontweight=\"bold\")\nax1.set_title(\"Total Revenue ($M)\", fontweight=\"bold\", fontsize=10)\nax1.set_xlim(0, df[\"total_revenue_millions\"].max() * 1.25)\nax1.spines[[\"top\", \"right\"]].set_visible(False)\n\n# Right: Avg Unit Price ($)\nbars2 = ax2.barh(\n    df[\"department_name\"], df[\"avg_unit_price\"], color=\"#D97706\", height=0.55\n)\nax2.bar_label(bars2, fmt=\"$%.1f\", padding=3, fontsize=8, fontweight=\"bold\")\nax2.axvline(\n    avg_benchmark,\n    color=\"#DC2626\",\n    ls=\"--\",\n    lw=1,\n    label=f\"Avg (${avg_benchmark:.1f})\",\n)\nax2.set_title(\"Avg Unit Price ($)\", fontweight=\"bold\", fontsize=10)\nax2.set_xlim(0, df[\"avg_unit_price\"].max() * 1.25)\nax2.legend(loc=\"lower right\", frameon=False, fontsize=8)\nax2.spines[[\"top\", \"right\"]].set_visible(False)\n\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:54.790252Z","iopub.execute_input":"2026-09-15T10:34:54.790703Z","iopub.status.idle":"2026-09-15T10:34:55.030485Z","shell.execute_reply.started":"2026-09-15T10:34:54.790683Z","shell.execute_reply":"2026-09-15T10:34:55.029584Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 🏷️ Department Economics: Revenue vs Average Unit Price\n\n---\n\n* **Revenue Anchors at Parity:** Top 3 earners (**Trouser \\$41.3M**, **Dresses \\$32.2M**, **Knitwear \\$28.6M**) align tightly with the catalog average price benchmark (**~$34.0 – \\$35.6 vs \\$35.1 Avg**).\n* **Premium Outlier:** **Outwear** commands the highest unit price at **\\$83.5 (nearly 2.4x the catalog average)**, generating substantial revenue (**\\$15.8M**) purely on high-margin ticket size.\n* **Volume-Driven Discount Lines:** **Swimwear (\\$22.2)** and **Lingerie (\\$20.1 – \\$20.8)** sit significantly below average pricing, relying on sheer units sold to break into the top-10 revenue leaderboard.\n\n---\n\n> 💡 **Strategic Action**  \n> Protect high baseline inventory on Core Trousers & Knitwear to maintain stable cash flow, while using mid-tier price items to upsell customers toward premium Outwear segments during seasonal transitions.","metadata":{}},{"cell_type":"markdown","source":"## *Ques7. Determine the percentage revenue contribution of each product group (e.g., Garment Upper Body, Shoes, Accessories) to assess category performance across the total catalog.*","metadata":{"id":"Bxk3otdM2vB8"}},{"cell_type":"code","source":"product_group_share_pct = \"\"\"\n  SELECT\n    a.product_group_name,\n    COUNT(t.article_id) AS total_orders,\n    ROUND(SUM(t.price), 2) AS group_revenue,\n    ROUND((SUM(t.price)*100) / (SELECT SUM(price) FROM transactions), 2) AS revenue_share_pct\n  FROM\n    transactions t\n  JOIN\n    articles a\n    ON t.article_id = a.article_id\n  GROUP BY\n    a.product_group_name\n  ORDER BY\n    group_revenue DESC;\n  \"\"\"\n\nproduct_group_share_pct = duckdb.query(product_group_share_pct).df()\nproduct_group_share_pct.head()","metadata":{"id":"25EH-k9-fvfA","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:55.032003Z","iopub.execute_input":"2026-09-15T10:34:55.032264Z","iopub.status.idle":"2026-09-15T10:34:55.459194Z","shell.execute_reply.started":"2026-09-15T10:34:55.032240Z","shell.execute_reply":"2026-09-15T10:34:55.458578Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Group smaller tails (<5%) into 'Others'\ndf = product_group_share_pct.copy()\ntop_mask = df['revenue_share_pct'] >= 5\ndf_donut = df[top_mask][['product_group_name', 'revenue_share_pct']].copy()\ndf_donut.loc[len(df_donut)] = ['Others (<5%)', df[~top_mask]['revenue_share_pct'].sum()]\n\nfig, ax = plt.subplots(figsize=(6.5, 4.5), dpi=120)\ncolors = ['#1E3A8A', '#2563EB', '#3B82F6', '#60A5FA', '#93C5FD', '#CBD5E1']\n\nwedges, texts, autotexts = ax.pie(\n    df_donut['revenue_share_pct'], labels=df_donut['product_group_name'],\n    autopct='%1.1f%%', startangle=90, pctdistance=0.78,\n    colors=colors, wedgeprops=dict(width=0.42, edgecolor='white', linewidth=1.5)\n)\n\nfor autotext in autotexts:\n    autotext.set_fontsize(8)\n    autotext.set_fontweight('bold')\n\nax.text(0, 0, 'Revenue\\nShare', ha='center', va='center', fontsize=10, fontweight='bold', color='#1E293B')\nplt.title('Product Group Revenue Contribution Share', fontweight='bold', fontsize=11, pad=12)\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:55.462306Z","iopub.execute_input":"2026-09-15T10:34:55.462539Z","iopub.status.idle":"2026-09-15T10:34:55.597331Z","shell.execute_reply.started":"2026-09-15T10:34:55.462521Z","shell.execute_reply":"2026-09-15T10:34:55.596405Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 👕 Product Group Share: Revenue Distribution Across Catalog\n\n---\n\n* **Core Apparel Dominance:** **Upper Body (37.9%)** and **Lower Body (26.1%)** form the core business engine, jointly commanding **64.0%** of total catalog revenue.\n* **Full Body Contribution:** Dresses and suits (**Garment Full body**) secure the third spot at **15.0%**, bringing core apparel's total share to **~79%**.\n* **Peripheral Categories:** Daily essentials like **Underwear (6.7%)** and **Swimwear (6.0%)** hold steady mid-tier volume, while non-clothing lines (Shoes & Accessories) remain marginal under **3%**.\n\n---\n\n> 💡 **Strategic Action**  \n> Focus digital cross-selling algorithms on \"Top + Bottom\" pairing bundles (e.g., recommend trousers on shirt detail pages) to capture basket synergy between the two largest revenue drivers.","metadata":{}},{"cell_type":"markdown","source":"## *Ques8. Analyze seasonal performance shifts by comparing top department sales and Average Order Value (AOV) variations between Peak Summer (May–July) and Peak Winter (Nov–Jan).*","metadata":{"id":"GI24mguKZ2XQ"}},{"cell_type":"code","source":"dept_aov_shift = \"\"\"\nWITH seasonal_dept_summary AS (\n    SELECT\n      a.department_name,\n      ROUND(SUM(CASE WHEN t.month IN (5, 6, 7) THEN t.price ELSE 0 END), 2) AS summer_revenue,\n      ROUND(SUM(CASE WHEN t.month IN (11, 12, 1) THEN t.price ELSE 0 END), 2) AS winter_revenue,\n      ROUND(AVG(CASE WHEN t.month IN (5, 6, 7) THEN t.price ELSE NULL END), 2) AS summer_aov,\n      ROUND(AVG(CASE WHEN t.month IN (11, 12, 1) THEN t.price ELSE NULL END), 2) AS winter_aov\n    FROM transactions t\n    JOIN articles a ON t.article_id = a.article_id\n    WHERE t.month IN (5, 6, 7, 11, 12, 1)\n    GROUP BY a.department_name\n)\nSELECT\n  department_name,\n  summer_revenue,\n  winter_revenue,\n  summer_aov,\n  winter_aov,\n  ROUND(COALESCE(winter_aov - summer_aov, 0), 2) AS aov_shift\nFROM seasonal_dept_summary\nORDER BY (summer_revenue + winter_revenue) DESC\nLIMIT 10;\n\"\"\"\n\ndept_aov_shift = duckdb.query(dept_aov_shift).df()\ndept_aov_shift","metadata":{"id":"DKpq3QO9fvP1","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:55.598444Z","iopub.execute_input":"2026-09-15T10:34:55.598701Z","iopub.status.idle":"2026-09-15T10:34:55.935994Z","shell.execute_reply.started":"2026-09-15T10:34:55.598679Z","shell.execute_reply":"2026-09-15T10:34:55.935141Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = dept_aov_shift.sort_values(\"aov_shift\", ascending=True).reset_index(\n    drop=True\n)\n\nfig, ax = plt.subplots(figsize=(9, 4.8), dpi=120)\ncolors = [\"#10B981\" if x >= 0 else \"#EF4444\" for x in df[\"aov_shift\"]]\n\nbars = ax.barh(\n    df[\"department_name\"], df[\"aov_shift\"], color=colors, height=0.55\n)\nax.axvline(0, color=\"#1E293B\", lw=1.2)\n\n# Value Labels (Padding adjusted to prevent overlap)\nfor bar in bars:\n  w = bar.get_width()\n  va = \"left\" if w >= 0 else \"right\"\n  offset = 0.6 if w >= 0 else -0.6\n  ax.text(\n      w + offset,\n      bar.get_y() + bar.get_height() / 2,\n      f\"{w:+.2f}$\",\n      va=\"center\",\n      ha=va,\n      fontsize=8.5,\n      fontweight=\"bold\",\n      color=\"#1E293B\",\n  )\n# 2. Layout & Bounds (Gives breathing room so labels don't clip)\nax.set_xlim(-6, 26)\nax.set_ylim(-0.8, 10.3)\nax.set_xlabel(\"Net Price Shift ($)\", fontweight=\"bold\", fontsize=9)\nax.spines[[\"top\", \"right\", \"left\"]].set_visible(False)\nax.grid(axis=\"x\", ls=\":\", alpha=0.35)\n\nplt.title(\n    \"AOV Difference: Winter minus Summer ($)\",\n    fontweight=\"bold\",\n    fontsize=11,\n    loc=\"left\",\n    pad=15,\n)\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:55.936893Z","iopub.execute_input":"2026-09-15T10:34:55.937084Z","iopub.status.idle":"2026-09-15T10:34:56.106927Z","shell.execute_reply.started":"2026-09-15T10:34:55.937067Z","shell.execute_reply":"2026-09-15T10:34:56.105862Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### ❄️☀️ Seasonality Dynamics: Summer vs Winter Performance\n\n---\n\n* **Extreme Seasonal Flips:** **Swimwear** collapses from **\\$12.9M (Summer)** to **\\$1.8M (Winter)**, while **Knitwear** explodes nearly **10x** in winter to **\\$12.4M**.\n* **Heavy Winter Ticket Premiums:** **Outwear** commands the largest seasonal basket expansion, driving unit price up by **+\\$22.93 (reaching \\$84.13)**, followed by **Knitwear (+ \\$7.97)**.\n* **All-Weather Non-Volatiles:** **Trousers (~\\$9.3M – \\$10.5M)** and **Denim Trousers (~\\$3.3M)** maintain near-identical sales across both seasons with virtually zero price fluctuation (< \\$1 shift).\n\n---\n\n> 💡 **Strategic Action**  \n> Shift warehouse inventory from light apparel to heavy knitwear/outerwear by late September to capture the high +$23 ticket premium, while heavily clearing leftover swimwear stock by end of July.","metadata":{}},{"cell_type":"markdown","source":"## *Ques9. Rank and identify the top 3 product types by total revenue within each macro product group using window functions to optimize catalog composition.*","metadata":{"id":"KwD53ZWEcZZM"}},{"cell_type":"code","source":"rank_macro_group = \"\"\"\n  WITH product_type_sales AS (\n    SELECT\n      a.product_group_name,\n      a.product_type_name,\n      COUNT(t.article_id) AS total_orders,\n      ROUND(SUM(t.price), 2) AS total_revenue\n  FROM transactions t\n  JOIN articles a ON t.article_id = a.article_id\n  GROUP BY a.product_group_name, a.product_type_name\n),\nranked_products AS (\n  SELECT\n    product_group_name,\n    product_type_name,\n    total_orders,\n    total_revenue,\n    DENSE_RANK() OVER (\n        PARTITION BY product_group_name ORDER BY total_revenue DESC\n    ) AS rank_within_group\n  FROM product_type_sales\n)\nSELECT\n  product_group_name,\n  product_type_name,\n  total_orders,\n  total_revenue,\n  rank_within_group\nFROM ranked_products\nWHERE rank_within_group <= 3\n  AND total_revenue > 100000\nORDER BY product_group_name, rank_within_group;\n\"\"\"\n\nrank_macro_group = duckdb.query(rank_macro_group).df()\nrank_macro_group","metadata":{"id":"q9q3KbHCfvck","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:56.107853Z","iopub.execute_input":"2026-09-15T10:34:56.108104Z","iopub.status.idle":"2026-09-15T10:34:56.656636Z","shell.execute_reply.started":"2026-09-15T10:34:56.108086Z","shell.execute_reply":"2026-09-15T10:34:56.655636Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 🏆 Intra-Category Hierarchy: Top-3 Product Types by Macro Group\n\n---\n\n* **Monopolistic Category Anchors:** Certain groups rely almost exclusively on a single dominant silhouette—**Dress** captures **\\$58.6M (93%+)** of Full Body, and **Trousers (\\$71.7M)** generate **4.5x** more revenue than the second-place Skirt (**\\$16.1M**).\n* **Balanced Tops Portfolio:** Unlike bottom-wear, **Upper Body** features distributed revenue strength led by **Sweater (\\$37.6M)**, closely supported by **Jacket ($19.8M)** and **Blouse (\\$17.5M)**.\n* **Specialty Segment Anchors:** Across intimate and warm-weather lines, **Bikini Top (\\$11.9M)** anchors Swimwear, while **Bra (\\$16.7M)** drives nearly double the volume of Underwear Bottoms (**\\$8.9M**).\n\n---\n\n> 💡 **Strategic Action**  \n> Streamline niche sub-categories with low traction (e.g., Dungarees at under $600K) and consolidate manufacturing/vendor contracts around high-conviction staples (**Trousers, Sweaters, Dresses**).","metadata":{}},{"cell_type":"markdown","source":"## *Ques10. Segment the customer base into generational age brackets (<25, 25–40, 40+) and compute total revenue contribution, order volume, and Average Order Value (AOV) to evaluate demographic spending behavior.*","metadata":{"id":"G4hIAXN_g3zf"}},{"cell_type":"code","source":"age_group_comparision = \"\"\"\n    SELECT \n        c.age_group,\n        COUNT(DISTINCT c.customer_id) AS total_customers,\n        COUNT(t.article_id) AS total_orders,\n        ROUND(SUM(t.price) / 1000000, 2) AS total_revenue_millions,\n        ROUND(AVG(t.price), 2) AS avg_order_value\n    FROM \n        transactions t\n    JOIN\n        customers c \n        ON t.customer_id = c.customer_id\n    GROUP BY\n        c.age_group\n    ORDER BY \n        total_revenue_millions DESC;\n\"\"\"\n\nage_group_comparision = duckdb.query(age_group_comparision).df()\nage_group_comparision","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:56.657563Z","iopub.execute_input":"2026-09-15T10:34:56.657738Z","iopub.status.idle":"2026-09-15T10:34:58.246862Z","shell.execute_reply.started":"2026-09-15T10:34:56.657722Z","shell.execute_reply":"2026-09-15T10:34:58.246192Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = age_group_comparision.sort_values(\n    'total_revenue_millions', ascending=True\n).reset_index(drop=True)\navg_aov = df['avg_order_value'].mean()\n\nfig, (ax1, ax2) = plt.subplots(\n    1, 2, figsize=(11, 4.2), sharey=True, dpi=120, gridspec_kw={'wspace': 0.15}\n)\n\n# Left: Total Revenue ($M)\nb1 = ax1.barh(\n    df['age_group'], df['total_revenue_millions'], color='#1E3A8A', height=0.52\n)\nax1.bar_label(b1, fmt='$%.1fM', padding=3, fontsize=8, fontweight='bold')\nax1.set_title('Total Revenue ($M)', fontweight='bold', fontsize=10)\nax1.set_xlim(0, df['total_revenue_millions'].max() * 1.25)\nax1.spines[['top', 'right']].set_visible(False)\n\n# Right: Average Order Value ($)\nb2 = ax2.barh(\n    df['age_group'], df['avg_order_value'], color='#D97706', height=0.52\n)\nax2.bar_label(b2, fmt='$%.2f', padding=3, fontsize=8, fontweight='bold')\nax2.axvline(\n    avg_aov,\n    color='#DC2626',\n    ls='--',\n    lw=1,\n    label=f'Benchmark Avg (${avg_aov:.1f})',\n)\nax2.set_title('Average Order Value ($)', fontweight='bold', fontsize=10)\nax2.set_xlim(0, 40)\nax2.legend(loc='lower right', frameon=False, fontsize=8)\nax2.spines[['top', 'right']].set_visible(False)\n\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:58.247778Z","iopub.execute_input":"2026-09-15T10:34:58.248021Z","iopub.status.idle":"2026-09-15T10:34:58.455336Z","shell.execute_reply.started":"2026-09-15T10:34:58.248002Z","shell.execute_reply":"2026-09-15T10:34:58.454205Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 👥 Demographic Economics: Generational Revenue & AOV Dynamics\n\n---\n\n* **Core Revenue Anchor:** **Adults** generate the highest revenue at **\\$138.5M (4.86M orders)**, closely supported by **Young Adults (\\$108.9M)**, making 20–40 age brackets responsible for **~59%** of total turnover.\n* **Purchasing Power Inversion:** **Seniors** hold the highest spending power with an AOV of **\\$30.31**, operating significantly above the catalog benchmark (**\\$27.40**), whereas **Teenagers** register the lowest basket value at **\\$22.82**.\n\n---\n\n> 💡 **Strategic Action**  \n> Direct high-volume trend promotions toward Adults/Young Adults to sustain transaction velocity, while curating premium, higher-margin catalogs for Seniors to capitalize on their superior basket purchasing power.","metadata":{}},{"cell_type":"markdown","source":"## *Ques11. Evaluate the impact of newsletter subscription frequency on total spending and Average Order Value (AOV) among active club members to measure marketing campaign effectiveness.*","metadata":{"id":"7MBOgPgghBYm"}},{"cell_type":"code","source":"new_frequency = \"\"\"\nSELECT \n    COALESCE(c.fashion_news_frequency, 'NONE') AS news_frequency,\n    COUNT(DISTINCT c.customer_id) AS active_members_count,\n    COUNT(t.article_id) AS total_orders,\n    ROUND(SUM(t.price), 2) AS total_revenue,\n    ROUND(AVG(t.price), 2) AS average_order_value,\n    ROUND(SUM(t.price) / COUNT(DISTINCT c.customer_id), 2) AS avg_spend_per_customer\nFROM \n    customers c\nJOIN \n    transactions t \n    ON c.customer_id = t.customer_id\nWHERE \n    c.club_member_status = 'ACTIVE'\n    AND (\n        c.fashion_news_frequency IN ('Regularly', 'Monthly') \n        OR c.fashion_news_frequency IS NULL \n        OR c.fashion_news_frequency = 'NONE'\n    )\nGROUP BY \n    COALESCE(c.fashion_news_frequency, 'NONE')\nORDER BY \n    total_revenue DESC\n\"\"\"\n\nnew_frequency = duckdb.query(new_frequency).df()\nnew_frequency","metadata":{"id":"V9eZ6zV7fvI8","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:58.456687Z","iopub.execute_input":"2026-09-15T10:34:58.456993Z","iopub.status.idle":"2026-09-15T10:34:59.330544Z","shell.execute_reply.started":"2026-09-15T10:34:58.456968Z","shell.execute_reply":"2026-09-15T10:34:59.329560Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 📩 Marketing Impact: Newsletter Frequency vs Member Lifetime Value\n\n---\n\n* **Massive Value Multiplier:** Active members receiving **Regular** newsletters generate an **Avg Spend per Customer of \\$494.09**, representing a **~2.7x premium** over monthly recipients (**\\$183.01**).\n* **Newsletter Engagement Scale:** The **Regular** cadence commands virtually the entire marketing audience (**365.8K active buyers and \\$180.7M revenue**), whereas **Monthly** frequency is commercially negligible (**353 users, $64.6K revenue**).\n\n---\n\n> 💡 **Strategic Action**  \n> Deprecate the low-engagement \"Monthly\" option and default new club member sign-ups into the high-converting \"Regular\" newsletter cadence to maximize customer lifetime spend.","metadata":{}},{"cell_type":"markdown","source":"## *Ques12. Categorize customers into purchase frequency cohorts (1 Order, 2–5 Orders, 5+ Orders) to determine the concentration of total revenue driven by high-repeat buyers.*","metadata":{"id":"FsrIBNSooXH7"}},{"cell_type":"code","source":"frequency_cohort = \"\"\"\n  WITH customer_orders AS (\n    SELECT\n        c.customer_id,\n        COUNT(t.article_id) AS total_orders,\n        SUM(t.price) AS total_customer_spend\n    FROM customers c\n    JOIN transactions t\n        ON c.customer_id = t.customer_id\n    GROUP BY c.customer_id\n)\nSELECT\n    CASE\n        WHEN total_orders = 1 THEN '1 Order (One-Time Buyers)'\n        WHEN total_orders BETWEEN 2 AND 5 THEN '2-5 Orders (Repeat Buyers)'\n        WHEN total_orders > 5 THEN '5+ Orders (High-Repeat/Loyal Buyers)'\n        END AS purchase_cohort,\n    COUNT(customer_id) AS total_customers,\n    ROUND((COUNT(customer_id) * 100.0) / (SELECT COUNT(*) FROM customer_orders),2) AS customer_share_pct,\n    ROUND(SUM(total_customer_spend), 2) AS total_revenue,\n    ROUND((SUM(total_customer_spend) * 100.0) / (SELECT SUM(total_customer_spend) FROM customer_orders),2) AS revenue_share_pct\nFROM customer_orders\nGROUP BY\n    CASE\n        WHEN total_orders = 1 THEN '1 Order (One-Time Buyers)'\n        WHEN total_orders BETWEEN 2 AND 5 THEN '2-5 Orders (Repeat Buyers)'\n        WHEN total_orders > 5 THEN '5+ Orders (High-Repeat/Loyal Buyers)'\n    END\nORDER BY total_revenue DESC\n\"\"\"\n\nfrequency_cohort = duckdb.query(frequency_cohort).df()\nfrequency_cohort","metadata":{"id":"BDvz8vV2fvF8","outputId":"35843102-e87c-4fc7-bd32-ff2ed3949597","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:34:59.331570Z","iopub.execute_input":"2026-09-15T10:34:59.331834Z","iopub.status.idle":"2026-09-15T10:35:00.581078Z","shell.execute_reply.started":"2026-09-15T10:34:59.331804Z","shell.execute_reply":"2026-09-15T10:35:00.579741Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df = frequency_cohort.iloc[::-1].reset_index(\n    drop=True\n)  # One-Time se Loyal order\ny = np.arange(len(df))\nh = 0.32\n\nfig, ax = plt.subplots(figsize=(9, 4.2), dpi=120)\n\n# Grouped Bars\nb1 = ax.barh(\n    y + h / 2,\n    df[\"customer_share_pct\"],\n    height=h,\n    label=\"Customer Share %\",\n    color=\"#94A3B8\",\n)\nb2 = ax.barh(\n    y - h / 2,\n    df[\"revenue_share_pct\"],\n    height=h,\n    label=\"Revenue Share %\",\n    color=\"#1E3A8A\",\n)\n\nax.bar_label(b1, fmt=\" %.1f%%\", padding=3, fontsize=8)\nax.bar_label(\n    b2, fmt=\" %.1f%%\", padding=3, fontsize=8, fontweight=\"bold\", color=\"#1E3A8A\"\n)\n\nax.set_yticks(y)\nax.set_yticklabels(df[\"purchase_cohort\"], fontsize=9)\nax.set_xlim(0, 110)\nax.set_xlabel(\"Share (%)\", fontweight=\"bold\", fontsize=9)\nax.spines[[\"top\", \"right\"]].set_visible(False)\nax.legend(frameon=False, loc=\"lower right\", fontsize=8.5)\n\nplt.title(\n    \"Customer Concentration: Share of Base vs Revenue Generated\",\n    fontweight=\"bold\",\n    fontsize=11,\n    loc=\"left\",\n    pad=12,\n)\nplt.tight_layout()\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:35:00.582197Z","iopub.execute_input":"2026-09-15T10:35:00.582470Z","iopub.status.idle":"2026-09-15T10:35:00.724257Z","shell.execute_reply.started":"2026-09-15T10:35:00.582449Z","shell.execute_reply":"2026-09-15T10:35:00.723014Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 🔁 Retention Economics: Cohort Volume vs Revenue Concentration\n\n---\n\n* **High-Loyalty Monopolization:** The **5+ Orders** cohort serves as the financial pillar, comprising **59.5% of the customer base** but producing an overwhelming **92.3% of total revenue (\\$389.0M)**.\n* **Under-Monetized Mid-Tier:** Customers with **(2–5 Orders)** account for nearly one-third of all buyers (**30.5%**), yet yield only **6.8% of aggregate sales (\\$28.8M)**.\n\n---\n\n> 💡 **Strategic Action**  \n> Build a targeted nurture funnel to convert the 30.5% mid-tier buyers into 5+ order loyalists—moving even a fraction of this segment delivers outsized revenue lift compared to new user acquisition.","metadata":{}},{"cell_type":"markdown","source":"## *Ques13. Identify the top 1% of high-value customers by total lifetime spend to target with elite VIP loyalty rewards and retention initiatives.*","metadata":{"id":"ksX-9D1NpyHs"}},{"cell_type":"code","source":"high_value_customers = \"\"\"\nWITH customer_spending AS (\n    SELECT \n        customer_id,\n        COUNT(article_id) AS total_orders,\n        ROUND(SUM(price), 2) AS total_lifetime_spend,\n        PERCENT_RANK() OVER (ORDER BY SUM(price) DESC) AS spend_percentile\n    FROM transactions\n    GROUP BY customer_id\n)\nSELECT \n    customer_id,\n    total_orders,\n    total_lifetime_spend,\n    ROUND(spend_percentile, 4) AS spend_percentile\nFROM customer_spending\nWHERE spend_percentile <= 0.01\nORDER BY total_lifetime_spend DESC;\n\"\"\"\n\nhigh_value_customers = duckdb.query(high_value_customers).df()\nprint(f\"Total VIP customers:\\t{high_value_customers.shape[0]}\")\nhigh_value_customers.head()","metadata":{"id":"8DRpBl76aqVN","trusted":true,"execution":{"iopub.status.busy":"2026-09-15T10:41:00.492929Z","iopub.execute_input":"2026-09-15T10:41:00.493200Z","iopub.status.idle":"2026-09-15T10:41:01.231289Z","shell.execute_reply.started":"2026-09-15T10:41:00.493178Z","shell.execute_reply":"2026-09-15T10:41:01.230249Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"### 💎 Elite Tier Identification: Top 1% High-Value Customers\n\n---\n\n* **VIP Segment Scale:** The top 1% cohort comprises **9,951 elite customers** (`PERCENT_RANK() <= 0.01`) exhibiting extreme lifetime loyalty.\n* **Massive Spend Concentration:** Peak accounts spend upwards of **$25K – $35K lifetime** across **400 to 1,000+ individual unit purchases**, operating hundreds of times above the baseline customer spend (~$423).\n\n---\n\n> 💡 **Strategic Action**  \n> Enroll these 9,951 VIP accounts into an exclusive White-Glove Loyalty Tier with personal styling previews, dedicated concierge support, and early access drops to ring-fence high-value turnover against churn.","metadata":{}},{"cell_type":"markdown","source":"# 🎯 Final Executive Recommendations\n\n---\n\n> ### **1. Double Down on 80/20 Winners:** \n**Ladieswear & Divided** generate **>70% of total revenue**, led by **Trousers (\\$41.3M)** and **Dresses (\\$58.6M)**. Cut floor space and inventory for slow-moving lines (**Kids & Baby at <2% combined**).\n\n> ### **2. Scale Omnichannel Strategy:**\nDigital is the primary growth engine (**~75% revenue share**). Expand **Buy Online, Pick Up in Store (BOPIS)** to funnel digital traffic into physical stores during offline peaks (**33.8% in July**).\n\n> ### **3. Control Seasonal Volatility:** \nRevenue plunges **-24.8% between June (\\$43.1M) and July (\\$32.4M)**, while **Swimwear drops by \\$11.1M** into winter. Launch early-July clearance events and fast-track winter collections by September to capture high ticket prices (**+\\$22.93 Outwear AOV**).\n\n> ### **4. Convert the 2–5 Orders Mid-Tier:** \nBuyers with **2–5 orders** make up **30.5% of customers** but generate only **6.8% of sales**. Target them via **Regular Newsletters (\\$494 spend per member)** to push them into the high-margin **5+ Orders cohort (92.3% revenue)**.\n\n> ### **5. Retain Top 1% VIPs:** \nAn elite group of **9,951 customers** spends up to **\\$35K lifetime** across **400–1,000+ orders**. Launch an exclusive **VIP Concierge Program** to lock in retention and eliminate churn risk.","metadata":{}}]}