Level Drop Dashboard

Retention % chỉ tính khi lọc Cohort New user (nhóm cài trong A→B — thấy được từ lần chơi đầu). Công thức: user của level L ÷ user của level mốc 100%, trong đó level mốc 100% = level thấp nhất có data trong "Khoảng Level" (cũng là số ở KPI "Tổng user"); cả tử lẫn mẫu là user của CẢ level (mọi map), gộp mọi version đang bật (vì query theo dõi user xuyên version). Dòng con (map khác khi mở rộng) không có Retention. Drop during % / Drop after % = số user during/after ÷ số user của map đó (rê chuột vào ô để xem số user gốc); màu theo Ngưỡng "rủi ro cao": đỏ > ngưỡng, vàng > ½ ngưỡng, xanh còn lại, tính từ lần chơi đầu ở map đó: during = chưa hoàn thành level rồi nghỉ ≥ 2 ngày; after = đã hoàn thành level (ở map/version nào cũng được) nhưng không chơi level cao hơn rồi nghỉ ≥ 2 ngày (định nghĩa đầy đủ ở tab Định nghĩa & Case, mục 03).

Dữ liệu nuôi Dashboard

Toàn bộ nguồn dữ liệu nuôi tab Dashboard nằm ở đây: data mặc định (kết quả query thật 28/09, tự nạp khi mở trang) / chỗ nạp kết quả query mới, cách chạy query, level_map, từ điển cột + nguyên văn query. Tab Dashboard chỉ hiển thị kết quả đã lọc — tái sử dụng đúng dữ liệu từ đây, không tách riêng.

Level ID mong muốn — level_map theo version

Đã nạp sẵn level_map của 1.2.2 → 1.11 (GD cung cấp, mỗi version 1 khối riêng). Định dạng: dòng 1.9.2: (nhiều version dùng chung 1 danh sách thì viết được 1.2.2 và 1.3.2:) rồi các cặp logical-mapped cách nhau dấu phẩy. Level không khai = chính số level (vd L2 → 2) — đã đối chiếu data thật 1.9.2 / 1.10.4 / 1.11: map đông user nhất khớp đủ 1000/1000 level. Vùng lặp level của build cũ (1.6.4 từ L301, 1.7.1 từ L351, 1.8.13 từ L501) tự tính theo công thức Loop ở tab Định nghĩa & Case mục 04, không áp level_map. Khai trùng 1 level → lấy giá trị SAU (giống game nạp override). Dashboard so map đại diện (đông user nhất trong các map đủ ngưỡng mẫu) với Level ID ở đây: lệch thì hiện ⚠ cạnh số Level; khi mở rộng level, map đúng level_map có nhãn map mong muốn. Version không có trong danh sách (vd 1.12+) thì không cảnh báo. Rê chuột vào ⚠ để thấy cả 2: map muốn phát (level_map) và map game phát thật, kèm % user. Bản 1.0 → 1.5 chưa có event gd_play_level nên không có trong data (level_map của các bản này giữ lại để tham khảo).

Query đọc từ đâu, từng cột nghĩa là gì

A1. Trường nguồn GA4 mà query đọc

A2. 27 cột kết quả query (đúng thứ tự xuất ra)

Mỗi dòng kết quả = version × logical_level × mapped_level × country × media_source × first_open_cohort. Danh sách này khớp đúng câu SELECT cuối của query ở mục E.

B. user_pseudo_id là gì

• Do chính SDK Firebase/GA4 tự sinh ra, gắn theo THIẾT BỊ + LẦN CÀI APP — không phải tài khoản đăng nhập, không phải email, không phải Google/Apple ID.
• Là "identity" DUY NHẤT mà mọi phép đếm COUNT(DISTINCT user_pseudo_id) trong toàn bộ query dùng để ra số "unique users" — kể cả unique_users_logical_level, mapping_users, mọi thứ liên quan tới "bao nhiêu người".
• Cài lại app / đổi máy / xoá data app → sinh ID HOÀN TOÀN MỚI, không có cách nối ngược lại ID cũ — đây chính là gốc của case "đổi điện thoại bị tính Drop oan" ở mục 03.
• Khác với AppsFlyer UID (ID gắn theo attribution) — 2 ID này KHÔNG dùng chung trong query Drop hiện tại, query chỉ dùng user_pseudo_id từ GA4.

C. event_params — vì sao phải UNNEST + COALESCE 4 kiểu

GA4 lưu mọi tham số dưới dạng mảng [{key, value}], và value là 1 STRUCT CHUNG có sẵn 4 field: string_value, int_value, float_value, double_value — chỉ ĐÚNG 1 trong 4 field có giá trị thật, 3 field còn lại NULL, tuỳ kiểu dữ liệu client gửi lên. Vì vậy mọi lần lấy giá trị trong query đều phải viết dạng:

Query Drop Level Data dùng đúng công thức này để lấy 2 giá trị: level (→ logical_level) và source_3 (→ mapped_level).

D. Pipeline query — 31 bước

Query chạy dạng SCRIPT (nhiều câu lệnh, bấm Run 1 lần như thường): params, first_open_users, raw, user_last_activity, play_events_raw, play_events được lưu thành bảng tạm (TEMP TABLE, sắp sẵn theo user) — bảng event gốc chỉ quét 2 lần (first_open_users + raw); tín hiệu theo user (hoạt động cuối, nguồn user) gộp 1 lượt ở user_last_activity. Đã so bằng máy: 31/31 bước + câu SELECT cuối giống hệt bản 1 câu WITH (chỉ đổi cách chạy, không đổi phép tính). Bản 1 câu WITH trước đây bị BigQuery tính lại CTE mỗi lần gọi (473 stage, chạy > 15 phút). Kết quả là câu SELECT cuối — trong BigQuery bấm vào bước cuối để xem / tải kết quả.

3 nhánh chạy song song rồi gộp ở metrics_base: (a) đếm user (population), (b) phễu Drop during/after theo TỪNG USER, (c) phân loại Pass/Drop/Pending (logic cũ, không đổi). Segment (country/media/cohort) được gán 1 lần theo user ở đầu rồi đi theo mọi nhánh.

E. Toàn văn query

Query lấy . Các tham số khác (version, level tối đa, tách country/media, ngưỡng) sửa trực tiếp trong CTE params_input sau khi copy.

/* ============================================================
   DROP PERFORMANCE BY EXACT MAPPED LEVEL — v3

   MỤC TIÊU
   ------------------------------------------------------------
   Tìm actual level / source_3 tốt nhất tại mỗi logical level,
   kèm phễu Drop during / Drop after theo TỪNG USER.

   1 ROW =
   VERSION x LOGICAL_LEVEL x MAPPED_LEVEL
   x COUNTRY x MEDIA_SOURCE x FIRST_OPEN_COHORT


   THAM SỐ (sửa ở CTE params_input)
   ------------------------------------------------------------
   - report_versions: [] = MỌI version, hoặc liệt kê cụ thể
     vd ['1.9.2','1.10.4','1.11'].
   - Bản 1.0 → 1.5 CHƯA có gd_play_level / gd_complete_level => không
     có trong kết quả (query chỉ đọc event gd_*).
   - LUÔN LOẠI (kể cả khi liệt kê version):
       build test 0.* / 99.* (vd 0.0.1, 99.0),
       user có country = exclude_countries (mặc định 'Vietnam').
   - max_report_level: level cao nhất xuất ra (mặc định 1000).
     Tầng theo dõi tiến trình tự giữ tới max_report_level + 1
     để level cuối cùng vẫn nhìn thấy level kế tiếp.
   - split_by_country / split_by_media: TRUE = tách dòng theo chiều
     đó, FALSE = gộp thành 'ALL' (giảm mạnh số dòng + LOW_SAMPLE).
   - country_top_n: chỉ giữ tên N nước đông user nhất (mặc định 10),
     các nước còn lại gộp thành 'OTHER' => giảm mạnh số dòng.
     0 = giữ đủ mọi nước.
   - min_map_share: map chiếm < ngưỡng này (mặc định 0.01 = 1%)
     user của (version, level) gộp thành 1 map 'OTHER_MAPS'
     => bớt rất nhiều dòng map lẻ tẻ; tổng user giữ đúng.
     0 = giữ đủ mọi map.
   - start_date: ngày bắt đầu lấy data = ngày user ĐẦU TIÊN chơi 1.6.4
     (19/06/2026). Data lấy từ start_date -> data_end_date.
   - data_end_date = ngày chạy query - 2 ngày (tự tính).
   - cohort_bucket_days: độ dài 1 KỲ CÀI APP (mặc định 7 ngày, 1 = theo
     ngày). Mỗi user mới ghi đúng 1 kỳ (ngày first_open) => dashboard
     tự cộng các kỳ nằm trong A->B, KHÔNG cần chạy lại query khi đổi A->B.


   CHIỀU PHÂN KHÚC — GÁN THEO TỪNG USER (mỗi user đúng 1 giá trị)
   ------------------------------------------------------------
   - country: quốc gia xuất hiện NHIỀU NHẤT (đếm chính xác, hoà thì
     theo chữ cái) trong các event của user trong cửa sổ. Gán theo user (không theo event) để mỗi user chỉ
     thuộc 1 country => cộng nhiều country không đếm trùng.
     Ngoài top country_top_n => 'OTHER'.
   - media_source: Firebase user property 'media_source' (đã sanitize,
     native AnalyticServices.java:87 + backfill
     SyncPersistedUserAcquisitionProperty), dự phòng event
     'ua_source_<x>', không có => 'unknown'.
   - first_open_cohort: ngày BẮT ĐẦU kỳ cài app của user ('YYYY-MM-DD',
     kỳ tính từ start_date, dài cohort_bucket_days ngày) nếu first_open
     nằm trong [start_date, data_end_date]; ngược lại 'old' (user cũ).


   DROP LOGIC (phân loại Pass/Drop/Pending — KHÔNG đổi)
   ------------------------------------------------------------
   PASS = sau khi chơi logical level X, user về sau reach level > X.
   Follow user XUYÊN VERSION.
     1.9.2 L123 -> update -> 1.10.4 L124 => L123 = PASS,
     KHÔNG tính false Drop cho 1.9.2.

   OUTCOME OWNER = exact mapping CUỐI CÙNG user chơi tại frontier
   level X trước khi progress hoặc drop.

   CONFIRMED DROP = không có level cao hơn sau đó
                    VÀ activity cuối <= obs_end - inactive_days.
   PENDING        = không có level cao hơn
                    NHƯNG activity cuối nằm trong inactive_days cuối.

   DROP RATE = confirmed_drop / (passed + confirmed_drop).
   Pending KHÔNG vào denominator.


   PHỄU DROP DURING / AFTER (theo TỪNG USER, trên mỗi dòng)
   ------------------------------------------------------------
   Với mỗi user có chơi dòng R (version, level X, map, segment),
   lấy lần chơi ĐẦU TIÊN của user ở R làm mốc:
     progressed   = sau mốc đó user có gd_play_level ở level > X
     completed_ev = sau mốc đó user có gd_complete_level ở level X
                    (bất kể map/version — đúng tinh thần xuyên version)
     passed       = completed_ev OR progressed
   Chia user của R thành 4 nhóm KHÔNG GIAO NHAU:
     continued              = progressed
     drop_after  (confirmed) = completed_ev AND NOT progressed AND inactive
     drop_during (confirmed) = NOT passed AND inactive
     pending                = NOT progressed AND còn active
   => mapping_users = continued + drop_after + drop_during + pending
   => cả 4 luôn >= 0 (đều là tập con của cùng 1 nhóm user).
   "inactive" = activity cuối <= obs_end - inactive_days (giống
   Confirmed Drop), để user vừa chơi hôm qua không bị tính rớt oan.

   ⚠ gd_complete_level có thể bắn GIẢ từ GameplayTool (remote config
   game_tool) — nếu config đó bật rộng, completed_ev bị thổi phồng
   => drop_during bị đánh giá THẤP hơn thực tế.


   LƯU Ý SO KHỚP VỚI RPU
   ------------------------------------------------------------
   RPU không tách theo segment. Vì mỗi user chỉ thuộc đúng 1
   country / 1 media / 1 cohort, SUM các segment của cùng
   (version, level, map) KHỚP đúng số distinct user tổng.
   ============================================================ */



/* ============================================================
   CHẠY DẠNG SCRIPT (nhiều câu lệnh) — bấm Run 1 lần như bình thường.
   Bảng event gốc chỉ được quét 2 LẦN (first_open_users + raw) rồi lưu
   vào bảng tạm (TEMP TABLE); các bước sau đọc từ bảng tạm => nhanh hơn
   nhiều so với 1 câu WITH dài (BigQuery tính lại CTE mỗi lần được gọi).
   Tín hiệu theo user (hoạt động cuối, nguồn user) gộp 1 lần ở
   user_last_activity thay vì đọc lại toàn bộ raw nhiều lần. Kết quả = câu SELECT
   cuối cùng (tab Results của bước cuối).
   ============================================================ */


CREATE TEMP TABLE params AS
WITH params_input AS (

  SELECT

    /* Ngày bắt đầu lấy data = ngày user đầu tiên chơi 1.6.4 */
    DATE '2026-06-19'
      AS start_date,

    /* Độ dài 1 kỳ cài app (ngày). 1 = từng ngày (file rất lớn) */
    7
      AS cohort_bucket_days,

    /* Ngày cuối lấy data = hôm nay - 2 ngày */
    DATE_SUB(
      CURRENT_DATE('Asia/Ho_Chi_Minh'),
      INTERVAL 2 DAY
    ) AS data_end_date,

    2
      AS inactive_days,

    /* [] = mọi version. Build test 0.* / 99.* LUÔN bị loại (ở raw) */
    CAST([] AS ARRAY<STRING>)
      AS report_versions,

    /* LUÔN loại user có country (gán theo user) thuộc danh sách này */
    ['Vietnam']
      AS exclude_countries,

    /* Giữ tên N nước đông user nhất, còn lại => 'OTHER'. 0 = giữ đủ */
    10
      AS country_top_n,

    /* Map < tỷ lệ này user của (version, level) => 'OTHER_MAPS'. 0 = giữ đủ */
    0.01
      AS min_map_share,

    1000
      AS max_report_level,

    TRUE
      AS split_by_country,

    TRUE
      AS split_by_media,

    /* Reliability */
    100
      AS min_mapping_users,

    100
      AS min_resolved_users,

    0.50
      AS min_outcome_coverage,

    0.20
      AS max_pending_share

)
  SELECT

    *,

    start_date
      AS obs_start,

    data_end_date
      AS obs_end

  FROM params_input;


/* ============================================================
   0. FIRST OPEN — ngày cài app đầu tiên của user mới
      (first_open trong [start_date, data_end_date])
   ============================================================ */

CREATE TEMP TABLE first_open_users AS
  SELECT

    user_pseudo_id,

    MIN(
      PARSE_DATE('%Y%m%d', _TABLE_SUFFIX)
    ) AS first_open_date

  FROM
    `r2368g168-and-water-sort-3d.analytics_527788107.events_intraday_*`

  CROSS JOIN params p

  WHERE

    _TABLE_SUFFIX BETWEEN

      FORMAT_DATE(
        '%Y%m%d',
        p.start_date
      )

      AND

      FORMAT_DATE(
        '%Y%m%d',
        p.obs_end
      )

    AND event_name = 'first_open'

    /* first_open từ build test 0.* / 99.* không tính là New user */
    AND NOT REGEXP_CONTAINS(
      COALESCE(app_info.version, ''),
      r'^(0|99)\.'
    )

  GROUP BY
    user_pseudo_id;


/* ============================================================
   1. RAW SIGNALS — trong cửa sổ quan sát [obs_start, obs_end]

   gd_play_level      : progression / mapping
   gd_complete_level  : phễu Drop during/after
   session_start +
   user_engagement    : user có quay lại game không
   ua_source_*        : media_source dự phòng

   KHÔNG filter version ở đây (trừ build test 0.* / 99.*):
   phải follow user xuyên version.
   ============================================================ */

CREATE TEMP TABLE raw
CLUSTER BY user_pseudo_id AS
  SELECT

    PARSE_DATE(
      '%Y%m%d',
      _TABLE_SUFFIX
    ) AS data_date,

    event_timestamp,

    event_bundle_sequence_id,

    batch_event_index,

    user_pseudo_id,

    event_name,

    app_info.version
      AS version,

    COALESCE(
      geo.country,
      'UNKNOWN'
    ) AS event_country,

    (
      SELECT up.value.string_value
      FROM UNNEST(user_properties) up
      WHERE up.key = 'media_source'
      LIMIT 1
    ) AS up_media_source,


    CASE

      WHEN event_name IN ('gd_play_level', 'gd_complete_level')

      THEN (

        SELECT

          COALESCE(

            SAFE_CAST(
              ep.value.string_value
              AS INT64
            ),

            ep.value.int_value,

            SAFE_CAST(
              ep.value.float_value
              AS INT64
            ),

            SAFE_CAST(
              ep.value.double_value
              AS INT64
            )

          )

        FROM UNNEST(event_params) ep

        WHERE ep.key = 'level'

        LIMIT 1

      )

    END AS logical_level,


    CASE

      WHEN event_name IN ('gd_play_level', 'gd_complete_level')

      THEN (

        SELECT

          COALESCE(

            ep.value.string_value,

            CAST(
              ep.value.int_value
              AS STRING
            ),

            CAST(
              ep.value.float_value
              AS STRING
            ),

            CAST(
              ep.value.double_value
              AS STRING
            )

          )

        FROM UNNEST(event_params) ep

        WHERE ep.key = 'source_3'

        LIMIT 1

      )

    END AS mapped_level


  FROM
    `r2368g168-and-water-sort-3d.analytics_527788107.events_intraday_*`

  CROSS JOIN params p


  WHERE

    _TABLE_SUFFIX BETWEEN

      FORMAT_DATE(
        '%Y%m%d',
        p.obs_start
      )

      AND

      FORMAT_DATE(
        '%Y%m%d',
        p.obs_end
      )


    AND user_pseudo_id IS NOT NULL


    /* Build test 0.* / 99.* (vd 0.0.1, 99.0): bỏ MỌI event ngay từ
       đây — không chỉ ẩn khỏi output — để event build test không
       được tính là tiến trình / complete / hoạt động của user */
    AND NOT REGEXP_CONTAINS(
      COALESCE(app_info.version, ''),
      r'^(0|99)\.'
    )


    AND (

      event_name IN (
        'gd_play_level',
        'gd_complete_level',
        'session_start',
        'user_engagement'
      )

      OR STARTS_WITH(event_name, 'ua_source_')

    );


/* ============================================================
   1a. TÍN HIỆU / USER — trong cửa sổ quan sát, gộp 1 lượt trên raw:
       - last_activity_date: lần hoạt động cuối (im lặng ≥ N ngày)
       - media_up / media_ua: nguồn user (dùng ở user_segments)
   ============================================================ */

CREATE TEMP TABLE user_last_activity
CLUSTER BY user_pseudo_id AS
  SELECT

    user_pseudo_id,

    MAX(
      data_date
    ) AS last_activity_date,

    /* user property media_source MỚI NHẤT */
    ARRAY_AGG(
      NULLIF(up_media_source, '') IGNORE NULLS
      ORDER BY event_timestamp DESC
      LIMIT 1
    )[SAFE_OFFSET(0)] AS media_up,

    /* dự phòng: event ua_source_<x> ĐẦU TIÊN */
    ARRAY_AGG(
      IF(
        STARTS_WITH(event_name, 'ua_source_'),
        NULLIF(SUBSTR(event_name, 11), ''),
        NULL
      ) IGNORE NULLS
      ORDER BY event_timestamp
      LIMIT 1
    )[SAFE_OFFSET(0)] AS media_ua


  FROM raw


  GROUP BY
    user_pseudo_id;


CREATE TEMP TABLE play_events_raw
CLUSTER BY user_pseudo_id AS
WITH

/* ============================================================
   1b0. COUNTRY / USER — country xuất hiện NHIỀU NHẤT (đếm chính xác),
   hoà thì lấy theo thứ tự chữ cái để kết quả ổn định giữa các lần chạy.
   ============================================================ */

user_country_mode AS (

  SELECT

    user_pseudo_id,

    event_country
      AS country_mode


  FROM raw


  GROUP BY

    user_pseudo_id,
    event_country


  QUALIFY

    ROW_NUMBER() OVER (

      PARTITION BY
        user_pseudo_id

      ORDER BY
        COUNT(*) DESC,
        event_country

    ) = 1

),


/* ============================================================
   1b1. TOP COUNTRY — N nước đông user nhất (sau khi loại
   exclude_countries); hoà thì theo chữ cái cho ổn định.
   ============================================================ */

country_top AS (

  SELECT

    r.country_mode


  FROM (

    SELECT

      uc.country_mode,

      ROW_NUMBER() OVER (
        ORDER BY
          COUNT(*) DESC,
          uc.country_mode
      ) AS country_rank


    FROM user_country_mode uc

    CROSS JOIN params p


    WHERE
      uc.country_mode
        NOT IN UNNEST(p.exclude_countries)


    GROUP BY
      uc.country_mode

  ) r

  CROSS JOIN params p


  WHERE
    p.country_top_n <= 0
    OR r.country_rank <= p.country_top_n

),


/* ============================================================
   1b. USER SEGMENTS — 1 dòng / user, mỗi chiều đúng 1 giá trị
   ============================================================ */

user_segments AS (

  /* 1 dòng / user: user_country_mode và user_last_activity đều đúng
     1 dòng / user của raw; first_open_users DISTINCT; country_top
     1 dòng / nước => không nhân dòng, không cần GROUP BY lại raw. */

  SELECT

    uc.user_pseudo_id,

    IF(
      p.split_by_country,
      IF(
        ct.country_mode IS NOT NULL,
        uc.country_mode,
        'OTHER'
      ),
      'ALL'
    ) AS country,

    IF(
      p.split_by_media,
      COALESCE(
        ua.media_up,
        ua.media_ua,
        'unknown'
      ),
      'ALL'
    ) AS media_source,

    /* Kỳ cài app: ngày bắt đầu kỳ (tính từ start_date) — user cũ = 'old' */
    IF(
      fo.user_pseudo_id IS NOT NULL,
      CAST(
        DATE_ADD(
          p.start_date,
          INTERVAL DIV(
            DATE_DIFF(fo.first_open_date, p.start_date, DAY),
            p.cohort_bucket_days
          ) * p.cohort_bucket_days DAY
        ) AS STRING
      ),
      'old'
    ) AS first_open_cohort


  FROM user_country_mode uc

  CROSS JOIN params p

  JOIN user_last_activity ua
    ON ua.user_pseudo_id
       = uc.user_pseudo_id

  LEFT JOIN first_open_users fo
    ON fo.user_pseudo_id
       = uc.user_pseudo_id

  LEFT JOIN country_top ct
    ON ct.country_mode
       = uc.country_mode


  /* Loại cả user (mọi event của họ), không loại từng event,
     để tiến trình của user còn lại không bị cắt */
  WHERE
    uc.country_mode
      NOT IN UNNEST(p.exclude_countries)

)


/* ============================================================
   2. PLAY EVENTS — tới max_report_level + 1

   Giữ dư 1 level để level cuối vẫn thấy level kế tiếp
   (vd max = 1000 thì L1000 -> L1001 vẫn = PASS).
   ============================================================ */
  SELECT

    r.data_date,

    r.event_timestamp,

    r.event_bundle_sequence_id,

    r.batch_event_index,

    r.user_pseudo_id,

    r.version,

    r.logical_level,

    COALESCE(
      r.mapped_level,
      'UNKNOWN'
    ) AS mapped_level,

    s.country,

    s.media_source,

    s.first_open_cohort


  FROM raw r

  JOIN user_segments s
    ON s.user_pseudo_id
       = r.user_pseudo_id

  CROSS JOIN params p


  WHERE

    r.event_name = 'gd_play_level'

    AND r.version IS NOT NULL

    AND r.logical_level
        BETWEEN 1 AND p.max_report_level + 1;


CREATE TEMP TABLE play_events
CLUSTER BY user_pseudo_id AS
WITH

/* ============================================================
   2a. MAP LẺ — map chiếm < min_map_share user (distinct) của
   (version, level) gộp thành 'OTHER_MAPS'. Tính trên TOÀN BỘ
   user (mọi segment) để 1 map gộp hay không là như nhau ở mọi
   segment => cộng segment vẫn khớp. Tổng user không đổi.
   ============================================================ */

map_share AS (

  SELECT

    m.version,

    m.logical_level,

    m.mapped_level,

    SAFE_DIVIDE(
      m.map_users,
      l.level_users
    ) AS map_share


  FROM (

    SELECT
      version,
      logical_level,
      mapped_level,
      COUNT(DISTINCT user_pseudo_id) AS map_users
    FROM play_events_raw
    GROUP BY
      version,
      logical_level,
      mapped_level

  ) m

  JOIN (

    SELECT
      version,
      logical_level,
      COUNT(DISTINCT user_pseudo_id) AS level_users
    FROM play_events_raw
    GROUP BY
      version,
      logical_level

  ) l
    ON l.version = m.version
   AND l.logical_level = m.logical_level

)


  SELECT

    pe.* REPLACE (

      IF(
        ms.map_share < p.min_map_share,
        'OTHER_MAPS',
        pe.mapped_level
      ) AS mapped_level

    )


  FROM play_events_raw pe

  JOIN map_share ms
    ON ms.version = pe.version
   AND ms.logical_level = pe.logical_level
   AND ms.mapped_level = pe.mapped_level

  CROSS JOIN params p;


WITH

/* ============================================================
   2b. COMPLETE EVENTS — chỉ cần user + level + thời điểm
   ============================================================ */

complete_events AS (

  SELECT

    r.user_pseudo_id,

    r.logical_level,

    r.event_timestamp


  FROM raw r

  CROSS JOIN params p


  WHERE

    r.event_name = 'gd_complete_level'

    AND r.logical_level
        BETWEEN 1 AND p.max_report_level + 1

),


/* ============================================================
   2c. VERSION SCOPE — version nào được xuất ra
   ============================================================ */

version_scope AS (

  SELECT DISTINCT

    pe.version

  FROM play_events pe

  CROSS JOIN params p

  WHERE

    ARRAY_LENGTH(p.report_versions) = 0

    OR pe.version IN UNNEST(p.report_versions)

),


/* ============================================================
   3. LOGICAL LEVEL POPULATION
   ============================================================ */

logical_population AS (

  SELECT

    pe.version,

    pe.logical_level,

    pe.country,

    pe.media_source,

    pe.first_open_cohort,

    COUNT(
      DISTINCT pe.user_pseudo_id
    ) AS unique_users_logical_level


  FROM play_events pe

  JOIN version_scope vs
    ON vs.version = pe.version

  CROSS JOIN params p


  WHERE

    pe.logical_level
      BETWEEN 1 AND p.max_report_level


  GROUP BY

    pe.version,
    pe.logical_level,
    pe.country,
    pe.media_source,
    pe.first_open_cohort

),


/* ============================================================
   4. EXACT MAPPING EXPOSURE
   ============================================================ */

mapping_population AS (

  SELECT

    pe.version,

    pe.logical_level,

    pe.mapped_level,

    pe.country,

    pe.media_source,

    pe.first_open_cohort,

    COUNT(
      DISTINCT pe.user_pseudo_id
    ) AS mapping_users,

    COUNT(*)
      AS play_events_mapping


  FROM play_events pe

  JOIN version_scope vs
    ON vs.version = pe.version

  CROSS JOIN params p


  WHERE

    pe.logical_level
      BETWEEN 1 AND p.max_report_level


  GROUP BY

    pe.version,
    pe.logical_level,
    pe.mapped_level,
    pe.country,
    pe.media_source,
    pe.first_open_cohort

),


/* ============================================================
   5. MAPPING COUNT / LEVEL — số map khác nhau (không đếm segment)
   ============================================================ */

mapping_count AS (

  SELECT

    version,

    logical_level,

    COUNT(
      DISTINCT mapped_level
    ) AS mapping_count_at_level


  FROM mapping_population


  GROUP BY

    version,
    logical_level

),


/* ============================================================
   5b. PHỄU DURING/AFTER — timeline từng user

   future_max_play_level = level cao nhất user chơi SAU event này.
   ============================================================ */

play_future AS (

  SELECT

    pe.*,

    MAX(pe.logical_level) OVER (

      PARTITION BY
        pe.user_pseudo_id

      ORDER BY
        pe.event_timestamp,
        COALESCE(pe.event_bundle_sequence_id, 0),
        COALESCE(pe.batch_event_index, 0)

      ROWS BETWEEN
        1 FOLLOWING
        AND UNBOUNDED FOLLOWING

    ) AS future_max_play_level


  FROM play_events pe

),


/* Lần chơi ĐẦU TIÊN của mỗi user trên mỗi dòng R. */

exposure_first AS (

  SELECT

    pf.*


  FROM play_future pf

  JOIN version_scope vs
    ON vs.version = pf.version

  CROSS JOIN params p


  WHERE

    pf.logical_level
      BETWEEN 1 AND p.max_report_level


  QUALIFY

    ROW_NUMBER() OVER (

      PARTITION BY
        pf.user_pseudo_id,
        pf.version,
        pf.logical_level,
        pf.mapped_level

      ORDER BY
        pf.event_timestamp,
        COALESCE(pf.event_bundle_sequence_id, 0),
        COALESCE(pf.batch_event_index, 0)

    ) = 1

),


/* Lần complete CUỐI của user tại mỗi level — có complete sau
   lần chơi đầu <=> last_complete_ts >= first_play_ts. */

complete_last AS (

  SELECT

    user_pseudo_id,

    logical_level,

    MAX(event_timestamp)
      AS last_complete_ts


  FROM complete_events


  GROUP BY

    user_pseudo_id,
    logical_level

),


exposure_outcome AS (

  SELECT

    ef.version,

    ef.logical_level,

    ef.mapped_level,

    ef.country,

    ef.media_source,

    ef.first_open_cohort,

    ef.user_pseudo_id,

    COALESCE(
      ef.future_max_play_level > ef.logical_level,
      FALSE
    ) AS progressed,

    COALESCE(
      cl.last_complete_ts >= ef.event_timestamp,
      FALSE
    ) AS completed_ev,

    ula.last_activity_date
      <= DATE_SUB(
           p.obs_end,
           INTERVAL p.inactive_days DAY
         ) AS is_inactive


  FROM exposure_first ef

  LEFT JOIN complete_last cl
    ON cl.user_pseudo_id = ef.user_pseudo_id
   AND cl.logical_level = ef.logical_level

  JOIN user_last_activity ula
    ON ula.user_pseudo_id = ef.user_pseudo_id

  CROSS JOIN params p

),


funnel_summary AS (

  SELECT

    version,

    logical_level,

    mapped_level,

    country,

    media_source,

    first_open_cohort,

    COUNT(*)
      AS funnel_users,

    COUNTIF(
      completed_ev OR progressed
    ) AS level_completed_users,

    COUNTIF(
      progressed
    ) AS level_continued_users,

    COUNTIF(
      NOT completed_ev
      AND NOT progressed
      AND is_inactive
    ) AS drop_during_level_users,

    COUNTIF(
      completed_ev
      AND NOT progressed
      AND is_inactive
    ) AS drop_after_level_users,

    COUNTIF(
      NOT progressed
      AND NOT is_inactive
    ) AS drop_pending_level_users


  FROM exposure_outcome


  GROUP BY

    version,
    logical_level,
    mapped_level,
    country,
    media_source,
    first_open_cohort

),


/* ============================================================
   6. FIRST OBSERVED PLAY / USER — xử lý LEFT CENSORING

   Lần đầu thấy user đã ở L250 => không biết L250 là progression
   thật hay replay => không dùng chính level đó để assign Drop.
   Từ L251+ dùng bình thường.
   ============================================================ */

first_observed_play AS (

  SELECT

    user_pseudo_id,

    logical_level
      AS first_observed_level


  FROM play_events


  QUALIFY

    ROW_NUMBER() OVER (

      PARTITION BY
        user_pseudo_id

      ORDER BY
        event_timestamp,
        COALESCE(event_bundle_sequence_id, 0),
        COALESCE(batch_event_index, 0)

    ) = 1

),


/* ============================================================
   7. MAX LEVEL TRƯỚC MỖI LƯỢT CHƠI
   ============================================================ */

play_with_previous_max AS (

  SELECT

    p.*,

    MAX(
      logical_level
    ) OVER (

      PARTITION BY
        user_pseudo_id

      ORDER BY
        event_timestamp,
        COALESCE(event_bundle_sequence_id, 0),
        COALESCE(batch_event_index, 0)

      ROWS BETWEEN
        UNBOUNDED PRECEDING
        AND 1 PRECEDING

    ) AS previous_max_level


  FROM play_events p

),


/* ============================================================
   8. FRONTIER PLAY

   GIỮ: new max + retry tại current max.
   LOẠI: backward replay (300 -> replay 123).
   LEFT CENSOR: first observed level > 1 => bỏ chính level đó.
   ============================================================ */

frontier_play AS (

  SELECT

    p.*


  FROM play_with_previous_max p


  JOIN first_observed_play f

    ON f.user_pseudo_id
       = p.user_pseudo_id


  WHERE

    (
      p.previous_max_level IS NULL

      OR

      p.logical_level
        >= p.previous_max_level
    )


    AND NOT (

      f.first_observed_level > 1

      AND

      p.logical_level
        = f.first_observed_level

    )

),


/* ============================================================
   9. OUTCOME OWNER — mapping CUỐI CÙNG user chơi tại frontier X
   ============================================================ */

level_owner AS (

  SELECT

    user_pseudo_id,

    logical_level,

    version,

    mapped_level,

    country,

    media_source,

    first_open_cohort,

    event_timestamp
      AS owner_ts,

    /* Mang theo tie-breaker để LEAD phía sau có thứ tự xác định
       khi 2 owner trùng event_timestamp. */
    COALESCE(event_bundle_sequence_id, 0)
      AS owner_seq,

    COALESCE(batch_event_index, 0)
      AS owner_idx


  FROM frontier_play


  QUALIFY

    ROW_NUMBER() OVER (

      PARTITION BY

        user_pseudo_id,

        logical_level


      ORDER BY

        event_timestamp DESC,

        COALESCE(
          event_bundle_sequence_id,
          0
        ) DESC,

        COALESCE(
          batch_event_index,
          0
        ) DESC

    ) = 1

),


/* ============================================================
   10. NEXT FRONTIER LEVEL — có thể nằm ở version khác
   ============================================================ */

owner_with_next AS (

  SELECT

    *,

    LEAD(
      logical_level
    ) OVER (

      PARTITION BY
        user_pseudo_id

      ORDER BY
        owner_ts,
        owner_seq,
        owner_idx

    ) AS next_logical_level,


    LEAD(
      version
    ) OVER (

      PARTITION BY
        user_pseudo_id

      ORDER BY
        owner_ts,
        owner_seq,
        owner_idx

    ) AS next_version,


    LEAD(
      owner_ts
    ) OVER (

      PARTITION BY
        user_pseudo_id

      ORDER BY
        owner_ts,
        owner_seq,
        owner_idx

    ) AS next_level_ts


  FROM level_owner

),


/* ============================================================
   12. CLASSIFY OUTCOME
   ============================================================ */

classified AS (

  SELECT

    o.user_pseudo_id,

    o.version,

    o.logical_level,

    o.mapped_level,

    o.country,

    o.media_source,

    o.first_open_cohort,

    o.owner_ts,

    o.next_logical_level,

    o.next_version,

    o.next_level_ts,

    ula.last_activity_date,


    CASE
      WHEN o.next_logical_level > o.logical_level
      THEN 1
      ELSE 0
    END AS is_passed,


    CASE
      WHEN o.next_logical_level > o.logical_level
       AND o.next_version != o.version
      THEN 1
      ELSE 0
    END AS is_cross_version_pass,


    CASE
      WHEN o.next_logical_level IS NULL
       AND ula.last_activity_date
           <= DATE_SUB(
                p.obs_end,
                INTERVAL p.inactive_days DAY
              )
      THEN 1
      ELSE 0
    END AS is_confirmed_drop,


    CASE
      WHEN o.next_logical_level IS NULL
       AND ula.last_activity_date
           > DATE_SUB(
               p.obs_end,
               INTERVAL p.inactive_days DAY
             )
      THEN 1
      ELSE 0
    END AS is_pending


  FROM owner_with_next o


  JOIN user_last_activity ula

    ON ula.user_pseudo_id
       = o.user_pseudo_id


  JOIN version_scope vs

    ON vs.version = o.version


  CROSS JOIN params p


  WHERE

    o.logical_level
      BETWEEN 1 AND p.max_report_level

),


/* ============================================================
   13. OUTCOME SUMMARY
   ============================================================ */

outcome_summary AS (

  SELECT

    version,

    logical_level,

    mapped_level,

    country,

    media_source,

    first_open_cohort,

    COUNT(*)
      AS outcome_users_mapping,

    COUNTIF(
      is_passed = 1
    ) AS passed_users_mapping,

    COUNTIF(
      is_confirmed_drop = 1
    ) AS confirmed_drop_users_mapping,

    COUNTIF(
      is_pending = 1
    ) AS pending_users_mapping,

    COUNTIF(
      is_passed = 1
      OR
      is_confirmed_drop = 1
    ) AS resolved_users_mapping,

    COUNTIF(
      is_cross_version_pass = 1
    ) AS cross_version_pass_users


  FROM classified


  GROUP BY

    version,
    logical_level,
    mapped_level,
    country,
    media_source,
    first_open_cohort

),


/* ============================================================
   14. MERGE EXPOSURE + OUTCOME + PHỄU
   ============================================================ */

metrics_base AS (

  SELECT

    mp.version,

    mp.logical_level,

    mp.mapped_level,

    mp.country,

    mp.media_source,

    mp.first_open_cohort,

    lp.unique_users_logical_level,

    mp.mapping_users,

    SAFE_DIVIDE(
      mp.mapping_users,
      lp.unique_users_logical_level
    ) AS mapping_share,

    mc.mapping_count_at_level,

    mp.play_events_mapping,

    COALESCE(os.outcome_users_mapping, 0)
      AS outcome_users_mapping,

    COALESCE(os.passed_users_mapping, 0)
      AS passed_users_mapping,

    COALESCE(os.confirmed_drop_users_mapping, 0)
      AS confirmed_drop_users_mapping,

    COALESCE(os.pending_users_mapping, 0)
      AS pending_users_mapping,

    COALESCE(os.resolved_users_mapping, 0)
      AS resolved_users_mapping,

    COALESCE(os.cross_version_pass_users, 0)
      AS cross_version_pass_users,

    COALESCE(fs.level_completed_users, 0)
      AS level_completed_users,

    COALESCE(fs.level_continued_users, 0)
      AS level_continued_users,

    COALESCE(fs.drop_during_level_users, 0)
      AS drop_during_level_users,

    COALESCE(fs.drop_after_level_users, 0)
      AS drop_after_level_users,

    COALESCE(fs.drop_pending_level_users, 0)
      AS drop_pending_level_users


  FROM mapping_population mp


  JOIN logical_population lp

    ON lp.version = mp.version
   AND lp.logical_level = mp.logical_level
   AND lp.country = mp.country
   AND lp.media_source = mp.media_source
   AND lp.first_open_cohort = mp.first_open_cohort


  /* mapping_count không tách segment — nhiều dòng mp : 1 dòng mc, cố ý */
  JOIN mapping_count mc

    ON mc.version = mp.version
   AND mc.logical_level = mp.logical_level


  LEFT JOIN outcome_summary os

    ON os.version = mp.version
   AND os.logical_level = mp.logical_level
   AND os.mapped_level = mp.mapped_level
   AND os.country = mp.country
   AND os.media_source = mp.media_source
   AND os.first_open_cohort = mp.first_open_cohort


  /* Cùng grain với mp — 1-1 (funnel_users = mapping_users) */
  LEFT JOIN funnel_summary fs

    ON fs.version = mp.version
   AND fs.logical_level = mp.logical_level
   AND fs.mapped_level = mp.mapped_level
   AND fs.country = mp.country
   AND fs.media_source = mp.media_source
   AND fs.first_open_cohort = mp.first_open_cohort

),


/* ============================================================
   15. CALCULATED METRICS
   ============================================================ */

metrics AS (

  SELECT

    *,

    SAFE_DIVIDE(
      confirmed_drop_users_mapping,
      resolved_users_mapping
    ) AS drop_rate_mapping,

    SAFE_DIVIDE(
      pending_users_mapping,
      outcome_users_mapping
    ) AS pending_share,

    SAFE_DIVIDE(
      outcome_users_mapping,
      mapping_users
    ) AS outcome_coverage,

    SAFE_DIVIDE(
      cross_version_pass_users,
      passed_users_mapping
    ) AS cross_version_pass_share


  FROM metrics_base

),


/* ============================================================
   16. SAMPLE STATUS
   ============================================================ */

quality AS (

  SELECT

    m.*,

    CASE

      WHEN mapped_level = 'UNKNOWN'
        THEN 'UNKNOWN_MAPPING'

      WHEN mapping_users < p.min_mapping_users
        THEN 'LOW_SAMPLE'

      WHEN resolved_users_mapping < p.min_resolved_users
        THEN 'LOW_SAMPLE'

      WHEN COALESCE(outcome_coverage, 0) < p.min_outcome_coverage
        THEN 'LOW_COVERAGE'

      WHEN COALESCE(pending_share, 0) > p.max_pending_share
        THEN 'HIGH_PENDING'

      ELSE 'OK'

    END AS sample_status


  FROM metrics m

  CROSS JOIN params p

)


/* ============================================================
   FINAL OUTPUT
   ============================================================ */

SELECT

  q.version,

  q.logical_level,

  q.mapped_level
    AS mapped_level_thuc_te,

  q.country,

  q.media_source,

  q.first_open_cohort,

  /* EXPOSURE */
  q.unique_users_logical_level,

  q.mapping_users,

  ROUND(q.mapping_share * 100, 2)
    AS mapping_share_pct,

  q.mapping_count_at_level,

  /* OUTCOME (Pass/Drop/Pending) */
  q.confirmed_drop_users_mapping,

  q.resolved_users_mapping,

  ROUND(q.drop_rate_mapping * 100, 2)
    AS drop_rate_mapping_pct,

  ROUND(q.pending_share * 100, 2)
    AS pending_share_pct,

  ROUND(q.outcome_coverage * 100, 2)
    AS outcome_coverage_pct,

  /* PHỄU DURING / AFTER (theo từng user) */
  q.level_completed_users,

  q.level_continued_users,

  q.drop_during_level_users,

  q.drop_after_level_users,

  q.drop_pending_level_users,

  q.sample_status,

  /* CỬA SỔ ĐÃ DÙNG — để dashboard hiển thị đúng khoảng dữ liệu */
  p.start_date
    AS cohort_start_date,

  p.obs_end
    AS cohort_end_date,

  p.obs_start
    AS activity_start_date,

  p.obs_end
    AS activity_end_date,

  p.data_end_date,

  p.cohort_bucket_days


FROM quality q

CROSS JOIN params p


ORDER BY

  q.logical_level,

  q.version,

  q.mapped_level;

Dashboard này dùng để làm gì

Đo % Drop theo từng level, phễu Drop during / Drop after, Retention theo cohort — so sánh giữa các phiên bản app, lọc theo nguồn user, quốc gia, cohort. Dữ liệu gốc lấy từ query Drop Level Data (BigQuery) — mọi định nghĩa Pass/Drop/Pending bên dưới đều là logic THẬT đang chạy trong query đó, không phải suy diễn.

Mọi chữ, nhãn, ký hiệu trên Dashboard nghĩa là gì

Quy ước: thứ gì hiện trên tab Dashboard đều được định nghĩa ở tab này; tên cột / công thức của query nằm ở tab Data (luôn khớp lệnh query ở mục E).

KPI

Cột trong bảng

Nhãn ở dòng con (khi mở rộng 1 level)

Giá trị đặc biệt trong bộ lọc

Khác

Bộ lọc đang có / đề xuất thêm

3 event làm nền cho toàn bộ Drop Level Data

Đọc thẳng code Unity (r23-multiple-surface), không suy đoán — trạng thái CONFIRMED cho cả 3.

Đúng theo logic query Drop Level Data (toàn văn ở tab Data, mục E)

Liệt kê hết case đang được query xử lý — copy bảng này ra Sheet để bạn thêm case mới khi phát sinh. Mỗi nhóm xếp case từ bình thường tới phức tạp hơn (thường có update xen vào).

Phễu Drop during / Drop after — đếm theo TỪNG USER

Tách riêng khỏi Pass/Drop/Pending ở trên (không thay thế). Với mỗi user đã chơi 1 dòng (version × level X × map × segment), lấy lần chơi ĐẦU TIÊN của họ ở dòng đó làm mốc, rồi xếp user vào đúng 1 trong 4 nhóm — 4 nhóm không giao nhau, cộng lại = số user của map, nên không bao giờ âm (bản cũ lấy hiệu 2 phép đếm độc lập nên từng ra số âm). Dashboard hiện Drop during % và Drop after % = số user nhóm đó ÷ số user của map (4 nhóm cộng lại = 100%); số user gốc xem ở tooltip.

• 1 user chơi 2 map ở cùng level (vd trước/sau khi đổi config) được tính ở CẢ 2 dòng map — phễu đếm theo lượt tiếp xúc với map, khác Pass/Drop/Pending (chỉ lần chơi cuối có outcome).
• Replay level cũ (đã qua rồi quay lại chơi): user đó thường đã có level cao hơn sau mốc → Continued, không bị tính rớt.
• "Im lặng ≥ 2 ngày" dùng đúng mốc như Confirmed Drop (lần hoạt động cuối ≤ cuối khoảng hoạt động − 2 ngày).

Vì sao 1 logical_level luôn ra nhiều mapped_level khác nhau — đọc theo góc vận hành

Đây không phải nhiễu ngẫu nhiên. Engine quyết định "level X hiện nội dung nào" qua đúng 4 lớp ưu tiên, tra thẳng GameLevelLoader.cs. Hiểu đúng 4 lớp này thì mới biết khi nào là bạn (GD) chủ động đổi nội dung, khi nào là engine tự lặp do hết nội dung độc bản, và khi nào là nhiễu/bug.

4 lớp quyết định nội dung 1 level — theo đúng thứ tự engine kiểm tra

Nguồn: GameLevelLoader.cs:727-814 (region "Loop Level — Mapping") — GetMappedLevel(), ResolveContentLevel(), ResolveLoopEligibleLevel()

Định nghĩa: Map "đại diện" và mở rộng xem map khác

Vì ≤ 1.11 vẫn còn lớp 1 (Remote Config override) nên 1 logical_level (vd L202) có thể có nhiều mapped_level thật cùng lúc — dashboard xử lý bằng cách chọn 1 map "đại diện" mặc định, cho mở rộng xem hết khi cần:

Level có nhiều map thật sẽ thấy tag "+N map khác" ngay cạnh tên map ở dòng mặc định — đó là dấu hiệu để biết nên bấm mở rộng hay không. Query gộp mọi map chiếm < 1% user của level thành 1 dòng "Map lẻ (gộp)" (OTHER_MAPS, tham số min_map_share) — tính là 1 trong "+N", không bao giờ làm map đại diện.

Vận hành nên đọc data thế nào từ đây

Chưa xử lý trong query — nên biết trước khi tin số tuyệt đối