Page MenuHomePhabricator
Create Task
Maniphest T119925

Calculate monthly mobile Wikipedia edits and editors for Major Gifts report
Closed, ResolvedPublic
Actions

Description

Total

App schema

SELECT LEFT(timestamp, 6) as "month", COUNT(*) as "mobile app edits"
FROM MobileWikiAppEdit_9003125
WHERE 
   event_action = "saved" AND
   wiki LIKE "%wiki" AND
   wiki NOT IN (
      "commonswiki", "wikidatawiki", "metawiki", "loginwiki",
      "mediawikiwiki", "sourceswiki", "specieswiki", "testwiki",
      "test2wiki", "testwikidatawiki", "incubatorwiki", "advisorywiki",
      "outreachwiki", "qualitywiki", "strategywiki", "usabilitywiki",
      "collabwiki", "donatewiki", "execwiki", "officewiki",
      "legalteamwiki", "movementroleswiki", "internalwiki",
      "auditcomwiki", "boardgovcomwiki", "boardwiki", "chairwiki",
      "chapcomwiki", "checkuser_wiki", "foundationwiki", "grantswiki",
      "iegcomwiki", "nostalgiawiki", "ombudsmenwiki", "otrs_wikiwiki",
      "searchcomwiki", "spcomwiki", "stewardwiki", "tenwiki",
      "transitionteamwiki", "votewiki", "wg_enwiki", "zerowiki",
      "checkuserwiki", "fdcwiki" ) AND
   wiki NOT LIKE "wikimania%" AND
   wiki NOT LIKE "arbcom%"
GROUP BY month;

Mobile web schema

SELECT LEFT(timestamp, 6) as "month", COUNT(*) as "mobile web edits"
FROM log.MobileWebEditing_8599025
WHERE 
   event_action = "success" AND
   wiki LIKE "%wiki" AND
   wiki NOT IN (
      "commonswiki", "wikidatawiki", "metawiki", "loginwiki",
      "mediawikiwiki", "sourceswiki", "specieswiki", "testwiki",
      "test2wiki", "testwikidatawiki", "incubatorwiki", "advisorywiki",
      "outreachwiki", "qualitywiki", "strategywiki", "usabilitywiki",
      "collabwiki", "donatewiki", "execwiki", "officewiki",
      "legalteamwiki", "movementroleswiki", "internalwiki",
      "auditcomwiki", "boardgovcomwiki", "boardwiki", "chairwiki",
      "chapcomwiki", "checkuser_wiki", "foundationwiki", "grantswiki",
      "iegcomwiki", "nostalgiawiki", "ombudsmenwiki", "otrs_wikiwiki",
      "searchcomwiki", "spcomwiki", "stewardwiki", "tenwiki",
      "transitionteamwiki", "votewiki", "wg_enwiki", "zerowiki",
      "checkuserwiki", "fdcwiki" ) AND
   wiki NOT LIKE "wikimania%" AND
   wiki NOT LIKE "arbcom%"
GROUP BY month;

Edit schema

SELECT LEFT(timestamp, 6) as "month", COUNT(*) as "mobile web edits"
FROM log.`Edit_13457736`
WHERE 
	event_platform = "phone" AND
	event_action = "saveSuccess" AND
	wiki LIKE "%wiki" AND
	wiki NOT IN (
      "commonswiki", "wikidatawiki", "metawiki", "loginwiki",
      "mediawikiwiki", "sourceswiki", "specieswiki", "testwiki",
      "test2wiki", "testwikidatawiki", "incubatorwiki", "advisorywiki",
      "outreachwiki", "qualitywiki", "strategywiki", "usabilitywiki",
      "collabwiki", "donatewiki", "execwiki", "officewiki",
      "legalteamwiki", "movementroleswiki", "internalwiki",
      "auditcomwiki", "boardgovcomwiki", "boardwiki", "chairwiki",
      "chapcomwiki", "checkuser_wiki", "foundationwiki", "grantswiki",
      "iegcomwiki", "nostalgiawiki", "ombudsmenwiki", "otrs_wikiwiki",
      "searchcomwiki", "spcomwiki", "stewardwiki", "tenwiki",
      "transitionteamwiki", "votewiki", "wg_enwiki", "zerowiki",
      "checkuserwiki", "fdcwiki" ) AND
	wiki NOT LIKE "wikimania%" AND
	wiki NOT LIKE "arbcom%"
GROUP BY month;

Article edits only

Mobile web schema

SELECT LEFT(timestamp, 6) as "month", COUNT(*) as "mobile web edits"
FROM log.MobileWebEditing_8599025
WHERE 
   event_action = "success" AND
   wiki LIKE "%wiki" AND
   wiki NOT IN (
      "commonswiki", "wikidatawiki", "metawiki", "loginwiki",
      "mediawikiwiki", "sourceswiki", "specieswiki", "testwiki",
      "test2wiki", "testwikidatawiki", "incubatorwiki", "advisorywiki",
      "outreachwiki", "qualitywiki", "strategywiki", "usabilitywiki",
      "collabwiki", "donatewiki", "execwiki", "officewiki",
      "legalteamwiki", "movementroleswiki", "internalwiki",
      "auditcomwiki", "boardgovcomwiki", "boardwiki", "chairwiki",
      "chapcomwiki", "checkuser_wiki", "foundationwiki", "grantswiki",
      "iegcomwiki", "nostalgiawiki", "ombudsmenwiki", "otrs_wikiwiki",
      "searchcomwiki", "spcomwiki", "stewardwiki", "tenwiki",
      "transitionteamwiki", "votewiki", "wg_enwiki", "zerowiki",
      "checkuserwiki", "fdcwiki" ) AND
   wiki NOT LIKE "wikimania%" AND
   wiki NOT LIKE "arbcom%" AND
      event_namespace = 0
GROUP BY month;

Edit schema

SELECT LEFT(timestamp, 6) as "month", COUNT(*) as "mobile web edits"
FROM log.`Edit_13457736`
WHERE 
	event_platform = "phone" AND
	event_action = "saveSuccess" AND
	wiki LIKE "%wiki" AND
	wiki NOT IN (
      "commonswiki", "wikidatawiki", "metawiki", "loginwiki",
      "mediawikiwiki", "sourceswiki", "specieswiki", "testwiki",
      "test2wiki", "testwikidatawiki", "incubatorwiki", "advisorywiki",
      "outreachwiki", "qualitywiki", "strategywiki", "usabilitywiki",
      "collabwiki", "donatewiki", "execwiki", "officewiki",
      "legalteamwiki", "movementroleswiki", "internalwiki",
      "auditcomwiki", "boardgovcomwiki", "boardwiki", "chairwiki",
      "chapcomwiki", "checkuser_wiki", "foundationwiki", "grantswiki",
      "iegcomwiki", "nostalgiawiki", "ombudsmenwiki", "otrs_wikiwiki",
      "searchcomwiki", "spcomwiki", "stewardwiki", "tenwiki",
      "transitionteamwiki", "votewiki", "wg_enwiki", "zerowiki",
      "checkuserwiki", "fdcwiki" ) AND
	wiki NOT LIKE "wikimania%" AND
	wiki NOT LIKE "arbcom%" AND
	`event_page.ns` = 0
GROUP BY month;

App schema

SELECT LEFT(timestamp, 6) as "month", COUNT(*) as "mobile app edits"
FROM MobileWikiAppEdit_9003125
WHERE 
   event_action = "saved" AND
   wiki LIKE "%wiki" AND
   wiki NOT IN (
      "commonswiki", "wikidatawiki", "metawiki", "loginwiki",
      "mediawikiwiki", "sourceswiki", "specieswiki", "testwiki",
      "test2wiki", "testwikidatawiki", "incubatorwiki", "advisorywiki",
      "outreachwiki", "qualitywiki", "strategywiki", "usabilitywiki",
      "collabwiki", "donatewiki", "execwiki", "officewiki",
      "legalteamwiki", "movementroleswiki", "internalwiki",
      "auditcomwiki", "boardgovcomwiki", "boardwiki", "chairwiki",
      "chapcomwiki", "checkuser_wiki", "foundationwiki", "grantswiki",
      "iegcomwiki", "nostalgiawiki", "ombudsmenwiki", "otrs_wikiwiki",
      "searchcomwiki", "spcomwiki", "stewardwiki", "tenwiki",
      "transitionteamwiki", "votewiki", "wg_enwiki", "zerowiki",
      "checkuserwiki", "fdcwiki" ) AND
   wiki NOT LIKE "wikimania%" AND
   wiki NOT LIKE "arbcom%" AND
   event_pageNS IS NULL
GROUP BY month;

English Wikipedia only

App schema

SELECT LEFT(timestamp, 6) as "month", COUNT(*) as "mobile app edits"
FROM MobileWikiAppEdit_9003125
WHERE 
   event_action = "saved" AND
   wiki = "enwiki"
GROUP BY month;

Mobile web schema

SELECT LEFT(timestamp, 6) as "month", COUNT(*) as "mobile web edits"
FROM log.MobileWebEditing_8599025
WHERE 
   event_action = "success" AND
   wiki = "enwiki"
GROUP BY month;

Edit schema

SELECT LEFT(timestamp, 6) as "month", COUNT(*) as "mobile web edits"
FROM log.`Edit_13457736`
WHERE 
	event_platform = "phone" AND
	event_action = "saveSuccess" AND
	wiki = "enwiki"
GROUP BY month;

English Wikipedia mobile editors

SELECT LEFT( rev_timestamp, 6 ), COUNT( DISTINCT( rev_user ) )
FROM change_tag
INNER JOIN revision
ON rev_id = ct_rev_id
WHERE
	ct_tag = "mobile edit" AND
	rev_timestamp >= "20140801000000"
GROUP BY LEFT( rev_timestamp, 6 )

Related Objects

Event Timeline

nshahquinn-wmf created this task.Dec 1 2015, 1:09 AM
nshahquinn-wmf claimed this task.
nshahquinn-wmf raised the priority of this task from to High.
nshahquinn-wmf updated the task description. (Show Details)
nshahquinn-wmf added a project: Contributors-Analysis.
nshahquinn-wmf moved this task to Neil's in progress on the Contributors-Analysis board.
nshahquinn-wmf subscribed.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 1 2015, 1:09 AM
nshahquinn-wmf added a comment.EditedDec 1 2015, 2:11 AM
This comment has been deleted.
nshahquinn-wmf updated the task description. (Show Details)Dec 1 2015, 7:23 PM
nshahquinn-wmf set Security to None.
nshahquinn-wmf updated the task description. (Show Details)Dec 1 2015, 7:46 PM
nshahquinn-wmf updated the task description. (Show Details)Dec 1 2015, 11:55 PM
nshahquinn-wmf closed this task as Resolved.Dec 3 2015, 12:01 AM

Done, despite the MariaDB weirdness described in T120119.

2015-12-01 Mobile edits.ods22 KBDownload

nshahquinn-wmf mentioned this in T149710: Calculate monthly mobile Wikipedia edits for Major Gifts report.Nov 14 2016, 11:49 PM
nshahquinn-wmf mentioned this in T180541: Calculate monthly mobile Wikipedia edits for Major Gifts report [due Dec 6].Nov 28 2017, 2:30 AM
nshahquinn-wmf raised the priority of this task from High to Needs Triage.Mar 30 2018, 10:14 AM
nshahquinn-wmf moved this task from Neil's in progress to Done on the Contributors-Analysis board.
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