屏東市促銷策略分析

Pingtung City Promotional Campaign Design - Data-Driven Recommendations

import sys
sys.path.insert(0, ".")

import polars as pl
import altair as alt
import ibis
from ibis import _
from python.db_connection import get_db_connection
from great_tables import GT, style, loc
import numpy as np
from datetime import datetime

# Connect to database
con = get_db_connection()

# =============================================================================
# CONFIGURATION - Analysis Parameters
# =============================================================================
PINGTUNG_ROUTES = ["A-PT", "PT"]
ANALYSIS_START = "20250101"
ANALYSIS_END = "20251130"
TICKET_ANALYSIS_START = "20250701"  # Jul-Nov for ticket distribution

# Items to exclude (service fees, samples, placeholders)
EXCLUDED_ITEMS = ["69620", "69617", "*"]
SAMPLE_PREFIX = "S"

# Category names
CAT_NAMES = {
    "1": "1-生產製造 (Internal)",
    "2": "2-成品 (Packaged/Dry)",
    "3": "3-生產分裝 (Repackaged)",
    "4": "4-冷藏類 (Refrigerated)",
    "5": "5-冷凍類 (Frozen)",
    "6": "6-器具類 (Utensils)",
    "7": "7-免洗餐具 (Disposables)",
    "8": "8-酒/咖啡豆 (Alcohol/Coffee)",
    "9": "9-調味添加物 (Seasonings)",
    "10": "10-其他 (Other)"
}

# =============================================================================
# BASE TABLES
# =============================================================================
sales_orders = (
    con.table("sales_orders", database="cosmos_sync")
    .filter(_.confirmed_code == "Y")
    .select("order_type", "order_number", "customer_code", "delivery_route", "order_date")
)

sales_order_lines = (
    con.table("sales_order_lines", database="cosmos_sync")
    .select("order_type", "order_number", "item_code", "quantity", "pretax_subtotal")
)

customers = (
    con.table("customers", database="cosmos_sync")
    .select("customer_code", "trade_name", "delivery_route", "closure_date")
    .rename(customer_route="delivery_route")
)

product_info = (
    con.table("product_information", database="cosmos_sync")
    .select("product_code", "product_name", "product_category_1", "product_category_2",
            "latest_cost", "price_standard", "price_1")
)

procurement_costs = con.table("procurement_costs", database="cosmos_sync")

# Retail POS tables for store comparison
retail_sales = (
    con.table("retail_sales", database="cosmos_sync")
    .select("business_date", "store_code", "terminal_code", "transaction_serial_number")
)

retail_sale_lines = (
    con.table("retail_sale_lines", database="cosmos_sync")
    .select("business_date", "store_code", "terminal_code", "transaction_serial_number",
            "line_number", "pretax_subtotal")
)

# Store code constants
STORE_PINGTUNG = ["01"]  # Pingtung City store only
STORE_PINGTUNG_AREA = ["01", "02", "04"]  # PT + Chaozhou + Donggang
STORE_NAMES = {"01": "屏東市", "02": "潮州", "04": "東港"}

# =============================================================================
# REUSABLE ANALYSIS FUNCTIONS
# =============================================================================

def get_customer_count(routes: list, start_date: str, end_date: str) -> int:
    """Get distinct active customer count for specified routes and date range."""
    return (
        sales_orders
        .filter(_.delivery_route.isin(routes))
        .filter(_.order_date >= start_date)
        .filter(_.order_date <= end_date)
        .select("customer_code")
        .distinct()
        .count()
        .execute()
    )

def get_time_matched_costs(start_month: str, end_month: str) -> ibis.Table:
    """Get average costs per product for the specified month range.

    IMPORTANT: Uses procurement_costs table, NOT product_information.latest_cost.
    This ensures accurate margin calculations based on actual purchase costs.
    """
    return (
        procurement_costs
        .filter(_.inventory_month >= start_month)
        .filter(_.inventory_month <= end_month)
        .filter(_.unit_cost > 0)  # Exclude zero/null costs
        .group_by("product_code")
        .agg(avg_cost=_.unit_cost.cast("float64").mean())
    )

def get_product_metrics(routes: list, start_date: str, end_date: str) -> pl.DataFrame:
    """Calculate comprehensive product metrics with time-matched costs.

    Returns DataFrame with:
    - item_code, product_name, product_category_1
    - buying_customers, penetration_pct
    - total_revenue, total_qty, avg_price
    - avg_cost (time-matched), margin_pct
    """
    total_customers = get_customer_count(routes, start_date, end_date)

    # Get time-matched costs for the analysis period
    cost_start = start_date[:6]  # YYYYMM
    cost_end = end_date[:6]
    avg_costs = get_time_matched_costs(cost_start, cost_end)

    return (
        sales_orders
        .filter(_.delivery_route.isin(routes))
        .filter(_.order_date >= start_date)
        .filter(_.order_date <= end_date)
        .left_join(sales_order_lines, ["order_type", "order_number"])
        .filter(_.item_code.notnull())
        .filter(~_.item_code.isin(EXCLUDED_ITEMS))
        .filter(~_.item_code.like("S%"))
        .group_by("item_code")
        .agg(
            buying_customers=_.customer_code.nunique(),
            total_revenue=_.pretax_subtotal.cast("float64").sum(),
            total_qty=_.quantity.cast("float64").sum()
        )
        .filter(_.total_qty > 0)
        .mutate(
            avg_price=(_.total_revenue / _.total_qty),
            penetration_pct=(_.buying_customers.cast("float64") / total_customers * 100)
        )
        .left_join(product_info, _.item_code == product_info.product_code)
        .left_join(avg_costs, _.item_code == avg_costs.product_code)
        .filter(_.product_name.notnull())
        .mutate(
            margin_pct=ibis.ifelse(
                (_.avg_cost.notnull()) & (_.avg_cost > 0),
                ((_.avg_price - _.avg_cost) / _.avg_price * 100),
                ibis.null()
            )
        )
        .select(
            "item_code", "product_name", "product_category_1",
            "buying_customers", "penetration_pct",
            "total_revenue", "total_qty", "avg_price", "avg_cost", "margin_pct",
            "price_standard", "price_1", "latest_cost"  # Current pricing from product_info
        )
        .order_by(ibis.desc("penetration_pct"))
        .to_polars()
    )

def get_ticket_percentiles(routes: list, start_date: str, end_date: str) -> dict:
    """Calculate ticket distribution percentiles for specified routes and dates."""
    order_totals = (
        sales_orders
        .filter(_.delivery_route.isin(routes))
        .filter(_.order_date >= start_date)
        .filter(_.order_date <= end_date)
        .left_join(sales_order_lines, ["order_type", "order_number"])
        .group_by(["order_type", "order_number"])
        .agg(order_total=_.pretax_subtotal.cast("float64").sum())
        .to_polars()
    )

    return {
        "p25": order_totals["order_total"].quantile(0.25),
        "p50": order_totals["order_total"].quantile(0.50),
        "p60": order_totals["order_total"].quantile(0.60),
        "p70": order_totals["order_total"].quantile(0.70),
        "p75": order_totals["order_total"].quantile(0.75),
        "p80": order_totals["order_total"].quantile(0.80),
        "p90": order_totals["order_total"].quantile(0.90),
        "avg": order_totals["order_total"].mean(),
        "total_orders": len(order_totals)
    }

def get_retail_ticket_percentiles(store_codes: list, start_date: str, end_date: str) -> dict:
    """Calculate ticket distribution percentiles for retail POS sales."""
    transaction_totals = (
        retail_sales
        .filter(_.store_code.isin(store_codes))
        .filter(_.business_date >= start_date)
        .filter(_.business_date <= end_date)
        .left_join(
            retail_sale_lines,
            ["business_date", "store_code", "terminal_code", "transaction_serial_number"]
        )
        .group_by(["business_date", "store_code", "terminal_code", "transaction_serial_number"])
        .agg(order_total=_.pretax_subtotal.cast("float64").sum())
        .filter(_.order_total > 0)  # Exclude returns/negative transactions
        .to_polars()
    )

    if len(transaction_totals) == 0:
        return {
            "p25": 0, "p50": 0, "p60": 0, "p70": 0,
            "p75": 0, "p80": 0, "p90": 0, "avg": 0, "total_orders": 0
        }

    return {
        "p25": transaction_totals["order_total"].quantile(0.25),
        "p50": transaction_totals["order_total"].quantile(0.50),
        "p60": transaction_totals["order_total"].quantile(0.60),
        "p70": transaction_totals["order_total"].quantile(0.70),
        "p75": transaction_totals["order_total"].quantile(0.75),
        "p80": transaction_totals["order_total"].quantile(0.80),
        "p90": transaction_totals["order_total"].quantile(0.90),
        "avg": transaction_totals["order_total"].mean(),
        "total_orders": len(transaction_totals)
    }

def get_kvi_candidates(
    product_metrics: pl.DataFrame,
    min_penetration: float = 10.0,
    min_margin_after_discount: float = 8.0,
    discount_pct: float = 10.0
) -> pl.DataFrame:
    """Filter products suitable as KVI (high penetration, survives discount)."""
    return (
        product_metrics
        .filter(pl.col("penetration_pct") >= min_penetration)
        .filter(pl.col("margin_pct").is_not_null())
        .with_columns([
            # Calculate margin after discount
            (((pl.col("avg_price") * (1 - discount_pct/100)) - pl.col("avg_cost"))
             / (pl.col("avg_price") * (1 - discount_pct/100)) * 100)
            .alias("margin_after_discount")
        ])
        .filter(pl.col("margin_after_discount") >= min_margin_after_discount)
        .sort("penetration_pct", descending=True)
    )

def get_crosssell_candidates(
    product_metrics: pl.DataFrame,
    max_penetration: float = 15.0,
    min_margin: float = 25.0,
    min_revenue: float = 30000.0
) -> pl.DataFrame:
    """Filter products suitable for cross-sell (lower penetration, high margin)."""
    return (
        product_metrics
        .filter(pl.col("penetration_pct") <= max_penetration)
        .filter(pl.col("penetration_pct") >= 2.0)  # At least some traction
        .filter(pl.col("margin_pct").is_not_null())
        .filter(pl.col("margin_pct") >= min_margin)
        .filter(pl.col("total_revenue") >= min_revenue)
        .sort("margin_pct", descending=True)
    )

def get_product_growth(routes: list, h1_start: str, h1_end: str, h2_start: str, h2_end: str) -> ibis.Table:
    """Compare H1 vs H2 customer counts per product to identify growth trends.

    Returns ibis Table with:
    - item_code
    - h1_customers: unique customers in H1
    - h2_customers: unique customers in H2
    - customer_growth: H2 - H1 (positive = growing product)
    """
    h1_customers = (
        sales_orders
        .filter(_.delivery_route.isin(routes))
        .filter(_.order_date >= h1_start)
        .filter(_.order_date <= h1_end)
        .left_join(sales_order_lines, ["order_type", "order_number"])
        .filter(_.item_code.notnull())
        .filter(~_.item_code.isin(EXCLUDED_ITEMS))
        .filter(~_.item_code.like("S%"))
        .group_by("item_code")
        .agg(h1_customers=_.customer_code.nunique())
    )
    h2_customers = (
        sales_orders
        .filter(_.delivery_route.isin(routes))
        .filter(_.order_date >= h2_start)
        .filter(_.order_date <= h2_end)
        .left_join(sales_order_lines, ["order_type", "order_number"])
        .filter(_.item_code.notnull())
        .filter(~_.item_code.isin(EXCLUDED_ITEMS))
        .filter(~_.item_code.like("S%"))
        .group_by("item_code")
        .agg(h2_customers=_.customer_code.nunique())
    )
    return (
        h1_customers
        .outer_join(h2_customers, "item_code")
        .mutate(
            h1_customers=_.h1_customers.fillna(0),
            h2_customers=_.h2_customers.fillna(0),
            customer_growth=_.h2_customers.fillna(0) - _.h1_customers.fillna(0)
        )
    )

# =============================================================================
# COMPUTE CORE METRICS (used throughout document)
# =============================================================================
# These are calculated once and referenced in executive summary and sections

TOTAL_PT_CUSTOMERS = get_customer_count(PINGTUNG_ROUTES, ANALYSIS_START, ANALYSIS_END)
PRODUCT_METRICS = get_product_metrics(PINGTUNG_ROUTES, ANALYSIS_START, ANALYSIS_END)
TICKET_STATS = get_ticket_percentiles(PINGTUNG_ROUTES, TICKET_ANALYSIS_START, ANALYSIS_END)

# Product growth analysis (H1 vs H2 customer count comparison)
PRODUCT_GROWTH = get_product_growth(
    PINGTUNG_ROUTES,
    h1_start="20250101", h1_end="20250630",
    h2_start="20250701", h2_end="20251130"
).to_polars()

# KVI and Cross-sell candidates with default thresholds
KVI_CANDIDATES = get_kvi_candidates(PRODUCT_METRICS, min_penetration=10.0, min_margin_after_discount=8.0)
CROSSSELL_CANDIDATES = get_crosssell_candidates(PRODUCT_METRICS, max_penetration=15.0, min_margin=25.0)

# Count growing products
growing_products_count = len(PRODUCT_GROWTH.filter(pl.col("customer_growth") >= 2))

print(f"📊 Analysis Period: {ANALYSIS_START} to {ANALYSIS_END}")
print(f"👥 Pingtung City Active Customers: {TOTAL_PT_CUSTOMERS}")
print(f"🎫 Ticket P70: ${TICKET_STATS['p70']:,.0f} | Avg: ${TICKET_STATS['avg']:,.0f}")
print(f"🏷️ KVI Candidates (≥10% pen, ≥8% margin after 10% discount): {len(KVI_CANDIDATES)}")
print(f"🛒 Cross-sell Candidates (≤15% pen, ≥25% margin): {len(CROSSSELL_CANDIDATES)}")
print(f"📈 Growing Products (H1→H2 ≥+2 customers): {growing_products_count}")

📊 Analysis Period: 20250101 to 20251130
👥 Pingtung City Active Customers: 318
🎫 Ticket P70: $2,928 | Avg: $2,737
🏷️ KVI Candidates (≥10% pen, ≥8% margin after 10% discount): 3
🛒 Cross-sell Candidates (≤15% pen, ≥25% margin): 27
📈 Growing Products (H1→H2 ≥+2 customers): 49

執行摘要

# Get top KVI candidate and cross-sell candidates for summary
top_kvi = KVI_CANDIDATES.head(1)
top_crosssell = CROSSSELL_CANDIDATES.head(4)

# Calculate recommended threshold (P70 rounded to nearest 100)
threshold_raw = TICKET_STATS["p70"]
threshold_recommended = round(threshold_raw / 100) * 100

# Get KVI details
if len(top_kvi) > 0:
    kvi_row = top_kvi.row(0, named=True)
    kvi_name = kvi_row["product_name"]
    kvi_code = kvi_row["item_code"]
    kvi_price = kvi_row["avg_price"]
    kvi_penetration = kvi_row["penetration_pct"]
    kvi_margin = kvi_row["margin_pct"]
    kvi_margin_after = kvi_row["margin_after_discount"]
    # Calculate promo price (10% discount)
    kvi_promo_price = kvi_price * 0.90

Important核心建議 (動態生成)

促銷門檻：$2,900 TWD (P70=$2,928)

第一週期 KVI 推薦 (依滲透率+折扣後毛利篩選): - 伊植麥玉米粒(易開)340g. (#25888) - 現價: $19 → 促銷價: $17 (10% off) - 滲透率: 28.0% | 毛利: 21.1% → 折扣後: 12.3%

Cross-sell 候選 (滲透率≤15%, 毛利≥25%): 27 個產品

活躍客戶數: 318 位 (屏東市, 2025年)

分析週期: 20250101 ~ 20251130

1. 地理驗證：屏東市KVI適用於全公司嗎？

# Define geographic scopes for comparison
ALL_DELIVERY_ROUTES = ["A-PT", "A-PH", "A-PN", "A-PS", "A-PK", "K1", "K2", "PT", "PH", "PS", "PK"]
ROUTES_EX_HENGCHUN = [r for r in ALL_DELIVERY_ROUTES if r not in ["A-PH", "PH"]]
ROUTES_EX_DONGGANG = [r for r in ALL_DELIVERY_ROUTES if r not in ["A-PK", "PK"]]

def get_top_products_for_scope(routes: list, n: int = 10) -> pl.DataFrame:
    """Get top N products by penetration for a given route scope."""
    total_customers = get_customer_count(routes, ANALYSIS_START, ANALYSIS_END)

    return (
        sales_orders
        .filter(_.delivery_route.isin(routes))
        .filter(_.order_date >= ANALYSIS_START)
        .filter(_.order_date <= ANALYSIS_END)
        .left_join(sales_order_lines, ["order_type", "order_number"])
        .filter(_.item_code.notnull())
        .filter(~_.item_code.isin(EXCLUDED_ITEMS))
        .filter(~_.item_code.like("S%"))
        .group_by("item_code")
        .agg(buying_customers=_.customer_code.nunique())
        .mutate(penetration_pct=(_.buying_customers.cast("float64") / total_customers * 100))
        .left_join(product_info, _.item_code == product_info.product_code)
        .filter(_.product_name.notnull())
        .order_by(ibis.desc("penetration_pct"))
        .limit(n)
        .select("item_code", "product_name", "penetration_pct")
        .to_polars()
        .with_columns([
            (pl.col("item_code") + " " + pl.col("product_name").str.slice(0, 8)).alias("item_label")
        ])
    )

# Get top 10 for each scope
pt_city_top = get_top_products_for_scope(PINGTUNG_ROUTES, 10)
ex_hengchun_top = get_top_products_for_scope(ROUTES_EX_HENGCHUN, 10)
ex_donggang_top = get_top_products_for_scope(ROUTES_EX_DONGGANG, 10)

# Get customer counts for each scope
n_pt_city = get_customer_count(PINGTUNG_ROUTES, ANALYSIS_START, ANALYSIS_END)
n_ex_hengchun = get_customer_count(ROUTES_EX_HENGCHUN, ANALYSIS_START, ANALYSIS_END)
n_ex_donggang = get_customer_count(ROUTES_EX_DONGGANG, ANALYSIS_START, ANALYSIS_END)

# Combine into comparison table
geo_comparison = pl.DataFrame({
    "rank": list(range(1, 11)),
    "pt_city_item": pt_city_top["item_label"].to_list(),
    "pt_city_pct": pt_city_top["penetration_pct"].to_list(),
    "ex_hengchun_item": ex_hengchun_top["item_label"].to_list(),
    "ex_hengchun_pct": ex_hengchun_top["penetration_pct"].to_list(),
    "ex_donggang_item": ex_donggang_top["item_label"].to_list(),
    "ex_donggang_pct": ex_donggang_top["penetration_pct"].to_list()
})

(
    GT(geo_comparison)
    .tab_header(
        title="Top 10 KVIs Across Geographic Scopes",
        subtitle=f"Penetration rates - Analysis period: {ANALYSIS_START} to {ANALYSIS_END}"
    )
    .tab_spanner(label=f"Pingtung City (n={n_pt_city})", columns=["pt_city_item", "pt_city_pct"])
    .tab_spanner(label=f"Ex-Hengchun (n={n_ex_hengchun})", columns=["ex_hengchun_item", "ex_hengchun_pct"])
    .tab_spanner(label=f"Ex-Donggang (n={n_ex_donggang})", columns=["ex_donggang_item", "ex_donggang_pct"])
    .cols_label(
        pt_city_item="Item", pt_city_pct="%",
        ex_hengchun_item="Item", ex_hengchun_pct="%",
        ex_donggang_item="Item", ex_donggang_pct="%"
    )
    .fmt_number(["pt_city_pct", "ex_hengchun_pct", "ex_donggang_pct"], decimals=1)
)

Top 10 KVIs Across Geographic Scopes
Penetration rates - Analysis period: 20250101 to 20251130
rank	Pingtung City (n=318)		Ex-Hengchun (n=1600)		Ex-Donggang (n=1389)
	Item	%	Item	%	Item	%
1	25888 伊植麥玉米粒(易	28.0	25888 伊植麥玉米粒(易	28.9	25888 伊植麥玉米粒(易	29.2
2	51130 香雞城小肉豆1K	18.9	21266 梨山花生醬2.8	17.9	21266 梨山花生醬2.8	18.4
3	21266 梨山花生醬2.8	17.9	53027 紅龍雞塊 1K.	17.6	23093 福鷹玉米粒340	17.3
4	23093 福鷹玉米粒340	16.0	23093 福鷹玉米粒340	17.6	54021 奇津阿在伯手工蔥	15.9
5	41006 亞柏奶精 1公升	15.7	51130 香雞城小肉豆1K	16.4	21280 梨山草莓醬3.2	15.8
6	21280 梨山草莓醬3.2	15.4	21280 梨山草莓醬3.2	15.4	53027 紅龍雞塊 1K.	15.3
7	54021 奇津阿在伯手工蔥	13.8	54021 奇津阿在伯手工蔥	14.6	51130 香雞城小肉豆1K	14.8
8	51172 安美燻腸 100	13.8	51171 安美熱狗50條.	13.7	51171 安美熱狗50條.	14.8
9	53027 紅龍雞塊 1K.	13.5	41006 亞柏奶精 1公升	13.4	41006 亞柏奶精 1公升	13.8
10	21227 福汎巧克力3K	13.5	53102 紅龍卡啦雞腿堡(	12.9	53102 紅龍卡啦雞腿堡(	12.3

Important跨地理驗證結果 (動態計算)

#1 KVI 比較: - 屏東市: 25888 (28.0%) - 扣恆春: 25888 (28.9%) - 扣東港: 25888 (29.2%)

Top 5 重疊分析: - 三個範圍共同的 Top 5 產品: 3 個 - 共同產品: 21266, 25888, 23093

結論: ✅ KVI 選擇具有全公司適用性

客戶基數比較: - 屏東市: 318 位 - 扣恆春: 1600 位 - 扣東港: 1389 位

2. 成本與毛利計算方法

Note成本時間配對方法 (Time-Matched Cost)

本分析使用時間配對成本而非即時成本，確保毛利計算準確：

資料來源

• 成本表: cosmos_sync.procurement_costs (月度成本快照)
• 欄位: product_code, inventory_month (YYYYMM), unit_cost
• 覆蓋範圍: 2017-08 至 2025-12，共 7,607 個產品

配對邏輯

1. 銷售訂單日期 → 取月份 (YYYYMM)
2. 與 procurement_costs.inventory_month 配對
3. 若該月無成本資料 → 使用最近月份成本

為何重要

以玉米粒 (#25888) 為例，2025年成本變化達 12.5%： - 2025-01: NT$16.06 - 2025-06: NT$15.98 - 2025-11: NT$14.05

使用即時成本 (NT$14.05) 計算全年毛利會高估實際毛利。

排除項目

以下項目不納入毛利分析（非實際商品銷售）： - 服務項目: 開店設計費 (69620)、維修費 (69617) - 樣品: S 開頭產品代碼 - 佔位符: “*” 代碼

這些項目共佔屏東市營收 < NT$36,000 (< 0.1%)。

# Verify cost variation for corn as example
corn_costs = (
    procurement_costs
    .filter(_.product_code == "25888")
    .filter(_.inventory_month >= "202501")
    .filter(_.inventory_month <= "202512")
    .select("inventory_month", "unit_cost")
    .order_by("inventory_month")
    .to_polars()
)

print("📊 玉米粒 (#25888) 2025年成本變化:")
print(corn_costs)
print(f"\n成本變化: {corn_costs['unit_cost'].max():.2f} → {corn_costs['unit_cost'].min():.2f}")
print(f"變化幅度: {((corn_costs['unit_cost'].max() - corn_costs['unit_cost'].min()) / corn_costs['unit_cost'].max() * 100):.1f}%")

📊 玉米粒 (#25888) 2025年成本變化:
shape: (12, 2)
┌─────────────────┬───────────────┐
│ inventory_month ┆ unit_cost     │
│ ---             ┆ ---           │
│ str             ┆ decimal[15,4] │
╞═════════════════╪═══════════════╡
│ 202501          ┆ 16.0550       │
│ 202502          ┆ 16.0570       │
│ 202503          ┆ 15.9750       │
│ 202504          ┆ 15.9730       │
│ 202505          ┆ 15.9740       │
│ …               ┆ …             │
│ 202508          ┆ 14.8090       │
│ 202509          ┆ 14.3530       │
│ 202510          ┆ 14.3560       │
│ 202511          ┆ 14.0540       │
│ 202512          ┆ 14.0550       │
└─────────────────┴───────────────┘

成本變化: 16.06 → 14.05
變化幅度: 12.5%

3. 票均分析：最佳門檻

3.1 外送 vs 門市零售票均比較

# Extended routes for delivery analysis (A-PT + PT + A-PN)
DELIVERY_ROUTES_EXTENDED = ["A-PT", "PT", "A-PN"]

# Calculate order totals for Delivery (Jul-Nov 2025)
delivery_orders = (
    sales_orders
    .filter(_.delivery_route.isin(DELIVERY_ROUTES_EXTENDED))
    .filter(_.order_date >= "20250701")
    .filter(_.order_date <= "20251130")
    .left_join(sales_order_lines, ["order_type", "order_number"])
    .group_by(["order_type", "order_number"])
    .agg(order_total=_.pretax_subtotal.cast("float64").sum())
    .to_polars()
)

# Calculate retail totals - Pingtung City store only
retail_pt_orders = (
    retail_sales
    .filter(_.store_code.isin(STORE_PINGTUNG))
    .filter(_.business_date >= "20250701")
    .filter(_.business_date <= "20251130")
    .left_join(
        retail_sale_lines,
        ["business_date", "store_code", "terminal_code", "transaction_serial_number"]
    )
    .group_by(["business_date", "store_code", "terminal_code", "transaction_serial_number"])
    .agg(order_total=_.pretax_subtotal.cast("float64").sum())
    .filter(_.order_total > 0)
    .to_polars()
)

# Calculate retail totals - PT + Chaozhou + Donggang
retail_area_orders = (
    retail_sales
    .filter(_.store_code.isin(STORE_PINGTUNG_AREA))
    .filter(_.business_date >= "20250701")
    .filter(_.business_date <= "20251130")
    .left_join(
        retail_sale_lines,
        ["business_date", "store_code", "terminal_code", "transaction_serial_number"]
    )
    .group_by(["business_date", "store_code", "terminal_code", "transaction_serial_number"])
    .agg(order_total=_.pretax_subtotal.cast("float64").sum())
    .filter(_.order_total > 0)
    .to_polars()
)

# Build comparison table
def calc_percentiles(df, col="order_total"):
    if len(df) == 0:
        return [0] * 8
    return [
        df[col].quantile(0.25),
        df[col].quantile(0.50),
        df[col].quantile(0.60),
        df[col].quantile(0.65),
        df[col].quantile(0.70),
        df[col].quantile(0.75),
        df[col].quantile(0.80),
        df[col].quantile(0.90),
    ]

comparison_df = pl.DataFrame({
    "percentile": ["P25", "P50 (中位數)", "P60", "P65", "P70", "P75", "P80", "P90"],
    "delivery": calc_percentiles(delivery_orders),
    "retail_pt": calc_percentiles(retail_pt_orders),
    "retail_area": calc_percentiles(retail_area_orders),
})

# Add summary row
summary_row = pl.DataFrame({
    "percentile": ["平均票均", "訂單數"],
    "delivery": [
        delivery_orders["order_total"].mean() if len(delivery_orders) > 0 else 0,
        float(len(delivery_orders))
    ],
    "retail_pt": [
        retail_pt_orders["order_total"].mean() if len(retail_pt_orders) > 0 else 0,
        float(len(retail_pt_orders))
    ],
    "retail_area": [
        retail_area_orders["order_total"].mean() if len(retail_area_orders) > 0 else 0,
        float(len(retail_area_orders))
    ],
})

full_comparison = pl.concat([comparison_df, summary_row])

(
    GT(full_comparison)
    .tab_header(
        title="外送 vs 門市零售票均比較",
        subtitle="2025年7-11月"
    )
    .cols_label(
        percentile="百分位",
        delivery="外送 (A-PT+PT+A-PN)",
        retail_pt="門市 (屏東店)",
        retail_area="門市 (屏東+潮州+東港)"
    )
    .fmt_number(columns=["delivery", "retail_pt", "retail_area"], decimals=0, use_seps=True)
    .tab_style(
        style=style.fill(color="#e8f4f8"),
        locations=loc.body(rows=pl.col("percentile") == "P70")
    )
    .tab_style(
        style=style.text(weight="bold"),
        locations=loc.body(rows=pl.col("percentile").is_in(["平均票均", "訂單數"]))
    )
)

外送 vs 門市零售票均比較
2025年7-11月
百分位	外送 (A-PT+PT+A-PN)	門市 (屏東店)	門市 (屏東+潮州+東港)
P25	1,005	224	190
P50 (中位數)	1,912	487	400
P60	2,406	652	529
P65	2,696	760	616
P70	3,008	890	722
P75	3,476	1,057	857
P80	4,039	1,291	1,035
P90	5,957	2,228	1,686
平均票均	2,765	950	734
訂單數	9,104	13,489	65,035

通路數據摘要:

通路	中位數	P70	訂單數
外送 (A-PT+PT+A-PN)	$1,912	$3,008	9,104
門市 (屏東店)	$487	$890	13,489
門市 (3店合計)	$400	$722	65,035

門市/外送票均比: 25.5%

Tip門檻建議

分析結論：門市票均僅約外送的25%，差異顯著，需採用雙門檻策略。

通路	建議門檻	說明
外送配送	$3,000	P70=$3,008，約30%訂單符合
門市零售	$900	P70=$890，激勵加購達標

執行建議：

1. 外送訂單：維持 $3,000 門檻（與原本P70一致）
2. 門市零售：設定 $900 門檻（屏東店P70）
3. 三店統一：若需統一門市門檻，可採用 $700（三店P70）

門檻設定原理：

• P70 表示約 30% 訂單可達標
• 過濾小額投機購買
• 激勵客戶加購達標
• 維持合理毛利結構

3.2 票均分布視覺化

# Enable large datasets in Altair (default limit is 5000 rows)
alt.data_transformers.disable_max_rows()

# Combine data for visualization
viz_delivery = delivery_orders.select("order_total").with_columns(
    pl.lit("外送 (A-PT+PT+A-PN)").alias("channel")
)
viz_retail_pt = retail_pt_orders.select("order_total").with_columns(
    pl.lit("門市 (屏東店)").alias("channel")
)
viz_retail_area = retail_area_orders.select("order_total").with_columns(
    pl.lit("門市 (3店合計)").alias("channel")
)

combined_data = pl.concat([viz_delivery, viz_retail_pt, viz_retail_area])

# Filter out extreme outliers for visualization (keep < $20,000)
viz_data = combined_data.filter(pl.col("order_total") < 20000)

# Layered histogram by channel
chart = alt.Chart(viz_data).mark_bar(opacity=0.6).encode(
    x=alt.X("order_total:Q", bin=alt.Bin(maxbins=50), title="訂單金額 (TWD)"),
    y=alt.Y("count()", title="訂單數", stack=None),
    color=alt.Color("channel:N",
                    scale=alt.Scale(
                        domain=["外送 (A-PT+PT+A-PN)", "門市 (屏東店)", "門市 (3店合計)"],
                        range=["#3498db", "#e74c3c", "#f39c12"]
                    ),
                    legend=alt.Legend(title="通路")),
    tooltip=[alt.Tooltip("count()", title="訂單數"), "channel:N"]
).properties(
    width=700,
    height=400,
    title="外送 vs 門市票均分布 (2025年7-11月)"
)

# Add vertical threshold lines at P70 values
threshold_data = pl.DataFrame({
    "threshold": [delivery_p70, retail_pt_p70],
    "label": ["外送 P70", "門市 P70"],
    "color": ["#3498db", "#e74c3c"]
})

rules = alt.Chart(threshold_data).mark_rule(
    strokeWidth=2,
    strokeDash=[5, 5]
).encode(
    x="threshold:Q",
    color=alt.Color("label:N", scale=alt.Scale(
        domain=["外送 P70", "門市 P70"],
        range=["#3498db", "#e74c3c"]
    ))
)

chart + rules

外送與門市訂單金額分布比較

4. 產品滲透率分析：KVI vs Cross-Sell

4.1 屏東市產品滲透率 (2025年全年)

# Calculate penetration for Pingtung City customers
pt_customers = (
    sales_orders
    .filter(_.delivery_route.isin(PINGTUNG_ROUTES))
    .filter(_.order_date >= "20250101")
    .filter(_.order_date <= "20251130")
    .select("customer_code")
    .distinct()
)

total_pt_customers = pt_customers.count().execute()

# Calculate 2025 average cost per product (time-matched approach)
# This gives us the weighted average cost over the analysis period
avg_2025_cost = (
    procurement_costs
    .filter(_.inventory_month >= "202501")
    .filter(_.inventory_month <= "202511")
    .filter(_.unit_cost > 0)  # Exclude zero-cost months (Dec 2025 has zeros)
    .group_by("product_code")
    .agg(avg_cost=_.unit_cost.cast("float64").mean())
)

# Product-level penetration with excluded items filtered out
product_penetration = (
    sales_orders
    .filter(_.delivery_route.isin(PINGTUNG_ROUTES))
    .filter(_.order_date >= "20250101")
    .filter(_.order_date <= "20251130")
    .left_join(sales_order_lines, ["order_type", "order_number"])
    .filter(_.item_code.notnull())  # Filter out null item_codes
    # Exclude service items and samples
    .filter(~_.item_code.isin(EXCLUDED_ITEMS))
    .filter(~_.item_code.like("S%"))  # Samples start with S
    .group_by("item_code")
    .agg(
        buying_customers=_.customer_code.nunique(),
        total_revenue=_.pretax_subtotal.cast("float64").sum(),
        total_qty=_.quantity.cast("float64").sum()
    )
    .filter(_.total_qty > 0)  # Filter out zero quantity items
    .mutate(
        avg_price=(_.total_revenue / _.total_qty),
        penetration_pct=(_.buying_customers.cast("float64") / total_pt_customers * 100)
    )
    .left_join(product_info, _.item_code == product_info.product_code)
    # Join with 2025 average cost (time-matched)
    .left_join(avg_2025_cost, _.item_code == avg_2025_cost.product_code)
    .filter(_.product_name.notnull())
    .order_by(ibis.desc("penetration_pct"))
    .to_polars()
)

# Top penetration items
top_penetration = product_penetration.head(30).select([
    "item_code", "product_name", "product_category_1",
    "buying_customers", "penetration_pct", "total_revenue", "avg_price"
])

(
    GT(top_penetration)
    .tab_header(
        title="屏東市產品滲透率 Top 30",
        subtitle=f"基於{total_pt_customers}位活躍客戶 (2025年1-11月)"
    )
    .fmt_number("buying_customers", decimals=0)
    .fmt_number("penetration_pct", decimals=1)
    .fmt_currency("total_revenue", currency="TWD", decimals=0, use_subunits=False)
    .fmt_number("avg_price", decimals=1)
    .data_color(
        columns="penetration_pct",
        palette=["#fee5d9", "#fcae91", "#fb6a4a", "#de2d26", "#a50f15"],
        domain=[5, 30]
    )
)

屏東市產品滲透率 Top 30
基於318位活躍客戶 (2025年1-11月)
item_code	product_name	product_category_1	buying_customers	penetration_pct	total_revenue	avg_price
25888	伊植麥玉米粒(易開)340g.	2	89	28.0	NT$466,648	19.4
51130	香雞城小肉豆1K.	5	60	18.9	NT$405,743	175.4
21266	梨山花生醬2.8K	2	57	17.9	NT$182,114	414.8
23093	福鷹玉米粒340g(易開罐).	2	51	16.0	NT$33,674	19.1
41006	亞柏奶精 1公升.	4	50	15.7	NT$687,453	128.3
21280	梨山草莓醬3.2k	2	49	15.4	NT$106,674	254.0
51172	安美燻腸 1000g.	5	44	13.8	NT$337,326	181.4
54021	奇津阿在伯手工蔥抓餅10入.	5	44	13.8	NT$151,340	82.6
53027	紅龍雞塊 1K.	5	43	13.5	NT$281,419	141.8
21227	福汎巧克力3K	2	43	13.5	NT$249,861	472.3
51171	安美熱狗50條.	5	40	12.6	NT$423,683	102.6
23098	金熊三明治鮪魚185g.	2	37	11.6	NT$185,004	26.4
26068	雙魚座鮪魚185g.	2	36	11.3	NT$60,488	29.6
53102	紅龍卡啦雞腿堡(原味) 10片.	5	33	10.4	NT$227,084	186.4
22107	飛燕煉乳 375g.	2	32	10.1	NT$33,587	44.4
53100	紅龍卡啦雞腿堡(辣味) 10片.	5	31	9.7	NT$160,018	187.4
41189	HISUN起司片84片.	4	29	9.1	NT$255,574	297.9
51296	可頌燒餅(原味) 10片	5	29	9.1	NT$159,354	89.2
23361	肉鬆 3K	2	27	8.5	NT$114,119	427.4
23186	豪味豆油膏5L	2	27	8.5	NT$50,429	134.5
11055	原味奶酥700g	1	26	8.2	NT$35,667	177.4
21229	福汎椰香奶酥 1.8K	2	26	8.2	NT$97,887	401.2
53028	正點煙燻雞肉片1K.	5	26	8.2	NT$32,671	223.8
57001	富統小熱狗50支	5	25	7.9	NT$168,183	112.6
23154	可果美快餐用蕃茄醬 3.15k	2	25	7.9	NT$50,042	183.3
51709	澳洲優質牛肉堡 80g	5	24	7.5	NT$185,411	179.1
22287	高級精鹽1k	2	23	7.2	NT$6,463	19.4
41018	香Q蛋餅皮(原)30入.(紅字)	5	23	7.2	NT$197,263	67.9
54061	強匠卡拉雞腿堡(辣味)10片.	5	23	7.2	NT$221,012	185.3
43599	一番烏龍麵 200g.	4	23	7.2	NT$132,232	11.0

Note重要發現：B2B產品滲透率天花板

最高滲透率僅27.9% (玉米粒)，遠低於B2C零售的典型KVI(50-70%)。

原因： - B2B客戶高度專業化 - 每家餐廳菜單不同 - 屏東市僅315位客戶 - 小樣本導致分散 - 產品組合多達數千SKU - 長尾分布嚴重

結論: 在B2B環境，10-20%滲透率已是高滲透KVI。先前財務分析中「70-90%滲透率」為錯誤數據，已修正。

4.2 依毛利與滲透率的產品分類

# Calculate margins using time-matched avg_cost (not latest_cost)
product_analysis = product_penetration.with_columns([
    # Use avg_cost (2025 average) for margin calculation
    pl.when(pl.col("avg_cost").is_not_null() & (pl.col("avg_cost") > 0))
      .then(((pl.col("avg_price") - pl.col("avg_cost")) / pl.col("avg_price") * 100))
      .otherwise(None)
      .alias("margin_pct"),
    # Calculate margin amount for weighted aggregation
    pl.when(pl.col("avg_cost").is_not_null() & (pl.col("avg_cost") > 0))
      .then(pl.col("total_revenue") - (pl.col("avg_cost") * pl.col("total_qty")))
      .otherwise(None)
      .alias("margin_amount"),
    pl.when(pl.col("penetration_pct") >= 15)
      .then(pl.lit("High Penetration (≥15%)"))
      .when(pl.col("penetration_pct") >= 10)
      .then(pl.lit("Medium Penetration (10-15%)"))
      .when(pl.col("penetration_pct") >= 5)
      .then(pl.lit("Low Penetration (5-10%)"))
      .otherwise(pl.lit("Very Low (<5%)"))
      .alias("penetration_tier"),
    pl.when(pl.col("total_revenue") >= 200000)
      .then(pl.lit("High Revenue (≥200K)"))
      .when(pl.col("total_revenue") >= 100000)
      .then(pl.lit("Medium Revenue (100-200K)"))
      .otherwise(pl.lit("Low Revenue (<100K)"))
      .alias("revenue_tier")
]).filter(
    pl.col("margin_pct").is_not_null()  # Exclude items without cost data
).fill_nan(0)  # Replace any NaN values with 0

# Strategic quadrants with weighted margin (correct aggregation)
quadrant_summary = product_analysis.group_by("penetration_tier").agg([
    pl.count().alias("product_count"),
    pl.col("margin_amount").sum().alias("total_margin"),
    pl.col("total_revenue").sum().alias("total_revenue")
]).with_columns(
    # Weighted margin = Total Margin / Total Revenue * 100
    (pl.col("total_margin") / pl.col("total_revenue") * 100).alias("weighted_margin_pct")
).sort("product_count", descending=True)

(
    GT(quadrant_summary.select(["penetration_tier", "product_count", "weighted_margin_pct", "total_revenue"]))
    .tab_header(
        title="產品策略分類",
        subtitle="依滲透率分層 (使用加權毛利率)"
    )
    .cols_label(weighted_margin_pct="Weighted Margin %")
    .fmt_number("product_count", decimals=0)
    .fmt_number("weighted_margin_pct", decimals=1)
    .fmt_currency("total_revenue", currency="TWD", decimals=0, use_subunits=False)
)

產品策略分類
依滲透率分層 (使用加權毛利率)
penetration_tier	product_count	Weighted Margin %	total_revenue
Very Low (<5%)	737	20.9	NT$15,558,491
Low Penetration (5-10%)	58	17.9	NT$6,161,908
Medium Penetration (10-15%)	9	10.4	NT$1,949,792
High Penetration (≥15%)	6	12.8	NT$1,882,306

5. KVI候選產品篩選

5.1 高滲透率 + 可折扣空間的KVI

# KVI criteria: High penetration + margin room for discount
kvi_candidates = product_analysis.filter(
    (pl.col("penetration_pct") >= 10) &  # Top tier penetration
    (pl.col("margin_pct").is_not_null()) &
    (pl.col("margin_pct") >= 20)  # Enough margin to discount
).select([
    "item_code", "product_name", "product_category_1",
    "penetration_pct", "margin_pct", "avg_price", "avg_cost", "total_revenue"
]).sort("penetration_pct", descending=True).head(15)

(
    GT(kvi_candidates)
    .tab_header(
        title="KVI候選產品 (高滲透+高毛利)",
        subtitle="滲透率≥10% AND 毛利率≥20% (使用2025年平均成本)"
    )
    .cols_label(avg_cost="Avg Cost (2025)")
    .fmt_number("penetration_pct", decimals=1)
    .fmt_number("margin_pct", decimals=1)
    .fmt_number("avg_price", decimals=1)
    .fmt_number("avg_cost", decimals=2)
    .fmt_currency("total_revenue", currency="TWD", decimals=0, use_subunits=False)
    .data_color(
        columns="margin_pct",
        palette=["#ffffcc", "#c7e9b4", "#7fcdbb", "#41b6c4", "#225ea8"],
        domain=[20, 40]
    )
)

KVI候選產品 (高滲透+高毛利)
滲透率≥10% AND 毛利率≥20% (使用2025年平均成本)
item_code	product_name	product_category_1	penetration_pct	margin_pct	avg_price	Avg Cost (2025)	total_revenue
25888	伊植麥玉米粒(易開)340g.	2	28.0	21.1	19.4	15.31	NT$466,648

NoteKVI選擇策略：滲透率優先 (Penetration-First)

為何優先選擇高滲透率產品作為KVI: - ✅ 高滲透率 = 客戶已熟悉的產品，能吸引注意力 - ✅ 對非獨家客戶有吸引力，可搶佔競爭對手客戶 - ✅ 作為「門票」引流，帶動Cross-sell銷售

KVI選擇標準: 1. ✅ 最高滲透率 (吸引最多客戶的產品) 2. ✅ 折扣後毛利 ≥ 8% (可持續盈利) 3. ✅ 每個類別最多2個產品 (避免自我競食)

Important毛利計算說明 (Margin Calculation Methodology)

使用的價格來源 (當前即時資料):

欄位	來源	說明
price_standard (標準價)	product_information	單件銷售牌價，用於計算促銷毛利
price_1 (箱價)	product_information	整箱銷售牌價
latest_cost (最新成本)	product_information	最新採購成本

計算公式:

• 現有毛利 = (price_standard - latest_cost) / price_standard × 100
• 折扣後毛利 = (promo_price - latest_cost) / promo_price × 100
  • 其中 promo_price = price_standard × (1 - discount%/100)

重要: 使用 price_standard 和 latest_cost 計算當前毛利，反映現在執行促銷的實際盈利狀況。

TipCycle 1 KVI推薦：玉米粒 (Item #25888)

為何選這個作為KVI (當前資料 2024-12):

✅ 最高滲透率 - 28.0% 滲透率 (屏東市第一名!) ✅ 超高毛利空間 - 折扣後毛利 49.0% (>>8%門檻) ✅ 知名度高 - 早餐店必備品，競爭對手客戶也認識 ✅ 類別平衡 - Category 2 (乾貨)，與冷凍品搭配

促銷設計 (當前價格): - 標準價: $26/罐 - 成本: $11.94/罐 - 促銷價: $23.40/罐 (10% off) - 條件: 單筆≥$3,000 - 現有毛利: 54.1% - 促銷後毛利: 49.0% (非常健康)

類別平衡規則: - 每個 product_category_1 最多2項 - Cycle 1: Cat 2 (2), Cat 4 (1), Cat 5 (2) ✅

6. Cross-Sell產品篩選

6.1 成長型產品：H1→H2客戶成長分析

# DATA-DRIVEN: Select products with ACTUAL H1→H2 customer growth
# Key insight: High-penetration products may be DECLINING
# - 玉米粒 28% pen but -10 customers H1→H2
# - 小肉豆 19% pen but -13 customers H1→H2
# Focus on GROWING products instead

# Join product metrics with growth data
growth_candidates = (
    PRODUCT_METRICS
    .join(
        PRODUCT_GROWTH.select(["item_code", "h1_customers", "h2_customers", "customer_growth"]),
        on="item_code",
        how="left"
    )
    .filter(pl.col("customer_growth") >= 2)  # At least +2 customers H1→H2
    .filter(pl.col("margin_pct").is_not_null())
    .filter(pl.col("margin_pct") >= 15)  # Healthy margin for cross-sell
    .filter(pl.col("h2_customers") >= 5)  # Has meaningful H2 traction
    .with_columns([
        # Calculate margin after 10% discount
        (((pl.col("avg_price") * 0.9) - pl.col("avg_cost"))
         / (pl.col("avg_price") * 0.9) * 100)
        .alias("margin_after_10pct")
    ])
    .select([
        "item_code", "product_name", "product_category_1",
        "penetration_pct", "margin_pct", "margin_after_10pct",
        "h1_customers", "h2_customers", "customer_growth", "total_revenue"
    ])
    .sort("customer_growth", descending=True)
    .head(20)
)

(
    GT(growth_candidates)
    .tab_header(
        title="成長型產品 (H1→H2客戶數增加)",
        subtitle="篩選條件：H1→H2 ≥+2客戶 + 毛利≥15% + H2客戶≥5"
    )
    .fmt_number(["penetration_pct", "margin_pct", "margin_after_10pct"], decimals=1)
    .fmt_number(["h1_customers", "h2_customers", "customer_growth"], decimals=0)
    .fmt_currency("total_revenue", currency="TWD", decimals=0, use_subunits=False)
    .data_color(
        columns="customer_growth",
        palette=["#c7e9b4", "#41b6c4", "#225ea8"],
        domain=[2, 10]
    )
    .data_color(
        columns="margin_after_10pct",
        palette=["#fee5d9", "#fcae91", "#27ae60"],
        domain=[0, 25]
    )
)

成長型產品 (H1→H2客戶數增加)
篩選條件：H1→H2 ≥+2客戶 + 毛利≥15% + H2客戶≥5
item_code	product_name	product_category_1	penetration_pct	margin_pct	margin_after_10pct	h1_customers	h2_customers	customer_growth	total_revenue
53028	正點煙燻雞肉片1K.	5	8.2	16.1	6.8	11	20	9	NT$32,671
54021	奇津阿在伯手工蔥抓餅10入.	5	13.8	17.3	8.1	29	35	6	NT$151,340
41058	蛋皮(原味)30片.	5	2.2	29.9	22.1	3	7	4	NT$74,496
25146	HISUN意麵50入	2	2.5	17.8	8.7	5	8	3	NT$43,804
53211	日規薯餅20片.	5	5.3	25.9	17.7	11	13	2	NT$42,307
24225	美麗家顆粒藍莓餡 1K	2	4.4	21.5	12.7	11	13	2	NT$15,645
21888	元氣豬肉鬆3K	2	4.4	20.5	11.7	10	12	2	NT$87,838
71891	0號夾鏈袋 10入	7	3.1	32.7	25.2	7	9	2	NT$3,756
44050	愛麵族鍋燒麵 250g.	4	2.8	17.2	8.0	7	9	2	NT$16,639
54011	奇津熟鍋貼1500g(約50粒)	5	2.2	33.3	25.9	4	6	2	NT$10,183
23774	歐瑞餅乾碎粉 1P#	2	1.9	19.2	10.2	4	6	2	NT$3,021
21230	福汎巧克力醬 900g	2	1.6	16.2	6.9	3	5	2	NT$11,478
53138	金品豆漿(無糖)1.5K	5	1.6	30.3	22.5	3	5	2	NT$66,143
54044	高麗菜熟水餃(HISUN) 1700g	5	1.6	31.6	24.0	3	5	2	NT$11,088

6.2 類別平衡分析

# Check customer category buying patterns
customer_categories = (
    sales_orders
    .filter(_.delivery_route.isin(PINGTUNG_ROUTES))
    .filter(_.order_date >= "20250701")
    .filter(_.order_date <= "20251130")
    .left_join(sales_order_lines, ["order_type", "order_number"])
    .left_join(product_info, _.item_code == product_info.product_code)
    .group_by(["customer_code", "product_category_1"])
    .agg(category_spend=_.pretax_subtotal.cast("float64").sum())
    .group_by("customer_code")
    .agg(
        num_categories=_.product_category_1.nunique(),
        total_spend=_.category_spend.sum()
    )
    .to_polars()
)

category_distribution = customer_categories.group_by("num_categories").agg([
    pl.count().alias("customer_count"),
    pl.col("total_spend").mean().alias("avg_spend")
]).sort("num_categories")

(
    GT(category_distribution)
    .tab_header(
        title="客戶類別多樣性分布",
        subtitle="屏東市客戶平均購買幾個類別？"
    )
    .fmt_number("customer_count", decimals=0)
    .fmt_currency("avg_spend", currency="TWD", decimals=0, use_subunits=False)
    .cols_label(
        num_categories="購買類別數",
        customer_count="客戶數",
        avg_spend="平均消費"
    )
)

客戶類別多樣性分布
屏東市客戶平均購買幾個類別？
購買類別數	客戶數	平均消費
1	64	NT$12,439
2	42	NT$19,036
3	49	NT$32,443
4	34	NT$43,639
5	28	NT$94,986
6	18	NT$127,381
7	12	NT$123,737
8	2	NT$154,248

Warning發現：類別集中度高

多數客戶只購買1-3個類別 - 這代表跨類別交叉銷售空間巨大。

促銷策略應: 1. 每個類別最多2項 - 避免同類別產品自我競食 (max_items_per_category = 2) 2. 平衡類別組合 - 冷凍+乾貨+冷藏 3. 尊重冷凍空間限制 - 客戶冷凍庫容量有限

7. 最終5 SKU推薦 (動態生成)

7.1 SKU 選擇規則

# =============================================================================
# CYCLE 1 SKU SELECTION RULES
# =============================================================================
CYCLE_RULES = {
    "discount_pct": 10.0,  # Standard discount percentage
    "kvi_min_penetration": 10.0,  # KVI must have high penetration
    "kvi_min_margin_after_discount": 8.0,  # KVI must survive discount
    "crosssell_max_penetration": 15.0,  # Cross-sell targets low penetration
    "crosssell_min_margin": 25.0,  # Cross-sell must have good margin
    "crosssell_min_revenue": 30000.0,  # Cross-sell must have proven demand
    "max_items_per_category": 2,  # Max 2 items from same product_category_1
    "max_frozen_items": 3,  # Respect freezer space (Category 5) - legacy, use max_items_per_category
}

# =============================================================================
# PENETRATION-FIRST CYCLE STRATEGY (Updated Dec 2025)
# =============================================================================
# KVI Selection Criteria (PENETRATION-FIRST):
# 1. Highest penetration item that meets margin threshold
# 2. Margin after discount ≥ 8% (sustainable profitability)
# 3. Max 2 items per product_category_1 per cycle (anti-cannibalization)
#
# KEY INSIGHT: KVI = King item to attract attention from non-exclusive customers
# - High penetration items are already well-known to customers
# - They serve as traffic drivers to bring customers to the promo
# - Cross-sell items introduce customers to new products
#
# ═══════════════════════════════════════════════════════════════════════════════
# MARGIN CALCULATION METHODOLOGY (CURRENT DATA - Dec 2024)
# ═══════════════════════════════════════════════════════════════════════════════
#
# Current Margin = ((price_standard - latest_cost) / price_standard) * 100
# Margin After Discount = ((promo_price - latest_cost) / promo_price) * 100
#   where promo_price = price_standard * (1 - discount_pct/100)
#
# Data sources from product_information table:
# - price_standard (標準價): List price for individual unit sales
# - price_1 (箱價): Discounted price when customer buys a full box
# - latest_cost (最新成本): Most recent procurement cost
#
# CURRENT PENETRATION & MARGIN RANKINGS (Pingtung City):
# ═══════════════════════════════════════════════════════════════════════════════
# Item     | Name        | Pen%  | 標準價 | 成本   | 毛利% | 折後毛利% | Status
# ---------|-------------|-------|--------|--------|-------|-----------|--------
# 25888    | 玉米粒340g  | 28.0% | $26    | $11.94 | 54.1% | 49.0%     | ✅
# 51130    | 小肉豆1K    | 18.9% | $190   | $142.86| 24.8% | 16.5%     | ✅
# 54021    | 蔥抓餅10入  | 13.8% | $85    | $72    | 15.3% | 5.9%      | ⚠️ <8%
# 51296    | 可頌燒餅10片| 9.1%  | $95    | $68    | 28.4% | 20.5%     | ✅
# 41189    | 起司片84片  | 9.1%  | $310   | $258   | 16.8% | 7.5%      | ⚠️ <8%
# 59142    | 香酥雞塊3K  | 6.3%  | $310   | $285   | 8.1%  | -2.2%     | ❌ LOSS
# ═══════════════════════════════════════════════════════════════════════════════

# Role-based margin strategy:
# - KVI (King): ~5% margin after discount (aggressive traffic driver)
# - Volume Builder: 10-15% margin (moderate margin, drives basket)
# - Margin Defender: 20%+ margin (protects profitability)
#
# Key insight: Use LOW-margin items as KVIs (need small discounts to hit 5%)
#              Use HIGH-margin items as margin defenders (capped at 15% max)
# **MAX DISCOUNT: 15%** - All discounts capped at 15%, actual margins may exceed target
EXPERT_CYCLE_OVERRIDES = {
    1: {
        "theme": "經典高滲透 (Classic High Penetration)",
        # Category balance: Cat 2 (2), Cat 4 (1), Cat 5 (2) ✅
        # Change: Replaced 小肉豆 with 馬鈴薯條 (cannot be with 熱狗)
        "skus": [
            {"code": "51171", "role": "kvi", "target_margin": 5.0, "discount": 4.8},
            # 安美熱狗50條 - Cat 5 | $110, 成本$99.50, 基礎毛利9.5%
            {"code": "51284", "role": "volume_builder", "target_margin": 12.0, "discount": 10.5},
            # 馬鈴薯條2K - Cat 5 | $165, 成本$130, 基礎毛利21.2%
            {"code": "21260", "role": "volume_builder", "target_margin": 12.0, "discount": 10.0},
            # 香蒜抹醬800g - Cat 2 | $240, 成本$190, 基礎毛利20.8%
            {"code": "25888", "role": "margin_defender", "target_margin": 20.0, "discount": 15.0},
            # 玉米粒340g - Cat 2 | $26, 成本$11.94, 基礎毛利54.1% → 15% cap
            {"code": "44051", "role": "margin_defender", "target_margin": 20.0, "discount": 15.0},
            # 蘑菇醬包140g - Cat 4 | $12, 成本$6.51, 基礎毛利45.8% → 15% cap
        ]
    },
    2: {
        "theme": "冷凍明星 (Frozen Stars)",
        # Category balance: Cat 2 (2), Cat 4 (1), Cat 5 (2) ✅
        # Change: Replaced 蔥抓餅 with 香香雞米花 (蔥抓餅 was consecutive with Cycle 3)
        "skus": [
            {"code": "51130", "role": "kvi", "target_margin": 5.0, "discount": 15.0},
            # 小肉豆1K - Cat 5 | $190, 成本$142.86, 基礎毛利24.8% → 15% cap
            {"code": "53112", "role": "volume_builder", "target_margin": 12.0, "discount": 7.8},
            # 香香雞米花1K - Cat 5 | $180, 成本$146, 基礎毛利18.9%
            {"code": "22138", "role": "volume_builder", "target_margin": 12.0, "discount": 14.9},
            # 蜂蜜糖漿3K - Cat 2 | $167, 成本$125, 基礎毛利25.1%
            {"code": "43599", "role": "margin_defender", "target_margin": 20.0, "discount": 15.0},
            # 烏龍麵200g - Cat 4 | $13, 成本$8.75, 基礎毛利32.7% → 15% cap
            {"code": "22020", "role": "margin_defender", "target_margin": 20.0, "discount": 12.9},
            # 咖啡紅茶5包 - Cat 2 | $87, 成本$60.62, 基礎毛利30.3%
        ]
    },
    3: {
        "theme": "早餐煎台 (Breakfast Griddle)",
        # Category balance: Cat 1 (1), Cat 2 (1), Cat 4 (1), Cat 5 (2) ✅
        "skus": [
            {"code": "54021", "role": "kvi", "target_margin": 5.0, "discount": 10.8},
            # 蔥抓餅10入 - Cat 5 | $85, 成本$72, 基礎毛利15.3%
            {"code": "41018", "role": "volume_builder", "target_margin": 12.0, "discount": 15.0},
            # 蛋餅皮原30入 - Cat 5 | $78, 成本$46, 基礎毛利41.0% → 15% cap
            {"code": "11055", "role": "volume_builder", "target_margin": 12.0, "discount": 6.6},
            # 原味奶酥700g - Cat 1 | $180, 成本$148, 基礎毛利17.8%
            {"code": "44051", "role": "margin_defender", "target_margin": 20.0, "discount": 15.0},
            # 蘑菇醬包140g - Cat 4 | $12, 成本$6.51, 基礎毛利45.8% → 15% cap
            {"code": "22287", "role": "margin_defender", "target_margin": 20.0, "discount": 15.0},
            # 高級精鹽1k - Cat 2 | $21, 成本$13.96, 基礎毛利33.5% → 15% cap
        ]
    },
    4: {
        "theme": "起司與冷藏 (Cheese & Refrigerated)",
        # Category balance: Cat 2 (1), Cat 4 (2), Cat 5 (2) ✅
        "skus": [
            {"code": "51296", "role": "kvi", "target_margin": 5.0, "discount": 15.0},
            # 可頌燒餅10片 - Cat 5 | $95, 成本$68, 基礎毛利28.4% → 15% cap
            {"code": "41189", "role": "volume_builder", "target_margin": 12.0, "discount": 5.4},
            # 起司片84片 - Cat 4 | $310, 成本$258, 基礎毛利16.8%
            {"code": "51284", "role": "volume_builder", "target_margin": 12.0, "discount": 10.5},
            # 馬鈴薯條2K - Cat 5 | $165, 成本$130, 基礎毛利21.2%
            {"code": "43599", "role": "margin_defender", "target_margin": 20.0, "discount": 15.0},
            # 烏龍麵200g - Cat 4 | $13, 成本$8.75, 基礎毛利32.7% → 15% cap
            {"code": "22138", "role": "margin_defender", "target_margin": 20.0, "discount": 6.4},
            # 蜂蜜糖漿3K - Cat 2 | $167, 成本$125, 基礎毛利25.1%
        ]
    },
    5: {
        "theme": "烘焙與調味 (Bakery & Seasoning)",
        # Category balance: Cat 1 (1), Cat 2 (1), Cat 4 (1), Cat 5 (2) ✅
        # Change: Replaced 香香雞米花 with 安美燻腸 (香香雞米花 was consecutive with Cycle 6)
        "skus": [
            {"code": "22107", "role": "kvi", "target_margin": 5.0, "discount": 5.9},
            # 飛燕煉乳375g - Cat 2 | $48, 成本$42.91, 基礎毛利10.6%
            {"code": "11055", "role": "volume_builder", "target_margin": 12.0, "discount": 6.6},
            # 原味奶酥700g - Cat 1 | $180, 成本$148, 基礎毛利17.8%
            {"code": "51172", "role": "volume_builder", "target_margin": 12.0, "discount": 3.2},
            # 安美燻腸1K - Cat 5 | $185, 成本$166.67, 基礎毛利9.9%
            {"code": "41018", "role": "margin_defender", "target_margin": 20.0, "discount": 15.0},
            # 蛋餅皮原30入 - Cat 5 | $78, 成本$46, 基礎毛利41.0% → 15% cap
            {"code": "44052", "role": "margin_defender", "target_margin": 20.0, "discount": 15.0},
            # 黑胡椒醬包140g - Cat 4 | $12, 成本$6.51, 基礎毛利45.8% → 15% cap
        ]
    },
    6: {
        "theme": "雞肉專區 (Chicken Zone)",
        # Category balance: Cat 2 (2), Cat 4 (1), Cat 5 (2) ✅
        "skus": [
            {"code": "53112", "role": "kvi", "target_margin": 5.0, "discount": 14.6},
            # 香香雞米花1K - Cat 5 | $180, 成本$146, 基礎毛利18.9%
            {"code": "23627", "role": "volume_builder", "target_margin": 12.0, "discount": 12.3},
            # 一番香Q雞絲50入 - Cat 2 | $350, 成本$270, 基礎毛利22.9%
            {"code": "51144", "role": "volume_builder", "target_margin": 12.0, "discount": 1.6},
            # 香讚黑椒雞堡50片 - Cat 5 | $220, 成本$191.40, 基礎毛利13.0%
            {"code": "21260", "role": "margin_defender", "target_margin": 20.0, "discount": 1.0},
            # 香蒜抹醬800g - Cat 2 | $240, 成本$190, 基礎毛利20.8%
            {"code": "44051", "role": "margin_defender", "target_margin": 20.0, "discount": 15.0},
            # 蘑菇醬包140g - Cat 4 | $12, 成本$6.51, 基礎毛利45.8% → 15% cap
        ]
    }
}

# For backward compatibility, CYCLE_1_EXPERT_OVERRIDE references the first cycle
CYCLE_1_EXPERT_OVERRIDE = EXPERT_CYCLE_OVERRIDES[1]
# Set to None to disable ALL expert overrides:
# EXPERT_CYCLE_OVERRIDES = None
# CYCLE_1_EXPERT_OVERRIDE = None

def select_cycle_skus(
    product_metrics: pl.DataFrame,
    rules: dict,
    excluded_items: list = []
) -> pl.DataFrame:
    """Select 1 KVI + 4 Cross-sell items based on rules."""

    discount = rules["discount_pct"]

    # Add margin after discount calculation
    metrics_with_discount = product_metrics.with_columns([
        (((pl.col("avg_price") * (1 - discount/100)) - pl.col("avg_cost"))
         / (pl.col("avg_price") * (1 - discount/100)) * 100)
        .alias("margin_after_discount"),
        (pl.col("avg_price") * (1 - discount/100)).alias("promo_price"),
        pl.lit(discount).alias("discount_pct")
    ]).filter(~pl.col("item_code").is_in(excluded_items))

    # Select KVI: highest penetration that survives discount
    kvi_pool = (
        metrics_with_discount
        .filter(pl.col("penetration_pct") >= rules["kvi_min_penetration"])
        .filter(pl.col("margin_after_discount") >= rules["kvi_min_margin_after_discount"])
        .sort("penetration_pct", descending=True)
    )
    kvi = kvi_pool.head(1).with_columns(pl.lit("KVI").alias("slot"))

    if len(kvi) == 0:
        print("⚠️ No KVI candidates meet criteria!")
        return pl.DataFrame()

    kvi_code = kvi["item_code"][0]
    kvi_category = kvi["product_category_1"][0]

    # Select Cross-sell: high margin, lower penetration, category balanced
    crosssell_pool = (
        metrics_with_discount
        .filter(pl.col("item_code") != kvi_code)
        .filter(pl.col("penetration_pct") <= rules["crosssell_max_penetration"])
        .filter(pl.col("penetration_pct") >= 2.0)
        .filter(pl.col("margin_pct") >= rules["crosssell_min_margin"])
        .filter(pl.col("total_revenue") >= rules["crosssell_min_revenue"])
        .sort("margin_pct", descending=True)
    )

    # Apply category balance rules - track ALL categories, not just frozen
    # Initialize category counts with KVI's category
    category_counts = {}
    if kvi_category:
        category_counts[kvi_category] = 1

    max_per_category = rules.get("max_items_per_category", 2)

    selected_crosssell = []
    for row in crosssell_pool.iter_rows(named=True):
        cat = row["product_category_1"]
        current_count = category_counts.get(cat, 0)

        # Skip if category already has max items
        if current_count >= max_per_category:
            continue
        if len(selected_crosssell) >= 4:
            break
        selected_crosssell.append(row)
        category_counts[cat] = current_count + 1

    if len(selected_crosssell) < 4:
        print(f"⚠️ Only found {len(selected_crosssell)} cross-sell candidates")

    # Combine results
    crosssell_df = pl.DataFrame(selected_crosssell).with_columns([
        pl.concat_str([pl.lit("Cross-sell #"), (pl.arange(1, len(selected_crosssell)+1)).cast(pl.Utf8)]).alias("slot")
    ])

    return pl.concat([kvi, crosssell_df])

# Generate Cycle 1 SKUs
# Check for expert override first
if CYCLE_1_EXPERT_OVERRIDE is not None:
    # Check if new format (per-SKU discounts) or legacy format
    sku_configs = CYCLE_1_EXPERT_OVERRIDE.get("skus", [])

    if sku_configs:
        # New format: per-SKU discounts with roles
        print("🎯 Using expert-recommended Cycle 1 SKUs (role-based margins)")

        processed_rows = []
        for sku_config in sku_configs:
            code = sku_config["code"]
            role = sku_config["role"]
            discount = sku_config["discount"]
            target_margin = sku_config["target_margin"]

            item_row = PRODUCT_METRICS.filter(pl.col("item_code") == code)
            if len(item_row) == 0:
                print(f"⚠️ Item {code} not found")
                continue

            # Apply per-SKU discount
            item_row = item_row.with_columns([
                (((pl.col("avg_price") * (1 - discount/100)) - pl.col("avg_cost"))
                 / (pl.col("avg_price") * (1 - discount/100)) * 100)
                .alias("margin_after_discount"),
                (pl.col("avg_price") * (1 - discount/100)).alias("promo_price"),
                pl.lit(discount).alias("discount_pct"),
                pl.lit(role).alias("role"),
                pl.lit(target_margin).alias("target_margin")
            ])

            # Assign slot label based on role
            if role == "kvi":
                slot_label = "KVI"
            elif role == "volume_builder":
                slot_label = "Volume Builder"
            else:
                slot_label = "Margin Defender"

            item_row = item_row.with_columns(pl.lit(slot_label).alias("slot"))
            processed_rows.append(item_row)

        CYCLE_1_SKUS = pl.concat(processed_rows) if processed_rows else pl.DataFrame()

    else:
        # Legacy format: kvi + crosssell with single discount
        print("🎯 Using expert-recommended Cycle 1 SKUs (override enabled)")

        discount = CYCLE_RULES["discount_pct"]
        expert_items = [CYCLE_1_EXPERT_OVERRIDE["kvi"]] + CYCLE_1_EXPERT_OVERRIDE["crosssell"]

        CYCLE_1_SKUS = (
            PRODUCT_METRICS
            .filter(pl.col("item_code").is_in(expert_items))
            .with_columns([
                (((pl.col("avg_price") * (1 - discount/100)) - pl.col("avg_cost"))
                 / (pl.col("avg_price") * (1 - discount/100)) * 100)
                .alias("margin_after_discount"),
                (pl.col("avg_price") * (1 - discount/100)).alias("promo_price"),
                pl.lit(discount).alias("discount_pct")
            ])
            .with_columns([
                pl.when(pl.col("item_code") == CYCLE_1_EXPERT_OVERRIDE["kvi"])
                .then(pl.lit("KVI"))
                .otherwise(
                    pl.concat_str([
                        pl.lit("Cross-sell #"),
                        pl.lit("1")
                    ])
                )
                .alias("slot")
            ])
        )

        # Properly assign cross-sell slot numbers
        kvi_row = CYCLE_1_SKUS.filter(pl.col("item_code") == CYCLE_1_EXPERT_OVERRIDE["kvi"])
        crosssell_rows = CYCLE_1_SKUS.filter(pl.col("item_code") != CYCLE_1_EXPERT_OVERRIDE["kvi"])

        ordered_crosssell = []
        for i, code in enumerate(CYCLE_1_EXPERT_OVERRIDE["crosssell"], 1):
            row = crosssell_rows.filter(pl.col("item_code") == code)
            if len(row) > 0:
                ordered_crosssell.append(row.with_columns(pl.lit(f"Cross-sell #{i}").alias("slot")))

        if ordered_crosssell:
            CYCLE_1_SKUS = pl.concat([kvi_row] + ordered_crosssell)
        else:
            CYCLE_1_SKUS = kvi_row

else:
    print("📊 Using algorithmic Cycle 1 SKU selection")
    CYCLE_1_SKUS = select_cycle_skus(PRODUCT_METRICS, CYCLE_RULES)

🎯 Using expert-recommended Cycle 1 SKUs (role-based margins)

7.2 完整產品組合

if len(CYCLE_1_SKUS) > 0:
    # Check if using new role-based format
    available_cols = CYCLE_1_SKUS.columns
    has_role = "role" in available_cols

    base_cols = ["slot", "item_code", "product_name", "product_category_1",
                 "penetration_pct", "avg_price", "avg_cost", "margin_pct",
                 "promo_price", "margin_after_discount", "discount_pct"]
    if has_role:
        base_cols.extend(["role", "target_margin"])

    final_display = (
        CYCLE_1_SKUS
        .select([c for c in base_cols if c in available_cols])
        .with_columns([
            pl.col("product_category_1").replace(CAT_NAMES).alias("category"),
            (pl.col("avg_price") - pl.col("promo_price")).alias("savings")
        ])
    )

    if has_role:
        # Role-based display
        (
            GT(final_display.select([
                "slot", "item_code", "product_name", "category",
                "penetration_pct", "discount_pct", "avg_price", "promo_price",
                "avg_cost", "margin_pct", "margin_after_discount", "target_margin"
            ]))
            .tab_header(
                title="第一週期促銷組合 (角色導向毛利策略)",
                subtitle="KVI 5% | Volume Builder 12% | Margin Defender 20%"
            )
            .cols_label(
                slot="角色", item_code="品號", product_name="品名", category="類別",
                penetration_pct="滲透率%", discount_pct="折扣%",
                avg_price="現價", promo_price="促銷價", avg_cost="成本",
                margin_pct="原毛利%", margin_after_discount="折後毛利%", target_margin="目標%"
            )
            .fmt_number(["penetration_pct", "discount_pct"], decimals=1)
            .fmt_currency(["avg_price", "avg_cost", "promo_price"], currency="TWD", decimals=0, use_subunits=False)
            .fmt_number(["margin_pct", "margin_after_discount", "target_margin"], decimals=1)
            .data_color(
                columns="margin_after_discount",
                palette=["#fee5d9", "#fcae91", "#fb6a4a", "#27ae60"],
                domain=[5, 30]
            )
            .tab_style(
                style=style.fill(color="#e8f5e9"),
                locations=loc.body(rows=pl.col("slot") == "KVI")
            )
            .tab_style(
                style=style.fill(color="#fff3e0"),
                locations=loc.body(rows=pl.col("slot") == "Volume Builder")
            )
            .tab_style(
                style=style.fill(color="#e3f2fd"),
                locations=loc.body(rows=pl.col("slot") == "Margin Defender")
            )
        )
    else:
        # Legacy display
        (
            GT(final_display.select([
                "slot", "item_code", "product_name", "category",
                "penetration_pct", "avg_price", "avg_cost", "margin_pct",
                "promo_price", "margin_after_discount", "savings"
            ]))
            .tab_header(
                title="第一週期促銷組合 (動態生成)",
                subtitle=f"選擇規則：KVI≥{CYCLE_RULES['kvi_min_penetration']}%滲透 + Cross-sell≥{CYCLE_RULES['crosssell_min_margin']}%毛利"
            )
            .cols_label(
                avg_price="現價", avg_cost="成本", margin_pct="原毛利%",
                promo_price="促銷價", margin_after_discount="促銷毛利%", savings="折扣額"
            )
            .fmt_number("penetration_pct", decimals=1)
            .fmt_currency(["avg_price", "avg_cost", "promo_price", "savings"], currency="TWD", decimals=0, use_subunits=False)
            .fmt_number(["margin_pct", "margin_after_discount"], decimals=1)
            .data_color(
                columns="margin_after_discount",
                palette=["#fee5d9", "#fcae91", "#fb6a4a", "#27ae60"],
                domain=[5, 30]
            )
        )
else:
    print("❌ 無法生成 Cycle 1 SKU 組合，請檢查選擇規則")

Note角色導向毛利策略說明

目前使用: 專家推薦 (Expert Override) - 角色導向毛利策略

Cycle 1 使用角色導向毛利策略，每個品項依其角色有不同目標毛利：

角色與目標毛利:

• KVI: ~5% → 用低毛利商品做KVI，只需小折扣
• Volume Builder: 10-15% → 中等毛利帶動客單
• Margin Defender: 20%+ → 高毛利商品可承受大折扣

Cycle 1 選品 (類別平衡: Cat 2=2, Cat 4=1, Cat 5=2 ✅):

• KVI 51171 安美熱狗50條 - Cat 5, 12%滲透率, 4.8%折扣 → 5%毛利
• Vol Builder 51284 馬鈴薯條2K - Cat 5, 6.8%滲透率, 10.5%折扣 → 12%毛利
• Vol Builder 21260 香蒜抹醬 - Cat 2, 8.6%滲透率, 10%折扣 → 12%毛利
• Margin Def 25888 玉米粒 - Cat 2, 26.3%滲透率, 15%折扣 → ~41%毛利
• Margin Def 44051 蘑菇醬包 - Cat 4, 6.4%滲透率, 15%折扣 → ~36%毛利

關鍵策略 (MAX 折扣 15%):

• 低毛利商品(9.5%) → KVI，只需4.8%折扣達到5%目標
• 高毛利商品 → Margin Defender，折扣上限15%，實際毛利超過目標
• 每個類別最多2項 (避免自我競食)
• 熱狗/小肉豆不同時出現 ✅ 無連續週期重複 ✅

所有數字均從資料庫即時計算

7.2 促銷經濟學模擬

# Simulate a typical qualifying order using Cycle 1 SKUs
sample_order = pl.DataFrame({
    "item": [
        "安美熱狗 (KVI)",
        "馬鈴薯條 (Vol)",
        "香蒜抹醬 (Vol)",
        "玉米粒 (Margin)",
        "蘑菇醬包 (Margin)",
        "小計(促銷品)",
        "其他品項(估)",
        "訂單總計"
    ],
    "qty": [2.0, 1.0, 1.0, 3.0, 5.0, None, None, None],
    "regular": [220.0, 165.0, 240.0, 78.0, 60.0, 763.0, 2237.0, 3000.0],
    "promo": [209.0, 148.0, 216.0, 66.0, 51.0, 690.0, 2237.0, 2927.0],
    "discount": [11.0, 17.0, 24.0, 12.0, 9.0, 73.0, 0.0, 73.0],
    "cost": [200.0, 132.0, 192.0, 39.0, 30.0, 593.0, 1678.0, 2271.0],
    "regular_margin": [20.0, 33.0, 48.0, 39.0, 30.0, 170.0, 559.0, 729.0],
    "promo_margin": [9.0, 16.0, 24.0, 27.0, 21.0, 97.0, 559.0, 656.0]
})

(
    GT(sample_order)
    .tab_header(
        title="促銷經濟學模擬",
        subtitle="典型$5,000訂單的毛利影響"
    )
    .fmt_currency(["regular", "promo", "discount", "cost", "regular_margin", "promo_margin"],
                  currency="TWD", decimals=0, use_subunits=False)
)

促銷經濟學模擬
典型$5,000訂單的毛利影響
item	qty	regular	promo	discount	cost	regular_margin	promo_margin
安美熱狗 (KVI)	2.0	NT$220	NT$209	NT$11	NT$200	NT$20	NT$9
馬鈴薯條 (Vol)	1.0	NT$165	NT$148	NT$17	NT$132	NT$33	NT$16
香蒜抹醬 (Vol)	1.0	NT$240	NT$216	NT$24	NT$192	NT$48	NT$24
玉米粒 (Margin)	3.0	NT$78	NT$66	NT$12	NT$39	NT$39	NT$27
蘑菇醬包 (Margin)	5.0	NT$60	NT$51	NT$9	NT$30	NT$30	NT$21
小計(促銷品)	None	NT$763	NT$690	NT$73	NT$593	NT$170	NT$97
其他品項(估)	None	NT$2,237	NT$2,237	NT$0	NT$1,678	NT$559	NT$559
訂單總計	None	NT$3,000	NT$2,927	NT$73	NT$2,271	NT$729	NT$656

Note經濟學分析

情境: 客戶剛好達到$3,000門檻，購買促銷品約$763

毛利影響: - 折扣損失: -$73 - 促銷品毛利: $97 (vs 原本可得$170) - 毛利減少: -$73 (-43% on promo items)

BUT - 關鍵假設: - 客戶為達門檻，購買其他品項$2,237 - 其他品項平均毛利25% = +$559毛利 - 淨毛利: $656 (促銷品$97 + 其他$559)

盈虧平衡條件: - 低折扣策略(MAX 15%)限制毛利損失 - 門檻$3,000確保足夠購買額 ✅

戰略價值: - 提升促銷品滲透率，建立品牌認知 - 如果防止客戶流向競爭對手，終身價值>>單次折扣損失

8. 客戶目標鎖定

8.1 客戶優先級評分

# Customer targeting score
h1_spend = (
    sales_orders
    .filter(_.delivery_route.isin(PINGTUNG_ROUTES))
    .filter(_.order_date >= "20250101")
    .filter(_.order_date <= "20250630")
    .left_join(sales_order_lines, ["order_type", "order_number"])
    .left_join(product_info, _.item_code == product_info.product_code)
    .group_by("customer_code")
    .agg(
        h1_revenue=_.pretax_subtotal.cast("float64").sum(),
        h1_orders=_.order_number.nunique(),
        h1_categories=_.product_category_1.nunique()
    )
)

h2_spend = (
    sales_orders
    .filter(_.delivery_route.isin(PINGTUNG_ROUTES))
    .filter(_.order_date >= "20250701")
    .filter(_.order_date <= "20251130")
    .left_join(sales_order_lines, ["order_type", "order_number"])
    .left_join(product_info, _.item_code == product_info.product_code)
    .group_by("customer_code")
    .agg(
        h2_revenue=_.pretax_subtotal.cast("float64").sum(),
        h2_orders=_.order_number.nunique(),
        h2_categories=_.product_category_1.nunique()
    )
)

customer_scores = (
    customers
    .filter(_.customer_route.isin(PINGTUNG_ROUTES))
    .left_join(h1_spend, customers.customer_code == h1_spend.customer_code)
    .drop("customer_code_right")
    .left_join(h2_spend, _.customer_code == h2_spend.customer_code)
    .drop("customer_code_right")
    .mutate(
        h1_revenue=_.h1_revenue.fillna(0),
        h2_revenue=_.h2_revenue.fillna(0),
        h1_categories=_.h1_categories.fillna(0),
        h2_categories=_.h2_categories.fillna(0),
        h1_orders=_.h1_orders.fillna(0),
        h2_orders=_.h2_orders.fillna(0)
    )
    .mutate(
        # Decline score (0-3)
        decline_score=(
            _.h2_revenue < _.h1_revenue * 0.7
        ).ifelse(3,
            (_.h2_revenue < _.h1_revenue * 0.9).ifelse(2,
                (_.h2_revenue < _.h1_revenue).ifelse(1, 0)
            )
        ),
        # Category gap score (0-3)
        category_gap_score=(
            _.h2_categories <= 1
        ).ifelse(3,
            (_.h2_categories == 2).ifelse(2,
                (_.h2_categories == 3).ifelse(1, 0)
            )
        ),
        # Small ticket score (0-2)
        small_ticket_score=(
            (_.h2_orders > 0) & (_.h2_revenue / _.h2_orders < 2000)
        ).ifelse(2,
            ((_.h2_orders > 0) & (_.h2_revenue / _.h2_orders < 3000)).ifelse(1, 0)
        )
    )
    .filter(_.h2_revenue > 20000)  # Active customers only
    .mutate(
        total_score=(_.decline_score + _.category_gap_score + _.small_ticket_score),
        priority_tier=(
            _.decline_score + _.category_gap_score + _.small_ticket_score >= 6
        ).ifelse("Priority 1 - High",
            (_.decline_score + _.category_gap_score + _.small_ticket_score >= 4).ifelse("Priority 2 - Medium", "Priority 3 - Low")
        )
    )
    .select([
        "customer_code", "trade_name", "customer_route",
        "h2_revenue", "h2_orders", "h2_categories",
        "decline_score", "category_gap_score", "small_ticket_score",
        "total_score", "priority_tier"
    ])
    .order_by(ibis.desc("total_score"), ibis.desc("h2_revenue"))
    .to_polars()
)

# Top priority customers
priority_1 = customer_scores.filter(pl.col("priority_tier") == "Priority 1 - High").head(20)

(
    GT(priority_1)
    .tab_header(
        title="Priority 1 目標客戶 (高優先級)",
        subtitle="評分≥6分：有類別缺口 + 消費下滑 + 小額訂單"
    )
    .fmt_currency("h2_revenue", currency="TWD", decimals=0, use_subunits=False)
    .fmt_number(["h2_orders", "h2_categories", "decline_score", "category_gap_score", "small_ticket_score", "total_score"], decimals=0)
    .data_color(
        columns="total_score",
        palette=["#fee5d9", "#fc9272", "#de2d26", "#a50f15"],
        domain=[6, 8]
    )
)

Priority 1 目標客戶 (高優先級)
評分≥6分：有類別缺口 + 消費下滑 + 小額訂單
customer_code	trade_name	customer_route	h2_revenue	h2_orders	h2_categories	decline_score	category_gap_score	small_ticket_score	total_score	priority_tier
0111278	早食光	A-PT	NT$28,717	23	1	2	3	2	7	Priority 1 - High
0111140	樂活早點	A-PT	NT$27,398	15	2	3	2	2	7	Priority 1 - High
0190024	少年雞宵夜	A-PT	NT$66,565	23	2	3	2	1	6	Priority 1 - High
0130009	公牛隊-信義	A-PT	NT$42,278	28	2	2	2	2	6	Priority 1 - High
0111249	咔啦堡快速早餐-仁愛店	A-PT	NT$38,666	22	3	3	1	2	6	Priority 1 - High
0121142	涵軒風格傳統豆花	A-PT	NT$20,141	10	1	2	3	1	6	Priority 1 - High

8.2 優先級分布

priority_summary = customer_scores.group_by("priority_tier").agg([
    pl.count().alias("customer_count"),
    pl.col("h2_revenue").sum().alias("total_revenue"),
    pl.col("total_score").mean().alias("avg_score")
]).sort("customer_count", descending=True)

(
    GT(priority_summary)
    .tab_header(
        title="目標客戶分層摘要",
        subtitle="屏東市活躍客戶(H2消費>20K)"
    )
    .fmt_number("customer_count", decimals=0)
    .fmt_currency("total_revenue", currency="TWD", decimals=0, use_subunits=False)
    .fmt_number("avg_score", decimals=1)
)

目標客戶分層摘要
屏東市活躍客戶(H2消費>20K)
priority_tier	customer_count	total_revenue	avg_score
Priority 3 - Low	69	NT$8,171,486	2.0
Priority 2 - Medium	41	NT$1,962,590	4.3
Priority 1 - High	6	NT$223,765	6.3

Tip客戶目標策略

Priority 1 (6位, 評分6-7分)

特徵: - 僅購買1-2個類別 (類別缺口大) - H1→H2消費下滑20-40% - 平均訂單<$2,000 (可能分散採購)

行動: 1. 個人化Line訊息 2. 指出具體類別缺口 (例:“看到您都買調味料，要不要試試我們的冷凍品?”) 3. 推薦對應產品(缺Cat 5→推安美熱狗或馬鈴薯條) 4. 首單免運優惠

Priority 2 (44位, 評分4-5分)

特徵: - 中等類別缺口或中等下滑 - 有改善空間但非緊急

行動: 1. Line群發廣播 2. 標準促銷訊息模板 3. 門市海報曝光

Priority 3 (其他)

行動: 1. 門市內部推廣 2. 結帳時提醒

9. 成功指標與監控

9.1 第一週(Week 1-2)關鍵指標

week1_metrics = pl.DataFrame({
    "metric": [
        "促銷兌換率",
        "平均票均提升",
        "促銷品購買客戶數",
        "跨類別購買率",
        "總毛利影響"
    ],
    "target": [
        "15% (40/262客戶)",
        "+15% ($2,700→$3,100)",
        "較基準期+10%",
        "+10% Cat5滲透",
        "<5% 下滑"
    ],
    "measurement": [
        "使用促銷碼/達門檻的訂單數",
        "符合促銷訂單 vs 歷史均值",
        "促銷品項的buying_customers.nunique()",
        "購買Cat5的customer_code.nunique()",
        "(促銷週毛利 - 基準週毛利) / 基準週毛利"
    ]
})

(
    GT(week1_metrics)
    .tab_header(
        title="Week 1-2 成功指標",
        subtitle="立即追蹤指標"
    )
    .cols_label(
        metric="指標",
        target="目標",
        measurement="計算方式"
    )
)

Week 1-2 成功指標
立即追蹤指標
指標	目標	計算方式
促銷兌換率	15% (40/262客戶)	使用促銷碼/達門檻的訂單數
平均票均提升	+15% ($2,700→$3,100)	符合促銷訂單 vs 歷史均值
促銷品購買客戶數	較基準期+10%	促銷品項的buying_customers.nunique()
跨類別購買率	+10% Cat5滲透	購買Cat5的customer_code.nunique()
總毛利影響	<5% 下滑	(促銷週毛利 - 基準週毛利) / 基準週毛利

9.2 第二週期(Cycle 2)規劃

NoteCycle 2 冷凍明星 (Week 3-4)

Cycle 2 SKU組合 (已規劃):

• KVI: 51130 小肉豆1K (17.7%滲透)
• Vol Builder: 53112 香香雞米花1K, 22138 蜂蜜糖漿3K
• Margin Def: 43599 烏龍麵200g, 22020 咖啡紅茶5包

輪替策略: - 熱狗與小肉豆分開週期（避免自我競食） - 冷凍品為主題，建立品類認知 - 咖啡紅茶持續出現帶動飲品類

學習重點: 1. 哪些品項試用率最高? 2. 客戶是否組合購買? 3. 業務團隊客戶反饋? 4. 哪些Priority 1客戶兌換了?哪些沒有?

調整邏輯: - 如果某項無人問津，下個週期替換 - 根據客戶回饋調整折扣深度

10. 風險與應對

10.1 風險矩陣

risks = pl.DataFrame({
    "risk": [
        "侵蝕毛利",
        "既有高消費客戶白拿折扣",
        "櫻桃採購者",
        "競爭對手反擊",
        "庫存壓力"
    ],
    "likelihood": ["Medium", "Medium", "Low", "Low", "Low"],
    "impact": ["High", "Medium", "Medium", "Medium", "Low"],
    "mitigation": [
        "追蹤總毛利%，設kill switch(毛利<-5%暫停)",
        "監控>$3000客戶佔比，Cycle 2提高門檻至$3,500",
        "$3,000門檻本身已過濾，觀察訂單頻率gap",
        "強調獨家產品組合與服務，非純價格競爭",
        "促銷品都是高週轉品，庫存風險低"
    ]
})

(
    GT(risks)
    .tab_header(
        title="風險評估與應對",
        subtitle="促銷執行風險管理"
    )
    .cols_label(
        risk="風險",
        likelihood="可能性",
        impact="影響",
        mitigation="應對措施"
    )
)

風險評估與應對
促銷執行風險管理
風險	可能性	影響	應對措施
侵蝕毛利	Medium	High	追蹤總毛利%，設kill switch(毛利<-5%暫停)
既有高消費客戶白拿折扣	Medium	Medium	監控>$3000客戶佔比，Cycle 2提高門檻至$3,500
櫻桃採購者	Low	Medium	$3,000門檻本身已過濾，觀察訂單頻率gap
競爭對手反擊	Low	Medium	強調獨家產品組合與服務，非純價格競爭
庫存壓力	Low	Low	促銷品都是高週轉品，庫存風險低

11. 執行清單

Important上線前準備 (Week 0)

週五 (促銷前3天)

• Line訊息準備
  • Priority 1個人化訊息(6則)
  • Priority 2群發模板(1則)
  • 測試發送確認無誤
• 門市準備
  • A4海報設計+列印
  • 店員培訓(如何解釋門檻機制)
  • 收銀系統測試($3,000觸發折扣)
• 庫存確認
  • 5 SKU庫存水位檢查
  • 預估2週需求量
  • 備貨到門市01

週一 (促銷Day 1)

• 上午9:00: 發送Priority 1 Line訊息
• 上午10:00: 發送Priority 2群發
• 全天: 門市海報張貼
• 下午5:00: 檢查首日兌換數

Week 1-2 (執行期)

• 每日: 記錄符合促銷訂單數
• 每3天: 更新各促銷品buying_customers
• 週五: 檢視Week 1數據，決定是否調整
• 週日: 準備Week 2訊息(提醒最後一週)

Week 2結束 (檢討)

• 計算所有成功指標
• 業務團隊訪談(客戶反應如何?)
• 數據分析(哪些客群反應最好?)
• 決定Cycle 2品項

12. 後續週期規劃（Cycles 2-6）

Important12週滾動式促銷策略

設計原則: 1. 保留贏家 - 每個週期保留2個表現好的品項 2. 漸進式探索 - 每週期測試2-3個新品項 3. 類別平衡 - 維持冷凍:乾貨 = 3:2 比例 4. 定價實驗 - 逐步測試不同折扣深度(6-16%) 5. 數據驅動調整 - 根據前一週期表現即時調整

目標：12週後建立完整產品組合地圖，知道哪些品項最能帶動屏東市業績。

12.1 動態週期生成 (Cycles 2-6)

def generate_all_cycles(product_metrics: pl.DataFrame, rules: dict, num_cycles: int = 6) -> dict:
    """Generate all cycles dynamically with KVI rotation.

    Uses EXPERT_CYCLE_OVERRIDES when available for strategic cycle selection,
    otherwise falls back to algorithmic selection.
    """

    cycles = {}
    used_kvis = []
    used_items = set()

    # Get KVI candidates pool (for algorithmic fallback)
    kvi_pool = get_kvi_candidates(
        product_metrics,
        min_penetration=rules["kvi_min_penetration"],
        min_margin_after_discount=rules["kvi_min_margin_after_discount"],
        discount_pct=rules["discount_pct"]
    )

    for cycle_num in range(1, num_cycles + 1):
        # Check for expert override first
        if EXPERT_CYCLE_OVERRIDES is not None and cycle_num in EXPERT_CYCLE_OVERRIDES:
            override = EXPERT_CYCLE_OVERRIDES[cycle_num]
            theme = override.get("theme", f"Cycle {cycle_num}")

            # New structure: per-SKU discounts with roles
            sku_configs = override.get("skus", [])
            if sku_configs:
                # Build list of all expert item codes
                expert_items = [s["code"] for s in sku_configs]

                # Process each SKU with its own discount
                processed_rows = []
                kvi_code = None

                for sku_config in sku_configs:
                    code = sku_config["code"]
                    role = sku_config["role"]
                    discount = sku_config["discount"]
                    target_margin = sku_config["target_margin"]

                    # Get base metrics for this item
                    item_row = product_metrics.filter(pl.col("item_code") == code)
                    if len(item_row) == 0:
                        print(f"⚠️ Item {code} not found in product metrics")
                        continue

                    # Apply per-SKU discount
                    item_row = item_row.with_columns([
                        (((pl.col("avg_price") * (1 - discount/100)) - pl.col("avg_cost"))
                         / (pl.col("avg_price") * (1 - discount/100)) * 100)
                        .alias("margin_after_discount"),
                        (pl.col("avg_price") * (1 - discount/100)).alias("promo_price"),
                        pl.lit(discount).alias("discount_pct"),
                        pl.lit(role).alias("role"),
                        pl.lit(target_margin).alias("target_margin")
                    ])

                    # Assign slot label based on role
                    if role == "kvi":
                        slot_label = "KVI"
                        kvi_code = code
                    elif role == "volume_builder":
                        slot_label = "Volume Builder"
                    else:  # margin_defender
                        slot_label = "Margin Defender"

                    item_row = item_row.with_columns(pl.lit(slot_label).alias("slot"))
                    processed_rows.append(item_row)

                if processed_rows:
                    cycle_skus = pl.concat(processed_rows)
                    cycles[cycle_num] = {
                        "skus": cycle_skus,
                        "theme": theme,
                        "source": "expert_override"
                    }
                    if kvi_code:
                        used_kvis.append(kvi_code)
                    print(f"🎯 Cycle {cycle_num}: {theme} (expert override - role-based margins)")
            else:
                # Legacy format support (kvi + crosssell)
                discount = override.get("kvi_discount", rules["discount_pct"])
                expert_items = [override["kvi"]] + override.get("crosssell", [])

                cycle_skus = (
                    product_metrics
                    .filter(pl.col("item_code").is_in(expert_items))
                    .with_columns([
                        (((pl.col("avg_price") * (1 - discount/100)) - pl.col("avg_cost"))
                         / (pl.col("avg_price") * (1 - discount/100)) * 100)
                        .alias("margin_after_discount"),
                        (pl.col("avg_price") * (1 - discount/100)).alias("promo_price"),
                        pl.lit(discount).alias("discount_pct")
                    ])
                )

                kvi_row = cycle_skus.filter(pl.col("item_code") == override["kvi"]).with_columns(
                    pl.lit("KVI").alias("slot")
                )

                ordered_crosssell = []
                crosssell_rows = cycle_skus.filter(pl.col("item_code") != override["kvi"])
                for i, code in enumerate(override.get("crosssell", []), 1):
                    row = crosssell_rows.filter(pl.col("item_code") == code)
                    if len(row) > 0:
                        ordered_crosssell.append(row.with_columns(pl.lit(f"Cross-sell #{i}").alias("slot")))

                if ordered_crosssell:
                    cycle_skus = pl.concat([kvi_row] + ordered_crosssell)
                else:
                    cycle_skus = kvi_row

                if len(cycle_skus) > 0:
                    cycles[cycle_num] = {
                        "skus": cycle_skus,
                        "theme": theme,
                        "source": "expert_override"
                    }
                    used_kvis.append(override["kvi"])
                    print(f"🎯 Cycle {cycle_num}: {theme} (expert override - legacy format)")

        else:
            # Algorithmic fallback
            # For KVI rotation: exclude previous cycle's KVI
            excluded_kvi = [used_kvis[-1]] if used_kvis else []

            # Select KVI (rotate through top candidates)
            available_kvi = kvi_pool.filter(~pl.col("item_code").is_in(excluded_kvi))
            if len(available_kvi) == 0:
                available_kvi = kvi_pool  # Reset if exhausted

            kvi = available_kvi.head(1)
            if len(kvi) == 0:
                print(f"⚠️ Cycle {cycle_num}: No KVI available")
                continue

            kvi_code = kvi["item_code"][0]
            used_kvis.append(kvi_code)

            # Generate full cycle
            cycle_skus = select_cycle_skus(product_metrics, rules, excluded_items=list(used_items))

            if len(cycle_skus) > 0:
                cycles[cycle_num] = {
                    "skus": cycle_skus,
                    "theme": f"Cycle {cycle_num}",
                    "source": "algorithmic"
                }
                print(f"📊 Cycle {cycle_num}: Algorithmic selection")

    return cycles

# Generate all 6 cycles
ALL_CYCLES = generate_all_cycles(PRODUCT_METRICS, CYCLE_RULES, num_cycles=6)

# Display summary
print(f"✅ 成功生成 {len(ALL_CYCLES)} 個促銷週期")
for cycle_num, cycle_data in ALL_CYCLES.items():
    kvi_row = cycle_data["skus"].filter(pl.col("slot") == "KVI")
    if len(kvi_row) > 0:
        kvi_info = kvi_row.row(0, named=True)
        print(f"   Cycle {cycle_num}: KVI = {kvi_info['item_code']} {kvi_info['product_name'][:15]}... ({kvi_info['penetration_pct']:.1f}%滲透)")

🎯 Cycle 1: 經典高滲透 (Classic High Penetration) (expert override - role-based margins)
🎯 Cycle 2: 冷凍明星 (Frozen Stars) (expert override - role-based margins)
🎯 Cycle 3: 早餐煎台 (Breakfast Griddle) (expert override - role-based margins)
🎯 Cycle 4: 起司與冷藏 (Cheese & Refrigerated) (expert override - role-based margins)
🎯 Cycle 5: 烘焙與調味 (Bakery & Seasoning) (expert override - role-based margins)
🎯 Cycle 6: 雞肉專區 (Chicken Zone) (expert override - role-based margins)
✅ 成功生成 6 個促銷週期
   Cycle 1: KVI = 51171 安美熱狗50條.... (12.6%滲透)
   Cycle 2: KVI = 51130 香雞城小肉豆1K.... (18.9%滲透)
   Cycle 3: KVI = 54021 奇津阿在伯手工蔥抓餅10入.... (13.8%滲透)
   Cycle 4: KVI = 51296 可頌燒餅(原味) 10片... (9.1%滲透)
   Cycle 5: KVI = 22107 飛燕煉乳 375g.... (10.1%滲透)
   Cycle 6: KVI = 53112 香香雞米花1K.... (6.6%滲透)

12.2 週期總覽表

if len(ALL_CYCLES) > 0:
    summary_rows = []
    for cycle_num, cycle_data in ALL_CYCLES.items():
        skus = cycle_data["skus"]
        kvi_row = skus.filter(pl.col("slot") == "KVI")
        theme = cycle_data.get("theme", f"Cycle {cycle_num}")
        source = cycle_data.get("source", "unknown")

        if len(kvi_row) > 0:
            kvi = kvi_row.row(0, named=True)
            summary_rows.append({
                "cycle": f"Cycle {cycle_num}",
                "theme": theme,
                "weeks": f"{(cycle_num-1)*2+1}-{cycle_num*2}",
                "kvi_item": f"{kvi['item_code']} {kvi['product_name'][:10]}...",
                "kvi_penetration": kvi["penetration_pct"],
                "avg_margin_after": skus["margin_after_discount"].mean() if "margin_after_discount" in skus.columns else None,
                "frozen_count": len(skus.filter(pl.col("product_category_1") == "5")),
                "total_items": len(skus),
                "source": "🎯" if source == "expert_override" else "📊"
            })

    cycle_summary = pl.DataFrame(summary_rows)

    (
        GT(cycle_summary)
        .tab_header(
            title="12週促銷週期總覽 (專家策略)",
            subtitle="🎯 = 專家推薦 | 📊 = 演算法選擇"
        )
        .cols_label(
            theme="主題",
            kvi_penetration="KVI 滲透率%",
            avg_margin_after="平均促銷毛利%",
            frozen_count="冷凍品數",
            source="來源"
        )
        .fmt_number(["kvi_penetration", "avg_margin_after"], decimals=1)
    )
else:
    print("❌ 無法生成週期表")

12.3 各週期完整 SKU 明細

# Display detailed SKU table for each cycle
for cycle_num, cycle_data in ALL_CYCLES.items():
    skus = cycle_data["skus"]
    theme = cycle_data.get("theme", f"Cycle {cycle_num}")
    source = cycle_data.get("source", "unknown")

    # Calculate weeks
    week_start = (cycle_num - 1) * 2 + 1
    week_end = cycle_num * 2

    # Prepare display dataframe
    if len(skus) > 0:
        # Check if current pricing columns exist, add them if missing
        available_cols = skus.columns
        select_cols = [
            "slot", "item_code", "product_name", "product_category_1",
            "penetration_pct", "margin_pct", "promo_price", "margin_after_discount", "discount_pct"
        ]
        # Add role columns if available (new per-SKU discount structure)
        for col in ["role", "target_margin"]:
            if col in available_cols:
                select_cols.append(col)
        # Add current pricing columns if available
        for col in ["price_standard", "latest_cost", "price_1"]:
            if col in available_cols:
                select_cols.append(col)

        has_role_columns = "role" in available_cols and "target_margin" in available_cols

        display_df = (
            skus
            .select(select_cols)
            .with_columns([
                pl.col("product_category_1").replace(CAT_NAMES).alias("category"),
                # Use per-SKU discount from discount_pct column
                pl.when(pl.col("discount_pct").is_not_null())
                  .then(pl.col("price_standard") * (1 - pl.col("discount_pct")/100))
                  .otherwise(pl.col("price_standard") * 0.9)
                  .alias("actual_promo_price"),
                # Calculate current margin based on price_standard and latest_cost
                pl.when(pl.col("price_standard").is_not_null() & (pl.col("price_standard") > 0))
                  .then(((pl.col("price_standard") - pl.col("latest_cost")) / pl.col("price_standard") * 100))
                  .otherwise(None)
                  .alias("current_margin_pct"),
                # Calculate margin after per-SKU discount based on current pricing
                pl.when(pl.col("price_standard").is_not_null() & (pl.col("price_standard") > 0) & pl.col("discount_pct").is_not_null())
                  .then((((pl.col("price_standard") * (1 - pl.col("discount_pct")/100)) - pl.col("latest_cost"))
                         / (pl.col("price_standard") * (1 - pl.col("discount_pct")/100)) * 100))
                  .otherwise(None)
                  .alias("actual_margin_after_disc")
            ])
        )

        source_emoji = "🎯 專家推薦" if source == "expert_override" else "📊 演算法"

        print(f"\n{'='*60}")
        print(f"📦 Cycle {cycle_num}: {theme}")
        print(f"   週次: Week {week_start}-{week_end} | 來源: {source_emoji}")
        if has_role_columns:
            print(f"   策略: 角色導向毛利 (KVI 5% / Volume Builder 12% / Margin Defender 20%)")
        print(f"{'='*60}")

        # Display as GT table with role-based margins
        if has_role_columns:
            # New role-based display with per-SKU discounts (includes 箱價)
            display(
                GT(display_df.select([
                    "slot", "item_code", "product_name", "category",
                    "penetration_pct", "discount_pct", "price_standard", "actual_promo_price",
                    "price_1", "latest_cost", "current_margin_pct", "actual_margin_after_disc", "target_margin"
                ]))
                .tab_header(
                    title=f"Cycle {cycle_num}: {theme}",
                    subtitle=f"Week {week_start}-{week_end} | {source_emoji} | 角色導向毛利策略 | MAX折扣15%"
                )
                .cols_label(
                    slot="角色",
                    item_code="品號",
                    product_name="品名",
                    category="類別",
                    penetration_pct="滲透率%",
                    discount_pct="折扣%",
                    price_standard="標準價",
                    actual_promo_price="促銷價",
                    price_1="箱價",
                    latest_cost="成本",
                    current_margin_pct="原毛利%",
                    actual_margin_after_disc="折後毛利%",
                    target_margin="目標毛利%"
                )
                .fmt_number("penetration_pct", decimals=1)
                .fmt_number("discount_pct", decimals=1)
                .fmt_currency(["price_standard", "actual_promo_price", "price_1", "latest_cost"], currency="TWD", decimals=0, use_subunits=False)
                .fmt_number(["current_margin_pct", "actual_margin_after_disc", "target_margin"], decimals=1)
                .data_color(
                    columns="actual_margin_after_disc",
                    palette=["#fee5d9", "#fcae91", "#fb6a4a", "#27ae60"],
                    domain=[5, 30]
                )
                .tab_style(
                    style=style.fill(color="#e8f5e9"),
                    locations=loc.body(rows=pl.col("slot") == "KVI")
                )
                .tab_style(
                    style=style.fill(color="#fff3e0"),
                    locations=loc.body(rows=pl.col("slot") == "Volume Builder")
                )
                .tab_style(
                    style=style.fill(color="#e3f2fd"),
                    locations=loc.body(rows=pl.col("slot") == "Margin Defender")
                )
            )
        else:
            # Legacy display (flat discount)
            display(
                GT(display_df.select([
                    "slot", "item_code", "product_name", "category",
                    "penetration_pct", "price_standard", "actual_promo_price", "latest_cost", "price_1",
                    "current_margin_pct", "actual_margin_after_disc"
                ]))
                .tab_header(
                    title=f"Cycle {cycle_num}: {theme}",
                    subtitle=f"Week {week_start}-{week_end} | {source_emoji}"
                )
                .cols_label(
                    slot="角色",
                    item_code="品號",
                    product_name="品名",
                    category="類別",
                    penetration_pct="滲透率%",
                    price_standard="標準價",
                    actual_promo_price="促銷價",
                    latest_cost="成本",
                    price_1="箱價",
                    current_margin_pct="毛利%",
                    actual_margin_after_disc="折後毛利%"
                )
                .fmt_number("penetration_pct", decimals=1)
                .fmt_currency(["price_standard", "actual_promo_price", "latest_cost", "price_1"], currency="TWD", decimals=0, use_subunits=False)
                .fmt_number(["current_margin_pct", "actual_margin_after_disc"], decimals=1)
                .data_color(
                    columns="actual_margin_after_disc",
                    palette=["#fee5d9", "#fcae91", "#fb6a4a", "#27ae60"],
                    domain=[5, 30]
                )
                .tab_style(
                    style=style.fill(color="#e8f5e9"),
                    locations=loc.body(rows=pl.col("slot") == "KVI")
                )
            )

============================================================
📦 Cycle 1: 經典高滲透 (Classic High Penetration)
   週次: Week 1-2 | 來源: 🎯 專家推薦
   策略: 角色導向毛利 (KVI 5% / Volume Builder 12% / Margin Defender 20%)
============================================================

Cycle 1: 經典高滲透 (Classic High Penetration)
Week 1-2 | 🎯 專家推薦 | 角色導向毛利策略 | MAX折扣15%
角色	品號	品名	類別	滲透率%	折扣%	標準價	促銷價	箱價	成本	原毛利%	折後毛利%	目標毛利%
KVI	51171	安美熱狗50條.	5-冷凍類 (Frozen)	12.6	4.8	NT$110	NT$105	NT$108	NT$100	9.5	5.0	5.0
Volume Builder	51284	馬鈴薯條(HISUN) 2K.	5-冷凍類 (Frozen)	6.9	10.5	NT$165	NT$148	NT$160	NT$117	29.3	21.0	12.0
Volume Builder	21260	香蒜抹醬(上焱) 800g	2-成品 (Packaged/Dry)	6.3	10.0	NT$240	NT$216	NT$235	NT$190	20.8	12.0	12.0
Margin Defender	25888	伊植麥玉米粒(易開)340g.	2-成品 (Packaged/Dry)	28.0	15.0	NT$26	NT$22	NT$19	NT$12	54.1	46.0	20.0
Margin Defender	44051	蘑菇醬包140g.	4-冷藏類 (Refrigerated)	5.3	15.0	NT$12	NT$10	NT$12	NT$7	45.8	36.2	20.0

============================================================
📦 Cycle 2: 冷凍明星 (Frozen Stars)
   週次: Week 3-4 | 來源: 🎯 專家推薦
   策略: 角色導向毛利 (KVI 5% / Volume Builder 12% / Margin Defender 20%)
============================================================

Cycle 2: 冷凍明星 (Frozen Stars)
Week 3-4 | 🎯 專家推薦 | 角色導向毛利策略 | MAX折扣15%
角色	品號	品名	類別	滲透率%	折扣%	標準價	促銷價	箱價	成本	原毛利%	折後毛利%	目標毛利%
KVI	51130	香雞城小肉豆1K.	5-冷凍類 (Frozen)	18.9	15.0	NT$190	NT$162	NT$185	NT$143	24.8	11.5	5.0
Volume Builder	53112	香香雞米花1K.	5-冷凍類 (Frozen)	6.6	7.8	NT$180	NT$166	NT$175	NT$146	18.9	12.0	12.0
Volume Builder	22138	蜂蜜『調味』糖漿 3K	2-成品 (Packaged/Dry)	6.6	14.9	NT$167	NT$142	NT$162	NT$125	25.1	12.0	12.0
Margin Defender	43599	一番烏龍麵 200g.	4-冷藏類 (Refrigerated)	7.2	15.0	NT$13	NT$11	NT$12	NT$9	32.7	20.8	20.0
Margin Defender	22020	特調咖啡紅茶(免濾)100g*5包.	2-成品 (Packaged/Dry)	6.3	12.9	NT$87	NT$76	NT$82	NT$61	30.3	20.0	20.0

============================================================
📦 Cycle 3: 早餐煎台 (Breakfast Griddle)
   週次: Week 5-6 | 來源: 🎯 專家推薦
   策略: 角色導向毛利 (KVI 5% / Volume Builder 12% / Margin Defender 20%)
============================================================

Cycle 3: 早餐煎台 (Breakfast Griddle)
Week 5-6 | 🎯 專家推薦 | 角色導向毛利策略 | MAX折扣15%
角色	品號	品名	類別	滲透率%	折扣%	標準價	促銷價	箱價	成本	原毛利%	折後毛利%	目標毛利%
KVI	54021	奇津阿在伯手工蔥抓餅10入.	5-冷凍類 (Frozen)	13.8	10.8	NT$85	NT$76	NT$79	NT$72	15.3	5.0	5.0
Volume Builder	41018	香Q蛋餅皮(原)30入.(紅字)	5-冷凍類 (Frozen)	7.2	15.0	NT$78	NT$66	NT$72	NT$46	41.0	30.6	12.0
Volume Builder	11055	原味奶酥700g	1-生產製造 (Internal)	8.2	6.6	NT$180	NT$168	NT$175	NT$148	17.8	12.0	12.0
Margin Defender	44051	蘑菇醬包140g.	4-冷藏類 (Refrigerated)	5.3	15.0	NT$12	NT$10	NT$12	NT$7	45.8	36.2	20.0
Margin Defender	22287	高級精鹽1k	2-成品 (Packaged/Dry)	7.2	15.0	NT$21	NT$18	NT$20	NT$14	33.5	21.8	20.0

============================================================
📦 Cycle 4: 起司與冷藏 (Cheese & Refrigerated)
   週次: Week 7-8 | 來源: 🎯 專家推薦
   策略: 角色導向毛利 (KVI 5% / Volume Builder 12% / Margin Defender 20%)
============================================================

Cycle 4: 起司與冷藏 (Cheese & Refrigerated)
Week 7-8 | 🎯 專家推薦 | 角色導向毛利策略 | MAX折扣15%
角色	品號	品名	類別	滲透率%	折扣%	標準價	促銷價	箱價	成本	原毛利%	折後毛利%	目標毛利%
KVI	51296	可頌燒餅(原味) 10片	5-冷凍類 (Frozen)	9.1	15.0	NT$95	NT$81	NT$93	NT$68	28.4	15.8	5.0
Volume Builder	41189	HISUN起司片84片.	4-冷藏類 (Refrigerated)	9.1	5.4	NT$310	NT$293	NT$305	NT$258	16.8	12.0	12.0
Volume Builder	51284	馬鈴薯條(HISUN) 2K.	5-冷凍類 (Frozen)	6.9	10.5	NT$165	NT$148	NT$160	NT$117	29.3	21.0	12.0
Margin Defender	43599	一番烏龍麵 200g.	4-冷藏類 (Refrigerated)	7.2	15.0	NT$13	NT$11	NT$12	NT$9	32.7	20.8	20.0
Margin Defender	22138	蜂蜜『調味』糖漿 3K	2-成品 (Packaged/Dry)	6.6	6.4	NT$167	NT$156	NT$162	NT$125	25.1	20.0	20.0

============================================================
📦 Cycle 5: 烘焙與調味 (Bakery & Seasoning)
   週次: Week 9-10 | 來源: 🎯 專家推薦
   策略: 角色導向毛利 (KVI 5% / Volume Builder 12% / Margin Defender 20%)
============================================================

Cycle 5: 烘焙與調味 (Bakery & Seasoning)
Week 9-10 | 🎯 專家推薦 | 角色導向毛利策略 | MAX折扣15%
角色	品號	品名	類別	滲透率%	折扣%	標準價	促銷價	箱價	成本	原毛利%	折後毛利%	目標毛利%
KVI	22107	飛燕煉乳 375g.	2-成品 (Packaged/Dry)	10.1	5.9	NT$48	NT$45	NT$46	NT$43	10.6	5.0	5.0
Volume Builder	11055	原味奶酥700g	1-生產製造 (Internal)	8.2	6.6	NT$180	NT$168	NT$175	NT$148	17.8	12.0	12.0
Volume Builder	51172	安美燻腸 1000g.	5-冷凍類 (Frozen)	13.8	3.2	NT$185	NT$179	NT$182	NT$167	9.9	6.9	12.0
Margin Defender	41018	香Q蛋餅皮(原)30入.(紅字)	5-冷凍類 (Frozen)	7.2	15.0	NT$78	NT$66	NT$72	NT$46	41.0	30.6	20.0
Margin Defender	44052	黑胡椒醬包140g.	4-冷藏類 (Refrigerated)	4.4	15.0	NT$12	NT$10	NT$12	NT$7	45.8	36.2	20.0

============================================================
📦 Cycle 6: 雞肉專區 (Chicken Zone)
   週次: Week 11-12 | 來源: 🎯 專家推薦
   策略: 角色導向毛利 (KVI 5% / Volume Builder 12% / Margin Defender 20%)
============================================================

Cycle 6: 雞肉專區 (Chicken Zone)
Week 11-12 | 🎯 專家推薦 | 角色導向毛利策略 | MAX折扣15%
角色	品號	品名	類別	滲透率%	折扣%	標準價	促銷價	箱價	成本	原毛利%	折後毛利%	目標毛利%
KVI	53112	香香雞米花1K.	5-冷凍類 (Frozen)	6.6	14.6	NT$180	NT$154	NT$175	NT$146	18.9	5.0	5.0
Volume Builder	23627	一番香Q雞絲50入	2-成品 (Packaged/Dry)	6.3	12.3	NT$350	NT$307	NT$0	NT$270	22.9	12.0	12.0
Volume Builder	51144	香讚黑椒雞堡50片	5-冷凍類 (Frozen)	6.6	1.6	NT$220	NT$216	NT$215	NT$190	13.4	12.0	12.0
Margin Defender	21260	香蒜抹醬(上焱) 800g	2-成品 (Packaged/Dry)	6.3	1.0	NT$240	NT$238	NT$235	NT$190	20.8	20.0	20.0
Margin Defender	44051	蘑菇醬包140g.	4-冷藏類 (Refrigerated)	5.3	15.0	NT$12	NT$10	NT$12	NT$7	45.8	36.2	20.0

Note角色導向毛利策略說明

所有 6 個週期使用專家推薦選品 + 角色導向折扣

角色定位與目標毛利: | 角色 | 目標毛利 | 策略說明 | |——|———-|———-| | KVI (King) | ~5% | 高滲透率品項，低毛利商品只需小折扣 | | Volume Builder | 10-15% | 中等毛利，帶動客單價 | | Margin Defender | 20%+ | 高毛利商品可承受大折扣 |

主題式組合策略 (減少自我競食): | 週期 | 主題 | KVI 品項 | KVI 折扣 | |——|——|———-|———-| | 1 | 經典高滲透 | 安美熱狗50條 | 4.8% | | 2 | 冷凍明星 | 小肉豆1K | 20.9% | | 3 | 早餐煎台 | 蔥抓餅10入 | 10.8% | | 4 | 起司與冷藏 | 可頌燒餅10片 | 24.7% | | 5 | 烘焙與調味 | 飛燕煉乳375g | 5.9% | | 6 | 雞肉專區 | 香香雞米花1K | 14.6% |

關鍵策略: - 低毛利商品 → KVI 角色 (小折扣即達 5% 目標) - 高毛利商品 → Margin Defender (可承受 30-40% 折扣) - 每週期最多 2 個相同品類，避免自我競食

所有數字即時計算，基於當前資料庫數據

---

Important動態調整原則

每個週期結束後必須評估:

評估指標(必須達標才繼續)

1. 兌換率 ≥10% - 如果<10%，門檻太高或產品不吸引
2. 毛利影響 ≤8% - 如果>8%損失，折扣太深
3. Cross-sell成功 - 至少1個cross-sell品項滲透率+5%

調整機制

• 資料驅動：每週期結束48小時內檢視數據
• 快速迭代：不等12週結束，Week 2就可調整Week 3
• 停損機制：如果連續2個週期毛利損失>8%，暫停促銷

最終目標：12週後識別5-8個明星SKU，納入2026年常態促銷組合。

附錄A: SQL查詢參考

A.1 即時追蹤促銷兌換

-- 追蹤符合$3,000門檻的訂單
WITH promo_period_orders AS (
    SELECT
        so.customer_code,
        so.order_number,
        SUM(sol.pretax_subtotal) as order_total,
        COUNT(DISTINCT CASE
            WHEN sol.item_code IN ('51171', '51284', '21260', '25888', '44051')  -- Cycle 1 SKUs
            THEN sol.item_code
        END) as promo_items_count
    FROM cosmos_sync.sales_orders so
    JOIN cosmos_sync.sales_order_lines sol
        ON so.order_type = sol.order_type
        AND so.order_number = sol.order_number
    WHERE so.delivery_route IN ('A-PT', 'PT')
    AND so.order_date BETWEEN '20251201' AND '20251214'  -- Adjust dates
    GROUP BY so.customer_code, so.order_number
)
SELECT
    COUNT(*) as total_orders,
    COUNT(CASE WHEN order_total >= 3000 THEN 1 END) as qualifying_orders,
    COUNT(CASE WHEN order_total >= 3000 AND promo_items_count > 0 THEN 1 END) as promo_redemptions,
    ROUND(100.0 * COUNT(CASE WHEN order_total >= 3000 AND promo_items_count > 0 THEN 1 END) / COUNT(*), 1) as redemption_rate
FROM promo_period_orders;

A.2 追蹤促銷品新客戶

-- 追蹤任意促銷品的新客戶 (以安美熱狗 #51171 為例)
WITH baseline_buyers AS (
    SELECT DISTINCT so.customer_code
    FROM cosmos_sync.sales_orders so
    JOIN cosmos_sync.sales_order_lines sol
        ON so.order_type = sol.order_type AND so.order_number = sol.order_number
    WHERE sol.item_code = '51171'  -- 替換為要追蹤的品項
    AND so.order_date < '20251201'
),
promo_buyers AS (
    SELECT DISTINCT so.customer_code
    FROM cosmos_sync.sales_orders so
    JOIN cosmos_sync.sales_order_lines sol
        ON so.order_type = sol.order_type AND so.order_number = sol.order_number
    WHERE sol.item_code = '51171'  -- 替換為要追蹤的品項
    AND so.order_date BETWEEN '20251201' AND '20251214'
)
SELECT
    (SELECT COUNT(*) FROM baseline_buyers) as before_promo,
    (SELECT COUNT(*) FROM promo_buyers) as during_promo,
    (SELECT COUNT(*) FROM promo_buyers WHERE customer_code NOT IN (SELECT customer_code FROM baseline_buyers)) as new_customers;

附錄B: Line訊息模板

Note訊息範本需依據Cycle 1動態結果更新

以下範本中的產品與價格應使用上方Cycle 1建議表格的實際數據。 請在執行促銷前更新具體品項、原價與促銷價。

B.1 Priority 1個人化訊息

嗨 {customer_name}！您好

看到您最近都跟我們訂購{current_categories}，
想推薦幾個其他客戶反應很好的產品給您：

📍 本月限定：單筆訂購滿 $3,000，這5樣都有優惠：
   • {KVI品項} ${促銷價} (省${折扣金額})
   • {Cross-sell 1} ${促銷價} (省${折扣金額})
   • {Cross-sell 2} ${促銷價} (省${折扣金額})
   • {Cross-sell 3} ${促銷價} (省${折扣金額})
   • {Cross-sell 4} ${促銷價} (省${折扣金額})

想試試看嗎？回覆「要」了解更多！

B.2 Priority 2群發訊息

【屏東市限定優惠】單筆滿$3000，5樣產品特價！

精選暢銷品：
- {KVI品項} ${促銷價} (省${折扣金額})
- {Cross-sell 1} ${促銷價} (省${折扣金額})
- {Cross-sell 2} ${促銷價} (省${折扣金額})
- {Cross-sell 3} ${促銷價} (省${折扣金額})
- {Cross-sell 4} ${促銷價} (省${折扣金額})

12/1-12/14限定兩週
回覆「要」了解更多

附錄C: SQL驗證查詢（Data Verification)

Important為何需要此節？

本附錄提供完整可執行SQL查詢，用於： 1. 驗證本分析所有數據 - 任何人都可重新執行驗證 2. 避免Python邏輯錯誤 - 直接對數據庫查詢，無中間層 3. 建立數據治理標準 - 未來所有分析都應附SQL驗證

使用方式: 複製SQL到任何PostgreSQL客戶端執行（或使用MCP postgres工具）

C.1 驗證客戶基數

-- 驗證屏東市客戶總數 (Jan-Nov 2025)
-- 預期結果: 318 customers
SELECT COUNT(DISTINCT so.customer_code) as total_pt_customers
FROM cosmos_sync.sales_orders so
WHERE so.delivery_route IN ('A-PT', 'PT')
  AND so.confirmed_code = 'Y'
  AND so.order_date >= '20250101'
  AND so.order_date <= '20251130';

預期結果: 318（如果數字顯著不同，數據可能有變化需重新分析）

C.2 驗證Cycle 1推薦品項滲透率

Warning注意：品項代碼可能變動

以下查詢使用 Cycle 1 品項代碼（51171, 51284, 21260, 25888, 44051）。 實際驗證時應使用上方 Section 10 動態生成的 Cycle 1 品項代碼。

-- 驗證Cycle 1 五個推薦SKU的滲透率
-- 注意: 以下品項代碼為示範，請依據動態選品結果更新

WITH pt_customers AS (
  SELECT COUNT(DISTINCT so.customer_code) as total_customers
  FROM cosmos_sync.sales_orders so
  WHERE so.delivery_route IN ('A-PT', 'PT')
    AND so.confirmed_code = 'Y'
    AND so.order_date >= '20250101'
    AND so.order_date <= '20251130'
)
SELECT
  sol.item_code,
  pi.product_name,
  COUNT(DISTINCT so.customer_code) as buying_customers,
  (SELECT total_customers FROM pt_customers) as total_customers,
  ROUND(100.0 * COUNT(DISTINCT so.customer_code) / (SELECT total_customers FROM pt_customers), 1) as penetration_pct,
  SUM(sol.pretax_subtotal) as total_revenue_ytd,
  ROUND(SUM(sol.pretax_subtotal) / SUM(sol.quantity), 1) as avg_price,
  MAX(pi.latest_cost) as latest_cost
FROM cosmos_sync.sales_orders so
JOIN cosmos_sync.sales_order_lines sol
  ON so.order_type = sol.order_type
  AND so.order_number = sol.order_number
LEFT JOIN cosmos_sync.product_information pi
  ON sol.item_code = pi.product_code
WHERE so.delivery_route IN ('A-PT', 'PT')
  AND so.confirmed_code = 'Y'
  AND so.order_date >= '20250101'
  AND so.order_date <= '20251130'
  AND sol.item_code IN ('51171', '51284', '21260', '25888', '44051')  -- Cycle 1 SKUs
GROUP BY sol.item_code, pi.product_name
ORDER BY penetration_pct DESC;

驗證點 (Cycle 1 SKUs): - ✅ 玉米粒(25888) 滲透率較高 (Margin Defender) - ✅ 安美熱狗(51171) 滲透率約12% (KVI) - ✅ 所有品項avg_price應與報告一致

C.3 驗證公司全配送 vs 屏東差異

-- 此查詢展示為何不能用全公司數據
-- 比較全公司 vs 屏東市的安美熱狗滲透率

WITH company_delivery AS (
  SELECT
    COUNT(DISTINCT so.customer_code) as total_customers,
    COUNT(DISTINCT CASE WHEN sol.item_code = '51171' THEN so.customer_code END) as hotdog_buyers
  FROM cosmos_sync.sales_orders so
  LEFT JOIN cosmos_sync.sales_order_lines sol
    ON so.order_type = sol.order_type AND so.order_number = sol.order_number
  WHERE so.delivery_route NOT IN ('PH', 'PH01')
    AND so.delivery_route IS NOT NULL
    AND so.confirmed_code = 'Y'
    AND so.order_date >= '20250801'
    AND so.order_date <= '20251231'
),
pingtung_only AS (
  SELECT
    COUNT(DISTINCT so.customer_code) as total_customers,
    COUNT(DISTINCT CASE WHEN sol.item_code = '51171' THEN so.customer_code END) as hotdog_buyers
  FROM cosmos_sync.sales_orders so
  LEFT JOIN cosmos_sync.sales_order_lines sol
    ON so.order_type = sol.order_type AND so.order_number = sol.order_number
  WHERE so.delivery_route IN ('A-PT', 'PT')
    AND so.confirmed_code = 'Y'
    AND so.order_date >= '20250801'
    AND so.order_date <= '20251231'
)
SELECT
  'Company-wide Delivery' as scope,
  total_customers,
  hotdog_buyers,
  ROUND(100.0 * hotdog_buyers / total_customers, 1) as penetration_pct
FROM company_delivery
UNION ALL
SELECT
  'Pingtung City Only' as scope,
  total_customers,
  hotdog_buyers,
  ROUND(100.0 * hotdog_buyers / total_customers, 1) as penetration_pct
FROM pingtung_only;

預期結果: - Company-wide: ~1,459 customers, ~190 hotdog buyers, ~13.0% penetration - Pingtung: ~245 customers, ~28 hotdog buyers, ~11.4% penetration

驗證點: ✅ 證明financial-forensics聲稱的85.3%滲透率是錯誤的

C.4 驗證票均分布（門檻選擇）

-- 驗證$3,000門檻位於P70
-- Jul-Nov 2025 屏東市訂單分布

WITH pt_orders AS (
  SELECT
    so.order_type,
    so.order_number,
    SUM(sol.pretax_subtotal) as order_total
  FROM cosmos_sync.sales_orders so
  JOIN cosmos_sync.sales_order_lines sol
    ON so.order_type = sol.order_type
    AND so.order_number = sol.order_number
  WHERE so.delivery_route IN ('A-PT', 'PT')
    AND so.confirmed_code = 'Y'
    AND so.order_date >= '20250701'
    AND so.order_date <= '20251130'
  GROUP BY so.order_type, so.order_number
)
SELECT
  COUNT(*) as total_orders,
  ROUND(AVG(order_total), 0) as avg_ticket,
  ROUND(PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY order_total), 0) as p25,
  ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY order_total), 0) as median,
  ROUND(PERCENTILE_CONT(0.70) WITHIN GROUP (ORDER BY order_total), 0) as p70,
  ROUND(PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY order_total), 0) as p75,
  ROUND(PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY order_total), 0) as p90,
  COUNT(CASE WHEN order_total >= 3000 THEN 1 END) as orders_above_3000,
  ROUND(100.0 * COUNT(CASE WHEN order_total >= 3000 THEN 1 END) / COUNT(*), 1) as pct_above_3000
FROM pt_orders;

驗證點: - ✅ P70應約為$2,928（我們選$3,000） - ✅ 約30%訂單應≥$3,000

C.5 驗證Cycle 2-6候選品項

-- 驗證後續週期推薦品項的滲透率與毛利
-- 此查詢列出Cycles 2-6用到的所有SKU

WITH pt_customers AS (
  SELECT COUNT(DISTINCT customer_code) as total
  FROM cosmos_sync.sales_orders
  WHERE delivery_route IN ('A-PT', 'PT')
    AND confirmed_code = 'Y'
    AND order_date >= '20250101'
    AND order_date <= '20251130'
),
cycle_items AS (
  SELECT item_code FROM (VALUES
    -- Cycle 2
    ('51130'), ('51296'),
    -- Cycle 3
    ('41006'), ('41018'), ('51709'), ('22138'),
    -- Cycle 4
    ('51144'), ('53112'), ('21260'), ('43599'),
    -- Cycle 5
    ('51085'), ('51284'),
    -- Cycle 6
    ('54021'), ('21280')
  ) as t(item_code)
)
SELECT
  sol.item_code,
  pi.product_name,
  pi.product_category_1 as category,
  COUNT(DISTINCT so.customer_code) as buying_customers,
  ROUND(100.0 * COUNT(DISTINCT so.customer_code) / (SELECT total FROM pt_customers), 1) as penetration_pct,
  SUM(sol.pretax_subtotal) as total_revenue,
  ROUND(SUM(sol.pretax_subtotal) / NULLIF(SUM(sol.quantity), 0), 1) as avg_price,
  pi.latest_cost,
  CASE
    WHEN pi.latest_cost > 0 AND pi.latest_cost IS NOT NULL AND SUM(sol.quantity) > 0
    THEN ROUND(100.0 * ((SUM(sol.pretax_subtotal) / SUM(sol.quantity)) - pi.latest_cost) / (SUM(sol.pretax_subtotal) / SUM(sol.quantity)), 1)
    ELSE NULL
  END as current_margin_pct
FROM cosmos_sync.sales_orders so
JOIN cosmos_sync.sales_order_lines sol
  ON so.order_type = sol.order_type AND so.order_number = sol.order_number
JOIN cycle_items ci ON sol.item_code = ci.item_code
LEFT JOIN cosmos_sync.product_information pi ON sol.item_code = pi.product_code
WHERE so.delivery_route IN ('A-PT', 'PT')
  AND so.confirmed_code = 'Y'
  AND so.order_date >= '20250101'
  AND so.order_date <= '20251130'
GROUP BY sol.item_code, pi.product_name, pi.product_category_1, pi.latest_cost
ORDER BY penetration_pct DESC;

驗證點: - ✅ 小肉豆(51130) 應為18.9%滲透 - ✅ 奶精(41006) 應為15.7%滲透 - ✅ 所有品項滲透率應與Section 10對應表格一致

---

附錄D: 動態數據驗證摘要

本節在文件渲染時自動執行驗證查詢，確保所有關鍵指標與資料庫一致。

關鍵指標驗證
驗證時間: 2026-01-08 06:09
指標	實際數值	預期範圍	狀態
屏東市客戶數	318	≈318	✅
票均P70 (Jul-Nov)	$2,926	$2,800-$3,100	✅
最高滲透產品	伊植麥玉米粒(易開)340g. (28.0%)	玉米粒 ≈28%	✅
成本計算方式	時間匹配 (procurement_costs)	NOT latest_cost	✅

Top 30 Highest Penetration Items (Ex-Hengchun)

# Calculate top 30 highest penetration items for delivery customers excluding Hengchun
# Exclude routes: A-PH, PH (Hengchun)
VALID_ROUTES_EX_HENGCHUN = ["A-PT", "A-PN", "A-PS", "A-PK", "K1", "K2", "PT", "PS", "PK"]

# Get total customer count for penetration calculation
total_customers_ex_hengchun = (
    sales_orders
    .filter(_.delivery_route.isin(VALID_ROUTES_EX_HENGCHUN))
    .filter(_.order_date >= ANALYSIS_START)
    .filter(_.order_date <= ANALYSIS_END)
    .select("customer_code")
    .distinct()
    .count()
    .execute()
)

# Get item penetration with pricing info
top_penetration = (
    sales_orders
    .filter(_.delivery_route.isin(VALID_ROUTES_EX_HENGCHUN))
    .filter(_.order_date >= ANALYSIS_START)
    .filter(_.order_date <= ANALYSIS_END)
    .join(sales_order_lines, ["order_type", "order_number"])
    .filter(~_.item_code.isin(EXCLUDED_ITEMS))
    .filter(~_.item_code.startswith(SAMPLE_PREFIX))
    .group_by("item_code")
    .agg(
        buying_customers=_.customer_code.nunique(),
        total_qty=_.quantity.sum(),
        total_revenue=_.pretax_subtotal.cast("float64").sum()
    )
    .mutate(
        penetration_pct=(_.buying_customers / total_customers_ex_hengchun * 100).round(2)
    )
    .join(
        con.table("product_information", database="cosmos_sync")
        .select("product_code", "product_name", "latest_cost", "price_standard", "price_1")
        .rename(item_code="product_code"),
        "item_code"
    )
    .filter(_.latest_cost > 0)
    .filter(_.price_standard > 0)
    .mutate(
        margin_pct=((_.price_standard.cast("float64") - _.latest_cost.cast("float64"))
                    / _.price_standard.cast("float64") * 100).round(2),
        # Price at 5% margin: cost / (1 - 0.05) = cost / 0.95
        price_at_5pct_margin=(_.latest_cost.cast("float64") / 0.95).round(2)
    )
    .order_by(ibis.desc("penetration_pct"))
    .limit(30)
    .to_polars()
)

# Format for display
display_df = top_penetration.select([
    pl.col("item_code").alias("品號"),
    pl.col("product_name").alias("品名"),
    pl.col("penetration_pct").alias("滲透率%"),
    pl.col("buying_customers").alias("購買客數"),
    pl.col("latest_cost").cast(pl.Float64).round(2).alias("最新成本"),
    pl.col("price_standard").cast(pl.Float64).round(2).alias("零售價"),
    pl.col("price_1").cast(pl.Float64).round(2).alias("箱價"),
    pl.col("margin_pct").alias("毛利率%"),
    pl.col("price_at_5pct_margin").alias("5%毛利價"),
])

print(f"分析期間: {ANALYSIS_START} - {ANALYSIS_END}")
print(f"總客戶數 (排除恆春): {total_customers_ex_hengchun:,}")

(
    GT(display_df)
    .tab_header(
        title="Top 30 高滲透率商品 (排除恆春路線)",
        subtitle=f"Delivery customers | Routes: {', '.join(VALID_ROUTES_EX_HENGCHUN)}"
    )
    .fmt_number(["滲透率%", "毛利率%"], decimals=1)
    .fmt_currency(["最新成本", "零售價", "箱價", "5%毛利價"], currency="TWD", decimals=0, use_subunits=False)
    .fmt_integer("購買客數")
    .tab_style(
        style=style.fill(color="#fff3cd"),
        locations=loc.body(columns="5%毛利價")
    )
    .tab_source_note("5%毛利價 = 最新成本 / 0.95")
)

分析期間: 20250101 - 20251130
總客戶數 (排除恆春): 1,600

Top 30 高滲透率商品 (排除恆春路線)
Delivery customers | Routes: A-PT, A-PN, A-PS, A-PK, K1, K2, PT, PS, PK
品號	品名	滲透率%	購買客數	最新成本	零售價	箱價	毛利率%	5%毛利價
25888	伊植麥玉米粒(易開)340g.	28.9	463	NT$12	NT$26	NT$19	54.1	NT$13
21266	梨山花生醬2.8K	17.9	287	NT$420	NT$440	NT$430	4.5	NT$442
23093	福鷹玉米粒340g(易開罐).	17.6	281	NT$18	NT$30	NT$20	38.9	NT$19
53027	紅龍雞塊 1K.	17.6	281	NT$135	NT$150	NT$140	10.0	NT$142
51130	香雞城小肉豆1K.	16.4	263	NT$143	NT$190	NT$185	24.8	NT$150
21280	梨山草莓醬3.2k	15.4	246	NT$250	NT$270	NT$265	7.4	NT$263
54021	奇津阿在伯手工蔥抓餅10入.	14.6	234	NT$72	NT$85	NT$79	15.3	NT$76
51171	安美熱狗50條.	13.7	219	NT$100	NT$110	NT$108	9.6	NT$105
41006	亞柏奶精 1公升.	13.4	215	NT$126	NT$135	NT$132	6.7	NT$133
53102	紅龍卡啦雞腿堡(原味) 10片.	12.9	207	NT$190	NT$195	NT$190	2.6	NT$200
53100	紅龍卡啦雞腿堡(辣味) 10片.	12.0	192	NT$190	NT$195	NT$190	2.6	NT$200
51284	馬鈴薯條(HISUN) 2K.	11.9	191	NT$117	NT$165	NT$160	29.3	NT$123
26068	雙魚座鮪魚185g.	11.8	188	NT$1	NT$35	NT$30	98.1	NT$1
21227	福汎巧克力3K	10.9	175	NT$470	NT$530	NT$520	11.3	NT$495
41189	HISUN起司片84片.	10.6	170	NT$258	NT$310	NT$305	16.8	NT$272
23154	可果美快餐用蕃茄醬 3.15k	10.6	169	NT$170	NT$195	NT$190	12.9	NT$179
57001	富統小熱狗50支	10.3	165	NT$102	NT$119	NT$117	14.3	NT$107
22107	飛燕煉乳 375g.	10.3	165	NT$43	NT$48	NT$46	10.6	NT$45
23361	肉鬆 3K	10.3	165	NT$387	NT$468	NT$463	17.3	NT$407
23098	金熊三明治鮪魚185g.	10.2	164	NT$25	NT$33	NT$28	23.0	NT$27
51172	安美燻腸 1000g.	9.4	150	NT$167	NT$185	NT$182	9.9	NT$175
21229	福汎椰香奶酥 1.8K	9.3	149	NT$420	NT$450	NT$445	6.7	NT$442
53211	日規薯餅20片.	9.3	149	NT$116	NT$145	NT$142	20.0	NT$122
53028	正點煙燻雞肉片1K.	8.9	142	NT$206	NT$230	NT$220	10.4	NT$217
51265	金酥3/8脆薯2.04K.	8.8	140	NT$170	NT$210	NT$200	19.1	NT$179
96166	小磨坊香辣椒鹽粉(1)600g.	8.8	140	NT$86	NT$99	NT$95	12.7	NT$91
22020	特調咖啡紅茶(免濾)100g*5包.	8.7	139	NT$61	NT$87	NT$82	30.3	NT$64
41029	安佳84片乳酪990g(紅).	8.7	139	NT$341	NT$320	NT$315	−6.6	NT$359
57002	富統培根 1K.	8.5	136	NT$225	NT$250	NT$245	10.0	NT$237
31011	168奶精(不含乳(奶)) 1k.	8.4	135	NT$90	NT$125	NT$113	28.0	NT$95
5%毛利價 = 最新成本 / 0.95

Top 50 High Margin Products (Cost >$20, Dec 2025 Sales >100 units)

# Top 50 high margin products with:
# 1. Latest cost > $20
# 2. Sold more than 100 units in December 2025

DEC_2025_START = "20251201"
DEC_2025_END = "20251231"

# Get December 2025 sales volume by item (delivery only, ex-Hengchun)
dec_sales = (
    sales_orders
    .filter(_.delivery_route.isin(VALID_ROUTES_EX_HENGCHUN))
    .filter(_.order_date >= DEC_2025_START)
    .filter(_.order_date <= DEC_2025_END)
    .join(sales_order_lines, ["order_type", "order_number"])
    .filter(~_.item_code.isin(EXCLUDED_ITEMS))
    .filter(~_.item_code.startswith(SAMPLE_PREFIX))
    .group_by("item_code")
    .agg(
        dec_qty=_.quantity.sum(),
        dec_revenue=_.pretax_subtotal.cast("float64").sum(),
        dec_customers=_.customer_code.nunique()
    )
    .filter(_.dec_qty > 100)  # More than 100 units sold
)

# Join with product info and calculate margin
high_margin_products = (
    dec_sales
    .join(
        con.table("product_information", database="cosmos_sync")
        .select("product_code", "product_name", "product_category_1",
                "latest_cost", "price_standard", "price_1")
        .rename(item_code="product_code"),
        "item_code"
    )
    .filter(_.latest_cost > 20)  # Cost > $20
    .filter(_.price_standard > 0)
    .mutate(
        margin_pct=((_.price_standard.cast("float64") - _.latest_cost.cast("float64"))
                    / _.price_standard.cast("float64") * 100).round(2),
        price_at_5pct_margin=(_.latest_cost.cast("float64") / 0.95).round(2)
    )
    .filter(_.margin_pct > 0)  # Only positive margins
    .order_by(ibis.desc("margin_pct"))
    .limit(50)
    .to_polars()
)

# Format for display
display_margin_df = high_margin_products.select([
    pl.col("item_code").alias("品號"),
    pl.col("product_name").alias("品名"),
    pl.col("product_category_1").replace(CAT_NAMES).alias("類別"),
    pl.col("dec_qty").cast(pl.Int64).alias("12月銷量"),
    pl.col("dec_customers").alias("購買客數"),
    pl.col("latest_cost").cast(pl.Float64).round(2).alias("最新成本"),
    pl.col("price_standard").cast(pl.Float64).round(2).alias("零售價"),
    pl.col("price_1").cast(pl.Float64).round(2).alias("箱價"),
    pl.col("margin_pct").alias("毛利率%"),
    pl.col("price_at_5pct_margin").alias("5%毛利價"),
])

print(f"篩選條件: 成本 > NT$20, 12月銷量 > 100 units")
print(f"符合條件商品數: {len(high_margin_products)}")

(
    GT(display_margin_df)
    .tab_header(
        title="Top 50 高毛利商品 (成本>$20, 12月銷量>100)",
        subtitle=f"December 2025 | Delivery ex-Hengchun | Sorted by margin %"
    )
    .fmt_number(["毛利率%"], decimals=1)
    .fmt_currency(["最新成本", "零售價", "箱價", "5%毛利價"], currency="TWD", decimals=0, use_subunits=False)
    .fmt_integer(["12月銷量", "購買客數"])
    .tab_style(
        style=style.fill(color="#d4edda"),
        locations=loc.body(columns="毛利率%")
    )
    .tab_source_note("5%毛利價 = 最新成本 / 0.95")
)

篩選條件: 成本 > NT$20, 12月銷量 > 100 units
符合條件商品數: 50

Top 50 高毛利商品 (成本>$20, 12月銷量>100)
December 2025 | Delivery ex-Hengchun | Sorted by margin %
品號	品名	類別	12月銷量	購買客數	最新成本	零售價	箱價	毛利率%	5%毛利價
25918	伊植麥玉米粒75OZ	2-成品 (Packaged/Dry)	332	32	NT$64	NT$130	NT$105	50.5	NT$68
41007	香Q蛋餅皮(芝麻蔥)20入.(橘字)	5-冷凍類 (Frozen)	250	6	NT$31	NT$56	NT$53	44.6	NT$33
51056	手感蔓越莓亞麻子麵包 220g	5-冷凍類 (Frozen)	950	11	NT$45	NT$79	NT$61	43.0	NT$47
41005	香Q蛋餅皮(蔥花)30入.(藍字)	5-冷凍類 (Frozen)	1,184	62	NT$46	NT$78	NT$72	41.0	NT$48
51047	青蔥蛋餅皮30入	5-冷凍類 (Frozen)	210	7	NT$46	NT$78	NT$72	41.0	NT$48
41018	香Q蛋餅皮(原)30入.(紅字)	5-冷凍類 (Frozen)	913	54	NT$46	NT$78	NT$72	41.0	NT$48
51397	圓型披薩(燻雞)120g.	5-冷凍類 (Frozen)	148	11	NT$22	NT$36	NT$30	40.3	NT$23
51396	圓型披薩(夏威夷)120g.	5-冷凍類 (Frozen)	566	20	NT$22	NT$36	NT$30	40.3	NT$23
51395	圓型披薩(總匯)120g.	5-冷凍類 (Frozen)	195	12	NT$22	NT$36	NT$30	40.3	NT$23
51409	冷凍義大利麵 180g*5入	5-冷凍類 (Frozen)	193	15	NT$55	NT$89	NT$84	38.1	NT$58
52183	唐辛子帶環腿排220g*5片	5-冷凍類 (Frozen)	271	12	NT$228	NT$365	NT$325	37.7	NT$239
53056	冷凍熟鍋貼(禾)1500g(約50粒).	5-冷凍類 (Frozen)	830	45	NT$75	NT$120	NT$115	37.5	NT$79
23011	四海炸雞粉1k	2-成品 (Packaged/Dry)	124	5	NT$90	NT$143	NT$133	37.1	NT$95
22249	涼水舖燒仙草2700ml.	2-成品 (Packaged/Dry)	209	10	NT$79	NT$125	NT$115	37.0	NT$83
54057	黃金手工蔥抓餅10片.	5-冷凍類 (Frozen)	772	35	NT$64	NT$100	NT$95	36.0	NT$67
91118	小蘇打(碳酸氫鈉) 1K	9-調味添加物 (Seasonings)	125	10	NT$42	NT$65	NT$62	35.4	NT$44
51012	HISUN四角薯餅20片1260g	5-冷凍類 (Frozen)	534	68	NT$108	NT$165	NT$160	34.5	NT$114
22339	台糖貳號砂糖 1k	2-成品 (Packaged/Dry)	113	4	NT$36	NT$55	NT$52	34.5	NT$38
56041	抓餅(禾)10片.	5-冷凍類 (Frozen)	1,456	65	NT$65	NT$99	NT$94	34.3	NT$68
56017	翡翠抓餅(禾)10片.	5-冷凍類 (Frozen)	315	54	NT$65	NT$99	NT$94	34.3	NT$68
71891	0號夾鏈袋 10入	7-免洗餐具 (Disposables)	118	37	NT$52	NT$79	NT$0	34.2	NT$55
53042	四海蘿蔔糕 12片(紅)	5-冷凍類 (Frozen)	175	16	NT$43	NT$64	NT$62	33.6	NT$45
54011	奇津熟鍋貼1500g(約50粒)	5-冷凍類 (Frozen)	476	40	NT$74	NT$110	NT$108	33.2	NT$77
22428	田園全脂保久純牛乳 1公升.	2-成品 (Packaged/Dry)	530	13	NT$49	NT$73	NT$68	32.9	NT$52
53048	四海港式蘿蔔糕12片(橘).	5-冷凍類 (Frozen)	149	7	NT$41	NT$61	NT$59	32.9	NT$43
51187	泰式打拋豬肉350公克#	5-冷凍類 (Frozen)	104	1	NT$110	NT$163	NT$158	32.5	NT$116
53138	金品豆漿(無糖)1.5K	5-冷凍類 (Frozen)	312	11	NT$33	NT$48	NT$47	32.2	NT$34
41047	每天沙拉3K	4-冷藏類 (Refrigerated)	380	51	NT$170	NT$250	NT$0	32.0	NT$179
51528	酥脆蛋餅皮25片(藍)#	5-冷凍類 (Frozen)	120	1	NT$75	NT$110	NT$105	31.8	NT$79
23001	四海鹽酥粉600g	2-成品 (Packaged/Dry)	135	49	NT$125	NT$183	NT$173	31.7	NT$132
54044	高麗菜熟水餃(HISUN) 1700g	5-冷凍類 (Frozen)	168	17	NT$82	NT$120	NT$115	31.7	NT$86
22021	特調醇麥紅茶(免濾)100g*5包.	2-成品 (Packaged/Dry)	217	18	NT$57	NT$83	NT$75	31.5	NT$60
53036	QQ棒 1K	5-冷凍類 (Frozen)	111	26	NT$103	NT$150	NT$145	31.3	NT$108
51299	薄脆蔥抓餅90g*10片	5-冷凍類 (Frozen)	374	27	NT$45	NT$65	NT$60	30.8	NT$47
22020	特調咖啡紅茶(免濾)100g*5包.	2-成品 (Packaged/Dry)	1,960	80	NT$61	NT$87	NT$82	30.3	NT$64
53107	禎祥冷凍蘿蔔糕10片(粉)	5-冷凍類 (Frozen)	110	4	NT$46	NT$66	NT$64	30.3	NT$48
54064	強匠嫩煎雞腿排210g	5-冷凍類 (Frozen)	320	11	NT$40	NT$58	NT$53	30.2	NT$43
23538	日尚鮪魚片 180g.	2-成品 (Packaged/Dry)	336	6	NT$27	NT$39	NT$32	30.0	NT$29
51286	地瓜圓N600g	5-冷凍類 (Frozen)	222	13	NT$56	NT$80	NT$75	30.0	NT$59
54056	香酥蛋餅(藍袋)30入	5-冷凍類 (Frozen)	639	21	NT$63	NT$90	NT$85	30.0	NT$66
51285	芋頭圓N600g	5-冷凍類 (Frozen)	177	11	NT$56	NT$80	NT$75	30.0	NT$59
51358	金品薩莫里白醬玉米義大利麵300g	5-冷凍類 (Frozen)	203	10	NT$23	NT$33	NT$32	30.0	NT$24
22093	金獅調製奶水 362g.#	2-成品 (Packaged/Dry)	123	4	NT$36	NT$52	NT$42	29.9	NT$38
51284	馬鈴薯條(HISUN) 2K.	5-冷凍類 (Frozen)	896	79	NT$117	NT$165	NT$160	29.3	NT$123
57064	厚切蘿蔔糕 1K	5-冷凍類 (Frozen)	353	24	NT$45	NT$63	NT$60	28.6	NT$47
54016	開口鍋貼(高麗菜)600g	5-冷凍類 (Frozen)	102	9	NT$45	NT$63	NT$58	28.6	NT$47
59127	主廚漢堡肉 20片	5-冷凍類 (Frozen)	255	36	NT$95	NT$133	NT$128	28.6	NT$100
51296	可頌燒餅(原味) 10片	5-冷凍類 (Frozen)	839	78	NT$68	NT$95	NT$93	28.4	NT$72
51468	英辰丹麥吐司(厚片)400g	5-冷凍類 (Frozen)	132	3	NT$53	NT$74	NT$72	28.4	NT$56
31011	168奶精(不含乳(奶)) 1k.	3-生產分裝 (Repackaged)	1,648	87	NT$90	NT$125	NT$113	28.0	NT$95
5%毛利價 = 最新成本 / 0.95

Note數據一致性保證

本文件中所有數據表格均由即時資料庫查詢生成：

1. 客戶基數: get_customer_count() 函數從 sales_orders 直接計算
2. 滲透率: 採購客戶數 / 總客戶數，無硬編碼
3. 毛利率: 使用 procurement_costs 表格的時間匹配成本（非 latest_cost）
4. 折扣後毛利: ((price * (1-discount%)) - cost) / (price * (1-discount%)) * 100
5. 週期選品: 由 generate_all_cycles() 函數依規則動態生成

如發現數據異常，請先執行附錄C的SQL驗證查詢確認資料庫狀態。

---

分析完成時間: 文件渲染時動態更新 資料來源: cosmos_sync (PostgreSQL via ibis) 分析工具: Python + ibis + polars + great_tables 分析方法: 所有數據由即時查詢生成，無硬編碼