To supersede `urlbar_events_daily` with inclusion of `annoyance_signal_type`

[teonbrooks]

this table is to supersede urlbar_events_daily with clearer name, pattern for other derived tables, and inclusion of annoyance signal

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 "this table is to supersede urlbar_events_daily with clearer name, pattern for other derived tables, and inclusion of annoyance signal"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_urlbar.py /tmp/workspace/generated-sql/dags/bqetl_urlbar.py --- /tmp/workspace/main-generated-sql/dags/bqetl_urlbar.py 2024-05-30 20:01:27.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_urlbar.py 2024-05-30 20:02:40.000000000 +0000 @@ -112,6 +112,23 @@ depends_on_past=False, ) + firefox_desktop_derived__urlbar_events_engagement_by_product_type_aggregates__v1 = bigquery_etl_query( + task_id="firefox_desktop_derived__urlbar_events_engagement_by_product_type_aggregates__v1", + destination_table="urlbar_events_engagement_by_product_type_aggregates_v1", + dataset_id="firefox_desktop_derived", + project_id="moz-fx-data-shared-prod", + owner="tbrooks@mozilla.com", + email=[ + "akomar@mozilla.com", + "akommasani@mozilla.com", + "anicholson@mozilla.com", + "tbrooks@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + firefox_desktop_urlbar_events__v2 = bigquery_etl_query( task_id="firefox_desktop_urlbar_events__v2", destination_table="urlbar_events_v2", @@ -154,6 +171,10 @@ firefox_desktop_urlbar_events__v2 ) + firefox_desktop_derived__urlbar_events_engagement_by_product_type_aggregates__v1.set_upstream( + firefox_desktop_urlbar_events__v2 + ) + firefox_desktop_urlbar_events__v2.set_upstream(wait_for_copy_deduplicate_all) telemetry_derived__urlbar_clients_daily__v1.set_upstream( Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop: urlbar_events_engagement_by_product_type_aggregates Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived: urlbar_events_engagement_by_product_type_aggregates_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/metadata.yaml 2024-05-30 19:59:27.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Urlbar Events Engagement By Product Type Aggregates +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.firefox_desktop_derived.urlbar_events_engagement_by_product_type_aggregates_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/view.sql 2024-05-30 19:57:43.000000000 +0000 @@ -0,0 +1,7 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.firefox_desktop.urlbar_events_engagement_by_product_type_aggregates` +AS +SELECT + * +FROM + `moz-fx-data-shared-prod.firefox_desktop_derived.urlbar_events_engagement_by_product_type_aggregates_v1` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/backfill.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/backfill.yaml 2024-05-30 19:57:43.000000000 +0000 @@ -0,0 +1,7 @@ +2024-05-31: + start_date: 2023-10-24 + end_date: 2024-05-31 + reason: Recreating a table and would like to have the historical data, which dates back to the start date of the backfill + watchers: + - tbrooks@mozilla.com + status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/metadata.yaml 2024-05-30 19:59:22.000000000 +0000 @@ -0,0 +1,32 @@ +friendly_name: Urlbar Events Engagement by Product Type Aggregates +description: |- + Each row in this table represents a daily summary statistic of impressions + and clicks per product result type per a host of meaningful dimensions. +owners: +- tbrooks@mozilla.com +- akommasani@mozilla.com +labels: + incremental: true + schedule: daily + dag: bqetl_urlbar + owner1: tbrooks + owner2: akommasani +scheduling: + dag_name: bqetl_urlbar +bigquery: + time_partitioning: + type: day + field: submission_date + require_partition_filter: true + expiration_days: null + range_partitioning: null + clustering: + fields: + - normalized_channel +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + query.sql: + - mozdata.firefox_desktop.urlbar_events diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/query.sql 2024-05-30 19:57:43.000000000 +0000 @@ -0,0 +1,113 @@ +WITH temp_unnested AS ( + SELECT + submission_date, + event_id, + event_action, + res.product_result_type AS product_result_type, + annoyance_signal_type, + normalized_channel, + normalized_country_code, + pref_fx_suggestions AS firefox_suggest_enabled, + pref_sponsored_suggestions AS sponsored_suggestions_enabled, + is_terminal, + ( + product_selected_result = res.product_result_type + AND event_action = 'engaged' + AND is_terminal + ) AS is_clicked, + (product_selected_result = res.product_result_type AND event_action = 'annoyance') AS is_annoyed + FROM + `mozdata.firefox_desktop.urlbar_events` + CROSS JOIN + UNNEST(results) AS res + WHERE + submission_date = @submission_date +), +temp_session AS ( + SELECT + submission_date, + event_id, + product_result_type, + annoyance_signal_type, + is_terminal, + ANY_VALUE(normalized_channel) AS normalized_channel, + ANY_VALUE(normalized_country_code) AS normalized_country_code, + ANY_VALUE(firefox_suggest_enabled) AS firefox_suggest_enabled, + ANY_VALUE(sponsored_suggestions_enabled) AS sponsored_suggestions_enabled, + LOGICAL_OR(is_clicked) AS is_clicked, + LOGICAL_OR(is_annoyed) AS is_annoyed, + LOGICAL_OR(is_terminal = TRUE) AS is_impression, + FROM + temp_unnested + GROUP BY + submission_date, + event_id, + product_result_type, + annoyance_signal_type, + is_terminal +), +total_urlbar_sessions AS ( + SELECT + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled, + COUNT(DISTINCT event_id) AS urlbar_sessions + FROM + temp_session + WHERE + is_terminal = TRUE + GROUP BY + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled +), +daily_counts AS ( + SELECT + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled, + product_result_type, + annoyance_signal_type, + COUNTIF(is_impression) AS urlbar_impressions, + COUNTIF(is_clicked) AS urlbar_clicks, + COUNTIF(is_annoyed) AS urlbar_annoyances, + FROM + temp_session + GROUP BY + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled, + product_result_type, + annoyance_signal_type +) +SELECT + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled, + product_result_type, + annoyance_signal_type, + urlbar_impressions, + urlbar_clicks, + urlbar_annoyances, + urlbar_sessions +FROM + daily_counts +LEFT JOIN + total_urlbar_sessions + USING ( + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled + ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/schema.yaml 2024-05-30 19:57:43.000000000 +0000 @@ -0,0 +1,37 @@ +--- +fields: + - name: submission_date + type: DATE + mode: NULLABLE + - name: normalized_country_code + type: STRING + mode: NULLABLE + - name: normalized_channel + type: STRING + mode: NULLABLE + - name: firefox_suggest_enabled + type: BOOLEAN + mode: NULLABLE + - name: sponsored_suggestions_enabled + type: BOOLEAN + mode: NULLABLE + - name: product_result_type + type: STRING + mode: NULLABLE + description: Product type identifier for this result. + - name: annoyance_signal_type + type: STRING + mode: NULLABLE + description: Annoyance signal type for the selected result. + - name: urlbar_impressions + type: INTEGER + mode: NULLABLE + - name: urlbar_clicks + type: INTEGER + mode: NULLABLE + - name: urlbar_annoyances + type: INTEGER + mode: NULLABLE + - name: urlbar_sessions + type: INTEGER + mode: NULLABLE ```

Link to full diff

[dataops-ci-bot]

Integration report for "this table is to supersede urlbar_events_daily with clearer name, pattern for other derived tables, and inclusion of annoyance signal"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_urlbar.py /tmp/workspace/generated-sql/dags/bqetl_urlbar.py --- /tmp/workspace/main-generated-sql/dags/bqetl_urlbar.py 2024-05-31 20:32:32.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_urlbar.py 2024-05-31 20:33:45.000000000 +0000 @@ -112,6 +112,23 @@ depends_on_past=False, ) + firefox_desktop_derived__urlbar_events_engagement_by_product_type_aggregates__v1 = bigquery_etl_query( + task_id="firefox_desktop_derived__urlbar_events_engagement_by_product_type_aggregates__v1", + destination_table="urlbar_events_engagement_by_product_type_aggregates_v1", + dataset_id="firefox_desktop_derived", + project_id="moz-fx-data-shared-prod", + owner="tbrooks@mozilla.com", + email=[ + "akomar@mozilla.com", + "akommasani@mozilla.com", + "anicholson@mozilla.com", + "tbrooks@mozilla.com", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, + ) + firefox_desktop_urlbar_events__v2 = bigquery_etl_query( task_id="firefox_desktop_urlbar_events__v2", destination_table="urlbar_events_v2", @@ -154,6 +171,10 @@ firefox_desktop_urlbar_events__v2 ) + firefox_desktop_derived__urlbar_events_engagement_by_product_type_aggregates__v1.set_upstream( + firefox_desktop_urlbar_events__v2 + ) + firefox_desktop_urlbar_events__v2.set_upstream(wait_for_copy_deduplicate_all) telemetry_derived__urlbar_clients_daily__v1.set_upstream( Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop: urlbar_events_engagement_by_product_type_aggregates Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived: urlbar_events_engagement_by_product_type_aggregates_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/metadata.yaml 2024-05-31 20:30:42.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Urlbar Events Engagement By Product Type Aggregates +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.firefox_desktop_derived.urlbar_events_engagement_by_product_type_aggregates_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/urlbar_events_engagement_by_product_type_aggregates/view.sql 2024-05-31 20:28:57.000000000 +0000 @@ -0,0 +1,7 @@ +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.firefox_desktop.urlbar_events_engagement_by_product_type_aggregates` +AS +SELECT + * +FROM + `moz-fx-data-shared-prod.firefox_desktop_derived.urlbar_events_engagement_by_product_type_aggregates_v1` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/backfill.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/backfill.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/backfill.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/backfill.yaml 2024-05-31 20:28:57.000000000 +0000 @@ -0,0 +1,7 @@ +2024-05-31: + start_date: 2023-10-24 + end_date: 2024-05-31 + reason: Recreating a table and would like to have the historical data, which dates back to the start date of the backfill + watchers: + - tbrooks@mozilla.com + status: Initiate diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/metadata.yaml 2024-05-31 20:30:39.000000000 +0000 @@ -0,0 +1,32 @@ +friendly_name: Urlbar Events Engagement by Product Type Aggregates +description: |- + Each row in this table represents a daily summary statistic of impressions + and clicks per product result type per a host of meaningful dimensions. +owners: +- tbrooks@mozilla.com +- akommasani@mozilla.com +labels: + incremental: true + schedule: daily + dag: bqetl_urlbar + owner1: tbrooks + owner2: akommasani +scheduling: + dag_name: bqetl_urlbar +bigquery: + time_partitioning: + type: day + field: submission_date + require_partition_filter: true + expiration_days: null + range_partitioning: null + clustering: + fields: + - normalized_channel +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + query.sql: + - mozdata.firefox_desktop.urlbar_events diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/query.sql 2024-05-31 20:28:57.000000000 +0000 @@ -0,0 +1,113 @@ +WITH temp_unnested AS ( + SELECT + submission_date, + event_id, + event_action, + res.product_result_type AS product_result_type, + annoyance_signal_type, + normalized_channel, + normalized_country_code, + pref_fx_suggestions AS firefox_suggest_enabled, + pref_sponsored_suggestions AS sponsored_suggestions_enabled, + is_terminal, + ( + product_selected_result = res.product_result_type + AND event_action = 'engaged' + AND is_terminal + ) AS is_clicked, + (product_selected_result = res.product_result_type AND event_action = 'annoyance') AS is_annoyed + FROM + `mozdata.firefox_desktop.urlbar_events` + CROSS JOIN + UNNEST(results) AS res + WHERE + submission_date = @submission_date +), +temp_session AS ( + SELECT + submission_date, + event_id, + product_result_type, + annoyance_signal_type, + is_terminal, + ANY_VALUE(normalized_channel) AS normalized_channel, + ANY_VALUE(normalized_country_code) AS normalized_country_code, + ANY_VALUE(firefox_suggest_enabled) AS firefox_suggest_enabled, + ANY_VALUE(sponsored_suggestions_enabled) AS sponsored_suggestions_enabled, + LOGICAL_OR(is_clicked) AS is_clicked, + LOGICAL_OR(is_annoyed) AS is_annoyed, + LOGICAL_OR(is_terminal = TRUE) AS is_impression, + FROM + temp_unnested + GROUP BY + submission_date, + event_id, + product_result_type, + annoyance_signal_type, + is_terminal +), +total_urlbar_sessions AS ( + SELECT + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled, + COUNT(DISTINCT event_id) AS urlbar_sessions + FROM + temp_session + WHERE + is_terminal = TRUE + GROUP BY + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled +), +daily_counts AS ( + SELECT + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled, + product_result_type, + annoyance_signal_type, + COUNTIF(is_impression) AS urlbar_impressions, + COUNTIF(is_clicked) AS urlbar_clicks, + COUNTIF(is_annoyed) AS urlbar_annoyances, + FROM + temp_session + GROUP BY + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled, + product_result_type, + annoyance_signal_type +) +SELECT + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled, + product_result_type, + annoyance_signal_type, + urlbar_impressions, + urlbar_clicks, + urlbar_annoyances, + urlbar_sessions +FROM + daily_counts +LEFT JOIN + total_urlbar_sessions + USING ( + submission_date, + normalized_country_code, + normalized_channel, + firefox_suggest_enabled, + sponsored_suggestions_enabled + ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/urlbar_events_engagement_by_product_type_aggregates_v1/schema.yaml 2024-05-31 20:28:57.000000000 +0000 @@ -0,0 +1,37 @@ +--- +fields: + - name: submission_date + type: DATE + mode: NULLABLE + - name: normalized_country_code + type: STRING + mode: NULLABLE + - name: normalized_channel + type: STRING + mode: NULLABLE + - name: firefox_suggest_enabled + type: BOOLEAN + mode: NULLABLE + - name: sponsored_suggestions_enabled + type: BOOLEAN + mode: NULLABLE + - name: product_result_type + type: STRING + mode: NULLABLE + description: Product type identifier for this result. + - name: annoyance_signal_type + type: STRING + mode: NULLABLE + description: Annoyance signal type for the selected result. + - name: urlbar_impressions + type: INTEGER + mode: NULLABLE + - name: urlbar_clicks + type: INTEGER + mode: NULLABLE + - name: urlbar_annoyances + type: INTEGER + mode: NULLABLE + - name: urlbar_sessions + type: INTEGER + mode: NULLABLE ```

Link to full diff