Weekly report — week ending Thu 1 Oct 2026

-- Same question restricted to installs 2026-09-25 .. 09-30, so v33 and v34 are compared on the same dates
-- Installs 2026-09-18 .. 2026-09-30, ex-China. Milestones measured within the first 24 h after first_open;
-- day-1 return = any event on the next calendar day (installs whose next day is 09-28, the collection gap, excluded).
WITH ev AS (
  SELECT user_pseudo_id u, event_name e, event_timestamp ts, PARSE_DATE('%Y%m%d', event_date) d,
    app_info.version v, geo.country country,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='level') lvl,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec') eng
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260918' AND '20261001'
),
inst AS (
  SELECT u, ARRAY_AGG(STRUCT(ts, d, v, country) ORDER BY ts LIMIT 1)[OFFSET(0)] i
  FROM ev WHERE e = 'first_open' GROUP BY u
),
per AS (
  SELECT inst.u, inst.i.v build, inst.i.d install_date,
    COUNTIF(ev.e = 'session_start' AND ev.ts <= inst.i.ts + 86400000000)                          sessions_24h,
    SUM(IF(ev.e = 'user_engagement' AND ev.ts <= inst.i.ts + 86400000000, ev.eng, 0)) / 60000    minutes_24h,
    MIN(IF(ev.e = 'level_start', ev.ts, NULL))                                                   first_level_start_ts,
    MAX(IF(ev.e = 'level_win' AND ev.ts <= inst.i.ts + 86400000000, ev.lvl - 1, 0))              stages_cleared_24h,
    MAX(IF(ev.e = 'level_start' AND ev.lvl = 1, 1, 0))                                           started_stage_1,
    COUNTIF(ev.e = 'app_exception' AND ev.ts <= inst.i.ts + 86400000000)                         crashes_24h,
    COUNTIF(ev.e = 'purchase_click' AND ev.ts <= inst.i.ts + 86400000000)                        shop_taps_24h,
    COUNTIF(ev.e = 'show_ad_click' AND ev.ts <= inst.i.ts + 86400000000)                         ad_taps_24h,
    MAX(IF(DATE_DIFF(ev.d, inst.i.d, DAY) = 1, 1, 0))                                            returned_d1
  FROM inst JOIN ev USING (u)
  WHERE inst.i.country != 'China' AND ev.ts >= inst.i.ts AND inst.i.d BETWEEN '2026-09-25' AND '2026-09-30'
  GROUP BY 1, 2, 3
)
SELECT build,
  COUNT(*)                                                                                  installs,
  ROUND(100 * AVG(IF(install_date <= '2026-09-30' AND install_date != '2026-09-27', returned_d1, NULL)), 1) d1_pct,
  ROUND(100 * AVG(started_stage_1), 1)                                                      pct_started_stage_1,
  ROUND(100 * COUNTIF(stages_cleared_24h >= 1) / COUNT(*), 1)                               pct_cleared_1_stage,
  ROUND(100 * COUNTIF(stages_cleared_24h >= 5) / COUNT(*), 1)                               pct_cleared_5_stages,
  ROUND(100 * COUNTIF(stages_cleared_24h >= 10) / COUNT(*), 1)                              pct_cleared_10_stages,
  ROUND(100 * COUNTIF(stages_cleared_24h >= 20) / COUNT(*), 1)                              pct_cleared_20_stages,
  ROUND(APPROX_QUANTILES(minutes_24h, 2)[OFFSET(1)], 1)                                     median_minutes_24h,
  ROUND(100 * COUNTIF(minutes_24h < 1) / COUNT(*), 1)                                       pct_under_1_min,
  ROUND(AVG(sessions_24h), 2)                                                               sessions_24h,
  ROUND(100 * COUNTIF(crashes_24h > 0) / COUNT(*), 2)                                       pct_crash_24h
FROM per
GROUP BY build
HAVING installs >= 50
ORDER BY build

-- New installs since 2026-09-11 by the build they installed on (ex-China):
-- day-1 return, first-day play time and progress, fatal crashes
WITH x AS (
  SELECT user_pseudo_id u, event_name ev, event_timestamp ts,
    PARSE_DATE('%Y%m%d', event_date) d, app_info.version v, geo.country country,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec') eng,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='level') lvl,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='fatal') fatal
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260911' AND '20261001'
),
inst AS (
  SELECT u, ARRAY_AGG(STRUCT(d, v, country) ORDER BY ts LIMIT 1)[OFFSET(0)] i
  FROM x WHERE ev = 'first_open' GROUP BY u
),
per AS (
  SELECT inst.u, inst.i.v install_ver, inst.i.d install_date,
    MAX(IF(DATE_DIFF(ev.d, inst.i.d, DAY) = 1, 1, 0))                                    d1,
    SUM(IF(ev.d = inst.i.d AND ev.ev = 'user_engagement', ev.eng, 0)) / 60000            d0_minutes,
    MAX(IF(ev.d = inst.i.d AND ev.ev = 'level_win', ev.lvl - 1, 0))                      d0_stages_cleared,
    COUNTIF(ev.ev = 'app_exception' AND ev.fatal = 1 AND DATE_DIFF(ev.d, inst.i.d, DAY) <= 1) crashes_d0d1
  FROM inst JOIN x AS ev USING (u)
  WHERE inst.i.country != 'China'
  GROUP BY 1, 2, 3
)
SELECT install_ver,
  MIN(install_date) first_install, MAX(install_date) last_install,
  COUNT(*) installs,
  -- D1 only for installs whose D1 is observable and not the 09-28 data gap
  COUNTIF(install_date <= '2026-09-30' AND install_date != '2026-09-27')                       d1_eligible,
  ROUND(100 * AVG(IF(install_date <= '2026-09-30' AND install_date != '2026-09-27', d1, NULL)), 1) d1_pct,
  ROUND(APPROX_QUANTILES(d0_minutes, 2)[OFFSET(1)], 1)                                         median_d0_minutes,
  ROUND(100 * COUNTIF(d0_minutes < 1) / COUNT(*), 1)                                           pct_under_1_min_d0,
  ROUND(APPROX_QUANTILES(d0_stages_cleared, 2)[OFFSET(1)], 0)                                  median_d0_stages,
  ROUND(100 * COUNTIF(d0_stages_cleared = 0) / COUNT(*), 1)                                    pct_cleared_no_stage_d0,
  COUNTIF(crashes_d0d1 > 0)                                                                     installs_with_fatal_crash_d0d1,
  ROUND(100 * COUNTIF(crashes_d0d1 > 0) / COUNT(*), 2)                                          pct_with_fatal_crash
FROM per
GROUP BY install_ver
HAVING installs >= 50
ORDER BY first_install

WITH ev AS (
  SELECT user_pseudo_id u, PARSE_DATE('%Y%m%d', event_date) d, event_name e, event_timestamp ts,
    geo.country country, app_info.version v, event_value_in_usd usd,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key='product_id'),
             (SELECT value.string_value FROM UNNEST(event_params) WHERE key='position')) sku,
    (SELECT COALESCE(value.double_value, value.float_value, CAST(value.int_value AS FLOAT64))
       FROM UNNEST(event_params) WHERE key='value') val,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_format') ad_format,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_type') ad_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status') status,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') err,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec') eng,
    (SELECT value.string_value FROM UNNEST(user_properties) WHERE key='appsflyer_id') af
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX >= '20260801'
)
SELECT u, d,
  ANY_VALUE(country) country,
  ARRAY_AGG(v IGNORE NULLS ORDER BY ts DESC LIMIT 1)[SAFE_OFFSET(0)] v,
  MAX(af) af,
  COUNTIF(e='first_open') first_opens,
  SUM(IF(e='user_engagement', eng, 0)) / 60000 eng_min,
  SUM(IF(e='in_app_purchase', usd, 0)) iap_usd,
  COUNTIF(e='in_app_purchase') iap_buys,
  SUM(IF(e='in_app_purchase' AND sku LIKE '%skip.ad%', usd, 0)) noads_usd,
  COUNTIF(e='in_app_purchase' AND sku LIKE '%skip.ad%') noads_buys,
  SUM(IF(e='in_app_purchase' AND sku LIKE '%skip.ad%.sale', usd, 0)) sale_usd,
  COUNTIF(e='in_app_purchase' AND sku LIKE '%skip.ad%.sale') sale_buys,
  COUNTIF(e='purchase_click' AND sku LIKE '%skip.ad%') noads_taps,
  COUNTIF(e='purchase_click') shop_taps,
  SUM(IF(e='ad_impression_MAX', COALESCE(val, 0), 0)) ad_usd,
  COUNTIF(e='ad_impression_MAX') ad_imps,
  COUNTIF(e='ad_impression_MAX' AND UPPER(ad_format) LIKE '%REWARD%') rew_imps,
  COUNTIF(e='ad_impression_MAX' AND UPPER(ad_format) LIKE '%INTER%' AND UPPER(ad_format) NOT LIKE '%REWARD%') inter_imps,
  COUNTIF(e='show_ad_click') ad_clicks,
  COUNTIF(e='ad_reward') ad_rewards,
  COUNTIF(e='ad_request_status' AND ad_type='rewarded') rew_req,
  COUNTIF(e='ad_request_status' AND ad_type='rewarded' AND status='success') rew_ok,
  COUNTIF(e='ad_request_status' AND ad_type='inter') inter_req,
  COUNTIF(e='ad_request_status' AND ad_type='inter' AND status='success') inter_ok,
  COUNTIF(e='ad_request_status' AND status='fail' AND err LIKE '%applvn.com%') dns_fail,
  COUNTIF(e='ad_request_status' AND status='fail' AND (err LIKE '%No Fill%' OR err LIKE '%no eligible ads%')) nofill_fail,
  COUNTIF(e='ad_rewarded_show_failed') rew_show_fail
FROM ev GROUP BY u, d

-- The players behind the late-September ROAS spikes, matched from AppsFlyer ID to GA4: every purchase and
-- daily ad revenue. AppsFlyer IDs come from dashboard/afdata_local/af_cohort_*_rows.csv.
WITH ids AS (
  SELECT * FROM UNNEST([
    STRUCT('1790634583457-2090850799405754834' AS af_id, 'Sweden · GA ROAS Global · installed 28 Sep' AS label),
    STRUCT('1790717823534-6603760123075115430', 'UAE · GA ROAS Global · installed 29 Sep'),
    STRUCT('1790223402902-2556825812026972686', 'US · GA ROAS Tier1 15-09 · installed 24 Sep'),
    STRUCT('1790563492688-5948370201236570453', 'US · GA ROAS Tier1 15-09 · installed 28 Sep')])
),
ev AS (
  SELECT user_pseudo_id u, event_name e, event_timestamp ts, PARSE_DATE('%Y%m%d', event_date) d,
    geo.country country, app_info.version v, event_value_in_usd usd,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='product_id') sku,
    (SELECT COALESCE(value.double_value, value.float_value, CAST(value.int_value AS FLOAT64))
       FROM UNNEST(event_params) WHERE key='value') ad_value,
    (SELECT value.string_value FROM UNNEST(user_properties) WHERE key='appsflyer_id') af
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX >= '20260923'
),
users AS (SELECT DISTINCT ev.u, ids.label, ids.af_id FROM ev JOIN ids ON ev.af = ids.af_id)
SELECT users.label, users.af_id, ev.u AS user_pseudo_id, ev.e AS event_name,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP_MICROS(ev.ts)) AS event_time_utc,
  ev.country, ev.v AS version, ev.sku, ROUND(ev.usd, 2) AS usd, ROUND(ev.ad_value, 4) AS ad_value
FROM users JOIN ev USING (u)
WHERE ev.e IN ('first_open', 'in_app_purchase', 'ad_impression_MAX')
ORDER BY users.label, ev.ts

-- One row per non-China user active during the Lucky Machine window (2026-09-16 .. 09-25),
-- with event activity, spend before/during the event and activity after it.
WITH x AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) d, user_pseudo_id u, event_name ev, event_timestamp ts,
    app_info.version v, geo.country country, event_value_in_usd usd,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key='position'),
             (SELECT value.string_value FROM UNNEST(event_params) WHERE key='product_id')) pos,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='currency') cur,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_placement') placement,
    (SELECT COALESCE(CAST(value.int_value AS FLOAT64), value.double_value) FROM UNNEST(event_params) WHERE key='volume') vol
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260902' AND '20261001'
),
active AS (
  SELECT u FROM x WHERE d BETWEEN '2026-09-16' AND '2026-09-25' GROUP BY u
  HAVING LOGICAL_AND(country != 'China')
)
SELECT x.u,
  ARRAY_AGG(IF(d BETWEEN '2026-09-16' AND '2026-09-25', v, NULL) IGNORE NULLS ORDER BY ts DESC LIMIT 1)[SAFE_OFFSET(0)] event_version,
  ANY_VALUE(country) country,
  MIN(IF(ev = 'first_open', d, NULL))                                                         install_date,
  COUNT(DISTINCT IF(d BETWEEN '2026-09-16' AND '2026-09-25', d, NULL))                        event_days_active,
  COUNTIF(ev = 'spend' AND pos = 'play_event_lucky_machine')                                  spins,
  COUNT(DISTINCT IF(ev = 'spend' AND pos = 'play_event_lucky_machine', d, NULL))              spin_days,
  SUM(IF(ev = 'earn' AND cur = 'EVENT_LUCKY_MACHINE_ITEM' AND pos = 'check_in_event', vol, 0))           tickets_check_in,
  SUM(IF(ev = 'earn' AND cur = 'EVENT_LUCKY_MACHINE_ITEM' AND pos = 'ads_lucky_machine_ticket', vol, 0)) tickets_ads,
  COUNTIF(ev = 'earn' AND cur = 'EVENT_LUCKY_MACHINE_ITEM' AND pos = 'ads_lucky_machine_ticket')         ad_ticket_grants,
  SUM(IF(ev = 'earn' AND cur = 'EVENT_LUCKY_MACHINE_ITEM' AND pos = 'play_event_lucky_machine', vol, 0)) tickets_from_spins,
  SUM(IF(ev = 'earn' AND cur = 'EVENT_LUCKY_MACHINE_ITEM' AND pos LIKE '%lucky.machine%', vol, 0))       tickets_purchased,
  COUNTIF(ev = 'ad_impression_MAX' AND placement = 'rewareded_mini_event')                    event_ad_impressions,
  COUNTIF(ev = 'ad_impression_MAX' AND d BETWEEN '2026-09-16' AND '2026-09-25')               all_ad_impressions_in_window,
  COUNTIF(ev = 'show_ad_click' AND d BETWEEN '2026-09-16' AND '2026-09-25')                   watch_ad_taps_in_window,
  MAX(IF(ev LIKE 'event_slot_claim_stars_%', SAFE_CAST(REGEXP_EXTRACT(ev, '([0-9]+)$') AS INT64), NULL)) max_star_slot,
  COUNTIF(ev = 'purchase_click' AND pos LIKE '%lucky.machine%')                               pack_taps,
  COUNTIF(ev = 'in_app_purchase' AND pos LIKE '%lucky.machine%')                              pack_purchases,
  SUM(IF(ev = 'in_app_purchase' AND pos LIKE '%lucky.machine%', usd, 0))                      pack_usd,
  SUM(IF(ev = 'in_app_purchase' AND d BETWEEN '2026-09-02' AND '2026-09-15', usd, 0))         iap_before_usd,
  SUM(IF(ev = 'in_app_purchase' AND d BETWEEN '2026-09-16' AND '2026-09-25', usd, 0))         iap_during_usd,
  COUNT(DISTINCT IF(d BETWEEN '2026-09-26' AND '2026-10-01', d, NULL))                        days_active_after
FROM x JOIN active USING (u)
GROUP BY x.u

-- Lucky Machine ad tickets, time-free check, 2026-09-16 .. 2026-09-25: per user × day, tickets granted from ads
-- vs event ads actually watched (impressions on rewareded_mini_event and the game's own ad_rewarded_rewareded_mini_event).
WITH x AS (
  SELECT user_pseudo_id u, event_date d, event_name ev, event_timestamp ts, geo.country = 'China' cn,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='position') pos,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_placement') placement
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260916' AND '20260925'
    AND event_name IN ('earn', 'ad_impression_MAX', 'ad_rewarded_rewareded_mini_event')
),
c AS (
  SELECT u, d, LOGICAL_OR(cn) cn,
    COUNTIF(ev = 'earn' AND pos = 'ads_lucky_machine_ticket')                       tickets,
    COUNTIF(ev = 'ad_impression_MAX' AND placement = 'rewareded_mini_event')        event_ads,
    COUNTIF(ev = 'ad_rewarded_rewareded_mini_event')                                event_ad_rewards,
    (MAX(IF(ev = 'earn' AND pos = 'ads_lucky_machine_ticket', ts, NULL))
     - MIN(IF(ev = 'earn' AND pos = 'ads_lucky_machine_ticket', ts, NULL))) / 1e6   span_s
  FROM x GROUP BY 1, 2
)
SELECT IF(cn, 'China', 'rest of world') region, tickets AS tickets_per_user_day,
  COUNT(*) user_days, SUM(tickets) tickets, SUM(event_ads) event_ads_watched, SUM(event_ad_rewards) event_ad_rewards,
  ROUND(APPROX_QUANTILES(span_s, 2)[OFFSET(1)], 1) median_seconds_first_to_last_ticket
FROM c WHERE tickets > 0
GROUP BY 1, 2 ORDER BY 1, 2

-- For every Lucky Machine ad-ticket grant: what ad activity did the same user log from 60 s before to 30 s after it?
-- (MAX logs ad_impression_MAX 0.02-17 s AFTER the reward callback, so the window must extend past the grant.)
-- Distinguishes "ad shown but impression not logged" from "ticket granted with no ad at all".
WITH x AS (
  SELECT user_pseudo_id u, event_name ev, event_timestamp ts, geo.country = 'China' cn, app_info.version v,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='position') pos,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_type') ad_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status') status,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') err,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_placement'),
             (SELECT value.string_value FROM UNNEST(event_params) WHERE key='placement')) placement
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260916' AND '20260925'
    AND event_name IN ('earn', 'show_ad_click', 'ad_request', 'ad_request_status', 'ad_show',
                       'ad_impression_MAX', 'ad_reward', 'ad_rewarded_show_failed', 'ad_rewarded_rewareded_mini_event')
),
grants AS (SELECT u, ts, cn, v FROM x WHERE ev = 'earn' AND pos = 'ads_lucky_machine_ticket'),
ctx AS (
  SELECT g.u, g.ts, g.cn, g.v,
    COUNTIF(a.ev = 'show_ad_click')                                         taps,
    COUNTIF(a.ev = 'ad_request_status' AND a.ad_type = 'rewarded' AND a.status = 'success') rewarded_loaded,
    COUNTIF(a.ev = 'ad_request_status' AND a.status = 'fail')               load_failures,
    COUNTIF(a.ev = 'ad_show')                                               ad_shows,
    COUNTIF(a.ev = 'ad_impression_MAX')                                     impressions,
    COUNTIF(a.ev = 'ad_impression_MAX' AND a.placement = 'rewareded_mini_event') event_impressions,
    COUNTIF(a.ev = 'ad_reward')                                             ad_rewards,
    COUNTIF(a.ev = 'ad_rewarded_rewareded_mini_event')                      mini_event_ad_logs
  FROM grants g LEFT JOIN x a
    ON a.u = g.u AND a.ev != 'earn' AND a.ts BETWEEN g.ts - 60000000 AND g.ts + 30000000
  GROUP BY 1, 2, 3, 4
)
SELECT IF(cn, 'China', 'rest of world') region,
  CASE
    WHEN event_impressions > 0 THEN '1 event ad shown (impression on rewareded_mini_event)'
    WHEN impressions > 0       THEN '2 an ad was shown, but on another placement'
    WHEN ad_shows > 0 OR ad_rewards > 0 THEN '3 ad show/reward logged, no impression'
    WHEN load_failures > 0     THEN '4 no ad: load failed, ticket granted anyway'
    WHEN taps > 0              THEN '5 no ad: watch-ad tap only'
    ELSE                            '6 no ad activity from 60 s before to 30 s after'
  END AS what_preceded_the_grant,
  COUNT(*) grants, COUNT(DISTINCT u) users
FROM ctx GROUP BY 1, 2 ORDER BY 1, 2

-- Ad-ticket grants per user per day and the spacing between consecutive grants (all countries)
WITH g AS (
  SELECT user_pseudo_id u, PARSE_DATE('%Y%m%d', event_date) d, event_timestamp ts, geo.country = 'China' cn
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260916' AND '20260925' AND event_name = 'earn'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key='position') = 'ads_lucky_machine_ticket'
),
s AS (SELECT *, (ts - LAG(ts) OVER (PARTITION BY u ORDER BY ts)) / 1e6 AS gap_s FROM g)
SELECT IF(cn, 'China', 'rest of world') region,
  COUNT(*) grants, COUNT(DISTINCT u) users, COUNT(DISTINCT CONCAT(u, CAST(d AS STRING))) user_days,
  ROUND(COUNT(*) / COUNT(DISTINCT CONCAT(u, CAST(d AS STRING))), 2) grants_per_user_day,
  APPROX_QUANTILES(gap_s, 4) gap_seconds_quartiles,
  COUNTIF(gap_s < 15) grants_within_15s_of_previous
FROM s GROUP BY 1

-- Lucky Machine (slot machine) event, daily, 2026-09-14 .. 2026-09-27. China reported separately.
WITH x AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) d, user_pseudo_id u, event_name ev, geo.country = 'China' cn,
    event_value_in_usd usd,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key='position'),
             (SELECT value.string_value FROM UNNEST(event_params) WHERE key='product_id')) pos,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='currency') cur,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_placement') placement,
    (SELECT COALESCE(CAST(value.int_value AS FLOAT64), value.double_value) FROM UNNEST(event_params) WHERE key='volume') vol
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260914' AND '20260927'
)
SELECT d,
  COUNT(DISTINCT IF(NOT cn, u, NULL))                                                        dau_ex_cn,
  COUNT(DISTINCT IF(NOT cn AND cur='EVENT_LUCKY_MACHINE_ITEM', u, NULL))                     ticket_holders,
  COUNT(DISTINCT IF(NOT cn AND ev='spend' AND pos='play_event_lucky_machine', u, NULL))      spinners,
  COUNTIF(NOT cn AND ev='spend' AND pos='play_event_lucky_machine')                          spins,
  SUM(IF(NOT cn AND ev='earn' AND cur='EVENT_LUCKY_MACHINE_ITEM' AND pos='check_in_event', vol, 0))           tickets_check_in,
  SUM(IF(NOT cn AND ev='earn' AND cur='EVENT_LUCKY_MACHINE_ITEM' AND pos='ads_lucky_machine_ticket', vol, 0)) tickets_ads,
  SUM(IF(NOT cn AND ev='earn' AND cur='EVENT_LUCKY_MACHINE_ITEM' AND pos='play_event_lucky_machine', vol, 0)) tickets_from_spins,
  SUM(IF(NOT cn AND ev='earn' AND cur='EVENT_LUCKY_MACHINE_ITEM' AND pos LIKE '%lucky.machine%', vol, 0))     tickets_purchased,
  COUNTIF(NOT cn AND ev='ad_impression_MAX' AND placement='rewareded_mini_event')            event_ad_impressions,
  COUNTIF(NOT cn AND ev='event_slot_claim_stars_0')                                          star_slot0_claims,
  COUNTIF(NOT cn AND ev='purchase_click' AND pos LIKE '%lucky.machine%')                     pack_taps,
  COUNTIF(NOT cn AND ev='in_app_purchase' AND pos LIKE '%lucky.machine%')                    pack_purchases,
  ROUND(SUM(IF(NOT cn AND ev='in_app_purchase' AND pos LIKE '%lucky.machine%', usd, 0)), 2)  pack_usd,
  ROUND(SUM(IF(NOT cn AND ev='in_app_purchase', usd, 0)), 2)                                 all_iap_usd,
  COUNT(DISTINCT IF(cn AND ev='spend' AND pos='play_event_lucky_machine', u, NULL))          china_spinners
FROM x GROUP BY d ORDER BY d

-- Lucky Machine ticket grants per user per day (all countries), with the ad impressions the same user
-- generated that day. Check-in is expected at most once per day; an ad ticket is expected to follow an ad.
WITH x AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) d, user_pseudo_id u, event_name ev, event_timestamp ts,
    app_info.version v, geo.country = 'China' cn,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='position') pos,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='currency') cur,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_placement') placement
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260916' AND '20260925'
    AND event_name IN ('earn', 'ad_impression_MAX', 'show_ad_click', 'ad_reward', 'session_start')
)
SELECT d, u, ANY_VALUE(v) version, LOGICAL_OR(cn) china,
  COUNTIF(ev = 'earn' AND cur = 'EVENT_LUCKY_MACHINE_ITEM' AND pos = 'check_in_event')           check_in_grants,
  COUNTIF(ev = 'earn' AND cur = 'EVENT_LUCKY_MACHINE_ITEM' AND pos = 'ads_lucky_machine_ticket') ad_ticket_grants,
  COUNTIF(ev = 'ad_impression_MAX' AND placement = 'rewareded_mini_event')                       event_ad_impressions,
  COUNTIF(ev = 'ad_impression_MAX')                                                               all_ad_impressions,
  COUNTIF(ev = 'ad_reward')                                                                       ad_rewards,
  COUNTIF(ev = 'session_start')                                                                   sessions,
  -- seconds between consecutive check-in grants for this user-day
  MIN(IF(ev = 'earn' AND pos = 'check_in_event', ts, NULL))                                       first_check_in_ts,
  MAX(IF(ev = 'earn' AND pos = 'check_in_event', ts, NULL))                                       last_check_in_ts
FROM x
GROUP BY d, u
HAVING check_in_grants > 0 OR ad_ticket_grants > 0

-- What the Lucky Machine SKUs grant: earn events whose position is the SKU id
SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key='position') sku,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='currency') currency,
  COUNT(*) grants, COUNT(DISTINCT user_pseudo_id) users,
  APPROX_QUANTILES((SELECT COALESCE(CAST(value.int_value AS FLOAT64), value.double_value)
                    FROM UNNEST(event_params) WHERE key='volume'), 2)[OFFSET(1)] median_volume
FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
WHERE _TABLE_SUFFIX BETWEEN '20260916' AND '20260926' AND event_name = 'earn'
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key='position') LIKE '%lucky.machine%'
GROUP BY 1, 2 ORDER BY grants DESC

-- Proof of the reward ↔ impression pairing, 2026-09-18 .. 2026-10-01, ex-China.
-- No shared id exists, so pair on user + placement + time. For every reward (ad_rewarded_<placement>) find the
-- nearest REWARDED impression on the same placement AFTER it and BEFORE it, searching ±1 hour, and bucket the gaps.
WITH x AS (
  SELECT user_pseudo_id u, event_name ev, event_timestamp ts,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_placement') placement,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_format') fmt
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260918' AND '20261001' AND geo.country != 'China'
    AND (event_name LIKE 'ad\\_rewarded\\_%' OR event_name = 'ad_impression_MAX')
),
r AS (SELECT u, ts, REGEXP_REPLACE(ev, r'^ad_rewarded_', '') placement
      FROM x WHERE ev LIKE 'ad\\_rewarded\\_%' AND ev != 'ad_rewarded_show_failed'),
imp AS (SELECT u, ts, placement FROM x WHERE ev = 'ad_impression_MAX' AND fmt = 'REWARDED'),
pair AS (
  SELECT r.u, r.ts, r.placement,
    MIN(IF(i.ts >= r.ts, (i.ts - r.ts) / 1e6, NULL)) next_s,     -- seconds to the next same-placement impression
    MIN(IF(i.ts <  r.ts, (r.ts - i.ts) / 1e6, NULL)) prev_s      -- seconds since the previous one
  FROM r LEFT JOIN imp i ON i.u = r.u AND i.placement = r.placement AND ABS(i.ts - r.ts) <= 3600000000
  GROUP BY 1, 2, 3
)
SELECT
  CASE
    WHEN next_s IS NULL      THEN '9 no impression within 1 h after'
    WHEN next_s < 1          THEN '1 next impression 0–1 s after'
    WHEN next_s < 5          THEN '2 1–5 s after'
    WHEN next_s < 17         THEN '3 5–17 s after'
    WHEN next_s < 60         THEN '4 17–60 s after'
    WHEN next_s < 600        THEN '5 1–10 min after'
    ELSE                          '6 10–60 min after'
  END AS gap_bucket,
  COUNT(*) rewards,
  ROUND(MAX(next_s), 3) max_gap_s,
  COUNTIF(prev_s < 17) also_impression_within_17s_before
FROM pair GROUP BY 1 ORDER BY 1

-- Time-free check: per user × placement × day, number of rewards vs number of rewarded impressions.
-- If every reward has exactly one impression, the counts are equal regardless of timestamps.
WITH x AS (
  SELECT user_pseudo_id u, event_date d, event_name ev,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_placement') placement,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_format') fmt
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260918' AND '20261001' AND geo.country != 'China'
    AND (event_name LIKE 'ad\\_rewarded\\_%' OR event_name = 'ad_impression_MAX')
),
c AS (
  SELECT u, d, COALESCE(IF(ev = 'ad_impression_MAX', placement, NULL), REGEXP_REPLACE(ev, r'^ad_rewarded_', '')) placement,
    COUNTIF(ev LIKE 'ad\\_rewarded\\_%' AND ev != 'ad_rewarded_show_failed') rewards,
    COUNTIF(ev = 'ad_impression_MAX' AND fmt = 'REWARDED') impressions
  FROM x GROUP BY 1, 2, 3
)
SELECT
  CASE WHEN rewards = impressions THEN '1 equal'
       WHEN rewards > impressions THEN '2 more rewards than impressions'
       ELSE '3 more impressions than rewards' END AS comparison,
  COUNT(*) user_placement_days, SUM(rewards) rewards, SUM(impressions) impressions,
  SUM(GREATEST(rewards - impressions, 0)) excess_rewards
FROM c WHERE rewards > 0 OR impressions > 0
GROUP BY 1 ORDER BY 1

-- Rewarded-ad grants without an ad: one row per reward (ad_rewarded_<placement>), 2026-09-18 .. 2026-10-01, ex-China.
-- For each reward, the same user's ad activity from 120 s before to 30 s after it (MAX logs the impression
-- 0.02-17 s AFTER the reward callback, median 0.25 s), plus build, country and whether the user had
-- bought a no-ads SKU or membership before the reward (lifetime, from the whole export).
WITH x AS (
  SELECT user_pseudo_id u, event_name ev, event_timestamp ts, app_info.version v, geo.country country,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_format') fmt,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_placement'),
             (SELECT value.string_value FROM UNNEST(event_params) WHERE key='placement')) placement,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_source') ad_source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_type') ad_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status') status
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260918' AND '20261001' AND geo.country != 'China'
    AND (event_name LIKE 'ad\\_rewarded\\_%' OR event_name IN ('ad_impression_MAX', 'ad_show', 'show_ad_click',
         'ad_request_status', 'ad_reward'))
),
owners AS (   -- lifetime: first purchase time of each entitlement type
  SELECT user_pseudo_id u,
    MIN(IF((SELECT value.string_value FROM UNNEST(event_params) WHERE key='product_id') LIKE '%skip.ad%', event_timestamp, NULL)) noads_ts,
    MIN(IF((SELECT value.string_value FROM UNNEST(event_params) WHERE key='product_id') LIKE '%membership%', event_timestamp, NULL)) membership_ts
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE event_name = 'in_app_purchase'
  GROUP BY u
),
rewards AS (
  SELECT u, ts, v, country, REGEXP_REPLACE(ev, r'^ad_rewarded_', '') placement
  FROM x WHERE ev LIKE 'ad\\_rewarded\\_%' AND ev != 'ad_rewarded_show_failed'
),
ctx AS (
  SELECT r.u, r.ts, r.v, r.country, r.placement,
    COUNTIF(a.ev = 'ad_impression_MAX' AND a.fmt = 'REWARDED' AND a.placement = r.placement)                    rew_imp_same,
    COUNTIF(a.ev = 'ad_impression_MAX' AND a.fmt = 'INTER' AND a.placement = CONCAT('interstitial_backup_', r.placement)) backup_imp_same,
    COUNTIF(a.ev = 'ad_impression_MAX')                                                                          any_imp,
    COUNTIF(a.ev = 'ad_show')                                                                                    ad_show,
    COUNTIF(a.ev = 'show_ad_click')                                                                              taps,
    COUNTIF(a.ev = 'ad_request_status' AND a.ad_type = 'rewarded' AND a.status = 'fail')                         rew_load_fail,
    COUNTIF(a.ev = 'ad_request_status' AND a.ad_type = 'rewarded' AND a.status = 'success')                      rew_load_ok,
    ARRAY_AGG(IF(a.ev = 'ad_impression_MAX', a.ad_source, NULL) IGNORE NULLS ORDER BY a.ts DESC LIMIT 1)[SAFE_OFFSET(0)] ad_source,
    COUNTIF(a.ev LIKE 'ad\\_rewarded\\_%' AND a.ev != 'ad_rewarded_show_failed' AND a.ts < r.ts AND a.ts != r.ts) prior_rewards_120s
  FROM rewards r
  LEFT JOIN x a ON a.u = r.u AND a.ts BETWEEN r.ts - 120000000 AND r.ts + 30000000
  GROUP BY 1, 2, 3, 4, 5
)
SELECT ctx.*,
  CASE
    WHEN rew_imp_same > 0    THEN '1 rewarded ad shown (same placement)'
    WHEN backup_imp_same > 0 THEN '2 backup interstitial shown (same placement)'
    WHEN any_imp > 0         THEN '3 ad shown on another placement'
    WHEN ad_show > 0         THEN '4 ad_show logged, no impression'
    WHEN rew_load_fail > 0   THEN '5 no ad: load failed'
    WHEN taps > 0            THEN '6 no ad: watch-ad tap only'
    ELSE                          '7 no ad activity at all'
  END AS what_preceded,
  owners.noads_ts IS NOT NULL AND owners.noads_ts < ctx.ts           AS owned_noads,
  owners.membership_ts IS NOT NULL AND owners.membership_ts < ctx.ts AS owned_membership
FROM ctx LEFT JOIN owners USING (u)

-- Rewarded-ad event counts per build, this week (2026-09-25 .. 2026-10-01), ex-China, per 1,000 users.
-- ad_reward = generic reward log (fixed AdMob unit); ad_rewarded_<placement> = per-placement reward log.
WITH x AS (
  SELECT app_info.version v, user_pseudo_id u, event_name ev,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_format') fmt
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260925' AND '20261001' AND geo.country != 'China'
),
n AS (SELECT v, COUNT(DISTINCT u) users FROM x GROUP BY v)
SELECT x.v AS version, ANY_VALUE(n.users) users,
  COUNTIF(ev = 'show_ad_click')                                                        watch_ad_taps,
  COUNTIF(ev = 'ad_impression_MAX' AND fmt = 'REWARDED')                               rewarded_impressions,
  COUNTIF(ev = 'ad_show')                                                              ad_show,
  COUNTIF(ev LIKE 'ad\\_rewarded\\_%' AND ev != 'ad_rewarded_show_failed')            placement_rewards,
  COUNTIF(ev = 'ad_reward')                                                            generic_ad_reward
FROM x JOIN n USING (v)
GROUP BY x.v
HAVING users >= 50 AND version IN ('26.2', '29', '32', '33', '34')
ORDER BY version

-- Ads health by app version, this week 2026-09-25 .. 2026-10-01, all countries except China
WITH e AS (
  SELECT user_pseudo_id u, app_info.version v, event_name ev,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_format')    fmt,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_placement') placement,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_type')      ad_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status')       status,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error')        err,
    (SELECT COALESCE(value.double_value, value.float_value, CAST(value.int_value AS FLOAT64))
       FROM UNNEST(event_params) WHERE key='value') val
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260925' AND '20261001'
    AND geo.country != 'China'
    AND event_name IN ('ad_impression_MAX','ad_reward','show_ad_click','ad_request_status','user_engagement')
),
per_user AS (
  SELECT v, u,
    COUNTIF(ev='ad_impression_MAX' AND fmt='REWARDED')                                     rew_imps,
    COUNTIF(ev='ad_impression_MAX' AND fmt='INTER' AND placement LIKE 'interstitial_backup%') backup_inter_imps,
    COUNTIF(ev='ad_impression_MAX' AND fmt='INTER' AND placement NOT LIKE 'interstitial_backup%') regular_inter_imps,
    SUM(IF(ev='ad_impression_MAX', val, 0))                                                ad_usd,
    COUNTIF(ev='ad_reward')                                                                rewards,
    COUNTIF(ev='show_ad_click')                                                            ad_clicks,
    COUNTIF(ev='ad_request_status' AND ad_type='rewarded')                                 rew_loads,
    COUNTIF(ev='ad_request_status' AND ad_type='rewarded' AND status='success')            rew_loads_ok,
    COUNTIF(ev='ad_request_status' AND ad_type='inter')                                    inter_loads,
    COUNTIF(ev='ad_request_status' AND ad_type='inter' AND status='success')               inter_loads_ok,
    COUNTIF(ev='ad_request_status' AND status='fail' AND err LIKE '%applvn.com%')          dns_fails
  FROM e GROUP BY v, u
)
SELECT v AS version,
  COUNT(*)                                        users,
  SUM(rew_imps)                                   rewarded_impressions,
  SUM(regular_inter_imps)                         regular_interstitial_impressions,
  SUM(backup_inter_imps)                          backup_interstitial_impressions,
  ROUND(SUM(ad_usd), 2)                           ad_usd,
  SUM(ad_clicks)                                  watch_ad_taps,
  SUM(rewards)                                    rewards_granted,
  SUM(rewards) - SUM(rew_imps)                    rewards_minus_impressions,
  COUNTIF(rewards > 0 AND rew_imps = 0)           users_rewarded_never_saw_ad,
  SUM(IF(rew_imps = 0, rewards, 0))               rewards_to_those_users,
  SUM(rew_loads)                                  rewarded_load_attempts,
  ROUND(100 * SAFE_DIVIDE(SUM(rew_loads_ok), SUM(rew_loads)), 1)     rewarded_fill_pct,
  SUM(inter_loads)                                interstitial_load_attempts,
  SUM(inter_loads_ok)                             interstitial_loads_ok,
  SUM(dns_fails)                                  dns_fail_attempts,
  COUNTIF(dns_fails > 0)                          users_with_dns_fail,
  ROUND(SAFE_DIVIDE(SUM(dns_fails), COUNTIF(dns_fails > 0)), 0)      dns_fails_per_affected_user
FROM per_user
GROUP BY v
HAVING users >= 50 AND v IN ('26.2', '28', '29', '32', '33', '34')
ORDER BY users DESC

-- Why ad loads fail this week (2026-09-25 .. 2026-10-01, ex-China): the raw SDK error on ad_request_status,
-- grouped into failure types, with per-user retry intensity and the main countries.
WITH f AS (
  SELECT user_pseudo_id u, geo.country country,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') err
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260925' AND '20261001' AND geo.country != 'China'
    AND event_name = 'ad_request_status'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status') = 'fail'
),
k AS (
  SELECT u, country,
    CASE
      WHEN err LIKE 'Unable to resolve host%applvn%'                         THEN 'A · lookup of ms.applvn.com returns no address'
      WHEN err LIKE '%applvn.com/0.0.0.0%' OR err LIKE '%applvn.com/[::]%'   THEN 'B · lookup returns 0.0.0.0 (DNS ad blocker)'
      WHEN err IS NULL                                                       THEN 'C · no error message logged'
      WHEN err LIKE '%No Fill%' OR err LIKE '%no eligible ads%'              THEN 'D · no fill (no ad available)'
      ELSE                                                                        'E · other (connection reset, TLS, timeout …)'
    END kind
  FROM f
),
per_user AS (SELECT kind, u, COUNT(*) n FROM k GROUP BY 1, 2),
by_country AS (
  SELECT kind, STRING_AGG(CONCAT(country, ' ', CAST(cu AS STRING)), ', ' ORDER BY cu DESC, country LIMIT 6) top_countries
  FROM (SELECT kind, country, COUNT(DISTINCT u) cu FROM k GROUP BY 1, 2)
  GROUP BY kind
),
agg AS (
  SELECT kind, COUNT(*) users, SUM(n) failed_loads, APPROX_QUANTILES(n, 2)[OFFSET(1)] median_per_user, MAX(n) max_per_user
  FROM per_user GROUP BY kind
)
SELECT agg.kind AS failure_type, users, failed_loads,
  ROUND(failed_loads / users, 0)                           failed_loads_per_user,
  median_per_user, max_per_user,
  ROUND(100 * failed_loads / SUM(failed_loads) OVER (), 1) pct_of_failed_loads,
  top_countries AS top_countries_by_users
FROM agg JOIN by_country USING (kind)
ORDER BY failure_type

-- Every no-ads .sale signal by build, 2026-09-16 .. 2026-10-01
WITH e AS (
  SELECT app_info.version v, event_name, user_pseudo_id u, PARSE_DATE('%Y%m%d', event_date) d,
    COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key='product_id'),
             (SELECT value.string_value FROM UNNEST(event_params) WHERE key='position')) sku
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260916' AND '20261001'
)
SELECT v AS version, MIN(d) first_day, COUNT(DISTINCT u) users,
  COUNTIF(sku LIKE '%skip.ad%.sale' AND event_name='purchase_click')       sale_taps,
  COUNTIF(sku LIKE '%skip.ad%.sale' AND event_name='in_app_purchase')      sale_purchases,
  COUNTIF(sku LIKE '%skip.ad%.sale' AND event_name='earn')                 sale_reward_grants,
  COUNTIF(sku LIKE '%skip.ad%' AND sku NOT LIKE '%.sale' AND event_name='purchase_click')  full_price_noads_taps,
  COUNTIF(sku LIKE '%skip.ad%' AND sku NOT LIKE '%.sale' AND event_name='in_app_purchase') full_price_noads_purchases,
  MAX(IF(sku LIKE '%skip.ad%.sale' AND event_name='in_app_purchase', d, NULL))              last_sale_purchase
FROM e
GROUP BY v
HAVING users >= 20
ORDER BY first_day

-- China vs rest, 2026-09-18 .. 2026-10-01, by app version: who they are and what they earn
WITH e AS (
  SELECT user_pseudo_id u, app_info.version v, geo.country = 'China' AS china, event_name ev, event_value_in_usd usd,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_type') ad_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status')  status,
    (SELECT COALESCE(value.double_value, value.float_value, CAST(value.int_value AS FLOAT64))
       FROM UNNEST(event_params) WHERE key='value') val,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec') eng
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260918' AND '20261001'
)
SELECT IF(china, 'China', 'rest of world') AS region, v AS version,
  COUNT(DISTINCT u)                                                       users,
  COUNT(DISTINCT IF(ev='first_open', u, NULL))                            installs,
  ROUND(SUM(IF(ev='user_engagement', eng, 0)) / 60000 / COUNT(DISTINCT u), 1) minutes_per_user,
  COUNTIF(ev='purchase_click')                                            shop_taps,
  COUNTIF(ev='in_app_purchase')                                           purchases,
  ROUND(SUM(IF(ev='in_app_purchase', usd, 0)), 2)                         iap_usd,
  COUNTIF(ev='ad_impression_MAX')                                         ad_impressions,
  ROUND(SUM(IF(ev='ad_impression_MAX', val, 0)), 2)                       ad_usd,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(ev='ad_request_status' AND ad_type='rewarded' AND status='success'),
                          COUNTIF(ev='ad_request_status' AND ad_type='rewarded')), 1) rewarded_fill_pct
FROM e
GROUP BY region, version
HAVING users >= 30
ORDER BY region, users DESC

-- Event names by the build they first appear in (whole export); keep those first seen on v28 or later
WITH e AS (
  SELECT event_name, app_info.version AS v, PARSE_DATE('%Y%m%d', event_date) AS d, user_pseudo_id AS u
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
),
first_seen AS (
  SELECT event_name, MIN(d) AS first_day,
    ARRAY_AGG(v ORDER BY d LIMIT 1)[OFFSET(0)] AS first_version
  FROM e GROUP BY event_name
)
SELECT f.event_name, f.first_day, f.first_version,
  STRING_AGG(DISTINCT e.v ORDER BY e.v) AS versions_seen,
  COUNT(*) AS events, COUNT(DISTINCT e.u) AS users
FROM first_seen f JOIN e USING (event_name)
WHERE f.first_day >= '2026-09-14'
GROUP BY 1, 2, 3
ORDER BY f.first_day, events DESC

-- Parameters carried by the events introduced in v28-v30
SELECT event_name, p.key,
  COUNT(*) n,
  COUNT(DISTINCT COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING),
                          CAST(p.value.double_value AS STRING))) distinct_values,
  STRING_AGG(DISTINCT COALESCE(p.value.string_value, CAST(p.value.int_value AS STRING)) LIMIT 6) sample_values
FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`, UNNEST(event_params) p
WHERE _TABLE_SUFFIX >= '20260916'
  AND (event_name LIKE 'event_slot_claim_stars_%' OR event_name IN ('ad_rewarded_rewareded_mini_event', 'app_exception'))
  AND p.key NOT IN ('firebase_screen_class','firebase_screen_id','firebase_event_origin','ga_session_id',
                    'ga_session_number','engaged_session_event','firebase_conversion')
GROUP BY 1, 2
ORDER BY 1, 2

-- Events logged on v29 but missing (or nearly missing) on v32-v34, 2026-09-18 .. 2026-10-01, ex-China.
-- Per-user rates so builds with different audience sizes compare fairly.
WITH e AS (
  SELECT event_name, app_info.version v, user_pseudo_id u
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260918' AND '20261001' AND geo.country != 'China'
    AND app_info.version IN ('29', '32', '33', '34')
),
users AS (SELECT v, COUNT(DISTINCT u) n FROM e GROUP BY v),
ev AS (SELECT event_name, v, COUNT(*) events, COUNT(DISTINCT u) users FROM e GROUP BY 1, 2)
SELECT event_name,
  MAX(IF(ev.v='29', ev.users, 0)) users_v29, MAX(IF(ev.v='32', ev.users, 0)) users_v32,
  MAX(IF(ev.v='33', ev.users, 0)) users_v33, MAX(IF(ev.v='34', ev.users, 0)) users_v34,
  ROUND(100 * MAX(IF(ev.v='29', ev.users / users.n, 0)), 1) pct_users_v29,
  ROUND(100 * MAX(IF(ev.v='34', ev.users / users.n, 0)), 1) pct_users_v34
FROM ev JOIN users USING (v)
GROUP BY event_name
HAVING users_v29 >= 20 AND pct_users_v34 < 0.25 * pct_users_v29
ORDER BY users_v29 DESC