copy code for metadata, query, schema from marketing-prod to shared-prod

[Marlene-M-Hirose]

Checklist for reviewer:

• [ ] Commits should reference a bug or github issue, if relevant (if a bug is referenced, the pull request should include the bug number in the title).
• [ ] If the PR comes from a fork, trigger integration CI tests by running the Push to upstream workflow and provide the <username>:<branch> of the fork as parameter. The parameter will also show up in the logs of the manual-trigger-required-for-fork CI task together with more detailed instructions.
• [ ] If adding a new field to a query, ensure that the schema and dependent downstream schemas have been updated.
• [ ] When adding a new derived dataset, ensure that data is not available already (fully or partially) and recommend extending an existing dataset in favor of creating new ones. Data can be available in the bigquery-etl repository, looker-hub or in looker-spoke-default.

For modifications to schemas in restricted namespaces (see CODEOWNERS):

• [ ] Follow the change control procedure

┆Issue is synchronized with this Jira Task

[dataops-ci-bot]

Integration report for "copy code for metadata, query, schema from marketing-prod to shared-prod"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-06 20:49:04.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-06 20:50:14.000000000 +0000 @@ -131,6 +131,19 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_wmo_events_table = BigQueryTableExistenceSensor( + task_id="wait_for_wmo_events_table", + project_id="moz-fx-data-marketing-prod", + dataset_id="analytics_313696158", + table_id="events_{{ ds_nodash }}", + gcp_conn_id="google_cloud_shared_prod", + deferrable=True, + poke_interval=datetime.timedelta(seconds=1800), + timeout=datetime.timedelta(seconds=36000), + retries=1, + retry_delay=datetime.timedelta(seconds=1800), + ) + checks__fail_mozilla_org_derived__ga_clients__v2 = bigquery_dq_check( task_id="checks__fail_mozilla_org_derived__ga_clients__v2", source_table="ga_clients_v2", @@ -421,6 +434,21 @@ depends_on_past=False, ) + mozilla_org_derived__www_site_metrics_summary__v2 = bigquery_etl_query( + task_id="mozilla_org_derived__www_site_metrics_summary__v2", + destination_table="www_site_metrics_summary_v2", + dataset_id="mozilla_org_derived", + project_id="moz-fx-data-shared-prod", + owner="mhirose@mozilla.com", + email=[ + "kwindau@mozilla.com", + "mhirose@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + mozilla_vpn_derived__site_metrics_summary__v2 = bigquery_etl_query( task_id="mozilla_vpn_derived__site_metrics_summary__v2", destination_table="site_metrics_summary_v2", @@ -516,6 +544,10 @@ mozilla_org_derived__www_site_downloads__v2.set_upstream(wait_for_wmo_events_table) + mozilla_org_derived__www_site_metrics_summary__v2.set_upstream( + wait_for_wmo_events_table + ) + mozilla_vpn_derived__site_metrics_summary__v2.set_upstream( wait_for_wmo_events_table ) Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: www_site_metrics_summary_v2 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/metadata.yaml 2024-08-06 20:45:11.000000000 +0000 @@ -0,0 +1,36 @@ +friendly_name: WWW Site Metrics Summary V2 +description: |- + Summary of site metrics from Google Analytics (specifically Google Analytics 4) +owners: +- mhirose@mozilla.com +labels: + incremental: true + owner1: mhirose + dag: bqetl_google_analytics_derived_ga4 +scheduling: + dag_name: bqetl_google_analytics_derived_ga4 + depends_on_tables_existing: + - task_id: wait_for_wmo_events_table + table_id: moz-fx-data-marketing-prod.analytics_313696158.events_{{ ds_nodash }} + poke_interval: 30m + timeout: 10h + retries: 1 + retry_delay: 30m +bigquery: + time_partitioning: + type: day + field: date + require_partition_filter: false + expiration_days: null + range_partitioning: null + clustering: + fields: + - country + - browser + - source + - medium +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: {} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/query.sql 2024-08-06 20:43:17.000000000 +0000 @@ -0,0 +1,200 @@ +WITH firefox_desktop_downloads_stg AS ( + SELECT + PARSE_DATE('%Y%m%d', event_date) AS `date`, + device.category AS device_category, + device.operating_system AS operating_system, + device.web_info.browser AS browser, + device.language AS `language`, + geo.country AS country, + collected_traffic_source.manual_source AS source, + collected_traffic_source.manual_medium AS medium, + collected_traffic_source.manual_campaign_name AS campaign, + collected_traffic_source.manual_content AS ad_content, + event_name, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'product' + LIMIT + 1 + ).string_value AS product_type, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'platform' + LIMIT + 1 + ).string_value AS platform_type + FROM + `moz-fx-data-marketing-prod.analytics_313696158.events_*` + WHERE + _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) + AND _TABLE_SUFFIX <= '20240216' + AND event_name = 'product_download' +), +firefox_desktop_downloads AS ( + --use this logic on or before 2024-02-16 + SELECT + `date`, + device_category, + operating_system, + browser, + `language`, + country, + source, + medium, + campaign, + ad_content, + COUNTIF(NOT `moz-fx-data-shared-prod.udf.ga_is_mozilla_browser`(browser)) AS non_fx_downloads, + COUNT(1) AS downloads + FROM + firefox_desktop_downloads_stg + WHERE + product_type = 'firefox' + AND platform_type IN ( + 'win', + 'win64', + 'macos', + 'linux64', + 'win64-msi', + 'linux', + 'win-msi', + 'win64-aarch64' + ) + GROUP BY + `date`, + device_category, + operating_system, + browser, + `language`, + country, + source, + medium, + campaign, + ad_content + UNION ALL + --use this logic on & after 2024-02-17 + SELECT + PARSE_DATE('%Y%m%d', event_date) AS `date`, + device.category AS device_category, + device.operating_system AS operating_system, + device.web_info.browser AS browser, + device.language AS `language`, + geo.country AS country, + collected_traffic_source.manual_source AS source, + collected_traffic_source.manual_medium AS medium, + collected_traffic_source.manual_campaign_name AS campaign, + collected_traffic_source.manual_content AS ad_content, + COUNTIF( + NOT `moz-fx-data-shared-prod.udf.ga_is_mozilla_browser`(device.web_info.browser) + ) AS non_fx_downloads, + COUNT(1) AS downloads + FROM + `moz-fx-data-marketing-prod.analytics_313696158.events_*` + WHERE + _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) + AND event_name = 'firefox_download' + AND _TABLE_SUFFIX >= '20240217' + GROUP BY + event_date, + device.category, + device.operating_system, + device.web_info.browser, + device.language, + geo.country, + collected_traffic_source.manual_source, + collected_traffic_source.manual_medium, + collected_traffic_source.manual_campaign_name, + collected_traffic_source.manual_content +), +sessions_data AS ( + SELECT + PARSE_DATE('%Y%m%d', event_date) AS `date`, + device.category AS device_category, + device.operating_system AS operating_system, + device.web_info.browser AS browser, + device.language AS `language`, + geo.country AS country, + collected_traffic_source.manual_source AS source, + collected_traffic_source.manual_medium AS medium, + collected_traffic_source.manual_campaign_name AS campaign, + collected_traffic_source.manual_content AS ad_content, + COUNTIF(event_name = 'session_start') AS sessions, + COUNTIF( + event_name = 'session_start' + AND NOT `moz-fx-data-shared-prod.udf.ga_is_mozilla_browser`(device.web_info.browser) + ) AS non_fx_sessions + FROM + `moz-fx-data-marketing-prod.analytics_313696158.events_*` + WHERE + _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) + GROUP BY + event_date, + device.category, + device.operating_system, + device.web_info.browser, + device.language, + geo.country, + collected_traffic_source.manual_source, + collected_traffic_source.manual_medium, + collected_traffic_source.manual_campaign_name, + collected_traffic_source.manual_content +), +sessions_and_downloads_combined AS ( + SELECT + COALESCE(sess.date, dl.date) AS date, + COALESCE(sess.device_category, dl.device_category) AS device_category, + COALESCE(sess.operating_system, dl.operating_system) AS operating_system, + COALESCE(sess.browser, dl.browser) AS browser, + COALESCE(sess.language, dl.language) AS language, + COALESCE(sess.country, dl.country) AS country, + COALESCE(sess.source, dl.source) AS source, + COALESCE(sess.medium, dl.medium) AS medium, + COALESCE(sess.campaign, dl.campaign) AS campaign, + COALESCE(sess.ad_content, dl.ad_content) AS ad_content, + COALESCE(sess.sessions, 0) AS sessions, + COALESCE(sess.non_fx_sessions, 0) AS non_fx_sessions, + COALESCE(dl.downloads, 0) AS downloads, + COALESCE(dl.non_fx_downloads, 0) AS non_fx_downloads + FROM + sessions_data sess + FULL OUTER JOIN + firefox_desktop_downloads dl + ON sess.date = dl.date + AND COALESCE(sess.device_category, '') = COALESCE(dl.device_category, '') + AND COALESCE(sess.operating_system, '') = COALESCE(dl.operating_system, '') + AND COALESCE(sess.browser, '') = COALESCE(dl.browser, '') + AND COALESCE(sess.language, '') = COALESCE(dl.language, '') + AND COALESCE(sess.country, '') = COALESCE(dl.country, '') + AND COALESCE(sess.source, 'NA') = COALESCE(dl.source, 'NA') + AND COALESCE(sess.medium, 'NA') = COALESCE(dl.medium, 'NA') + AND COALESCE(sess.campaign, 'NA') = COALESCE(dl.campaign, 'NA') + AND COALESCE(sess.ad_content, 'NA') = COALESCE(dl.ad_content, 'NA') +) +SELECT + s.date, + s.device_category, + s.operating_system, + s.browser, + s.language, + s.country, + std_cntry_nms.standardized_country AS standardized_country_name, + s.source, + s.medium, + s.campaign, + s.ad_content, + s.sessions, + s.non_fx_sessions, + s.downloads, + s.non_fx_downloads +FROM + sessions_and_downloads_combined AS s +LEFT JOIN + `moz-fx-data-shared-prod.static.third_party_standardized_country_names` AS std_cntry_nms + ON s.country = std_cntry_nms.raw_country diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/schema.yaml 2024-08-06 20:43:17.000000000 +0000 @@ -0,0 +1,61 @@ +fields: +- mode: NULLABLE + name: date + type: DATE + description: The date of the visit +- mode: NULLABLE + name: device_category + type: STRING + description: The device category the visitor used to visit the site +- mode: NULLABLE + name: operating_system + type: STRING + description: The operating system the visitor used to visit the site +- mode: NULLABLE + name: browser + type: STRING + description: The browser the visitor used to visit the site +- mode: NULLABLE + name: language + type: STRING + description: The language used by the visitor during the visit +- mode: NULLABLE + name: country + type: STRING + description: The country the visitor was from during the visit +- mode: NULLABLE + name: standardized_country_name + type: STRING + description: The standardized name of the country the visitor was from during this visit +- mode: NULLABLE + name: source + type: STRING + description: Source +- mode: NULLABLE + name: medium + type: STRING + description: Medium +- mode: NULLABLE + name: campaign + type: STRING + description: Campaign +- mode: NULLABLE + name: ad_content + type: STRING + description: Advertising Content +- mode: NULLABLE + name: sessions + type: INT64 + description: Sessions +- mode: NULLABLE + name: non_fx_sessions + type: INT64 + description: Non Firefox Sessions +- mode: NULLABLE + name: downloads + type: INT64 + description: Downloads +- mode: NULLABLE + name: non_fx_downloads + type: INT64 + description: Non Firefox Downloads ```

Link to full diff

[dataops-ci-bot]

Integration report for "Update sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/metadata.yaml"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-06 21:40:43.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_analytics_derived_ga4.py 2024-08-06 21:41:24.000000000 +0000 @@ -421,6 +421,21 @@ depends_on_past=False, ) + mozilla_org_derived__www_site_metrics_summary__v2 = bigquery_etl_query( + task_id="mozilla_org_derived__www_site_metrics_summary__v2", + destination_table="www_site_metrics_summary_v2", + dataset_id="mozilla_org_derived", + project_id="moz-fx-data-shared-prod", + owner="mhirose@mozilla.com", + email=[ + "kwindau@mozilla.com", + "mhirose@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + mozilla_vpn_derived__site_metrics_summary__v2 = bigquery_etl_query( task_id="mozilla_vpn_derived__site_metrics_summary__v2", destination_table="site_metrics_summary_v2", @@ -516,6 +531,10 @@ mozilla_org_derived__www_site_downloads__v2.set_upstream(wait_for_wmo_events_table) + mozilla_org_derived__www_site_metrics_summary__v2.set_upstream( + wait_for_wmo_events_table + ) + mozilla_vpn_derived__site_metrics_summary__v2.set_upstream( wait_for_wmo_events_table ) Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived: www_site_metrics_summary_v2 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/metadata.yaml 2024-08-06 21:35:59.000000000 +0000 @@ -0,0 +1,32 @@ +friendly_name: WWW Site Metrics Summary V2 +description: |- + Summary of site metrics from Google Analytics (specifically Google Analytics 4) +owners: +- mhirose@mozilla.com +labels: + incremental: true + owner1: mhirose + dag: bqetl_google_analytics_derived_ga4 +scheduling: + dag_name: bqetl_google_analytics_derived_ga4 + depends_on: + - task_id: wait_for_wmo_events_table + dag_name: bqetl_google_analytics_derived_ga4 +bigquery: + time_partitioning: + type: day + field: date + require_partition_filter: false + expiration_days: null + range_partitioning: null + clustering: + fields: + - country + - browser + - source + - medium +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: {} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/query.sql 2024-08-06 21:33:59.000000000 +0000 @@ -0,0 +1,200 @@ +WITH firefox_desktop_downloads_stg AS ( + SELECT + PARSE_DATE('%Y%m%d', event_date) AS `date`, + device.category AS device_category, + device.operating_system AS operating_system, + device.web_info.browser AS browser, + device.language AS `language`, + geo.country AS country, + collected_traffic_source.manual_source AS source, + collected_traffic_source.manual_medium AS medium, + collected_traffic_source.manual_campaign_name AS campaign, + collected_traffic_source.manual_content AS ad_content, + event_name, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'product' + LIMIT + 1 + ).string_value AS product_type, + ( + SELECT + `value` + FROM + UNNEST(event_params) + WHERE + key = 'platform' + LIMIT + 1 + ).string_value AS platform_type + FROM + `moz-fx-data-marketing-prod.analytics_313696158.events_*` + WHERE + _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) + AND _TABLE_SUFFIX <= '20240216' + AND event_name = 'product_download' +), +firefox_desktop_downloads AS ( + --use this logic on or before 2024-02-16 + SELECT + `date`, + device_category, + operating_system, + browser, + `language`, + country, + source, + medium, + campaign, + ad_content, + COUNTIF(NOT `moz-fx-data-shared-prod.udf.ga_is_mozilla_browser`(browser)) AS non_fx_downloads, + COUNT(1) AS downloads + FROM + firefox_desktop_downloads_stg + WHERE + product_type = 'firefox' + AND platform_type IN ( + 'win', + 'win64', + 'macos', + 'linux64', + 'win64-msi', + 'linux', + 'win-msi', + 'win64-aarch64' + ) + GROUP BY + `date`, + device_category, + operating_system, + browser, + `language`, + country, + source, + medium, + campaign, + ad_content + UNION ALL + --use this logic on & after 2024-02-17 + SELECT + PARSE_DATE('%Y%m%d', event_date) AS `date`, + device.category AS device_category, + device.operating_system AS operating_system, + device.web_info.browser AS browser, + device.language AS `language`, + geo.country AS country, + collected_traffic_source.manual_source AS source, + collected_traffic_source.manual_medium AS medium, + collected_traffic_source.manual_campaign_name AS campaign, + collected_traffic_source.manual_content AS ad_content, + COUNTIF( + NOT `moz-fx-data-shared-prod.udf.ga_is_mozilla_browser`(device.web_info.browser) + ) AS non_fx_downloads, + COUNT(1) AS downloads + FROM + `moz-fx-data-marketing-prod.analytics_313696158.events_*` + WHERE + _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) + AND event_name = 'firefox_download' + AND _TABLE_SUFFIX >= '20240217' + GROUP BY + event_date, + device.category, + device.operating_system, + device.web_info.browser, + device.language, + geo.country, + collected_traffic_source.manual_source, + collected_traffic_source.manual_medium, + collected_traffic_source.manual_campaign_name, + collected_traffic_source.manual_content +), +sessions_data AS ( + SELECT + PARSE_DATE('%Y%m%d', event_date) AS `date`, + device.category AS device_category, + device.operating_system AS operating_system, + device.web_info.browser AS browser, + device.language AS `language`, + geo.country AS country, + collected_traffic_source.manual_source AS source, + collected_traffic_source.manual_medium AS medium, + collected_traffic_source.manual_campaign_name AS campaign, + collected_traffic_source.manual_content AS ad_content, + COUNTIF(event_name = 'session_start') AS sessions, + COUNTIF( + event_name = 'session_start' + AND NOT `moz-fx-data-shared-prod.udf.ga_is_mozilla_browser`(device.web_info.browser) + ) AS non_fx_sessions + FROM + `moz-fx-data-marketing-prod.analytics_313696158.events_*` + WHERE + _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', @submission_date) + GROUP BY + event_date, + device.category, + device.operating_system, + device.web_info.browser, + device.language, + geo.country, + collected_traffic_source.manual_source, + collected_traffic_source.manual_medium, + collected_traffic_source.manual_campaign_name, + collected_traffic_source.manual_content +), +sessions_and_downloads_combined AS ( + SELECT + COALESCE(sess.date, dl.date) AS date, + COALESCE(sess.device_category, dl.device_category) AS device_category, + COALESCE(sess.operating_system, dl.operating_system) AS operating_system, + COALESCE(sess.browser, dl.browser) AS browser, + COALESCE(sess.language, dl.language) AS language, + COALESCE(sess.country, dl.country) AS country, + COALESCE(sess.source, dl.source) AS source, + COALESCE(sess.medium, dl.medium) AS medium, + COALESCE(sess.campaign, dl.campaign) AS campaign, + COALESCE(sess.ad_content, dl.ad_content) AS ad_content, + COALESCE(sess.sessions, 0) AS sessions, + COALESCE(sess.non_fx_sessions, 0) AS non_fx_sessions, + COALESCE(dl.downloads, 0) AS downloads, + COALESCE(dl.non_fx_downloads, 0) AS non_fx_downloads + FROM + sessions_data sess + FULL OUTER JOIN + firefox_desktop_downloads dl + ON sess.date = dl.date + AND COALESCE(sess.device_category, '') = COALESCE(dl.device_category, '') + AND COALESCE(sess.operating_system, '') = COALESCE(dl.operating_system, '') + AND COALESCE(sess.browser, '') = COALESCE(dl.browser, '') + AND COALESCE(sess.language, '') = COALESCE(dl.language, '') + AND COALESCE(sess.country, '') = COALESCE(dl.country, '') + AND COALESCE(sess.source, 'NA') = COALESCE(dl.source, 'NA') + AND COALESCE(sess.medium, 'NA') = COALESCE(dl.medium, 'NA') + AND COALESCE(sess.campaign, 'NA') = COALESCE(dl.campaign, 'NA') + AND COALESCE(sess.ad_content, 'NA') = COALESCE(dl.ad_content, 'NA') +) +SELECT + s.date, + s.device_category, + s.operating_system, + s.browser, + s.language, + s.country, + std_cntry_nms.standardized_country AS standardized_country_name, + s.source, + s.medium, + s.campaign, + s.ad_content, + s.sessions, + s.non_fx_sessions, + s.downloads, + s.non_fx_downloads +FROM + sessions_and_downloads_combined AS s +LEFT JOIN + `moz-fx-data-shared-prod.static.third_party_standardized_country_names` AS std_cntry_nms + ON s.country = std_cntry_nms.raw_country diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/mozilla_org_derived/www_site_metrics_summary_v2/schema.yaml 2024-08-06 21:33:59.000000000 +0000 @@ -0,0 +1,61 @@ +fields: +- mode: NULLABLE + name: date + type: DATE + description: The date of the visit +- mode: NULLABLE + name: device_category + type: STRING + description: The device category the visitor used to visit the site +- mode: NULLABLE + name: operating_system + type: STRING + description: The operating system the visitor used to visit the site +- mode: NULLABLE + name: browser + type: STRING + description: The browser the visitor used to visit the site +- mode: NULLABLE + name: language + type: STRING + description: The language used by the visitor during the visit +- mode: NULLABLE + name: country + type: STRING + description: The country the visitor was from during the visit +- mode: NULLABLE + name: standardized_country_name + type: STRING + description: The standardized name of the country the visitor was from during this visit +- mode: NULLABLE + name: source + type: STRING + description: Source +- mode: NULLABLE + name: medium + type: STRING + description: Medium +- mode: NULLABLE + name: campaign + type: STRING + description: Campaign +- mode: NULLABLE + name: ad_content + type: STRING + description: Advertising Content +- mode: NULLABLE + name: sessions + type: INT64 + description: Sessions +- mode: NULLABLE + name: non_fx_sessions + type: INT64 + description: Non Firefox Sessions +- mode: NULLABLE + name: downloads + type: INT64 + description: Downloads +- mode: NULLABLE + name: non_fx_downloads + type: INT64 + description: Non Firefox Downloads ```

Link to full diff