Hey @AndrewTavis_WMDE, just adding a few clarifying questions here, so I can support with this one :)

1. What should the country reflect here? The user's country, or the country of the site for which they are making the edit for. Am also trying to check in the event.mediawiki_page_change_v1 source to see if we could use the column normalized_host.project column for the join to the countries source (so long as we are trying to get the country code for the site with the edit.
2. Could you confirm the table name for the country join is canonical_data.countries?
3. Assuming the is_mobile_first column can be calculated with a statement that checks for the country which has a higher number of mobile edits than desktop edits.
4. Is providing gender as a dimension still required?

Thanks!

Answers to the above questions, @catherine.kelsey.wmde:

1. It would be the country of the user, not of the project
   • Data sources for this are wd_device_type_edits (we're editing this to add in the necessary columns to then be able to join on the data that was imported to show mobile first countries)
   • Underlying data source for wd_device_type_edits is event.mediawiki_page_change_v1 as you mentioned
   • Not having looked to much into this task, I was misremembering that this is coming from wmf.webrequest where the country would be much easier to derive (let's thus sync on this to make sure that this can be figured out)
2. Yes, canonical_data.countries, and the data that has the mobile use of countries is ext_cloudflare_percent_mobile_usage_by_country
3. See table above :)
4. We should also sync on this, as it might be difficult without having access to the wmf.webrequest data

Nice! Just adding some further thoughts on this from some table exlorations this afternoon:

• • Could we also use any of the underlying tables / data wmf.account_registrations_dbt? To get any additional info provided at registration to join on user ID?
  • wmf.webrequest in geocoded_data we have a country_code value for each row. As such, do we need to request our source tables to be updated (e.g. event.mediawiki_page_change_v1) to parse the country code from wmf.webrequest - rather than create a join on wmf.webrequest
• Can then use the above 2 tables you mention in point 2 :)

Adding a small summary for @AnnieKim_WMDE and @katiamusiolekwmde ahead of our sync later today:

• wmde data analysts have a table we have built that currently provides the data for the visualisation of the device type edits dashboard. This is at the end what we want to update / provide as the output.
• The table that we have built, consumes data from event.mediawiki_page_change_v1 - this is not owned / maintained by us. So have reached out to a few engineers about getting this stream updated to include country_code from wmf.webrequest as a dimension.
• If this is possible, it would mean we can update our table code to include the country as a column, and then also add whether the country is mobile first (yes/no) as another column.
• If this is not possible (am hoping for an answer / more clarity by 10. June), will then look at how I can add another task for our code to consume and then join the country code ourselves. This would be not super favourable, as it would mean creating an initial join / look up between the 2 large event data sets mentioned above.
• As part of our June planning, we as a team discussed delivering this by end of June, due to holidays this month in the team from both analysts.

Adding a technical / task and source detail progress update, as today there has been some success:

1. We can use the source raw table (currently in use) wmf_raw.mediawiki_private_cu_changes to add the country code. We have done this by adding in the additional temporary functions, as per how the WMF Data Engineering team handles the data for their geoeditors task. Here is an example reference for the function.
2. I have created a branch to implement the changes in our WMDE refinery and have made the code updates for the 2 core files. However, because we now need to alter the table, I need to speak with someone about how we handle that at WMDE. As the current DAG would break if I merged as is.
3. Next steps: test hql code, find out ALTER table process, publish change and update visualisation.

I have also provided stakeholders with an update and told them this will not be available until I am back from vacation.

Update ahead of my leave:

1. Code has been tested in terminal, I received no error messages - however the query compilation was too large to check the output.
2. I have generated a merge request to pass the linting checks, which have now been approved. However, the code cannot be merged until I have an answer on how we ALTER tables, in order to add the 2 new columns.

When I am back, I will need to sync with someone with knowledge on how to finalise point 2. Then can merge / ensure the DAG runs. As such, still aiming for / have communicated with stakeholders that we expect an end of June delivery.

Thank you so much, Catherine. Hope you can enjoy the time off fully.

Am 11.06.2026 um 13:41 schrieb catherine.kelsey.wmde <no-reply@phabricator.wikimedia.org>:

View Task https://phabricator.wikimedia.org/T424995
catherine.kelsey.wmde added a comment.

Update ahead of my leave:

Code has been tested in terminal, I received no error messages - however the query compilation was too large to check the output.
I have generated a merge request to pass the linting checks, which have now been approved. However, the code cannot be merged until I have an answer on how we ALTER tables, in order to add the 2 new columns.
When I am back, I will need to sync with someone with knowledge on how to finalise point 2. Then can merge / ensure the DAG runs. As such, still aiming for / have communicated with stakeholders that we expect an end of June delivery.

TASK DETAIL
https://phabricator.wikimedia.org/T424995

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

Update based on work completed this week / next steps:

• Code for populating the table updated. Based on MR review requests/responses. And a need to update the function as the one previous was deprecated.
• The ALTER statement has been applied and the table now has the additional columns.
• Need to update the code for the DAG in the Airflow repo, however am having access right issues. Will write to WMF for support. And then for actually testing the DAG will need Andrew support Monday.

The stakeholders have been informed of progress and that we hope for an end of next week delivery date (June data). But will update them if this looks to slip based on Monday testing.

catherinekelsey opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2353

wmde_T424995

catherinekelsey merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2353

wmde_T424995

Hey @AnnieKim_WMDE - the dashboard / output has been updated and I've also posted in the channel. Please let me know your feedback / comments and can work to finalise anything outstanding!