This is a very basic daily DAU query for when the event.product_metrics_app_base app_open event instrumentation is distributed to app users - I will need to fine tune when we have data coming in - with DAU/MAU and Retention metrics we need to eliminate users with malformed/bad device dates since they can throw off our counts, as well as specifying Production app version data from Android.

iOS Release: 8.0.1 (6042) Submitted to App Store, waiting for review 2026-05-07
Android Release: TBD

SELECT
  DATE(from_iso8601_timestamp(dt)) AS "date",
  COUNT(DISTINCT CASE WHEN agent.client_platform = 'android' AND REGEXP_LIKE(agent.app_version_name,'-r-')  THEN agent.app_install_id END) AS android_n_uniques,
  COUNT(DISTINCT CASE WHEN agent.client_platform = 'ios' THEN agent.app_install_id END) AS ios_n_uniques
FROM event.product_metrics_app_base
WHERE YEAR = 2026 AND MONTH >= 05 AND DAY >= 1
  AND instrument_name = 'apps-open'
  AND action = 'app_open'
  AND agent.client_platform IN ('android', 'ios')
  AND DATE(from_iso8601_timestamp(dt)) < current_date
GROUP BY DATE(from_iso8601_timestamp(dt))
ORDER BY 1

need to eliminate users with malformed/bad device dates since they can throw off our counts

This happens a lot, but usually we rely on meta.dt instead of dt which sidesteps issues with people's time settings. Is that possible here? Like are app teams sending this event relatively quickly after they would've triggered. If so, we can just depend on the meta.dt set by EventGate at intake. Otherwise, can the app teams just set dt to their best approximation? If it's a lot of users that would get filtered out, I'd just worry we're throwing out otherwise good data (people with bad time settings are just a weird "normal" thing on the internet).

Thanks @Milimetric, agreed we can switch to using meta.dt in all queries going forward. For DAU/MAU it should help to eliminate the bad device dates.

milimetric opened https://gitlab.wikimedia.org/repos/data-engineering/dbt-jobs/-/merge_requests/56

Add query for DE3 metric "DAU on mobile apps"

@Milimetric @SNowick_WMF,
Can we call this metric "Daily Readers on apps" to parallel "Weekly logged-in Readers on web"
A less wordy solution could alternatively be "Daily app Readers" and "Weekly logged-in web Readers"

milimetric merged https://gitlab.wikimedia.org/repos/data-engineering/dbt-jobs/-/merge_requests/56

Reorganize Tiger Team metrics and add models