Fenix

metric-hub

Pre-defined data sources for fenix. 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 = 'Fenix'
)

---

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 Android'
)

---

new_profile_activation

Client ID column: None

Submission Date column: None

Definition:

`moz-fx-data-shared-prod.fenix.new_profile_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 event.name = "set_to_default_card" then DATE(submission_timestamp) END) as set_to_default_card
            , count(case when event.name = "turn_on_notifications_card" then DATE(submission_timestamp) END) as turn_on_notifications_card
            , count(case when event.name = "sign_in_card" then DATE(submission_timestamp) END) as sign_in_card
      FROM
        `mozdata.org_mozilla_firefox.events` tm
      CROSS JOIN
        UNNEST(events) AS event
      CROSS JOIN
        UNNEST(event.extra) AS ext
      WHERE event.category = "onboarding" AND ext.value ="impression" AND event.name in ("set_to_default_card", "turn_on_notifications_card", "sign_in_card")
      AND DATE(submission_timestamp) >= "2023-01-01"
      GROUP BY 1
      ) expo
LEFT JOIN (
  SELECT
      client_info.client_id as client_id
            , count(case when event.name = "set_to_default" then DATE(submission_timestamp) END) as set_to_default
            , count(case when event.name = "turn_on_notifications" then DATE(submission_timestamp) END) as turn_on_notifications
            , count(case when event.name = "sign_in" then DATE(submission_timestamp) END) as sign_in
  FROM
    `mozdata.org_mozilla_firefox.events` tm
  CROSS JOIN
     UNNEST(events) AS event
  CROSS JOIN
     UNNEST(event.extra) AS ext
  WHERE event.category = "onboarding" AND ext.key ="action" AND event.name in  ("set_to_default", "turn_on_notifications", "sign_in")
  AND DATE(submission_timestamp) >= "2023-01-01"
  GROUP BY 1
) conv
ON expo.client_id = conv.client_id
GROUP BY 1, 2, 3, 4, 5, 6
)

---

feature_usage_metrics_v1

Client ID column: NULL

Submission Date column: None

Definition:

(
    SELECT
        *
    FROM `mozdata.fenix.feature_usage_metrics` p
    LEFT JOIN (
        SELECT
        submission_date,
        SUM(dau) AS dau
        FROM `mozdata.telemetry.active_users_aggregates`
        WHERE app_name = 'Fenix'
        GROUP BY submission_date
    )
    USING(submission_date)
)

---