[RS-1282] Adding dependence into Glean's newtab ping for dismissals and tile disables

[sergiosonline]

We can consider an effective cutover date for Pingcentre -> Glean of Feb 20, 2024. Unsure what's the most robust way of baking this here. An idea:

• Merge this as-is and only backfill up to Feb 20, 2024 👍

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 "Adding dependence into Glean's newtab"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-26 18:59:59.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-26 19:00:17.000000000 +0000 @@ -1,6 +1,8 @@ friendly_name: Sponsored Tiles Clients Daily description: |- - daily client-level aggregates of Sponsored Tiles-related fields + daily client-level aggregates of Sponsored Tiles-related fields. + Note that for desktop tile dismissals and disables, we deprecated PingCentre's Activity Stream in Fx123, + and now rely on Glean's newtab ping as of Fx120. There's an effective cutover date of Feb 20, 2024. owners: - skahmann@mozilla.com - cmorales@mozilla.com diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-26 18:58:22.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-26 18:58:16.000000000 +0000 @@ -43,7 +43,8 @@ 1, 2 ), - ------ DESKTOP Dismissals and Disables +------ DESKTOP Dismissals and Disables +--- Note PingCentre was deprecated as of Fx123 (Feb 20, 2024) desktop_activity_stream_events AS ( SELECT client_id, @@ -66,6 +67,43 @@ 1, 2 ), +--- Current telemetry for dismissals and deactivations comes in Glean's newtab ping as of Fx120 (Nov 21, 2023) +desktop_newtab_events AS ( + SELECT + n.client_info.client_id AS client_id, + n.metrics.uuid.legacy_telemetry_client_id AS legacy_telemetry_client_id, + DATE(submission_timestamp) AS submission_date, + COUNTIF( + e.category = 'topsites' + AND e.name = 'dismiss' + AND `mozfun.map.get_key`(e.extra, 'is_sponsored') = 'true' + ) AS sponsored_tiles_dismissal_count, + COUNTIF( + e.name = 'pref_changed' + AND `mozfun.map.get_key`(e.extra, 'pref_name') = 'browser.newtabpage.activity-stream.showSponsoredTopSites' + AND `mozfun.map.get_key`(e.extra, 'new_value') = 'false' + ) AS sponsored_tiles_disable_count + FROM `moz-fx-data-shared-prod.firefox_desktop.newtab` n, + UNNEST(events) e + WHERE + DATE(submission_timestamp) = @submission_date + GROUP BY + 1, + 2, + 3 +), +desktop_joint_events AS ( + SELECT + COALESCE(n.submission_date, a.submission_date) AS submission_date, + COALESCE(n.legacy_telemetry_client_id, a.client_id) AS client_id, + COALESCE(n.sponsored_tiles_dismissal_count, a.sponsored_tiles_dismissal_count) AS sponsored_tiles_dismissal_count, + COALESCE(n.sponsored_tiles_disable_count, a.sponsored_tiles_disable_count) AS sponsored_tiles_disable_count + FROM desktop_newtab_events n + FULL OUTER JOIN desktop_activity_stream_events a + ON + n.legacy_telemetry_client_id = a.client_id + AND n.submission_date = a.submission_date +), ------ iOS SPONSORED TILES ios_data AS ( SELECT @@ -192,7 +230,7 @@ impressions_main USING (client_id, submission_date) LEFT JOIN - desktop_activity_stream_events + desktop_joint_events USING (client_id, submission_date) -- add experiments data LEFT JOIN ```

Link to full diff

[dataops-ci-bot]

Integration report for "ran bqetl formatter"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-26 20:43:12.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-26 20:43:32.000000000 +0000 @@ -1,6 +1,8 @@ friendly_name: Sponsored Tiles Clients Daily description: |- - daily client-level aggregates of Sponsored Tiles-related fields + daily client-level aggregates of Sponsored Tiles-related fields. + Note that for desktop tile dismissals and disables, we deprecated PingCentre's Activity Stream in Fx123, + and now rely on Glean's newtab ping as of Fx120. There's an effective cutover version of Fx123. owners: - skahmann@mozilla.com - cmorales@mozilla.com diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-26 20:41:28.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-26 20:41:15.000000000 +0000 @@ -43,7 +43,8 @@ 1, 2 ), - ------ DESKTOP Dismissals and Disables +------ DESKTOP Dismissals and Disables +--- Note PingCentre was deprecated as of Fx123 (Feb 20, 2024) desktop_activity_stream_events AS ( SELECT client_id, @@ -62,6 +63,66 @@ `moz-fx-data-shared-prod.activity_stream.events` WHERE DATE(submission_timestamp) = @submission_date + AND CAST(metadata.user_agent.version AS INT64) < 123 + GROUP BY + 1, + 2 +), +--- Current telemetry for dismissals and deactivations comes in Glean's newtab ping as of Fx120 (Nov 21, 2023) +desktop_newtab_events AS ( + SELECT + n.client_info.client_id AS client_id, + n.metrics.uuid.legacy_telemetry_client_id AS legacy_telemetry_client_id, + DATE(submission_timestamp) AS submission_date, + COUNTIF( + e.category = 'topsites' + AND e.name = 'dismiss' + AND `mozfun.map.get_key`(e.extra, 'is_sponsored') = 'true' + ) AS sponsored_tiles_dismissal_count, + COUNTIF( + e.name = 'pref_changed' + AND `mozfun.map.get_key`( + e.extra, + 'pref_name' + ) = 'browser.newtabpage.activity-stream.showSponsoredTopSites' + AND `mozfun.map.get_key`(e.extra, 'new_value') = 'false' + ) AS sponsored_tiles_disable_count + FROM + `moz-fx-data-shared-prod.firefox_desktop.newtab` n, + UNNEST(events) e + WHERE + DATE(submission_timestamp) = @submission_date + AND `mozfun`.norm.browser_version_info(client_info.app_display_version).major_version >= 123 + GROUP BY + 1, + 2, + 3 +), +desktop_joint_events AS ( + SELECT + n.submission_date, + n.legacy_telemetry_client_id AS client_id, + n.sponsored_tiles_dismissal_count, + n.sponsored_tiles_disable_count + FROM + desktop_newtab_events n + UNION DISTINCT + SELECT + a.submission_date, + a.client_id, + a.sponsored_tiles_dismissal_count, + a.sponsored_tiles_disable_count + FROM + desktop_activity_stream_events a +), +desktop_agg_events AS ( + SELECT + submission_date, + client_id, + SUM(sponsored_tiles_dismissal_count) AS sponsored_tiles_dismissal_count, + SUM(sponsored_tiles_disable_count) AS sponsored_tiles_disable_count + FROM + desktop_joint_events GROUP BY 1, 2 @@ -192,7 +253,7 @@ impressions_main USING (client_id, submission_date) LEFT JOIN - desktop_activity_stream_events + desktop_agg_events USING (client_id, submission_date) -- add experiments data LEFT JOIN ```

Link to full diff

[sergiosonline]

Thanks, @curtismorales . Just merged all of your suggestions.

[dataops-ci-bot]

Integration report for "Merge branch 'main' into updating_sponsored_tiles_clients_daily"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-27 20:52:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-27 20:53:23.000000000 +0000 @@ -1,6 +1,8 @@ friendly_name: Sponsored Tiles Clients Daily description: |- - daily client-level aggregates of Sponsored Tiles-related fields + daily client-level aggregates of Sponsored Tiles-related fields. + Note that for desktop tile dismissals and disables, we deprecated PingCentre's Activity Stream in Fx123, + and now rely on Glean's newtab ping as of Fx120. There's an effective cutover version of Fx123. owners: - skahmann@mozilla.com - cmorales@mozilla.com diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-27 20:51:16.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-27 20:51:26.000000000 +0000 @@ -43,7 +43,8 @@ 1, 2 ), - ------ DESKTOP Dismissals and Disables +------ DESKTOP Dismissals and Disables +--- Note PingCentre was deprecated as of Fx123 (Feb 20, 2024) desktop_activity_stream_events AS ( SELECT client_id, @@ -62,9 +63,69 @@ `moz-fx-data-shared-prod.activity_stream.events` WHERE DATE(submission_timestamp) = @submission_date + AND CAST(metadata.user_agent.version AS INT64) < 123 GROUP BY - 1, - 2 + client_id, + submission_date +), +--- Current telemetry for dismissals and deactivations comes in Glean's newtab ping as of Fx120 (Nov 21, 2023) +desktop_newtab_events AS ( + SELECT + n.client_info.client_id AS client_id, + n.metrics.uuid.legacy_telemetry_client_id AS legacy_telemetry_client_id, + DATE(submission_timestamp) AS submission_date, + COUNTIF( + e.category = 'topsites' + AND e.name = 'dismiss' + AND `mozfun.map.get_key`(e.extra, 'is_sponsored') = 'true' + ) AS sponsored_tiles_dismissal_count, + COUNTIF( + e.name = 'pref_changed' + AND `mozfun.map.get_key`( + e.extra, + 'pref_name' + ) = 'browser.newtabpage.activity-stream.showSponsoredTopSites' + AND `mozfun.map.get_key`(e.extra, 'new_value') = 'false' + ) AS sponsored_tiles_disable_count + FROM + `moz-fx-data-shared-prod.firefox_desktop.newtab` n, + UNNEST(events) e + WHERE + DATE(submission_timestamp) = @submission_date + AND `mozfun`.norm.browser_version_info(client_info.app_display_version).major_version >= 123 + GROUP BY + client_id, + legacy_telemetry_client_id, + submission_date +), +desktop_joint_events AS ( + SELECT + n.submission_date, + n.legacy_telemetry_client_id AS client_id, + n.sponsored_tiles_dismissal_count, + n.sponsored_tiles_disable_count + FROM + desktop_newtab_events n + UNION ALL + SELECT + a.submission_date, + a.client_id, + a.sponsored_tiles_dismissal_count, + a.sponsored_tiles_disable_count + FROM + desktop_activity_stream_events a +), +desktop_agg_events AS ( + SELECT + submission_date, + client_id, + SUM(sponsored_tiles_dismissal_count) AS sponsored_tiles_dismissal_count, + SUM(sponsored_tiles_disable_count) AS sponsored_tiles_disable_count + FROM + desktop_joint_events + GROUP BY + submission_date, + client_id ), ------ iOS SPONSORED TILES ios_data AS ( @@ -192,7 +253,7 @@ impressions_main USING (client_id, submission_date) LEFT JOIN - desktop_activity_stream_events + desktop_agg_events USING (client_id, submission_date) -- add experiments data LEFT JOIN ```

Link to full diff

[dataops-ci-bot]

Integration report for "Merge branch 'main' into updating_sponsored_tiles_clients_daily"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-28 15:23:56.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-28 15:23:58.000000000 +0000 @@ -1,6 +1,8 @@ friendly_name: Sponsored Tiles Clients Daily description: |- - daily client-level aggregates of Sponsored Tiles-related fields + daily client-level aggregates of Sponsored Tiles-related fields. + Note that for desktop tile dismissals and disables, we deprecated PingCentre's Activity Stream in Fx123, + and now rely on Glean's newtab ping as of Fx120. There's an effective cutover version of Fx123. owners: - skahmann@mozilla.com - cmorales@mozilla.com diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-28 15:22:03.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-28 15:22:01.000000000 +0000 @@ -43,7 +43,8 @@ 1, 2 ), - ------ DESKTOP Dismissals and Disables +------ DESKTOP Dismissals and Disables +--- Note PingCentre was deprecated as of Fx123 (Feb 20, 2024) desktop_activity_stream_events AS ( SELECT client_id, @@ -62,9 +63,69 @@ `moz-fx-data-shared-prod.activity_stream.events` WHERE DATE(submission_timestamp) = @submission_date + AND CAST(metadata.user_agent.version AS INT64) < 123 GROUP BY - 1, - 2 + client_id, + submission_date +), +--- Current telemetry for dismissals and deactivations comes in Glean's newtab ping as of Fx120 (Nov 21, 2023) +desktop_newtab_events AS ( + SELECT + n.client_info.client_id AS client_id, + n.metrics.uuid.legacy_telemetry_client_id AS legacy_telemetry_client_id, + DATE(submission_timestamp) AS submission_date, + COUNTIF( + e.category = 'topsites' + AND e.name = 'dismiss' + AND `mozfun.map.get_key`(e.extra, 'is_sponsored') = 'true' + ) AS sponsored_tiles_dismissal_count, + COUNTIF( + e.name = 'pref_changed' + AND `mozfun.map.get_key`( + e.extra, + 'pref_name' + ) = 'browser.newtabpage.activity-stream.showSponsoredTopSites' + AND `mozfun.map.get_key`(e.extra, 'new_value') = 'false' + ) AS sponsored_tiles_disable_count + FROM + `moz-fx-data-shared-prod.firefox_desktop.newtab` n, + UNNEST(events) e + WHERE + DATE(submission_timestamp) = @submission_date + AND `mozfun`.norm.browser_version_info(client_info.app_display_version).major_version >= 123 + GROUP BY + client_id, + legacy_telemetry_client_id, + submission_date +), +desktop_joint_events AS ( + SELECT + n.submission_date, + n.legacy_telemetry_client_id AS client_id, + n.sponsored_tiles_dismissal_count, + n.sponsored_tiles_disable_count + FROM + desktop_newtab_events n + UNION ALL + SELECT + a.submission_date, + a.client_id, + a.sponsored_tiles_dismissal_count, + a.sponsored_tiles_disable_count + FROM + desktop_activity_stream_events a +), +desktop_agg_events AS ( + SELECT + submission_date, + client_id, + SUM(sponsored_tiles_dismissal_count) AS sponsored_tiles_dismissal_count, + SUM(sponsored_tiles_disable_count) AS sponsored_tiles_disable_count + FROM + desktop_joint_events + GROUP BY + submission_date, + client_id ), ------ iOS SPONSORED TILES ios_data AS ( @@ -192,7 +253,7 @@ impressions_main USING (client_id, submission_date) LEFT JOIN - desktop_activity_stream_events + desktop_agg_events USING (client_id, submission_date) -- add experiments data LEFT JOIN ```

Link to full diff

[dataops-ci-bot]

Integration report for "Merge branch 'main' into updating_sponsored_tiles_clients_daily"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-28 16:14:02.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-28 16:14:18.000000000 +0000 @@ -1,6 +1,8 @@ friendly_name: Sponsored Tiles Clients Daily description: |- - daily client-level aggregates of Sponsored Tiles-related fields + daily client-level aggregates of Sponsored Tiles-related fields. + Note that for desktop tile dismissals and disables, we deprecated PingCentre's Activity Stream in Fx123, + and now rely on Glean's newtab ping as of Fx120. There's an effective cutover version of Fx123. owners: - skahmann@mozilla.com - cmorales@mozilla.com diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-28 16:12:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-28 16:12:23.000000000 +0000 @@ -43,7 +43,8 @@ 1, 2 ), - ------ DESKTOP Dismissals and Disables +------ DESKTOP Dismissals and Disables +--- Note PingCentre was deprecated as of Fx123 (Feb 20, 2024) desktop_activity_stream_events AS ( SELECT client_id, @@ -62,9 +63,69 @@ `moz-fx-data-shared-prod.activity_stream.events` WHERE DATE(submission_timestamp) = @submission_date + AND CAST(metadata.user_agent.version AS INT64) < 123 GROUP BY - 1, - 2 + client_id, + submission_date +), +--- Current telemetry for dismissals and deactivations comes in Glean's newtab ping as of Fx120 (Nov 21, 2023) +desktop_newtab_events AS ( + SELECT + n.client_info.client_id AS client_id, + n.metrics.uuid.legacy_telemetry_client_id AS legacy_telemetry_client_id, + DATE(submission_timestamp) AS submission_date, + COUNTIF( + e.category = 'topsites' + AND e.name = 'dismiss' + AND `mozfun.map.get_key`(e.extra, 'is_sponsored') = 'true' + ) AS sponsored_tiles_dismissal_count, + COUNTIF( + e.name = 'pref_changed' + AND `mozfun.map.get_key`( + e.extra, + 'pref_name' + ) = 'browser.newtabpage.activity-stream.showSponsoredTopSites' + AND `mozfun.map.get_key`(e.extra, 'new_value') = 'false' + ) AS sponsored_tiles_disable_count + FROM + `moz-fx-data-shared-prod.firefox_desktop.newtab` n, + UNNEST(events) e + WHERE + DATE(submission_timestamp) = @submission_date + AND `mozfun`.norm.browser_version_info(client_info.app_display_version).major_version >= 123 + GROUP BY + client_id, + legacy_telemetry_client_id, + submission_date +), +desktop_joint_events AS ( + SELECT + n.submission_date, + n.legacy_telemetry_client_id AS client_id, + n.sponsored_tiles_dismissal_count, + n.sponsored_tiles_disable_count + FROM + desktop_newtab_events n + UNION ALL + SELECT + a.submission_date, + a.client_id, + a.sponsored_tiles_dismissal_count, + a.sponsored_tiles_disable_count + FROM + desktop_activity_stream_events a +), +desktop_agg_events AS ( + SELECT + submission_date, + client_id, + SUM(sponsored_tiles_dismissal_count) AS sponsored_tiles_dismissal_count, + SUM(sponsored_tiles_disable_count) AS sponsored_tiles_disable_count + FROM + desktop_joint_events + GROUP BY + submission_date, + client_id ), ------ iOS SPONSORED TILES ios_data AS ( @@ -192,7 +253,7 @@ impressions_main USING (client_id, submission_date) LEFT JOIN - desktop_activity_stream_events + desktop_agg_events USING (client_id, submission_date) -- add experiments data LEFT JOIN ```

Link to full diff

[dataops-ci-bot]

Integration report for "Merge branch 'main' into updating_sponsored_tiles_clients_daily"

sql.diff

Click to expand!

```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-28 16:39:07.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/metadata.yaml 2024-06-28 16:39:17.000000000 +0000 @@ -1,6 +1,8 @@ friendly_name: Sponsored Tiles Clients Daily description: |- - daily client-level aggregates of Sponsored Tiles-related fields + daily client-level aggregates of Sponsored Tiles-related fields. + Note that for desktop tile dismissals and disables, we deprecated PingCentre's Activity Stream in Fx123, + and now rely on Glean's newtab ping as of Fx120. There's an effective cutover version of Fx123. owners: - skahmann@mozilla.com - cmorales@mozilla.com diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-28 16:37:17.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/sponsored_tiles_clients_daily_v1/query.sql 2024-06-28 16:37:21.000000000 +0000 @@ -43,7 +43,8 @@ 1, 2 ), - ------ DESKTOP Dismissals and Disables +------ DESKTOP Dismissals and Disables +--- Note PingCentre was deprecated as of Fx123 (Feb 20, 2024) desktop_activity_stream_events AS ( SELECT client_id, @@ -62,9 +63,69 @@ `moz-fx-data-shared-prod.activity_stream.events` WHERE DATE(submission_timestamp) = @submission_date + AND CAST(metadata.user_agent.version AS INT64) < 123 GROUP BY - 1, - 2 + client_id, + submission_date +), +--- Current telemetry for dismissals and deactivations comes in Glean's newtab ping as of Fx120 (Nov 21, 2023) +desktop_newtab_events AS ( + SELECT + n.client_info.client_id AS client_id, + n.metrics.uuid.legacy_telemetry_client_id AS legacy_telemetry_client_id, + DATE(submission_timestamp) AS submission_date, + COUNTIF( + e.category = 'topsites' + AND e.name = 'dismiss' + AND `mozfun.map.get_key`(e.extra, 'is_sponsored') = 'true' + ) AS sponsored_tiles_dismissal_count, + COUNTIF( + e.name = 'pref_changed' + AND `mozfun.map.get_key`( + e.extra, + 'pref_name' + ) = 'browser.newtabpage.activity-stream.showSponsoredTopSites' + AND `mozfun.map.get_key`(e.extra, 'new_value') = 'false' + ) AS sponsored_tiles_disable_count + FROM + `moz-fx-data-shared-prod.firefox_desktop.newtab` n, + UNNEST(events) e + WHERE + DATE(submission_timestamp) = @submission_date + AND `mozfun`.norm.browser_version_info(client_info.app_display_version).major_version >= 123 + GROUP BY + client_id, + legacy_telemetry_client_id, + submission_date +), +desktop_joint_events AS ( + SELECT + n.submission_date, + n.legacy_telemetry_client_id AS client_id, + n.sponsored_tiles_dismissal_count, + n.sponsored_tiles_disable_count + FROM + desktop_newtab_events n + UNION ALL + SELECT + a.submission_date, + a.client_id, + a.sponsored_tiles_dismissal_count, + a.sponsored_tiles_disable_count + FROM + desktop_activity_stream_events a +), +desktop_agg_events AS ( + SELECT + submission_date, + client_id, + SUM(sponsored_tiles_dismissal_count) AS sponsored_tiles_dismissal_count, + SUM(sponsored_tiles_disable_count) AS sponsored_tiles_disable_count + FROM + desktop_joint_events + GROUP BY + submission_date, + client_id ), ------ iOS SPONSORED TILES ios_data AS ( @@ -192,7 +253,7 @@ impressions_main USING (client_id, submission_date) LEFT JOIN - desktop_activity_stream_events + desktop_agg_events USING (client_id, submission_date) -- add experiments data LEFT JOIN ```

Link to full diff