feat: move mobile engagement logic into mobile kpi support metrics generator

mozilla / bigquery-etl

Bigquery ETL

https://mozilla.github.io/bigquery-etl

Mozilla Public License 2.0

246 stars 98 forks source link

feat: move mobile engagement logic into mobile kpi support metrics generator #5661

Closed kik-kik closed 2 months ago

kik-kik commented 2 months ago

feat: move mobile engagement logic into mobile kpi support metrics generator

This change includes:

Removing mobile engagement related queries from telemetry and telemetry_derived
Adding the mobile engagement logic to mobile_kpi_support_metrics generator

The following command can be used locally to generate all artifacts produced by this generator:

./bqetl generate mobile_kpi_support_metrics --output_dir=gen_sql

Checklist for reviewer:

[ ] Commits should reference a bug or github issue, if relevant (if a bug is referenced, the pull request should include the bug number in the title).
[ ] If the PR comes from a fork, trigger integration CI tests by running the Push to upstream workflow and provide the <username>:<branch> of the fork as parameter. The parameter will also show up in the logs of the manual-trigger-required-for-fork CI task together with more detailed instructions.
[ ] If adding a new field to a query, ensure that the schema and dependent downstream schemas have been updated.
[ ] When adding a new derived dataset, ensure that data is not available already (fully or partially) and recommend extending an existing dataset in favor of creating new ones. Data can be available in the bigquery-etl repository, looker-hub or in looker-spoke-default.

For modifications to schemas in restricted namespaces (see CODEOWNERS):

[ ] Follow the change control procedure

⚠️ Only part of the diff is displayed.

Click to expand!

```diff Only in /tmp/workspace/main-generated-sql/dags/: bqetl_mobile_engagement_model.py diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_analytics_tables.py /tmp/workspace/generated-sql/dags/bqetl_analytics_tables.py --- /tmp/workspace/main-generated-sql/dags/bqetl_analytics_tables.py 2024-05-29 13:26:23.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_analytics_tables.py 2024-05-29 13:38:14.000000000 +0000 @@ -284,13 +284,6 @@ external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", ) - ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - checks__fail_fenix_derived__firefox_android_clients__v1_external.set_upstream( checks__fail_fenix_derived__firefox_android_clients__v1 ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_firefox_ios.py /tmp/workspace/generated-sql/dags/bqetl_firefox_ios.py --- /tmp/workspace/main-generated-sql/dags/bqetl_firefox_ios.py 2024-05-29 13:26:23.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_firefox_ios.py 2024-05-29 13:38:13.000000000 +0000 @@ -287,13 +287,6 @@ execution_date="{{ (execution_date - macros.timedelta(seconds=7200)).isoformat() }}", ) - ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_firefox_ios_derived__clients_activation__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_firefox_ios_derived__clients_activation__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=57600)).isoformat() }}", - ) - checks__fail_firefox_ios_derived__clients_activation__v1_external.set_upstream( checks__fail_firefox_ios_derived__clients_activation__v1 ) @@ -343,13 +336,6 @@ execution_date="{{ (execution_date - macros.timedelta(seconds=7200)).isoformat() }}", ) - ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=57600)).isoformat() }}", - ) - checks__fail_firefox_ios_derived__firefox_ios_clients__v1_external.set_upstream( checks__fail_firefox_ios_derived__firefox_ios_clients__v1 ) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py --- /tmp/workspace/main-generated-sql/dags/bqetl_glean_usage.py 2024-05-29 13:26:23.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_glean_usage.py 2024-05-29 13:38:17.000000000 +0000 @@ -495,13 +495,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_gud", external_task_id="wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1", @@ -558,13 +551,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_gud", external_task_id="wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1", @@ -621,13 +607,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_gud", external_task_id="wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1", @@ -684,13 +663,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_gud", external_task_id="wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1", @@ -747,13 +719,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_gud", external_task_id="wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1", @@ -778,21 +743,6 @@ task_group=task_group_focus_android, ) - with TaskGroup( - "checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1_external", - parent_group=task_group_focus_android, - ) as checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1_external: - ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1_external.set_upstream( - checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( task_id="checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", source_table="baseline_clients_last_seen_v1", @@ -819,13 +769,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_gud", external_task_id="wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", @@ -850,21 +793,6 @@ task_group=task_group_focus_android, ) - with TaskGroup( - "checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1_external", - parent_group=task_group_focus_android, - ) as checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1_external: - ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1_external.set_upstream( - checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1 - ) - checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1 = bigquery_dq_check( task_id="checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1", source_table="baseline_clients_last_seen_v1", @@ -905,13 +833,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_gud", external_task_id="wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1", @@ -954,13 +875,6 @@ execution_date="{{ (execution_date - macros.timedelta(seconds=7200)).isoformat() }}", ) - ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1_external.set_upstream( checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 ) @@ -997,13 +911,6 @@ execution_date="{{ (execution_date - macros.timedelta(seconds=7200)).isoformat() }}", ) - ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1_external.set_upstream( checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 ) @@ -1034,13 +941,6 @@ ) ExternalTaskMarker( - task_id="bqetl_mobile_engagement_model__wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_mobile_engagement_model", - external_task_id="wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", - execution_date="{{ (execution_date - macros.timedelta(days=-1, seconds=50400)).isoformat() }}", - ) - - ExternalTaskMarker( task_id="bqetl_gud__wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", external_dag_id="bqetl_gud", external_task_id="wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/dags/bqetl_mobile_engagement_model.py /tmp/workspace/generated-sql/dags/bqetl_mobile_engagement_model.py --- /tmp/workspace/main-generated-sql/dags/bqetl_mobile_engagement_model.py 2024-05-29 13:26:23.000000000 +0000 +++ /tmp/workspace/generated-sql/dags/bqetl_mobile_engagement_model.py 1970-01-01 00:00:00.000000000 +0000 @@ -1,321 +0,0 @@ -# Generated via https://github.com/mozilla/bigquery-etl/blob/main/bigquery_etl/query_scheduling/generate_airflow_dags.py - -from airflow import DAG -from airflow.sensors.external_task import ExternalTaskMarker -from airflow.sensors.external_task import ExternalTaskSensor -from airflow.utils.task_group import TaskGroup -import datetime -from operators.gcp_container_operator import GKEPodOperator -from utils.constants import ALLOWED_STATES, FAILED_STATES -from utils.gcp import bigquery_etl_query, bigquery_dq_check - -docs = """ -### bqetl_mobile_engagement_model - -Built from bigquery-etl repo, [`dags/bqetl_mobile_engagement_model.py`](https://github.com/mozilla/bigquery-etl/blob/generated-sql/dags/bqetl_mobile_engagement_model.py) - -#### Description - -Loads the mobile engagement model tables -#### Owner - -kwindau@mozilla.com - -#### Tags - -* impact/tier_2 -* repo/bigquery-etl -""" - - -default_args = { - "owner": "kwindau@mozilla.com", - "start_date": datetime.datetime(2024, 5, 16, 0, 0), - "end_date": None, - "email": ["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - "depends_on_past": False, - "retry_delay": datetime.timedelta(seconds=1800), - "email_on_failure": True, - "email_on_retry": False, - "retries": 2, -} - -tags = ["impact/tier_2", "repo/bigquery-etl"] - -with DAG( - "bqetl_mobile_engagement_model", - default_args=default_args, - schedule_interval="0 12 * * *", - doc_md=docs, - tags=tags, -) as dag: - - wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 = ( - ExternalTaskSensor( - task_id="wait_for_checks__fail_fenix_derived__firefox_android_clients__v1", - external_dag_id="bqetl_analytics_tables", - external_task_id="checks__fail_fenix_derived__firefox_android_clients__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - ) - - wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 = ( - ExternalTaskSensor( - task_id="wait_for_checks__fail_firefox_ios_derived__clients_activation__v1", - external_dag_id="bqetl_firefox_ios", - external_task_id="checks__fail_firefox_ios_derived__clients_activation__v1", - execution_delta=datetime.timedelta(seconds=28800), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - ) - - wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1", - external_dag_id="bqetl_firefox_ios", - external_task_id="checks__fail_firefox_ios_derived__firefox_ios_clients__v1", - execution_delta=datetime.timedelta(seconds=28800), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="fenix.checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="fenix.checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="fenix.checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="fenix.checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="fenix.checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_android.checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="firefox_ios.checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1 = ExternalTaskSensor( - task_id="wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", - external_dag_id="bqetl_glean_usage", - external_task_id="focus_ios.checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1", - execution_delta=datetime.timedelta(seconds=36000), - check_existence=True, - mode="reschedule", - allowed_states=ALLOWED_STATES, - failed_states=FAILED_STATES, - pool="DATA_ENG_EXTERNALTASKSENSOR", - ) - - telemetry_derived__mobile_engagement__v1 = bigquery_etl_query( - task_id="telemetry_derived__mobile_engagement__v1", - destination_table="mobile_engagement_v1", - dataset_id="telemetry_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - - telemetry_derived__mobile_engagement_clients__v1 = bigquery_etl_query( - task_id="telemetry_derived__mobile_engagement_clients__v1", - destination_table="mobile_engagement_clients_v1", - dataset_id="telemetry_derived", - project_id="moz-fx-data-shared-prod", - owner="kwindau@mozilla.com", - email=["kwindau@mozilla.com", "telemetry-alerts@mozilla.com"], - date_partition_parameter="submission_date", - depends_on_past=False, - ) - - telemetry_derived__mobile_engagement__v1.set_upstream( - telemetry_derived__mobile_engagement_clients__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_fenix_derived__firefox_android_clients__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_firefox_ios_derived__clients_activation__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_firefox_ios_derived__firefox_ios_clients__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_fenix_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_fenix_nightly_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_fennec_aurora_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_firefox_beta_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_firefox_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_focus_beta_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_focus_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_focus_nightly_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_ios_fennec_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_ios_firefox_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_ios_firefoxbeta_derived__baseline_clients_last_seen__v1 - ) - - telemetry_derived__mobile_engagement_clients__v1.set_upstream( - wait_for_checks__fail_org_mozilla_ios_focus_derived__baseline_clients_last_seen__v1 - ) Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix: engagement Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix: engagement_clients Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived: engagement_v1 Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios: engagement Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios: engagement_clients Only in /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_ios_derived: engagement_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_engagement: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry/mobile_engagement_clients: schema.yaml Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived: mobile_engagement_clients_v1 Only in /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/telemetry_derived: mobile_engagement_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-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_backend_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:25:12.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-05-28" + DATE(submission_timestamp) >= "2024-05-29" 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/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-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/accounts_frontend_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:25:12.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-05-28" + DATE(submission_timestamp) >= "2024-05-29" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/bedrock_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:25:12.000000000 +0000 @@ -80,7 +80,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-05-28" + DATE(submission_timestamp) >= "2024-05-29" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-05-29 13:22:46.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates/schema.yaml 2024-05-29 13:31:41.000000000 +0000 @@ -1,49 +1,49 @@ fields: -- mode: NULLABLE - name: submission_date +- name: submission_date type: DATE -- mode: NULLABLE - name: source + mode: NULLABLE +- name: source type: STRING -- mode: NULLABLE - name: event_type + mode: NULLABLE +- name: event_type type: STRING -- mode: NULLABLE - name: form_factor + mode: NULLABLE +- name: form_factor type: STRING -- mode: NULLABLE - name: country + mode: NULLABLE +- name: country type: STRING -- mode: NULLABLE - name: subdivision1 + mode: NULLABLE +- name: subdivision1 type: STRING -- mode: NULLABLE - name: advertiser + mode: NULLABLE +- name: advertiser type: STRING -- mode: NULLABLE - name: release_channel + mode: NULLABLE +- name: release_channel type: STRING -- mode: NULLABLE - name: position + mode: NULLABLE +- name: position type: INTEGER -- mode: NULLABLE - name: provider + mode: NULLABLE +- name: provider type: STRING -- mode: NULLABLE - name: match_type + mode: NULLABLE +- name: match_type type: STRING -- mode: NULLABLE - name: normalized_os + mode: NULLABLE +- name: normalized_os type: STRING -- mode: NULLABLE - name: suggest_data_sharing_enabled + mode: NULLABLE +- name: suggest_data_sharing_enabled type: BOOLEAN -- mode: NULLABLE - name: event_count + mode: NULLABLE +- name: event_count type: INTEGER -- mode: NULLABLE - name: user_count + mode: NULLABLE +- name: user_count type: INTEGER -- mode: NULLABLE - name: query_type + mode: NULLABLE +- name: query_type type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-05-29 13:22:46.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/contextual_services/event_aggregates_suggest/schema.yaml 2024-05-29 13:31:40.000000000 +0000 @@ -1,40 +1,40 @@ fields: -- mode: NULLABLE - name: submission_date +- name: submission_date type: DATE -- mode: NULLABLE - name: form_factor + mode: NULLABLE +- name: form_factor type: STRING -- mode: NULLABLE - name: country + mode: NULLABLE +- name: country type: STRING -- mode: NULLABLE - name: advertiser + mode: NULLABLE +- name: advertiser type: STRING -- mode: NULLABLE - name: normalized_os + mode: NULLABLE +- name: normalized_os type: STRING -- mode: NULLABLE - name: release_channel + mode: NULLABLE +- name: release_channel type: STRING -- mode: NULLABLE - name: position + mode: NULLABLE +- name: position type: INTEGER -- mode: NULLABLE - name: provider + mode: NULLABLE +- name: provider type: STRING -- mode: NULLABLE - name: match_type + mode: NULLABLE +- name: match_type type: STRING -- mode: NULLABLE - name: suggest_data_sharing_enabled + mode: NULLABLE +- name: suggest_data_sharing_enabled type: BOOLEAN -- mode: NULLABLE - name: impression_count + mode: NULLABLE +- name: impression_count type: INTEGER -- mode: NULLABLE - name: click_count + mode: NULLABLE +- name: click_count type: INTEGER -- mode: NULLABLE - name: query_type + mode: NULLABLE +- name: query_type type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/debug_ping_view_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/debug_ping_view_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/debug_ping_view_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/debug_ping_view_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:25:12.000000000 +0000 @@ -60,7 +60,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-05-28" + DATE(submission_timestamp) >= "2024-05-29" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/metadata.yaml 2024-05-29 13:35:26.000000000 +0000 @@ -0,0 +1,13 @@ +friendly_name: Engagement +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.fenix_derived.engagement_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/schema.yaml 2024-05-29 13:30:42.000000000 +0000 @@ -0,0 +1,85 @@ +fields: +- name: submission_date + type: DATE + mode: NULLABLE + description: null +- name: first_seen_date + type: DATE + mode: NULLABLE + description: When the profile has been seen for the first time. +- name: normalized_channel + type: STRING + mode: NULLABLE + description: Release channel of the app the profile is using. +- name: app_name + type: STRING + mode: NULLABLE + description: App name the profile is using. +- name: app_version + type: STRING + mode: NULLABLE + description: Client's app version on the metric date. +- name: country + type: STRING + mode: NULLABLE + description: Client's country on the metric date. +- name: locale + type: STRING + mode: NULLABLE + description: Client's locale on the metric date. +- name: is_mobile + type: BOOLEAN + mode: NULLABLE + description: Indicates if this specific entry is used towards calculating mobile + DAU. +- name: adjust_ad_group + type: STRING + mode: NULLABLE + description: Adjust Ad Group the profile is attributed to. +- name: adjust_campaign + type: STRING + mode: NULLABLE + description: Adjust Campaign the profile is attributed to. +- name: adjust_creative + type: STRING + mode: NULLABLE + description: Adjust Creative the profile is attributed to. +- name: adjust_network + type: STRING + mode: NULLABLE + description: Adjust Network the profile is attributed to. +- name: play_store_attribution_campaign + type: STRING + mode: NULLABLE + description: Play store campaign the profile is attributed to. +- name: play_store_attribution_medium + type: STRING + mode: NULLABLE + description: Play store Medium the profile is attributed to. +- name: play_store_attribution_source + type: STRING + mode: NULLABLE + description: Play store source the profile is attributed to. +- name: meta_attribution_app + type: STRING + mode: NULLABLE + description: Facebook app linked to paid marketing. +- name: install_source + type: STRING + mode: NULLABLE + description: The source of a profile installation. +- name: dau + type: INTEGER + mode: NULLABLE + description: DAU - Daily Active Users +- name: wau + type: INTEGER + mode: NULLABLE + description: WAU - Weekly Active Users +- name: mau + type: INTEGER + mode: NULLABLE + description: MAU - Monthly Active Users +- name: lifecycle_stage + type: STRING + mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement/view.sql 2024-05-29 13:25:11.000000000 +0000 @@ -0,0 +1,19 @@ +-- Query generated via `mobile_kpi_support_metrics` SQL generator. +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.fenix.engagement` +AS +SELECT + *, + CASE + WHEN first_seen_date = submission_date + THEN 'new_profile' + WHEN DATE_DIFF(submission_date, first_seen_date, DAY) + BETWEEN 1 + AND 27 + THEN 'repeat_user' + WHEN DATE_DIFF(submission_date, first_seen_date, DAY) >= 28 + THEN 'existing_user' + ELSE 'Unknown' + END AS lifecycle_stage, +FROM + `moz-fx-data-shared-prod.fenix_derived.engagement_v1` diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement_clients/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement_clients/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement_clients/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement_clients/metadata.yaml 2024-05-29 13:35:26.000000000 +0000 @@ -0,0 +1,14 @@ +friendly_name: Engagement Clients +description: |- + Please provide a description for the query +owners: [] +labels: {} +bigquery: null +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + view.sql: + - moz-fx-data-shared-prod.fenix.active_users + - moz-fx-data-shared-prod.fenix_derived.firefox_android_clients_v1 diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement_clients/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement_clients/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement_clients/view.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/engagement_clients/view.sql 2024-05-29 13:25:11.000000000 +0000 @@ -0,0 +1,84 @@ +-- Query generated via `mobile_kpi_support_metrics` SQL generator. +CREATE OR REPLACE VIEW + `moz-fx-data-shared-prod.fenix.engagement_clients` +AS +WITH active_users AS ( + SELECT + submission_date, + client_id, + sample_id, + first_seen_date, + app_name, + normalized_channel, + locale, + country, + isp, + app_display_version, + is_dau, + is_wau, + is_mau, + is_mobile, + FROM + `moz-fx-data-shared-prod.fenix.active_users` +), +attribution AS ( + SELECT + client_id, + sample_id, + channel AS normalized_channel, + adjust_ad_group, + adjust_creative, + adjust_network, + CASE + WHEN adjust_network IN ('Google Organic Search', 'Organic') + THEN '' + ELSE adjust_campaign + END AS adjust_campaign, + play_store_attribution_campaign, + play_store_attribution_medium, + play_store_attribution_source, + meta_attribution_app, + install_source, + FROM + `moz-fx-data-shared-prod.fenix_derived.firefox_android_clients_v1` +) +SELECT + submission_date, + client_id, + sample_id, + first_seen_date, + app_name, + normalized_channel, + app_display_version AS app_version, + locale, + country, + isp, + is_dau, + is_wau, + is_mau, + is_mobile, + attribution.adjust_ad_group, + attribution.adjust_campaign, + attribution.adjust_creative, + attribution.adjust_network, + attribution.play_store_attribution_campaign, + attribution.play_store_attribution_medium, + attribution.play_store_attribution_source, + attribution.meta_attribution_app, + attribution.install_source, + CASE + WHEN active_users.submission_date = first_seen_date + THEN 'new_profile' + WHEN DATE_DIFF(active_users.submission_date, first_seen_date, DAY) + BETWEEN 1 + AND 27 + THEN 'repeat_user' + WHEN DATE_DIFF(active_users.submission_date, first_seen_date, DAY) >= 28 + THEN 'existing_user' + ELSE 'Unknown' + END AS lifecycle_stage, +FROM + active_users +LEFT JOIN + attribution + USING (client_id, sample_id, normalized_channel) diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-05-29 13:22:46.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_clients/schema.yaml 2024-05-29 13:30:44.000000000 +0000 @@ -26,6 +26,9 @@ - name: adjust_network type: STRING mode: NULLABLE +- name: install_source + type: STRING + mode: NULLABLE - name: retained_week_2 type: BOOLEAN mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-05-29 13:22:46.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/funnel_retention_week_4/schema.yaml 2024-05-29 13:30:42.000000000 +0000 @@ -48,6 +48,10 @@ description: 'The type of source of a client installation. ' +- name: install_source + type: STRING + mode: NULLABLE + description: null - name: new_profiles type: INTEGER mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/schema.yaml 2024-05-29 13:28:19.000000000 +0000 @@ -655,6 +655,9 @@ - name: home_bookmarks_show_all_bookmarks type: INTEGER mode: NULLABLE + - name: bounce_tracking_protection_purge_count_classified_tracker + type: INTEGER + mode: NULLABLE - name: custom_distribution type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/view.sql 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/metrics/view.sql 2024-05-29 13:28:18.000000000 +0000 @@ -386,7 +386,8 @@ metrics.counter.hls_canplay_requested, metrics.counter.hls_canplay_supported, metrics.counter.home_bookmarks_bookmark_clicked, - metrics.counter.home_bookmarks_show_all_bookmarks + metrics.counter.home_bookmarks_show_all_bookmarks, + metrics.counter.bounce_tracking_protection_purge_count_classified_tracker ) AS `counter`, STRUCT( metrics.custom_distribution.geckoview_document_site_origins, @@ -1012,7 +1013,8 @@ metrics.counter.hls_canplay_requested, metrics.counter.hls_canplay_supported, metrics.counter.home_bookmarks_bookmark_clicked, - metrics.counter.home_bookmarks_show_all_bookmarks + metrics.counter.home_bookmarks_show_all_bookmarks, + metrics.counter.bounce_tracking_protection_purge_count_classified_tracker ) AS `counter`, STRUCT( metrics.custom_distribution.geckoview_document_site_origins, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix/use_counters/schema.yaml 2024-05-29 13:26:53.000000000 +0000 @@ -7164,6 +7164,12 @@ - name: use_counter_css_page_css_position_anchor type: INTEGER mode: NULLABLE + - name: use_counter_css_doc_css_position_visibility + type: INTEGER + mode: NULLABLE + - name: use_counter_css_page_css_position_visibility + type: INTEGER + mode: NULLABLE - name: labeled_counter type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/checks.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/checks.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/checks.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/checks.sql 2024-05-29 13:25:11.000000000 +0000 @@ -0,0 +1,3 @@ +-- Query generated via `mobile_kpi_support_metrics` SQL generator. +# warn +{{ min_row_count(1), "WHERE submission_date = @submission_date" }} diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/metadata.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/metadata.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/metadata.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/metadata.yaml 2024-05-29 13:35:29.000000000 +0000 @@ -0,0 +1,29 @@ +friendly_name: Profile / Client Engagement - Fenix (Aggregated) +description: |- + Profile / Client Engagement (Fenix) aggregated metrics +owners: +- mozilla/kpi_table_reviewers +- kik@mozilla.com +labels: + schedule: daily + incremental: true + owner1: kik +bigquery: + time_partitioning: + type: day + field: submission_date + require_partition_filter: false + expiration_days: null + range_partitioning: null + clustering: + fields: + - app_name + - country + - first_seen_date +workgroup_access: +- role: roles/bigquery.dataViewer + members: + - workgroup:mozilla-confidential +references: + query.sql: + - moz-fx-data-shared-prod.fenix.engagement_clients diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/query.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/query.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/query.sql 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/query.sql 2024-05-29 13:25:11.000000000 +0000 @@ -0,0 +1,44 @@ +-- Query generated via `mobile_kpi_support_metrics` SQL generator. +SELECT + submission_date, + first_seen_date, + normalized_channel, + app_name, + app_version, + country, + locale, + is_mobile, + adjust_ad_group, + adjust_campaign, + adjust_creative, + adjust_network, + play_store_attribution_campaign, + play_store_attribution_medium, + play_store_attribution_source, + meta_attribution_app, + install_source, + COUNTIF(is_dau) AS dau, + COUNTIF(is_wau) AS wau, + COUNTIF(is_mau) AS mau +FROM + `moz-fx-data-shared-prod.fenix.engagement_clients` +WHERE + submission_date = @submission_date +GROUP BY + submission_date, + first_seen_date, + normalized_channel, + app_name, + app_version, + country, + locale, + is_mobile, + adjust_ad_group, + adjust_campaign, + adjust_creative, + adjust_network, + play_store_attribution_campaign, + play_store_attribution_medium, + play_store_attribution_source, + meta_attribution_app, + install_source diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/schema.yaml 1970-01-01 00:00:00.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/fenix_derived/engagement_v1/schema.yaml 2024-05-29 13:25:11.000000000 +0000 @@ -0,0 +1,100 @@ +fields: +- mode: NULLABLE + name: submission_date + type: DATE + description: + +- mode: NULLABLE + name: first_seen_date + type: DATE + description: When the profile has been seen for the first time. + +- mode: NULLABLE + name: normalized_channel + type: STRING + description: Release channel of the app the profile is using. + +- name: app_name + type: STRING + mode: NULLABLE + description: App name the profile is using. + +- name: app_version + type: STRING + mode: NULLABLE + description: Client's app version on the metric date. + +- name: country + type: STRING + mode: NULLABLE + description: Client's country on the metric date. + +- name: locale + type: STRING + mode: NULLABLE + description: Client's locale on the metric date. + +- name: is_mobile + type: BOOLEAN + mode: NULLABLE + description: Indicates if this specific entry is used towards calculating mobile DAU. + +- name: adjust_ad_group + type: STRING + mode: NULLABLE + description: Adjust Ad Group the profile is attributed to. + +- name: adjust_campaign + type: STRING + mode: NULLABLE + description: Adjust Campaign the profile is attributed to. + +- name: adjust_creative + type: STRING + mode: NULLABLE + description: Adjust Creative the profile is attributed to. + +- name: adjust_network + type: STRING + mode: NULLABLE + description: Adjust Network the profile is attributed to. + +- name: play_store_attribution_campaign + type: STRING + mode: NULLABLE + description: Play store campaign the profile is attributed to. + +- name: play_store_attribution_medium + type: STRING + mode: NULLABLE + description: Play store Medium the profile is attributed to. + +- name: play_store_attribution_source + type: STRING + mode: NULLABLE + description: Play store source the profile is attributed to. + +- name: meta_attribution_app + type: STRING + mode: NULLABLE + description: Facebook app linked to paid marketing. + +- name: install_source + type: STRING + mode: NULLABLE + description: The source of a profile installation. + +- name: dau + type: INTEGER + mode: NULLABLE + description: DAU - Daily Active Users + +- name: wau + type: INTEGER + mode: NULLABLE + description: WAU - Weekly Active Users + +- name: mau + type: INTEGER + mode: NULLABLE + description: MAU - Monthly Active Users diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/metrics/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/metrics/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/metrics/schema.yaml 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/metrics/schema.yaml 2024-05-29 13:24:24.000000000 +0000 @@ -812,6 +812,14 @@ description: 'Record when a canPlayType request supports HLS. ' + - name: bounce_tracking_protection_purge_count_classified_tracker + type: INTEGER + mode: NULLABLE + description: 'Counts sites purged from BTP that are classified as trackers as + part of the following lists: [emailtracking-protection, fingerprinting-protection, + socialtracking-protection, tracking-protection] + + ' - name: labeled_counter type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/use_counters/schema.yaml 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/use_counters/schema.yaml 2024-05-29 13:24:16.000000000 +0000 @@ -17632,6 +17632,22 @@ rate. ' + - name: use_counter_css_doc_css_position_visibility + type: INTEGER + mode: NULLABLE + description: 'Whether a document used the CSS property position-visibility. + Compare against `use.counter.content_documents_destroyed` to calculate the + rate. + + ' + - name: use_counter_css_page_css_position_visibility + type: INTEGER + mode: NULLABLE + description: 'Whether a page used the CSS property position-visibility. Compare + against `use.counter.top_level_content_documents_destroyed` to calculate the + rate. + + ' - name: labeled_counter type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/user_characteristics/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/user_characteristics/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/user_characteristics/schema.yaml 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop/user_characteristics/schema.yaml 2024-05-29 13:24:18.000000000 +0000 @@ -491,6 +491,30 @@ description: 'The CSS system-color Selecteditemtext. ' + - name: characteristics_avail_height + type: INTEGER + mode: NULLABLE + description: 'Available height property + + ' + - name: characteristics_avail_width + type: INTEGER + mode: NULLABLE + description: 'Available width property + + ' + - name: characteristics_orientation_angle + type: INTEGER + mode: NULLABLE + description: 'Orientation angle of the screen + + ' + - name: characteristics_pixel_depth + type: INTEGER + mode: NULLABLE + description: 'Screen pixel depth property + + ' - name: string type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_tasks_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:25:12.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_background_tasks_live.background_tasks_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_live.events_v1` UNION ALL SELECT submission_timestamp, @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_background_tasks_live.events_v1` + `moz-fx-data-shared-prod.firefox_desktop_background_tasks_live.background_tasks_v1` ) CROSS JOIN UNNEST(events) AS event, @@ -70,7 +70,7 @@ LEFT JOIN UNNEST(event.extra) AS event_extra WHERE - DATE(submission_timestamp) >= "2024-05-28" + DATE(submission_timestamp) >= "2024-05-29" GROUP BY submission_date, window_start, diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update/metrics/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update/metrics/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update/metrics/schema.yaml 2024-05-29 13:23:22.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update/metrics/schema.yaml 2024-05-29 13:24:17.000000000 +0000 @@ -629,6 +629,14 @@ description: 'Record when a canPlayType request supports HLS. ' + - name: bounce_tracking_protection_purge_count_classified_tracker + type: INTEGER + mode: NULLABLE + description: 'Counts sites purged from BTP that are classified as trackers as + part of the following lists: [emailtracking-protection, fingerprinting-protection, + socialtracking-protection, tracking-protection] + + ' - name: datetime type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update/use_counters/schema.yaml /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update/use_counters/schema.yaml --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update/use_counters/schema.yaml 2024-05-29 13:23:22.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_background_update/use_counters/schema.yaml 2024-05-29 13:24:22.000000000 +0000 @@ -17632,6 +17632,22 @@ rate. ' + - name: use_counter_css_doc_css_position_visibility + type: INTEGER + mode: NULLABLE + description: 'Whether a document used the CSS property position-visibility. + Compare against `use.counter.content_documents_destroyed` to calculate the + rate. + + ' + - name: use_counter_css_page_css_position_visibility + type: INTEGER + mode: NULLABLE + description: 'Whether a page used the CSS property position-visibility. Compare + against `use.counter.top_level_content_documents_destroyed` to calculate the + rate. + + ' - name: labeled_counter type: RECORD mode: NULLABLE diff -bur --no-dereference --new-file /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql --- /tmp/workspace/main-generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:23:21.000000000 +0000 +++ /tmp/workspace/generated-sql/sql/moz-fx-data-shared-prod/firefox_desktop_derived/event_monitoring_live_v1/materialized_view.sql 2024-05-29 13:25:12.000000000 +0000 @@ -50,7 +50,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.urlbar_potential_exposure_v1` + `moz-fx-data-shared-prod.firefox_desktop_live.events_v1` UNION ALL SELECT submission_timestamp, @@ -60,7 +60,7 @@ client_info.app_display_version AS version, ping_info FROM - `moz-fx-data-shared-prod.firefox_desktop_live.events_v1` + `moz-fx-data-s ```

⚠️ Only part of the diff is displayed.

Link to full diff