Page MenuHomePhabricator
Create Task
Maniphest T286903

Track and monitor items per editor ratio for Wikidata
Closed, ResolvedPublic3 Estimated Story Points
Actions

Description

Context:
Wikidata Analytics

User story:
As Wikidata PMs we want to get a better understanding of the relationship of Items and editors over time to inform our product strategy.

Problem:
We monitor the number of total Items and the number of active editors per day already (internal). We currently do not have a Graphana board and we do not monitor the number of active Items. The number of active Items is used as an indicator for potentially vandalized Items here.

Acceptance criteria:

  • Track number of active items (the number of items that were touched at least once in the last 30 days) per day
  • Create Graphana dashboard illustrating the following:
    • the number of Items (tracked already)
    • the number of active Items
    • the number of active editors (tracked already)
    • the proportion of items / active editors (with the three definitions of 1, 5, and 100 edits per editor)
    • the proportion of active items / active editors (with the three definitions of 1, 5, and 100 edits per editor)

Open questions:

  • Could we easily track this for segments of Wikidata?
    • Yes, but would be inefficient, so let's not do this for now.
  • Where should we put this?

Origin:
Community request

Tech note:
Take a look at this Gerrit repo https://gerrit.wikimedia.org/r/admin/repos/analytics/wmde/scripts (on github)
Docs at https://wikitech.wikimedia.org/wiki/WMDE/Analytics#analytics/wmde/scripts_repo too

Details

SubjectRepoBranchLines +/-
Make active_items.php executableanalytics/wmde/scriptsproduction+0 -0
Make active_items.php executableanalytics/wmde/scriptsmaster+0 -0
Add active_items.php to daily.03.shanalytics/wmde/scriptsproduction+1 -0
Add active_items.php to daily.03.shanalytics/wmde/scriptsmaster+1 -0
Add comment to active_items.sqlanalytics/wmde/scriptsproduction+1 -1
Add comment to active_items.sqlanalytics/wmde/scriptsmaster+1 -1
Track number of active itemsanalytics/wmde/scriptsproduction+41 -0
Track number of active itemsanalytics/wmde/scriptsmaster+41 -0
Customize query in gerrit

Related Objects

Event Timeline

Manuel created this task.Jul 19 2021, 12:00 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 19 2021, 12:00 PM
Manuel updated the task description. (Show Details)Jul 19 2021, 12:02 PM
Manuel updated the task description. (Show Details)Jul 19 2021, 12:07 PM
Manuel renamed this task from Track and monitor active items on Wikidata to Track and monitor editors per item ratio for Wikidata.Jul 19 2021, 12:20 PM
Manuel updated the task description. (Show Details)
Manuel updated the task description. (Show Details)
Lydia_Pintscher renamed this task from Track and monitor editors per item ratio for Wikidata to Track and monitor active items on Wikidata.Jul 19 2021, 12:20 PM
Lydia_Pintscher updated the task description. (Show Details)
Lydia_Pintscher renamed this task from Track and monitor active items on Wikidata to Track and monitor editors per item ratio for Wikidata.Jul 19 2021, 12:24 PM
Lydia_Pintscher updated the task description. (Show Details)
Lydia_Pintscher updated the task description. (Show Details)
Manuel updated the task description. (Show Details)Jul 19 2021, 12:36 PM
Manuel updated the task description. (Show Details)
Manuel updated the task description. (Show Details)Jul 19 2021, 12:39 PM
Maintenance_bot added a project: Wikidata.Jul 19 2021, 12:45 PM
Esc3300 subscribed.Jul 19 2021, 12:51 PM

That is a good question. Wikidata:Main_Page/Popular might be the one thing that is somewhat close, though it mostly highlights bicycle racing.

Ainali awarded a token.Jul 19 2021, 3:36 PM
So9q subscribed.Jul 20 2021, 4:43 AM
Manuel renamed this task from Track and monitor editors per item ratio for Wikidata to Track and monitor items per editor ratio for Wikidata.Aug 5 2021, 7:54 AM
Manuel added a project: Wikidata-Campsite.
Manuel moved this task from Incoming to Waiting tasks on the Wikidata Analytics board.Aug 5 2021, 7:59 AM
Manuel moved this task from Waiting tasks to Monitoring on the Wikidata Analytics board.Aug 5 2021, 8:08 AM
Lucas_Werkmeister_WMDE subscribed.Aug 10 2021, 8:24 AM

Should be a straightforward query:

MariaDB [wikidatawiki]> SELECT COUNT(DISTINCT rc_title) FROM recentchanges WHERE rc_namespace = 0;
+--------------------------+
| COUNT(DISTINCT rc_title) |
+--------------------------+
|                  9493417 |
+--------------------------+
1 row in set (27.620 sec)
Manuel updated the task description. (Show Details)Aug 10 2021, 8:30 AM
Manuel updated the task description. (Show Details)Aug 10 2021, 8:35 AM
Manuel updated the task description. (Show Details)
Manuel moved this task from Incoming to Unconnected Stories on the Wikidata-Campsite board.Aug 10 2021, 9:05 AM
Manuel moved this task from Monitoring to Needs PM work on the Wikidata Analytics board.Aug 11 2021, 8:03 AM
Addshore updated the task description. (Show Details)Aug 11 2021, 10:20 AM
Michael subscribed.Aug 11 2021, 10:21 AM
Addshore updated the task description. (Show Details)Aug 11 2021, 10:21 AM
Addshore updated the task description. (Show Details)Aug 11 2021, 10:24 AM
Addshore updated the task description. (Show Details)
Addshore set the point value for this task to 3.
Addshore moved this task from Unconnected Stories to Wikidata-Campsite-Iteration-∞ (On Hold) on the Wikidata-Campsite board.Aug 11 2021, 10:52 AM
Addshore edited projects, added Wikidata-Campsite (Wikidata-Campsite-Iteration-∞ (On Hold)); removed Wikidata-Campsite.
Lucas_Werkmeister_WMDE added a comment.Aug 16 2021, 3:30 PM

I tried out queries for counting active items for different thresholds (1, 5, 100), and the result seems interesting:

MariaDB [wikidatawiki]> SELECT COUNT(*) FROM (SELECT rc_title FROM recentchanges WHERE rc_namespace = 0 GROUP BY rc_title HAVING COUNT(*) >= 1) x;
+----------+
| COUNT(*) |
+----------+
|  9673250 |
+----------+
1 row in set (15.703 sec)
MariaDB [wikidatawiki]> SELECT COUNT(*) FROM (SELECT rc_title FROM recentchanges WHERE rc_namespace = 0 GROUP BY rc_title HAVING COUNT(*) >= 5) x;
+----------+
| COUNT(*) |
+----------+
|   438827 |
+----------+
1 row in set (9.089 sec)

MariaDB [wikidatawiki]> SELECT COUNT(*) FROM (SELECT rc_title FROM recentchanges WHERE rc_namespace = 0 GROUP BY rc_title HAVING COUNT(*) >= 100) x;
+----------+
| COUNT(*) |
+----------+
|      416 |
+----------+
1 row in set (9.182 sec)

Some 9.7 million distinct items are edited in 30 days, as we saw above; but only 439k of them see 5 or more edits, and only 416 of them 100 or more edits. We actually have more highly active users than highly active items, by that measure.

Is that worth tracking, or should we limit it to just the active items regardless of their number of edits?

Lucas_Werkmeister_WMDE claimed this task.Aug 17 2021, 12:13 PM
Lucas_Werkmeister_WMDE moved this task from To Do (prioritised from top to bottom) to Doing on the Wikidata-Campsite (Wikidata-Campsite-Iteration-∞ (On Hold)) board.
gerritbot added a comment.Aug 17 2021, 1:08 PM

Change 713464 had a related patch set uploaded (by Lucas Werkmeister (WMDE); author: Lucas Werkmeister (WMDE)):

[analytics/wmde/scripts@master] Track number of active items

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

gerritbot added a project: Patch-For-Review.Aug 17 2021, 1:08 PM
Lucas_Werkmeister_WMDE moved this task from Doing to Peer Review on the Wikidata-Campsite (Wikidata-Campsite-Iteration-∞ (On Hold)) board.Aug 17 2021, 1:08 PM
Manuel added a comment.Aug 18 2021, 12:02 PM

@Lucas_Werkmeister_WMDE thank you for pointing this out! Could you please provide me with the list of Items in the ">= 100" group? This would be helpful to better understand what kind of content this is.

gerritbot added a comment.Aug 18 2021, 12:46 PM

Change 713464 merged by jenkins-bot:

[analytics/wmde/scripts@master] Track number of active items

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

Lucas_Werkmeister_WMDE mentioned this in rADES6ff9d97d2279: Track number of active items.Aug 18 2021, 12:47 PM
Lucas_Werkmeister_WMDE added a comment.Aug 18 2021, 12:54 PM

Sure, I put them at P17044 with their English labels. Almost half are COVID-related.

Maintenance_bot removed a project: Patch-For-Review.Aug 18 2021, 1:10 PM
gerritbot added a comment.Aug 19 2021, 12:17 PM

Change 713850 had a related patch set uploaded (by Lucas Werkmeister (WMDE); author: Lucas Werkmeister (WMDE)):

[analytics/wmde/scripts@production] Track number of active items

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

gerritbot added a project: Patch-For-Review.Aug 19 2021, 12:17 PM
gerritbot added a comment.Aug 19 2021, 4:12 PM

Change 713897 had a related patch set uploaded (by Lucas Werkmeister (WMDE); author: Lucas Werkmeister (WMDE)):

[analytics/wmde/scripts@master] Add comment to active_items.sql

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

Ladsgroup moved this task from Peer Review to Doing on the Wikidata-Campsite (Wikidata-Campsite-Iteration-∞ (On Hold)) board.Aug 23 2021, 12:15 PM
Ladsgroup subscribed.

I assume creating the graph is left now.

gerritbot added a comment.Aug 23 2021, 12:16 PM

Change 713850 merged by jenkins-bot:

[analytics/wmde/scripts@production] Track number of active items

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

gerritbot added a comment.Aug 23 2021, 12:16 PM

Change 713897 merged by jenkins-bot:

[analytics/wmde/scripts@master] Add comment to active_items.sql

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

gerritbot added a comment.Aug 23 2021, 12:16 PM

Change 714153 had a related patch set uploaded (by Ladsgroup; author: Lucas Werkmeister (WMDE)):

[analytics/wmde/scripts@production] Add comment to active_items.sql

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

gerritbot added a comment.Aug 23 2021, 12:17 PM

Change 714153 merged by jenkins-bot:

[analytics/wmde/scripts@production] Add comment to active_items.sql

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

Ladsgroup mentioned this in rADESc9e346ea6bde: Add comment to active_items.sql.Aug 23 2021, 12:28 PM
Lucas_Werkmeister_WMDE mentioned this in rADESc79495d151db: Track number of active items.Aug 23 2021, 12:28 PM
Lucas_Werkmeister_WMDE mentioned this in rADESed656fcbcd3e: Add comment to active_items.sql.
Maintenance_bot removed a project: Patch-For-Review.Aug 23 2021, 1:10 PM
gerritbot added a comment.Aug 25 2021, 4:08 PM

Change 714803 had a related patch set uploaded (by Lucas Werkmeister (WMDE); author: Lucas Werkmeister (WMDE)):

[analytics/wmde/scripts@master] Add active_items.php to daily.03.sh

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

gerritbot added a project: Patch-For-Review.Aug 25 2021, 4:08 PM
Lucas_Werkmeister_WMDE moved this task from Doing to Peer Review on the Wikidata-Campsite (Wikidata-Campsite-Iteration-∞ (On Hold)) board.Aug 25 2021, 4:09 PM
gerritbot added a comment.Aug 25 2021, 5:38 PM

Change 714680 had a related patch set uploaded (by Ladsgroup; author: Lucas Werkmeister (WMDE)):

[analytics/wmde/scripts@production] Add active_items.php to daily.03.sh

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

gerritbot added a comment.Aug 25 2021, 5:39 PM

Change 714803 merged by jenkins-bot:

[analytics/wmde/scripts@master] Add active_items.php to daily.03.sh

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

gerritbot added a comment.Aug 25 2021, 5:39 PM

Change 714680 merged by jenkins-bot:

[analytics/wmde/scripts@production] Add active_items.php to daily.03.sh

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

Lucas_Werkmeister_WMDE mentioned this in rADESbea9d8adc55b: Add active_items.php to daily.03.sh.Aug 25 2021, 5:40 PM
Ladsgroup mentioned this in rADESc8a5269b4646: Add active_items.php to daily.03.sh.Aug 25 2021, 5:40 PM
Lucas_Werkmeister_WMDE moved this task from Peer Review to Doing on the Wikidata-Campsite (Wikidata-Campsite-Iteration-∞ (On Hold)) board.Aug 25 2021, 5:41 PM
gerritbot added a comment.Aug 26 2021, 1:10 PM

Change 715013 had a related patch set uploaded (by Lucas Werkmeister (WMDE); author: Lucas Werkmeister (WMDE)):

[analytics/wmde/scripts@master] Make active_items.php executable

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

Lucas_Werkmeister_WMDE moved this task from Doing to Peer Review on the Wikidata-Campsite (Wikidata-Campsite-Iteration-∞ (On Hold)) board.Aug 26 2021, 1:11 PM
gerritbot added a comment.Aug 26 2021, 1:13 PM

Change 715013 merged by jenkins-bot:

[analytics/wmde/scripts@master] Make active_items.php executable

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

gerritbot added a comment.Aug 26 2021, 1:13 PM

Change 714856 had a related patch set uploaded (by Ladsgroup; author: Lucas Werkmeister (WMDE)):

[analytics/wmde/scripts@production] Make active_items.php executable

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

gerritbot added a comment.Aug 26 2021, 1:15 PM

Change 714856 merged by jenkins-bot:

[analytics/wmde/scripts@production] Make active_items.php executable

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

Lucas_Werkmeister_WMDE mentioned this in rADES09ca18ac8f15: Make active_items.php executable.Aug 26 2021, 1:16 PM
Ladsgroup mentioned this in rADES76afc833d9e8: Make active_items.php executable.Aug 26 2021, 1:16 PM
Lucas_Werkmeister_WMDE moved this task from Peer Review to Doing on the Wikidata-Campsite (Wikidata-Campsite-Iteration-∞ (On Hold)) board.Aug 26 2021, 1:23 PM
Lucas_Werkmeister_WMDE moved this task from Doing to Test (Verification) on the Wikidata-Campsite (Wikidata-Campsite-Iteration-∞ (On Hold)) board.Aug 27 2021, 8:28 AM

Alright, I’ve added three new panels with the requested metrics to the Wikidata Site Stats dashboard. Feel free to reorganize them (e.g. separate dashboard).

Lucas_Werkmeister_WMDE added a comment.Aug 27 2021, 8:28 AM

(Side note: in Items / Active Editors, I’m counting all items. We could also change this to only the non-redirect items.)

Manuel closed this task as Resolved.Aug 30 2021, 4:27 PM

Thank you!

So9q added a comment.Aug 31 2021, 1:21 PM

@Lucas_Werkmeister_WMDE
Hi, I fail to understand the descriptions on 2 of the new dashboards.

Items / Active Editors:
Could this be better explained? What is the 1, 5, 100 metric? Users? Edits? Is rising better than falling?

Regarding: Active Items / Active Editors
What is the purpose of this metric? Is rising better than falling? Could you explain in the i-text what it means?

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL