dataops-ci-bot
commented
2 months ago Integration report for "Update registration_funnels_by_service.toml"
sql.diff
Click to expand!
```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_generated_funnels.py /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py --- /tmp/workspace/main-generated-sql/dags/bqetl_generated_funnels.py 2024-07-30 06:01:28.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py 2024-07-30 06:19:59.000000000 +0000 @@ -75,6 +75,18 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) + wait_for_accounts_backend_derived__events_stream__v1 = ExternalTaskSensor( + task_id="wait_for_accounts_backend_derived__events_stream__v1", + external_dag_id="bqetl_glean_usage", + external_task_id="accounts_backend.accounts_backend_derived__events_stream__v1", + execution_delta=datetime.timedelta(seconds=10800), + check_existence=True, + mode="reschedule", + allowed_states=ALLOWED_STATES, + failed_states=FAILED_STATES, + pool="DATA_ENG_EXTERNALTASKSENSOR", + ) + wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 = ( ExternalTaskSensor( task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", @@ -137,21 +149,6 @@ pool="DATA_ENG_EXTERNALTASKSENSOR", ) - accounts_frontend_derived__email_first_reg_login_funnels__v1 = bigquery_etl_query( - task_id="accounts_frontend_derived__email_first_reg_login_funnels__v1", - destination_table="email_first_reg_login_funnels_v1", - dataset_id="accounts_frontend_derived", - project_id="moz-fx-data-shared-prod", - owner="ksiegler@mozilla.org", - email=[ - "ascholtz@mozilla.com", - "ksiegler@mozilla.org", - "telemetry-alerts@mozilla.com", - ], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - accounts_frontend_derived__email_first_reg_login_funnels_by_service__v1 = bigquery_etl_query( task_id="accounts_frontend_derived__email_first_reg_login_funnels_by_service__v1", destination_table="email_first_reg_login_funnels_by_service_v1", @@ -291,8 +288,19 @@ ) ) - accounts_frontend_derived__email_first_reg_login_funnels__v1.set_upstream( - wait_for_copy_deduplicate_all + monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1 = bigquery_etl_query( + task_id="monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1", + destination_table="monitor_dashboard_user_journey_funnels_v1", + dataset_id="monitor_frontend_derived", + project_id="moz-fx-data-shared-prod", + owner="ksiegler@mozilla.org", + email=[ + "ascholtz@mozilla.com", + "ksiegler@mozilla.org", + "telemetry-alerts@mozilla.com", + ], + date_partition_parameter="submission_date", + depends_on_past=False, ) accounts_frontend_derived__email_first_reg_login_funnels_by_service__v1.set_upstream( @@ -304,7 +312,11 @@ ) accounts_frontend_derived__login_funnels_by_service__v1.set_upstream( - wait_for_copy_deduplicate_all + wait_for_accounts_backend_derived__events_stream__v1 + ) + + accounts_frontend_derived__login_funnels_by_service__v1.set_upstream( + wait_for_accounts_frontend_derived__events_stream__v1 ) accounts_frontend_derived__monitor_mozilla_accounts_funnels__v1.set_upstream( @@ -320,7 +332,11 @@ ) accounts_frontend_derived__registration_funnels_by_service__v1.set_upstream( - wait_for_copy_deduplicate_all + wait_for_accounts_backend_derived__events_stream__v1 + ) + + accounts_frontend_derived__registration_funnels_by_service__v1.set_upstream( + wait_for_accounts_frontend_derived__events_stream__v1 ) fenix_derived__android_onboarding__v1.set_upstream( @@ -344,3 +360,7 @@ firefox_accounts_derived__registration_funnels_legacy_events__v1.set_upstream( wait_for_firefox_accounts_derived__fxa_stdout_events__v1 ) + + monitor_frontend_derived__monitor_dashboard_user_journey_funnels__v1.set_upstream( + wait_for_copy_deduplicate_all + ) Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived: email_first_reg_login_funnels_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/event_monitoring_live_v1/materialized_view.sql 2024-07-30 05:55:45.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/event_monitoring_live_v1/materialized_view.sql 2024-07-30 05:58:29.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-07-29" + DATE(submission_timestamp) >= "2024-07-30" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 2024-07-30 05:55:45.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_cirrus_derived/event_monitoring_live_v1/materialized_view.sql 2024-07-30 05:58:29.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-07-29" + DATE(submission_timestamp) >= "2024-07-30" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/email_first_reg_login_funnels_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/email_first_reg_login_funnels_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/email_first_reg_login_funnels_v1/metadata.yaml 2024-07-30 05:55:45.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/email_first_reg_login_funnels_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,27 +0,0 @@ -friendly_name: Email First Reg Login Funnels -description: |- - Please provide a description for the query -owners: -- ksiegler@mozilla.org -labels: - incremental: true - dag: bqetl_generated_funnels - owner1: ksiegler -scheduling: - dag_name: bqetl_generated_funnels -bigquery: - time_partitioning: - type: day - field: submission_date - require_partition_filter: false - expiration_days: null - range_partitioning: null - clustering: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:mozilla-confidential -references: - query.sql: - - mozdata.accounts_backend.accounts_events - - mozdata.accounts_frontend.accounts_events diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/email_first_reg_login_funnels_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/email_first_reg_login_funnels_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/email_first_reg_login_funnels_v1/query.sql 2024-07-30 05:55:45.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/email_first_reg_login_funnels_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,194 +0,0 @@ --- extract the relevant fields for each funnel step and segment if necessary -WITH email_first_login_success_email_first_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.accounts_events - WHERE - DATE(submission_timestamp) = @submission_date - AND metrics.string.event_name = 'email_first_view' -), -email_first_login_success_login_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.accounts_events - INNER JOIN - email_first_login_success_email_first_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - DATE(submission_timestamp) = @submission_date - AND metrics.string.event_name = 'login_view' -), -email_first_login_success_login_success AS ( - SELECT - metrics.string.session_flow_id AS join_key, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.accounts_events - INNER JOIN - email_first_login_success_login_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - DATE(submission_timestamp) = @submission_date - AND metrics.string.event_name = 'login_submit_success' -), -email_first_registration_success_email_first_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.accounts_events - WHERE - DATE(submission_timestamp) = @submission_date - AND metrics.string.event_name = 'email_first_view' -), -email_first_registration_success_reg_view AS ( - SELECT - metrics.string.session_flow_id AS join_key, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.accounts_events - INNER JOIN - email_first_registration_success_email_first_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - DATE(submission_timestamp) = @submission_date - AND metrics.string.event_name = 'reg_view' -), -email_first_registration_success_reg_success AS ( - SELECT - metrics.string.session_flow_id AS join_key, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_backend.accounts_events - INNER JOIN - email_first_registration_success_reg_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - DATE(submission_timestamp) = @submission_date - AND metrics.string.event_name = 'reg_complete' -), --- aggregate each funnel step value -email_first_login_success_email_first_view_aggregated AS ( - SELECT - submission_date, - "email_first_login_success" AS funnel, - COUNT(DISTINCT column) AS aggregated - FROM - email_first_login_success_email_first_view - GROUP BY - submission_date, - funnel -), -email_first_login_success_login_view_aggregated AS ( - SELECT - submission_date, - "email_first_login_success" AS funnel, - COUNT(DISTINCT column) AS aggregated - FROM - email_first_login_success_login_view - GROUP BY - submission_date, - funnel -), -email_first_login_success_login_success_aggregated AS ( - SELECT - submission_date, - "email_first_login_success" AS funnel, - COUNT(DISTINCT column) AS aggregated - FROM - email_first_login_success_login_success - GROUP BY - submission_date, - funnel -), -email_first_registration_success_email_first_view_aggregated AS ( - SELECT - submission_date, - "email_first_registration_success" AS funnel, - COUNT(DISTINCT column) AS aggregated - FROM - email_first_registration_success_email_first_view - GROUP BY - submission_date, - funnel -), -email_first_registration_success_reg_view_aggregated AS ( - SELECT - submission_date, - "email_first_registration_success" AS funnel, - COUNT(DISTINCT column) AS aggregated - FROM - email_first_registration_success_reg_view - GROUP BY - submission_date, - funnel -), -email_first_registration_success_reg_success_aggregated AS ( - SELECT - submission_date, - "email_first_registration_success" AS funnel, - COUNT(DISTINCT column) AS aggregated - FROM - email_first_registration_success_reg_success - GROUP BY - submission_date, - funnel -), --- merge all funnels so results can be written into one table -merged_funnels AS ( - SELECT - submission_date, - funnel, - COALESCE( - email_first_login_success_email_first_view_aggregated.aggregated, - email_first_registration_success_email_first_view_aggregated.aggregated - ) AS email_first_view, - COALESCE(email_first_login_success_login_view_aggregated.aggregated, NULL) AS login_view, - COALESCE(email_first_login_success_login_success_aggregated.aggregated, NULL) AS login_success, - COALESCE(NULL, email_first_registration_success_reg_view_aggregated.aggregated) AS reg_view, - COALESCE( - NULL, - email_first_registration_success_reg_success_aggregated.aggregated - ) AS reg_success, - FROM - email_first_login_success_email_first_view_aggregated - FULL OUTER JOIN - email_first_login_success_login_view_aggregated - USING (submission_date, funnel) - FULL OUTER JOIN - email_first_login_success_login_success_aggregated - USING (submission_date, funnel) - FULL OUTER JOIN - email_first_registration_success_email_first_view_aggregated - USING (submission_date, funnel) - FULL OUTER JOIN - email_first_registration_success_reg_view_aggregated - USING (submission_date, funnel) - FULL OUTER JOIN - email_first_registration_success_reg_success_aggregated - USING (submission_date, funnel) -) -SELECT - * -FROM - merged_funnels diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/event_monitoring_live_v1/materialized_view.sql 2024-07-30 05:55:45.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/event_monitoring_live_v1/materialized_view.sql 2024-07-30 05:58:29.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-07-29" + DATE(submission_timestamp) >= "2024-07-30" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/metadata.yaml 2024-07-30 05:55:45.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/metadata.yaml 2024-07-30 06:15:11.000000000 +0000 @@ -23,5 +23,5 @@ - workgroup:mozilla-confidential references: query.sql: - - mozdata.accounts_backend.accounts_events - - mozdata.accounts_frontend.accounts_events + - mozdata.accounts_backend.events_stream + - mozdata.accounts_frontend.events_stream diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/query.sql 2024-07-30 05:55:45.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/query.sql 2024-07-30 06:04:05.000000000 +0000 @@ -1,5 +1,5 @@ -- extract the relevant fields for each funnel step and segment if necessary -WITH login_overall_success_by_service_login_view AS ( +WITH login_complete_by_service_login_view AS ( SELECT metrics.string.session_flow_id AS join_key, IF( @@ -14,30 +14,30 @@ ) ) AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream WHERE {% if is_init() %} DATE(submission_timestamp) >= DATE("2024-01-01") {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_view' + AND event = 'login.view' AND metrics.string.session_flow_id != '' ), -login_overall_success_by_service_login_complete AS ( +login_complete_by_service_login_complete AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_backend.accounts_events + mozdata.accounts_backend.events_stream INNER JOIN - login_overall_success_by_service_login_view AS prev + login_complete_by_service_login_view AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -46,10 +46,10 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_complete' + AND event = 'login.complete' AND metrics.string.session_flow_id != '' ), -login_submit_overall_success_by_service_login_view AS ( +login_submit_complete_by_service_login_view AS ( SELECT metrics.string.session_flow_id AS join_key, IF( @@ -64,30 +64,30 @@ ) ) AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream WHERE {% if is_init() %} DATE(submission_timestamp) >= DATE("2024-01-01") {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_view' + AND event = 'login.view' AND metrics.string.session_flow_id != '' ), -login_submit_overall_success_by_service_login_submit AS ( +login_submit_complete_by_service_login_submit AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream INNER JOIN - login_submit_overall_success_by_service_login_view AS prev + login_submit_complete_by_service_login_view AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -96,20 +96,20 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_submit' + AND event = 'login.submit' AND metrics.string.session_flow_id != '' ), -login_submit_overall_success_by_service_login_success AS ( +login_submit_complete_by_service_login_complete AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_backend.accounts_events + mozdata.accounts_backend.events_stream INNER JOIN - login_submit_overall_success_by_service_login_submit AS prev + login_submit_complete_by_service_login_submit AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -118,32 +118,10 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_success' + AND event = 'login.complete' AND metrics.string.session_flow_id != '' ), -login_submit_overall_success_by_service_login_complete AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_backend.accounts_events - INNER JOIN - login_submit_overall_success_by_service_login_success AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-01-01") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND metrics.string.event_name = 'login_complete' - AND metrics.string.session_flow_id != '' -), -login_success_with_email_by_service_login_view AS ( +login_email_confirmation_complete_by_service_login_view AS ( SELECT metrics.string.session_flow_id AS join_key, IF( @@ -158,74 +136,30 @@ ) ) AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.accounts_events - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-01-01") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND metrics.string.event_name = 'login_view' - AND metrics.string.session_flow_id != '' -), -login_success_with_email_by_service_login_submit AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.accounts_events - INNER JOIN - login_success_with_email_by_service_login_view AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-01-01") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND metrics.string.event_name = 'login_submit' - AND metrics.string.session_flow_id != '' -), -login_success_with_email_by_service_login_success AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_backend.accounts_events - INNER JOIN - login_success_with_email_by_service_login_submit AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id + mozdata.accounts_frontend.events_stream WHERE {% if is_init() %} DATE(submission_timestamp) >= DATE("2024-01-01") {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_success' + AND event = 'login.view' AND metrics.string.session_flow_id != '' ), -login_success_with_email_by_service_login_email_confirmation_view AS ( +login_email_confirmation_complete_by_service_login_submit AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream INNER JOIN - login_success_with_email_by_service_login_success AS prev + login_email_confirmation_complete_by_service_login_view AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -234,20 +168,20 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_email_confirmation_view' + AND event = 'login.submit' AND metrics.string.session_flow_id != '' ), -login_success_with_email_by_service_login_email_confirmation_submit AS ( +login_email_confirmation_complete_by_service_login_email_confirmation_view AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream INNER JOIN - login_success_with_email_by_service_login_email_confirmation_view AS prev + login_email_confirmation_complete_by_service_login_submit AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -256,20 +190,20 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_email_confirmation_submit' + AND event = 'login.email_confirmation_view' AND metrics.string.session_flow_id != '' ), -login_success_with_email_by_service_login_email_confirmation_success AS ( +login_email_confirmation_complete_by_service_login_email_confirmation_submit AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_backend.accounts_events + mozdata.accounts_frontend.events_stream INNER JOIN - login_success_with_email_by_service_login_email_confirmation_submit AS prev + login_email_confirmation_complete_by_service_login_email_confirmation_view AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -278,20 +212,20 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_email_confirmation_success' + AND event = 'login.email_confirmation_submit' AND metrics.string.session_flow_id != '' ), -login_success_with_email_by_service_login_complete AS ( +login_email_confirmation_complete_by_service_login_complete AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_backend.accounts_events + mozdata.accounts_backend.events_stream INNER JOIN - login_success_with_email_by_service_login_email_confirmation_success AS prev + login_email_confirmation_complete_by_service_login_email_confirmation_submit AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -300,10 +234,10 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_complete' + AND event = 'login.complete' AND metrics.string.session_flow_id != '' ), -login_success_with_2fa_by_service_login_view AS ( +login_2fa_complete_by_service_login_view AS ( SELECT metrics.string.session_flow_id AS join_key, IF( @@ -318,30 +252,30 @@ ) ) AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream WHERE {% if is_init() %} DATE(submission_timestamp) >= DATE("2024-01-01") {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_view' + AND event = 'login.view' AND metrics.string.session_flow_id != '' ), -login_success_with_2fa_by_service_login_submit AS ( +login_2fa_complete_by_service_login_submit AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream INNER JOIN - login_success_with_2fa_by_service_login_view AS prev + login_2fa_complete_by_service_login_view AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -350,20 +284,20 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_submit' + AND event = 'login.submit' AND metrics.string.session_flow_id != '' ), -login_success_with_2fa_by_service_login_success AS ( +login_2fa_complete_by_service_login_two_factor_view AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_backend.accounts_events + mozdata.accounts_frontend.events_stream INNER JOIN - login_success_with_2fa_by_service_login_submit AS prev + login_2fa_complete_by_service_login_submit AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -372,20 +306,20 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_success' + AND event = 'login.totp_form_view' AND metrics.string.session_flow_id != '' ), -login_success_with_2fa_by_service_login_two_factor_view AS ( +login_2fa_complete_by_service_login_two_factor_submit AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream INNER JOIN - login_success_with_2fa_by_service_login_success AS prev + login_2fa_complete_by_service_login_two_factor_view AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -394,20 +328,20 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_totp_form_view' + AND event = 'login.totp_code_submit' AND metrics.string.session_flow_id != '' ), -login_success_with_2fa_by_service_login_two_factor_submit AS ( +login_2fa_complete_by_service_login_complete AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_backend.events_stream INNER JOIN - login_success_with_2fa_by_service_login_two_factor_view AS prev + login_2fa_complete_by_service_login_two_factor_submit AS prev ON prev.submission_date = DATE(submission_timestamp) AND prev.join_key = metrics.string.session_flow_id WHERE @@ -416,309 +350,200 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'login_totp_code_submit' - AND metrics.string.session_flow_id != '' -), -login_success_with_2fa_by_service_login_two_factor_success AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_frontend.accounts_events - INNER JOIN - login_success_with_2fa_by_service_login_two_factor_submit AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-01-01") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND metrics.string.event_name = 'login_totp_code_success_view' - AND metrics.string.session_flow_id != '' -), -login_success_with_2fa_by_service_login_complete AS ( - SELECT - metrics.string.session_flow_id AS join_key, - prev.service AS service, - DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, - metrics.string.session_flow_id AS column - FROM - mozdata.accounts_backend.accounts_events - INNER JOIN - login_success_with_2fa_by_service_login_two_factor_success AS prev - ON prev.submission_date = DATE(submission_timestamp) - AND prev.join_key = metrics.string.session_flow_id - WHERE - {% if is_init() %} - DATE(submission_timestamp) >= DATE("2024-01-01") - {% else %} - DATE(submission_timestamp) = @submission_date - {% endif %} - AND metrics.string.event_name = 'login_complete' + AND event = 'login.complete' AND metrics.string.session_flow_id != '' ), -- aggregate each funnel step value -login_overall_success_by_service_login_view_aggregated AS ( - SELECT - submission_date, - "login_overall_success_by_service" AS funnel, - service, - COUNT(DISTINCT column) AS aggregated - FROM - login_overall_success_by_service_login_view - GROUP BY - service, - submission_date, - funnel -), -login_overall_success_by_service_login_complete_aggregated AS ( +login_complete_by_service_login_view_aggregated AS ( SELECT submission_date, - "login_overall_success_by_service" AS funnel, + "login_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_overall_success_by_service_login_complete + login_complete_by_service_login_view GROUP BY service, submission_date, funnel ), -login_submit_overall_success_by_service_login_view_aggregated AS ( +login_complete_by_service_login_complete_aggregated AS ( SELECT submission_date, - "login_submit_overall_success_by_service" AS funnel, + "login_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_submit_overall_success_by_service_login_view + login_complete_by_service_login_complete GROUP BY service, submission_date, funnel ), -login_submit_overall_success_by_service_login_submit_aggregated AS ( +login_submit_complete_by_service_login_view_aggregated AS ( SELECT submission_date, - "login_submit_overall_success_by_service" AS funnel, + "login_submit_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_submit_overall_success_by_service_login_submit + login_submit_complete_by_service_login_view GROUP BY service, submission_date, funnel ), -login_submit_overall_success_by_service_login_success_aggregated AS ( +login_submit_complete_by_service_login_submit_aggregated AS ( SELECT submission_date, - "login_submit_overall_success_by_service" AS funnel, + "login_submit_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_submit_overall_success_by_service_login_success + login_submit_complete_by_service_login_submit GROUP BY service, submission_date, funnel ), -login_submit_overall_success_by_service_login_complete_aggregated AS ( +login_submit_complete_by_service_login_complete_aggregated AS ( SELECT submission_date, - "login_submit_overall_success_by_service" AS funnel, + "login_submit_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_submit_overall_success_by_service_login_complete + login_submit_complete_by_service_login_complete GROUP BY service, submission_date, funnel ), -login_success_with_email_by_service_login_view_aggregated AS ( +login_email_confirmation_complete_by_service_login_view_aggregated AS ( SELECT submission_date, - "login_success_with_email_by_service" AS funnel, + "login_email_confirmation_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_email_by_service_login_view + login_email_confirmation_complete_by_service_login_view GROUP BY service, submission_date, funnel ), -login_success_with_email_by_service_login_submit_aggregated AS ( +login_email_confirmation_complete_by_service_login_submit_aggregated AS ( SELECT submission_date, - "login_success_with_email_by_service" AS funnel, + "login_email_confirmation_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_email_by_service_login_submit + login_email_confirmation_complete_by_service_login_submit GROUP BY service, submission_date, funnel ), -login_success_with_email_by_service_login_success_aggregated AS ( +login_email_confirmation_complete_by_service_login_email_confirmation_view_aggregated AS ( SELECT submission_date, - "login_success_with_email_by_service" AS funnel, + "login_email_confirmation_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_email_by_service_login_success + login_email_confirmation_complete_by_service_login_email_confirmation_view GROUP BY service, submission_date, funnel ), -login_success_with_email_by_service_login_email_confirmation_view_aggregated AS ( +login_email_confirmation_complete_by_service_login_email_confirmation_submit_aggregated AS ( SELECT submission_date, - "login_success_with_email_by_service" AS funnel, + "login_email_confirmation_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_email_by_service_login_email_confirmation_view + login_email_confirmation_complete_by_service_login_email_confirmation_submit GROUP BY service, submission_date, funnel ), -login_success_with_email_by_service_login_email_confirmation_submit_aggregated AS ( +login_email_confirmation_complete_by_service_login_complete_aggregated AS ( SELECT submission_date, - "login_success_with_email_by_service" AS funnel, + "login_email_confirmation_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_email_by_service_login_email_confirmation_submit + login_email_confirmation_complete_by_service_login_complete GROUP BY service, submission_date, funnel ), -login_success_with_email_by_service_login_email_confirmation_success_aggregated AS ( +login_2fa_complete_by_service_login_view_aggregated AS ( SELECT submission_date, - "login_success_with_email_by_service" AS funnel, + "login_2fa_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_email_by_service_login_email_confirmation_success + login_2fa_complete_by_service_login_view GROUP BY service, submission_date, funnel ), -login_success_with_email_by_service_login_complete_aggregated AS ( +login_2fa_complete_by_service_login_submit_aggregated AS ( SELECT submission_date, - "login_success_with_email_by_service" AS funnel, + "login_2fa_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_email_by_service_login_complete + login_2fa_complete_by_service_login_submit GROUP BY service, submission_date, funnel ), -login_success_with_2fa_by_service_login_view_aggregated AS ( +login_2fa_complete_by_service_login_two_factor_view_aggregated AS ( SELECT submission_date, - "login_success_with_2fa_by_service" AS funnel, + "login_2fa_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_2fa_by_service_login_view + login_2fa_complete_by_service_login_two_factor_view GROUP BY service, submission_date, funnel ), -login_success_with_2fa_by_service_login_submit_aggregated AS ( +login_2fa_complete_by_service_login_two_factor_submit_aggregated AS ( SELECT submission_date, - "login_success_with_2fa_by_service" AS funnel, + "login_2fa_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_2fa_by_service_login_submit + login_2fa_complete_by_service_login_two_factor_submit GROUP BY service, submission_date, funnel ), -login_success_with_2fa_by_service_login_success_aggregated AS ( +login_2fa_complete_by_service_login_complete_aggregated AS ( SELECT submission_date, - "login_success_with_2fa_by_service" AS funnel, + "login_2fa_complete_by_service" AS funnel, service, COUNT(DISTINCT column) AS aggregated FROM - login_success_with_2fa_by_service_login_success - GROUP BY - service, - submission_date, - funnel -), -login_success_with_2fa_by_service_login_two_factor_view_aggregated AS ( - SELECT - submission_date, - "login_success_with_2fa_by_service" AS funnel, - service, - COUNT(DISTINCT column) AS aggregated - FROM - login_success_with_2fa_by_service_login_two_factor_view - GROUP BY - service, - submission_date, - funnel -), -login_success_with_2fa_by_service_login_two_factor_submit_aggregated AS ( - SELECT - submission_date, - "login_success_with_2fa_by_service" AS funnel, - service, - COUNT(DISTINCT column) AS aggregated - FROM - login_success_with_2fa_by_service_login_two_factor_submit - GROUP BY - service, - submission_date, - funnel -), -login_success_with_2fa_by_service_login_two_factor_success_aggregated AS ( - SELECT - submission_date, - "login_success_with_2fa_by_service" AS funnel, - service, - COUNT(DISTINCT column) AS aggregated - FROM - login_success_with_2fa_by_service_login_two_factor_success - GROUP BY - service, - submission_date, - funnel -), -login_success_with_2fa_by_service_login_complete_aggregated AS ( - SELECT - submission_date, - "login_success_with_2fa_by_service" AS funnel, - service, - COUNT(DISTINCT column) AS aggregated - FROM - login_success_with_2fa_by_service_login_complete + login_2fa_complete_by_service_login_complete GROUP BY service, submission_date, @@ -728,131 +553,98 @@ merged_funnels AS ( SELECT COALESCE( - login_overall_success_by_service_login_view_aggregated.service, - login_submit_overall_success_by_service_login_view_aggregated.service, - login_success_with_email_by_service_login_view_aggregated.service, - login_success_with_2fa_by_service_login_view_aggregated.service + login_complete_by_service_login_view_aggregated.service, + login_submit_complete_by_service_login_view_aggregated.service, + login_email_confirmation_complete_by_service_login_view_aggregated.service, + login_2fa_complete_by_service_login_view_aggregated.service ) AS service, submission_date, funnel, COALESCE( - login_overall_success_by_service_login_view_aggregated.aggregated, - login_submit_overall_success_by_service_login_view_aggregated.aggregated, - login_success_with_email_by_service_login_view_aggregated.aggregated, - login_success_with_2fa_by_service_login_view_aggregated.aggregated + login_complete_by_service_login_view_aggregated.aggregated, + login_submit_complete_by_service_login_view_aggregated.aggregated, + login_email_confirmation_complete_by_service_login_view_aggregated.aggregated, + login_2fa_complete_by_service_login_view_aggregated.aggregated ) AS login_view, COALESCE( NULL, - login_submit_overall_success_by_service_login_submit_aggregated.aggregated, - login_success_with_email_by_service_login_submit_aggregated.aggregated, - login_success_with_2fa_by_service_login_submit_aggregated.aggregated + login_submit_complete_by_service_login_submit_aggregated.aggregated, + login_email_confirmation_complete_by_service_login_submit_aggregated.aggregated, + login_2fa_complete_by_service_login_submit_aggregated.aggregated ) AS login_submit, COALESCE( NULL, - login_submit_overall_success_by_service_login_success_aggregated.aggregated, - login_success_with_email_by_service_login_success_aggregated.aggregated, - login_success_with_2fa_by_service_login_success_aggregated.aggregated - ) AS login_success, - COALESCE( - NULL, NULL, - login_success_with_email_by_service_login_email_confirmation_view_aggregated.aggregated, + login_email_confirmation_complete_by_service_login_email_confirmation_view_aggregated.aggregated, NULL ) AS login_email_confirmation_view, COALESCE( NULL, NULL, - login_success_with_email_by_service_login_email_confirmation_submit_aggregated.aggregated, + login_email_confirmation_complete_by_service_login_email_confirmation_submit_aggregated.aggregated, NULL ) AS login_email_confirmation_submit, COALESCE( NULL, NULL, - login_success_with_email_by_service_login_email_confirmation_success_aggregated.aggregated, - NULL - ) AS login_email_confirmation_success, - COALESCE( - NULL, - NULL, NULL, - login_success_with_2fa_by_service_login_two_factor_view_aggregated.aggregated + login_2fa_complete_by_service_login_two_factor_view_aggregated.aggregated ) AS login_two_factor_view, COALESCE( NULL, NULL, NULL, - login_success_with_2fa_by_service_login_two_factor_submit_aggregated.aggregated + login_2fa_complete_by_service_login_two_factor_submit_aggregated.aggregated ) AS login_two_factor_submit, COALESCE( - NULL, - NULL, - NULL, - login_success_with_2fa_by_service_login_two_factor_success_aggregated.aggregated - ) AS login_two_factor_success, - COALESCE( - login_overall_success_by_service_login_complete_aggregated.aggregated, - login_submit_overall_success_by_service_login_complete_aggregated.aggregated, - login_success_with_email_by_service_login_complete_aggregated.aggregated, - login_success_with_2fa_by_service_login_complete_aggregated.aggregated + login_complete_by_service_login_complete_aggregated.aggregated, + login_submit_complete_by_service_login_complete_aggregated.aggregated, + login_email_confirmation_complete_by_service_login_complete_aggregated.aggregated, + login_2fa_complete_by_service_login_complete_aggregated.aggregated ) AS login_complete, FROM - login_overall_success_by_service_login_view_aggregated - FULL OUTER JOIN - login_overall_success_by_service_login_complete_aggregated - USING (submission_date, service, funnel) - FULL OUTER JOIN - login_submit_overall_success_by_service_login_view_aggregated - USING (submission_date, service, funnel) - FULL OUTER JOIN - login_submit_overall_success_by_service_login_submit_aggregated - USING (submission_date, service, funnel) - FULL OUTER JOIN - login_submit_overall_success_by_service_login_success_aggregated - USING (submission_date, service, funnel) - FULL OUTER JOIN - login_submit_overall_success_by_service_login_complete_aggregated - USING (submission_date, service, funnel) + login_complete_by_service_login_view_aggregated FULL OUTER JOIN - login_success_with_email_by_service_login_view_aggregated + login_complete_by_service_login_complete_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_email_by_service_login_submit_aggregated + login_submit_complete_by_service_login_view_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_email_by_service_login_success_aggregated + login_submit_complete_by_service_login_submit_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_email_by_service_login_email_confirmation_view_aggregated + login_submit_complete_by_service_login_complete_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_email_by_service_login_email_confirmation_submit_aggregated + login_email_confirmation_complete_by_service_login_view_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_email_by_service_login_email_confirmation_success_aggregated + login_email_confirmation_complete_by_service_login_submit_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_email_by_service_login_complete_aggregated + login_email_confirmation_complete_by_service_login_email_confirmation_view_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_2fa_by_service_login_view_aggregated + login_email_confirmation_complete_by_service_login_email_confirmation_submit_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_2fa_by_service_login_submit_aggregated + login_email_confirmation_complete_by_service_login_complete_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_2fa_by_service_login_success_aggregated + login_2fa_complete_by_service_login_view_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_2fa_by_service_login_two_factor_view_aggregated + login_2fa_complete_by_service_login_submit_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_2fa_by_service_login_two_factor_submit_aggregated + login_2fa_complete_by_service_login_two_factor_view_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_2fa_by_service_login_two_factor_success_aggregated + login_2fa_complete_by_service_login_two_factor_submit_aggregated USING (submission_date, service, funnel) FULL OUTER JOIN - login_success_with_2fa_by_service_login_complete_aggregated + login_2fa_complete_by_service_login_complete_aggregated USING (submission_date, service, funnel) ) SELECT diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/registration_funnels_by_service_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/registration_funnels_by_service_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/registration_funnels_by_service_v1/metadata.yaml 2024-07-30 05:55:45.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/registration_funnels_by_service_v1/metadata.yaml 2024-07-30 06:15:11.000000000 +0000 @@ -23,5 +23,5 @@ - workgroup:mozilla-confidential references: query.sql: - - mozdata.accounts_backend.accounts_events - - mozdata.accounts_frontend.accounts_events + - mozdata.accounts_backend.events_stream + - mozdata.accounts_frontend.events_stream diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/registration_funnels_by_service_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/registration_funnels_by_service_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/registration_funnels_by_service_v1/query.sql 2024-07-30 05:55:45.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/registration_funnels_by_service_v1/query.sql 2024-07-30 06:04:05.000000000 +0000 @@ -14,27 +14,28 @@ ) ) AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream WHERE {% if is_init() %} DATE(submission_timestamp) >= DATE("2024-01-01") {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'reg_view' + AND event = 'reg.view' + AND metrics.string.session_flow_id != '' ), registration_overall_success_by_service_reg_email_code_view AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_frontend.accounts_events + mozdata.accounts_frontend.events_stream INNER JOIN registration_overall_success_by_service_reg_view AS prev ON prev.submission_date = DATE(submission_timestamp) @@ -45,17 +46,18 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'reg_signup_code_view' + AND event = 'reg.signup_code_view' + AND metrics.string.session_flow_id != '' ), registration_overall_success_by_service_reg_complete AS ( SELECT metrics.string.session_flow_id AS join_key, prev.service AS service, DATE(submission_timestamp) AS submission_date, - client_info.client_id AS client_id, + metrics.string.account_user_id_sha256 AS client_id, metrics.string.session_flow_id AS column FROM - mozdata.accounts_backend.accounts_events + mozdata.accounts_backend.events_stream INNER JOIN registration_overall_success_by_service_reg_email_code_view AS prev ON prev.submission_date = DATE(submission_timestamp) @@ -66,7 +68,340 @@ {% else %} DATE(submission_timestamp) = @submission_date {% endif %} - AND metrics.string.event_name = 'reg_complete' + AND event = 'reg.complete' + AND metrics.string.session_flow_id != '' +), +registrations_from_google_deeplink_google_deeplink AS ( + SELECT + metrics.string.session_flow_id AS join_key, + IF( + COALESCE( + NULLIF(metrics.string.relying_party_oauth_client_id, ''), + NULLIF(metrics.string.relying_party_service, '') + ) = 'sync', + '5882386c6d801776', + COALESCE( + NULLIF(metrics.string.relying_party_oauth_client_id, ''), + NULLIF(metrics.string.relying_party_service, '') + ) + ) AS service, + DATE(submission_timestamp) AS submission_date, + metrics.string.account_user_id_sha256 AS client_id, + metrics.string.session_flow_id AS column + FROM + mozdata.accounts_frontend.events_stream + WHERE + {% if is_init() %} + DATE(submission_timestamp) >= DATE("2024-01-01") + {% else %} + DATE(submission_timestamp) = @submission_date + {% endif %} + AND event = 'third_party_auth.google_deeplink' + AND metrics.string.session_flow_id != '' +), +registrations_from_google_deeplink_reg_complete AS ( + SELECT + metrics.string.session_flow_id AS join_key, + prev.service AS service, + DATE(submission_timestamp) AS submission_date, + metrics.string.account_user_id_sha256 AS client_id, + metrics.string.session_flow_id AS column + FROM + mozdata.accounts_backend.events_stream + INNER JOIN + registrations_from_google_deeplink_google_deeplink AS prev + ON prev.submission_date = DATE(submission_timestamp) + AND prev.join_key = metrics.string.session_flow_id + WHERE + {% if is_init() %} + DATE(submission_timestamp) >= DATE("2024-01-01") + {% else %} + DATE(submission_timestamp) = @submission_date + {% endif %} + AND event = 'reg.complete' + AND metrics.string.session_flow_id != '' +), +registrations_from_google_deeplink_google_reg_complete AS ( + SELECT + metrics.string.session_flow_id AS join_key, + prev.service AS service, + DATE(submission_timestamp) AS submission_date, + metrics.string.account_user_id_sha256 AS client_id, + metrics.string.session_flow_id AS column + FROM + mozdata.accounts_backend.events_stream + INNER JOIN + registrations_from_google_deeplink_reg_complete AS prev + ON prev.submission_date = DATE(submission_timestamp) + AND prev.join_key = metrics.string.session_flow_id + WHERE + {% if is_init() %} + DATE(submission_timestamp) >= DATE("2024-01-01") + {% else %} + DATE(submission_timestamp) = @submission_date + {% endif %} + AND event = 'third_party_auth.google_reg_complete' + AND metrics.string.session_flow_id != '' +), +registrations_from_apple_deeplink_apple_deeplink AS ( + SELECT + metrics.string.session_flow_id AS join_key, + IF( + COALESCE( + NULLIF(metrics.string.relying_party_oauth_client_id, ''), + NULLIF(metrics.string.relying_party_service, '') + ) = 'sync', + '5882386c6d801776', + COALESCE( + NULLIF(metrics.string.relying_party_oauth_client_id, ''), + NULLIF(metrics.string.relying_party_service, '') + ) + ) AS service, + DATE(submission_timestamp) AS submission_date, + metrics.string.account_user_id_sha256 AS client_id, + metrics.string.session_flow_id AS column + FROM + mozdata.accounts_frontend.events_stream + WHERE + {% if is_init() %} + DATE(submission_timestamp) >= DATE("2024-01-01") + {% else %} + DATE(submission_timestamp) = @submission_date + {% endif %} + AND event = 'third_party_auth.apple_deeplink' + AND metrics.string.session_flow_id != '' +), +registrations_from_apple_deeplink_reg_complete AS ( + SELECT + metrics.string.session_flow_id AS join_key, + prev.service AS service, + DATE(submission_timestamp) AS submission_date, + metrics.string.account_user_id_sha256 AS client_id, + metrics.string.session_flow_id AS column + FROM + mozdata.accounts_backend.events_stream + INNER JOIN + registrations_from_apple_deeplink_apple_deeplink AS prev + ON prev.submission_date = DATE(submission_timestamp) + AND prev.join_key = metrics.string.session_flow_id + WHERE + {% if is_init() %} + DATE(submission_timestamp) >= DATE("2024-01-01") + {% else %} + DATE(submission_timestamp) = @submission_date + {% endif %} + AND event = 'reg.complete' + AND metrics.string.session_flow_id != '' +), +registrations_from_apple_deeplink_apple_reg_complete AS ( + SELECT + metrics.string.session_flow_id AS join_key, + prev.service AS service, + DATE(submission_timestamp) AS submission_date, + metrics.string.account_user_id_sha256 AS client_id, + metrics.string.session_flow_id AS column + FROM + mozdata.accounts_backend.events_stream + INNER JOIN + registrations_from_apple_deeplink_reg_complete AS prev + ON prev.submission_date = DATE(submission_timestamp) + AND prev.join_key = metrics.string.session_flow_id + WHERE + {% if is_init() %} + DATE(submission_timestamp) >= DATE("2024-01-01") + {% else %} + DATE(submission_timestamp) = @submission_date + {% endif %} + AND event = 'third_party_auth.apple_reg_complete' + AND metrics.string.session_flow_id != '' +), +registrations_from_google_email_first_email_first_view AS ( + SELECT + metrics.string.session_flow_id AS join_key, + IF( + COALESCE( + NULLIF(metrics.string.relying_party_oauth_client_id, ''), + NULLIF(metrics.string.relying_party_service, '') + ) = 'sync', + '5882386c6d801776', + COALESCE( + NULLIF(metrics.string.relying_party_oauth_client_id, ''), + NULLIF(metrics.string.relying_party_service, '') + ) + ) AS service, + DATE(submission_timestamp) AS submission_date, + metrics.string.account_user_id_sha256 AS client_id, + metrics.string.session_flow_id AS column + FROM + mozdata.accounts_frontend.events_stream + WHERE + {% if is_init() %} + DATE(submission_timestamp) >= DATE("2024-01-01") + {% else %} + DATE(submission_timestamp) = @submission_date + {% endif %} + AND event = 'email.first_view' + AND metrics.string.session_flow_id != '' +), +registrations_from_google_email_first_email_first_google_start AS ( + SELECT + metrics.string.session_flow_id AS join_key, + prev.service AS service, + DATE(submission_timestamp) AS submission_date, + metrics.string.account_user_id_sha256 AS client_id, + metrics.string.session_flow_id AS column + FROM + mozdata.accounts_frontend.events_stream + INNER JOIN + registrations_from_google_email_first_email_first_view AS prev + ON prev.submission_date = DATE(submission_timestamp) + AND prev.join_key = metrics.string.session_flow_id + WHERE + {% if is_init() %} + DATE(submission_timestamp) >= DATE("2024-01-01") + {% else %} + DATE(submission_timestamp) = @submission_date + {% endif %} + AND event = 'email.first_google_oauth_start' + AND metrics.string.session_flow_id != '' +), +registrations_from_google_email_first_reg_complete AS ( + SELECT + metrics.string.session_flow_id AS join_key, + prev.service AS service, + DATE(submission_timestamp) AS submission_date, + metrics.string.account_user_id_sha256 AS client_id, + metrics.string.session_flow_id AS column + FROM + mozdata.accounts_backend.events_stream + INNER JOIN + registrations_from_google_email_first_email_first_google_start AS prev + ON prev.submission_date = DATE(submission_timestamp) + AND prev.join_key = metrics.string.session_flow_id + WHERE + {% if is_init() %} + DATE(submission_timestamp) >= DATE("2024-01-01") + {% else %} + DATE(submission_timestamp) = @submission_date + {% endif %} + AND event = 'reg.complete' + AND metrics.string.session_flow_id != '' +), +registrations_from_go ```⚠️ Only part of the diff is displayed.
Summary of changes made based on new data available:
events_stream┆Issue is synchronized with this Jira Task