A few questions about the data here before making any changes:

1. I'm only seeing 2451 MV23 package code activities and there are 2821 rows in that file. Is that a concern? I can make a list of the missing ones if needed.
2. For the MV52, MV42, MV62, MV41, MV51, MV61 codes, I'm seeing 20144 activities but only 1291 rows in the file. Are we changing all of them or just the ones in the file?
3. And for the A1 etc ones, I see 171252 activities, but 167272 rows in the file. Is there a way to figure out what the third character of the code should be?
4. Also for the A1 etc ones, the codes in the spreadsheet of all codes don't match the codes in the new mapping file (e.g. DMAB4 in the mapping file isn't a code in the spreadsheet of all codes).
5. Some of the codes in the spreadsheet of all codes are missing or look like they also need an update in CiviCRM. E.g. MV13 is the code in CiviCRM, DMMV13 in the spreadsheet, so that probably also needs updating. Same for MV33, MVP2, MVP1. Haven't checked the online ones, but given the mismatch above, suspect some of those are missing too.
6. Overall, the counts in the speadsheet of all codes match the counts in CiviCRM, while the counts for the mapping files do not seem to be right.

Hi Lars, I'll try to answer your questions - but let me know if anything is unclear:

1. In the file WMF_33459_3373_IND_END - there are 2,451 rows that are related to the code DMMV23 (which is the corresponding MV23 code) - so this seems to align with the activities number noted in Civi - this file also contains the packages DMMV13 (78 records/rows) and DMMV33 (279 records/rows) - which brings the row count up to the 2,821 you reference - so I don't think there should be any missing records.
2. For the MV codes MV52 (DMMV52), MV42 (DMMV42), MV62 (DMMV62), MV41 (DMMV41), MV51 (DMMV51), MV61 (DMMV61) - these are sitting in two files, as the files are split between Organization and Individual records - which might account for the discrepancy you are seeing. I'm seeing the following via file: 'WMF_33459_33783_IND_WMF' & 'WMF_33459_33783_ORG_WMF'

MV52 (DMMV52) --> Ind = 1,185 ; Org = 532 --> Total = 1,717
MV42 (DMMV42) --> Ind = 2,127 ; Org = 59 --> Total = 2,186
MV62 (DMMV62) --> Ind = 6,123; Org = 46 --> Total = 6,169
MV41 (DMMV41) --> Ind = 2,117; Org = 68 --> Total = 2,185
MV51 (DMMV51) --> Ind = 1,192; Org = 525 --> Total = 1,717
MV61 (DMMV61) --> Ind = 6,110; Org = 60 --> Total = 6,170

• this total matches the 20,144 activities you reference. All the codes across all files would need to be updated.

3. I haven't done the same calculations to the A1 etc codes - but I would imagine the same issues as noted with the MV codes - which is that the Organization file is not being factored into this calculation. I'm not entirely sure what you're referencing for the 3rd character here - but across all of the A1 etc versions of the package code the first 3 characters are missing - I recognize that it's really character 3 that is the variable - but I'm not sure how to individually determine what character 3 would be based on the old package information.
4. Noting I was able to find a DMAB4 when using a search in the file (example CID: 40729522) - in my search there were 4,179 records in the Individual file reflecting that code. So I think that all the codes should be accounted for - but if you have another example let me know.
5. See the reference to the various files noted above MV13 - is in the 'END' labelled file - this is due to splits between the Foundation vs Endowment donors.
6. I think the counts I've noted above should help to solve these discrepancies - and as you mentioned the overall counts match - but let me know,

Also - happy to jump on a call if that would be helpful - thanks!

Thanks, I think I understand the structure of these files now. Here's what I propose:

1. We delete all these activities in CiviCRM (I'll make sure the counts I delete match the spreadsheets).

2. We re-import all the activities with the corrected codes.

3. In January, we check for donations after Nov 10 with package codes that regex match ^[A-F][1-6]$|^ND7$|^MV[P0-9][123]$ and for each we check the contact for a DM activity between Nov 10 and Dec 5 and set the package code the contribution to the package code for the DM activity. I've verified this regex matches all the codes. After, verify there are no more donations with these codes with the following query:

SELECT a.id AS id, a.receive_date AS receive_date, contact_id_1.sort_name AS contact_id.sort_name, Gift_Data_2.package FROM civicrm_contribution a LEFT JOIN civicrm_contact contact_id_1 ON a.contact_id = contact_id_1.id LEFT JOIN civicrm_value_1_gift_data_7 Gift_Data_2 ON a.id = Gift_Data_2.entity_id WHERE (Gift_Data_2.package REGEXP "^MV[P0-9][123]$") AND (a.receive_date > "20251110000000");

Make sense to you @MRitch-WMF ?

Makes sense and sounds good to me - thanks for your work on this!

Step 1 is done, all the incorrect activities have been deleted.

@MDemosWMF could I put re-importing these direct mail activities from the updated files on your plate?

@Lars Thanks for sorting this out! I am out the rest of the week, so if we need this in sooner I won't be able to but can put it on my to-dos for next week.

@MDemosWMF Thanks, I'm sure that will be fine. I planned to fix the donations in the second week of January, so I think as long as it is done before then, that would be fine.

@Lars Could you upload the Individual WMF file saved here to Civi so I can import the updated activities? https://docs.google.com/spreadsheets/d/1qdtzaIcii1JufTzV9g0WpNuM1dG8gnCI/edit?usp=drive_link&ouid=110785378642940298706&rtpof=true&sd=true

The other two updated files are imported!

It should be there now @MDemosWMF

@Eileenmcnaughton Sorry I accidentally shared the wrong version of the file. Here is the correct one: https://drive.google.com/file/d/1KORv4UJlT8szYClqh8JGwv9nz0l1APLH/view?usp=drive_link

@MDemosWMF - ok that one is up

Thanks!

@Lars Just imported that last large file, but there are 77 that came back with no matching CID - can you help find the correct CID and get these into Civi? https://civicrm.wikimedia.org/civicrm/search#/display/Import_3917/Import_3917?_status=ERROR

@MDemosWMF I think those should use the same file mapping merged contact ids that we would have created when we originally imported these activities (as we would have run into the same issue then as now). Can you handle them with that file or send me a link so I can do it?

Ok I think I got these in! Imports should be complete now. @Lars @MRitch-WMF

Change #1224273 had a related patch set uploaded (by Lars SG; author: Lars SG):

[wikimedia/fundraising/crm@master] Update incorrect DM package codes on contributions

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

Thanks @MDemosWMF, appreciate it. Luckily there was only one contact who had a contribution and no DM activity (but a related contact had the activity, so that was easily fixed). The other 1529 will be updated by the patch above. None are later than Dec 11.

Once updated we should ensure is_major_gift it set for the 2 where it applies ... H32526NOVMGF / H32526NOVMGE

ANd gift_data.Fund for those 2

@Eileenmcnaughton Here's what we have now for these. But only the DM package is changing for these, so that shouldn't change is_major_gift or fund, should it?

@Lars if the package is a major gifts package (which I believe is those 2 MGE/MGF ones) then the 2 fields is_major_gift & Fund should be set appropriately

@Eileenmcnaughton Just to clarify, this is something we are expecting to be done by @MDemosWMF's team or something we are expecting to have been done automatically (and it isn't working correctly)? I know I have a pending phab to do some work to automate some of this, but I'm not quite sure if these missing values are missing because of automation not working or because of the import process.

So - the goal is to be automated - I think at the moment @MDemosWMF is ensuring the fund is set on import - not sure if the is_major_gift is being set or not - or whether these ones got set or not

@Eileenmcnaughton @Lars I don't know anything about the 'is major gift' but we are setting the fund info upon import of check gifts.

@Eileenmcnaughton @MDemosWMF I'm just looking at T386031: Automate some Direct Mail white mail coding now and wondering if we should get together to figure out exactly which fields we can set automatically on the import hook, including is_major_gift, gift_data.fund and anything else along those lines. It would probably make sense to automate as much of this as we can now.

Change #1224273 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Update incorrect DM package codes on contributions

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

The incorrect packages have now been updated.