Firefox desktop
metric-hub
Pre-defined metrics for firefox_desktop. These metrics are defined in metric-hub
active_hours
Active hours
Measures the amount of time (in 5-second increments) during which Firefox received user input from a keyboard or mouse. The Firefox window does not need to be focused.
Data Source: clients_daily
Definition:
{{agg_sum("active_hours_sum")}}
SQL with DataSource
SELECT
COALESCE(SUM(active_hours_sum), 0)
FROM (
mozdata.telemetry.clients_daily
)
active_hours_glean
Active hours in Glean
Measures the amount of time (in 5-second increments) during which Firefox received user input from a keyboard or mouse. The Firefox window does not need to be focused.
Data Source: baseline_clients_daily
Definition:
{{agg_sum("browser_engagement_active_ticks/(3600/5)")}}
SQL with DataSource
SELECT
COALESCE(SUM(browser_engagement_active_ticks/(3600/5)), 0)
FROM (
(
SELECT
p.*,
FROM `moz-fx-data-shared-prod.{dataset}.baseline_clients_daily` p
)
)
uri_count
URIs visited
Counts the total number of URIs visited. Includes within-page navigation events (e.g. to anchors).
Data Source: clients_daily
Definition:
{{agg_sum("scalar_parent_browser_engagement_total_uri_count_sum")}}
SQL with DataSource
SELECT
COALESCE(SUM(scalar_parent_browser_engagement_total_uri_count_sum), 0)
FROM (
mozdata.telemetry.clients_daily
)
uri_count_v2
URIs visited
Counts the total number of URIs visited for both normal and private mode. Includes within-page navigation events (e.g. to anchors).
Data Source: clients_daily
Definition:
{{agg_sum("scalar_parent_browser_engagement_total_uri_count_normal_and_private_mode_sum")}}
SQL with DataSource
SELECT
COALESCE(SUM(scalar_parent_browser_engagement_total_uri_count_normal_and_private_mode_sum), 0)
FROM (
mozdata.telemetry.clients_daily
)
uri_count_v2_glean
URIs visited in Glean
Counts the total number of URIs visited for both normal and private mode. Includes within-page navigation events (e.g. to anchors).
Data Source: baseline_clients_daily
Definition:
{{agg_sum("COALESCE(browser_engagement_uri_count,0)")}}
SQL with DataSource
SELECT
COALESCE(SUM(COALESCE(browser_engagement_uri_count,0)), 0)
FROM (
(
SELECT
p.*,
FROM `moz-fx-data-shared-prod.{dataset}.baseline_clients_daily` p
)
)
search_count
SAP searches in Glean
Counts the number of searches a user performed through Firefox's Search Access Points. Learn more in the search data documentation.
Data Source: search_clients_engines_sources_daily
Definition:
{{agg_sum("sap")}}
SQL with DataSource
SELECT
COALESCE(SUM(sap), 0)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
search_count_glean
SAP searches in Glean
Counts the number of searches a user performed through Firefox's Search Access Points. Learn more in the search data documentation.
Data Source: glean_sap_events_stream
Definition:
COUNT(*)
SQL with DataSource
SELECT
COUNT(*)
FROM (
(
SELECT
*, DATE(submission_timestamp) AS submission_date
FROM
`mozdata.firefox_desktop.events_stream`
WHERE
event_category = 'sap'
AND event_name = 'counts'
)
)
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: search_clients_engines_sources_daily
Definition:
{{agg_sum("tagged_sap")}}
SQL with DataSource
SELECT
COALESCE(SUM(tagged_sap), 0)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
tagged_search_count_glean
Tagged SAP searches in Glean
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: serp_events
Definition:
COUNTIF(is_tagged)
SQL with DataSource
SELECT
COUNTIF(is_tagged)
FROM (
mozdata.firefox_desktop.serp_events
)
tagged_follow_on_search_count
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: search_clients_engines_sources_daily
Definition:
{{agg_sum("tagged_follow_on")}}
SQL with DataSource
SELECT
COALESCE(SUM(tagged_follow_on), 0)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
tagged_follow_on_search_count_glean
Tagged follow-on searches in Glean
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: serp_events
Definition:
COUNTIF((is_tagged = TRUE) AND (sap_source = "follow_on_from_refine_on_incontent_search" OR sap_source = "follow_on_from_refine_on_SERP"))
SQL with DataSource
SELECT
COUNTIF((is_tagged = TRUE) AND (sap_source = "follow_on_from_refine_on_incontent_search" OR sap_source = "follow_on_from_refine_on_SERP"))
FROM (
mozdata.firefox_desktop.serp_events
)
ad_clicks
Ad clicks
Counts clicks on ads on search engine result pages with a Mozilla partner tag.
Data Source: search_clients_engines_sources_daily
Definition:
{{agg_sum("ad_click")}}
SQL with DataSource
SELECT
COALESCE(SUM(ad_click), 0)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
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: search_clients_engines_sources_daily
Definition:
{{agg_sum("search_with_ads")}}
SQL with DataSource
SELECT
COALESCE(SUM(search_with_ads), 0)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
searches_with_ads_glean
Search result pages with ads in Glean
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: serp_events
Definition:
COUNTIF(is_tagged = TRUE AND num_ads_visible > 0)
SQL with DataSource
SELECT
COUNTIF(is_tagged = TRUE AND num_ads_visible > 0)
FROM (
mozdata.firefox_desktop.serp_events
)
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: search_clients_engines_sources_daily
Definition:
{{agg_sum("organic")}}
SQL with DataSource
SELECT
COALESCE(SUM(organic), 0)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
organic_search_count_glean
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: serp_events
Definition:
COUNTIF(is_tagged = FALSE)
SQL with DataSource
SELECT
COUNTIF(is_tagged = FALSE)
FROM (
mozdata.firefox_desktop.serp_events
)
search_with_ads_organic
Organic searches with ads
Counts search result pages from organic searches served with advertising. Organic searches are not performed through a Firefox SAP and are not monetizable. Users may not actually see these ads thanks to e.g. ad-blockers. Learn more in the search analysis documentation.
Data Source: search_clients_engines_sources_daily
Definition:
{{agg_sum("search_with_ads_organic")}}
SQL with DataSource
SELECT
COALESCE(SUM(search_with_ads_organic), 0)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
ad_clicks_organic
Organic ad clicks
Counts clicks on ads on search engine result pages organic searches. Organic searches are not performed through a Firefox SAP and are not monetizable.
Data Source: search_clients_engines_sources_daily
Definition:
{{agg_sum("ad_click_organic")}}
SQL with DataSource
SELECT
COALESCE(SUM(ad_click_organic), 0)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
unenroll
Unenrollments
Counts the number of clients with an experiment unenrollment event.
Data Source: normandy_events
Definition:
{{agg_any(
""" event_category = 'normandy'
AND event_method = 'unenroll'
AND event_string_value = '{experiment_slug}'
""")}}
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR( event_category = 'normandy'
AND event_method = 'unenroll'
AND event_string_value = '{experiment_slug}'
), FALSE)
FROM (
(
SELECT
*
FROM mozdata.telemetry.events
WHERE event_category = 'normandy'
)
)
notification_clicks
System Notification Clicks
Counts the number of clients that launched Firefox to handle the notification corresponding to this experiment.
Data Source: browser_launched_to_handle_events
Definition:
{{agg_any(
""" event_method = 'system_notification'
AND event_object = 'toast'
AND STARTS_WITH(mozfun.map.get_key(event_map_values, 'name'), '{experiment_slug}:')
""")}}
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR( event_method = 'system_notification'
AND event_object = 'toast'
AND STARTS_WITH(mozfun.map.get_key(event_map_values, 'name'), '{experiment_slug}:')
), FALSE)
FROM (
(
SELECT
*
FROM `mozdata.telemetry.events`
WHERE event_category = 'browser.launched_to_handle'
)
)
view_about_logins
about:logins viewers
Counts the number of clients that viewed about:logins.
Data Source: events
Definition:
{{agg_any(
""" event_method = 'open_management'
AND event_category = 'pwmgr'
""")}}
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR( event_method = 'open_management'
AND event_category = 'pwmgr'
), FALSE)
FROM (
mozdata.telemetry.events
)
view_about_protections
about:protections viewers
Counts the number of clients that viewed about:protections.
Data Source: events
Definition:
{{agg_any(
""" event_method = 'show'
AND event_object = 'protection_report'
""")}}
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR( event_method = 'show'
AND event_object = 'protection_report'
), FALSE)
FROM (
mozdata.telemetry.events
)
connect_fxa
Connected FxA
Counts the number of clients that took action to connect to FxA. This does not include clients that were already connected to FxA at the start of the experiment and remained connected.
Data Source: events
Definition:
{{agg_any(
""" event_method = 'connect'
AND event_object = 'account'
""")}}
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR( event_method = 'connect'
AND event_object = 'account'
), FALSE)
FROM (
mozdata.telemetry.events
)
pocket_rec_clicks
Clicked Pocket organic recs in New Tab
Counts the number of Pocket rec clicks made by each client.
Data Source: activity_stream_events
Definition:
COUNTIF(
event = 'CLICK'
AND source = 'CARDGRID'
AND JSON_EXTRACT_SCALAR(value, '$.card_type') = 'organic'
)
SQL with DataSource
SELECT
COUNTIF(
event = 'CLICK'
AND source = 'CARDGRID'
AND JSON_EXTRACT_SCALAR(value, '$.card_type') = 'organic'
)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date
FROM mozdata.activity_stream.events
)
)
pocket_spoc_clicks
Clicked Pocket sponsored content in New Tab
Counts the number of Pocket sponsored content clicks made by each client.
Data Source: activity_stream_events
Definition:
COUNTIF(
event = 'CLICK'
AND source = 'CARDGRID'
AND JSON_EXTRACT_SCALAR(value, '$.card_type') = 'spoc'
)
SQL with DataSource
SELECT
COUNTIF(
event = 'CLICK'
AND source = 'CARDGRID'
AND JSON_EXTRACT_SCALAR(value, '$.card_type') = 'spoc'
)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date
FROM mozdata.activity_stream.events
)
)
days_of_use
Days of use
The number of days in the interval that each client sent a main ping.
Data Source: clients_daily
Definition:
COUNT(submission_date)
SQL with DataSource
SELECT
COUNT(submission_date)
FROM (
mozdata.telemetry.clients_daily
)
days_of_use_glean
Days of use in Glean
The number of days in the interval that each client sent a baseline ping.
Data Source: baseline_clients_daily
Definition:
COUNT(submission_date)
SQL with DataSource
SELECT
COUNT(submission_date)
FROM (
(
SELECT
p.*,
FROM `moz-fx-data-shared-prod.{dataset}.baseline_clients_daily` p
)
)
qualified_cumulative_days_of_use
QCDOU
The number of days in the interval that each client sent a main ping, given that the client had >0 active hours and >0 URIs loaded.
Data Source: clients_daily
Definition:
COUNTIF(
active_hours_sum > 0 AND
scalar_parent_browser_engagement_total_uri_count_normal_and_private_mode_sum > 0
)
SQL with DataSource
SELECT
COUNTIF(
active_hours_sum > 0 AND
scalar_parent_browser_engagement_total_uri_count_normal_and_private_mode_sum > 0
)
FROM (
mozdata.telemetry.clients_daily
)
qualified_cumulative_days_of_use_glean
QCDOU in Glean
The number of days in the interval that each client sent a baseline ping, given that the client had >0 active hours and >0 URIs loaded.
Data Source: baseline_clients_daily
Definition:
COUNTIF(
COALESCE(browser_engagement_active_ticks, 0) > 0 AND
COALESCE(browser_engagement_uri_count,0) > 0
)
SQL with DataSource
SELECT
COUNTIF(
COALESCE(browser_engagement_active_ticks, 0) > 0 AND
COALESCE(browser_engagement_uri_count,0) > 0
)
FROM (
(
SELECT
p.*,
FROM `moz-fx-data-shared-prod.{dataset}.baseline_clients_daily` p
)
)
daily_active_users
DAU
The number of unique clients with >0 active hours and >0 URIs loaded that
we received a main ping from each day. 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: clients_daily
Definition:
COUNTIF(active_hours_sum > 0 AND total_uri_count > 0)
SQL with DataSource
SELECT
COUNTIF(active_hours_sum > 0 AND total_uri_count > 0)
FROM (
mozdata.telemetry.clients_daily
)
daily_active_users_v2
Firefox Desktop 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 Desktop.
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_desktop_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 Desktop'
)
)
client_level_daily_active_users_v1
Firefox Desktop 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: clients_daily
Definition:
COUNTIF(active_hours_sum > 0 AND total_uri_count > 0 AND isp_name <> 'BrowserStack')
SQL with DataSource
SELECT
COUNTIF(active_hours_sum > 0 AND total_uri_count > 0 AND isp_name <> 'BrowserStack')
FROM (
mozdata.telemetry.clients_daily
)
client_level_daily_active_users_v2
Firefox Desktop 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_desktop_active_users_view
Definition:
COUNTIF(is_dau)
SQL with DataSource
SELECT
COUNTIF(is_dau)
FROM (
(
SELECT *
FROM `moz-fx-data-shared-prod.telemetry.desktop_active_users`
WHERE is_desktop
)
)
daily_active_users_per_1000_clients_legacy
DAU per 1,000 clients
This metric uses our canonical, supported definition of Daily Active Users (DAU), expressed in a format suited for use in experiments. In an experimental comparison, it describes the additional (incremental) DAU seen on each day from a treatment.
The units are expressed in terms of thousands of clients enrolled or exposed, so the effect can be scaled to either the observed
or expected rollout population as needed to estimate absolute DAU impact.
Effects are averaged to a per-day basis over each analysis period. Since feature changes often show a strong "novelty effect", this
metric is best interpreted over Week 4 or later, in order to better estimate what the lasting steady-state effects are.
Data Source: firefox_desktop_active_users_view
Definition:
COUNTIF(is_dau) * 1000
SQL with DataSource
SELECT
COUNTIF(is_dau) * 1000
FROM (
(
SELECT *
FROM `moz-fx-data-shared-prod.telemetry.desktop_active_users`
WHERE is_desktop
)
)
client_level_daily_active_users_v2_glean
Firefox Desktop Client-Level DAU in Glean
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_desktop_baseline_active_users_view
Definition:
COUNTIF(is_dau)
SQL with DataSource
SELECT
COUNTIF(is_dau)
FROM (
(
SELECT *
FROM `moz-fx-data-shared-prod.firefox_desktop.baseline_active_users`
WHERE is_desktop
)
)
daily_active_users_per_1000_clients
DAU per 1,000 clients (Glean)
This metric uses our canonical, supported definition of Daily Active Users (DAU), expressed in a format suited for use in experiments. In an experimental comparison, it describes the additional (incremental) DAU seen on each day from a treatment.
The units are expressed in terms of thousands of clients enrolled or exposed, so the effect can be scaled to either the observed
or expected rollout population as needed to estimate absolute DAU impact.
Effects are averaged to a per-day basis over each analysis period. Since feature changes often show a strong "novelty effect", this
metric is best interpreted over Week 4 or later, in order to better estimate what the lasting steady-state effects are.
Data Source: firefox_desktop_baseline_active_users_view
Definition:
COUNTIF(is_dau) * 1000
SQL with DataSource
SELECT
COUNTIF(is_dau) * 1000
FROM (
(
SELECT *
FROM `moz-fx-data-shared-prod.firefox_desktop.baseline_active_users`
WHERE is_desktop
)
)
desktop_dau_kpi
Desktop DAU KPI
The average number of unique clients with >0 active hours and >0 URIs loaded that
we received a main ping from each day in the 28-day period ending on December 15th.
To reconstruct the annual Desktop DAU KPI, this metric needs to be aggregated by
EXTRACT(YEAR FROM submission_date).
For questions, please contact bochocki@mozilla.com or firefox-kpi@mozilla.com.
Data Source: clients_daily
Definition:
COUNTIF(
active_hours_sum > 0 AND
total_uri_count > 0 AND
FORMAT_DATE('%m-%d', submission_date) BETWEEN '11-18' AND '12-15'
) / 28
SQL with DataSource
SELECT
COUNTIF(
active_hours_sum > 0 AND
total_uri_count > 0 AND
FORMAT_DATE('%m-%d', submission_date) BETWEEN '11-18' AND '12-15'
) / 28
FROM (
mozdata.telemetry.clients_daily
)
desktop_dau_kpi_v2
Firefox Desktop DAU KPI
The average Firefox Desktop DAU
in the 28-day period ending on December 15th. This is the official Desktop DAU KPI reporting definition. The logic is
detailed on the Confluence DAU page
and is automatically cross-checked, actively monitored, and change controlled.
To reconstruct the annual Desktop DAU KPI, this metric needs to be aggregated by
EXTRACT(YEAR FROM submission_date).
Data Source: firefox_desktop_active_users_aggregates_view
Definition:
SUM(IF(FORMAT_DATE('%m-%d', submission_date) BETWEEN '11-18' AND '12-15', dau, 0)) / 28
SQL with DataSource
SELECT
SUM(IF(FORMAT_DATE('%m-%d', submission_date) BETWEEN '11-18' AND '12-15', dau, 0)) / 28
FROM (
(
SELECT *
FROM `moz-fx-data-shared-prod.telemetry.active_users_aggregates`
WHERE app_name = 'Firefox Desktop'
)
)
disable_pocket_clicks
Disabled Pocket in New Tab
Counts the number of clicks to disable Pocket in New Tab made by each client.
Data Source: activity_stream_events
Definition:
COUNTIF(
event = 'PREF_CHANGED'
AND source = 'TOP_STORIES'
AND JSON_EXTRACT_SCALAR(value, '$.status') = 'false'
)
SQL with DataSource
SELECT
COUNTIF(
event = 'PREF_CHANGED'
AND source = 'TOP_STORIES'
AND JSON_EXTRACT_SCALAR(value, '$.status') = 'false'
)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date
FROM mozdata.activity_stream.events
)
)
disable_pocket_spocs_clicks
Disabled Pocket sponsored content in New Tab
Counts the number of clicks to disable Pocket sponsored content in New Tab made by each client.
Data Source: activity_stream_events
Definition:
COUNTIF(
event = 'PREF_CHANGED'
AND source = 'POCKET_SPOCS'
AND JSON_EXTRACT_SCALAR(value, '$.status') = 'false'
)
SQL with DataSource
SELECT
COUNTIF(
event = 'PREF_CHANGED'
AND source = 'POCKET_SPOCS'
AND JSON_EXTRACT_SCALAR(value, '$.status') = 'false'
)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date
FROM mozdata.activity_stream.events
)
)
content_shutdown_crashes
Content Shutdown Crashes
Number of Content Shutdown Crashes
Data Source: crash
Definition:
SUM(IF(REGEXP_CONTAINS(payload.process_type, 'content') AND REGEXP_CONTAINS(payload.metadata.ipc_channel_error, 'ShutDownKill'), 1, 0))
SQL with DataSource
SELECT
SUM(IF(REGEXP_CONTAINS(payload.process_type, 'content') AND REGEXP_CONTAINS(payload.metadata.ipc_channel_error, 'ShutDownKill'), 1, 0))
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM mozdata.telemetry.crash
)
)
content_crashes
Content Crashes
Number of Content Crashes
Data Source: crash
Definition:
SUM(IF(REGEXP_CONTAINS(payload.process_type, 'content') AND NOT REGEXP_CONTAINS(COALESCE(payload.metadata.ipc_channel_error, ''), 'ShutDownKill'), 1, 0))
SQL with DataSource
SELECT
SUM(IF(REGEXP_CONTAINS(payload.process_type, 'content') AND NOT REGEXP_CONTAINS(COALESCE(payload.metadata.ipc_channel_error, ''), 'ShutDownKill'), 1, 0))
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM mozdata.telemetry.crash
)
)
shutdown_hangs
Shutdown Hangs
Number of Shutdown Hangs
Data Source: crash
Definition:
SUM(IF(payload.metadata.moz_crash_reason LIKE r'MOZ\_CRASH%', 1, 0))
SQL with DataSource
SELECT
SUM(IF(payload.metadata.moz_crash_reason LIKE r'MOZ\_CRASH%', 1, 0))
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM mozdata.telemetry.crash
)
)
oom_crashes
OOM Crashes
Number of Out of Memory Crashes
Data Source: crash
Definition:
SUM(IF(payload.metadata.oom_allocation_size IS NOT NULL, 1, 0))
SQL with DataSource
SELECT
SUM(IF(payload.metadata.oom_allocation_size IS NOT NULL, 1, 0))
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM mozdata.telemetry.crash
)
)
main_crashes
Main Crashes
Number of Main Crashes
Data Source: crash
Definition:
SUM(IF(payload.process_type = 'main' OR payload.process_type IS NULL, 1, 0))
SQL with DataSource
SELECT
SUM(IF(payload.process_type = 'main' OR payload.process_type IS NULL, 1, 0))
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM mozdata.telemetry.crash
)
)
startup_crashes
Startup Crashes
Number of Startup Crashes
Data Source: crash
Definition:
SUM(IF(payload.metadata.startup_crash = '1', 1, 0))
SQL with DataSource
SELECT
SUM(IF(payload.metadata.startup_crash = '1', 1, 0))
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM mozdata.telemetry.crash
)
)
gmplugin_crashes
Gmplugin Crashes
Number of GMPlugin (for loading DRM media in a highly sandboxed process) Crashes
Data Source: main
Definition:
SUM(COALESCE(`moz-fx-data-shared-prod`.udf.keyed_histogram_get_sum(payload.keyed_histograms.subprocess_crashes_with_dump,'gmplugin'), 0))
SQL with DataSource
SELECT
SUM(COALESCE(`moz-fx-data-shared-prod`.udf.keyed_histogram_get_sum(payload.keyed_histograms.subprocess_crashes_with_dump,'gmplugin'), 0))
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gpu_crashes
Gpu Crashes
Number of GPU Crashes
Data Source: main
Definition:
SUM(COALESCE(`moz-fx-data-shared-prod`.udf.keyed_histogram_get_sum(payload.keyed_histograms.subprocess_crashes_with_dump, 'gpu'), 0))
SQL with DataSource
SELECT
SUM(COALESCE(`moz-fx-data-shared-prod`.udf.keyed_histogram_get_sum(payload.keyed_histograms.subprocess_crashes_with_dump, 'gpu'), 0))
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
plugin_crashes
Plugin Crashes
Number of Plugin Crashes
Data Source: main
Definition:
SUM(COALESCE(`moz-fx-data-shared-prod`.udf.keyed_histogram_get_sum(payload.keyed_histograms.subprocess_crashes_with_dump, 'plugin'), 0))
SQL with DataSource
SELECT
SUM(COALESCE(`moz-fx-data-shared-prod`.udf.keyed_histogram_get_sum(payload.keyed_histograms.subprocess_crashes_with_dump, 'plugin'), 0))
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
child_process_launch_ms
Child Process Launch Ms
Time spent in the generic child process launching code, which is run off-main-thread and used by all child process types
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.child_process_launch_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.child_process_launch_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
content_process_count
Content Process Count
A count of the number of content processes, collected every five minutes.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.content_process_count) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.content_process_count) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
content_process_max
Content Process Max
The maximum number of content processes, submitted when a new maximum is reached.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.content_process_max) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.content_process_max) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
checkerboard_severity
Checkerboard Severity
Opaque measure of the severity of a checkerboard event
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.checkerboard_severity) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.checkerboard_severity) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
content_frame_time_vsync
Content Frame Time Vsync
The time, in percentage of a vsync interval, spent from the vsync that started a paint in the content process until that frame is presented in the compositor.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.content_frame_time_vsync) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.content_frame_time_vsync) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
fx_urlbar_quick_suggest_remote_settings_latency_ms
Firefox Suggest Remote Settings Latency Ms
The time from when Firefox starts fetching a suggestion from remote settings to the time the suggestion is retrieved.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.fx_urlbar_quick_suggest_remote_settings_latency_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.fx_urlbar_quick_suggest_remote_settings_latency_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
fx_new_window_ms
Fx New Window Ms
Firefox: Time taken to open a new browser window (ms)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.fx_new_window_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.fx_new_window_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
fx_tab_switch_composite_e10s_ms
Fx Tab Switch Composite E10S Ms
Firefox: Time in ms between tab selection and first composite of the tab content in e10s windows
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.fx_tab_switch_composite_e10s_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.fx_tab_switch_composite_e10s_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
fx_tab_switch_total_e10s_ms
Fx Tab Switch Total E10S Ms
Firefox: Time in ms between tab selection and tab content paint in e10s windows
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.fx_tab_switch_total_e10s_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.fx_tab_switch_total_e10s_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
timestamps_first_paint_two
Timestamps First Paint Two
Record the timestamp of the first main window paint, in milliseconds since process start. Intended to replace first_paint since first_paint is broken.
Data Source: main
Definition:
SUM(payload.processes.parent.scalars.timestamps_first_paint_two)
SQL with DataSource
SELECT
SUM(payload.processes.parent.scalars.timestamps_first_paint_two)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
timestamps_about_home_topsites_first_paint
Timestamps About Home Topsites First Paint
Record the timestamp of when the first about:home's Topsites are painted. Only records if about:home is set as the default homepage, and if sessions are not being restored by default.
Data Source: main
Definition:
SUM(payload.processes.parent.scalars.timestamps_about_home_topsites_first_paint)
SQL with DataSource
SELECT
SUM(payload.processes.parent.scalars.timestamps_about_home_topsites_first_paint)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
keypress_present_latency_ms
Keypress Present Latency Ms
Time between receiving a keypress event on the event loop and compositing its result onto the screen (ms)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.gpu.histograms.keypress_present_latency) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.gpu.histograms.keypress_present_latency) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
scroll_present_latency
Scroll Present Latency
Time between receiving a scroll event on the event loop and compositing its result onto the screen (ms)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.gpu.histograms.scroll_present_latency) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.gpu.histograms.scroll_present_latency) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
memory_total
Memory Total
Total Memory Across All Processes (KB)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.memory_total) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.memory_total) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
cycle_collector_max_pause
Cycle Collector Max Pause
Longest pause for an individual slice of one cycle collection, including preparation (ms)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.cycle_collector_max_pause) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.cycle_collector_max_pause) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
cycle_collector_max_pause_content
Cycle Collector Max Pause Content
Longest pause for an individual slice of one cycle collection, including preparation (ms)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.cycle_collector_max_pause) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.cycle_collector_max_pause) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
cycle_collector_visited_gced
Cycle Collector Visited GCED
Number of JS objects visited by the cycle collector
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.cycle_collector_visited_gced) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.cycle_collector_visited_gced) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
cycle_collector_visited_ref_counted
Cycle Collector Visited Ref Counted
Number of ref counted objects visited by the cycle collector
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.cycle_collector_visited_ref_counted) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.cycle_collector_visited_ref_counted) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_max_pause_2
Gc Max Pause 2
Longest GC slice in a GC (ms)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_max_pause_ms_2) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_max_pause_ms_2) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_max_pause_2_content
Gc Max Pause 2 Content
Longest GC slice in a GC (ms)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_max_pause_ms_2) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_max_pause_ms_2) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_ms
Gc Ms
Time spent running JS GC (ms)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_ms_content
Gc Ms Content
Time spent running JS GC (ms) in the content process
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_slice_during_idle
Gc Slice During Idle
Percent of GC slice done during idle time
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_slice_during_idle) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_slice_during_idle) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_slice_during_idle_content
Gc Slice During Idle Content
Percent of GC slice done during idle time in the content process
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_slice_during_idle) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_slice_during_idle) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_non_incremental
Gc Non Incremental
Was the GC non-incremental?
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_non_incremental) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_non_incremental) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_reason_2
Gc Reason 2
Reason (enum value) for initiating a GC
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_reason_2) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_reason_2) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_mark_rate_2
Gc Mark Rate 2
The number of objects marked per ms during GC.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_mark_rate_2) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.gc_mark_rate_2) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
gc_budget_overrun
Gc Budget Overrun
How long a GC slice ran over its budget in microseconds
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_budget_overrun) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.gc_budget_overrun) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
memory_unique_content_startup
Memory Unique Content Startup
Unique Set Size of Content Process at Startup (KB)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.memory_unique_content_startup) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.memory_unique_content_startup) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
perf_first_contentful_paint_ms
Perf First Contentful Paint Ms
The time between navigationStart and the first contentful paint of a foreground http or https root content document, in milliseconds. The contentful paint timestamp is taken during display list building and does not include rasterization or compositing of that paint.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_first_contentful_paint_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_first_contentful_paint_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
time_to_first_interaction_ms
Time To First Interaction Ms
Time in milliseconds from the first non-blank paint to the creation time of the next click, key, mouse or scroll event per top-level content browsing context.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.time_to_first_interaction_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.time_to_first_interaction_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
perf_page_load_time_ms
Perf Page Load Time Ms
Time in milliseconds from navigationStart to loadEventStart for the foreground http or https root content document
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_page_load_time_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_page_load_time_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
input_event_response_coalesced
Input Event Response Coalesced
Time (ms) from the Input event being created to the end of it being handled, but with overlapping events coalesced.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.input_event_response_coalesced_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.input_event_response_coalesced_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
mouseup_followed_by_click_present_latency
Mouseup Followed By Click Present Latency
Time between receiving a mouseup which follow by a mouseclick on the event loop and compositing its result onto the screen (ms)
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.gpu.histograms.mouseup_followed_by_click_present_latency) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.gpu.histograms.mouseup_followed_by_click_present_latency) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
fx_page_load_ms_2
Fx Page Load Ms 2
Firefox: Time taken to load a page (ms). This includes all static contents, no dynamic content. Page reloads and loading of about: pages are not included.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.fx_page_load_ms_2) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.fx_page_load_ms_2) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
perf_dom_content_loaded_time
Perf DOM Content Loaded Time
Time in milliseconds from navigationStart to domContentLoaded for the foreground http or https root content document.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_dom_content_loaded_time_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_dom_content_loaded_time_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
perf_dom_contentperf_dom_content_loaded_time_from_responsestart_ms_loaded_time
Perf DOM Content Loaded Time From Response Start
Time in milliseconds from responseStart to domContentLoaded for the foreground http or https root content document.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_dom_content_loaded_time_from_responsestart_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_dom_content_loaded_time_from_responsestart_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
perf_page_load_time_from_responsestart
Perf Page Load Time from Response Start
Time in milliseconds from navigationStart to loadEventStart for the foreground http or https root content document.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_page_load_time_from_responsestart_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_page_load_time_from_responsestart_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
perf_request_animation_callback_non_pageload
Perf Request Animation Callback Non Pageload
Time spent in milliseconds calling all request animation frame callbacks for a document after it has reached readystate complete.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_request_animation_callback_non_pageload_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_request_animation_callback_non_pageload_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
perf_request_animation_callback_pageload
Perf Request Animation Callback Pageload
Time spent in milliseconds calling all request animation frame callbacks for a document after it has reached readystate complete.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_request_animation_callback_pageload_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.perf_request_animation_callback_pageload_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
loaded_tab_count
Loaded Tab Count
Number of fully loaded (i.e., not pending from session restore) tabs opened across all windows, collected at most every 5 minutes whenever the user interacts with the browser in the following ways: open tab/window, page load, restoring a pending tab.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.loaded_tab_count) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.loaded_tab_count) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
active_ticks
Browser Engagement Active Ticks
The count of the number of five-second intervals ('ticks') the user was considered 'active' in a subsession. Session activity involves keyboard or mouse interaction with the application. It does not take into account whether or not the window has focus or is in the foreground, only if it is receiving these interaction events.
Data Source: main
Definition:
SUM(payload.processes.parent.scalars.browser_engagement_active_ticks)
SQL with DataSource
SELECT
SUM(payload.processes.parent.scalars.browser_engagement_active_ticks)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
subsession_length
Subsession Length
The subsession length until now in seconds, monotonic
Data Source: main
Definition:
SUM(payload.info.subsession_length)
SQL with DataSource
SELECT
SUM(payload.info.subsession_length)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
tab_pinned_event_count
Browser Engagement Tab Pinned Event Count
The count of tab pinned events per subsession, across all windows, after the session has been restored. This includes tab pinned events from private windows.
Data Source: main
Definition:
SUM(payload.processes.parent.scalars.browser_engagement_tab_pinned_event_count)
SQL with DataSource
SELECT
SUM(payload.processes.parent.scalars.browser_engagement_tab_pinned_event_count)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
tab_open_event_count
Browser Engagement Tab Open Event Count
The count of tab open events per subsession, across all windows, after the session has been restored. This includes tab open events from private windows and from manual session restorations (i.e. after crashes and from about:home).
Data Source: main
Definition:
SUM(payload.processes.parent.scalars.browser_engagement_tab_open_event_count)
SQL with DataSource
SELECT
SUM(payload.processes.parent.scalars.browser_engagement_tab_open_event_count)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
tab_unload_to_reload
Tab Unload To Reload
How long (sec) a tab had been unloaded until it was reloaded.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.tab_unload_to_reload) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.tab_unload_to_reload) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
tab_reload_count
Browser Engagement Tab Reload Count
The count of tab reload events by the user after unloaded.
Data Source: main
Definition:
SUM(payload.processes.parent.scalars.browser_engagement_tab_reload_count)
SQL with DataSource
SELECT
SUM(payload.processes.parent.scalars.browser_engagement_tab_reload_count)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
tab_unload_count
Browser Engagement Tab Unload Count
The count of tab unload events by TabUnloader due to a low-memory situation.
Data Source: main
Definition:
SUM(payload.processes.parent.scalars.browser_engagement_tab_unload_count)
SQL with DataSource
SELECT
SUM(payload.processes.parent.scalars.browser_engagement_tab_unload_count)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
concurrent_pinned_tab_count
Browser Engagement Concurrent Pinned Tab Count
The count of maximum number of pinned tabs open during a subsession. This includes private windows and the ones opened when starting the browser. Starting Firefox 85 this includes number of restored pinned tabs at startup.
Data Source: main
Definition:
SUM(payload.processes.parent.scalars.browser_engagement_max_concurrent_tab_pinned_count)
SQL with DataSource
SELECT
SUM(payload.processes.parent.scalars.browser_engagement_max_concurrent_tab_pinned_count)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
concurrent_opened_tab_count
Browser Engagement Concurrent Opened Tab Count
The count of maximum number of tabs open during a subsession, across all windows, including tabs in private windows and restored at startup.
Data Source: main
Definition:
SUM(payload.processes.parent.scalars.browser_engagement_max_concurrent_tab_count)
SQL with DataSource
SELECT
SUM(payload.processes.parent.scalars.browser_engagement_max_concurrent_tab_count)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
opened_tab_count
Opened Tab Count
Number of tabs opened across all windows, collected at most every 5 minutes whenever the user interacts with the browser in the following ways: open tab/window, page load.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.tab_count) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.tab_count) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
js_pageload_xdr_encoding_ms
Js Pageload Xdr Encoding Ms
Time spent during page load XDR encoding Javascript in ms.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_xdr_encoding_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_xdr_encoding_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
js_pageload_protect_ms
Js Pageload Protect Ms
Time spent during page load protecting JIT executable memory.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_protect_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_protect_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
js_pageload_parse_ms
Js Pageload Parse Ms
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_parse_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_parse_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
js_pageload_execution_ms
Js Pageload Execution Ms
Time spent during page load syntax parsing JS scripts on the main thread in ms.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_execution_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_execution_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
js_pageload_delazification_ms
Js Pageload Delazification Ms
Time spent during page load delazifying Javascript in ms.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_delazification_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_delazification_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
js_pageload_baseline_compile_ms
Js Pageload Baseline Compile Ms
Time spent during page load baseline compiling Javascript in ms.
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_baseline_compile_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.processes.content.histograms.js_pageload_baseline_compile_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
input_event_response_ms
Input Event Response Ms
Time (ms) from the Input event being created to the end of it being handled for events handling during page load only
Data Source: main
Definition:
ARRAY_AGG(mozfun.hist.extract(payload.histograms.input_event_response_ms) IGNORE NULLS)
SQL with DataSource
SELECT
ARRAY_AGG(mozfun.hist.extract(payload.histograms.input_event_response_ms) IGNORE NULLS)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
memory_pressure_count
Memory Pressure Count
Event memory pressure count
Data Source: events_memory
Definition:
SUM(SAFE_CAST(SPLIT(event_string_value, ',')[OFFSET(1)] AS NUMERIC))
SQL with DataSource
SELECT
SUM(SAFE_CAST(SPLIT(event_string_value, ',')[OFFSET(1)] AS NUMERIC))
FROM (
(
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_with_ads
Search with Ads
Total number of Searches with Ads
Data Source: search_clients_engines_sources_daily
Definition:
SUM(search_with_ads)
SQL with DataSource
SELECT
SUM(search_with_ads)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
ad_click
Ad Clicks
Total number of Ad Clicks
Data Source: search_clients_engines_sources_daily
Definition:
SUM(ad_click)
SQL with DataSource
SELECT
SUM(ad_click)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
ad_click_glean
Num Ad Clicks in Glean
Total number of Ad Clicks From Serp Events in Glean
Data Source: serp_events
Definition:
SUM(IF(is_tagged = TRUE, num_ad_clicks, 0))
SQL with DataSource
SELECT
SUM(IF(is_tagged = TRUE, num_ad_clicks, 0))
FROM (
mozdata.firefox_desktop.serp_events
)
sap
SAP Search Count
Total number of SAP search counts
Data Source: search_clients_engines_sources_daily
Definition:
SUM(sap)
SQL with DataSource
SELECT
SUM(sap)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
ad_click_organic
Organic Ad Click Count
Total number of Organic Ad Click Counts
Data Source: search_clients_engines_sources_daily
Definition:
SUM(ad_click_organic)
SQL with DataSource
SELECT
SUM(ad_click_organic)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
is_default_browser
Is Default Browser
Was Firefox the default browser at any point during the interval?
Data Source: clients_daily
Definition:
COALESCE(LOGICAL_OR(is_default_browser), FALSE)
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR(is_default_browser), FALSE)
FROM (
mozdata.telemetry.clients_daily
)
is_default_browser_glean
Is Default Browser
Was Firefox the default browser at any point during the interval?
Data Source: baseline_clients_daily
Definition:
COALESCE(LOGICAL_OR(is_default_browser), FALSE)
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR(is_default_browser), FALSE)
FROM (
(
SELECT
p.*,
FROM `moz-fx-data-shared-prod.{dataset}.baseline_clients_daily` p
)
)
is_default_pdf_handler
Is Default PDF Handler (Windows)
Was Firefox the default PDF Handler at any point during the interval?
Data Source: metrics
Definition:
COALESCE(LOGICAL_OR(mozfun.map.get_key(metrics.labeled_boolean.os_environment_is_default_handler, '.pdf')), FALSE)
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR(mozfun.map.get_key(metrics.labeled_boolean.os_environment_is_default_handler, '.pdf')), FALSE)
FROM (
(
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
)
)
is_pinned
Is Pinned (Windows Taskbar)
Was Firefox pinned to the Windows Taskbar at any point during the interval?
Data Source: clients_daily
Definition:
COALESCE(LOGICAL_OR(scalar_parent_os_environment_is_taskbar_pinned), FALSE)
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR(scalar_parent_os_environment_is_taskbar_pinned), FALSE)
FROM (
mozdata.telemetry.clients_daily
)
newtab_searches
Newtab Handoff Searches
Count of searches performed on the New Tab and handed off to the urlbar
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(search_interaction_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(search_interaction_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_any_searches
Any Newtab Searches
Client performed any Newtab Handoff searches during the experiment
Data Source: newtab_clients_daily
Definition:
{{agg_any("search_interaction_count > 0")}}
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR(search_interaction_count > 0), FALSE)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_gt4_searches
Greater Than 4 Newtab Searches
Client performed at least 5 Newtab Handoff searches during the experiment
Data Source: newtab_clients_daily
Definition:
COALESCE(CASE WHEN SUM(search_interaction_count) > 4 THEN 1 ELSE 0 END, 0)
SQL with DataSource
SELECT
COALESCE(CASE WHEN SUM(search_interaction_count) > 4 THEN 1 ELSE 0 END, 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_searches_with_ads
Newtab Searches with Ads
Count of searches performed on the New Tab that resulted in an ad impression on the SERP
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(search_ad_impression_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(search_ad_impression_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_ad_clicks
Newtab Ad Clicks
Count of searches performed on the New Tab that resulted in an ad click
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(search_ad_click_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(search_ad_click_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_ad_click_rate
Newtab Ad Click Rate
New Tab ad clicks divided by New Tab searches with ads
Data Source: newtab_clients_daily
Definition:
SAFE_DIVIDE(COALESCE(SUM(search_ad_click_count), 0), COALESCE(SUM(search_ad_impression_count), 0))
SQL with DataSource
SELECT
SAFE_DIVIDE(COALESCE(SUM(search_ad_click_count), 0), COALESCE(SUM(search_ad_impression_count), 0))
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
organic_content_clicks
Organic Content Clicks
Count of clicks on Organic content.
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(organic_content_click_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(organic_content_click_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
any_organic_content_clicks
Any organic Content Clicks
Client had any Content clicks during the experiment.
Data Source: newtab_clients_daily
Definition:
COALESCE(LOGICAL_OR(organic_content_click_count > 0), FALSE)
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR(organic_content_click_count > 0), FALSE)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
sponsored_content_clicks
Sponsored Content Clicks
Count of clicks on Sponsored content.
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(sponsored_content_click_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(sponsored_content_click_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
organic_content_impressions
Organic Content Impressions
Count of impressions on Organic content.
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(organic_content_impression_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(organic_content_impression_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
sponsored_content_impressions
Sponsored Content Impressions
Count of impressions of Sponsored Content.
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(sponsored_content_impression_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(sponsored_content_impression_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
sponsored_tile_impressions
Sponsored Tile Impressions
Count of impressions of Sponsored Tiles (aka Sponsored Topsites on New Tab) across all positions.
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(sponsored_topsite_impression_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(sponsored_topsite_impression_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
sponsored_tile_clicks
Sponsored Tile Clicks
Count of clicks of Sponsored Tiles (aka Sponsored Topsites on New Tab) across all positions.
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(sponsored_topsite_click_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(sponsored_topsite_click_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_newtab_enabled
Newtab Newtab Enabled
Whether or not new tabs are set to display the default New Tab page.
Data Source: newtab_clients_daily
Definition:
COALESCE(MAX(IF(newtab_category = 'enabled', 1, 0)), 0)
SQL with DataSource
SELECT
COALESCE(MAX(IF(newtab_category = 'enabled', 1, 0)), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_homepage_enabled
Newtab Homepage Enabled
Whether or not the homepage is set to display the default New Tab page.
Data Source: newtab_clients_daily
Definition:
COALESCE(MAX(IF(homepage_category = 'enabled', 1, 0)), 0)
SQL with DataSource
SELECT
COALESCE(MAX(IF(homepage_category = 'enabled', 1, 0)), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_tiles_enabled
Newtab Tiles Enabled
Whether or not tiles are enabled on the New Tab. Includes both sponsored and nonsponsored tiles.
Data Source: newtab_clients_daily
Definition:
COALESCE(MAX(CAST(organic_topsites_enabled AS INT)), 0)
SQL with DataSource
SELECT
COALESCE(MAX(CAST(organic_topsites_enabled AS INT)), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_sponsored_tiles_enabled
Newtab Sponsored Tiles Enabled
Whether or not sponsored tiles are enabled on the New Tab. Includes both sponsored and nonsponsored tiles.
Data Source: newtab_clients_daily
Definition:
COALESCE(MAX(CAST(sponsored_topsites_enabled AS INT)), 0)
SQL with DataSource
SELECT
COALESCE(MAX(CAST(sponsored_topsites_enabled AS INT)), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_content_enabled
Newtab Content Enabled
Whether or not Content is enabled on the New Tab.
Data Source: newtab_clients_daily
Definition:
COALESCE(MAX(CAST(organic_content_enabled AS INT)), 0)
SQL with DataSource
SELECT
COALESCE(MAX(CAST(organic_content_enabled AS INT)), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_sponsored_content_stories_enabled
Newtab Sponsored Content Stories Enabled
Whether or not Content Sponsored Stories is enabled on the New Tab.
Data Source: newtab_clients_daily
Definition:
COALESCE(MAX(CAST(sponsored_content_enabled AS INT)), 0)
SQL with DataSource
SELECT
COALESCE(MAX(CAST(sponsored_content_enabled AS INT)), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_engagement
Newtab Engagement
Whether or not the client had a newtab search OR a content click OR a tile click.
Data Source: newtab_clients_daily
Definition:
COALESCE(MAX(CASE WHEN any_engagement_visits > 0 THEN 1 ELSE 0 END), 0)
SQL with DataSource
SELECT
COALESCE(MAX(CASE WHEN any_engagement_visits > 0 THEN 1 ELSE 0 END), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_visits
Newtab Visit Count
Count of New Tab visits
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(all_visits), 0)
SQL with DataSource
SELECT
COALESCE(SUM(all_visits), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_engaged_visits
Newtab Engaged Visit Count
Count of New Tab visits with any engagement
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(any_engagement_visits), 0)
SQL with DataSource
SELECT
COALESCE(SUM(any_engagement_visits), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_non_search_engagement
Newtab Non-Search Engaged Visit Count
Count of New Tab visits with non-search engagement
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(nonsearch_engagement_visits), 0)
SQL with DataSource
SELECT
COALESCE(SUM(nonsearch_engagement_visits), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_organic_topsite_clicks_v2
Newtab Organic Tile Clicks
Count of New Tab organic tile clicks across all positions.
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(organic_topsite_click_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(organic_topsite_click_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_organic_topsite_impressions_v2
Newtab Organic Tile Impressions
Count of New Tab organic tile impressions across all positions.
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(organic_topsite_impression_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(organic_topsite_impression_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
newtab_weather_widget_enabled
Weather Widget Enabled
Whether or not the weather widget is enabled on the New Tab.
Data Source: newtab_clients_daily
Definition:
COALESCE(MAX(CAST(newtab_weather_enabled AS INT)), 0)
SQL with DataSource
SELECT
COALESCE(MAX(CAST(newtab_weather_enabled AS INT)), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
imported_bookmarks
Imported Bookmarks
Count of clients who imported bookmarks
Data Source: clients_daily
Definition:
LOGICAL_OR(bookmark_migrations_quantity_all IS NOT NULL AND bookmark_migrations_quantity_all != 0)
SQL with DataSource
SELECT
LOGICAL_OR(bookmark_migrations_quantity_all IS NOT NULL AND bookmark_migrations_quantity_all != 0)
FROM (
mozdata.telemetry.clients_daily
)
imported_bookmarks_count
Total Number of Imported Bookmarks
Total number of imported bookmarks per client who imported
Data Source: clients_daily
Definition:
NULLIF(SUM(bookmark_migrations_quantity_all), 0)
SQL with DataSource
SELECT
NULLIF(SUM(bookmark_migrations_quantity_all), 0)
FROM (
mozdata.telemetry.clients_daily
)
imported_logins
Imported Logins
Count of clients who imported logins
Data Source: clients_daily
Definition:
LOGICAL_OR(logins_migrations_quantity_all IS NOT NULL AND logins_migrations_quantity_all != 0)
SQL with DataSource
SELECT
LOGICAL_OR(logins_migrations_quantity_all IS NOT NULL AND logins_migrations_quantity_all != 0)
FROM (
mozdata.telemetry.clients_daily
)
imported_logins_count
Total Number of Imported Logins
Total number of imported logins per client who imported
Data Source: clients_daily
Definition:
NULLIF(SUM(logins_migrations_quantity_all), 0)
SQL with DataSource
SELECT
NULLIF(SUM(logins_migrations_quantity_all), 0)
FROM (
mozdata.telemetry.clients_daily
)
imported_history
Imported History
Count of clients who imported history
Data Source: clients_daily
Definition:
LOGICAL_OR(history_migrations_quantity_all IS NOT NULL AND history_migrations_quantity_all != 0)
SQL with DataSource
SELECT
LOGICAL_OR(history_migrations_quantity_all IS NOT NULL AND history_migrations_quantity_all != 0)
FROM (
mozdata.telemetry.clients_daily
)
imported_history_count
Total Number of Imported History Links
Total number of imported history links per client who imported
Data Source: clients_daily
Definition:
NULLIF(SUM(history_migrations_quantity_all), 0)
SQL with DataSource
SELECT
NULLIF(SUM(history_migrations_quantity_all), 0)
FROM (
mozdata.telemetry.clients_daily
)
fxa_signed_in
FxA Sign-in Rate
Is the client signed in at any point during the experiment
Data Source: clients_daily
Definition:
CAST(COALESCE(SUM(CASE WHEN fxa_configured IS TRUE THEN 1 ELSE 0 END),0) > 0 AS INT)
SQL with DataSource
SELECT
CAST(COALESCE(SUM(CASE WHEN fxa_configured IS TRUE THEN 1 ELSE 0 END),0) > 0 AS INT)
FROM (
mozdata.telemetry.clients_daily
)
fxa_signed_in_glean
FxA Sign-in Rate (Glean)
Is the client signed in at any point during the experiment, based on the Glean fxa.account_enabled metric
Data Source: metrics
Definition:
CAST(COALESCE(SUM(CASE WHEN metrics.boolean.fxa_account_enabled IS TRUE THEN 1 ELSE 0 END),0) > 0 AS INT)
SQL with DataSource
SELECT
CAST(COALESCE(SUM(CASE WHEN metrics.boolean.fxa_account_enabled IS TRUE THEN 1 ELSE 0 END),0) > 0 AS INT)
FROM (
(
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
)
)
retained
Retained
Records whether a client submitted any pings (i.e. used Firefox).
Note: As of June 2026, this metric is being deprecated in favor of "Retained (DAU)", which better matches the conventional
definition of Retention based on "active" instead of "seen" now recommended by Data Science team and used for reporting in
other contexts.
Data Source: clients_daily
Definition:
COALESCE(SUM(pings_aggregated_by_this_row), 0) > 0
SQL with DataSource
SELECT
COALESCE(SUM(pings_aggregated_by_this_row), 0) > 0
FROM (
mozdata.telemetry.clients_daily
)
retained_dau
Retained (DAU)
Whether the client had at least one DAU-qualifying day in the analysis window (is_dau = TRUE on any day).
Conventionally expressed as a percentage rate: The percentage of clients from the originating cohort that were then active in the later period.
Most typically, this is measured as Week 2 Retention in order to balance timeliness and accuracy.
But when time permits, Data Science recommends using Week 4 Retention as more representative of long-term effects.
Data Source: firefox_desktop_active_users_view
Definition:
COALESCE(LOGICAL_OR(is_dau), FALSE)
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR(is_dau), FALSE)
FROM (
(
SELECT *
FROM `moz-fx-data-shared-prod.telemetry.desktop_active_users`
WHERE is_desktop
)
)
active_in_last_3_days
3 Days Retention
Records whether a client submitted any pings (i.e. used Firefox) on any of the last 3 days.
Data Source: firefox_desktop_baseline_active_users_view
Definition:
COALESCE(MIN(days_since_desktop_active), 30) < 3
SQL with DataSource
SELECT
COALESCE(MIN(days_since_desktop_active), 30) < 3
FROM (
(
SELECT *
FROM `moz-fx-data-shared-prod.firefox_desktop.baseline_active_users`
WHERE is_desktop
)
)
active_in_last_3_days_legacy
3 Days Retention
Records whether a client submitted any pings (i.e. used Firefox) on any of the last 3 days. Uses legacy telemetry.
Data Source: firefox_desktop_active_users_view
Definition:
COALESCE(MIN(mozfun.bits28.days_since_seen(days_active_bits)), 30) < 3
SQL with DataSource
SELECT
COALESCE(MIN(mozfun.bits28.days_since_seen(days_active_bits)), 30) < 3
FROM (
(
SELECT *
FROM `moz-fx-data-shared-prod.telemetry.desktop_active_users`
WHERE is_desktop
)
)
non_ssl_loads_v1
Non-SSL Loads
Page loads of Firefox users that were not using SSL
Data Source: main
Definition:
SUM(
mozfun.map.get_key(mozfun.hist.extract(payload.histograms.http_pageload_is_ssl).values, 0)
)
SQL with DataSource
SELECT
SUM(
mozfun.map.get_key(mozfun.hist.extract(payload.histograms.http_pageload_is_ssl).values, 0)
)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
ssl_loads_v1
SSL Loads
Page loads of Firefox users that were using SSL
Data Source: main
Definition:
SUM(
mozfun.map.get_key(mozfun.hist.extract(payload.histograms.http_pageload_is_ssl).values, 1)
)
SQL with DataSource
SELECT
SUM(
mozfun.map.get_key(mozfun.hist.extract(payload.histograms.http_pageload_is_ssl).values, 1)
)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
http_pageload_is_ssl_ratio_v1
SSL Loads Probe Ratio
Ratio of clients that have the http_pageload_is_ssl_ratio_v1 probe
Data Source: main
Definition:
COUNT(payload.histograms.http_pageload_is_ssl) / COUNT(*)
SQL with DataSource
SELECT
COUNT(payload.histograms.http_pageload_is_ssl) / COUNT(*)
FROM (
(
SELECT
*,
DATE(submission_timestamp) AS submission_date,
environment.experiments
FROM `moz-fx-data-shared-prod.telemetry_stable.main_v5`
)
)
sponsored_tiles_dismissals
Sponsored Tiles Dismissals Count
Count of sponsored tiles dismissals in all positions
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(sponsored_topsite_dismissal_count), 0)
SQL with DataSource
SELECT
COALESCE(SUM(sponsored_topsite_dismissal_count), 0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
any_sponsored_tiles_dismissals
Any Sponsored Tiles Dismissed
Clients that dismissed any sponsored tiles
Data Source: newtab_clients_daily
Definition:
COALESCE(LOGICAL_OR(
sponsored_topsite_dismissal_count > 0
), FALSE)
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR(
sponsored_topsite_dismissal_count > 0
), FALSE)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
organic_tiles_dismissals
Organic Tiles Dismissals Count
Count of organic tiles dismissals in all positions
Data Source: newtab_clients_daily
Definition:
COALESCE(SUM(organic_topsite_dismissal_count),0)
SQL with DataSource
SELECT
COALESCE(SUM(organic_topsite_dismissal_count),0)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
any_organic_tiles_dismissals
Any Organic Tiles Dismissed
Clients that dismissed any organic tiles
Data Source: newtab_clients_daily
Definition:
COALESCE(LOGICAL_OR(
organic_topsite_dismissal_count > 0
), FALSE)
SQL with DataSource
SELECT
COALESCE(LOGICAL_OR(
organic_topsite_dismissal_count > 0
), FALSE)
FROM (
moz-fx-data-shared-prod.firefox_desktop.newtab_clients_daily
)
separate_search_engine
Split PBM search engine
Indicates whether the client configured separate search engines for regular browsing and private browsing mode.
Data Source: search_clients_engines_sources_daily
Definition:
COALESCE(ANY_VALUE(default_search_engine != default_private_search_engine), false)
SQL with DataSource
SELECT
COALESCE(ANY_VALUE(default_search_engine != default_private_search_engine), false)
FROM (
mozdata.search.search_clients_engines_sources_daily
)
new_profiles_v1
New Profiles
The number of newly acquired Firefox Desktop clients. A client is newly acquired on the first date they appear in either of new profile, main or first shutdown pings based on submission date
Data Source: clients_first_seen_v2
Definition:
COUNT(*)
SQL with DataSource
SELECT
COUNT(*)
FROM (
moz-fx-data-shared-prod.telemetry_derived.clients_first_seen_v2
)
pdf_engagement
Data Source: metrics
Definition:
(
(COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "freetext")) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "ink")) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing_highlight_kind, "highlight")) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing_highlight_kind, "free_highlight")) > 0, FALSE)) AND
(COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "print")) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "save")) > 0, FALSE))
)
SQL with DataSource
SELECT
(
(COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "freetext")) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "ink")) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing_highlight_kind, "highlight")) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing_highlight_kind, "free_highlight")) > 0, FALSE)) AND
(COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "print")) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "save")) > 0, FALSE))
)
FROM (
(
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
)
)
pdf_freetext
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "freetext")) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "freetext")) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_ink
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "ink")) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "ink")) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_stamp
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "stamp")) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "stamp")) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_signature
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "signature")) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "signature")) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_print
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "print")) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "print")) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_save
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "save")) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing, "save")) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_opening
Data Source: metrics
Definition:
(
COALESCE(SUM(metrics.counter.pdfjs_used) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(metrics.counter.pdfjs_used) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_highlight
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing_highlight_kind, "highlight")) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing_highlight_kind, "highlight")) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_free_highlight
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing_highlight_kind, "free_highlight")) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.pdfjs_editing_highlight_kind, "free_highlight")) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_invoked_to_handle
PDF Invoked to Handle
Firefox was invoked (i.e., was already running and was not launched) to handle a pdf file
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.os_environment_invoked_to_handle, '.pdf')) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.os_environment_invoked_to_handle, '.pdf')) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_launched_to_handle
PDF Launched to Handle
Firefox was launched afresh (i.e., was not already running) to handle a pdf file
Data Source: metrics
Definition:
(
COALESCE(SUM(mozfun.map.get_key( metrics.labeled_counter.os_environment_launched_to_handle, '.pdf')) > 0, FALSE)
)
SQL with DataSource
SELECT
(
COALESCE(SUM(mozfun.map.get_key( metrics.labeled_counter.os_environment_launched_to_handle, '.pdf')) > 0, FALSE)
)
FROM (
(
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
)
)
pdf_launched_or_invoked_to_handle
PDF Launched or Invoked
Firefox was launched or invoked to handle a pdf file
Data Source: metrics
Definition:
(
(COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.os_environment_invoked_to_handle, '.pdf')) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.os_environment_launched_to_handle, '.pdf')) > 0, FALSE))
)
SQL with DataSource
SELECT
(
(COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.os_environment_invoked_to_handle, '.pdf')) > 0, FALSE) OR
COALESCE(SUM(mozfun.map.get_key(metrics.labeled_counter.os_environment_launched_to_handle, '.pdf')) > 0, FALSE))
)
FROM (
(
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
)
)
repeat_first_month_user
Repeat First Month Users
Clients that were DAU at least once between their 2nd and 28th days, inclusive (first day = Day 1).
Data Source: clients_first_seen_28_days_later
Definition:
LOGICAL_OR(COALESCE(qualified_second_day, FALSE))
SQL with DataSource
SELECT
LOGICAL_OR(COALESCE(qualified_second_day, FALSE))
FROM (
`moz-fx-data-shared-prod.telemetry.clients_first_seen_28_days_later`
)
new_profile_retained_week4
Week 4 Retained Users
Clients that were DAU at least once between their 22nd and 28th days, inclusive (first day = Day 1).
Data Source: clients_first_seen_28_days_later
Definition:
LOGICAL_OR(COALESCE(qualified_week4, FALSE))
SQL with DataSource
SELECT
LOGICAL_OR(COALESCE(qualified_week4, FALSE))
FROM (
`moz-fx-data-shared-prod.telemetry.clients_first_seen_28_days_later`
)
cohort_clients_in_cohort
Number of Clients in Daily Cohort of New Users
The number of new profiles per day (using method from clients_first_seen_v2)
Data Source: desktop_cohort_daily_retention
Definition:
SUM(COALESCE(num_clients_in_cohort, 0))
SQL with DataSource
SELECT
SUM(COALESCE(num_clients_in_cohort, 0))
FROM (
`moz-fx-data-shared-prod.telemetry.desktop_cohort_daily_retention`
)
cohort_clients_active_on_day
Number of Clients Retained on Day
Number of Clients Retained (Qualifying as DAU) on each day
Data Source: desktop_cohort_daily_retention
Definition:
SUM(COALESCE(num_clients_dau_on_day, 0))
SQL with DataSource
SELECT
SUM(COALESCE(num_clients_dau_on_day, 0))
FROM (
`moz-fx-data-shared-prod.telemetry.desktop_cohort_daily_retention`
)
cohort_clients_active_in_week
Number of Clients Retained in Last Week
Number of Clients Retained (Qualifying as DAU) in previous 7 days (current day inclusive)
Data Source: desktop_cohort_daily_retention
Definition:
SUM(COALESCE(num_clients_dau_active_atleastonce_in_last_7_days, 0))
SQL with DataSource
SELECT
SUM(COALESCE(num_clients_dau_active_atleastonce_in_last_7_days, 0))
FROM (
`moz-fx-data-shared-prod.telemetry.desktop_cohort_daily_retention`
)
cohort_clients_active_in_month
Number of Clients Retained in Last Month
Number of Clients Retained (Qualifying as DAU) in previous 28 days (current day inclusive)
Data Source: desktop_cohort_daily_retention
Definition:
SUM(COALESCE(num_clients_dau_active_atleastonce_in_last_28_days, 0))
SQL with DataSource
SELECT
SUM(COALESCE(num_clients_dau_active_atleastonce_in_last_28_days, 0))
FROM (
`moz-fx-data-shared-prod.telemetry.desktop_cohort_daily_retention`
)
socket_crash_count_v1
Client Crash Count
Number of Socket crashes by a single client. Filter on this field to remove clients with large numbers of crashes.
Data Source: clients_daily
Definition:
SUM(socket_crash_count)
SQL with DataSource
SELECT
SUM(socket_crash_count)
FROM (
mozdata.telemetry.clients_daily
)
socket_crash_active_hours_v1
Client Crash Active Hours
Total active hours of a client with socket crashes
Data Source: clients_daily
Definition:
SUM(IF(socket_crash_count > 0, active_hours_sum, 0))
SQL with DataSource
SELECT
SUM(IF(socket_crash_count > 0, active_hours_sum, 0))
FROM (
mozdata.telemetry.clients_daily
)
socket_crash_dau_v1
Daily Active Users with socket crashes
Daily active user count with socket crashes
Data Source: clients_daily
Definition:
COUNTIF(socket_crash_count > 0)
SQL with DataSource
SELECT
COUNTIF(socket_crash_count > 0)
FROM (
mozdata.telemetry.clients_daily
)
serp_impressions
SERP impressions
Number of SERP page loads
Data Source: serp_events
Definition:
COUNT(*)
SQL with DataSource
SELECT
COUNT(*)
FROM (
mozdata.firefox_desktop.serp_events
)
engagement_rate_v1
Firefox Desktop 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: desktop_engagement_view
Definition:
SUM(dau) / SUM(mau)
SQL with DataSource
SELECT
SUM(dau) / SUM(mau)
FROM (
(
SELECT *
FROM `moz-fx-data-shared-prod.telemetry.desktop_engagement`
WHERE is_desktop
)
)
retention_rate_v1
Firefox Desktop 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: desktop_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.telemetry.desktop_retention`
WHERE is_desktop
)
)
new_profile_retention_rate_v1
Firefox Desktop 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: desktop_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.telemetry.desktop_retention`
WHERE is_desktop
)
)
sync_to_mobile
Desktop Sync to Mobile
Did a desktop user sync to mobile
Data Source: clients_daily
Definition:
LOGICAL_OR(COALESCE(sync_count_mobile_mean > 0, FALSE))
SQL with DataSource
SELECT
LOGICAL_OR(COALESCE(sync_count_mobile_mean > 0, FALSE))
FROM (
mozdata.telemetry.clients_daily
)
site_breakage_impressions
Webcompat Reporting Panel Impressions
Data Source: site_breakage_events
Definition:
CAST(COALESCE(LOGICAL_OR(event_name = 'opened'), FALSE) AS INT64)
SQL with DataSource
SELECT
CAST(COALESCE(LOGICAL_OR(event_name = 'opened'), FALSE) AS INT64)
FROM (
(
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'
)
)
site_breakage_uses
Webcompat Reporting Panel Uses
Data Source: site_breakage_events
Definition:
CAST(COALESCE(LOGICAL_OR(event_name = 'send' OR event_name = 'send_more_info'), FALSE) AS INT64)
SQL with DataSource
SELECT
CAST(COALESCE(LOGICAL_OR(event_name = 'send' OR event_name = 'send_more_info'), FALSE) AS INT64)
FROM (
(
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'
)
)
etp_disablement
ETP Disablement
Data Source: protections_popup_events
Definition:
CAST(COALESCE(LOGICAL_OR(event_name = 'click_etp_toggle_off'), FALSE) AS INT64)
SQL with DataSource
SELECT
CAST(COALESCE(LOGICAL_OR(event_name = 'click_etp_toggle_off'), FALSE) AS INT64)
FROM (
(
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'
)
)
urlbar_impressions
URL Bar Impressions
Data Source: urlbar_events_daily_engagement_by_product_result_type_v1
Definition:
SUM(urlbar_impressions)
SQL with DataSource
SELECT
SUM(urlbar_impressions)
FROM (
(
SELECT * FROM `mozdata.firefox_desktop.urlbar_events_daily_engagement_by_product_result_type`
)
)
number_of_sessions
Number of Cold Starts (Based on Legacy Session IDs)
Data Source: metrics
Definition:
COALESCE(COUNT(DISTINCT metrics.uuid.legacy_telemetry_session_id), 0)
SQL with DataSource
SELECT
COALESCE(COUNT(DISTINCT metrics.uuid.legacy_telemetry_session_id), 0)
FROM (
(
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
)
)
number_of_autostart_launches
Number of Launches Using Autostart
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'Autostart'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'Autostart'), 0)
FROM (
(
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
)
)
number_of_desktoplauncher_launches
Number of Launches Using the Desktop Launcher
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'DesktopLauncher'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'DesktopLauncher'), 0)
FROM (
(
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
)
)
number_of_desktop_launches
Number of Launches Using an Desktop Shortcut (Normal Mode)
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'Desktop'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'Desktop'), 0)
FROM (
(
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
)
)
number_of_desktopprivate_launches
Number of Launches Using an Desktop Shortcut (Private Mode)
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'DesktopPrivate'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'DesktopPrivate'), 0)
FROM (
(
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
)
)
number_of_other_launches
Number of Launches Using an Unknown Method
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'Other'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'Other'), 0)
FROM (
(
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
)
)
number_of_othershortcut_launches
Number of Launches Using Another Shortcut
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'OtherShortcut'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'OtherShortcut'), 0)
FROM (
(
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
)
)
number_of_startmenu_launches
Number of Launches Using Start Menu (Normal Mode)
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'StartMenu'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'StartMenu'), 0)
FROM (
(
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
)
)
number_of_startmenuprivate_launches
Number of Launches Using Start Menu (Private Mode)
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'StartMenuPrivate'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'StartMenuPrivate'), 0)
FROM (
(
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
)
)
number_of_taskbar_launches
Number of Launches Using Taskbar (Normal Mode)
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'Taskbar'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'Taskbar'), 0)
FROM (
(
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
)
)
number_of_taskbarprivate_launches
Number of Launches Using Taskbar (Private Mode)
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'TaskbarPrivate'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'TaskbarPrivate'), 0)
FROM (
(
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
)
)
number_of_taskbartab_launches
Number of Launches Using Taskbar Tabs
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'TaskbarTab'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method = 'TaskbarTab'), 0)
FROM (
(
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
)
)
number_of_non_taskbartab_launches
Number of Launches without Using Taskbar Tabs
Data Source: metrics
Definition:
COALESCE(COUNTIF(metrics.string.os_environment_launch_method != 'TaskbarTab'), 0)
SQL with DataSource
SELECT
COALESCE(COUNTIF(metrics.string.os_environment_launch_method != 'TaskbarTab'), 0)
FROM (
(
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
)
)
form_autofill_address_fields_filled
Address autofill: gross auto-filled fields
Per client: the number of address form fields Firefox auto-filled, summed over every address form the client interacted with.
Only fields whose type Firefox had to *infer* are counted -- i.e. fields
identified by the regex heuristics, by Fathom, or by the ML field
classifier. Fields carrying an explicit `autocomplete` attribute are
excluded, since their type needs no inference and is therefore unaffected
by changes to the field classifier.
This is the gross figure: it equals
`form_autofill_address_fields_modified` +
`form_autofill_address_fields_net_kept`.
Data Source: form_autofill_forms
Definition:
COALESCE(SUM(IF(form_type = 'address', autofilled_fields, 0)), 0)
SQL with DataSource
SELECT
COALESCE(SUM(IF(form_type = 'address', autofilled_fields, 0)), 0)
FROM (
(
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
)
)
form_autofill_address_fields_modified
Address autofill: modified auto-filled fields
Per client: of the address fields Firefox auto-filled, the number the user
subsequently edited (a filled_modified_address_form event named the
field). Counted once per field per form interaction, however many times the
user edited it.
A field the user edits is one autofill got wrong, so this is the error
count against `form_autofill_address_fields_filled`.
Data Source: form_autofill_forms
Definition:
COALESCE(SUM(IF(form_type = 'address', modified_fields, 0)), 0)
SQL with DataSource
SELECT
COALESCE(SUM(IF(form_type = 'address', modified_fields, 0)), 0)
FROM (
(
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
)
)
form_autofill_address_fields_net_kept
Address autofill: net kept auto-filled fields
Per client: the number of auto-filled address fields the user left
untouched -- auto-filled and never subsequently edited. This is the
user-accepted yield of address autofill, and the counterpart to
form_autofill_address_fields_modified.
Data Source: form_autofill_forms
Definition:
COALESCE(SUM(IF(form_type = 'address', net_kept_fields, 0)), 0)
SQL with DataSource
SELECT
COALESCE(SUM(IF(form_type = 'address', net_kept_fields, 0)), 0)
FROM (
(
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
)
)
form_autofill_credit_card_fields_filled
Credit card autofill: gross auto-filled fields
Per client: the number of credit card form fields Firefox auto-filled,
summed over every credit card form the client interacted with. Same
inferred-field rule as form_autofill_address_fields_filled; note that
Fathom accounts for roughly half of all auto-filled credit card fields, so
excluding it here would halve this metric.
Equals `form_autofill_credit_card_fields_modified` +
`form_autofill_credit_card_fields_net_kept`.
Data Source: form_autofill_forms
Definition:
COALESCE(SUM(IF(form_type = 'credit_card', autofilled_fields, 0)), 0)
SQL with DataSource
SELECT
COALESCE(SUM(IF(form_type = 'credit_card', autofilled_fields, 0)), 0)
FROM (
(
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
)
)
form_autofill_credit_card_fields_modified
Credit card autofill: modified auto-filled fields
Per client: of the credit card fields Firefox auto-filled, the number the
user subsequently edited (a filled_modified_cc_form_v2 event named the
field). Counted once per field per form interaction.
Data Source: form_autofill_forms
Definition:
COALESCE(SUM(IF(form_type = 'credit_card', modified_fields, 0)), 0)
SQL with DataSource
SELECT
COALESCE(SUM(IF(form_type = 'credit_card', modified_fields, 0)), 0)
FROM (
(
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
)
)
form_autofill_credit_card_fields_net_kept
Credit card autofill: net kept auto-filled fields
Per client: the number of auto-filled credit card fields the user left untouched -- auto-filled and never subsequently edited.
Data Source: form_autofill_forms
Definition:
COALESCE(SUM(IF(form_type = 'credit_card', net_kept_fields, 0)), 0)
SQL with DataSource
SELECT
COALESCE(SUM(IF(form_type = 'credit_card', net_kept_fields, 0)), 0)
FROM (
(
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
)
)
form_autofill_address_form_submissions
Address form submissions
Per client: the number of distinct address forms submitted
(submitted_address_form / submitted_address_form_ext), counted once per
form interaction regardless of how the fields were populated. Includes
forms Firefox did not auto-fill.
Data Source: form_autofill_forms
Definition:
COUNT(DISTINCT IF(form_type = 'address' AND form_submitted, flow, NULL))
SQL with DataSource
SELECT
COUNT(DISTINCT IF(form_type = 'address' AND form_submitted, flow, NULL))
FROM (
(
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
)
)
form_autofill_credit_card_form_submissions
Credit card form submissions
Per client: the number of distinct credit card forms submitted
(submitted_cc_form_v2), counted once per form interaction regardless of
how the fields were populated. Includes forms Firefox did not auto-fill.
Data Source: form_autofill_forms
Definition:
COUNT(DISTINCT IF(form_type = 'credit_card' AND form_submitted, flow, NULL))
SQL with DataSource
SELECT
COUNT(DISTINCT IF(form_type = 'credit_card' AND form_submitted, flow, NULL))
FROM (
(
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
)
)