looker/definitions/ads.toml

### data_sources.consolidated_ads_spocs
[metrics.spoc_impressions]
select_expression = "SUM(impressions)"
data_source = "consolidated_ads_spocs"
friendly_name = "SPOC impressions"
description = "Daily SPOC impressions"

[metrics.spoc_clicks]
select_expression = "SUM(clicks)"
data_source = "consolidated_ads_spocs"
friendly_name = "SPOC clicks"
description = "Daily SPOC clicks"

[metrics.spoc_revenue]
select_expression = "SUM(revenue)"
data_source = "consolidated_ads_spocs"
friendly_name = "SPOC revenue"
description = "Daily SPOC revenue"

[metrics.spoc_impressions.statistics.sum]
[metrics.spoc_clicks.statistics.sum]
[metrics.spoc_revenue.statistics.sum]

### data_sources.native_desktop_ad_metrics
[metrics.native_spend]
select_expression = "SUM(spend)"
data_source = "native_desktop_ad_metrics"
friendly_name = "Native Spend"
description ="Native Desktop Metrics spend"

[metrics.native_dismisses]
select_expression = "SUM(dismisses)"
data_source = "native_desktop_ad_metrics"
friendly_name = "Native Dismisses"
description ="Native Desktop Metrics dismisses"

[metrics.native_impressions]
select_expression = "SUM(impressions)"
data_source = "native_desktop_ad_metrics"
friendly_name = "Native Impressions"
description ="Native Desktop Metrics impressions"

[metrics.native_clicks]
select_expression = "SUM(clicks)"
data_source = "native_desktop_ad_metrics"
friendly_name = "Native Clicks"
description ="Native Desktop Metrics clicks"

[metrics.native_saves]
select_expression = "SUM(saves)"
data_source = "native_desktop_ad_metrics"
friendly_name = "Native Saves"
description ="Native Desktop Metrics saves"

[metrics.native_spend.statistics.sum]
[metrics.native_dismisses.statistics.sum]
[metrics.native_impressions.statistics.sum]
[metrics.native_clicks.statistics.sum]
[metrics.native_saves.statistics.sum]
[metrics.native_dismisses.statistics.ratio]
numerator = "native_dismisses.sum"
denominator = "native_impressions.sum"
[metrics.native_clicks.statistics.ratio]
numerator = "native_clicks.sum"
denominator = "native_impressions.sum"
[metrics.native_saves.statistics.ratio]
numerator = "native_saves.sum"
denominator = "native_impressions.sum"

### data_sources.ad_mmm_metrics_daily
[metrics.ad_impressions]
select_expression = "SUM(impressions)"
data_source = "ad_mmm_metrics_daily"
friendly_name = "Ad Impressions"
description ="Ad impressions"

[metrics.ad_clicks]
select_expression = "SUM(clicks)"
data_source = "ad_mmm_metrics_daily"
friendly_name = "Ad Clicks"
description ="Ad impressions"

[metrics.ad_spend]
select_expression = "SUM(spend)"
data_source = "ad_mmm_metrics_daily"
friendly_name = "Ad Spend"
description ="Ad spend"

[metrics.ad_impressions.statistics.sum]
[metrics.ad_clicks.statistics.sum]
[metrics.ad_spend.statistics.sum]
[metrics.ad_clicks.statistics.ratio]
numerator = "ad_clicks.sum"
denominator = "ad_impressions.sum"


[data_sources.consolidated_ads_spocs]
from_expression = """
(
  SELECT
    DATE(submission_timestamp) AS submission_date,
    advertiser as client,
    campaign_name,
    targeted_country,
    rate_type,
    creative_title as title,
    creative_url as url,
    image_url as image,
    SUM(impressions) AS impressions,
    SUM(clicks) AS clicks,
    SUM(revenue) AS revenue
  FROM `mozdata.ads.consolidated_ad_metrics_hourly`
  GROUP BY
    submission_date,
    advertiser,
    campaign_name,
    targeted_country,
    rate_type,
    creative_title,
    creative_url,
    image_url
)
"""
friendly_name = "Default Looker Base Fields for Firefox SPOC Reporting"
description = """
  Default fields used to filter and segment metric definition views in Looker
  for Firefox SPOC reporting
"""
columns_as_dimensions = true
submission_date_column = "submission_date"
client_id_column = "NULL"

[data_sources.native_desktop_ad_metrics]
from_expression = """
(
  SELECT
    *,
    REGEXP_EXTRACT(ad_url, r'utm_campaign=([^&]+)') AS campaign_name_custom,
    REGEXP_EXTRACT(ad_url, r'ref=([^&]+)') AS campaign_name_custom_2
  FROM `mozdata.ads.native_desktop_ad_metrics_hourly`
)
"""
friendly_name = "Native Desktop Ads Metrics Daily"
description = """
  Tracks Native Desktop Ad Metrics Across Days.
  Primarily used for AdOps Internal and External reporting.
  Data in this view is only available starting on April 1, 2025.
"""
columns_as_dimensions = true
submission_date_column = "submission_date"
client_id_column = "NULL"

[data_sources.ad_mmm_metrics_daily]
from_expression = "mozdata.ads.campaign_metrics_daily"
friendly_name = "Ad Campaign Metrics Daily (MMM)"
description = """
  Tracks ad metrics across days, advertisers, and campaigns, for both native and tiles.
  Primarily used for the MMM (Mix Media Modeling) data feed product.
  Data in this view goes back 24 months.
"""
columns_as_dimensions = true
submission_date_column = "submission_date"
client_id_column = "NULL"