[Wikidata-bugs] [Maniphest] [Commented On] T213597: [REQUEST] Baselines for structured data on Commons

[mpopov]

mpopov added a comment.

Okay, here are the numbers which were calculated with the following conditions:

• Using the December 2018 snapshot of MediaWiki History in the Data Lake
• Only files which have not been deleted are counted
• Only revisions to the metadata which were not reverted AND which were not reverts AND which were not deleted
• "Metadata augmented w/in 1st 2mo" means there was at least 1 byte-adding revision to the file's page within the first 60 days after creation

It looks like the baseline for % of files which have metadata added within the first 2 months is 45.6% overall:

Files since 2003	Metadata augmented w/in 1st 2mo (60d)	Proportion
52,640,746	24,003,593	45.599%

Here are the final numbers:

Year	Files uploaded that year	Metadata augmented w/in 1st 2mo (60d)	Proportion
2004	17,669	9,423	53.331%
2005	265,976	108,449	40.774%
2006	648,025	228,230	35.219%
2007	1,205,884	371,729	30.826%
2008	1,403,480	576,987	41.111%
2009	1,927,836	822,061	42.642%
2010	2,333,372	863,588	37.010%
2011	3,884,635	1,287,972	33.156%
2012	3,490,905	1,589,173	45.523%
2013	4,591,272	2,007,547	43.725%
2014	4,715,323	2,215,437	46.984%
2015	5,683,966	2,990,535	52.614%
2016	6,312,067	2,921,214	46.280%
2017	8,182,236	3,623,897	44.290%
2018	7,978,099	4,387,351	54.992%

Month	Files uploaded that month	Metadata augmented w/in 1st 2mo (60d)	Proportion
January 2018	652,863	322,246	49.359%
February 2018	705,945	399,709	56.620%
March 2018	784,484	358,703	45.725%
April 2018	609,520	276,230	45.319%
May 2018	714,875	414,765	58.019%
June 2018	588,235	363,863	61.857%
July 2018	650,022	409,261	62.961%
August 2018	783,718	515,037	65.717%
September 2018	817,719	436,632	53.396%
October 2018	563,806	296,135	52.524%
November 2018	573,655	363,017	63.281%
December 2018	533,257	231,753	43.460%

Appendix

USE wmf;
WITH page_creation_timestamps AS (
  -- since page_creation_timestamp in mediawiki_history table is wrong:
  SELECT
    page_id,
    event_timestamp AS upload_timestamp
  FROM mediawiki_history
  WHERE snapshot = '${snapshot}'
    AND wiki_db = 'commonswiki'
    AND event_entity = 'revision'
    AND page_namespace = 6
    AND revision_parent_id = 0
    AND NOT revision_is_identity_revert -- don't count edits that are reverts
    AND NOT revision_is_identity_reverted -- don't count edits that were reverted
    AND NOT revision_is_deleted -- don't counts edits moved to archive table
    AND page_id IS NOT NULL -- don't count deleted files
), fixed_revision_history AS (
  SELECT
    page_creation_timestamps.page_id AS page_id,
    upload_timestamp,
    event_timestamp AS revision_timestamp,
    revision_parent_id,
    revision_text_bytes_diff
  FROM page_creation_timestamps
  LEFT JOIN mediawiki_history ON (
    page_creation_timestamps.page_id = mediawiki_history.page_id
    AND mediawiki_history.snapshot = '${snapshot}'
    AND mediawiki_history.wiki_db = 'commonswiki'
    AND NOT mediawiki_history.revision_is_identity_revert -- don't count edits that are reverts
    AND NOT mediawiki_history.revision_is_identity_reverted -- don't count edits that were reverted
    AND NOT mediawiki_history.revision_is_deleted -- don't counts edits moved to archive table
  )
), summarized_revisions AS (
  SELECT
    page_id, TO_DATE(upload_timestamp) AS creation_date,
    COUNT(1) AS n_edits,
    SUM(IF(revision_parent_id > 0, 1, 0)) as n_later_edits,
    SUM(IF(revision_text_bytes_diff > 0 AND DATEDIFF(revision_timestamp, upload_timestamp) <= 60 AND revision_parent_id > 0, 1, 0)) AS n_additions_2mo
  FROM fixed_revision_history
  GROUP BY page_id, TO_DATE(upload_timestamp)
)
SELECT
  creation_date,
  COUNT(1) AS n_uploaded, -- files uploaded
  SUM(IF(n_later_edits > 0, 1, 0)) AS n_later_edited, -- files whose pages were edited after upload
  SUM(IF(n_additions_2mo > 0, 1, 0)) AS n_added_to_2mo -- files that have had metadata added after creation and in first 2 months
FROM summarized_revisions
GROUP BY creation_date;

TASK DETAIL

https://phabricator.wikimedia.org/T213597

EMAIL PREFERENCES

https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: mpopov
Cc: Neil_P._Quinn_WMF, chelsyx, MNeisler, mpopov, kzimmerman, Ramsey-WMF, Abit, JKSTNK, Lahi, PDrouin-WMF, E1presidente, Cparle, Anooprao, SandraF_WMF, Tramullas, Acer, Silverfish, Susannaanas, Jane023, Wikidata-bugs, Base, matthiasmullie, Ricordisamoa, Wesalius, Lydia_Pintscher, Fabrice_Florin, Raymond, Steinsplitter

_______________________________________________
Wikidata-bugs mailing list
Wikidata-bugs@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikidata-bugs