catherinekelsey opened https://gitlab.wikimedia.org/repos/wmde/analytics/-/merge_requests/30

T419106_initial_commit

Requested an initial review from @AndrewTavis_WMDE - however, am expecting there to be some changes / updates / lessons - in order for me to understand the process for running one-off tasks. Link for MR here: https://gitlab.wikimedia.org/repos/wmde/analytics/-/merge_requests/30

catherinekelsey merged https://gitlab.wikimedia.org/repos/wmde/analytics/-/merge_requests/30

T419106_initial_commit

Output available here: https://docs.google.com/spreadsheets/d/1CfCC57p3RvyaueKsa2XrLi7dg0qSOhf9Q396KwEoybw/edit?gid=1314723548#gid=1314723548

Still need to add the steps for this sheet generation into Gitlab, but so is available for @Lydia_Pintscher to review.

Output shared / updated with @Lydia_Pintscher (as initial share had wrongly included language codes as "labels" - when in fact label is the property title, returned in English). The previous shared sheet is now updated with this.

As per previous comment, have also updated and added to the repo with:

• Updated table name / location
• Updated hql for the insert statement, as in the previous iteration I created a temporary table - however, this DDL statement does not work when running the code in the terminal.
• Added an ipynb file, to show the steps taken in Jupyter Notebook to publish the data as a csv.

Thank you, @catherine.kelsey.wmde!
I looked through the spreadsheet and it seems the numbers are off. Some examples:

• For P5460 it says it is used on 99 Items but it is actually used on a lot more: https://www.wikidata.org/w/index.php?title=Special:WhatLinksHere/Property:P5460&namespace=0&limit=500
• For the same property it says it is used on 354 Items that have a sitelink. But logically that number can not be larger than the number of Items the Property is used on.
• Towards the end the last two columns are emtpy but should not be. For example P5629 is empty in the spreadsheet but according to https://www.wikidata.org/wiki/Special:WhatLinksHere?target=Property%3AP5629&namespace=0&limit=500 it is used on a lot of Items.

Do you know what's going on?

Hi Lydia - will take a look at this and come back to you ASAP!

Hi Lydia - apologies for the delay on this. But I now realise the issue: I had used the incorrect parameter from the table, falsely understanding that claims.id and site_links.site were correct in providing the numbers for the items that a property links to. I believe I now have found the correct parameter in the table to use, so will be re-building this now.

Hi Lydia - some hopefully happy news today, I've re-run and updated the sheet. This time using a more recent snapshot date of 01/06/2026. And have cross checked for just taking the counts of item links / site links and we get the same result. Please be aware, it's slightly lower than what's on the site now - I assume that's because it's from a few weeks back. But hopefully is ok for you with this snapshot.

Sheet here: https://docs.google.com/spreadsheets/d/1CfCC57p3RvyaueKsa2XrLi7dg0qSOhf9Q396KwEoybw/edit?gid=707586689#gid=707586689

Will move to review, and if you have any other questions / concerns - please let me know. Apologies for the delay / back and forth on this!

Thank you so much!

I just noticed that it doesn't just contain Properties with datatype external identifier but that's actually useful from a quick look over it so let's keep it as is.

One remaining issue I saw: There are a bunch of entries with no data for the last 2 columns. They are now at the bottom of the sheet. Do you know what's going on there?

And last but not least: Any chance I or someone else can run the script as well? I am starting to see issues as I go through the data and it'd be useful to see changes reflected without having to potentially bother you again and again.

Hi @Lydia_Pintscher - thanks for taking a look and the questions :). To answer them:

1. OK great!
2. I've done some checks / investigation to this, and the properties at the end are returning null, because of how we need to define and then bve able to count the items. These properties are within another property for the item. The property being "main wikidata property". See an example here: https://www.wikidata.org/wiki/Q112174921 (in relation to P10830). I have spot checked a few others, and see the same pattern. If you have any details on the concept of how this type of Wikidata structure, let me know - and can run something specific for these properties.
3. The code can be found here - but let me know if you need further support - as to re-run would need to update the Snapshot date in the .hql file, and then use the python script to export.

Let me know what other support you need for this, as am happy to help :)

In T419106#12068860, @catherine.kelsey.wmde wrote:

Hi @Lydia_Pintscher - thanks for taking a look and the questions :). To answer them:

1. OK great!
2. I've done some checks / investigation to this, and the properties at the end are returning null, because of how we need to define and then bve able to count the items. These properties are within another property for the item. The property being "main wikidata property". See an example here: https://www.wikidata.org/wiki/Q112174921 (in relation to P10830). I have spot checked a few others, and see the same pattern. If you have any details on the concept of how this type of Wikidata structure, let me know - and can run something specific for these properties.

Ahhhh! Ok I see what's going on. Then I'll consider them as 0.
Might be nice to make the script write 0 for those cases but this will do for me.

1. The code can be found here - but let me know if you need further support - as to re-run would need to update the Snapshot date in the .hql file, and then use the python script to export.

Great. Maybe we can give this a try at the off-site together.

Let me know what other support you need for this, as am happy to help :)

I think this is good to close then. Thanks!

Hey @Lydia_Pintscher

1. I've created a follow up ticket for these types of properties here. Let me know if it covers everything for you / feel free to add!
2. Would love this, especially as it might mean I can use your brain for more help with understanding Wikidata entity set-up. I've put a reminder in my calendar for this during the offsite!
3. Great, will close this now :)

Next steps:

1. Add data_type field to query and re-run.
2. Create an additional query for the cases where there is a main wikiData property on the item - e.g.: https://www.wikidata.org/wiki/Q58781582

Hey again @Lydia_Pintscher - I think we are there! Update made for the code to include a block that scans for this weird case of main wikiData property. Sheet here https://docs.google.com/spreadsheets/d/1CfCC57p3RvyaueKsa2XrLi7dg0qSOhf9Q396KwEoybw/edit?gid=1186769380#gid=1186769380

Shared it on-wiki in the Notability policy RfC and also created https://www.wikidata.org/wiki/Wikidata:Notability/External_identifiers