Coupon 漏斗分析指南

分析脈絡

對應提問

如何分析 Coupon 的發放數、兌換數、使用數？

拆解方式

coupon + coupon_useqty：券設定與配額
coupon_member：會員領取明細
tableau_order_all：訂單使用明細（order_coupon_uuid = coupon）
coupon_condition：需要時查券的適用條件（JSON）
母子券：透過 note 的 FROM: 標記聚合回母券（活動粒度）

資料來源與欄位

dw_kkdb.coupon + coupon_useqty — 發放
dw_kkdb.coupon_member — 兌換
dm_tableau.tableau_order_all — 使用

資料區間

覆蓋率驗證日期：2026-03-12

三張表串起完整漏斗

dw_kkdb.coupon + coupon_useqty（發放配額） → dw_kkdb.coupon_member（兌換領取） → dm_tableau.tableau_order_all（訂單使用）

JOIN Key 統一為券碼：coupon = order_coupon_uuid。部分券有母子關係（母券 → 多張子券），子券的 note 含 [FROM:母券碼]。

漏斗架構

三張表串起完整漏斗，JOIN Key 統一為券碼（coupon）。

dw_kkdb.coupon  +  coupon_useqty    ──► 發放（配額、券設定）
        │              ON coupon = coupon
        │
        ▼
dw_kkdb.coupon_member               ──► 兌換（誰領了哪張券）
        │              ON coupon = coupon
        │
        ▼
dm_tableau.tableau_order_all         ──► 使用（訂單用了哪張券）
                       ON coupon = order_coupon_uuid
                       WHERE table_name = 'tableau_order'

需要查券的適用條件時：JOIN dw_kkdb.coupon_condition ON coupon = coupon，條件存在 condition_txt（JSON 格式）。

母券與子券

部分券有母子關係，分析漏斗時需注意粒度選擇。

什麼是母子券？

一檔活動（如 Cross-Sell、Cohort 行銷）會先建立一張母券（活動級別的券設定），再從母券衍生出多張子券（實際發放給會員的券碼）。子券的 note 欄位會記錄 [FROM:母券碼]。

母券 (parent_coupon)          ← 活動級別設定
  └── 1:N ── 子券 (coupon)    ← 實際發放的券碼
                │
                ├── coupon_member     會員領取的是子券
                └── order_coupon_uuid 訂單使用的也是子券

如何辨識母子關係

欄位	說明
`coupon.note`	子券的 note 含 `[FROM:母券碼]`，用 `REGEXP_EXTRACT(note, r'FROM:([^\]]+)')` 取出母券碼

誰在用母子券？

母子券不限於 Cohort，多種來源都在使用（2026-03-12 驗證）：

來源	不重複子券數	佔比
Cohort 行銷券	333,653	66%
非 Cohort（Cross-Sell、手動活動等）	173,490	34%
合計（帶 FROM: 的子券）	507,143	100%

對漏斗分析的影響

以子券粒度分析（預設）：三張表直接串接即可，每張子券獨立有配額、領取、使用記錄，數字正確
以活動粒度分析（聚合回母券）：需加 REGEXP_EXTRACT(note, r'FROM:([^\]]+)') AS parent_coupon，再 GROUP BY parent_coupon

各表關鍵欄位

dw_kkdb.coupon — 券主表

欄位	說明
`coupon`	券碼（PK），也是整條漏斗的 JOIN Key
`coupon_nm`	券名稱
`discount_amount`	折扣金額（<1 為比例折扣，≥1 為定額）
`offsetting_tp`	折抵方式：Q=定額、S=比例
`note`	備註，子券會包含 `[FROM:母券碼]`

dw_kkdb.coupon_useqty — 配額

欄位	說明
`qty`	總配額（全部 ≥ 1，大量券用極大值如 10,000,000 表示無上限）
`retain_qty`	已被領取數
`order_qty`	已被使用於訂單的數量

dw_kkdb.coupon_member — 兌換明細

欄位	說明
`coupon`	券碼（FK → coupon 主表）
`member_uuid`	領取的會員
`create_date`	領取時間（Partition Key）
`order_qty`	該會員用這張券下了幾單

dm_tableau.tableau_order_all — 訂單使用

欄位	說明
`order_coupon_uuid`	券碼（= coupon 主表 PK，來源：order_lst.coupon_uuid）
`order_coupon_note`	券名稱
`coupon_type_nm`	券類型
`coupon_adj_accounting_price`	營收/GMV（金額計算必用）
`coupon_adj_order_profit`	利潤/GP（金額計算必用）

注意：tableau_order_all 查詢必加 WHERE table_name = 'tableau_order'，金額一律用 coupon_adj_* 欄位。

SQL 範本：子券粒度

以子券為粒度的漏斗查詢（含 parent_coupon 欄位，可選擇是否聚合）。

-- Coupon 完整漏斗：每張券的發放 / 兌換 / 使用
WITH coupon_base AS (
    SELECT c.coupon,
           c.coupon_nm,
           c.discount_amount,
           u.qty          AS total_quota,
           u.retain_qty   AS claimed_count,
           u.order_qty    AS used_count_agg,
           -- 母子券：取出母券碼（無母券則為 NULL）
           REGEXP_EXTRACT(c.note, r'FROM:([^]]+)') AS parent_coupon
      FROM `dw_kkdb.coupon` c
      JOIN `dw_kkdb.coupon_useqty` u ON c.coupon = u.coupon
),

claimed AS (
    SELECT coupon,
           COUNT(DISTINCT member_uuid) AS claimed_members
      FROM `dw_kkdb.coupon_member`
  GROUP BY coupon
),

used AS (
    SELECT order_coupon_uuid                  AS coupon,
           COUNT(DISTINCT order_oid)           AS order_count,
           SUM(coupon_adj_accounting_price)     AS total_gmv,
           SUM(coupon_adj_order_profit)         AS total_gp
      FROM `dm_tableau.tableau_order_all`
     WHERE table_name = 'tableau_order'
       AND accounting_type = 'asset'
       AND order_coupon_uuid IS NOT NULL
       AND order_coupon_uuid != ''
       -- AND accounting_dt_tw >= '2025-01-01'  -- 依需求加日期範圍
  GROUP BY order_coupon_uuid
)

   SELECT cb.coupon,
          cb.coupon_nm,
          cb.parent_coupon,
          cb.total_quota,
          cl.claimed_members,
          u.order_count,
          u.total_gmv,
          u.total_gp,
          SAFE_DIVIDE(cl.claimed_members, cb.total_quota) AS claim_rate,
          SAFE_DIVIDE(u.order_count, cl.claimed_members)  AS use_rate
     FROM coupon_base cb
LEFT JOIN claimed cl ON cb.coupon = cl.coupon
LEFT JOIN used u     ON cb.coupon = u.coupon
 ORDER BY u.total_gmv DESC NULLS LAST
    LIMIT 100

SQL 範本：聚合回母券（活動粒度）

把子券聚合回母券，看一檔活動整體的發放/兌換/使用。

-- 聚合回母券粒度：看一檔活動的整體漏斗
WITH coupon_with_parent AS (
    SELECT c.coupon,
           REGEXP_EXTRACT(c.note, r'FROM:([^]]+)') AS parent_coupon,
           u.qty          AS total_quota,
           u.retain_qty   AS claimed_count
      FROM `dw_kkdb.coupon` c
      JOIN `dw_kkdb.coupon_useqty` u ON c.coupon = u.coupon
     WHERE REGEXP_CONTAINS(c.note, r'FROM:')
),

claimed AS (
    SELECT coupon,
           COUNT(DISTINCT member_uuid) AS claimed_members
      FROM `dw_kkdb.coupon_member`
  GROUP BY coupon
),

used AS (
    SELECT order_coupon_uuid                  AS coupon,
           COUNT(DISTINCT order_oid)           AS order_count,
           SUM(coupon_adj_accounting_price)     AS total_gmv
      FROM `dm_tableau.tableau_order_all`
     WHERE table_name = 'tableau_order'
       AND accounting_type = 'asset'
       AND order_coupon_uuid IS NOT NULL
       AND order_coupon_uuid != ''
  GROUP BY order_coupon_uuid
)

   SELECT cp.parent_coupon,
          p.coupon_nm                          AS parent_coupon_nm,
          COUNT(DISTINCT cp.coupon)             AS child_count,
          SUM(cp.total_quota)                   AS total_quota,
          SUM(cl.claimed_members)               AS total_claimed,
          SUM(u.order_count)                    AS total_orders,
          SUM(u.total_gmv)                      AS total_gmv
     FROM coupon_with_parent cp
LEFT JOIN `dw_kkdb.coupon` p ON cp.parent_coupon = p.coupon
LEFT JOIN claimed cl          ON cp.coupon = cl.coupon
LEFT JOIN used u              ON cp.coupon = u.coupon
 GROUP BY cp.parent_coupon, p.coupon_nm
 ORDER BY total_gmv DESC NULLS LAST
    LIMIT 50

分析方法說明

三張核心表透過券碼（coupon）串接，覆蓋率經實際驗證均 > 99.9%。