屏東市促銷目標客戶分析

Pingtung Customer Targeting for Promo Campaign

發佈於

2026年1月21日

1. 分析目的

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

地理條件 (符合任一):

  • 配送路線為 PT、A-PT、A-PN 或 PN
  • 或送貨地址包含「屏東市」

AND 消費條件:

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

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

  • 2025/8/15-12/15 期間有超過 2 張訂單金額低於 $3,000
  • 或 2025/10/16-12/15 相較 2025/8/15-10/15 銷售額下降超過 30%
程式碼 
# Date constants (YYYYMMDD format for database)
YEAR_START = "20250101"
YEAR_END = "20251231"
PERIOD_START = "20250815"
PERIOD_END = "20251215"
PERIOD1_START = "20250815"
PERIOD1_END = "20251015"
PERIOD2_START = "20251016"
PERIOD2_END = "20251215"

# Target routes for Pingtung area
TARGET_ROUTES = ["PT", "A-PT", "A-PN", "PN"]

# 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
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 <= '20251231'
    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,224 customers and 58,525 orders

2. 客戶篩選

程式碼 
# Step 1: Geographic filter - route OR address contains 屏東市
geo_customers = customers_raw.filter(
    pl.col("delivery_route").is_in(TARGET_ROUTES) |
    pl.col("shipping_address").str.contains("屏東市")
).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")

# 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
    .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: 3,160 customers in target geography
Step 2: 21,726 orders from geographic customers
Step 3: 560 customers with >= $1,000 spend in 2025
程式碼 
# Filter orders for the analysis period (8/15 - 12/15)
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/15 - 10/15
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/16 - 12/15
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: 366 customers with >2 tickets < $3,000
Activity Filter B: 123 customers with >30% sales decline
Combined Activity Filter: 416 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: 416
- Low ticket only: 293
- Declining only: 50
- Both conditions: 73

3. 執行摘要

目標客戶總覽

指標 數值
總目標客戶數 416
低客單客戶 (>2張<$3K) 366
業績下滑客戶 (>30%↓) 123
兩者皆符合 73
平均年度消費 $134,118
平均訂單數 49.2

篩選漏斗

  1. 地理篩選: 3,160 位屏東地區客戶
  2. 消費篩選: 560 位年消費 ≥ $1,000
  3. 活動篩選: 416 位符合目標條件

目標原因分布

  • 低客單 (小額訂單多): 366 位 (88.0%)
  • 業績下滑 (需挽回): 123 位 (29.6%)

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消費 訂單數 低客單次數 業績變化% 目標原因
0190012 宵歸暝-屏東總店 A-PT 1,510,451 89 6 None 低客單
1010037 美美早餐店(鹽埔)-1.4 A-PN 1,458,425 324 57 None 低客單
0610012 繁華市場1.4 A-PN 1,304,472 297 58 None 低客單
1010047 四海-新庄1.4 A-PN 1,248,517 131 9 None 低客單
1010013 張蓮珠-1.4 A-PN 1,184,968 121 8 None 低客單
1010057 永和-新圍(1.4) A-PN 982,411 118 3 None 低客單
0111161 小田園直營-大武2.5 A-PT 968,258 107 4 None 低客單
0110870 蔡蛋 A-PT 918,876 265 19 None 低客單
0610071 大屏北早餐-繁華1.4 A-PN 766,670 129 11 None 低客單
0111119 小田園直營-棒球2.5 A-PT 739,216 112 6 None 低客單
0610046 永和-潭頭-1.4 A-PN 659,212 105 10 None 低客單
0810013 金屋早點2.5 A-PN 618,355 107 13 None 低客單
1110025 田子早點-1.4 A-PN 590,250 119 15 None 低客單
0111263 早安8快速早餐-廣東店 A-PT 542,108 106 5 None 低客單
0111120 小田園直營-廣東1.4 A-PT 533,858 128 9 None 低客單
0910058 張坤如2.5 A-PN 533,274 98 5 None 低客單
0910007 天心-里港2.5 A-PN 530,582 103 8 None 低客單
0111122 早安8快速早餐-永大店 A-PT 506,553 103 18 None 低客單
0810005 順利早點2.5 A-PN 502,953 107 7 None 低客單
1110117 大屏北早餐-高樹店2.5 A-PN 494,960 110 23 −37.8 低客單
0111265 早安8快速早餐-廣二店 A-PT 485,708 121 24 None 低客單
0110039 陸橋豆乳 A-PT 476,506 121 9 None 低客單
1010041 美香早點(2.5) A-PN 463,834 106 7 None 低客單
0810019 陳惠林-2.5 A-PN 455,095 94 7 None 低客單
0111242 咕嘰咕嘰-林森 PT 450,156 108 14 None 低客單
1010038 糖果盒子/20-1.4 A-PN 435,130 114 17 None 低客單
0111239 初飽了沒快速早餐 A-PT 432,938 164 32 None 低客單
0810076 大屏北早餐-九如店 A-PN 432,712 99 9 None 低客單
0110008 鍋貼叔叔 A-PT 431,789 185 49 None 低客單
0910008 天天早點-1.4 A-PN 411,514 139 22 None 低客單
0120959 巧味鹽酥雞(勝利路) A-PT 407,459 137 34 None 低客單
0110043 拿了就走 A-PT 400,402 108 16 −38.2 低客單
0110167 光復-美而美 A-PT 389,760 82 14 −32.9 低客單
0110651 阮兜仔早餐店 A-PT 380,717 115 16 None 低客單
1030021 鹽埔火鍋2.5 A-PN 377,810 69 8 None 低客單
1110022 美而美-高樹 2.5 A-PN 373,719 100 11 None 低客單
0910011 天心-三塊厝-2.5 A-PN 371,727 93 10 −37.1 低客單
1110004 胖胖早點-1.4 A-PN 370,196 129 25 None 低客單
0111118 郭記肉圓 A-PT 358,823 166 44 None 低客單
1010024 林振宏1.4 A-PN 353,185 116 31 None 低客單
1010097 永和豆漿-鹽埔1.4 A-PN 346,794 50 4 None 低客單
0111156 咕嘰加盟-永大 A-PT 342,847 99 20 None 低客單
1120024 俊龍商號-中盤2.5 A-PN 341,068 97 14 None 低客單
0110096 豆豆早點屏東廣東 A-PT 330,023 203 50 None 低客單
0120517 頭前溪巧味早點2.5 A-PT 323,998 86 10 None 低客單
0810014 巨森-九如-2.5 A-PN 318,350 106 20 None 低客單
0120725 清涼冷飲-公館2.5 A-PT 315,648 101 20 None 低客單
0111243 二弄1號你的廚房 A-PT 314,984 74 9 None 低客單
1010022 晨間餐飲-翁冬蜜-1.4 A-PN 291,654 116 28 None 低客單
0111225 早安美芝城-屏東自由 A-PT 289,378 112 28 None 低客單

5. 月度營收分析

程式碼 
# Calculate monthly revenue for Aug-Dec 2025
monthly_data = []
months = [
    ("08", "aug_revenue", "8月"),
    ("09", "sep_revenue", "9月"),
    ("10", "oct_revenue", "10月"),
    ("11", "nov_revenue", "11月"),
    ("12", "dec_revenue", "12月"),
]

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

for month_num, col_name, _ in months:
    start_date = f"2025{month_num}01"
    end_date = f"2025{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("aug_revenue").fill_null(0),
        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("avg_ticket_size").fill_null(0).round(0),
        pl.col("csm").fill_null(""),
    ])
    .select([
        "customer_code",
        "trade_name",
        "delivery_route",
        "csm",
        "avg_ticket_size",
        "aug_revenue",
        "sep_revenue",
        "oct_revenue",
        "nov_revenue",
        "dec_revenue",
    ])
    .sort("customer_code")
)

# Display sample
(
    GT(export_df.head(20))
    .tab_header(
        title="月度營收明細 (前20筆)",
        subtitle="2025年8月至12月"
    )
    .cols_label(
        customer_code="客戶代碼",
        trade_name="商號名稱",
        delivery_route="路線",
        csm="CSM",
        avg_ticket_size="平均客單",
        aug_revenue="8月",
        sep_revenue="9月",
        oct_revenue="10月",
        nov_revenue="11月",
        dec_revenue="12月",
    )
    .fmt_number(
        columns=["avg_ticket_size", "aug_revenue", "sep_revenue",
                 "oct_revenue", "nov_revenue", "dec_revenue"],
        decimals=0,
        use_seps=True
    )
)
月度營收明細 (前20筆)
2025年8月至12月
客戶代碼 商號名稱 路線 CSM 平均客單 8月 9月 10月 11月 12月
0110002 美而美公園西路 A-PT 002 1,374 14,578 17,065 15,582 12,531 13,936
0110004 巨林-屏東中正 A-PT 002 982 2,171 2,094 1,202 2,104 1,015
0110007 夏一跳-公裕街 A-PT 002 2,839 19,872 20,931 21,572 8,827 10,554
0110008 鍋貼叔叔 A-PT 002 2,334 19,157 58,890 44,733 44,711 45,099
0110009 早來找去 A-PT 002 2,238 17,216 14,106 18,576 15,634 17,284
0110013 喀拉喀民族店 A-PT 002 3,775 10,225 16,990 12,027 10,950 14,580
0110021 小叮噹屏東附小 A-PT 002 2,673 3,826 10,479 6,794 1,913 6,635
0110022 小叮噹公園東路 A-PT 002 1,495 1,763 2,172 3,305 2,625 2,551
0110031 歐嗨喲早午餐-施小姐 A-PT 002 2,929 9,999 7,189 6,361 13,265 6,433
0110037 福記早點2.5 A-PT 002 1,444 7,811 9,450 9,976 4,206 5,498
0110039 陸橋豆乳 A-PT 002 3,938 43,355 35,171 49,064 33,958 50,925
0110041 歸來抓餅-3 K2 002 2,127 4,857 3,742 2,686 4,409 0
0110042 歸來早點W3 K2 002 5,091 13,834 17,658 19,491 7,687 19,231
0110043 拿了就走 A-PT 002 3,707 37,671 28,663 66,101 31,173 34,936
0110044 美而美屏東鶴聲 A-PT 002 897 10,501 11,632 7,267 8,460 10,921
0110045 明蕙早餐 A-PT 002 1,858 2,190 0 4,381 2,551 11,594
0110053 美美早點-3 A-PT 002 2,499 4,438 1,420 2,679 10,264 2,973
0110058 陳太太-復興南路2.5 A-PT 002 2,281 13,749 7,524 8,623 3,126 3,762
0110062 營養早點-信義阿姑 A-PT 002 1,277 2,979 1,267 2,530 2,182 1,918
0110070 季香早點 A-PT 002 2,573 0 1,324 1,324 0 1,324

6. 資料輸出

程式碼 
from datetime import date

# Sheet/file name
export_name = f"CustomerTargeting_{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/18q_n4eSWRfTsG4NBS1df013-K2R3lTls6EXbDozpqnY

**Google Sheet created:** https://docs.google.com/spreadsheets/d/18q_n4eSWRfTsG4NBS1df013-K2R3lTls6EXbDozpqnY

7. 資料說明

註釋欄位說明

輸出欄位:

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

篩選邏輯:

  • 地理: 路線 PT/A-PT/A-PN/PN 或地址含「屏東市」
  • 消費: 2025年消費 ≥ $1,000
  • 活動: >2張低客單(<$3K) 或 業績下滑>30%