coupon_adj_order_profit 新舊版差異分析

DT-5561 sponsor_data 改版 vs UDF calculate_discounted_profit | 2026-03-24
KKday Data Team
Generated: 2026-03-24 01:50

分析脈絡

對應提問
DT-5561 改版後 coupon_adj_order_profit 為何差 ~1%？差異來自哪些 coupon？
拆解方式
  1. 查詢原版 UDF calculate_discounted_profit 的完整定義
  2. 比對新版 sponsor_data CASE 邏輯與原版 UDF 的 22 條規則
  3. 找出所有差異訂單並按 coupon UUID 分群統計
資料來源與欄位
  • dm_tableau.tableau_order / tableau_order_offline
  • dm_tableau.tableau_order_all（原版計算結果）
  • dw_kkdb.coupon（sponsor_data）
  • dm_tableau.INFORMATION_SCHEMA.ROUTINES（UDF 定義）
資料區間
全量資料（無日期範圍限制）

改版背景

原版：使用 BigQuery UDF calculate_discounted_profit，內含 22 條 hardcoded CASE 規則，用 order_coupon_uuid 和 order_coupon_note 做 REGEX 匹配，對應 10%~100% 不等的折扣回補比例。

新版（DT-5561）：改用 dw_kkdb.coupon.sponsor_data 的 bearDiscountType（fixed / percentage）和 bearDiscountAmount，以資料驅動取代 hardcoded 邏輯。

差額 = 原版 (UDF) - 新版 (sponsor_data)，正值表示新版少算

整體差異總覽

新版（sponsor_data bearDiscountType）vs 原版（UDF calculate_discounted_profit）在 coupon_adj_order_profit 上的差異

關鍵發現
共 6,385,863 筆訂單存在計算差異，新版比原版少計 2,631,787 TWD。主因是多數第三方合作 coupon 的 sponsor_data 欄位未填充，導致新版無法識別折扣規則、直接使用原始 order_profit。
差異訂單數
6,385,863
原版 (UDF) 合計
2,907,487
新版 (sponsor_data) 合計
275,700
差額
+2,631,787
資料來源與計算方式
SQL 查詢
WITH diff_orders AS (
    SELECT sub.order_oid, sub.order_coupon_uuid, sub.order_coupon_note,
           sub.order_coupon_price_kk, sub.bear_discount_type, sub.bear_discount_amount,
           old.coupon_adj_order_profit AS old_value, sub.new_profit AS new_value,
           old.coupon_adj_order_profit - sub.new_profit AS diff
      FROM (
            SELECT oa.order_oid, oa.order_coupon_uuid, oa.order_coupon_note,
                   oa.order_profit, oa.order_coupon_price_kk, oa.order_pkg_oid,
                   cs.bear_discount_type, cs.bear_discount_amount,
                   CASE
                       WHEN REGEXP_CONTAINS(oa.order_coupon_uuid, r'THSR') AND oa.order_pkg_oid = 421443
                           THEN oa.order_coupon_price_kk + oa.order_profit
                       WHEN cs.bear_discount_type = 'fixed'
                           THEN oa.order_profit + cs.bear_discount_amount
                       WHEN cs.bear_discount_type = 'percentage'
                           THEN oa.order_profit + oa.order_coupon_price_kk * cs.bear_discount_amount
                       ELSE oa.order_profit
                   END AS new_profit
              FROM (SELECT *, 'tableau_order' AS table_name FROM `dm_tableau.tableau_order`
                    UNION ALL
                    SELECT *, 'tableau_order_offline' AS table_name FROM `dm_tableau.tableau_order_offline`) oa
              LEFT JOIN (SELECT coupon,
                                JSON_VALUE(sponsor_data, '$.bearDiscountType') AS bear_discount_type,
                                SAFE_CAST(JSON_VALUE(sponsor_data, '$.bearDiscountAmount') AS NUMERIC) AS bear_discount_amount
                           FROM `dw_kkdb.coupon`
                          WHERE sponsor_data IS NOT NULL) cs
                ON oa.order_coupon_uuid = cs.coupon
           ) sub
      JOIN `dm_tableau.tableau_order_all` old ON sub.order_oid = old.order_oid
     WHERE ROUND(old.coupon_adj_order_profit, 2) != ROUND(sub.new_profit, 2)
)
   SELECT order_coupon_uuid, bear_discount_type, bear_discount_amount,
          COUNT(*) AS cnt, ROUND(SUM(diff), 2) AS total_diff,
          ROUND(AVG(old_value), 2) AS avg_old, ROUND(AVG(new_value), 2) AS avg_new
     FROM diff_orders
 GROUP BY order_coupon_uuid, bear_discount_type, bear_discount_amount
 ORDER BY ABS(SUM(diff)) DESC
    LIMIT 30

差異來源分類

差異可分為兩大類型

關鍵發現
類型 A — UDF 有 match 但新版無 sponsor_data：佔差額 1,164,700 TWD，這些 coupon 在 UDF 裡有 hardcoded 規則（用 UUID/note REGEX 匹配），但 coupon 表的 sponsor_data 是 NULL。

類型 B — 新版有 sponsor_data 但 UDF 無 match：佔差額 130,305 TWD（反向），典型如 KKDWEWA26（fixed=105），新版多加了回補但 UDF 沒有這條規則。

Top 20 差異 Coupon

依差額絕對值排序的前 20 個 coupon，紅色 = UDF 較高（新版少算），綠色 = 新版較高（新版多算）

Coupon 系列差額分群

將差異 coupon 按命名規則分群（JCB、DBS/CITI、SKBINF、KKDWEWA 等），觀察各系列的影響範圍

結論與建議

根因

  • sponsor_data 欄位填充率不足，未涵蓋 UDF 裡所有 hardcoded coupon 規則
  • UDF 有 22 條規則覆蓋上百個 coupon pattern，但多數 coupon 在 coupon 表沒有 sponsor_data
  • 少數反向案例（如 KKDWEWA26）：新 coupon 有 sponsor_data 但不在 UDF hardcoded list

建議行動

  • 短期：確保所有 UDF 涵蓋的 coupon 在 coupon 表都有正確的 sponsor_data（需協調 coupon 系統補資料）
  • 過渡期：在新版 SQL 中保留 UDF 的 hardcoded fallback 邏輯，當 sponsor_data 為 NULL 時回退到 REGEX 規則
  • 長期：所有新 coupon 建立時即填寫 sponsor_data，逐步淘汰 hardcoded 規則

差異 Coupon 完整明細（Top 30）

bear_discount_type 為「—」表示 coupon 表沒有 sponsor_data

Coupon UUID sponsor_data 類型 sponsor_data 金額 訂單數 差額合計 Avg (UDF) Avg (新版)
KKDWEWA26 fixed 105.0 1,241 -130,305.00 8.63 113.63
KKDBOCHKAE — — 5,963 +84,110.77 1.65 -12.45
HASE300 — — 2,654 +83,356.39 10.68 -20.72
25HSBCVISA10 — — 4,178 +67,817.37 24.61 8.38
SKBINF3000 — — 1,191 +58,274.56 24.29 -22.82
VISASH50 — — 2,516 +57,469.15 5.60 -17.24
41620570USJ — — 969 +51,497.32 -1.47 -54.61
JCB6010 — — 1,966 +41,779.94 29.60 8.34
JCB250002 — — 7,528 +41,119.28 8.88 3.42
JCB250004 — — 7,449 +40,947.06 9.77 4.27
JCB6011 — — 1,915 +40,710.90 29.60 8.34
JCB250003 — — 7,546 +40,281.98 9.39 4.05
JCB6009 — — 1,862 +36,383.32 23.61 4.07
45631863DBS24 — — 11,799 +35,696.60 3.13 0.11
JCB4010 — — 5,202 +34,695.92 14.67 8.00
ATOME25 — — 1,451 +34,628.98 -1.26 -25.12
KKCTBC500 — — 4,201 +34,549.99 4.29 -3.94
JCB4011 — — 5,023 +34,273.84 15.82 9.00
45631863DBS2H — — 10,380 +32,116.98 3.14 0.04
SKBINF25Q2 — — 808 +32,095.58 24.29 -15.43
SKBINF8 — — 3,933 +30,534.14 7.30 -0.46
42844002DBS24 — — 10,283 +30,404.27 2.99 0.03
JCB4009 — — 4,397 +29,533.55 13.63 6.91
42844002DBS2H — — 9,229 +29,154.83 2.93 -0.23
25KKCCBA250 — — 1,575 +28,631.46 13.21 -4.97
45631863DBS1H — — 9,150 +28,054.24 2.90 -0.16
JCB500002 — — 1,527 +27,527.18 16.07 -1.96
JCB500003 — — 1,488 +27,266.91 14.89 -3.44
JCB500004 — — 1,605 +26,132.53 13.64 -2.64
45631863CITI2023 — — 7,301 +25,654.50 3.30 -0.21
資料來源與計算方式
SQL 查詢
WITH diff_orders AS (
    SELECT sub.order_oid, sub.order_coupon_uuid, sub.order_coupon_note,
           sub.order_coupon_price_kk, sub.bear_discount_type, sub.bear_discount_amount,
           old.coupon_adj_order_profit AS old_value, sub.new_profit AS new_value,
           old.coupon_adj_order_profit - sub.new_profit AS diff
      FROM (
            SELECT oa.order_oid, oa.order_coupon_uuid, oa.order_coupon_note,
                   oa.order_profit, oa.order_coupon_price_kk, oa.order_pkg_oid,
                   cs.bear_discount_type, cs.bear_discount_amount,
                   CASE
                       WHEN REGEXP_CONTAINS(oa.order_coupon_uuid, r'THSR') AND oa.order_pkg_oid = 421443
                           THEN oa.order_coupon_price_kk + oa.order_profit
                       WHEN cs.bear_discount_type = 'fixed'
                           THEN oa.order_profit + cs.bear_discount_amount
                       WHEN cs.bear_discount_type = 'percentage'
                           THEN oa.order_profit + oa.order_coupon_price_kk * cs.bear_discount_amount
                       ELSE oa.order_profit
                   END AS new_profit
              FROM (SELECT *, 'tableau_order' AS table_name FROM `dm_tableau.tableau_order`
                    UNION ALL
                    SELECT *, 'tableau_order_offline' AS table_name FROM `dm_tableau.tableau_order_offline`) oa
              LEFT JOIN (SELECT coupon,
                                JSON_VALUE(sponsor_data, '$.bearDiscountType') AS bear_discount_type,
                                SAFE_CAST(JSON_VALUE(sponsor_data, '$.bearDiscountAmount') AS NUMERIC) AS bear_discount_amount
                           FROM `dw_kkdb.coupon`
                          WHERE sponsor_data IS NOT NULL) cs
                ON oa.order_coupon_uuid = cs.coupon
           ) sub
      JOIN `dm_tableau.tableau_order_all` old ON sub.order_oid = old.order_oid
     WHERE ROUND(old.coupon_adj_order_profit, 2) != ROUND(sub.new_profit, 2)
)
   SELECT order_coupon_uuid, bear_discount_type, bear_discount_amount,
          COUNT(*) AS cnt, ROUND(SUM(diff), 2) AS total_diff,
          ROUND(AVG(old_value), 2) AS avg_old, ROUND(AVG(new_value), 2) AS avg_new
     FROM diff_orders
 GROUP BY order_coupon_uuid, bear_discount_type, bear_discount_amount
 ORDER BY ABS(SUM(diff)) DESC
    LIMIT 30

原版 UDF 規則對照表

UDF calculate_discounted_profit 的 22 條 hardcoded 規則，每條對應不同的折扣回補比例

規則 回補比例 匹配方式 匹配 Pattern 計算公式
1 100% uuid KKDAYGO, GOTO, MOTTO, VISA50, WEWAB, ZENKOKU, ... (60+ patterns) coupon_price + profit
2 100% note KKday x Visa, MKT-AFF-KTO, MGTO-B1G1, ... coupon_price + profit
3 75% note 花旗銀行, 星展銀行, 星展饗樂卡 coupon_price × 0.75 + profit
4 90% note 新光銀行 Visa 無限卡 2024Q3 coupon_price × 0.9 + profit
5 90% uuid ESUN06-09, FUBONSP7510, ESUN11-12 系列 coupon_price × 0.9 + profit
6 89% note 新光銀行 Visa 無限卡 2025Q2 coupon_price × 0.89 + profit
7 89% uuid SKBINF26, SKBINF2512 coupon_price × 0.89 + profit
8 85% uuid ESUNTW, TR25, TR300 coupon_price × 0.85 + profit
9 84% note 台新銀行 coupon_price × 0.84 + profit
10 83% uuid KKCOS851/852/500, UNI480/120, HASE300 coupon_price × 0.83 + profit
11 80% uuid FUBONVISA25, FUBON500, BEA 系列, UNI1500, SKBINF75 coupon_price × 0.8 + profit
12 75% uuid KKDWEWAFNB, KKDAYDSB80, 25KKCCBATRA coupon_price × 0.75 + profit
13 75% note 星展饗樂生活卡 2026 全球 8 折 coupon_price × 0.75 + profit
14 72% uuid KKDAYDSB250, KKDWEWAJP coupon_price × 0.72 + profit
15 70% uuid TR85, KKDWEWA SPA/AIR/GBA/SEA coupon_price × 0.7 + profit
16 66% uuid KKQBURGER, KKDWEWA25, KKDBEA135, 25KKDBEA300, 25KKCCBA250 coupon_price × 0.66 + profit
17 60% uuid DBSCRUISE24, KKDWEWATIX, 25KKDPAY, 25KK 系列 coupon_price × 0.6 + profit
18 55% uuid ESUNSKI09, 25KKCCBA180 coupon_price × 0.55 + profit
19 50% uuid ATOME, HANA, HYUNDAI, KTOITF2, KKDWEWA150, CARDX coupon_price × 0.5 + profit
20 50% note MKT-PNS-KTC coupon_price × 0.5 + profit
21 10% uuid TRAVLOG4 coupon_price × 0.1 + profit
22 100% uuid+pkg THSR + order_pkg_oid = 421443 coupon_price + profit
資料來源與計算方式
SQL 查詢
SELECT routine_definition
  FROM `dm_tableau.INFORMATION_SCHEMA.ROUTINES`
 WHERE routine_name = 'calculate_discounted_profit'

分析方法說明

將新版 CASE 邏輯套用在 tableau_order + tableau_order_offline 上，與現有 tableau_order_all 的 coupon_adj_order_profit 逐筆比對，ROUND 到小數第 2 位後找出不一致的訂單，再按 coupon UUID 分群統計差額。