Weekly report — 9 to 15 October 2026

-- One row per day x app version x China flag, 2026-09-04 .. 2026-10-16: DAU, installs, IAP, payers, ads.
WITH ev AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) d, user_pseudo_id u, event_name e, event_timestamp ts,
    app_info.version v, geo.country = 'China' cn, event_value_in_usd usd,
    (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 '20260904' AND '20261016'
),
ud AS (
  SELECT d, u,
    ARRAY_AGG(v IGNORE NULLS ORDER BY ts DESC LIMIT 1)[SAFE_OFFSET(0)] v,
    LOGICAL_OR(cn) cn,
    COUNTIF(e='first_open') > 0 installed,
    SUM(IF(e='in_app_purchase', usd, 0)) iap_usd,
    COUNTIF(e='in_app_purchase') iap_buys,
    SUM(IF(e='ad_impression_MAX', COALESCE(val, 0), 0)) ad_usd,
    COUNTIF(e='ad_impression_MAX') ad_imps
  FROM ev GROUP BY d, u
)
SELECT d, v, cn, COUNT(*) dau, COUNTIF(installed) installs, SUM(iap_usd) iap_usd, SUM(iap_buys) iap_buys,
  COUNTIF(iap_buys > 0) payers, SUM(ad_usd) ad_usd, SUM(ad_imps) ad_imps, COUNTIF(ad_imps > 0) ad_watchers
FROM ud GROUP BY d, v, cn

-- IAP per payer, ex-China, two report weeks (02-08 Oct, 09-15 Oct).
SELECT IF(event_date >= '20261009', 'this', 'prev') wk, user_pseudo_id u, ANY_VALUE(geo.country) country,
  ANY_VALUE(app_info.version) v, COUNT(*) buys, SUM(event_value_in_usd) usd
FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
WHERE _TABLE_SUFFIX BETWEEN '20261002' AND '20261015' AND event_name = 'in_app_purchase' AND geo.country != 'China'
GROUP BY 1, 2

-- One row per product SKU x day x version (ex-China): store taps and purchases, plus each SKU's first day in the export.
WITH ev AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) d, user_pseudo_id u, event_name e, 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
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE event_name IN ('in_app_purchase', 'purchase_click', 'first_purchase')
    AND geo.country != 'China'
),
x AS (SELECT * FROM ev WHERE sku LIKE 'com.idle.tiny.%'),
first_seen AS (SELECT sku, MIN(d) first_day, MIN(IF(e='in_app_purchase', d, NULL)) first_buy_day FROM x GROUP BY sku)
SELECT x.sku, f.first_day, f.first_buy_day, x.d, x.v,
  COUNTIF(e='purchase_click') taps, COUNT(DISTINCT IF(e='purchase_click', u, NULL)) tappers,
  COUNTIF(e='in_app_purchase') buys, COUNT(DISTINCT IF(e='in_app_purchase', u, NULL)) buyers,
  SUM(IF(e='in_app_purchase', usd, 0)) usd
FROM x JOIN first_seen f USING (sku)
WHERE x.d >= '2026-09-04'
GROUP BY 1, 2, 3, 4, 5

-- Day-1 return by install day and install build, ex-China, installs 2026-09-18 .. 2026-10-15.
WITH ev AS (
  SELECT user_pseudo_id u, PARSE_DATE('%Y%m%d', event_date) d, event_name e, event_timestamp ts,
    app_info.version v, geo.country country
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260918' AND '20261016'
),
inst AS (
  SELECT u, MIN(d) install_d, ARRAY_AGG(v ORDER BY ts LIMIT 1)[OFFSET(0)] v, ANY_VALUE(country) country
  FROM ev WHERE e = 'first_open' GROUP BY u
)
SELECT i.install_d, i.v, COUNT(*) installs,
  COUNT(DISTINCT IF(a.d = DATE_ADD(i.install_d, INTERVAL 1 DAY), i.u, NULL)) d1_users
FROM inst i LEFT JOIN (SELECT DISTINCT u, d FROM ev WHERE e = 'user_engagement') a USING (u)
WHERE i.country != 'China' AND i.install_d <= '2026-10-15'
GROUP BY 1, 2

-- Event names first seen on or after 2026-10-02 (whole export), with the build they first appear in.
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-10-02'
GROUP BY 1, 2, 3
ORDER BY f.first_day, events DESC