Firefox ios

metric-hub

Pre-defined metrics for firefox_ios. These metrics are defined in metric-hub

baseline_ping_count

Baseline pings

Counts the number of baseline pings received from each client.

Data Source: baseline

Definition:

COUNT(document_id)

SQL with DataSource

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

---

metric_ping_count

Metrics pings

Counts the number of metrics pings received from each client.

Data Source: metrics

Definition:

COUNT(document_id)

SQL with DataSource

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

---

first_run_date

First run date

The earliest first-run date reported by each client.

Data Source: baseline

Definition:

MIN(client_info.first_run_date)

SQL with DataSource

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

---

active_hours

Active Hours

Total time Firefox was active

Data Source: baseline

Definition:

COALESCE(SUM(metrics.timespan.glean_baseline_duration.value), 0) / 3600.0

SQL with DataSource

SELECT
  COALESCE(SUM(metrics.timespan.glean_baseline_duration.value), 0) / 3600.0
FROM (
  (
    SELECT
        p.*,
        DATE(p.submission_timestamp) AS submission_date
    FROM `moz-fx-data-shared-prod.{dataset}.baseline` p
)
)

---

days_of_use

Days of use

The number of days in an observation window that clients used the browser.

Data Source: baseline

Definition:

COUNT(DISTINCT DATE(submission_timestamp))

SQL with DataSource

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

---

daily_active_users

DAU

The number of unique clients that we received a baseline ping from each day, excluding pings originating from BrowserStack. To be comparable to DAU used for KPI tracking, this metric needs to be aggregated by submission_date. If the metric is NOT aggregated by submission_date, the metric is similar to a "days of use" metric. For more details, refer to the DAU description in the Mozilla Data Documentation.

For questions, please contact bochocki@mozilla.com or firefox-kpi@mozilla.com.

Data Source: baseline_v2

Definition:

COUNT(DISTINCT CASE WHEN LOWER(metadata.isp.name) != 'browserstack' THEN client_info.client_id ELSE NULL END)

SQL with DataSource

SELECT
  COUNT(DISTINCT CASE WHEN LOWER(metadata.isp.name) != 'browserstack' THEN client_info.client_id ELSE NULL END)
FROM (
  (
    SELECT
        p.*,
        DATE(p.submission_timestamp) AS submission_date
    FROM `moz-fx-data-shared-prod.{dataset}.baseline` p
)
)

---

daily_active_users_v2

Firefox iOS DAU

This is the official DAU reporting definition. The logic is detailed on the Confluence DAU page and is automatically cross-checked, actively monitored, and change controlled. Whenever possible, this is the preferred DAU reporting definition to use for Firefox iOS. This metric needs to be aggregated by submission_date. If it is not aggregated by submission_date, it is similar to a "days of use" metric, and not DAU.

For more information, refer to [the DAU description in Confluence](https://mozilla-hub.atlassian.net/wiki/spaces/DATA/pages/314704478/Daily+Active+Users+DAU+Metric).
For questions please contact bochocki@mozilla.com or firefox-kpi@mozilla.com.

Data Source: firefox_ios_active_users_aggregates_view

Definition:

SUM(dau)

SQL with DataSource

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

---

client_level_daily_active_users_v1

Firefox iOS Client-Level DAU

This metric reports DAU values similar (but not necessarily identical) to the official DAU reporting definition. It's generally preferable to use the official DAU reporting definition when possible; this metric exists only for cases where reporting client_id is required (e.g. for experiments). This metric needs to be aggregated by submission_date. If it is not aggregated by submission_date, it is similar to a "days of use" metric, and not DAU.

For more information, refer to [the DAU description in Confluence](https://mozilla-hub.atlassian.net/wiki/spaces/DATA/pages/314704478/Daily+Active+Users+DAU+Metric).
For questions please contact bochocki@mozilla.com or firefox-kpi@mozilla.com.

Data Source: baseline_v2

Definition:

COUNT(DISTINCT CASE WHEN LOWER(metadata.isp.name) != 'browserstack' THEN client_info.client_id ELSE NULL END)

SQL with DataSource

SELECT
  COUNT(DISTINCT CASE WHEN LOWER(metadata.isp.name) != 'browserstack' THEN client_info.client_id ELSE NULL END)
FROM (
  (
    SELECT
        p.*,
        DATE(p.submission_timestamp) AS submission_date
    FROM `moz-fx-data-shared-prod.{dataset}.baseline` p
)
)

---

client_level_daily_active_users_v2

Firefox iOS Client-Level DAU

This metric reports DAU values similar (but not necessarily identical) to the official DAU reporting definition. It's generally preferable to use the official DAU reporting definition when possible; this metric exists only for cases where reporting client_id is required (e.g. for experiments). This metric needs to be aggregated by submission_date. If it is not aggregated by submission_date, it is similar to a "days of use" metric, and not DAU.

For more information, refer to the DAU description in Confluence. For questions please contact bochocki@mozilla.com or firefox-kpi@mozilla.com.

Data Source: baseline_v2

Definition:

COUNT(DISTINCT CASE WHEN metrics.timespan.glean_baseline_duration.value > 0
                         AND LOWER(metadata.isp.name) != 'browserstack'
                        THEN client_info.client_id
                        ELSE NULL END)

SQL with DataSource

SELECT
  COUNT(DISTINCT CASE WHEN metrics.timespan.glean_baseline_duration.value > 0
                         AND LOWER(metadata.isp.name) != 'browserstack'
                        THEN client_info.client_id
                        ELSE NULL END)
FROM (
  (
    SELECT
        p.*,
        DATE(p.submission_timestamp) AS submission_date
    FROM `moz-fx-data-shared-prod.{dataset}.baseline` p
)
)

---

client_level_daily_active_users_v3

Firefox iOS Client-Level DAU

Client-level DAU. The logic is detailed on the Confluence DAU page and is automatically cross-checked, actively monitored, and change controlled. This metric needs to be aggregated by submission_date. If it is not aggregated by submission_date, it is similar to a "days of use" metric, and not DAU.

Data Source: firefox_ios_active_users_view

Definition:

COUNTIF(is_dau)

SQL with DataSource

SELECT
  COUNTIF(is_dau)
FROM (
  (
    SELECT *
     FROM `moz-fx-data-shared-prod.firefox_ios.active_users`
    WHERE is_mobile
)
)

---

organic_search_count

Organic searches

Counts organic searches, which are searches that are not performed through a Firefox SAP and which are not monetizable. Learn more in the search data documentation.

Data Source: mobile_search_clients_engines_sources_daily

Definition:

{{agg_sum('organic')}}

SQL with DataSource

SELECT
  COALESCE(SUM(organic), 0)
FROM (
  (
    SELECT *
    FROM `mozdata.search.mobile_search_clients_engines_sources_daily`
    WHERE normalized_app_name_os = 'Firefox iOS'
)
)

---

ad_click_organic

Organic Ad Click Count

Total number of Organic Ad Click Counts

Data Source: mobile_search_clients_engines_sources_daily

Definition:

{{agg_sum('ad_click_organic')}}

SQL with DataSource

SELECT
  COALESCE(SUM(ad_click_organic), 0)
FROM (
  (
    SELECT *
    FROM `mozdata.search.mobile_search_clients_engines_sources_daily`
    WHERE normalized_app_name_os = 'Firefox iOS'
)
)

---

searches_with_ads_organic

Organic Search With Ads Count

Total number of Organic Search With Ads Counts

Data Source: mobile_search_clients_engines_sources_daily

Definition:

{{agg_sum('search_with_ads_organic')}}

SQL with DataSource

SELECT
  COALESCE(SUM(search_with_ads_organic), 0)
FROM (
  (
    SELECT *
    FROM `mozdata.search.mobile_search_clients_engines_sources_daily`
    WHERE normalized_app_name_os = 'Firefox iOS'
)
)

---

search_count

SAP searches

Counts the number of searches a user performed through Firefox's Search Access Points. Learn more in the search data documentation.

Data Source: mobile_search_clients_engines_sources_daily

Definition:

{{agg_sum('search_count')}}

SQL with DataSource

SELECT
  COALESCE(SUM(search_count), 0)
FROM (
  (
    SELECT *
    FROM `mozdata.search.mobile_search_clients_engines_sources_daily`
    WHERE normalized_app_name_os = 'Firefox iOS'
)
)

---

searches_with_ads

Search result pages with ads

Counts search result pages served with advertising. Users may not actually see these ads thanks to e.g. ad-blockers. Learn more in the search analysis documentation.

Data Source: mobile_search_clients_engines_sources_daily

Definition:

{{agg_sum('search_with_ads')}}

SQL with DataSource

SELECT
  COALESCE(SUM(search_with_ads), 0)
FROM (
  (
    SELECT *
    FROM `mozdata.search.mobile_search_clients_engines_sources_daily`
    WHERE normalized_app_name_os = 'Firefox iOS'
)
)

---

ad_clicks

Ad clicks

Counts clicks on ads on search engine result pages with a Mozilla partner tag.

Data Source: mobile_search_clients_engines_sources_daily

Definition:

{{agg_sum('ad_click')}}

SQL with DataSource

SELECT
  COALESCE(SUM(ad_click), 0)
FROM (
  (
    SELECT *
    FROM `mozdata.search.mobile_search_clients_engines_sources_daily`
    WHERE normalized_app_name_os = 'Firefox iOS'
)
)

---

tagged_search_count

Tagged SAP searches

Counts the number of searches a user performed through Firefox's Search Access Points that were submitted with a partner code and were potentially revenue-generating. Learn more in the search data documentation.

Data Source: mobile_search_clients_engines_sources_daily

Definition:

{{agg_sum('tagged_sap')}}

SQL with DataSource

SELECT
  COALESCE(SUM(tagged_sap), 0)
FROM (
  (
    SELECT *
    FROM `mozdata.search.mobile_search_clients_engines_sources_daily`
    WHERE normalized_app_name_os = 'Firefox iOS'
)
)

---

tagged_follow_on

Tagged follow-on searches

Counts the number of follow-on searches with a Mozilla partner tag. These are additional searches that users performed from a search engine results page after executing a tagged search through a SAP. Learn more in the search data documentation.

Data Source: mobile_search_clients_engines_sources_daily

Definition:

{{agg_sum('tagged_follow_on')}}

SQL with DataSource

SELECT
  COALESCE(SUM(tagged_follow_on), 0)
FROM (
  (
    SELECT *
    FROM `mozdata.search.mobile_search_clients_engines_sources_daily`
    WHERE normalized_app_name_os = 'Firefox iOS'
)
)

---

spoc_tiles_impressions

Sponsored Tiles Impressions

Number of times Contile Sponsored Tiles are shown.

Data Source: events

Definition:

COALESCE(COUNTIF(
          event.category = 'top_site'
          AND event.name = 'contile_impression'
      ),0)

SQL with DataSource

SELECT
  COALESCE(COUNTIF(
          event.category = 'top_site'
          AND event.name = 'contile_impression'
      ),0)
FROM (
  (
    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
)
)

---

spoc_tiles_clicks

Sponsored Tiles Clicks

Number of times user clicked a Contile Sponsored Tile.

Data Source: events

Definition:

COALESCE(COUNTIF(
          event.category = 'top_site'
          AND event.name = 'contile_click'
      ),0)

SQL with DataSource

SELECT
  COALESCE(COUNTIF(
          event.category = 'top_site'
          AND event.name = 'contile_click'
      ),0)
FROM (
  (
    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
)
)

---

spoc_tiles_preference_toggled

Sponsored Tiles Preference Toggled

Number of times Contile Sponsored Tiles setting is flipped.

Data Source: events

Definition:

COALESCE(SUM(CASE WHEN
          event.category = 'preferences'
          AND event.name = 'changed'
          AND `mozfun.map.get_key`(event.extra, 'preference') = 'sponsoredTiles'
    THEN 1 ELSE 0 END
  ),0)

SQL with DataSource

SELECT
  COALESCE(SUM(CASE WHEN
          event.category = 'preferences'
          AND event.name = 'changed'
          AND `mozfun.map.get_key`(event.extra, 'preference') = 'sponsoredTiles'
    THEN 1 ELSE 0 END
  ),0)
FROM (
  (
    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
)
)

---

new_profile_activation

New Profile Activation

A new profile is counted as activated one week after creation if it meets the following conditions: 1) at least 3 days of use during first week 2) at least one search between days 4-7.

Data Source: new_profile_activation

Definition:

COUNTIF(is_activated)

SQL with DataSource

SELECT
  COUNTIF(is_activated)
FROM (
  `mozdata.firefox_ios.new_profile_activation_clients`
)

---

turn_on_notifications_ctr_onboarding

Turn on Notification Click

This metric looks at proportion of all new profiles that were exposed to the turn on notification card and clicked the action during on-boarding.

Data Source: special_onboarding_events

Definition:

COALESCE(SUM(turn_on_notifications_flag))

SQL with DataSource

SELECT
  COALESCE(SUM(turn_on_notifications_flag))
FROM (
  (
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
)
)

---

set_to_default_ctr_onboarding

Set to Default Click

This metric looks at proportion of all new profiles that were exposed to the set to default card and clicked the action during on-boarding.

Data Source: special_onboarding_events

Definition:

COALESCE(SUM(set_to_default_flag))

SQL with DataSource

SELECT
  COALESCE(SUM(set_to_default_flag))
FROM (
  (
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
)
)

---

sign_in_ctr_onboarding

Sign in Click

This metric looks at proportion of all new profiles that were exposed to the sign-in card and clicked the action during on-boarding.

Data Source: special_onboarding_events

Definition:

COALESCE(SUM(sign_in_flag))

SQL with DataSource

SELECT
  COALESCE(SUM(sign_in_flag))
FROM (
  (
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
)
)

---

at_least_1_cta_ctr_onboarding

Clicked at least one CTA

This metric looks at proportion of all new profiles that were exposed to onboarding cards and clicked at least one action during on-boarding.

Data Source: special_onboarding_events

Definition:

COALESCE(SUM(at_least_1_cta))

SQL with DataSource

SELECT
  COALESCE(SUM(at_least_1_cta))
FROM (
  (
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
)
)

---

impressions

Firefox iOS appstore impressions

This is the number of unique impressions of firefox browser in iOS appstore. The etl of the base table is defined in bigquery-etl. This metric needs to be aggregated by first_seen_date (date column from the data recieved from appstore) for daily aggregation. The underlying table have a lag of 7 days. For questions please contact "rbaffourawuah@mozilla.com".

Data Source: appstore_funnel

Definition:

SUM(impressions)

SQL with DataSource

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

---

downloads

Firefox iOS appstore downloads

This is the total number of downloads of firefox browser in iOS appstore. The etl of the base table is defined in bigquery-etl. This metric needs to be aggregated by first_seen_date (date column from the data recieved from appstore) for daily aggregation. The underlying table have a lag of 7 days. For questions please contact "rbaffourawuah@mozilla.com".

Data Source: appstore_funnel

Definition:

SUM(total_downloads)

SQL with DataSource

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

---

funnel_new_profiles

Firefox iOS funnel new profiles

This is the total number of new profiles created on a given date. We only count new profiles that came via release channel and we also filter out app version 107.2 data that was recieved after February 1st. The etl of the base table is defined in bigquery-etl. This metric needs to be aggregated by first_seen_date for daily aggregation. The underlying table have a lag of 28 days, this means the most recent completed first seen date will be 28 days from current date. For questions please contact "rbaffourawuah@mozilla.com".

Data Source: funnel_retention

Definition:

SUM(new_profiles_metric_date)

SQL with DataSource

SELECT
  SUM(new_profiles_metric_date)
FROM (
  (
    SELECT *
    FROM `mozdata.firefox_ios.retention`
    WHERE metric_date = first_seen_date
)
)

---

repeat_users

Firefox iOS funnel repeat users

This is the total number of new profiles that visited more than once within their first 28 days. All the filters applied to new profile counts is applied to this calculation. The etl of the base table is defined in bigquery-etl. This metric needs to be aggregated by first_seen_date for daily aggregation. The underlying table have a lag of 28 days, this means the most recent completed first seen date will be 28 days from current date. For questions please contact "rbaffourawuah@mozilla.com".

Data Source: funnel_retention

Definition:

SUM(repeat_profiles)

SQL with DataSource

SELECT
  SUM(repeat_profiles)
FROM (
  (
    SELECT *
    FROM `mozdata.firefox_ios.retention`
    WHERE metric_date = first_seen_date
)
)

---

week_4_retained_users

Firefox iOS funnel week 4 retained users

This is the total number of new profiles that returned between between day 22 to day 28 after first seen. All the filters applied to new profile counts is applied to this calculation. The etl of the base table is defined in bigquery-etl. This metric needs to be aggregated by first_seen_date for daily aggregation. The underlying table have a lag of 28 days, this means the most recent completed first seen date will be 28 days from current date. For questions please contact "rbaffourawuah@mozilla.com".

Data Source: funnel_retention

Definition:

SUM(retained_week_4)

SQL with DataSource

SELECT
  SUM(retained_week_4)
FROM (
  (
    SELECT *
    FROM `mozdata.firefox_ios.retention`
    WHERE metric_date = first_seen_date
)
)

---

engagement_rate_v1

Firefox iOS Engagement Rate

The Engagement Rate is calculated as the ratio between DAU and MAU. For each day, we use the single-day DAU number and divide it by the MAU corresponding to the 28-day period ending on that day. For OKR reporting, we then calculate a 28-day moving average of this number. More information is provided on the New Profiles, Retention and Engagement Rate Confluence Page.

Data Source: firefox_ios_engagement_view

Definition:

SUM(dau) / SUM(mau)

SQL with DataSource

SELECT
  SUM(dau) / SUM(mau)
FROM (
  (
    SELECT *
     FROM `moz-fx-data-shared-prod.firefox_ios.engagement`
    WHERE is_mobile
)
)

---

retention_rate_v1

Firefox iOS Retention Rate

The Retention Rate is calculated as the proportion of clients that are active on the 4th week after the metric date.

Data Source: firefox_ios_retention_view

Definition:

SUM(retained_week_4) / SUM(active_metric_date)

SQL with DataSource

SELECT
  SUM(retained_week_4) / SUM(active_metric_date)
FROM (
  (
    SELECT *
     FROM `moz-fx-data-shared-prod.firefox_ios.retention`
    WHERE is_mobile
)
)

---

new_profile_retention_rate_v1

Firefox iOS New Proflie Retention Rate

The New Profile Retention Rate is calculated as the proportion of new profiles that are active on the 4th week after the metric date. More information is provided on the New Profiles, Retention and Engagement Rate Confluence Page.

Data Source: firefox_ios_retention_view

Definition:

SUM(retained_week_4_new_profiles) / SUM(new_profiles_metric_date)

SQL with DataSource

SELECT
  SUM(retained_week_4_new_profiles) / SUM(new_profiles_metric_date)
FROM (
  (
    SELECT *
     FROM `moz-fx-data-shared-prod.firefox_ios.retention`
    WHERE is_mobile
)
)

---

firefox_ios_share_button_tapped

Firefox iOS toolbar share button tapped

Number of times the toolbar share button is tapped.

Data Source: events

Definition:

COALESCE(SUM(CASE WHEN
          event.category = 'toolbar'
          AND event.name = 'share_button_tapped'
    THEN 1 ELSE 0 END
  ),0)

SQL with DataSource

SELECT
  COALESCE(SUM(CASE WHEN
          event.category = 'toolbar'
          AND event.name = 'share_button_tapped'
    THEN 1 ELSE 0 END
  ),0)
FROM (
  (
    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
)
)

---

firefox_ios_page_action_menu_share_page_with

Share page button in page action menu tapped

Number of times Share page button in page action menu is tapped.

Data Source: metrics

Definition:

COALESCE(SUM(metrics.counter.page_action_menu_share_page_with), 0)

SQL with DataSource

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

---