Currently, we are reading the country from the export files using the Mailing Country Code column. We can do either of the following to ensure the country data is added:

• We can update the import to use the Supporter IP Country column, this would require updating the template on FRUP to ensure the exports are generated with the ISO code we use.
• We can also parse out the country field from the URL generated by us, this can be found in the Donation Page URL column for the new donations exports and Recurring Page URL column for the new recurring exports. Here's an example of the URL in the exports: https://donate.wikimedia.org/w/index.php?form=FORM_ID&title=Special:LandingPage&country=US&uselang=en&utm_medium=portal&utm_source=UTM_SOURCE&utm_campaign=UTM_CAMPAIGN&form-template=TEMPLATE&fundraiseupScript=1

The current campaigns have been focused on US, so maybe retroactively we can update the countries with US. We need to get this fixed before next campaigns as those would be diverse.

Tagging @Pcoombe who might have input here

In T351773#9352668, @Damilare wrote:

Currently, we are reading the country from the export files using the Mailing Country Code column. We can do either of the following to ensure the country data is added:

• We can update the import to use the Supporter IP Country column, this would require updating the template on FRUP to ensure the exports are generated with the ISO code we use.
• We can also parse out the country field from the URL generated by us, this can be found in the Donation Page URL column for the new donations exports and Recurring Page URL column for the new recurring exports. Here's an example of the URL in the exports: https://donate.wikimedia.org/w/index.php?form=FORM_ID&title=Special:LandingPage&country=US&uselang=en&utm_medium=portal&utm_source=UTM_SOURCE&utm_campaign=UTM_CAMPAIGN&form-template=TEMPLATE&fundraiseupScript=1

The current campaigns have been focused on US, so maybe retroactively we can update the countries with US. We need to get this fixed before next campaigns as those would be diverse.

Thanks for all the research on this @Damilare. I just looked at a test export named export_donations_2023-11-28_00-00_2023-11-28_23-59.csv and I noticed that the Supporter columns might need updates to their mappings as the country value is currently in the city column.

It looks like there's one other candidate, Supporter Language, that we could potentially infer the country from, although it's not foolproof. I guess without the customer explicitly providing their country, nothing in that file is foolproof, but the IP country looks like our best bet.

Scrap that. OpenOffice was using single quotes as the string delimiter by default. When changing to double quotes, the fields line up as expected.

@MBeat33 @Damilare, when working on this, I've realised that it's not just country data missing, it's the entire address. In fact, in the handful of exports I've checked on production, I don't see entries for any donations under

• Mailing Address Line 1
• Mailing Address Line 2
• Mailing City
• Mailing Zip/Postal
• Mailing State/Region
• Mailing Country Code

Are we okay with only adding the country for now from one of the fields we can use as a fallback, or is this a bigger problem?

Thanks @jgleeson for Donor Relations it's helpful to have address data for searching. Short term adding country is fine, though I think the question of prioritization / scope of the issue touches more stakeholders (does email team use that data for segmentation? etc).

@MSuijkerbuijk_WMF @ERoden-WMF fyi and if this is a bigger problem, let me know if I can make a spinoff Task to add full address

Thanks @MBeat33. I'll focus on populating the Country field for now. @Damilare pointed out yesterday that we don't currently collect any address data on the FRUP form, which I think differs from our usual CC forms, so that might need to change if we would like to have the data in the future.

Hi @Damilare as far as I recall, this is a known fact. During the first conversations with FRU, we already talked about the fact that they don't collect postal address information. Am I correct about that? I don't recall though if we flagged to them that we wanted the postal address (and if it can be done). Do you have that info?

@ERoden-WMF From a DB point of view, could this create issues for us in the future? Should I talk to FRU about this?

There is an option in the FRU campaigns settings (in the "Supporter" section) to either request or require a postal address. We do not currently have this enabled for any of our campaigns

Unfortunately, I don't have that information @MSuijkerbuijk_WMF. Thanks @Pcoombe for sharing the screenshot looks like they've provided a control to request for these fields after the donation is made.

Change 980874 had a related patch set uploaded (by Jgleeson; author: Jgleeson):

[wikimedia/fundraising/SmashPig@master] Add country fallback behaviour to FRUP audit code parser.

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

Change 980876 had a related patch set uploaded (by Jgleeson; author: Jgleeson):

[wikimedia/fundraising/SmashPig@master] WIP: Add country fallback behaviour to FRUP recurring audit code parser.

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

I decided to take the simplest option and retrieve the country from the donor's donation URL in https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/SmashPig/+/980874

I did look at using 'Supporter IP Country' and mapping that from a long-form country name, e.g. 'United States' to a 2-character country code, which is straightforward but it would likely require us to pull in a new external package to help map the country name to the equivalent 2-character ISO code such as https://packagist.org/packages/league/iso3166. Using data we already had in the URL trumped adding a new package dependency.

@MBeat33 @Damilare When working on this, I noticed that we only attempt to map country when processing the donation audit files, presumably one-time donations. We do not try to map the country when processing the new recurring audit file. Is this something we also want to add for consistency? I thought I'd be proactive and started a WIP patch for this here

Yes that'd be a good addition too, thanks @jgleeson.

Change 980874 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Add country fallback behaviour to FRUP audit code parser.

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

Change 980876 merged by jenkins-bot:

[wikimedia/fundraising/SmashPig@master] Add country fallback behaviour to FRUP recurring audit code parser.

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

Change 984194 had a related patch set uploaded (by Damilare Adedoyin; author: Damilare Adedoyin):

[wikimedia/fundraising/crm@master] Update smashpig package to add country in Fundraiseup imports and update tests

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

Change 984194 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Update smashpig package to add country in Fundraiseup imports and update tests

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

Deployed and backfill done. Backfill job of 28k+ contacts had 11 errors due to duplicates and invalid countries entered. Errors can be found here. Moving to done.