高雄/潮州促銷目標客戶分析

Kaohsiung & Chaozhou Customer Targeting for Promo Campaign

發佈於

2026年1月21日

1. 分析目的

本分析旨在識別高雄/潮州地區適合促銷活動的目標客戶，篩選條件如下：

地理條件 (符合任一):

  • 配送路線為 K1、K2 或 PS

AND 消費條件:

  • 2025年消費總額至少 $1,000

AND 活動條件 (符合任一):

  • 2025/9/15-2026/1/7 期間有超過 2 張訂單金額低於 $3,000
  • 或 2025/10/31-12/31 相較 2025/8/31-10/30 銷售額下降超過 30%
程式碼 
# Date constants (YYYYMMDD format for database)
YEAR_START = "20250101"
YEAR_END = "20251231"
PERIOD_START = "20250915"
PERIOD_END = "20260107"
PERIOD1_START = "20250831"
PERIOD1_END = "20251030"
PERIOD2_START = "20251031"
PERIOD2_END = "20251231"

# Target routes for Kaohsiung/Chaozhou area
TARGET_ROUTES = ["K1", "K2", "PS"]

# Load customers table
customers_raw = con.sql("""
    SELECT
        customer_code,
        trade_name,
        delivery_route,
        shipping_address,
        closure_date,
        csm
    FROM cosmos_sync.customers
""").to_polars()

# Load sales orders with line totals (extended to 2026 for activity filter)
orders_raw = con.sql("""
    SELECT
        so.order_type,
        so.order_number,
        so.customer_code,
        so.delivery_route,
        so.order_date,
        COALESCE(SUM(sol.pretax_subtotal), 0) as order_total
    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.confirmed_code = 'Y'
        AND so.order_date >= '20250101'
        AND so.order_date <= '20260107'
    GROUP BY so.order_type, so.order_number, so.customer_code,
             so.delivery_route, so.order_date
""").to_polars()

print(f"Loaded {len(customers_raw):,} customers and {len(orders_raw):,} orders")
Loaded 10,233 customers and 60,318 orders

2. 客戶篩選

程式碼 
# Step 1: Geographic filter - routes K1, K2, PS
geo_customers = customers_raw.filter(
    pl.col("delivery_route").is_in(TARGET_ROUTES)
).select("customer_code", "trade_name", "delivery_route", "shipping_address", "csm")

geo_customer_codes = geo_customers["customer_code"].to_list()
print(f"Step 1: {len(geo_customers):,} customers in target geography (K1, K2, PS)")

# Step 2: Filter orders to geographic customers
geo_orders = orders_raw.filter(
    pl.col("customer_code").is_in(geo_customer_codes)
)
print(f"Step 2: {len(geo_orders):,} orders from geographic customers")

# Step 3: Calculate 2025 total spend per customer
customer_2025_spend = (
    geo_orders
    .filter(pl.col("order_date") <= "20251231")  # Only 2025 for spend calculation
    .group_by("customer_code")
    .agg(
        pl.col("order_total").sum().alias("total_spend_2025"),
        pl.col("order_total").count().alias("total_orders_2025")
    )
    .filter(pl.col("total_spend_2025") >= 1000)
)
print(f"Step 3: {len(customer_2025_spend):,} customers with >= $1,000 spend in 2025")

# Get list of customers meeting spend threshold
spend_customers = customer_2025_spend["customer_code"].to_list()
Step 1: 2,567 customers in target geography (K1, K2, PS)
Step 2: 22,928 orders from geographic customers
Step 3: 668 customers with >= $1,000 spend in 2025
程式碼 
# Filter orders for the analysis period (9/15/2025 - 1/7/2026)
period_orders = geo_orders.filter(
    pl.col("customer_code").is_in(spend_customers) &
    (pl.col("order_date") >= PERIOD_START) &
    (pl.col("order_date") <= PERIOD_END)
)

# Activity Filter A: More than 2 tickets < $3,000
low_ticket_customers = (
    period_orders
    .filter(pl.col("order_total") < 3000)
    .group_by("customer_code")
    .agg(pl.col("order_total").count().alias("low_ticket_count"))
    .filter(pl.col("low_ticket_count") > 2)
)
low_ticket_codes = set(low_ticket_customers["customer_code"].to_list())
print(f"Activity Filter A: {len(low_ticket_codes):,} customers with >2 tickets < $3,000")

# Activity Filter B: Sales dropped > 30% between periods
# Period 1: 8/31 - 10/30 (2025)
period1_sales = (
    geo_orders
    .filter(
        pl.col("customer_code").is_in(spend_customers) &
        (pl.col("order_date") >= PERIOD1_START) &
        (pl.col("order_date") <= PERIOD1_END)
    )
    .group_by("customer_code")
    .agg(pl.col("order_total").sum().alias("period1_sales"))
)

# Period 2: 10/31 - 12/31 (2025)
period2_sales = (
    geo_orders
    .filter(
        pl.col("customer_code").is_in(spend_customers) &
        (pl.col("order_date") >= PERIOD2_START) &
        (pl.col("order_date") <= PERIOD2_END)
    )
    .group_by("customer_code")
    .agg(pl.col("order_total").sum().alias("period2_sales"))
)

# Join and calculate change
sales_change = (
    period1_sales
    .join(period2_sales, on="customer_code", how="full", coalesce=True)
    .with_columns([
        pl.col("period1_sales").fill_null(0).cast(pl.Float64),
        pl.col("period2_sales").fill_null(0).cast(pl.Float64),
    ])
    .filter(pl.col("period1_sales") > 0)  # Only customers with period1 sales
    .with_columns(
        ((pl.col("period2_sales") - pl.col("period1_sales")) / pl.col("period1_sales") * 100)
        .alias("pct_change")
    )
    .filter(pl.col("pct_change") < -30)  # Dropped more than 30%
)
declining_codes = set(sales_change["customer_code"].to_list())
print(f"Activity Filter B: {len(declining_codes):,} customers with >30% sales decline")

# Combine activity filters (OR logic)
activity_customers = low_ticket_codes | declining_codes
print(f"Combined Activity Filter: {len(activity_customers):,} unique customers")
Activity Filter A: 396 customers with >2 tickets < $3,000
Activity Filter B: 110 customers with >30% sales decline
Combined Activity Filter: 460 unique customers
程式碼 
# Final target customers: geographic AND spend AND activity
target_customer_codes = list(activity_customers)

# Build comprehensive customer data
target_customers = (
    geo_customers
    .filter(pl.col("customer_code").is_in(target_customer_codes))
    .join(customer_2025_spend, on="customer_code", how="left")
    .join(
        low_ticket_customers.select("customer_code", "low_ticket_count"),
        on="customer_code",
        how="left"
    )
    .join(
        sales_change.select("customer_code", "period1_sales", "period2_sales", "pct_change"),
        on="customer_code",
        how="left"
    )
    .with_columns([
        pl.col("low_ticket_count").fill_null(0),
        pl.col("period1_sales").fill_null(0),
        pl.col("period2_sales").fill_null(0),
    ])
    .with_columns(
        pl.when(pl.col("low_ticket_count") > 2)
        .then(pl.lit("低客單"))
        .when(pl.col("pct_change") < -30)
        .then(pl.lit("業績下滑"))
        .otherwise(pl.lit(""))
        .alias("target_reason")
    )
)

# Calculate overlap
both_conditions = len(low_ticket_codes & declining_codes)

print(f"\n=== FINAL RESULTS ===")
print(f"Total target customers: {len(target_customers):,}")
print(f"- Low ticket only: {len(low_ticket_codes) - both_conditions:,}")
print(f"- Declining only: {len(declining_codes) - both_conditions:,}")
print(f"- Both conditions: {both_conditions:,}")

=== FINAL RESULTS ===
Total target customers: 460
- Low ticket only: 350
- Declining only: 64
- Both conditions: 46

3. 執行摘要

目標客戶總覽

指標 數值
總目標客戶數 460
低客單客戶 (>2張<$3K) 396
業績下滑客戶 (>30%↓) 110
兩者皆符合 46
平均年度消費 $121,849
平均訂單數 42.2

篩選漏斗

  1. 地理篩選: 2,567 位高雄/潮州地區客戶 (K1, K2, PS)
  2. 消費篩選: 668 位年消費 ≥ $1,000
  3. 活動篩選: 460 位符合目標條件

目標原因分布

  • 低客單 (小額訂單多): 396 位 (86.1%)
  • 業績下滑 (需挽回): 110 位 (23.9%)

4. 目標客戶明細

程式碼 
# Display top customers by spend
display_df = (
    target_customers
    .sort("total_spend_2025", descending=True)
    .select([
        "customer_code",
        "trade_name",
        "delivery_route",
        "total_spend_2025",
        "total_orders_2025",
        "low_ticket_count",
        "pct_change",
        "target_reason"
    ])
    .head(50)
)

(
    GT(display_df)
    .tab_header(
        title="目標客戶名單 (Top 50)",
        subtitle="依年度消費排序"
    )
    .cols_label(
        customer_code="客戶代碼",
        trade_name="商號名稱",
        delivery_route="路線",
        total_spend_2025="2025消費",
        total_orders_2025="訂單數",
        low_ticket_count="低客單次數",
        pct_change="業績變化%",
        target_reason="目標原因"
    )
    .fmt_number(columns=["total_spend_2025"], decimals=0, use_seps=True)
    .fmt_number(columns=["pct_change"], decimals=1)
    .tab_style(
        style=style.fill(color="#ffebee"),
        locations=loc.body(
            columns="pct_change",
            rows=pl.col("pct_change") < -30
        )
    )
)
目標客戶名單 (Top 50)
依年度消費排序
客戶代碼 商號名稱 路線 2025消費 訂單數 低客單次數 業績變化% 目標原因
5910033 恬芯快速早餐-大寮2.5 K1 1,371,132 114 7 None 低客單
2510038 好野早午餐-佳冬2.5 K2 1,307,206 93 12 None 低客單
4210084 鼎山永和豆漿-1.4 K1 1,054,420 170 20 None 低客單
5910027 小田園直營-大寮中興1.4 K1 1,014,112 102 3 None 低客單
4410018 萊恩快速早餐-二苓2.5 K1 876,112 118 5 None 低客單
5410002 美味田園-梓官1.4 K2 874,855 79 4 None 低客單
2210006 咕咕雞早餐-來義-3 K1 812,628 77 6 None 低客單
5910037 萊恩快速早餐-後庄1.4 K1 770,970 123 6 None 低客單
5310001 美味田園-岡山-1.4 K2 733,524 88 3 None 低客單
5810212 萊恩快速早餐-瑞興1.4 K1 656,899 107 8 None 低客單
5810151 小田園直營-南光-2.5 K1 624,585 106 3 None 低客單
4410040 香醇軒總部-5 K1 615,091 61 4 None 低客單
10760002 航特部-台南歸仁-1 K2 607,306 62 5 None 低客單
5810237 萊恩快速早餐-鳳北1.4 K2 571,629 98 7 None 低客單
4410037 金晨午早餐2.5 K1 540,794 134 14 None 低客單
2610066 枋寮-無尾熊包子2.5 K2 518,178 101 7 None 低客單
6210024 小田園直營店-久堂1.4 K2 500,921 116 6 None 低客單
1710004 美而美-新園2.5 K2 490,204 147 19 None 低客單
1710081 美好早餐坊2.5 K2 480,627 106 5 None 低客單
5810154 小田園直營-田中央2.5 K1 469,109 98 3 None 低客單
5310002 小田園加盟-隆豐-1.4-家 K2 469,007 97 3 None 低客單
4210133 萊歐快速早餐-1.4 K1 468,613 68 4 None 低客單
0290007 宵歸暝 潮州光華直營店 PS 459,632 50 5 None 低客單
5810219 萊恩快速早餐-文衡1.4 K1 459,091 99 9 None 低客單
2510056 蔡英秋-2.5 K2 458,610 135 30 None 低客單
0210304 早點見面 PS 428,304 127 13 None 低客單
0310036 林小姐-五房2.5 K2 423,461 99 9 None 低客單
0510094 食2.9-(原郭先生) PS 421,790 146 22 None 低客單
5910025 小田園直營-鳳林2.5 K1 417,175 103 7 None 低客單
5810141 香醇軒-鳳甲店-2.5 K1 408,410 119 14 None 低客單
4110056 香醇軒-瑞隆店2.5 K1 408,277 94 7 None 低客單
2410004 巨林-新埤2.5 K2 386,362 107 7 None 低客單
10610004 就是堡快速早餐-1 K2 378,334 43 9 None 低客單
4610029 香醇軒-文萊店-1.4 K1 369,265 109 15 None 低客單
1220013 甘慧娟(3) K1 361,025 68 9 None 低客單
2620008 百利冷飲店2.5 K2 360,996 125 33 −50.9 低客單
4410033 香醇軒-孔宅店2.5 K1 353,753 120 25 None 低客單
2410011 新埤-無尾熊包子W2 K2 351,940 100 12 None 低客單
0210002 美而美-潮州延平 PS 346,911 124 25 None 低客單
0210292 早安拾玖號 PS 341,046 64 5 None 低客單
3810007 香醇軒鹽埕直營-五福2.5 K1 339,686 101 6 None 低客單
2910004 柯小姐2.5 K2 332,957 95 15 None 低客單
4210130 香醇軒-鼎山店1.4 K1 324,756 120 25 None 低客單
3810008 反轉奶酥-鹽埕2.5 K1 318,733 103 23 None 低客單
2610076 巷角鍋燒茶飲-2.5 K2 317,090 105 19 None 低客單
0210116 元氣早點* PS 312,983 162 48 None 低客單
3710080 香醇軒直營-忠孝店2.5 K1 306,501 114 19 None 低客單
2510027 阮慶華2.5 K2 305,403 55 5 None 低客單
4310035 香醇軒-楠梓旗楠店1.4 K2 302,910 72 3 None 低客單
1710010 珍味早點2.5 K2 291,928 120 22 None 低客單

5. 月度營收分析

程式碼 
# Calculate monthly revenue for Sep-Dec 2025 + Jan 2026
monthly_data = []
months = [
    ("09", "2025", "sep_revenue", "9月"),
    ("10", "2025", "oct_revenue", "10月"),
    ("11", "2025", "nov_revenue", "11月"),
    ("12", "2025", "dec_revenue", "12月"),
    ("01", "2026", "jan_revenue", "1月"),
]

# Start with target customers base (include route and csm)
export_df = target_customers.select(["customer_code", "trade_name", "delivery_route", "csm"])

for month_num, year, col_name, _ in months:
    start_date = f"{year}{month_num}01"
    if month_num == "01":
        end_date = f"{year}{month_num}07"  # Only up to Jan 7
    else:
        end_date = f"{year}{month_num}31"

    month_sales = (
        geo_orders
        .filter(
            pl.col("customer_code").is_in(target_customer_codes) &
            (pl.col("order_date") >= start_date) &
            (pl.col("order_date") <= end_date)
        )
        .group_by("customer_code")
        .agg(pl.col("order_total").sum().alias(col_name))
    )

    export_df = export_df.join(month_sales, on="customer_code", how="left")

# Calculate average ticket size
avg_ticket = (
    geo_orders
    .filter(pl.col("customer_code").is_in(target_customer_codes))
    .group_by("customer_code")
    .agg(pl.col("order_total").mean().alias("avg_ticket_size"))
)

export_df = (
    export_df
    .join(avg_ticket, on="customer_code", how="left")
    .with_columns([
        pl.col("sep_revenue").fill_null(0),
        pl.col("oct_revenue").fill_null(0),
        pl.col("nov_revenue").fill_null(0),
        pl.col("dec_revenue").fill_null(0),
        pl.col("jan_revenue").fill_null(0),
        pl.col("avg_ticket_size").fill_null(0).round(0),
        pl.col("csm").fill_null(""),
    ])
    .select([
        "customer_code",
        "trade_name",
        "delivery_route",
        "csm",
        "avg_ticket_size",
        "sep_revenue",
        "oct_revenue",
        "nov_revenue",
        "dec_revenue",
        "jan_revenue",
    ])
    .sort("customer_code")
)

# Display sample
(
    GT(export_df.head(20))
    .tab_header(
        title="月度營收明細 (前20筆)",
        subtitle="2025年9月至2026年1月"
    )
    .cols_label(
        customer_code="客戶代碼",
        trade_name="商號名稱",
        delivery_route="路線",
        csm="CSM",
        avg_ticket_size="平均客單",
        sep_revenue="9月",
        oct_revenue="10月",
        nov_revenue="11月",
        dec_revenue="12月",
        jan_revenue="1月",
    )
    .fmt_number(
        columns=["avg_ticket_size", "sep_revenue", "oct_revenue",
                 "nov_revenue", "dec_revenue", "jan_revenue"],
        decimals=0,
        use_seps=True
    )
)
月度營收明細 (前20筆)
2025年9月至2026年1月
客戶代碼 商號名稱 路線 CSM 平均客單 9月 10月 11月 12月 1月
0110041 歸來抓餅-3 K2 002 2,283 3,742 2,686 4,409 5,724 0
0110042 歸來早點W3 K2 002 5,071 17,658 19,491 7,687 19,231 4,411
0110121 宜美早點-屏東頂柳W3 K2 002 1,858 8,164 12,009 6,577 10,937 2,185
0110190 歸來商店-施家W3 K2 3,174 0 3,095 0 0 0
0110711 美而美-下蚶W3 K2 002 2,296 8,270 10,280 9,291 9,707 2,046
0110923 轉角45-3 K1 003 1,255 3,783 6,224 5,747 7,319 1,307
0111112 芳鄰早餐店W3 K2 002 2,810 10,213 20,939 16,640 18,269 3,538
0180020 小騎士免洗餐具1.4 K2 002 6,457 0 6,457 0 0 0
0180098 泰興中盤-2 K2 003 1,766 9,134 6,662 5,795 9,060 1,510
0210001 晨光早午餐-四維 PS 005 2,298 7,843 7,577 6,150 9,002 1,353
0210002 美而美-潮州延平 PS 005 2,785 28,251 27,911 31,143 27,594 6,779
0210012 吳桂貞 PS 005 3,176 0 2,760 5,280 8,630 4,754
0210013 美而美-潮州光春 PS 005 1,665 2,117 1,225 2,729 2,147 0
0210015 夏一跳-潮州四維 PS 005 1,864 10,096 10,110 6,124 12,496 1,095
0210108 美而美-光華 PS 005 1,127 9,710 9,305 8,076 6,770 2,075
0210116 元氣早點* PS 005 1,947 23,683 21,807 22,543 28,027 8,290
0210126 活力-潮州泗林-3 K1 003 3,956 16,071 20,656 12,935 19,071 3,294
0210145 一心早點 PS 005 2,727 16,305 23,143 18,131 19,572 4,143
0210146 5度C早餐店 PS 005 1,188 6,485 8,149 9,483 7,174 2,038
0210163 早味道 PS 005 1,327 10,679 13,356 9,715 13,944 3,105

6. 資料輸出

程式碼 
from datetime import date

# Sheet/file name
export_name = f"CustomerTargeting_Promo2_{google_sheets_env}_{date.today().strftime('%Y%m%d')}"

# Try Google Sheets first, fall back to CSV for local
sheet_url = google_write_sheet(
    data=export_df,
    sheet_name=export_name,
    sheet_tab="target_customers",
)

if sheet_url:
    print(f"\n**Google Sheet created:** {sheet_url}")
else:
    # Local fallback: write CSV to /tmp
    csv_path = f"/tmp/{export_name}.csv"
    export_df.write_csv(csv_path)
    print(f"\n**CSV file created:** {csv_path}")
    print(f"Total rows: {len(export_df):,}")
Created sheet: https://docs.google.com/spreadsheets/d/15d8pJQIY0iXsLjo_rXh9pxpwnCAB5TlDLPJ5ZmazVVA

**Google Sheet created:** https://docs.google.com/spreadsheets/d/15d8pJQIY0iXsLjo_rXh9pxpwnCAB5TlDLPJ5ZmazVVA

7. 資料說明

註釋欄位說明

輸出欄位:

  • customer_code: 客戶代碼
  • trade_name: 商號名稱
  • delivery_route: 配送路線
  • csm: 客戶經理 (Customer Success Manager)
  • avg_ticket_size: 2025年平均訂單金額
  • sep_revenue ~ jan_revenue: 各月營收

篩選邏輯:

  • 地理: 路線 K1/K2/PS (高雄/潮州)
  • 消費: 2025年消費 ≥ $1,000
  • 活動: >2張低客單(<$3K, 9/15-1/7) 或 業績下滑>30% (10/31-12/31 vs 8/31-10/30)