dataops-ci-bot
commented
3 months ago Integration report for "Temporarily add back Google Search Console views in moz-fx-data-marketing-prod to avoid breaking existing reporting."
sql.diff
Click to expand!
```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_google_search_console.py /tmp/workspace/generated-sql/dags/bqetl_google_search_console.py --- /tmp/workspace/main-generated-sql/dags/bqetl_google_search_console.py 2024-06-21 03:09:05.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_google_search_console.py 2024-06-21 03:08:07.000000000 +0000 @@ -230,7 +230,7 @@ task_id="google_search_console_derived__search_impressions_by_page__v2", destination_table='search_impressions_by_page_v2${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', dataset_id="google_search_console_derived", - project_id="moz-fx-data-marketing-prod", + project_id="moz-fx-data-shared-prod", owner="srose@mozilla.com", email=["srose@mozilla.com", "telemetry-alerts@mozilla.com"], date_partition_parameter=None, @@ -242,7 +242,7 @@ task_id="google_search_console_derived__search_impressions_by_site__v2", destination_table='search_impressions_by_site_v2${{ macros.ds_format(macros.ds_add(ds, -1), "%Y-%m-%d", "%Y%m%d") }}', dataset_id="google_search_console_derived", - project_id="moz-fx-data-marketing-prod", + project_id="moz-fx-data-shared-prod", owner="srose@mozilla.com", email=["srose@mozilla.com", "telemetry-alerts@mozilla.com"], date_partition_parameter=None, Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod: google_search_console_derived Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod: google_search_console Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod: google_search_console_derived Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/monitoring/shredder_progress: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/pocket/pocket_reach_mau: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_terms/aggregated_search_terms_daily: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_terms/sanitization_job_data_validation_metrics: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_terms/sanitization_job_languages: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/search_terms/search_terms_daily: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/buildhub2: schema.yaml diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_page/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_page/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_page/metadata.yaml 2024-06-21 03:06:02.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_page/metadata.yaml 2024-06-21 03:03:27.000000000 +0000 @@ -25,7 +25,4 @@ # Generated by bigquery_etl.dependency references: view.sql: - - moz-fx-data-marketing-prod.google_search_console_derived.search_impressions_by_page_v1 - - moz-fx-data-marketing-prod.google_search_console_derived.search_impressions_by_page_v2 - - moz-fx-data-shared-prod.static.country_codes_v1 - - moz-fx-data-shared-prod.static.language_codes_v1 + - moz-fx-data-shared-prod.google_search_console.search_impressions_by_page diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_page/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_page/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_page/view.sql 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_page/view.sql 2024-06-21 02:48:03.000000000 +0000 @@ -1,128 +1,7 @@ CREATE OR REPLACE VIEW `moz-fx-data-marketing-prod.google_search_console.search_impressions_by_page` AS -WITH search_impressions_union AS ( - SELECT - `date`, - site_url, - site_domain_name, - page_url, - page_domain_name, - page_path, - localized_site_code, - localized_site_language_code, - localized_site_country_code, - query, - FALSE AS is_anonymized, - CAST(NULL AS BOOLEAN) AS has_good_page_experience, - search_type, - CAST(NULL AS STRING) AS search_appearance, - user_country_code, - device_type, - impressions, - clicks, - average_position - FROM - `moz-fx-data-marketing-prod.google_search_console_derived.search_impressions_by_page_v1` - WHERE - CASE - WHEN site_domain_name IN ( - 'addons.mozilla.org', - 'blog.mozilla.org', - 'getpocket.com', - 'support.mozilla.org', - 'www.mozilla.org' - ) - THEN `date` < '2023-08-01' - WHEN site_domain_name = 'developer.mozilla.org' - THEN `date` < '2024-04-10' - ELSE FALSE - END - UNION ALL - SELECT - `date`, - site_url, - site_domain_name, - page_url, - page_domain_name, - page_path, - localized_site_code, - localized_site_language_code, - localized_site_country_code, - query, - is_anonymized, - has_good_page_experience, - search_type, - search_appearance, - user_country_code, - device_type, - impressions, - clicks, - average_position - FROM - `moz-fx-data-marketing-prod.google_search_console_derived.search_impressions_by_page_v2` - WHERE - CASE - WHEN site_domain_name IN ( - 'addons.mozilla.org', - 'blog.mozilla.org', - 'getpocket.com', - 'support.mozilla.org', - 'www.mozilla.org' - ) - THEN `date` >= '2023-08-01' - ELSE TRUE - END -) SELECT - search_impressions.date, - search_impressions.site_url, - search_impressions.site_domain_name, - search_impressions.page_url, - search_impressions.page_domain_name, - search_impressions.page_path, - search_impressions.localized_site_code, - CONCAT( - COALESCE(localized_site_language.name, search_impressions.localized_site_language_code), - COALESCE( - CONCAT( - ' - ', - COALESCE(localized_site_country.name, search_impressions.localized_site_country_code) - ), - '' - ) - ) AS localized_site, - search_impressions.localized_site_language_code, - COALESCE( - localized_site_language.name, - search_impressions.localized_site_language_code - ) AS localized_site_language, - search_impressions.query, - mozfun.google_search_console.classify_site_query( - search_impressions.site_domain_name, - search_impressions.query, - search_impressions.search_type - ) AS query_type, - search_impressions.is_anonymized, - search_impressions.has_good_page_experience, - search_impressions.search_type, - search_impressions.search_appearance, - search_impressions.user_country_code, - COALESCE(user_country.name, search_impressions.user_country_code) AS user_country, - user_country.region_name AS user_region, - user_country.subregion_name AS user_subregion, - search_impressions.device_type, - search_impressions.impressions, - search_impressions.clicks, - search_impressions.average_position + * FROM - search_impressions_union AS search_impressions -LEFT JOIN - `moz-fx-data-shared-prod.static.language_codes_v1` AS localized_site_language - ON search_impressions.localized_site_language_code = localized_site_language.code_2 -LEFT JOIN - `moz-fx-data-shared-prod.static.country_codes_v1` AS localized_site_country - ON search_impressions.localized_site_country_code = localized_site_country.code -LEFT JOIN - `moz-fx-data-shared-prod.static.country_codes_v1` AS user_country - ON search_impressions.user_country_code = user_country.code_3 + `moz-fx-data-shared-prod.google_search_console.search_impressions_by_page` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_site/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_site/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_site/metadata.yaml 2024-06-21 03:06:02.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_site/metadata.yaml 2024-06-21 03:03:27.000000000 +0000 @@ -25,6 +25,4 @@ # Generated by bigquery_etl.dependency references: view.sql: - - moz-fx-data-marketing-prod.google_search_console_derived.search_impressions_by_site_v1 - - moz-fx-data-marketing-prod.google_search_console_derived.search_impressions_by_site_v2 - - moz-fx-data-shared-prod.static.country_codes_v1 + - moz-fx-data-shared-prod.google_search_console.search_impressions_by_site diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_site/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_site/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_site/view.sql 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console/search_impressions_by_site/view.sql 2024-06-21 02:48:03.000000000 +0000 @@ -1,85 +1,7 @@ CREATE OR REPLACE VIEW `moz-fx-data-marketing-prod.google_search_console.search_impressions_by_site` AS -WITH search_impressions_union AS ( - SELECT - `date`, - site_url, - site_domain_name, - query, - FALSE AS is_anonymized, - search_type, - user_country_code, - device_type, - impressions, - clicks, - average_top_position - FROM - `moz-fx-data-marketing-prod.google_search_console_derived.search_impressions_by_site_v1` - WHERE - CASE - WHEN site_domain_name IN ( - 'addons.mozilla.org', - 'blog.mozilla.org', - 'getpocket.com', - 'support.mozilla.org', - 'www.mozilla.org' - ) - THEN `date` < '2023-08-01' - WHEN site_domain_name = 'developer.mozilla.org' - THEN `date` < '2024-04-10' - ELSE FALSE - END - UNION ALL - SELECT - `date`, - site_url, - site_domain_name, - query, - is_anonymized, - search_type, - user_country_code, - device_type, - impressions, - clicks, - average_top_position - FROM - `moz-fx-data-marketing-prod.google_search_console_derived.search_impressions_by_site_v2` - WHERE - CASE - WHEN site_domain_name IN ( - 'addons.mozilla.org', - 'blog.mozilla.org', - 'getpocket.com', - 'support.mozilla.org', - 'www.mozilla.org' - ) - THEN `date` >= '2023-08-01' - ELSE TRUE - END -) SELECT - search_impressions.`date`, - search_impressions.site_url, - search_impressions.site_domain_name, - search_impressions.query, - mozfun.google_search_console.classify_site_query( - search_impressions.site_domain_name, - search_impressions.query, - search_impressions.search_type - ) AS query_type, - search_impressions.is_anonymized, - search_impressions.search_type, - search_impressions.user_country_code, - COALESCE(user_country.name, search_impressions.user_country_code) AS user_country, - user_country.region_name AS user_region, - user_country.subregion_name AS user_subregion, - search_impressions.device_type, - search_impressions.impressions, - search_impressions.clicks, - search_impressions.average_top_position + * FROM - search_impressions_union AS search_impressions -LEFT JOIN - `moz-fx-data-shared-prod.static.country_codes_v1` AS user_country - ON search_impressions.user_country_code = user_country.code_3 + `moz-fx-data-shared-prod.google_search_console.search_impressions_by_site` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/metadata.yaml 2024-06-21 03:06:02.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,46 +0,0 @@ -friendly_name: Search Impressions By Page -description: |- - Google Search impressions aggregated by page, synced by Fivetran to BigQuery for the following domains: - * addons.mozilla.org - * blog.mozilla.org - * developer.mozilla.org - * getpocket.com - * support.mozilla.org - * www.mozilla.org - - Anonymized search queries aren't included. - - For the developer.mozilla.org domain, we stopped syncing Google Search Console data with Fivetran in May 2024. - See https://bugzilla.mozilla.org/show_bug.cgi?id=1890816. - - For the other domains, we stopped syncing Google Search Console data with Fivetran in August 2023. - See https://bugzilla.mozilla.org/show_bug.cgi?id=1764960#c44. -owners: -- srose@mozilla.com -labels: - incremental: true - owner1: srose -scheduling: - # Not scheduled because we stopped syncing Google Search Console data with Fivetran. - # See https://bugzilla.mozilla.org/show_bug.cgi?id=1764960#c44 and https://bugzilla.mozilla.org/show_bug.cgi?id=1890816. - #dag_name: bqetl_google_search_console - date_partition_parameter: date -bigquery: - time_partitioning: - type: day - field: date - require_partition_filter: false - expiration_days: null - clustering: - fields: - - site_domain_name - -# Generated by bigquery_etl.dependency -references: - query.sql: - - moz-fx-data-bq-fivetran.google_search_console_addons.keyword_page_report - - moz-fx-data-bq-fivetran.google_search_console_blog.keyword_page_report - - moz-fx-data-bq-fivetran.google_search_console_mdn.keyword_page_report - - moz-fx-data-bq-fivetran.google_search_console_pocket.keyword_page_report - - moz-fx-data-bq-fivetran.google_search_console_support.keyword_page_report - - moz-fx-data-bq-fivetran.google_search_console_www.keyword_page_report diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/query.sql 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,53 +0,0 @@ -{% set fivetran_gsc_datasets = [ - {'id': 'moz-fx-data-bq-fivetran.google_search_console_addons', 'query_column': 'keyword'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_blog', 'query_column': 'keyword'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_mdn', 'query_column': 'query'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_pocket', 'query_column': 'keyword'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_support', 'query_column': 'keyword'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_www', 'query_column': 'keyword'}, -] %} -WITH keyword_page_report_union AS ( - {% for fivetran_gsc_dataset in fivetran_gsc_datasets %} - {% if not loop.first %} - UNION ALL - {% endif %} - SELECT - `date`, - site, - page, - `{{ fivetran_gsc_dataset['query_column'] }}` AS query, - search_type, - country, - device, - impressions, - clicks, - position - FROM - `{{ fivetran_gsc_dataset['id'] }}.keyword_page_report` - {% endfor %} -) -SELECT - `date`, - site AS site_url, - mozfun.google_search_console.extract_url_domain_name(site) AS site_domain_name, - page AS page_url, - mozfun.google_search_console.extract_url_domain_name(page) AS page_domain_name, - mozfun.google_search_console.extract_url_path(page) AS page_path, - mozfun.google_search_console.extract_url_locale(page) AS localized_site_code, - mozfun.google_search_console.extract_url_language_code(page) AS localized_site_language_code, - mozfun.google_search_console.extract_url_country_code(page) AS localized_site_country_code, - query, - INITCAP(search_type) AS search_type, - UPPER(country) AS user_country_code, - INITCAP(device) AS device_type, - CAST(impressions AS INTEGER) AS impressions, - CAST(clicks AS INTEGER) AS clicks, - position AS average_position -FROM - keyword_page_report_union -WHERE - {% if is_init() %} - `date` < CURRENT_DATE() - {% else %} - `date` = @date - {% endif %} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/schema.yaml 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,73 +0,0 @@ -fields: -- name: date - type: DATE - mode: NULLABLE - description: The day on which the search occurred (Pacific Time). -- name: site_url - type: STRING - mode: NULLABLE - description: |- - For domain properties, this will be `sc-domain:` followed by the domain name. - For URL-prefix properties, it will be the full URL of the property definition. -- name: site_domain_name - type: STRING - mode: NULLABLE - description: Domain name of the site. -- name: page_url - type: STRING - mode: NULLABLE - description: The final page URL linked by a search result after any skip redirects. -- name: page_domain_name - type: STRING - mode: NULLABLE - description: Domain name of the page URL. -- name: page_path - type: STRING - mode: NULLABLE - description: The path part of the page URL. -- name: localized_site_code - type: STRING - mode: NULLABLE - description: Localized site code such as `en-US` or `de` found in the first segment of the page URL path (if any). -- name: localized_site_language_code - type: STRING - mode: NULLABLE - description: Localized site language code in ISO-639-alpha-2 format found in the first segment of the page URL path (if any). -- name: localized_site_country_code - type: STRING - mode: NULLABLE - description: Localized site country code in ISO-3166-1-alpha-2 format found in the first segment of the page URL path (if any). -- name: query - type: STRING - mode: NULLABLE - description: The search query. -- name: search_type - type: STRING - mode: NULLABLE - description: |- - Where the link was seen by the user: - * Web: In Google Search's default "All" tab. - * Image: In Google Search's "Images" tab. - * Video: In Google Search's "Videos" tab. - * News: In Google Search's "News" tab. -- name: user_country_code - type: STRING - mode: NULLABLE - description: Country from which the user was searching, in ISO-3166-1-alpha-3 format. -- name: device_type - type: STRING - mode: NULLABLE - description: |- - The type of device on which the user was searching: Desktop, Mobile, or Tablet. -- name: impressions - type: INTEGER - mode: NULLABLE - description: The number of times that search results with a link to the page were shown to a user. -- name: clicks - type: INTEGER - mode: NULLABLE - description: The number of times a user clicked a search result link to the page. -- name: average_position - type: FLOAT - mode: NULLABLE - description: The average position of the page in the search results, where `1` is the topmost position. diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/metadata.yaml 2024-06-21 03:06:02.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,65 +0,0 @@ -friendly_name: Search Impressions By Page -description: |- - Google Search impressions aggregated by page, exported directly to BigQuery for the following domains: - * addons.mozilla.org - * blog.mozilla.org - * developer.mozilla.org - * getpocket.com - * support.mozilla.org - * www.mozilla.org - - Anonymized search queries are included. - - For the developer.mozilla.org domain, we started exporting Google Search Console data directly to BigQuery in April 2024. - See https://bugzilla.mozilla.org/show_bug.cgi?id=1890816. - - For the other domains, we started exporting Google Search Console data directly to BigQuery in July 2023. - See https://bugzilla.mozilla.org/show_bug.cgi?id=1764960#c34. -owners: -- srose@mozilla.com -labels: - incremental: true - owner1: srose -scheduling: - dag_name: bqetl_google_search_console - date_partition_parameter: date - # Google Search Console exports typically happen two days after the data date in UTC. - date_partition_offset: -1 - depends_on_table_partitions_existing: - - task_id: wait_for_google_search_console_addons_url_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_addons.searchdata_url_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_blog_url_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_blog.searchdata_url_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_getpocket_url_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_getpocket.searchdata_url_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_mdn_url_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_mdn.searchdata_url_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_support_url_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_support.searchdata_url_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_www_url_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_www.searchdata_url_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' -bigquery: - time_partitioning: - type: day - field: date - require_partition_filter: false - expiration_days: null - clustering: - fields: - - site_domain_name - -# Generated by bigquery_etl.dependency -references: - query.sql: - - moz-fx-data-marketing-prod.searchconsole_addons.searchdata_url_impression - - moz-fx-data-marketing-prod.searchconsole_blog.searchdata_url_impression - - moz-fx-data-marketing-prod.searchconsole_getpocket.searchdata_url_impression - - moz-fx-data-marketing-prod.searchconsole_mdn.searchdata_url_impression - - moz-fx-data-marketing-prod.searchconsole_support.searchdata_url_impression - - moz-fx-data-marketing-prod.searchconsole_www.searchdata_url_impression diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/query.sql 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,95 +0,0 @@ -{% set gsc_export_dataset_ids = [ - 'moz-fx-data-marketing-prod.searchconsole_addons', - 'moz-fx-data-marketing-prod.searchconsole_blog', - 'moz-fx-data-marketing-prod.searchconsole_getpocket', - 'moz-fx-data-marketing-prod.searchconsole_mdn', - 'moz-fx-data-marketing-prod.searchconsole_support', - 'moz-fx-data-marketing-prod.searchconsole_www', -] %} -{% set search_appearance_flag_columns = { - 'is_action': 'Action', - 'is_amp_blue_link': 'AMP non-rich result', - 'is_amp_image_result': 'AMP on image result', - 'is_amp_story': 'AMP story', - 'is_amp_top_stories': 'AMP top stories', - 'is_edu_q_and_a': 'Education Q&A', - 'is_events_details': 'Event details', - 'is_events_listing': 'Event listing', - 'is_job_details': 'Job details', - 'is_job_listing': 'Job listing', - 'is_learning_videos': 'Learning videos', - 'is_math_solvers': 'Math solvers', - 'is_merchant_listings': 'Merchant listings', - 'is_organic_shopping': 'Shopping', - 'is_practice_problems': 'Practice problems', - 'is_product_snippets': 'Product snippets', - 'is_recipe_feature': 'Recipe feature', - 'is_recipe_rich_snippet': 'Recipe rich snippet', - 'is_review_snippet': 'Review snippet', - 'is_search_appearance_android_app': 'Android app', - 'is_special_announcement': 'Special announcement', - 'is_subscribed_content': 'Subscribed content', - 'is_tpf_faq': 'FAQ rich result', - 'is_tpf_howto': 'How-to rich result', - 'is_tpf_qa': 'Q&A rich result', - 'is_translated_result': 'Translated result', - 'is_video': 'Video', -} %} -WITH searchdata_url_impression_union AS ( - {% for gsc_export_dataset_id in gsc_export_dataset_ids %} - {% if not loop.first %} - UNION ALL - {% endif %} - SELECT - data_date, - site_url, - url, - query, - is_anonymized_query, - is_anonymized_discover, - is_page_experience, - search_type, - {{ search_appearance_flag_columns | join(',\n') }}, - country, - device, - impressions, - clicks, - sum_position - FROM - `{{ gsc_export_dataset_id }}.searchdata_url_impression` - {% endfor %} -) -SELECT - data_date AS `date`, - site_url, - mozfun.google_search_console.extract_url_domain_name(site_url) AS site_domain_name, - url AS page_url, - mozfun.google_search_console.extract_url_domain_name(url) AS page_domain_name, - mozfun.google_search_console.extract_url_path(url) AS page_path, - mozfun.google_search_console.extract_url_locale(url) AS localized_site_code, - mozfun.google_search_console.extract_url_language_code(url) AS localized_site_language_code, - mozfun.google_search_console.extract_url_country_code(url) AS localized_site_country_code, - query, - (is_anonymized_query OR is_anonymized_discover) AS is_anonymized, - is_page_experience AS has_good_page_experience, - INITCAP(REPLACE(search_type, '_', ' ')) AS search_type, - CASE - {% for search_appearance_flag_column, search_appearance_label in search_appearance_flag_columns.items() %} - WHEN {{ search_appearance_flag_column }} - THEN '{{ search_appearance_label }}' - {% endfor %} - ELSE 'Normal result' - END AS search_appearance, - UPPER(country) AS user_country_code, - INITCAP(device) AS device_type, - impressions, - clicks, - ((sum_position / impressions) + 1) AS average_position -FROM - searchdata_url_impression_union -WHERE - {% if is_init() %} - data_date < CURRENT_DATE() - {% else %} - data_date = @date - {% endif %} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/schema.yaml 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_page_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,109 +0,0 @@ -fields: -- name: date - type: DATE - mode: NULLABLE - description: The day on which the search occurred (Pacific Time). -- name: site_url - type: STRING - mode: NULLABLE - description: |- - For domain properties, this will be `sc-domain:` followed by the domain name. - For URL-prefix properties, it will be the full URL of the property definition. -- name: site_domain_name - type: STRING - mode: NULLABLE - description: Domain name of the site. -- name: page_url - type: STRING - mode: NULLABLE - description: |- - The final page URL linked by a search result after any skip redirects. - This will be null for anonymized Discover impressions. -- name: page_domain_name - type: STRING - mode: NULLABLE - description: |- - Domain name of the page URL. - This will be null for anonymized Discover impressions. -- name: page_path - type: STRING - mode: NULLABLE - description: |- - The path part of the page URL. - This will be null for anonymized Discover impressions. -- name: localized_site_code - type: STRING - mode: NULLABLE - description: |- - Localized site code such as `en-US` or `de` found in the first segment of the page URL path (if any). - This will be null for anonymized Discover impressions. -- name: localized_site_language_code - type: STRING - mode: NULLABLE - description: |- - Localized site language code in ISO-639-alpha-2 format found in the first segment of the page URL path (if any). - This will be null for anonymized Discover impressions. -- name: localized_site_country_code - type: STRING - mode: NULLABLE - description: |- - Localized site country code in ISO-3166-1-alpha-2 format found in the first segment of the page URL path (if any). - This will be null for anonymized Discover impressions. -- name: query - type: STRING - mode: NULLABLE - description: |- - The search query. - This will be null for anonymized search impressions, and all Discover and Google News search impressions. -- name: is_anonymized - type: BOOLEAN - mode: NULLABLE - description: |- - Whether Google has anonymized the search impression to protect the users' privacy. - The `query` field will be null for anonymized search impressions. - The `country_code`, `page_url`, and related fields will be null for anonymized Discover impressions. -- name: has_good_page_experience - type: BOOLEAN - mode: NULLABLE - description: Whether Google Search considers the page to be providing a good page experience. -- name: search_type - type: STRING - mode: NULLABLE - description: |- - Where the link was seen by the user: - * Web: In Google Search's default "All" tab. - * Image: In Google Search's "Images" tab. - * Video: In Google Search's "Videos" tab. - * News: In Google Search's "News" tab. - * Discover: In Google's Discover feed. - * Google News: On news.google.com or in the Google News app on Android and iOS. -- name: search_appearance - type: STRING - mode: NULLABLE - description: How the search result appeared (e.g. normal result, translated result, video). -- name: user_country_code - type: STRING - mode: NULLABLE - description: |- - Country from which the user was searching, in ISO-3166-1-alpha-3 format. - This will be null for anonymized Discover impressions. -- name: device_type - type: STRING - mode: NULLABLE - description: |- - The type of device on which the user was searching: Desktop, Mobile, or Tablet. - This will be null for Discover impressions. -- name: impressions - type: INTEGER - mode: NULLABLE - description: The number of times that search results with a link to the page were shown to a user. -- name: clicks - type: INTEGER - mode: NULLABLE - description: The number of times a user clicked a search result link to the page. -- name: average_position - type: FLOAT - mode: NULLABLE - description: |- - The average position of the page in the search results, where `1` is the topmost position. - This will be null for Discover and Google News search impressions. diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/metadata.yaml 2024-06-21 03:06:02.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,46 +0,0 @@ -friendly_name: Search Impressions By Site -description: |- - Google Search impressions aggregated by site, synced by Fivetran to BigQuery for the following domains: - * addons.mozilla.org - * blog.mozilla.org - * developer.mozilla.org - * getpocket.com - * support.mozilla.org - * www.mozilla.org - - Anonymized search queries aren't included. - - For the developer.mozilla.org domain, we stopped syncing Google Search Console data with Fivetran in May 2024. - See https://bugzilla.mozilla.org/show_bug.cgi?id=1890816. - - For the other domains, we stopped syncing Google Search Console data with Fivetran in August 2023. - See https://bugzilla.mozilla.org/show_bug.cgi?id=1764960#c44. -owners: -- srose@mozilla.com -labels: - incremental: true - owner1: srose -scheduling: - # Not scheduled because we stopped syncing Google Search Console data with Fivetran. - # See https://bugzilla.mozilla.org/show_bug.cgi?id=1764960#c44 and https://bugzilla.mozilla.org/show_bug.cgi?id=1890816. - #dag_name: bqetl_google_search_console - date_partition_parameter: date -bigquery: - time_partitioning: - type: day - field: date - require_partition_filter: false - expiration_days: null - clustering: - fields: - - site_domain_name - -# Generated by bigquery_etl.dependency -references: - query.sql: - - moz-fx-data-bq-fivetran.google_search_console_addons.keyword_site_report_by_site - - moz-fx-data-bq-fivetran.google_search_console_blog.keyword_site_report_by_site - - moz-fx-data-bq-fivetran.google_search_console_mdn.keyword_site_report_by_site - - moz-fx-data-bq-fivetran.google_search_console_pocket.keyword_site_report_by_site - - moz-fx-data-bq-fivetran.google_search_console_support.keyword_site_report_by_site - - moz-fx-data-bq-fivetran.google_search_console_www.keyword_site_report_by_site diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/query.sql 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,46 +0,0 @@ -{% set fivetran_gsc_datasets = [ - {'id': 'moz-fx-data-bq-fivetran.google_search_console_addons', 'query_column': 'keyword'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_blog', 'query_column': 'keyword'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_mdn', 'query_column': 'query'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_pocket', 'query_column': 'keyword'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_support', 'query_column': 'keyword'}, - {'id': 'moz-fx-data-bq-fivetran.google_search_console_www', 'query_column': 'keyword'}, -] %} -WITH keyword_site_report_by_site_union AS ( - {% for fivetran_gsc_dataset in fivetran_gsc_datasets %} - {% if not loop.first %} - UNION ALL - {% endif %} - SELECT - `date`, - site, - `{{ fivetran_gsc_dataset['query_column'] }}` AS query, - search_type, - country, - device, - impressions, - clicks, - position - FROM - `{{ fivetran_gsc_dataset['id'] }}.keyword_site_report_by_site` - {% endfor %} -) -SELECT - `date`, - site AS site_url, - mozfun.google_search_console.extract_url_domain_name(site) AS site_domain_name, - query, - INITCAP(search_type) AS search_type, - UPPER(country) AS user_country_code, - INITCAP(device) AS device_type, - CAST(impressions AS INTEGER) AS impressions, - CAST(clicks AS INTEGER) AS clicks, - position AS average_top_position -FROM - keyword_site_report_by_site_union -WHERE - {% if is_init() %} - `date` < CURRENT_DATE() - {% else %} - `date` = @date - {% endif %} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/schema.yaml 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,49 +0,0 @@ -fields: -- name: date - type: DATE - mode: NULLABLE - description: The day on which the search occurred (Pacific Time). -- name: site_url - type: STRING - mode: NULLABLE - description: |- - For domain properties, this will be `sc-domain:` followed by the domain name. - For URL-prefix properties, it will be the full URL of the property definition. -- name: site_domain_name - type: STRING - mode: NULLABLE - description: Domain name of the site. -- name: query - type: STRING - mode: NULLABLE - description: The search query. -- name: search_type - type: STRING - mode: NULLABLE - description: |- - Where the link was seen by the user: - * Web: In Google Search's default "All" tab. - * Image: In Google Search's "Images" tab. - * Video: In Google Search's "Videos" tab. - * News: In Google Search's "News" tab. -- name: user_country_code - type: STRING - mode: NULLABLE - description: Country from which the user was searching, in ISO-3166-1-alpha-3 format. -- name: device_type - type: STRING - mode: NULLABLE - description: |- - The type of device on which the user was searching: Desktop, Mobile, or Tablet. -- name: impressions - type: INTEGER - mode: NULLABLE - description: The number of times that search results with at least one link to the site were shown to a user. -- name: clicks - type: INTEGER - mode: NULLABLE - description: The number of times a user clicked at least one search result link to the site. -- name: average_top_position - type: FLOAT - mode: NULLABLE - description: The average top position of the site in the search results, where `1` is the topmost position. diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/metadata.yaml 2024-06-21 03:06:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,65 +0,0 @@ -friendly_name: Search Impressions By Site -description: |- - Google Search impressions aggregated by site, exported directly to BigQuery for the following domains: - * addons.mozilla.org - * blog.mozilla.org - * developer.mozilla.org - * getpocket.com - * support.mozilla.org - * www.mozilla.org - - Anonymized search queries are included. - - For the developer.mozilla.org domain, we started exporting Google Search Console data directly to BigQuery in April 2024. - See https://bugzilla.mozilla.org/show_bug.cgi?id=1890816. - - For the other domains, we started exporting Google Search Console data directly to BigQuery in July 2023. - See https://bugzilla.mozilla.org/show_bug.cgi?id=1764960#c34. -owners: -- srose@mozilla.com -labels: - incremental: true - owner1: srose -scheduling: - dag_name: bqetl_google_search_console - date_partition_parameter: date - # Google Search Console exports typically happen two days after the data date in UTC. - date_partition_offset: -1 - depends_on_table_partitions_existing: - - task_id: wait_for_google_search_console_addons_site_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_addons.searchdata_site_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_blog_site_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_blog.searchdata_site_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_getpocket_site_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_getpocket.searchdata_site_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_mdn_site_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_mdn.searchdata_site_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_support_site_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_support.searchdata_site_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' - - task_id: wait_for_google_search_console_www_site_impressions - table_id: moz-fx-data-marketing-prod.searchconsole_www.searchdata_site_impression - partition_id: '{{ data_interval_start.subtract(days=1) | ds_nodash }}' -bigquery: - time_partitioning: - type: day - field: date - require_partition_filter: false - expiration_days: null - clustering: - fields: - - site_domain_name - -# Generated by bigquery_etl.dependency -references: - query.sql: - - moz-fx-data-marketing-prod.searchconsole_addons.searchdata_site_impression - - moz-fx-data-marketing-prod.searchconsole_blog.searchdata_site_impression - - moz-fx-data-marketing-prod.searchconsole_getpocket.searchdata_site_impression - - moz-fx-data-marketing-prod.searchconsole_mdn.searchdata_site_impression - - moz-fx-data-marketing-prod.searchconsole_support.searchdata_site_impression - - moz-fx-data-marketing-prod.searchconsole_www.searchdata_site_impression diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/query.sql 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,48 +0,0 @@ -{% set gsc_export_dataset_ids = [ - 'moz-fx-data-marketing-prod.searchconsole_addons', - 'moz-fx-data-marketing-prod.searchconsole_blog', - 'moz-fx-data-marketing-prod.searchconsole_getpocket', - 'moz-fx-data-marketing-prod.searchconsole_mdn', - 'moz-fx-data-marketing-prod.searchconsole_support', - 'moz-fx-data-marketing-prod.searchconsole_www', -] %} -WITH searchdata_site_impression_union AS ( - {% for gsc_export_dataset_id in gsc_export_dataset_ids %} - {% if not loop.first %} - UNION ALL - {% endif %} - SELECT - data_date, - site_url, - query, - is_anonymized_query, - search_type, - country, - device, - impressions, - clicks, - sum_top_position - FROM - `{{ gsc_export_dataset_id }}.searchdata_site_impression` - {% endfor %} -) -SELECT - data_date AS `date`, - site_url, - mozfun.google_search_console.extract_url_domain_name(site_url) AS site_domain_name, - query, - is_anonymized_query AS is_anonymized, - INITCAP(REPLACE(search_type, '_', ' ')) AS search_type, - UPPER(country) AS user_country_code, - INITCAP(device) AS device_type, - impressions, - clicks, - ((sum_top_position / impressions) + 1) AS average_top_position -FROM - searchdata_site_impression_union -WHERE - {% if is_init() %} - data_date < CURRENT_DATE() - {% else %} - data_date = @date - {% endif %} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/schema.yaml 2024-06-21 03:05:55.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-marketing-prod/google_search_console_derived/search_impressions_by_site_v2/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,55 +0,0 @@ -fields: -- name: date - type: DATE - mode: NULLABLE - description: The day on which the search occurred (Pacific Time). -- name: site_url - type: STRING - mode: NULLABLE - description: |- - For domain properties, this will be `sc-domain:` followed by the domain name. - For URL-prefix properties, it will be the full URL of the property definition. -- name: site_domain_name - type: STRING - mode: NULLABLE - description: Domain name of the site. -- name: query - type: STRING - mode: NULLABLE - description: The search query. -- name: is_anonymized - type: BOOLEAN - mode: NULLABLE - description: |- - Whether Google has anonymized the search impression to protect the users' privacy. - The `query` field will be null for anonymized search impressions. -- name: search_type - type: STRING - mode: NULLABLE - description: |- - Where the link was seen by the user: - * Web: In Google Search's default "All" tab. - * Image: In Google Search's "Images" tab. - * Video: In Google Search's "Videos" tab. - * News: In Google Search's "News" tab. -- name: user_country_code - type: STRING - mode: NULLABLE - description: Country from which the user was searching, in ISO-3166-1-alpha-3 format. -- name: device_type - type: STRING - mode: NULLABLE - description: |- - The type of device on which the user was searching: Desktop, Mobile, or Tablet. -- name: impressions - type: INTEGER - mode: NULLABLE - description: The number of times that search results with at least one link to the site were shown to a user. -- name: clicks - type: INTEGER - mode: NULLABLE - description: The number of times a user clicked at least one search result link to the site. -- name: average_top_position - type: FLOAT - mode: NULLABLE - description: The average top position of the site in the search results, where `1` is the topmost position. diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_source_type_report/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_source_type_report/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_source_type_report/schema.yaml 2024-06-21 03:05:49.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_app_store_territory_source_type_report/schema.yaml 2024-06-21 02:55:12.000000000 +0000 @@ -1 +1,34 @@ -{} +fields: +- name: app_id + type: INTEGER + mode: NULLABLE +- name: date + type: TIMESTAMP + mode: NULLABLE +- name: source_type + type: STRING + mode: NULLABLE +- name: territory + type: STRING + mode: NULLABLE +- name: _fivetran_synced + type: TIMESTAMP + mode: NULLABLE +- name: impressions + type: INTEGER + mode: NULLABLE +- name: impressions_unique_device + type: INTEGER + mode: NULLABLE +- name: meets_threshold + type: BOOLEAN + mode: NULLABLE +- name: page_views + type: INTEGER + mode: NULLABLE +- name: page_views_unique_device + type: INTEGER + mode: NULLABLE +- name: date_pst + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_source_type_report/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_source_type_report/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_source_type_report/schema.yaml 2024-06-21 03:05:49.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_downloads_territory_source_type_report/schema.yaml 2024-06-21 02:55:12.000000000 +0000 @@ -1 +1,31 @@ -{} +fields: +- name: app_id + type: INTEGER + mode: NULLABLE +- name: date + type: TIMESTAMP + mode: NULLABLE +- name: source_type + type: STRING + mode: NULLABLE +- name: territory + type: STRING + mode: NULLABLE +- name: _fivetran_synced + type: TIMESTAMP + mode: NULLABLE +- name: first_time_downloads + type: INTEGER + mode: NULLABLE +- name: meets_threshold + type: BOOLEAN + mode: NULLABLE +- name: redownloads + type: INTEGER + mode: NULLABLE +- name: total_downloads + type: INTEGER + mode: NULLABLE +- name: date_pst + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_source_type_report/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_source_type_report/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_source_type_report/schema.yaml 2024-06-21 03:05:49.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/app_store/firefox_usage_territory_source_type_report/schema.yaml 2024-06-21 02:55:12.000000000 +0000 @@ -1 +1,37 @@ -{} +fields: +- name: app_id + type: INTEGER + mode: NULLABLE +- name: date + type: TIMESTAMP + mode: NULLABLE +- name: source_type + type: STRING + mode: NULLABLE +- name: territory + type: STRING + mode: NULLABLE +- name: _fivetran_synced + type: TIMESTAMP + mode: NULLABLE +- name: active_devices + type: INTEGER + mode: NULLABLE +- name: active_devices_last_30_days + type: INTEGER + mode: NULLABLE +- name: deletions + type: INTEGER + mode: NULLABLE +- name: installations + type: INTEGER + mode: NULLABLE +- name: meets_threshold + type: BOOLEAN + mode: NULLABLE +- name: sessions + type: INTEGER + mode: NULLABLE +- name: date_pst + type: TIMESTAMP + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/adm_forecasting/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/adm_forecasting/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/adm_forecasting/schema.yaml 2024-06-21 03:05:49.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/adm_forecasting/schema.yaml 2024-06-21 02:55:15.000000000 +0000 @@ -1 +1,43 @@ -{} +fields: +- name: product + type: STRING + mode: NULLABLE +- name: submission_date + type: DATE + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: device + type: STRING + mode: NULLABLE +- name: eligible_share_country + type: FLOAT + mode: NULLABLE +- name: clients + type: INTEGER + mode: NULLABLE +- name: p_amazon + type: FLOAT + mode: NULLABLE +- name: p_other + type: FLOAT + mode: NULLABLE +- name: amazon_clients + type: FLOAT + mode: NULLABLE +- name: other_clients + type: FLOAT + mode: NULLABLE +- name: amazon_clicks + type: INTEGER + mode: NULLABLE +- name: other_clicks + type: INTEGER + mode: NULLABLE +- name: amazon_clicks_per_client + type: FLOAT + mode: NULLABLE +- name: other_clicks_per_client + type: FLOAT + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-06-21 03:05:49.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-06-21 02:55:15.000000000 +0000 @@ -1 +1,49 @@ -{} +fields: +- name: submission_date + type: DATE + mode: NULLABLE +- name: source + type: STRING + mode: NULLABLE +- name: event_type + type: STRING + mode: NULLABLE +- name: form_factor + type: STRING + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: subdivision1 + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: provider + type: STRING + mode: NULLABLE +- name: match_type + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: suggest_data_sharing_enabled + type: BOOLEAN + mode: NULLABLE +- name: event_count + type: INTEGER + mode: NULLABLE +- name: user_count + type: INTEGER + mode: NULLABLE +- name: query_type + type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_spons_tiles/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_spons_tiles/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_spons_tiles/schema.yaml 2024-06-21 03:05:49.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_spons_tiles/schema.yaml 2024-06-21 02:55:15.000000000 +0000 @@ -1 +1,31 @@ -{} +fields: +- name: submission_date + type: DATE + mode: NULLABLE +- name: form_factor + type: STRING + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: provider + type: STRING + mode: NULLABLE +- name: impression_count + type: INTEGER + mode: NULLABLE +- name: click_count + type: INTEGER + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-06-21 03:05:49.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-06-21 02:55:15.000000000 +0000 @@ -1 +1,40 @@ -{} +fields: +- name: submission_date + type: DATE + mode: NULLABLE +- name: form_factor + type: STRING + mode: NULLABLE +- name: country + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: provider + type: STRING + mode: NULLABLE +- name: match_type + type: STRING + mode: NULLABLE +- name: suggest_data_sharing_enabled + type: BOOLEAN + mode: NULLABLE +- name: impression_count + type: INTEGER + mode: NULLABLE +- name: click_count + type: INTEGER + mode: NULLABLE +- name: query_type + type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_click_live/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_click_live/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_click_live/schema.yaml 2024-06-21 03:05:49.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_click_live/schema.yaml 2024-06-21 02:55:14.000000000 +0000 @@ -1 +1,174 @@ -{} +fields: +- name: additional_properties + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: block_id + type: INTEGER + mode: NULLABLE +- name: context_id + type: STRING + mode: NULLABLE +- name: document_id + type: STRING + mode: NULLABLE +- name: experiments + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: RECORD + mode: NULLABLE + fields: + - name: branch + type: STRING + mode: NULLABLE +- name: locale + type: STRING + mode: NULLABLE +- name: metadata + type: RECORD + mode: NULLABLE + fields: + - name: geo + type: RECORD + mode: NULLABLE + fields: + - name: city + type: STRING + mode: NULLABLE + - name: country + type: STRING + mode: NULLABLE + - name: db_version + type: STRING + mode: NULLABLE + - name: subdivision1 + type: STRING + mode: NULLABLE + - name: subdivision2 + type: STRING + mode: NULLABLE + - name: header + type: RECORD + mode: NULLABLE + fields: + - name: date + type: STRING + mode: NULLABLE + - name: dnt + type: STRING + mode: NULLABLE + - name: x_debug_id + type: STRING + mode: NULLABLE + - name: x_pingsender_version + type: STRING + mode: NULLABLE + - name: x_source_tags + type: STRING + mode: NULLABLE + - name: x_telemetry_agent + type: STRING + mode: NULLABLE + - name: x_foxsec_ip_reputation + type: STRING + mode: NULLABLE + - name: x_lb_tags + type: STRING + mode: NULLABLE + - name: parsed_date + type: TIMESTAMP + mode: NULLABLE + - name: parsed_x_source_tags + type: STRING + mode: REPEATED + - name: parsed_x_lb_tags + type: RECORD + mode: NULLABLE + fields: + - name: tls_version + type: STRING + mode: NULLABLE + - name: tls_cipher_hex + type: STRING + mode: NULLABLE + - name: isp + type: RECORD + mode: NULLABLE + fields: + - name: db_version + type: STRING + mode: NULLABLE + - name: name + type: STRING + mode: NULLABLE + - name: organization + type: STRING + mode: NULLABLE + - name: user_agent + type: RECORD + mode: NULLABLE + fields: + - name: browser + type: STRING + mode: NULLABLE + - name: os + type: STRING + mode: NULLABLE + - name: version + type: STRING + mode: NULLABLE +- name: normalized_app_name + type: STRING + mode: NULLABLE +- name: normalized_channel + type: STRING + mode: NULLABLE +- name: normalized_country_code + type: STRING + mode: NULLABLE +- name: normalized_os + type: STRING + mode: NULLABLE +- name: normalized_os_version + type: STRING + mode: NULLABLE +- name: position + type: INTEGER + mode: NULLABLE +- name: release_channel + type: STRING + mode: NULLABLE +- name: reporting_url + type: STRING + mode: NULLABLE +- name: sample_id + type: INTEGER + mode: NULLABLE +- name: submission_timestamp + type: TIMESTAMP + mode: NULLABLE +- name: version + type: STRING + mode: NULLABLE +- name: scenario + type: STRING + mode: NULLABLE +- name: request_id + type: STRING + mode: NULLABLE +- name: match_type + type: STRING + mode: NULLABLE +- name: improve_suggest_experience_checked + type: BOOLEAN + mode: NULLABLE +- name: source + type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_impression_live/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_impression_live/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_impression_live/schema.yaml 2024-06-21 03:05:49.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/quicksuggest_impression_live/schema.yaml 2024-06-21 02:55:14.000000000 +0000 @@ -1 +1,177 @@ -{} +fields: +- name: additional_properties + type: STRING + mode: NULLABLE +- name: advertiser + type: STRING + mode: NULLABLE +- name: block_id + type: INTEGER + mode: NULLABLE +- name: context_id + type: STRING + mode: NULLABLE +- name: document_id + type: STRING + mode: NULLABLE +- name: experiments + type: RECORD + mode: REPEATED + fields: + - name: key + type: STRING + mode: NULLABLE + - name: value + type: RECORD + mode: NULLABLE + fields: + - name: branch + type: STRING + mode: NULLABLE +- name: is_clicked + type: BOOLEAN + mode: NULLABLE +- name: locale + type: STRING + mode: NULLABLE +- name: metadata + type: RECORD + mode: NULLABLE + fields: + - name: geo + type: RECORD + mode: NULLABLE + fields: + - name: city + type: STRING + mode: NULLABLE + - name: country + type: STRING + mode: NULLABLE + - name: db_version + type: STRING + mode: NULLABLE + - name: subdivision1 + type: STRING + mode: NULLABLE + - name: subdivision2 + type: STRING + mode: NULLABLE + - name: header + type: RECORD + mode: NULLABLE + fields: + - name: date + type: STRING + mode: NULLABLE + - name: dnt + type: STRING + mode: NULLABLE + - name: x_debug_id + type: STRING + mode: NULLABLE + - name: x_pingsender_version + type: STRING + mode: NULLABLE + - name: x_source_tags + type: STRING + mode: NULLABLE + - name: x_telemetry_agent + type: STRING + mode: NULLABLE + - name: x_foxsec_ip_reputation + type: STRING + mode: NULLABLE + - name: x_lb_tags + type: STRING + mode: NULLABLE + - name: parsed_date + type: TIMESTAMP + mode: NULLABLE + - name: parsed_x_source_tags + type: STRING + mode: REPEATED + - name: ```⚠️ Only part of the diff is displayed.
sean-rose
DENG-1733: Make Google Search Console data available for use
As discussed during the Data Org work week, going forward
moz-fx-data-marketing-prodwill be used as a place for Google Search Console, Google Analytics, etc. to export data, but all ETLs and user-facing views should be inmoz-fx-data-shared-prod&mozdata.Checklist for reviewer:
<username>:<branch>of the fork as parameter. The parameter will also show up in the logs of themanual-trigger-required-for-forkCI task together with more detailed instructions.For modifications to schemas in restricted namespaces (see
CODEOWNERS):┆Issue is synchronized with this Jira Task