Create ETL pipelines for Automoderator baseline metrics
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	KCVelaga_WMF
	Apr 16 2024, 8:10 AM

Description

Several baselines metrics related to Automoderator can substantially vary based on the time of the query (for example, number of pending changes to review can be 100 or 1000). Data processing pipelines (Airflow or cron) to be created for the following:

Vandalism pageviews (frequency: weekly/daily)
Flagged revisions backlog (frequency: hourly)
Recent changes patrolling activity (frequency: daily)
- unpatrolled
- patrolled

Details

Related Changes in Gerrit:

	Subject	Repo	Branch	Lines +/-
	Add WMF data pipelines (git submodule) & scripts for regular runs	analytics/wmf-product/jobs	master	+44 -0

Customize query in gerrit

Related Changes in GitLab:

Title	Reference	Author	Source Branch	Dest Branch
DAGs to calculate metrics related to recentchanges patrolling (analytics_product)	repos/data-engineering/airflow-dags!825	kcvelaga	recentchanges_daily_dags	main
Initial Product Analytics moderation job (frevs)	repos/data-engineering/airflow-dags!770	kcvelaga	moderation-job-artifact-1	main
Moderation queries	repos/product-analytics/data-pipelines!15	kcvelaga	moderation_queries	main

Customize query in GitLab

Related Objects
Search...

Status	Assigned	Task
Resolved	KCVelaga_WMF	T362612 Create ETL pipelines for Automoderator baseline metrics
Resolved	KCVelaga_WMF	T362615 ETL pipeline for flaggedrevs metrics (pending frevs hourly)
Resolved	KCVelaga_WMF	T367016 ETL pipeline for unpatrolled recentchanges daily activity
Resolved	• JEbe-WMF	T367953 ETL pipeline for Pageviews Received to Potentially Vandalised Content
Resolved	KCVelaga_WMF	T368352 ETL pipeline for patrolled recentchanges daily activity

Event Timeline

KCVelaga_WMF created this task.Apr 16 2024, 8:10 AM

Restricted Application added a project: Moderator-Tools-Team. · View Herald TranscriptApr 16 2024, 8:10 AM

KCVelaga_WMF moved this task from Triage to Current Quarter on the Product-Analytics board.Apr 16 2024, 8:10 AM

Samwalton9-WMF moved this task from Inbox to Product backlog on the Moderator-Tools-Team board.Apr 16 2024, 8:13 AM

Samwalton9-WMF moved this task from Backlog to Data/Research on the Automoderator board.Apr 17 2024, 9:00 AM

KCVelaga_WMF changed the status of subtask T362615: ETL pipeline for flaggedrevs metrics (pending frevs hourly) from Open to In Progress.Jun 10 2024, 5:23 AM

KCVelaga_WMF changed the task status from Open to In Progress.Jun 25 2024, 5:05 AM

KCVelaga_WMF updated the task description. (Show Details)

KCVelaga_WMF changed the status of subtask T367016: ETL pipeline for unpatrolled recentchanges daily activity from Open to In Progress.

KCVelaga_WMF changed the status of subtask T368352: ETL pipeline for patrolled recentchanges daily activity from Open to In Progress.Jun 25 2024, 5:10 AM

Change #1054903 had a related patch set uploaded (by Bearloga; author: KCVelaga):

[analytics/wmf-product/jobs@master] Add WMF data pipelines (git submodule) & scripts for regular runs

https://gerrit.wikimedia.org/r/1054903

Change #1054903 merged by Bearloga:

[analytics/wmf-product/jobs@master] Add WMF data pipelines (git submodule) & scripts for regular runs

https://gerrit.wikimedia.org/r/1054903

mpopov mentioned this in rAWPJcafc4516d762: Add WMF data pipelines (git submodule) & scripts for regular runs.Jul 21 2024, 1:04 PM

Maintenance_bot removed a project: Patch-For-Review.Jul 21 2024, 1:30 PM

kcvelaga opened https://gitlab.wikimedia.org/repos/product-analytics/data-pipelines/-/merge_requests/15

Moderation queries

bearloga merged https://gitlab.wikimedia.org/repos/product-analytics/data-pipelines/-/merge_requests/15

Moderation queries

Maintenance_bot removed a project: Patch-For-Review.Jul 24 2024, 1:31 PM

kcvelaga opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/770

moderation jobs conda 0.1

KCVelaga_WMF triaged this task as Medium priority.Jul 31 2024, 11:54 AM

Iflorez mentioned this in T365404: Create a plan for moving campaigns-product metrics data to airflow.Aug 2 2024, 5:50 PM

Mentioned in SAL (#wikimedia-analytics) [2024-08-14T17:18:10Z] <ottomata> scap deploy airflow analytics_product for vandalism_pageviews_dag - T362612

KCVelaga_WMF closed subtask T367953: ETL pipeline for Pageviews Received to Potentially Vandalised Content as Resolved.Aug 20 2024, 12:05 PM

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/770

Initial Product Analytics moderation job (frevs)

Iflorez mentioned this in T374491: Create ETL pipelines for campaigns-product baseline metrics.Sep 10 2024, 10:50 PM

Maintenance_bot removed a project: Patch-For-Review.Sep 10 2024, 11:30 PM

KCVelaga_WMF closed subtask T362615: ETL pipeline for flaggedrevs metrics (pending frevs hourly) as Resolved.Sep 11 2024, 1:41 PM

kcvelaga opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/825

DAGs to calculate metrics related to recentchanges patrolling (analytics_product)

jebe merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/825

DAGs to calculate metrics related to recentchanges patrolling (analytics_product)

Maintenance_bot removed a project: Patch-For-Review.Sep 12 2024, 4:31 PM

KCVelaga_WMF closed subtask T367016: ETL pipeline for unpatrolled recentchanges daily activity as Resolved.Sep 13 2024, 7:00 AM

KCVelaga_WMF closed subtask T368352: ETL pipeline for patrolled recentchanges daily activity as Resolved.

All the pipelines are successfully running!

Primary job repo: moderation-mariadb-jobs

flagged revisions pending hourly

Calculates pending flagged revisions to be reviewed, by hour and wiki.

Script: flagged_revisions_pending_hourly.py
Destination table schema: create_moderation_flagged_revisions_pending_hourly.hql
Calculation query: generate_moderation_flagged_revisions_pending_hourly.sql
Destination table: wmf_product.moderation_flagged_revisions_pending_hourly
Package version: moderation-mariadb-jobs-0.4.0-v0.4.0.conda.tgz
DAG: flagged_revisions_pending_hourly_dag.py

unpatrolled recentchanges daily

Calculates pending unpatrolled recent changes daily, by wiki.

Script: unpatrolled_recentchanges_daily.py
Destination table schema: create_moderation_unpatrolled_recentchanges_daily.hql
Calculation query: generate_moderation_unpatrolled_recentchanges_daily.sql
Destination table: wmf_product.moderation_unpatrolled_recentchanges_daily
Package version: moderation-mariadb-jobs-0.5.0-v0.5.0.conda.tgz
DAG: unpatrolled_recentchanges_daily_dag.py

patrolled recentchanges daily

Calculates patrolled recent changes daily, by wiki.

Script: patrolled_recentchanges_daily.py
Destination table schema: create_moderation_patrolled_recentchanges_daily.hql
Calculation query: generate_moderation_patrolled_recentchanges_daily.sql
Destination table: wmf_product.moderation_patrolled_recentchanges_daily
Package version: moderation-mariadb-jobs-0.5.0-v0.5.0.conda.tgz
DAG: patrolled_recentchanges_daily_dag.py

vandalism pageviews monthly

Calculates pageviews of vandalized pages monthly, by wiki.

DAG: vandalism_pageviews_report_dag.py
Table creation file: create_moderation_vandal_pageviews_monthly.hql
Monthly generation file: generate_moderation_vandal_pageviews_monthly.hql

Create ETL pipelines for Automoderator baseline metricsClosed, ResolvedPublicActions