@cmelo I'm not understanding your last point, how do we achieve this?

and also, what happens to addresses with null countries? I think we have some. is the AC correct?

The AC should be up-to-date. My understanding is that we will decide once we see the actual events involved.

Change #1163327 had a related patch set uploaded (by Mhorsey; author: Mhorsey):

[mediawiki/extensions/CampaignEvents@master] Add new script to update old freetext country data new schema

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

Change #1163327 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@master] Add new script to update old freetext country data new schema

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

Change #1167592 had a related patch set uploaded (by Mhorsey; author: Mhorsey):

[mediawiki/extensions/CampaignEvents@wmf/1.45.0-wmf.9] Add new script to update old freetext country data new schema

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

Change #1167592 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@wmf/1.45.0-wmf.9] Add new script to update old freetext country data new schema

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

Mentioned in SAL (#wikimedia-operations) [2025-07-09T13:10:27Z] <sgimeno@deploy1003> Started scap sync-world: Backport for [[gerrit:1167592|Add new script to update old freetext country data new schema (T397270)]], [[gerrit:1167587|Growth: Enable limiting Add Link for dewiki (T396382)]]

Mentioned in SAL (#wikimedia-operations) [2025-07-09T13:12:32Z] <sgimeno@deploy1003> mhorsey, sgimeno, migr: Backport for [[gerrit:1167592|Add new script to update old freetext country data new schema (T397270)]], [[gerrit:1167587|Growth: Enable limiting Add Link for dewiki (T396382)]] synced to the testservers (see https://wikitech.wikimedia.org/wiki/Mwdebug). Changes can now be verified there.

Mentioned in SAL (#wikimedia-operations) [2025-07-09T13:20:35Z] <sgimeno@deploy1003> Finished scap sync-world: Backport for [[gerrit:1167592|Add new script to update old freetext country data new schema (T397270)]], [[gerrit:1167587|Growth: Enable limiting Add Link for dewiki (T396382)]] (duration: 10m 07s)

Mentioned in SAL (#wikimedia-operations) [2025-07-09T13:36:30Z] <Daimona> mwscript-k8s --comment="T397270" -f --file /srv/mediawiki/php-1.45.0-wmf.9/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv -- CampaignEvents:UpdateCountriesColumn --wiki metawiki --exceptions countryExceptionMappings.csv

Mentioned in SAL (#wikimedia-operations) [2025-07-09T13:39:01Z] <Daimona> mwscript-k8s --comment="T397270" -f --file /srv/mediawiki/php-1.45.0-wmf.9/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv -- CampaignEvents:UpdateCountriesColumn --wiki officewiki --exceptions countryExceptionMappings.csv

Mentioned in SAL (#wikimedia-operations) [2025-07-09T13:40:07Z] <Daimona> mwscript-k8s --comment="T397270" -f --file /srv/mediawiki/php-1.45.0-wmf.9/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv -- CampaignEvents:UpdateCountriesColumn --wiki testwiki --exceptions countryExceptionMappings.csv

Mentioned in SAL (#wikimedia-operations) [2025-07-09T13:41:31Z] <Daimona> mwscript-k8s --comment="T397270" -f --file /srv/mediawiki/php-1.45.0-wmf.9/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv -- CampaignEvents:UpdateCountriesColumn --wiki test2wiki --exceptions countryExceptionMappings.csv

Mentioned in SAL (#wikimedia-releng) [2025-07-09T13:48:19Z] <Daimona> (beta) mwscript CampaignEvents:UpdateCountriesColumn --wiki metawiki --exceptions /srv/mediawiki/php-master/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv # T397270

I ran the script everywhere, results are in the following pastes:

• Beta (all wikis): P78834
• testwiki: P78832
• test2wiki: P78833
• officewiki: P78831
• wikishared (remaining prod wikis): P78830

Beta has a weird miss, UAE, I thought that would have been in the country list but we can add it to our exceptions.

We're still missing some of the Congo variants on testwiki, I'm not sure how.

wikishared seems fine.

Generally happy with the output now.

In T397270#10989041, @MHorsey-WMF wrote:

We're still missing some of the Congo variants on testwiki, I'm not sure how.

I guess because we aren't normalizing the hardcoded exceptions. I don't think we should normalize aggressively because they are, well, hardcoded; but we could perhaps at least lowercase everything when comparing them.

wikishared seems fine.

Agreed, there's just one that isn't right:

323 - tsa matched TD - Tsad

But it's a test event and "tsa" is just nonsense, so whatever.

I guess the next thing is figuring out what to do with the unmatched events.

One thing I would like in the output (I think I mentioned it in gerrit) is the full address for unmatched rows, especially those with an empty country where I can't immediately say if the event is a test event or not. For wikishared, a quick query seems to suggest they're all real events whose country can easily be inferred by humans. For these, I think it would be nice to set them to the right country. We can either let the script do that (map ID to country), or manually run one-off queries, which would likely be easier given the small number of rows.

For actual test events, I've spent some time pondering the options, and my current opinion is that we should make them online and drop the country/address. We already know it is test data, so we should aim to clean it, not make it more realistic (e.g., by setting a real country). I also worry that fake countries might linger around in reports etc.

Change #1168205 had a related patch set uploaded (by Mhorsey; author: Mhorsey):

[mediawiki/extensions/CampaignEvents@master] Modifications to UpdateCountriesScript

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

Change #1168205 abandoned by Mhorsey:

[mediawiki/extensions/CampaignEvents@master] Modifications to UpdateCountriesScript

Reason:

Changes carried over to Ibe7d71d9346673c8bc22fc96e6299fafe853317d

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

Change #1169691 had a related patch set uploaded (by Mhorsey; author: Mhorsey):

[mediawiki/extensions/CampaignEvents@master] Modifications to UpdateCountriesScript

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

Change #1169691 had a related patch set uploaded (by Mhorsey; author: Mhorsey):

[mediawiki/extensions/CampaignEvents@master] Modifications to UpdateCountriesScript

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

Change #1169691 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@master] Modifications to UpdateCountriesScript

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

Change #1171268 had a related patch set uploaded (by Daimona Eaytoy; author: Mhorsey):

[mediawiki/extensions/CampaignEvents@wmf/1.45.0-wmf.10] Modifications to UpdateCountriesScript

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

Change #1171268 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@wmf/1.45.0-wmf.10] Modifications to UpdateCountriesScript

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

Mentioned in SAL (#wikimedia-operations) [2025-07-22T13:28:19Z] <lucaswerkmeister-wmde@deploy1003> Started scap sync-world: Backport for [[gerrit:1171268|Modifications to UpdateCountriesScript (T397270)]]

Mentioned in SAL (#wikimedia-operations) [2025-07-22T13:30:28Z] <lucaswerkmeister-wmde@deploy1003> lucaswerkmeister-wmde, daimona: Backport for [[gerrit:1171268|Modifications to UpdateCountriesScript (T397270)]] synced to the testservers (see https://wikitech.wikimedia.org/wiki/Mwdebug). Changes can now be verified there.

Mentioned in SAL (#wikimedia-operations) [2025-07-22T13:36:57Z] <lucaswerkmeister-wmde@deploy1003> Finished scap sync-world: Backport for [[gerrit:1171268|Modifications to UpdateCountriesScript (T397270)]] (duration: 08m 37s)

Mentioned in SAL (#wikimedia-operations) [2025-07-22T14:36:23Z] <Daimona> mwscript-k8s --comment="T397270" -f --file /srv/mediawiki/php-1.45.0-wmf.9/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv -- CampaignEvents:UpdateCountriesColumn --wiki metawiki --exceptions countryExceptionMappings.csv

Updated results (to be reviewed):

• Beta (all wikis): P79613
• testwiki: P79614
• test2wiki: P79615
• officewiki: P79616
• wikishared (remaining prod wikis): P79617

Change #1171617 had a related patch set uploaded (by Daimona Eaytoy; author: Daimona Eaytoy):

[mediawiki/extensions/CampaignEvents@master] Fix Database method usage in UpdateCountriesColumn

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

Review of results from T397270#11024395.

First the "easy" ones:

• Beta: overall numbers make sense; matches seem correct; I haven't checked what would be made online, as it's going to be test events anyway.
• Testwiki: overall numbers make sense; matches seem correct; two of the unmatched events were apparently missed from the list of exceptions: "République démocratique du congo" and "republique democratique du congo". I don't see them in production though, so that's fine. I also didn't check unmatched events in details as these are test events.
• Test2wiki: seems correct.
• officewiki: seems correct.

Now on to wikishared. First, I've confirmed that purged rows are unused. Then I checked matches: most of them are exact matches (potentially with different case). Checking the exceptions, most are one of

• "Côte d'Ivoire" using a single quote instead of a typographic apostrophe like in CLDR, or
• "Cameroun" (in French) being matched by the English version "Cameroon", although obviously the end result is the same, or
• "Congo-Kinshasa" without spaces, matching "Congo - Kinshasa" in CLDR
• "Tchad" (French) matching "Chad" (English)

The remaining exceptions are:

323 - "tsa" matched TD - (Tsad)
361 - "Australia" matched AT - (Austria)
374 - "Tanzanian" matched TZ - (Tanzania)
504 - "Congo-Brazzaville" matched CG - (Congo - Brazzaville)
581 - "Nigeeia" matched NG - (Nigeria)
689 - "Australia" matched AT - (Austria)
693 - "Indonésia" matched ID - (Indonésie)
747 - "Congo-Brazzaville" matched CG - (Congo - Brazzaville)

Which seem correct except for:

• "tsa" (mentioned in T397270#10989261). This is actually a keysmash, belonging to event 562, a deleted event created by @vaughnwalters to test T348281. So, no harm in changing it.
• 689 - "Australia" matched AT - (Austria) this is obviously problematic. This is for event 1680, a real event. So we need to tweak the fuzzy-matching threshold to avoid that. More on this below.

Finally, checking what events will be changed to online. The script output does not include much context, so I made P79747 for events with no address and country that will be made online, and P79748 for unmatched addresses. The former list has mostly real events, the latter mostly test events. We are going to change these events to online, and delete the address where one exists, so I'd like to have @ifried's sign-off (there's also community outreach involved).

Fuzzy-matching tweaks: to determine a better threshold, I copied all matches from beta + testwiki + production, then filtered out all the exact matches (case-insensitive), and also "Cameroun" and "Tchad" that are canonical but match another language, then removed duplicates. I then used the following script to get the actual similarity scores:

<?php

$m = [
	"Cote D'ivoire" => "Cote d’Ivoire",
	"United Stated" => "United States",
	"Polska" => "Poljska",
	"united staes" => "United States",
	"Tchade" => "Tchad",
	"tsa" => "Tsad",
	"Tanzanian" => "Tanzania",
	"Congo-kinshasa" => "Congo - Kinshasa",
	"Nigeeia" => "Nigeria",
	"Australia" => "Austria",
	"Indonésia" => "Indonésie",
	"Côte d'Ivoire" => "Côte d’Ivoire",
	"Congo-Brazzaville" => "Congo - Brazzaville",
];

$res = [];
foreach ( $m as $k => $v ) {
    similar_text( strtolower( $k ), strtolower( $v ), $percent );
    $res[ "$k -> $v" ] = $percent;
}

arsort( $res );
print_r( $res );

which gives:

(
    [united staes -> United States] => 96
    [Congo-Brazzaville -> Congo - Brazzaville] => 94.444444444444
    [Tanzanian -> Tanzania] => 94.117647058824
    [Congo-kinshasa -> Congo - Kinshasa] => 93.333333333333
    [United Stated -> United States] => 92.307692307692
    [Polska -> Poljska] => 92.307692307692
    [Tchade -> Tchad] => 90.909090909091
    [Indonésia -> Indonésie] => 90
    [Australia -> Austria] => 87.5
    [Côte d'Ivoire -> Côte d’Ivoire] => 86.666666666667
    [Cote D'ivoire -> Cote d’Ivoire] => 85.714285714286
    [tsa -> Tsad] => 85.714285714286
    [Nigeeia -> Nigeria] => 85.714285714286
)

So, there doesn't seem to be a good way to adjust the threshold without losing useful conversions. Which means the easiest solution is probably to leave the threshold as it is, but add "Australia" as a hardcoded exception.

Next steps:

• We need the patch above (https://gerrit.wikimedia.org/r/1171617) to fix some database errors
• Fix the Australia thing (possibly by adding an exception)
  • While at it, add the two missed variants for Congo
• @ifried to confirm that the changes to prod events are OK to make (we can provide more info)
• Once the fixes are in production, run the script

@MHorsey-WMF I'll leave it to you to finish this and do anything else as you deem appropriate.

I am confirming that the changes to prod events are okay. We just need an updated list of the impacted events and their associated organizers, which would include any data that would no longer be visible to them, so we can reach out to them to make any necessary changes. Thank you!

Change #1171617 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@master] Fix Database method usage in UpdateCountriesColumn

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

Change #1173383 had a related patch set uploaded (by Mhorsey; author: Mhorsey):

[mediawiki/extensions/CampaignEvents@master] Add final exceptions to script

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

Change #1173383 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@master] UpdateCountriesColumn: Add final exceptions

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

Mentioned in SAL (#wikimedia-operations) [2025-08-04T16:19:43Z] <Daimona> Running maintenance script for T397270 in x1: testwiki, test2wiki, officewiki, wikishared

I re-ran the script (in dry-run mode) to see if there have been any changes in the meantime. Results:

• Beta (all wikis): P80711
• testwiki: P80724
• test2wiki: P80726
• officewiki: P80727
• wikishared (remaining prod wikis): P80728

Haven't compared these to the last run yet.

Additional exceptions to add:

Beta

• Tianjin, China - CN

TestWiki

• Congo-Brazzaville Ifc de Brazzaville, Boulevard Raoul - CD
• Nord-kivu is also CD, but I don't think we should match it.

Test2Wiki

• none

OfficeWiki

• none

wikishared

• New York City - US
• Congo ( Brazzaville-Kinshasa) - CD

We are very popular in the DRC!!

Change #1175553 had a related patch set uploaded (by Mhorsey; author: Mhorsey):

[mediawiki/extensions/CampaignEvents@master] Add exceptions to country code migration script following test

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

I have double-checked the diffs and agree with the above, plus:

• 778 - "Enugu state" (event 1971), unmatched; it should be mapped to Nigeria, I added a comment in gerrit
• Events 1931 and 1976 (both "real" events) will be made online.

Change #1175553 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@master] Add exceptions to country code migration script following test

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

Change #1175583 had a related patch set uploaded (by Daimona Eaytoy; author: Mhorsey):

[mediawiki/extensions/CampaignEvents@wmf/1.45.0-wmf.12] Add exceptions to country code migration script following test

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

Change #1175583 merged by jenkins-bot:

[mediawiki/extensions/CampaignEvents@wmf/1.45.0-wmf.12] Add exceptions to country code migration script following test

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

Mentioned in SAL (#wikimedia-operations) [2025-08-04T20:17:24Z] <cjming@deploy1003> Started scap sync-world: Backport for [[gerrit:1175583|Add exceptions to country code migration script following test (T397270)]]

Mentioned in SAL (#wikimedia-operations) [2025-08-04T20:19:01Z] <cjming@deploy1003> daimona, cjming: Backport for [[gerrit:1175583|Add exceptions to country code migration script following test (T397270)]] synced to the testservers (see https://wikitech.wikimedia.org/wiki/Mwdebug). Changes can now be verified there.

Mentioned in SAL (#wikimedia-operations) [2025-08-04T20:24:55Z] <cjming@deploy1003> Finished scap sync-world: Backport for [[gerrit:1175583|Add exceptions to country code migration script following test (T397270)]] (duration: 07m 30s)

Mentioned in SAL (#wikimedia-operations) [2025-08-04T20:26:27Z] <Daimona> Re-run CampaignEvents country migration script in dry-run mode one last time for all wikis # T397270

Mentioned in SAL (#wikimedia-operations) [2025-08-04T20:33:04Z] <Daimona> mwscript-k8s --comment="T397270" -f --file /srv/mediawiki/php-1.45.0-wmf.12/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv -- CampaignEvents:UpdateCountriesColumn --wiki testwiki --exceptions countryExceptionMappings.csv --commit

Mentioned in SAL (#wikimedia-operations) [2025-08-04T20:35:19Z] <Daimona> mwscript-k8s --comment="T397270" -f --file /srv/mediawiki/php-1.45.0-wmf.12/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv -- CampaignEvents:UpdateCountriesColumn --wiki test2wiki --exceptions countryExceptionMappings.csv --commit

Mentioned in SAL (#wikimedia-operations) [2025-08-04T20:37:27Z] <Daimona> mwscript-k8s --comment="T397270" -f --file /srv/mediawiki/php-1.45.0-wmf.12/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv -- CampaignEvents:UpdateCountriesColumn --wiki officewiki --exceptions countryExceptionMappings.csv --commit

Mentioned in SAL (#wikimedia-operations) [2025-08-04T20:39:49Z] <Daimona> mwscript-k8s --comment="T397270" -f --file /srv/mediawiki/php-1.45.0-wmf.12/extensions/CampaignEvents/maintenance/countryExceptionMappings.csv -- CampaignEvents:UpdateCountriesColumn --wiki metawiki --exceptions countryExceptionMappings.csv --commit

I ran the script with --commit. I also did an additional dry run for each wiki before making any writes, just to make sure that we didn't get anything new. Below is the output, which includes both the dry-run and the --commit for each wiki:

• Beta (all wikis) P80770
• testwiki P80772
• test2wiki P80773
• officewiki P80774
• wikishared (remaining prod wikis): P80775

What this means in practice is that all the events in beta and in production should have been updated as per the AC, and can be verified accordingly (likely via direct DB queries).

If I run the following query against database wikishared and schema wikishared in superset :

SELECT 
    ce_address.cea_id,
    ce_address.cea_country,
    ce_address.cea_country_code,
    ce_address.cea_full_address,
    ce_event_address.ceea_event as event_id,
    campaign_events.event_meeting_type
FROM ce_address
JOIN ce_event_address ON ce_address.cea_id = ce_event_address.ceea_address
JOIN campaign_events ON ce_event_address.ceea_event = campaign_events.event_id
WHERE ce_address.cea_country IS NOT NULL

then I get the following:

I believe there should be nothing in the cea_country column now, is this thinking correct? Looking at these results there are a couple with the country remaining in the cea_full_address field as well, (the last two in the list) and I believe those should be gone as well based on AC of Removes the country from the full address field?

In T397270#11080645, @vaughnwalters wrote:

If I run the following query against database wikishared and schema wikishared [...] then I get the following:

(Noting that the data is public and available in the respective event pages, except for event 2003 that was deleted. I've been pasting these to WMF-NDA pastes so far out of an abundance of caution, but it probably was unnecessary.)

I believe there should be nothing in the cea_country column now, is this thinking correct?

No, we are still writing cea_country for new rows for the time being. That's controlled by the migration stage and mostly independent of the script. We'll stop writing to cea_country once we bump the migration stage to read_both,write_new, which is tracked in T397476. The plan was to do it in production last week, but there are a couple patches that I wanted to be merged first and these are still in code review. Plus fix-it week and me being OOO, it likely means it'll be next week.

More broadly, the script should only have affected older entries, which for wikishared, based on P80775, means anything with cea_id <= 791. Everything else has been created afterwards.

Looking at these results there are a couple with the country remaining in the cea_full_address field as well, (the last two in the list) and I believe those should be gone as well based on AC of Removes the country from the full address field?

As far as I can tell, these have been added in manually by the organizers. I don't have a remediation for that, sadly, but it might be worth looking into usability, as part of design or product sign-off. Out of 10 new events, for 4 of them the event organizers added the country in the address field, I'd say it's kinda concerning.

In T397270#11080724, @Daimona wrote:

In T397270#11080645, @vaughnwalters wrote:

If I run the following query against database wikishared and schema wikishared [...] then I get the following:

(Noting that the data is public and available in the respective event pages, except for event 2003 that was deleted. I've been pasting these to WMF-NDA pastes so far out of an abundance of caution, but it probably was unnecessary.)

Noted, I will be mindful of this going forward and will do the same.

Looking at these results there are a couple with the country remaining in the cea_full_address field as well, (the last two in the list) and I believe those should be gone as well based on AC of Removes the country from the full address field?

As far as I can tell, these have been added in manually by the organizers. I don't have a remediation for that, sadly, but it might be worth looking into usability, as part of design or product sign-off. Out of 10 new events, for 4 of them the event organizers added the country in the address field, I'd say it's kinda concerning.

Okay since this is a usability issue and not a technical issue, I'll pass this over to @JFernandez-WMF and @ifried for thoughts on this then.

Deletes duplicated row representing the same address (see T397476#10980178)

This was not implemented as part of this task and eventually resulted in T402239. It is being implemented there.

Thanks for spotting the double country listing that was created for a few of those events, @vaughnwalters! Since it looks like @Daimona handled it in the scope of T402239, I think we're good.

In that case, @JFernandez-WMF, I think you can just review this task from a more general design perspective, since the double country issue will be handled separately. Thanks!

thank youu @ifried! ill move to prod sign off