Interstitial ads load successfully ~3% of the time

0 · What's changed in the last few days

Engineering has already root-caused part of a fresh decline in rewarded fill, separate from the DNS/connect problem above: AppLovin's mediation server is returning ad responses for the REWARDED_INTER (Rewarded Interstitial) format without the ad_format field the client's MAX SDK expects. The SDK doesn't recognize the format, treats the response as No Fill (error 204), and the ad never becomes "ready" — a player who taps gets a "not ready" toast even though AppLovin did return something. This is a client-SDK/server-format mismatch, not a lack of inventory.

The data shows a matching pattern over the last 10 days: rewarded fill rate slid from 67% on 2026-09-13 to 44% on 2026-09-21 (choppy day to day, but the last full day is well below the first), while interstitial attempt volume surged from a baseline around 3,383/day to a peak of 15,946 attempts on 2026-09-20 — roughly 5× baseline — before easing back down, tracking the same days rewarded fill was at its worst. Interstitial fill rate stayed in the low single digits throughout, so this substitution is not recovering the failed ad opportunity, just changing what's being retried.

The last-10-days query:

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS day,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_type') AS ad_type,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status') AS status,
  COUNT(*) AS n
FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
WHERE event_name = 'ad_request_status'
  AND _TABLE_SUFFIX BETWEEN '20260913' AND '20260922'
GROUP BY day, ad_type, status
ORDER BY day, ad_type

1 · Fill rate by ad type

ad_request_status is the load outcome event — status = success or fail, with which network filled it and the raw SDK error on failure. This is the standard fill/fail-rate signal, and it's been live since build 5 (2026-07-01), so the whole export has consistent instrumentation.

2 · Daily fill-rate trend

Today's shard is partial and excluded. Both lines are consistently low across the whole window — this is not a one-day blip.

The query behind sections 1, 2, 3 and 4 — copy and re-run it:

SELECT
  PARSE_DATE('%Y%m%d', event_date) AS day,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_type') AS ad_type,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_network') AS ad_network,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status') AS status,
  CASE
    WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') IS NULL THEN NULL
    WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') LIKE '%applvn.com%' THEN 'AppLovin MAX mediation server unreachable (DNS/connect)'
    WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') LIKE '%No Fill%' OR (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') LIKE '%no eligible ads%' THEN 'No fill (no ad available)'
    WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') LIKE '%timeout%' OR (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') LIKE '%timed out%' THEN 'Timeout'
    WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') LIKE '%onnection%' OR (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') LIKE 'unexpected end of stream%' THEN 'Connection dropped mid-request'
    WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') LIKE '%JSONException%' THEN 'Malformed mediation response (JSON parse error)'
    ELSE 'Other'
  END AS error_bucket,
  COUNT(*) AS n
FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
WHERE event_name = 'ad_request_status'
GROUP BY day, ad_type, ad_network, status, error_bucket
ORDER BY day

3 · Which network fills, when a load does succeed

4 · Why loads fail

~32-37% of failures carry no error string at all (the SDK set status=fail but the error param is empty) — a real instrumentation gap; right now there's no way to tell what's behind that bucket. Genuine "no fill" (the network legitimately having nothing to serve) is a small minority of failures for both ad types.

5 · The rest of the funnel — and two unexplained gaps

• Only 1,076,819 of 1,930,708 ad_request events (56%) have a matching ad_request_status — the rest never resolve to a logged success or fail.
• Of 82,543 successful loads, only 29,107 (ad_show, 35%) are followed by an actual display. Plausibly SDK preloading/caching (a successful load doesn't have to be shown right away), but large enough that it's worth checking the client's ad-caching logic rather than assuming it's benign.
• The backup-interstitial fallback — the path meant to catch players when the primary interstitial fails — fails 82.5% of the time itself (3,183 of 3,860), worse than the primary path's already-poor fill rate. This is tracked separately from ad_request_status and carries no network/error breakdown, so it needs its own investigation.

The funnel query:

SELECT
  event_name,
  PARSE_DATE('%Y%m%d', event_date) AS day,
  COUNT(*) AS n,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
WHERE event_name IN ('show_ad_click','ad_request','ad_request_status','ad_show','ad_reward',
                      'ad_rewarded_show_failed','show_ad_inter_backup','show_ad_inter_backup_failed')
GROUP BY event_name, day
ORDER BY event_name, day

The concentration check (section verdict, para 3):

WITH s AS (
  SELECT
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='ad_type') AS ad_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status') AS status,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='error') AS error
  FROM `tiny-knightfall-idle-rpg-game.analytics_538412813.events_intraday_*`
  WHERE event_name = 'ad_request_status'
)
SELECT
  ad_type,
  COUNT(DISTINCT user_pseudo_id) AS users_total,
  COUNT(DISTINCT IF(status='fail' AND error LIKE '%applvn.com%', user_pseudo_id, NULL)) AS users_with_dns_fail,
  COUNTIF(status='fail' AND error LIKE '%applvn.com%') AS dns_fail_events
FROM s
GROUP BY ad_type

6 · Reading this number

• This counts load/show attempts, not ad impressions delivered or revenue. Pair with publisher.ad_revenue_in_usd if the next question is how much this is costing.
• The retry pattern inflates the attempt count. ~23% of users account for essentially all of the DNS-failure volume, at ~109 failed attempts each for interstitial — so the fill rate reflects the endpoint's reachability, not literally "97 in 100 ad breaks show nothing" to every player.
• ad_network='unknown' on every failed row is expected — no network filled the request, so none is attributed.
• Full working, including the daily-trend chart code and the pandas aggregation, is in notebooks/13_ad_fill_show_rate.ipynb.