Structured data on commons is defined as pieces of structured data attached to one particular item in commons stored in the MediaInfo database.

Metric definition. (Proposed)

1. A X% increase of file pages on Commons contain structured data
2. A X% increase in the percentage of file pages on Commons contain structured data

Currently, structured data on commons is not used in any other wikis except wiki commons. In the future, one more potential metric could be:

3. A X% increase in structured data on common used (uptake) across wikis.

Computation
A X% increase of file pages on Commons contain structured data

• SQL How many file pages on Commons contain at least one structured data element (statement or caption)? (ref: T239565)

SELECT COUNT(DISTINCT page_id)
FROM page 
# joining ON page_latest - we ONLY care about most recent
INNER JOIN slots ON slot_revision_id = page_latest 
# mediainfo slot must contain actual content
INNER JOIN content ON slot_content_id = content_id
	AND content_size > 122
INNER JOIN slot_roles ON role_id = slot_role_id
	AND role_name = 'mediainfo'

• Growth Rate

' A X% increase in the percentage of file pages on Commons contain structured data

• SQL

Total content pages on wiki commons

USE wmf;

SELECT COUNT(DISTINCT page_id)
FROM mediawiki_page_history
WHERE snapshot = {YYYY-MM}
	AND wiki_db = 'commonswiki'
	AND NOT page_is_deleted
	AND page_namespace_is_content;

• Growth Rate

@Nuria
Please review the initial calculation. Feel free to comment or discuss in our meeting.

Jennifer

SQL How many file pages on Commons contain at least one structured data element (statement or caption)? (ref: T239565)

This is the syntax if you run the queries on the analytics replica of mysql database. Probably it will be best to use this syntax which is the same query but in the scooped tables on the hadoop cluster (results are the same)

https://github.com/wikimedia/analytics-reportupdater-queries/blob/master/structured-data/commonswiki_mediainfo_slots

This data is automatically computed every month here (and we can augment this calculation to also calculate the "increase in the percentage of files on commons that contain structured data" let me know if you want to submit a code change for that).
https://analytics.wikimedia.org/published/datasets/periodic/reports/metrics/structured-data/

In this case i think it make sense to report the monthly growth rate on both files and monthly growth rate of structured data on files

If we want to have all files in commons to have structured data within three years I we should grow at a 3% rate every month for the percentage of files with SDC data

It's great that we have snapshot of the mediainfo slot table on hive! It's what I was looking for.
Agree with you that it's better to report the monthly growth rate on both files and monthly growth rate of structured data on files. Just want to tune the SQL and data a little bit to make it more accurate.

By cross checking the scooped table on hadoop with the table you provided me to refer, I understood that the date '2019-11-01' in this table did not mean the cutoff date, as I interpreted in the initial computation. Instead, it means the snapshot of '2019-11'. To match the correct month of mediainfo_slot and content page number, the adjusted table will be

Also I want to exclude redirected page from counting, as we double count some content creation if we include redirected page. So the adjusted SQL for " How many file pages on Commons contain at least one structured data element (statement or caption)?" is

SELECT 
       COUNT(DISTINCT page_id) AS mediainfo_slot_count
       -- page excludes deleted pages (which are in archive)
FROM wmf_raw.mediawiki_page
           -- joining on page_latest - we only care about most recent
INNER JOIN wmf_raw.mediawiki_slots         ON slot_revision_id = page_latest
           -- mediainfo slot must contain actual content
INNER JOIN wmf_raw.mediawiki_content       ON slot_content_id = content_id
                                       AND content_size > 122
INNER JOIN wmf_raw.mediawiki_slot_roles    ON role_id = slot_role_id
                                       AND role_name = 'mediainfo'
WHERE mediawiki_page.wiki_db = 'commonswiki'
   AND mediawiki_slots.wiki_db = 'commonswiki'
   AND mediawiki_content.wiki_db = 'commonswiki'
   AND mediawiki_slot_roles.wiki_db = 'commonswiki'
   AND mediawiki_page.snapshot = '{MONTH}'
   AND mediawiki_slots.snapshot = '{MONTH}'
   AND mediawiki_content.snapshot = '{MONTH}'
   AND mediawiki_slot_roles.snapshot = '{MONTH}'
   AND NOT mediawiki_page.page_is_redirect

SQL for total content pages on wiki commons

SELECT COUNT(DISTINCT page_id) AS total_content_pages_commons
FROM wmf.mediawiki_page_history
WHERE snapshot = '{MONTH}'
    AND wiki_db = 'commonswiki'
    AND NOT page_is_deleted AND NOT page_is_redirect
    AND page_namespace_is_content;

The adjusted data shows as below. The growth rate excluding redirect page is similar to the trend in previous table

Change 580136 had a related patch set uploaded (by Nuria; owner: Nuria):
[analytics/reportupdater-queries@master] Do not count pages that are just redirects

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

Also I want to exclude redirected page from counting,

Nice, thanks for the correction. Code patch submitted.

Change 580136 merged by Milimetric:
[analytics/reportupdater-queries@master] Do not count pages that are just redirects

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

Task is done and reviewed. Keep this ticket open until we reach the final decision.