Firefox desktop
metric-hub
Pre-defined data sources for firefox_desktop. These data sources are defined in metric-hub
browser_launched_to_handle_events
Client ID column: None
Submission Date column: None
Definition:
(
SELECT
*
FROM `mozdata.telemetry.events`
WHERE event_category = 'browser.launched_to_handle'
)
firefox_desktop_active_users_aggregates_view
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 Desktop'
)
main
Client ID column: None
Submission Date column: None
Definition:
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
crash
Client ID column: None
Submission Date column: None
Definition:
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM mozdata.telemetry.crash
)
events_memory
Client ID column: None
Submission Date column: submission_date
Definition:
(
SELECT
*
FROM `moz-fx-data-shared-prod.telemetry.events`
WHERE
event_category = 'memory_watcher'
AND event_method = 'on_high_memory'
AND event_object = 'stats'
)
search_clients_engines_sources_daily
Client ID column: None
Submission Date column: submission_date
Definition:
mozdata.search.search_clients_engines_sources_daily
clients_daily
Client ID column: client_id
Submission Date column: submission_date
Definition:
mozdata.telemetry.clients_daily
clients_last_seen
Client ID column: client_id
Submission Date column: submission_date
Definition:
mozdata.telemetry.clients_last_seen
search_clients_daily
Client ID column: None
Submission Date column: None
Definition:
mozdata.search.search_clients_engines_sources_daily
main_summary
Client ID column: None
Submission Date column: None
Definition:
mozdata.telemetry.main_summary
events
Client ID column: None
Submission Date column: None
Definition:
mozdata.telemetry.events
normandy_events
Client ID column: None
Submission Date column: None
Definition:
(
SELECT
*
FROM mozdata.telemetry.events
WHERE event_category = 'normandy'
)
cfr
Client ID column: None
Submission Date column: None
Definition:
(
SELECT
*,
DATE(submission_timestamp) AS submission_date
FROM `moz-fx-data-derived-datasets`.messaging_system.cfr
)
activity_stream_events
Client ID column: None
Submission Date column: None
Definition:
(
SELECT
*,
DATE(submission_timestamp) AS submission_date
FROM mozdata.activity_stream.events
)
clients_first_seen_v2
Client ID column: None
Submission Date column: first_seen_date
Definition:
moz-fx-data-shared-prod.telemetry_derived.clients_first_seen_v2
urlbar_events
Client ID column: legacy_telemetry_client_id
Submission Date column: submission_date
Definition:
(
SELECT * FROM `moz-fx-data-shared-prod.firefox_desktop.urlbar_events`
)
urlbar_events_daily_engagement_by_product_result_type_v1
Client ID column: None
Submission Date column: submission_date
Definition:
(
SELECT * FROM `mozdata.firefox_desktop.urlbar_events_daily_engagement_by_product_result_type`
)
serp_events
Client ID column: legacy_telemetry_client_id
Submission Date column: submission_date
Definition:
mozdata.firefox_desktop.serp_events
metrics
Client ID column: metrics.uuid.legacy_telemetry_client_id
Submission Date column: None
Definition:
(
SELECT
p.*,
p.metrics.uuid.legacy_telemetry_profile_group_id as profile_group_id,
DATE(p.submission_timestamp) AS submission_date
FROM `mozdata.firefox_desktop.metrics` p
)
newtab_visits
Client ID column: None
Submission Date column: submission_date
Definition:
(
SELECT
*
FROM
`moz-fx-data-shared-prod.firefox_desktop.newtab_visits_daily`
)
newtab_clients_daily
Client ID column: client_id
Submission Date column: None
Definition:
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
clients_first_seen_28_days_later
Client ID column: client_id
Submission Date column: first_seen_date
Definition:
`moz-fx-data-shared-prod.telemetry.clients_first_seen_28_days_later`
desktop_cohort_daily_retention
Client ID column: NULL
Submission Date column: submission_date
Definition:
`moz-fx-data-shared-prod.telemetry.desktop_cohort_daily_retention`
desktop_engagement_view
Client ID column: NULL
Submission Date column: submission_date
Definition:
(
SELECT *
FROM `moz-fx-data-shared-prod.telemetry.desktop_engagement`
WHERE is_desktop
)
desktop_retention_view
Client ID column: NULL
Submission Date column: metric_date
Definition:
(
SELECT *
FROM `moz-fx-data-shared-prod.telemetry.desktop_retention`
WHERE is_desktop
)
glean_events_stream
Client ID column: legacy_telemetry_client_id
Submission Date column: None
Definition:
(
SELECT *, DATE(submission_timestamp) AS submission_date
FROM `moz-fx-data-shared-prod.firefox_desktop.events_stream`
)
glean_sap_events_stream
Client ID column: legacy_telemetry_client_id
Submission Date column: None
Definition:
(
SELECT
*, DATE(submission_timestamp) AS submission_date
FROM
`mozdata.firefox_desktop.events_stream`
WHERE
event_category = 'sap'
AND event_name = 'counts'
)
baseline_clients_daily
Client ID column: client_id
Submission Date column: None
Definition:
(
SELECT
p.*,
FROM `moz-fx-data-shared-prod.{dataset}.baseline_clients_daily` p
)
firefox_desktop_active_users_view
Client ID column: client_id
Submission Date column: submission_date
Definition:
(
SELECT *
FROM `moz-fx-data-shared-prod.telemetry.desktop_active_users`
WHERE is_desktop
)
firefox_desktop_baseline_active_users_view
Client ID column: client_id
Submission Date column: submission_date
Definition:
(
SELECT *
FROM `moz-fx-data-shared-prod.firefox_desktop.baseline_active_users`
WHERE is_desktop
)
site_breakage_events
Client ID column: None
Submission Date column: None
Definition:
(
SELECT
legacy_telemetry_client_id as client_id,
profile_group_id,
DATE(submission_timestamp) AS submission_date,
experiments,
event_name,
event_extra,
FROM `mozdata.firefox_desktop.events_stream`
WHERE
event_category = 'webcompatreporting'
)
protections_popup_events
Client ID column: None
Submission Date column: None
Definition:
(
SELECT
legacy_telemetry_client_id as client_id,
profile_group_id,
DATE(submission_timestamp) AS submission_date,
experiments,
event_name,
event_extra,
FROM `mozdata.firefox_desktop.events_stream`
WHERE
event_category = 'security.ui.protectionspopup'
)
form_autofill_forms
Client ID column: legacy_telemetry_client_id
Submission Date column: submission_date
Definition:
(
WITH autofill_events AS (
SELECT
client_id,
legacy_telemetry_client_id,
profile_group_id,
DATE(submission_timestamp) AS submission_date,
IF(event_category = 'creditcard', 'credit_card', 'address') AS form_type,
event_name,
event_extra,
-- autofill tags every event of one form interaction with a shared uuid
JSON_VALUE(event_extra, '$.value') AS flow,
-- only set on filled_modified_* events: the field the user edited
JSON_VALUE(event_extra, '$.field_name') AS modified_field
FROM `moz-fx-data-shared-prod.firefox_desktop.events_stream`
WHERE
event_category IN ('address', 'creditcard')
AND event_name IN (
'detected_address_form', 'detected_address_form_ext',
'filled_address_form', 'filled_address_form_ext',
'filled_modified_address_form',
'submitted_address_form', 'submitted_address_form_ext',
'detected_cc_form_v2', 'filled_cc_form_v2',
'filled_modified_cc_form_v2', 'submitted_cc_form_v2'
)
AND JSON_VALUE(event_extra, '$.value') IS NOT NULL
),
-- One row per (form interaction, field, event): what the detected_* event
-- said about the field, and what the filled_* event did to it. The address
-- events split their fields across a base and an `_ext` event, so a key
-- absent from one variant simply comes back NULL and is dropped.
field_signals AS (
SELECT
client_id,
legacy_telemetry_client_id,
profile_group_id,
submission_date,
form_type,
flow,
f.field AS field,
IF(event_name LIKE 'detected_%', f.status, NULL) AS detected_as,
IF(event_name LIKE 'filled_%', f.status, NULL) AS fill_status
FROM
autofill_events,
UNNEST([
STRUCT('name' AS field, JSON_VALUE(event_extra, '$.name') AS status),
STRUCT('given-name', JSON_VALUE(event_extra, '$.given_name')),
STRUCT('family-name', JSON_VALUE(event_extra, '$.family_name')),
STRUCT('additional-name', JSON_VALUE(event_extra, '$.additional_name')),
STRUCT('organization', JSON_VALUE(event_extra, '$.organization')),
STRUCT('street-address', JSON_VALUE(event_extra, '$.street_address')),
STRUCT('address-line1', JSON_VALUE(event_extra, '$.address_line1')),
STRUCT('address-line2', JSON_VALUE(event_extra, '$.address_line2')),
STRUCT('address-line3', JSON_VALUE(event_extra, '$.address_line3')),
STRUCT('address-level1', JSON_VALUE(event_extra, '$.address_level1')),
STRUCT('address-level2', JSON_VALUE(event_extra, '$.address_level2')),
STRUCT('postal-code', JSON_VALUE(event_extra, '$.postal_code')),
STRUCT('country', JSON_VALUE(event_extra, '$.country')),
STRUCT('email', JSON_VALUE(event_extra, '$.email')),
STRUCT('tel', JSON_VALUE(event_extra, '$.tel'))
]) AS f
WHERE
event_name IN (
'detected_address_form', 'detected_address_form_ext',
'filled_address_form', 'filled_address_form_ext'
)
AND f.status IS NOT NULL
UNION ALL
SELECT
client_id,
legacy_telemetry_client_id,
profile_group_id,
submission_date,
form_type,
flow,
f.field,
IF(event_name = 'detected_cc_form_v2', f.status, NULL),
IF(event_name = 'filled_cc_form_v2', f.status, NULL)
FROM
autofill_events,
UNNEST([
STRUCT('cc-name' AS field, JSON_VALUE(event_extra, '$.cc_name') AS status),
STRUCT('cc-number', JSON_VALUE(event_extra, '$.cc_number')),
STRUCT('cc-type', JSON_VALUE(event_extra, '$.cc_type')),
STRUCT('cc-exp', JSON_VALUE(event_extra, '$.cc_exp')),
STRUCT('cc-exp-month', JSON_VALUE(event_extra, '$.cc_exp_month')),
STRUCT('cc-exp-year', JSON_VALUE(event_extra, '$.cc_exp_year'))
]) AS f
WHERE
event_name IN ('detected_cc_form_v2', 'filled_cc_form_v2')
AND f.status IS NOT NULL
UNION ALL
-- A modified event names the single field the user edited, using a finer
-- vocabulary than the detected/filled events report. Fold each edit onto
-- the label(s) those events use, so the edit can cancel out the fill.
SELECT
client_id,
legacy_telemetry_client_id,
profile_group_id,
submission_date,
form_type,
flow,
field,
NULL,
'modified'
FROM
autofill_events,
UNNEST(
CASE
-- Sub-inputs of a split street address. Autofill writes the
-- street into exactly one of these two and they co-occur in
-- ~1% of forms, so credit both: naming a field that was not
-- auto-filled is a no-op, since `modified` is only ever read
-- for fields that were detected and filled.
WHEN modified_field IN ('address-housenumber', 'address-extra-housesuffix')
THEN ['street-address', 'address-line1']
-- Same field, reported by name instead of by code.
WHEN modified_field = 'country-name' THEN ['country']
-- tel-national, tel-area-code, ... all roll up to tel.
WHEN modified_field LIKE 'tel%' THEN ['tel']
-- Components of the cardholder name.
WHEN modified_field IN ('cc-given-name', 'cc-family-name', 'cc-additional-name')
THEN ['cc-name']
ELSE [modified_field]
END
) AS field
WHERE event_name IN ('filled_modified_address_form', 'filled_modified_cc_form_v2')
),
-- Collapse to one row per (form interaction, field). A field can be named by
-- several events within one interaction and still counts only once.
per_field AS (
SELECT
client_id,
legacy_telemetry_client_id,
profile_group_id,
submission_date,
form_type,
flow,
field,
-- A detected_* event reports, per field, how the field's type was
-- established: 'false' = field not on the form, 'true' = explicit
-- autocomplete attribute, '0' = regex heuristic, a number = Fathom
-- confidence, 'ml' = ML field classifier. Everything except
-- 'false'/'true' means Firefox inferred the type itself.
COALESCE(LOGICAL_OR(
detected_as = 'ml' OR SAFE_CAST(detected_as AS FLOAT64) IS NOT NULL
), FALSE) AS detected,
COALESCE(LOGICAL_OR(fill_status = 'filled'), FALSE) AS autofilled,
COALESCE(LOGICAL_OR(fill_status = 'modified'), FALSE) AS modified
FROM field_signals
GROUP BY
client_id, legacy_telemetry_client_id, profile_group_id,
submission_date, form_type, flow, field
),
per_form AS (
SELECT
client_id,
legacy_telemetry_client_id,
profile_group_id,
submission_date,
form_type,
flow,
COUNTIF(detected) AS detected_fields,
COUNTIF(detected AND autofilled) AS autofilled_fields,
COUNTIF(detected AND autofilled AND modified) AS modified_fields,
COUNTIF(detected AND autofilled AND NOT modified) AS net_kept_fields
FROM per_field
GROUP BY
client_id, legacy_telemetry_client_id, profile_group_id,
submission_date, form_type, flow
),
submissions AS (
SELECT DISTINCT
client_id,
legacy_telemetry_client_id,
profile_group_id,
submission_date,
form_type,
flow
FROM autofill_events
WHERE event_name IN (
'submitted_address_form', 'submitted_address_form_ext', 'submitted_cc_form_v2'
)
)
-- A form can be submitted on a day its detected_* event did not land on
-- (interaction spanning midnight), and a form can be submitted having had
-- no inferred fields at all, so the two sides are unioned rather than
-- joined. An outer join here would also stop BigQuery pushing the caller's
-- submission_date filter down to the events_stream partition column.
SELECT
client_id,
legacy_telemetry_client_id,
profile_group_id,
submission_date,
form_type,
flow,
SUM(detected_fields) AS detected_fields,
SUM(autofilled_fields) AS autofilled_fields,
SUM(modified_fields) AS modified_fields,
SUM(net_kept_fields) AS net_kept_fields,
LOGICAL_OR(submitted) AS form_submitted
FROM (
SELECT
client_id, legacy_telemetry_client_id, profile_group_id,
submission_date, form_type, flow,
detected_fields, autofilled_fields, modified_fields, net_kept_fields,
FALSE AS submitted
FROM per_form
UNION ALL
SELECT
client_id, legacy_telemetry_client_id, profile_group_id,
submission_date, form_type, flow,
0, 0, 0, 0,
TRUE
FROM submissions
)
GROUP BY
client_id, legacy_telemetry_client_id, profile_group_id,
submission_date, form_type, flow
)