looker/definitions/firefox_desktop.toml

# Metrics

[metrics.days_of_use.statistics.average]

[metrics.socket_crash_count_v1.statistics.sum]
[metrics.socket_crash_active_hours_v1.statistics.sum]
[metrics.socket_crash_dau_v1.statistics.sum]
[metrics.socket_crash_active_hours_v1.statistics.client_count]
[metrics.socket_crash_count_v1.statistics.ratio]
numerator = "socket_crash_count_v1.sum"
denominator = "socket_crash_active_hours_v1.sum"


## OKRs example

[metrics.desktop_engagement_dau_v1]
select_expression = "SUM(dau)"
data_source = "desktop_engagement_v1"
friendly_name = "(non-official) DAU"
description = "(non-official) Daily active users on desktop based on engagment data"

[metrics.desktop_engagement_wau_v1]
select_expression = "SUM(wau)"
data_source = "desktop_engagement_v1"
friendly_name = "(non-official) WAU"
description = "(non-official) Weekly active users on desktop based on engagment data"

[metrics.desktop_engagement_mau_v1]
select_expression = "SUM(mau)"
data_source = "desktop_engagement_v1"
friendly_name = "(non-official) MAU"
description = "(non-official) Monthly active users on desktop based on engagment data"


[metrics.desktop_engagement_dau_v1.statistics.sum]
[metrics.desktop_engagement_wau_v1.statistics.sum]
[metrics.desktop_engagement_mau_v1.statistics.sum]

[metrics.desktop_engagement_dau_v1.statistics.ratio]
numerator = "desktop_engagement_dau_v1.sum"
denominator = "desktop_engagement_mau_v1.sum"

[metrics.desktop_engagement_dau_v1.statistics.rolling_average]
window_sizes = [1, 7, 28]


## New Tab metrics
[metrics.newtab_visits.statistics.sum]
[metrics.newtab_visits.statistics.client_count]
[metrics.newtab_engaged_visits.statistics.sum]
[metrics.newtab_ad_click_rate.statistics.average]

#HNT data model visit metrics and statistics
[metrics.nonsearch_engagement_visits]
data_source = "newtab_clients_daily_aggregates"
select_expression = "SUM(nonsearch_engagement_visits)"
friendly_name = "Nonsearch Engagement Visits"
description = "Count of visits with nonsearch engagement"

[metrics.default_ui_visits]
data_source = "newtab_clients_daily_aggregates"
select_expression = "SUM(default_ui_visits)"
friendly_name = "Default UI Visits"
description = "Count of visits with default UI"

[metrics.nonsearch_engagement_visits.statistics.sum]
[metrics.default_ui_visits.statistics.sum]
[metrics.nonsearch_engagement_visits.statistics.ratio]
numerator = "nonsearch_engagement_visits.sum"
denominator = "default_ui_visits.sum"


#HNT data model client metrics and statistics
[metrics.nonsearch_engagement_clients]
data_source = "newtab_clients_daily_aggregates"
select_expression = "SUM(nonsearch_engagement_clients)"
friendly_name = "Nonsearch Engagement Clients"
description = "Count of clients with nonsearch engagement"

[metrics.default_ui_clients]
data_source = "newtab_clients_daily_aggregates"
select_expression = "SUM(default_ui_clients)"
friendly_name = "Default UI Clients"
description = "Count of clients with default UI"

[metrics.nonsearch_engagement_clients.statistics.sum]
[metrics.default_ui_clients.statistics.sum]
[metrics.nonsearch_engagement_clients.statistics.ratio]
numerator = "nonsearch_engagement_clients.sum"
denominator = "default_ui_clients.sum"

### Ads data funnel statistics
[metrics.newtab_sponsored_tiles_enabled.statistics.client_count]
[metrics.sponsored_tile_impressions.statistics.client_count]
[metrics.sponsored_tile_clicks.statistics.client_count]

[metrics.newtab_sponsored_pocket_stories_enabled.statistics.client_count]
[metrics.sponsored_pocket_impressions.statistics.client_count]
[metrics.sponsored_pocket_clicks.statistics.client_count]


[metrics.sponsored_tile_ctr.statistics.population_ratio]
numerator='sponsored_tile_clicks'
denominator='sponsored_tile_impressions'

[metrics.sponsored_pocket_ctr.statistics.population_ratio]
numerator='sponsored_pocket_clicks'
denominator='sponsored_pocket_impressions'

# Data sources

[data_sources.looker_base_fields]
from_expression = """
(
  SELECT
    client_id,
    submission_date,
    sample_id,
    app_name,
    app_version,
    normalized_channel,
    country,
    experiments,
    os,
    locale,
    is_default_browser,
    partner_id,
    distribution_id,
    default_search_engine,
    normalized_os_version
  FROM
    `moz-fx-data-shared-prod`.telemetry_derived.clients_daily_v6
)
"""
friendly_name = "Default Looker Base Fields for Firefox Desktop"
description = """
  Default fields used to filter and segment metric definition views in Looker
"""
columns_as_dimensions = true
submission_date_column = "submission_date"
client_id_column = "client_id"

[data_sources.'*'.joins.looker_base_fields]

[data_sources.firefox_desktop_active_users_aggregates_view]
columns_as_dimensions = true


[data_sources.countries]
from_expression = "mozdata.static.country_codes_v1"
client_id_column = "code"
friendly_name = "Countries"
description = "Countries"
columns_as_dimensions = true
submission_date_column = "NULL"


[data_sources.desktop_engagement_v1]
from_expression = "moz-fx-data-shared-prod.telemetry.desktop_engagement"
submission_date_column = "submission_date"
description = "Aggregated DAU, WAU, and MAU by different attributes for engagement ratio calculation."
friendly_name = "Desktop Engagement"
client_id_column = "NULL"
columns_as_dimensions = true

[data_sources.desktop_engagement_v1.joins.countries]
relationship = "one_to_many"
on_expression = "desktop_engagement_v1.country = countries.code"

[data_sources.newtab_clients_daily]
columns_as_dimensions = true

[data_sources.newtab_clients_daily.joins.countries]
relationship = "one_to_many"
on_expression = "newtab_clients_daily.country_code = countries.code"

#HNT data model revamp data sources
[data_sources.newtab_clients_daily_aggregates]
from_expression = "moz-fx-data-shared-prod.analysis.gkatre__newtab_clients_daily_aggregates_v2"
submission_date_column = "submission_date"
client_id_column = "NULL"
columns_as_dimensions = false

[data_sources.newtab_clients_daily_aggregates_base_fields]
from_expression = '''(SELECT 
submission_date, app_version, os, channel, locale, browser_version, country, newtab_homepage_category, newtab_newtab_category,
organic_content_enabled, sponsored_content_enabled, sponsored_topsites_enabled, organic_topsites_enabled, newtab_search_enabled
FROM moz-fx-data-shared-prod.analysis.gkatre__newtab_clients_daily_aggregates_v2)'''
submission_date_column = "submission_date"
client_id_column = "NULL"
columns_as_dimensions = true

[data_sources.newtab_clients_daily_aggregates.joins.newtab_clients_daily_aggregates_base_fields]
relationship = "one_to_one"
on_expression = '''
(newtab_clients_daily_aggregates.submission_date = newtab_clients_daily_aggregates_base_fields.submission_date OR (newtab_clients_daily_aggregates.submission_date IS NULL AND newtab_clients_daily_aggregates_base_fields.submission_date IS NULL )) 
AND (newtab_clients_daily_aggregates.app_version = newtab_clients_daily_aggregates_base_fields.app_version OR (newtab_clients_daily_aggregates.app_version IS NULL AND newtab_clients_daily_aggregates_base_fields.app_version IS NULL ))
AND (newtab_clients_daily_aggregates.os = newtab_clients_daily_aggregates_base_fields.os OR (newtab_clients_daily_aggregates.os IS NULL AND newtab_clients_daily_aggregates_base_fields.os IS NULL ))
AND (newtab_clients_daily_aggregates.channel = newtab_clients_daily_aggregates_base_fields.channel OR (newtab_clients_daily_aggregates.channel IS NULL AND newtab_clients_daily_aggregates_base_fields.channel IS NULL ))
AND (newtab_clients_daily_aggregates.locale = newtab_clients_daily_aggregates_base_fields.locale OR (newtab_clients_daily_aggregates.locale IS NULL AND newtab_clients_daily_aggregates_base_fields.locale IS NULL ))
AND (newtab_clients_daily_aggregates.browser_version = newtab_clients_daily_aggregates_base_fields.browser_version OR (newtab_clients_daily_aggregates.browser_version IS NULL AND newtab_clients_daily_aggregates_base_fields.browser_version IS NULL ))
AND (newtab_clients_daily_aggregates.country = newtab_clients_daily_aggregates_base_fields.country OR (newtab_clients_daily_aggregates.country IS NULL AND newtab_clients_daily_aggregates_base_fields.country IS NULL ))
AND (newtab_clients_daily_aggregates.newtab_homepage_category = newtab_clients_daily_aggregates_base_fields.newtab_homepage_category OR (newtab_clients_daily_aggregates.newtab_homepage_category IS NULL AND newtab_clients_daily_aggregates_base_fields.newtab_homepage_category IS NULL ))
AND (newtab_clients_daily_aggregates.newtab_newtab_category = newtab_clients_daily_aggregates_base_fields.newtab_newtab_category OR (newtab_clients_daily_aggregates.newtab_newtab_category IS NULL AND newtab_clients_daily_aggregates_base_fields.newtab_newtab_category IS NULL ))
AND (newtab_clients_daily_aggregates.organic_content_enabled = newtab_clients_daily_aggregates_base_fields.organic_content_enabled OR (newtab_clients_daily_aggregates.organic_content_enabled IS NULL AND newtab_clients_daily_aggregates_base_fields.organic_content_enabled IS NULL ))
AND (newtab_clients_daily_aggregates.sponsored_content_enabled = newtab_clients_daily_aggregates_base_fields.sponsored_content_enabled OR (newtab_clients_daily_aggregates.sponsored_content_enabled IS NULL AND newtab_clients_daily_aggregates_base_fields.sponsored_content_enabled IS NULL ))
AND (newtab_clients_daily_aggregates.sponsored_topsites_enabled = newtab_clients_daily_aggregates_base_fields.sponsored_topsites_enabled OR (newtab_clients_daily_aggregates.sponsored_topsites_enabled IS NULL AND newtab_clients_daily_aggregates_base_fields.sponsored_topsites_enabled IS NULL ))
AND (newtab_clients_daily_aggregates.organic_topsites_enabled = newtab_clients_daily_aggregates_base_fields.organic_topsites_enabled OR (newtab_clients_daily_aggregates.organic_topsites_enabled IS NULL AND newtab_clients_daily_aggregates_base_fields.organic_topsites_enabled IS NULL ))
AND (newtab_clients_daily_aggregates.newtab_search_enabled = newtab_clients_daily_aggregates_base_fields.newtab_search_enabled OR (newtab_clients_daily_aggregates.newtab_search_enabled IS NULL AND newtab_clients_daily_aggregates_base_fields.newtab_search_enabled IS NULL ))
'''