MTA 位置歸因分析報告（含 Organic 觸點 + 100% 覆蓋）

   SELECT CASE
              WHEN journey_length IS NULL OR journey_length = 0 THEN '0 (無旅程)'
              WHEN journey_length = 1 THEN '1'
              WHEN journey_length = 2 THEN '2'
              WHEN journey_length = 3 THEN '3'
              WHEN journey_length = 4 THEN '4'
              WHEN journey_length = 5 THEN '5'
              WHEN journey_length BETWEEN 6 AND 10 THEN '6-10'
              WHEN journey_length BETWEEN 11 AND 20 THEN '11-20'
              ELSE '21+'
          END AS journey_group,
          COUNT(*) AS orders,
          ROUND(SUM(gross_profit), 2) AS total_profit
     FROM `dm_tableau.tableau_mta_order_424`
 GROUP BY 1
 ORDER BY MIN(COALESCE(journey_length, 0))


-- AOV by length

   SELECT COALESCE(journey_length, 0) AS jlen,
          ROUND(AVG(gross_revenue), 2) AS avg_revenue,
          ROUND(AVG(gross_profit), 2) AS avg_profit
     FROM `dm_tableau.tableau_mta_order_424`
    WHERE COALESCE(journey_length, 0) <= 15
 GROUP BY 1
 ORDER BY 1

   SELECT channel,
          SUM(first_cnt) AS first_touch_orders,
          SUM(last_cnt) AS last_touch_orders
     FROM (
           SELECT first_touch_channel AS channel, COUNT(*) AS first_cnt, 0 AS last_cnt
             FROM `dm_tableau.tableau_mta_order_424`
            WHERE first_touch_channel IS NOT NULL
         GROUP BY 1
          UNION ALL
           SELECT last_touch_channel, 0, COUNT(*)
             FROM `dm_tableau.tableau_mta_order_424`
            WHERE last_touch_channel IS NOT NULL
         GROUP BY 1
          )
 GROUP BY 1

-- Last-Click by Channel

   SELECT COALESCE(last_touch_channel, 'NO_JOURNEY') AS channel,
          ROUND(SUM(gross_profit), 2) AS lc_profit,
          COUNT(*) AS orders
     FROM `dm_tableau.tableau_mta_order_424`
 GROUP BY 1


-- MTA First by Channel

   SELECT first_touch_channel AS channel,
          ROUND(SUM(first_touch_attributed_profit), 2) AS mta_first
     FROM `dm_tableau.tableau_mta_order_424`
    WHERE first_touch_channel IS NOT NULL
 GROUP BY 1


-- MTA Last by Channel

   SELECT last_touch_channel AS channel,
          ROUND(SUM(last_touch_attributed_profit), 2) AS mta_last
     FROM `dm_tableau.tableau_mta_order_424`
    WHERE last_touch_channel IS NOT NULL
 GROUP BY 1

-- LC by Main Cat

   SELECT COALESCE(last_touch_main_cat, 'Unknown') AS main_cat,
          ROUND(SUM(gross_profit), 2) AS lc_profit
     FROM `dm_tableau.tableau_mta_order_424`
    WHERE last_touch_channel IS NOT NULL
 GROUP BY 1


-- MTA First by Main Cat

   SELECT COALESCE(first_touch_main_cat, 'Unknown') AS main_cat,
          ROUND(SUM(first_touch_attributed_profit), 2) AS mta_first
     FROM `dm_tableau.tableau_mta_order_424`
    WHERE first_touch_channel IS NOT NULL
 GROUP BY 1


-- MTA Last by Main Cat

   SELECT COALESCE(last_touch_main_cat, 'Unknown') AS main_cat,
          ROUND(SUM(last_touch_attributed_profit), 2) AS mta_last
     FROM `dm_tableau.tableau_mta_order_424`
    WHERE last_touch_channel IS NOT NULL
 GROUP BY 1

-- LC by CID

   SELECT COALESCE(last_touch_cid, 'ORGANIC') AS cid,
          MAX(COALESCE(last_touch_site_name, 'ORGANIC')) AS site_name,
          MAX(COALESCE(SUBSTR(last_touch_channel, 1, 4), 'ORGA')) AS ch,
          ROUND(SUM(gross_profit), 2) AS lc_profit,
          COUNT(*) AS orders
     FROM `dm_tableau.tableau_mta_order_424`
    WHERE last_touch_channel IS NOT NULL
 GROUP BY 1


-- MTA First by CID

   SELECT COALESCE(first_touch_cid, 'ORGANIC') AS cid,
          ROUND(SUM(first_touch_attributed_profit), 2) AS mta_first
     FROM `dm_tableau.tableau_mta_order_424`
    WHERE first_touch_channel IS NOT NULL
 GROUP BY 1

   SELECT main_cat,
          ROUND(SUM(mta_first_rev), 2) AS first_rev,
          ROUND(SUM(mta_last_rev), 2) AS last_rev
     FROM (
           SELECT COALESCE(first_touch_main_cat, 'Unknown') AS main_cat,
                  first_touch_attributed_revenue AS mta_first_rev, 0 AS mta_last_rev
             FROM `dm_tableau.tableau_mta_order_424`
            WHERE first_touch_channel IS NOT NULL
          UNION ALL
           SELECT COALESCE(last_touch_main_cat, 'Unknown'),
                  0, last_touch_attributed_revenue
             FROM `dm_tableau.tableau_mta_order_424`
            WHERE last_touch_channel IS NOT NULL
          )
 GROUP BY 1
 ORDER BY first_rev DESC

   SELECT first_touch_channel AS first_ch,
          last_touch_channel AS last_ch,
          COUNT(*) AS orders,
          ROUND(SUM(gross_profit), 2) AS profit
     FROM `dm_tableau.tableau_mta_order_424`
    WHERE journey_length >= 2
      AND first_touch_channel IS NOT NULL
      AND last_touch_channel IS NOT NULL
 GROUP BY 1, 2

WITH base AS (
    SELECT order_oid, gross_profit,
           user_journey_detail_adj AS journey,
           ARRAY_LENGTH(user_journey_detail_adj) AS jlen
      FROM `dw_analysis_record.kkday_order_cid_journey_action`
     WHERE ARRAY_LENGTH(user_journey_detail_adj) >= 3
),
cid_info AS (
    SELECT CAST(cid AS STRING) AS cid, cid_main_cat
      FROM `dm_tableau.tableau_party_affiliate`
),
middle AS (
    SELECT t.channel, t.cid, t.site_name, ci.cid_main_cat,
           ROUND(b.gross_profit * 0.2 / (b.jlen - 2), 4) AS attr_profit
      FROM base b, UNNEST(b.journey) AS t WITH OFFSET AS pos
           LEFT JOIN cid_info ci ON t.cid = ci.cid
     WHERE pos > 0 AND pos < b.jlen - 1
)
   SELECT channel, cid, MAX(site_name) AS site_name,
          MAX(COALESCE(cid_main_cat, 'Unknown')) AS main_cat,
          COUNT(*) AS assist_orders,
          ROUND(SUM(attr_profit), 2) AS middle_profit
     FROM middle
 GROUP BY 1, 2
 ORDER BY middle_profit DESC