Content translation/Public Unified Metrics Dashboard

The public dashboard with various Content Translation metrics currently lives on the public Superset instance at

https://superset.wmcloud.org/superset/dashboard/unified-cx-metrics/.

Introduction

The dashboard features various metrics related to the usage Content (& Section) Translation tool on Wikimedia projects. As the dashboard is public, it can be accessed by anyone with a Wikimedia SUL account, with no further permissions required. The dashboard is organized with various tabs:

(D) - data is updated daily, (M) - data is updated monthly

Monitoring metrics (D): This tab features metrics that are updated daily, to monitor the usage of the Content Translation, for article creation, including published and draft (in-progress) translations. Currently, monitoring metrics include new article creations only (either using CX or SX), but not translations that expansions to already published articles. Please refer to the "Key metrics (M)" tab for metrics related to the latter.
Custom suggestions (D): This tab features metrics that are updated daily, to understand the usage and user interactions with the custom suggestions menu within the Content Translation.
Key metrics (M): This tab features key metrics, that are updated monthly, to understand the usage of Content Translation. The filters on the left side can be used to further understand usage by namespaces, devices, creation vs. expansion, user experience levels, among others. As the data is updated monthly, the data period ends on the last day of the previous month.
Deletion stats (M): This tab features metrics that are updated monthly, to explore the deletion statistics related to articles translated using either of the Content or the Section Translation tools, along with a comparative view of articles created without using these tools. As the data is updated monthly, the data period ends on the last day of the previous month.
MT service availability (M): This tab provides an overview of various machine translation services available, and the language pairs they support.
MT service usage (M): This tab features metrics that are updated monthly, related to the usage of various machine translation services within the Content Translation.

Usually, daily stats are updated at 5 am UTC, and monthly stats on the 5th day of each month, which includes the data until the last day of the previous month. The aggregated datasets are also published at https://analytics.wikimedia.org/published/datasets/content_translation/.

Technical aspects

The dashboard is hosted on the public Superset instance of Wikimedia, maintained by the Wikimedia Cloud Services team. The working of the dashboard involves multiple extract, transform, load pipeline, where the data is pre-aggregated to required granularity (daily or monthly), and loaded to a database to which public Superset instance is connected to.

At a high-level, at core are the Airflow DAGs, which aggregate the data from the required sources, save a snapshot internally, and also a publish a public snapshot to https://analytics.wikimedia.org/published/datasets/content_translation/ at the specified cadence. These public flat files are then picked up by Toolforge jobs, and load them to ToolsDB, which is accessed by the public Superset instance.

Primary code repos

product-analytics/data-pipelines/content_translation: contains queries required to tables in Data Lake, aggregation queries to be used by Airflow, queries to create TSV flat files and purge older snapshots.
product-analytics/content-translation-airflow-jobs: a Python job repo that helps Airflow to fetch and load data from MariaDB-replicas to Data Lake, to be used for later aggregatations, and also run scripts for certain aggregations where SQL is solely not enough (for example, MT service availability).
airflow-dags/analytics_product/content_translations: Airflow DAGs that execute specified queries/scripts at the specific cadence, along with managing the required dataset dependencies needed to execute. Usually, each DAG script writes a snapshot to Data Lake under wmf_product database, publishes a TSV, and purges older snapshots.
product-analytics/content-translation-toolforge-jobs: contains job scripts to fetch public flat files, and load them to ToolsDB, under s56759__unified_cx_metrics_p database, which is connected to the public Superset instance.

Data pipelines

Load from MariaDB-replicas to Data Lake

cx_translators_load_daily
Description Loads the cx_translators data from MariaDB-replicas to Data Lake. Cadence daily Source data `cx_translators` Data lake destination `wmf_product.cx_translators` Python job repo script import_cx_translators.py Processing queries load_cx_translators_daily.sql Airflow DAG cx_translators_daily_dag.py

cx_translations_load_daily
Description Loads the cx_translations data from MariaDB-replicas to Data Lake. Cadence daily Source data `cx_translations` Data lake destination `wmf_product.cx_translations` Python job repo script import_cx_translations.py Processing queries load_cx_translations_table.sql Airflow DAG cx_translations_daily_dag.py

cx_corpora_load_monthly
Description Loads the cx_corpora data from MariaDB-replicas to Data Lake. Cadence monthly Source data `cx_corpora` Data lake destination `wmf_product.cx_corpora` Python job repo script import_cx_corpora.py Processing queries load_cx_corpora_daily.sql Airflow DAG cx_corpora_monthly_dag.py

Daily aggregations

cx_monitoring_metrics_daily

Description: Aggregates daily monitoring metrics related to published and draft translations using the Content Translation.
Cadence: daily
Source data: wmf_product.cx_translations
Data lake destination

Python job repo script: n/a
Processing queries

draft (in-progress translations)

published translations

Airflow DAG: cx_daily_monitoring_metrics_dag.py
Public output: https://analytics.wikimedia.org/published/datasets/content_translation/daily_monitoring_metrics/
Toolforge job

Toolforge tables

cx_suggestions_menu_interactions_daily

Description: Aggregates the metrics related to user interactions with Content Translation suggestions menu, daily.
Cadence: daily
Source data

Data lake destination: wmf_product.cx_suggestions_menu_interactions_daily
Python job repo script: n/a
Processing queries

Airflow DAG: cx_suggestions_menu_interactions_daily_dag.py
Public output: https://analytics.wikimedia.org/published/datasets/content_translation/daily_monitoring_metrics/
Toolforge job: update_cx_suggestions_menu_interactions_daily.py
Toolforge table: cx_suggestions_menu_interactions_daily

Monthly aggregations

cx_key_metrics_monthly

Description: Generates monthly key metrics related to articles translated using the Content Translation and the Section Translation tools.
Cadence: monthly
Source data

Data lake destination: wmf_product.cx_key_metrics_monthly
Python job repo script: n/a
Processing queries

Airflow DAG: cx_key_metrics_monthly_dag.py
Public output: https://analytics.wikimedia.org/published/datasets/content_translation/monthly_metrics/
Toolforge job: update_cx_key_metrics_monthly.py
Toolforge table: cx_key_metrics_monthly

cx_deletion_stats_monthly

Description: Generates monthly deletion statistics related to articles translated using the Content Translation and the Section Translation tools.
Cadence: monthly
Source data

Data lake destination: wmf_product.cx_deletion_stats_monthly
Python job repo script: n/a
Processing queries

Airflow DAG: cx_deletion_stats_monthly_dag.py
Public output: https://analytics.wikimedia.org/published/datasets/content_translation/monthly_metrics/
Toolforge job: update_cx_deletion_stats_monthly.py
Toolforge table: cx_deletion_stats_monthly

cx_mt_service_availability_monthly

Description: Processes and update machine translation service availability between language pairs.
Cadence: monthly
Source data

wmf_product.cx_translations
https://cxserver.wikimedia.org/v1/list/mt

Data lake destination: wmf_product.cx_mt_service_availability
Python job repo script: process_cx_mt_service_availability.py
Processing queries: publish_cx_mt_service_availability.hql
Airflow DAG: cx_mt_service_availability_monthly_dag.py
Public output: https://analytics.wikimedia.org/published/datasets/content_translation/monthly_metrics/
Toolforge job: update_cx_mt_service_availability.py
Toolforge table: cx_mt_service_availability

cx_mt_service_usage_monthly

Description: Calculates metrics related to machine translation service usage between languages in the Content Translation.
Cadence: monthly
Source data

wmf_product.cx_translations
wmf_product.cx_corpora
wmf_product.cx_mt_service_availability

Data lake destination: wmf_product.cx_mt_service_usage_monthly
Python job repo script: n/a
Processing queries

Airflow DAG: cx_mt_service_usage_monthly_dag.py
Public output: https://analytics.wikimedia.org/published/datasets/content_translation/monthly_metrics/
Toolforge job: update_cx_mt_service_usage_monthly.py
Toolforge table: create_cx_mt_service_availability.sql

cx_mt_default_service_comparison_monthly

Description: Calculates metrics that compare usage of default vs. optional services in the Content Translation.
Cadence: monthly
Source data: wmf_product.cx_mt_service_usage_monthly
Data lake destination: wmf_product.cx_mt_default_service_comparison_monthly
Python job repo script: calculate_cx_mt_default_service_comparison.py
Processing queries

publish_cx_mt_default_service_comparison_monthly.hql

Airflow DAG: cx_mt_default_service_comparison_monthly_dag.py
Public output: https://analytics.wikimedia.org/published/datasets/content_translation/monthly_metrics/
Toolforge job: update_cx_mt_default_service_comparison_monthly.py
Toolforge table: cx_mt_default_service_comparison_monthly

Maintenance

The data pipelines don't specifically require any maintenance. If the Airflow DAGs fail or don't complete execution within the specified SLA duration, an automated alert will be triggered to product-analytics+alertswikimedia.org, and if the Toolforge job fails, alert emails will be sent to all listed maintainers of the tool.

The ToolsDB database for the dashboard is created under the tool, unified-cx-metrics-db.

Tool admin panel: https://toolsadmin.wikimedia.org/tools/id/unified-cx-metrics-db
Currently listed maintainers: KCVelaga (WMF)

Toolforge monthly jobs are set to run on the 5th day of the each month. For any reason, if Airflow pipelines get delayed (for instance, due to delay in mediawiki_history snapshot - even though the Toolforge job will run without any error, it will actually only update the ToolsDB database with the public snapshot available, which would be a month old. In this case, a manual update will be required using the following steps:

ssh login.toolforge.org
become unified-cx-metrics-db
cd www/python
source venv/bin/activate
cd src/scripts/python/
python update_cx_mt_service_availability.py
exit
exit