Firefox ios

metric-hub

Pre-defined data sources for firefox_ios. These data sources are defined in metric-hub

active_users_aggregates_v1

Client ID column: NULL

Submission Date column: submission_date

Definition:

(
    SELECT *
     FROM `moz-fx-data-shared-prod.telemetry.active_users_aggregates`
    WHERE app_name = 'Firefox iOS'
)

---

baseline

Client ID column: client_info.client_id

Submission Date column: None

Definition:

(
    SELECT
        p.*,
        DATE(p.submission_timestamp) AS submission_date
    FROM `moz-fx-data-shared-prod.{dataset}.baseline` p
)

---

baseline_v2

Client ID column: client_info.client_id

Submission Date column: submission_date

Definition:

(
    SELECT
        p.*,
        DATE(p.submission_timestamp) AS submission_date
    FROM `moz-fx-data-shared-prod.{dataset}.baseline` p
)

---

events

Client ID column: client_info.client_id

Submission Date column: None

Definition:

(
    SELECT
        p.* EXCEPT (events),
        DATE(p.submission_timestamp) AS submission_date,
        event
    FROM
        `moz-fx-data-shared-prod.{dataset}.events` p
    CROSS JOIN
        UNNEST(p.events) AS event
)

---

metrics

Client ID column: client_info.client_id

Submission Date column: None

Definition:

(
    SELECT
        p.*,
        DATE(p.submission_timestamp) AS submission_date
    FROM `moz-fx-data-shared-prod.{dataset}.metrics` p
)

---

mobile_search_clients_engines_sources_daily

Client ID column: client_id

Submission Date column: submission_date

Definition:

(
    SELECT *
    FROM `mozdata.search.mobile_search_clients_engines_sources_daily`
    WHERE normalized_app_name_os = 'Firefox iOS'
)

---

new_profile_activation

Client ID column: None

Submission Date column: None

Definition:

`mozdata.firefox_ios.clients_activation`

---

special_onboarding_events

Client ID column: None

Submission Date column: None

Definition:

(
SELECT
    expo.submission_date
    , expo.client_id
    , case when (conv.set_to_default >= 1 AND expo.set_to_default_card >= 1) then 1
           when (coalesce(conv.set_to_default, 0) = 0 AND expo.set_to_default_card >= 1) then 0 else null end as set_to_default_flag
    , case when (conv.turn_on_notifications >= 1 AND expo.turn_on_notifications_card >= 1) then 1
           when (coalesce(conv.turn_on_notifications, 0) = 0 AND expo.turn_on_notifications_card >= 1) then 0 else null end as turn_on_notifications_flag
    , case when (conv.sign_in >= 1 AND expo.sign_in_card >= 1) then 1
           when (coalesce(conv.sign_in,0) = 0 AND expo.sign_in_card >= 1) then 0 else null end as sign_in_flag
    , case when (conv.set_to_default >= 1 AND expo.set_to_default_card >= 1) OR (conv.turn_on_notifications >= 1 AND expo.turn_on_notifications_card >= 1) OR (conv.sign_in >= 1 AND expo.sign_in_card >= 1)then 1
           when (coalesce(conv.set_to_default, 0) = 0 AND coalesce(conv.turn_on_notifications, 0) = 0 AND coalesce(conv.sign_in,0) = 0)  AND (set_to_default_card >= 1 OR turn_on_notifications_card >= 1 OR sign_in_card >= 1) then 0 else null end as at_least_1_cta

FROM (
      SELECT
            client_info.client_id as client_id
            , min(DATE(submission_timestamp)) as submission_date
            , count(case when ext.value in ("default-browser", "welcome") then DATE(submission_timestamp) END) as set_to_default_card
            , count(case when ext.value in ( "notification-permissions", "notificationPermission", "notificationPermissions") then DATE(submission_timestamp) END) as turn_on_notifications_card
            , count(case when ext.value in ("sign-to-sync", "signToSync") then DATE(submission_timestamp) END) as sign_in_card
      FROM
        `mozdata.org_mozilla_ios_firefox.events` tm
      CROSS JOIN
        UNNEST(events) AS event
      CROSS JOIN
        UNNEST(event.extra) AS ext
      WHERE event.name = "card_view" AND event.category = "onboarding" AND ext.key ="card_type"
        AND ext.value in ("default-browser", "welcome", "notification-permissions", "sign-to-sync", "signToSync", "notificationPermission", "notificationPermissions")
        -- Ask if any of the CTAs we care about are shown on wallpaper, welcome, pin card
        -- if so that will make the logic a bit complex
        -- Also ask if the different variation mean the same thing and ask engineers to standardize
      AND DATE(submission_timestamp) >= "2023-06-01"
      GROUP BY 1
      ) expo
LEFT JOIN (
  SELECT
      client_info.client_id as client_id
            , count(case when ext.value = "set-default-browser" then DATE(submission_timestamp) END) as set_to_default
            , count(case when ext.value = "request-notifications" then DATE(submission_timestamp) END) as turn_on_notifications
            , count(case when ext.value = "sync-sign-in" then DATE(submission_timestamp) END) as sign_in
  FROM
    `mozdata.org_mozilla_ios_firefox.events` tm
  CROSS JOIN
     UNNEST(events) AS event
  CROSS JOIN
     UNNEST(event.extra) AS ext
  WHERE event.category = "onboarding" AND event.name = "primary_button_tap"  AND ext.key ="button_action"
    AND ext.value in ("set-default-browser", "request-notifications", "open-default-browser-popup", "sync-sign-in")
    -- "open-default-browser-popup" the same as "set-default-browser"??
  AND DATE(submission_timestamp) >= "2023-06-01"
  GROUP BY 1
) conv
ON expo.client_id = conv.client_id
GROUP BY 1, 2, 3, 4, 5, 6
)

---

appstore_funnel

Client ID column: NULL

Submission Date column: submission_date

Definition:

(
    SELECT *
     FROM `mozdata.firefox_ios.app_store_funnel`
)

---

funnel_retention

Client ID column: NULL

Submission Date column: submission_date

Definition:

(
    SELECT *
     FROM `mozdata.firefox_ios.funnel_retention_week_4`
)

---