dataops-ci-bot
commented
2 months ago Integration report for "Include more versions of registration and login funnel"
sql.diff
Click to expand!
```diff diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_accounts_backend_external.py /tmp/workspace/generated-sql/dags/bqetl_accounts_backend_external.py --- /tmp/workspace/main-generated-sql/dags/bqetl_accounts_backend_external.py 2024-07-31 18:27:12.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_accounts_backend_external.py 2024-07-31 18:46:01.000000000 +0000 @@ -124,18 +124,6 @@ task_concurrency=1, ) - accounts_db_external__fxa_account_groups__v1 = bigquery_etl_query( - task_id="accounts_db_external__fxa_account_groups__v1", - destination_table="fxa_account_groups_v1", - dataset_id="accounts_db_external", - project_id="moz-fx-data-shared-prod", - owner="akomar@mozilla.com", - email=["akomar@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - task_concurrency=1, - ) - accounts_db_external__fxa_account_reset_tokens__v1 = bigquery_etl_query( task_id="accounts_db_external__fxa_account_reset_tokens__v1", destination_table="fxa_account_reset_tokens_v1", @@ -160,18 +148,6 @@ task_concurrency=1, ) - accounts_db_external__fxa_carts__v1 = bigquery_etl_query( - task_id="accounts_db_external__fxa_carts__v1", - destination_table="fxa_carts_v1", - dataset_id="accounts_db_external", - project_id="moz-fx-data-shared-prod", - owner="akomar@mozilla.com", - email=["akomar@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - task_concurrency=1, - ) - accounts_db_external__fxa_db_metadata__v1 = bigquery_etl_query( task_id="accounts_db_external__fxa_db_metadata__v1", destination_table="fxa_db_metadata_v1", @@ -256,18 +232,6 @@ task_concurrency=1, ) - accounts_db_external__fxa_groups__v1 = bigquery_etl_query( - task_id="accounts_db_external__fxa_groups__v1", - destination_table="fxa_groups_v1", - dataset_id="accounts_db_external", - project_id="moz-fx-data-shared-prod", - owner="akomar@mozilla.com", - email=["akomar@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - task_concurrency=1, - ) - accounts_db_external__fxa_key_fetch_tokens__v1 = bigquery_etl_query( task_id="accounts_db_external__fxa_key_fetch_tokens__v1", destination_table="fxa_key_fetch_tokens_v1", @@ -604,18 +568,6 @@ task_concurrency=1, ) - accounts_db_nonprod_external__fxa_account_groups__v1 = bigquery_etl_query( - task_id="accounts_db_nonprod_external__fxa_account_groups__v1", - destination_table="fxa_account_groups_v1", - dataset_id="accounts_db_nonprod_external", - project_id="moz-fx-data-shared-prod", - owner="akomar@mozilla.com", - email=["akomar@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - task_concurrency=1, - ) - accounts_db_nonprod_external__fxa_account_reset_tokens__v1 = bigquery_etl_query( task_id="accounts_db_nonprod_external__fxa_account_reset_tokens__v1", destination_table="fxa_account_reset_tokens_v1", @@ -640,18 +592,6 @@ task_concurrency=1, ) - accounts_db_nonprod_external__fxa_carts__v1 = bigquery_etl_query( - task_id="accounts_db_nonprod_external__fxa_carts__v1", - destination_table="fxa_carts_v1", - dataset_id="accounts_db_nonprod_external", - project_id="moz-fx-data-shared-prod", - owner="akomar@mozilla.com", - email=["akomar@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - task_concurrency=1, - ) - accounts_db_nonprod_external__fxa_db_metadata__v1 = bigquery_etl_query( task_id="accounts_db_nonprod_external__fxa_db_metadata__v1", destination_table="fxa_db_metadata_v1", @@ -732,18 +672,6 @@ dataset_id="accounts_db_nonprod_external", project_id="moz-fx-data-shared-prod", owner="akomar@mozilla.com", - email=["akomar@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter=None, - depends_on_past=False, - task_concurrency=1, - ) - - accounts_db_nonprod_external__fxa_groups__v1 = bigquery_etl_query( - task_id="accounts_db_nonprod_external__fxa_groups__v1", - destination_table="fxa_groups_v1", - dataset_id="accounts_db_nonprod_external", - project_id="moz-fx-data-shared-prod", - owner="akomar@mozilla.com", email=["akomar@mozilla.com", "telemetry-alerts@mozilla.com"], date_partition_parameter=None, depends_on_past=False, 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-31 18:27:12.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_generated_funnels.py 2024-07-31 18:46:02.000000000 +0000 @@ -288,6 +288,21 @@ ) ) + 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( wait_for_copy_deduplicate_all ) @@ -304,6 +319,10 @@ wait_for_accounts_frontend_derived__events_stream__v1 ) + accounts_frontend_derived__login_funnels_by_service__v1.set_upstream( + wait_for_copy_deduplicate_all + ) + accounts_frontend_derived__monitor_mozilla_accounts_funnels__v1.set_upstream( wait_for_copy_deduplicate_all ) @@ -345,3 +364,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_db_external: fxa_account_groups_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external: fxa_carts_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external: fxa_groups_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external: fxa_account_groups_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external: fxa_carts_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external: fxa_groups_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived: glam_client_probe_counts_extract_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/glam_extract_firefox_beta_v1: query.sql Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/glam_extract_firefox_beta_v1: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/glam_extract_firefox_nightly_v1: query.sql Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/glam_extract_firefox_nightly_v1: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/glam_extract_firefox_release_v1: query.sql Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived/glam_extract_firefox_release_v1: schema.yaml diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/metadata.yaml 2024-07-31 18:22:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,22 +0,0 @@ -friendly_name: accountGroups table from production fxa database -description: | - A mirror of the `accountGroups` table from the production `fxa` CloudSQL database, excluding columns containing confidential data, updated daily to match the current state of the table. - See https://mozilla.github.io/ecosystem-platform/reference/database-structure#database-fxa -owners: -- akomar@mozilla.com -labels: - application: accounts_backend - schedule: daily - dag: bqetl_accounts_backend_external - owner1: akomar -scheduling: - dag_name: bqetl_accounts_backend_external - date_partition_parameter: null -bigquery: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:accounts-confidential -references: - query.sql: - - EXTERNAL_QUERY('moz-fx-fxa-prod.us.fxa-rds-prod-prod-fxa', 'SELECT\n uid,\n group_id,\n role,\n managed_by,\n expires,\n notes\n FROM\n fxa.accountGroups\n ') diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/query.sql 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,21 +0,0 @@ -SELECT - TO_HEX(uid) AS uid, - group_id, - role, - managed_by, - SAFE.TIMESTAMP_MILLIS(SAFE_CAST(expires AS INT)) AS expires, - notes, -FROM - EXTERNAL_QUERY( - "moz-fx-fxa-prod.us.fxa-rds-prod-prod-fxa", - """SELECT - uid, - group_id, - role, - managed_by, - expires, - notes - FROM - fxa.accountGroups - """ - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/schema.yaml 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_account_groups_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,20 +0,0 @@ -fields: -- name: uid - type: STRING - mode: NULLABLE - description: Account ID in hexadecimal format. -- name: group_id - type: INTEGER - mode: NULLABLE -- name: role - type: STRING - mode: NULLABLE -- name: managed_by - type: STRING - mode: NULLABLE -- name: expires - type: TIMESTAMP - mode: NULLABLE -- name: notes - type: STRING - mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/metadata.yaml 2024-07-31 18:22:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,22 +0,0 @@ -friendly_name: carts table from production fxa database -description: | - A mirror of the `carts` table from the production `fxa` CloudSQL database, excluding columns containing confidential data, updated daily to match the current state of the table. - See https://mozilla.github.io/ecosystem-platform/reference/database-structure#database-fxa -owners: -- akomar@mozilla.com -labels: - application: accounts_backend - schedule: daily - dag: bqetl_accounts_backend_external - owner1: akomar -scheduling: - dag_name: bqetl_accounts_backend_external - date_partition_parameter: null -bigquery: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:accounts-confidential -references: - query.sql: - - EXTERNAL_QUERY('moz-fx-fxa-prod.us.fxa-rds-prod-prod-fxa', 'SELECT\n id,\n uid,\n state,\n errorReasonId,\n offeringConfigId,\n interval,\n experiment,\n taxAddress,\n createdAt,\n updatedAt,\n couponCode,\n stripeCustomerId,\n email,\n amount,\n version,\n eligibilityStatus\n FROM\n fxa.carts\n ') diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/query.sql 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,41 +0,0 @@ -SELECT - TO_HEX(id) AS id, - TO_HEX(uid) AS uid, - state, - errorReasonId, - offeringConfigId, - `interval`, - experiment, - taxAddress, - SAFE.TIMESTAMP_MILLIS(SAFE_CAST(createdAt AS INT)) AS createdAt, - SAFE.TIMESTAMP_MILLIS(SAFE_CAST(updatedAt AS INT)) AS updatedAt, - couponCode, - stripeCustomerId, - email, - amount, - version, - eligibilityStatus, -FROM - EXTERNAL_QUERY( - "moz-fx-fxa-prod.us.fxa-rds-prod-prod-fxa", - """SELECT - id, - uid, - state, - errorReasonId, - offeringConfigId, - `interval`, - experiment, - taxAddress, - createdAt, - updatedAt, - couponCode, - stripeCustomerId, - email, - amount, - version, - eligibilityStatus - FROM - fxa.carts - """ - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/schema.yaml 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_carts_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,51 +0,0 @@ -fields: -- name: id - type: STRING - mode: NULLABLE - description: Cart ID in hexadecimal format. -- name: uid - type: STRING - mode: NULLABLE - description: Account ID in hexadecimal format. -- name: state - type: STRING - mode: NULLABLE -- name: errorReasonId - type: STRING - mode: NULLABLE -- name: offeringConfigId - type: STRING - mode: NULLABLE -- name: interval - type: STRING - mode: NULLABLE -- name: experiment - type: STRING - mode: NULLABLE -- name: taxAddress - type: STRING - mode: NULLABLE -- name: createdAt - type: TIMESTAMP - mode: NULLABLE -- name: updatedAt - type: TIMESTAMP - mode: NULLABLE -- name: couponCode - type: STRING - mode: NULLABLE -- name: stripeCustomerId - type: STRING - mode: NULLABLE -- name: email - type: STRING - mode: NULLABLE -- name: amount - type: STRING - mode: NULLABLE -- name: version - type: INTEGER - mode: NULLABLE -- name: eligibilityStatus - type: STRING - mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/metadata.yaml 2024-07-31 18:22:58.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,22 +0,0 @@ -friendly_name: groups table from production fxa database -description: | - A mirror of the `groups` table from the production `fxa` CloudSQL database, excluding columns containing confidential data, updated daily to match the current state of the table. - See https://mozilla.github.io/ecosystem-platform/reference/database-structure#database-fxa -owners: -- akomar@mozilla.com -labels: - application: accounts_backend - schedule: daily - dag: bqetl_accounts_backend_external - owner1: akomar -scheduling: - dag_name: bqetl_accounts_backend_external - date_partition_parameter: null -bigquery: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:accounts-confidential -references: - query.sql: - - EXTERNAL_QUERY('moz-fx-fxa-prod.us.fxa-rds-prod-prod-fxa', 'SELECT\n id,\n name,\n display_name,\n capabilities\n FROM\n fxa.groups\n ') diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/query.sql 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,17 +0,0 @@ -SELECT - id, - name, - display_name, - capabilities, -FROM - EXTERNAL_QUERY( - "moz-fx-fxa-prod.us.fxa-rds-prod-prod-fxa", - """SELECT - id, - name, - display_name, - capabilities - FROM - fxa.groups - """ - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/schema.yaml 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_external/fxa_groups_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,13 +0,0 @@ -fields: -- name: id - type: INTEGER - mode: NULLABLE -- name: name - type: STRING - mode: NULLABLE -- name: display_name - type: STRING - mode: NULLABLE -- name: capabilities - type: STRING - mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/metadata.yaml 2024-07-31 18:22:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,22 +0,0 @@ -friendly_name: accountGroups table from nonprod (stage) fxa database -description: | - A mirror of the `accountGroups` table from the nonprod (stage) `fxa` CloudSQL database, excluding columns containing confidential data, updated daily to match the current state of the table. - See https://mozilla.github.io/ecosystem-platform/reference/database-structure#database-fxa -owners: -- akomar@mozilla.com -labels: - application: accounts_backend - schedule: daily - dag: bqetl_accounts_backend_external - owner1: akomar -scheduling: - dag_name: bqetl_accounts_backend_external - date_partition_parameter: null -bigquery: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:accounts-confidential -references: - query.sql: - - EXTERNAL_QUERY('moz-fx-fxa-nonprod.us.fxa-rds-nonprod-stage-fxa', 'SELECT\n uid,\n group_id,\n role,\n managed_by,\n expires,\n notes\n FROM\n fxa.accountGroups\n ') diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/query.sql 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,21 +0,0 @@ -SELECT - TO_HEX(uid) AS uid, - group_id, - role, - managed_by, - SAFE.TIMESTAMP_MILLIS(SAFE_CAST(expires AS INT)) AS expires, - notes, -FROM - EXTERNAL_QUERY( - "moz-fx-fxa-nonprod.us.fxa-rds-nonprod-stage-fxa", - """SELECT - uid, - group_id, - role, - managed_by, - expires, - notes - FROM - fxa.accountGroups - """ - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/schema.yaml 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_account_groups_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,20 +0,0 @@ -fields: -- name: uid - type: STRING - mode: NULLABLE - description: Account ID in hexadecimal format. -- name: group_id - type: INTEGER - mode: NULLABLE -- name: role - type: STRING - mode: NULLABLE -- name: managed_by - type: STRING - mode: NULLABLE -- name: expires - type: TIMESTAMP - mode: NULLABLE -- name: notes - type: STRING - mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/metadata.yaml 2024-07-31 18:22:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,22 +0,0 @@ -friendly_name: carts table from nonprod (stage) fxa database -description: | - A mirror of the `carts` table from the nonprod (stage) `fxa` CloudSQL database, excluding columns containing confidential data, updated daily to match the current state of the table. - See https://mozilla.github.io/ecosystem-platform/reference/database-structure#database-fxa -owners: -- akomar@mozilla.com -labels: - application: accounts_backend - schedule: daily - dag: bqetl_accounts_backend_external - owner1: akomar -scheduling: - dag_name: bqetl_accounts_backend_external - date_partition_parameter: null -bigquery: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:accounts-confidential -references: - query.sql: - - EXTERNAL_QUERY('moz-fx-fxa-nonprod.us.fxa-rds-nonprod-stage-fxa', 'SELECT\n id,\n uid,\n state,\n errorReasonId,\n offeringConfigId,\n interval,\n experiment,\n taxAddress,\n createdAt,\n updatedAt,\n couponCode,\n stripeCustomerId,\n email,\n amount,\n version,\n eligibilityStatus\n FROM\n fxa.carts\n ') diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/query.sql 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,41 +0,0 @@ -SELECT - TO_HEX(id) AS id, - TO_HEX(uid) AS uid, - state, - errorReasonId, - offeringConfigId, - `interval`, - experiment, - taxAddress, - SAFE.TIMESTAMP_MILLIS(SAFE_CAST(createdAt AS INT)) AS createdAt, - SAFE.TIMESTAMP_MILLIS(SAFE_CAST(updatedAt AS INT)) AS updatedAt, - couponCode, - stripeCustomerId, - email, - amount, - version, - eligibilityStatus, -FROM - EXTERNAL_QUERY( - "moz-fx-fxa-nonprod.us.fxa-rds-nonprod-stage-fxa", - """SELECT - id, - uid, - state, - errorReasonId, - offeringConfigId, - `interval`, - experiment, - taxAddress, - createdAt, - updatedAt, - couponCode, - stripeCustomerId, - email, - amount, - version, - eligibilityStatus - FROM - fxa.carts - """ - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/schema.yaml 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_carts_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,51 +0,0 @@ -fields: -- name: id - type: STRING - mode: NULLABLE - description: Cart ID in hexadecimal format. -- name: uid - type: STRING - mode: NULLABLE - description: Account ID in hexadecimal format. -- name: state - type: STRING - mode: NULLABLE -- name: errorReasonId - type: STRING - mode: NULLABLE -- name: offeringConfigId - type: STRING - mode: NULLABLE -- name: interval - type: STRING - mode: NULLABLE -- name: experiment - type: STRING - mode: NULLABLE -- name: taxAddress - type: STRING - mode: NULLABLE -- name: createdAt - type: TIMESTAMP - mode: NULLABLE -- name: updatedAt - type: TIMESTAMP - mode: NULLABLE -- name: couponCode - type: STRING - mode: NULLABLE -- name: stripeCustomerId - type: STRING - mode: NULLABLE -- name: email - type: STRING - mode: NULLABLE -- name: amount - type: STRING - mode: NULLABLE -- name: version - type: INTEGER - mode: NULLABLE -- name: eligibilityStatus - type: STRING - mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/metadata.yaml 2024-07-31 18:22:52.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,22 +0,0 @@ -friendly_name: groups table from nonprod (stage) fxa database -description: | - A mirror of the `groups` table from the nonprod (stage) `fxa` CloudSQL database, excluding columns containing confidential data, updated daily to match the current state of the table. - See https://mozilla.github.io/ecosystem-platform/reference/database-structure#database-fxa -owners: -- akomar@mozilla.com -labels: - application: accounts_backend - schedule: daily - dag: bqetl_accounts_backend_external - owner1: akomar -scheduling: - dag_name: bqetl_accounts_backend_external - date_partition_parameter: null -bigquery: null -workgroup_access: -- role: roles/bigquery.dataViewer - members: - - workgroup:accounts-confidential -references: - query.sql: - - EXTERNAL_QUERY('moz-fx-fxa-nonprod.us.fxa-rds-nonprod-stage-fxa', 'SELECT\n id,\n name,\n display_name,\n capabilities\n FROM\n fxa.groups\n ') diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/query.sql 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 @@ -1,17 +0,0 @@ -SELECT - id, - name, - display_name, - capabilities, -FROM - EXTERNAL_QUERY( - "moz-fx-fxa-nonprod.us.fxa-rds-nonprod-stage-fxa", - """SELECT - id, - name, - display_name, - capabilities - FROM - fxa.groups - """ - ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/schema.yaml 2024-07-31 18:21:11.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_db_nonprod_external/fxa_groups_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 @@ -1,13 +0,0 @@ -fields: -- name: id - type: INTEGER - mode: NULLABLE -- name: name - type: STRING - mode: NULLABLE -- name: display_name - type: STRING - mode: NULLABLE -- name: capabilities - type: STRING - mode: NULLABLE 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-31 18:21:48.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/metadata.yaml 2024-07-31 18:40:49.000000000 +0000 @@ -23,5 +23,6 @@ - workgroup:mozilla-confidential references: query.sql: + - mozdata.accounts_backend.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-31 18:21:48.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/login_funnels_by_service_v1/query.sql 2024-07-31 18:30:32.000000000 +0000 @@ -353,6 +353,858 @@ AND event = 'login.complete' AND metrics.string.session_flow_id != '' ), +logins_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 != '' +), +logins_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 + logins_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 != '' +), +logins_from_google_email_first_login_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 + logins_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 = 'login.complete' + AND metrics.string.session_flow_id != '' +), +logins_from_google_email_first_google_login_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.accounts_events + INNER JOIN + logins_from_google_email_first_login_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 metrics.string.event_name = 'third_party_auth_google_login_complete' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_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 != '' +), +logins_from_apple_email_first_email_first_apple_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 + logins_from_apple_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_apple_oauth_start' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_email_first_login_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 + logins_from_apple_email_first_email_first_apple_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 = 'login.complete' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_email_first_apple_login_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.accounts_events + INNER JOIN + logins_from_apple_email_first_login_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 metrics.string.event_name = 'third_party_auth_apple_login_complete' + AND metrics.string.session_flow_id != '' +), +logins_from_google_reg_reg_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 = 'reg.view' + AND metrics.string.session_flow_id != '' +), +logins_from_google_reg_reg_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 + logins_from_google_reg_reg_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 = 'third_party_auth.google_reg_start' + AND metrics.string.session_flow_id != '' +), +logins_from_google_reg_login_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 + logins_from_google_reg_reg_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 = 'login.complete' + AND metrics.string.session_flow_id != '' +), +logins_from_google_reg_google_login_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.accounts_events + INNER JOIN + logins_from_google_reg_login_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 metrics.string.event_name = 'third_party_auth_google_login_complete' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_reg_reg_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 = 'reg.view' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_reg_reg_apple_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 + logins_from_apple_reg_reg_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 = 'third_party_auth.apple_reg_start' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_reg_login_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 + logins_from_apple_reg_reg_apple_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 = 'login.complete' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_reg_apple_login_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.accounts_events + INNER JOIN + logins_from_apple_reg_login_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 metrics.string.event_name = 'third_party_auth_apple_login_complete' + AND metrics.string.session_flow_id != '' +), +logins_from_google_login_login_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 = 'login.view' + AND metrics.string.session_flow_id != '' +), +logins_from_google_login_login_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 + logins_from_google_login_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 event = 'third_party_auth.google_login_start' + AND metrics.string.session_flow_id != '' +), +logins_from_google_login_login_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 + logins_from_google_login_login_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 = 'login.complete' + AND metrics.string.session_flow_id != '' +), +logins_from_google_login_google_login_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.accounts_events + INNER JOIN + logins_from_google_login_login_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 metrics.string.event_name = 'third_party_auth_google_login_complete' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_login_login_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 = 'login.view' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_login_login_apple_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 + logins_from_apple_login_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 event = 'third_party_auth.apple_login_start' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_login_login_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 + logins_from_apple_login_login_apple_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 = 'login.complete' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_login_apple_login_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.accounts_events + INNER JOIN + logins_from_apple_login_login_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 metrics.string.event_name = 'third_party_auth_apple_login_complete' + AND metrics.string.session_flow_id != '' +), +login_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 != '' +), +login_from_google_deeplink_login_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 + login_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 = 'login.complete' + AND metrics.string.session_flow_id != '' +), +login_from_google_deeplink_google_login_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.accounts_events + INNER JOIN + login_from_google_deeplink_login_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 metrics.string.event_name = 'third_party_auth_google_login_complete' + AND metrics.string.session_flow_id != '' +), +logins_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 != '' +), +logins_from_apple_deeplink_login_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 + logins_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 = 'login.complete' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_deeplink_apple_login_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.accounts_events + INNER JOIN + logins_from_apple_deeplink_login_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 metrics.string.event_name = 'third_party_auth_apple_login_complete' + AND metrics.string.session_flow_id != '' +), +login_from_google_cached_logins_cached_login_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 = 'cached_login.view' + AND metrics.string.session_flow_id != '' +), +login_from_google_cached_logins_login_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 + login_from_google_cached_logins_cached_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 event = 'login.complete' + AND metrics.string.session_flow_id != '' +), +login_from_google_cached_logins_google_login_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.accounts_events + INNER JOIN + login_from_google_cached_logins_login_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 metrics.string.event_name = 'third_party_auth_google_login_complete' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_cached_logins_cached_login_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 = 'cached_login.view' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_cached_logins_login_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 + logins_from_apple_cached_logins_cached_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 event = 'login.complete' + AND metrics.string.session_flow_id != '' +), +logins_from_apple_cached_logins_apple_login_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.accounts_events + INNER JOIN + logins_from_apple_cached_logins_login_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 metrics.string.event_name = 'third_party_auth_apple_login_complete' + AND metrics.string.session_flow_id != '' +), -- aggregate each funnel step value login_complete_by_service_login_view_aggregated AS ( SELECT @@ -549,6 +1401,474 @@ submission_date, funnel ), +logins_from_google_email_first_email_first_view_aggregated AS ( + SELECT + submission_date, + "logins_from_google_email_first" AS funnel, + service, + COUNT(DISTINCT column) AS aggregated + FROM + logins_from_google_email_first_email_first_view + GROUP BY + service, + submission_date, + funnel +), +logins_from_google_email_first_email_first_google_start_aggregated AS ( + SELECT + submission_date, + "logins_from_google_email_first" AS funnel, + service, + COUNT(DISTINCT column) AS aggregated + FROM + logins_from_google_email_first_email_first_google_start + GROUP BY + service, + submission_date, + funnel +), +logins_from_google_email_first_login_complete_aggregated AS ( + SELECT + submission_date, + "logins_from_google_email_first" AS funnel, + service, + COUNT(DISTINCT column) AS aggregated + FROM + logins_from_google_email_first_login_complete + GROUP BY + service, + submission_date, + funnel +), +logins_from_google_email_first_google_login_complete_aggregated AS ( + SELECT + submission_date, + "logins_from_google_email_first" AS funnel, + service, + COUNT(DISTINCT column) AS aggregated + FROM + logins_from_google_email_first_google_login_complete + GROUP BY + service, + submission_date, + funnel +), +logins_from_apple_email_first_email_first_view_aggregated AS ( + SELECT + submissio ```⚠️ Only part of the diff is displayed.
Final updates (for now) include:
login_completecount)┆Issue is synchronized with this Jira Task