Add metrics: monthly casual users and active users (#936)

rosalieper · deer-wmde · deer-wmde · commit c93c9fbfd266 · 2025-12-15T18:41:49.000+01:00
* add DB migration

* Add metrics monthly casual users and active users

* add new metrics to WikiDailyMetrics::$metricNames as well

* fix migration reversal code

* update changelog

---------

Co-authored-by: dena &lt;dena@wikimedia.de&gt;
diff --git a/CHANGELOG.md b/CHANGELOG.md
@@ -1,5 +1,8 @@
 # api
 
+## 10x.19.6
+- Add metrics, monthly casual users and active users
+
 ## 10x.19.5
 - Add POST endpoint for wiki profiles
 
diff --git a/app/Metrics/App/WikiMetrics.php b/app/Metrics/App/WikiMetrics.php
@@ -33,6 +33,8 @@ public function saveMetrics(Wiki $wiki): void
         $monthlyActions = $this->getNumberOfActions(self::INTERVAL_MONTHLY);
         $quarterlyActions = $this->getNumberOfActions(self::INTERVAL_QUARTERLY);
 
+        $monthlyNumberOfUsersPerActivityType = $this->getNumberOfUsersPerActivityType();
+
         $dailyMetrics = new WikiDailyMetrics([
             'id' => $wiki->id . '_' . date('Y-m-d'),
             'pages' => $todayPageCount,
@@ -44,6 +46,8 @@ public function saveMetrics(Wiki $wiki): void
             'weekly_actions' => $weeklyActions,
             'monthly_actions' => $monthlyActions,
             'quarterly_actions' => $quarterlyActions,
+            'monthly_casual_users' => $monthlyNumberOfUsersPerActivityType[0],
+            'monthly_active_users' => $monthlyNumberOfUsersPerActivityType[1],
         ]);
 
         // compare current record to old record and only save if there is a change
@@ -130,4 +134,41 @@ protected function getNumberOfActions(string $interval): null|int
 
         return $actions;
     }
+
+    private function getNumberOfUsersPerActivityType() : array
+    {
+        $wikiDb = $this->wiki->wikiDb;
+        $tableRecentChanges = $wikiDb->name . '.' . $wikiDb->prefix . '_recentchanges';
+        $tableActor = $wikiDb->name . '.' . $wikiDb->prefix . '_actor';
+        $query = "SELECT
+            COUNT(CASE WHEN activity_count >= 1 AND activity_count < 5 THEN 1 END) AS monthly_casual_users,
+            COUNT(CASE WHEN activity_count >= 5 THEN 1 END) AS monthly_active_users
+        FROM (
+            SELECT
+                rc.rc_actor,
+                COUNT(*) AS activity_count
+            FROM
+                $tableRecentChanges AS rc
+                INNER JOIN $tableActor AS a ON rc.rc_actor = a.actor_id
+	        WHERE rc.rc_timestamp >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y%m%d%H%i%S') -- monthly
+		        /*
+		        Conditions below added for consistency with Wikidata: https://phabricator.wikimedia.org/diffusion/ADES/browse/master/src/wikidata/site_stats/sql/active_user_changes.sql
+		        */
+    	       AND a.actor_user != 0
+    	       AND rc.rc_bot = 0
+    	       AND (rc.rc_log_type != 'newusers' OR rc.rc_log_type IS NULL)
+               GROUP BY rc.rc_actor
+        ) AS actor_activity";
+
+        $manager = app()->db;
+        $manager->purge('mw');
+        $conn = $manager->connection('mw');
+        $pdo = $conn->getPdo();
+        $result = $pdo->query($query)->fetch();
+
+        return [
+            Arr::get($result, 'monthly_casual_users', null),
+            Arr::get($result, 'monthly_active_users',null)
+        ];
+    }
 }
diff --git a/app/WikiDailyMetrics.php b/app/WikiDailyMetrics.php
@@ -28,6 +28,8 @@ class WikiDailyMetrics extends Model
         'monthly_actions',
         'quarterly_actions',
         'number_of_triples',
+        'monthly_casual_users',
+        'monthly_active_users',
 
     ];
 
@@ -40,6 +42,8 @@ class WikiDailyMetrics extends Model
         'monthly_actions',
         'quarterly_actions',
         'number_of_triples',
+        'monthly_casual_users',
+        'monthly_active_users',
     ];
 
     public function areMetricsEqual(WikiDailyMetrics $wikiDailyMetrics): bool
diff --git a/database/migrations/2025_05_19_142457_add_monthly_user_count_to_wiki_daily_metrics_table.php b/database/migrations/2025_05_19_142457_add_monthly_user_count_to_wiki_daily_metrics_table.php
@@ -0,0 +1,30 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+    /**
+     * Run the migrations.
+     */
+    public function up(): void
+    {
+        Schema::table('wiki_daily_metrics', function (Blueprint $table) {
+            $table->integer('monthly_casual_users')->nullable();
+            $table->integer('monthly_active_users')->nullable();
+        });
+    }
+
+    /**
+     * Reverse the migrations.
+     */
+    public function down(): void
+    {
+        Schema::table('wiki_daily_metrics', function (Blueprint $table) {
+            $table->dropColumn('monthly_casual_users');
+            $table->dropColumn('monthly_active_users');
+        });
+    }
+};
