Moving this to Later as it may not make sense to do, if we are eventually switching away from Acoustic, until that happens.

I've disabled the is_202N_donor fields in Civi and covered the subtask that is the only change that changes the export in a useful way, rather than just removing fields.

Related T422533: Change AF_lifetime_usd_total in Acoustic to both_funds_lifetime_usd_total

For Q4 maintenance window, remove all the disabled custom fields. Also change last fields in Civi to both funds and then eliminate the one double (they are currently foundation only).

We'll need to do the following as well, as we will lose the lifetime_usd_total field in this index:

ALTER TABLE wmf_donor DROP INDEX entity_total;
ALTER TABLE wmf_donor ADD INDEX entity_total (entity_id, lifetime_including_endowment);

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

[wikimedia/fundraising/tools@master] Tiny clean up to remove fields now unused from minimal schema

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

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

[wikimedia/fundraising/crm@master] Remove separate endowment and foundation last donation date fields

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

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

[wikimedia/fundraising/crm@master] Remove disabled custom fields for accounting

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

Change #1287379 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Tiny clean up to remove fields now unused from minimal schema

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

Change #1287381 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Remove separate endowment and foundation last donation date fields

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

Alters done:

MariaDB [civicrm]> ALTER TABLE wmf_donor DROP INDEX entity_total;
Query OK, 0 rows affected (0.161 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [civicrm]> ALTER TABLE wmf_donor ADD INDEX entity_total (entity_id, lifetime_including_endowment);
Query OK, 0 rows affected (2 min 54.665 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [civicrm]> show index from wmf_donor where Key_name = 'entity_total';
+-----------+------------+--------------+--------------+------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table     | Non_unique | Key_name     | Seq_in_index | Column_name                  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-----------+------------+--------------+--------------+------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| wmf_donor |          1 | entity_total |            1 | entity_id                    | A         |    36214305 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| wmf_donor |          1 | entity_total |            2 | lifetime_including_endowment | A         |    36214305 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+-----------+------------+--------------+--------------+------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.001 sec)

Change #1287382 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Remove disabled custom fields for accounting

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

The above has been done, including deleting fields. Keeping this open for the remaining items.

Note for the next step on this: Send a list of fields to be deleted to Cassie / Analytics a week before deleting any fields.

@MSuijkerbuijk_WMF @NNgu-WMF Working on the audience framework update, I noticed that we have the First Donation Amount (USD) field in Civi, but it isn't exported to Acoustic. Would you like it to be exported to Acoustic or should we get rid of it entirely if it isn't used? It is currently non-endowment, but I think it would make sense to switch it to both funds at the same time (this will also make First Donation is Recurring? more feasible to implement).

If I'm not wrong, we usually apply LNA or HNA in Acoustic for targeting - but as the team works on new lifecycle strategies @NNgu-WMF could you confirm with the Content and Email teams if this criteria is needed? Thank you both

Just checked and yes, we have never used a first donation amount before in Acoustic, just first donation date.
If we did have such a field, I think it'd be more useful as both_funds_first_native_amount instead of being USD specific.

It hasn't come across any Content requests so far though.

@Lars I checked with the content team and they said First Donation Amount would be helpful - is there a First Native Donation Amount available?

@NNgu-WMF MGFE wants to keep the current First Donation Amount USD as well.
I can start easily pushing the existing First Donation Amount USD to Acoustic, if that is helpful. If we want to add a separate First Donation Native Amount and First Donation Native Currency, I think that would be a future project.

Cool, let's push First Donation Amount USD then! Thank you!

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

[wikimedia/fundraising/tools@master] Add first_donation_usd to Acoustic export

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

Change #1306744 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Add first_donation_usd to Acoustic export

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

To discuss later: Do we use CY and the CY change fields? If we could remove those, this would simplify the logic. Neither are exported to Acoustic.

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

[wikimedia/fundraising/crm@master] Remove unneeded separate endowment & foundation fields

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

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

[wikimedia/fundraising/tools@master] Remove unneeded separate endowment & foundation fields

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

ALTER TABLE silverpop_export_stat
  DROP COLUMN foundation_highest_usd_amount,
  DROP COLUMN endowment_highest_usd_amount;

ALTER TABLE silverpop_export
  CHANGE COLUMN foundation_highest_usd_amount highest_usd_amount DECIMAL(20, 2),
  CHANGE COLUMN foundation_highest_native_amount highest_native_amount DECIMAL(20, 2),
  CHANGE COLUMN foundation_highest_native_currency highest_native_currency VARCHAR(3),
  CHANGE COLUMN foundation_highest_donation_date highest_donation_date DATETIME,
  DROP COLUMN endowment_highest_usd_amount;

DROP TABLE IF EXISTS silverpop_endowment_highest;

Eight Acoustic fields to delete:

• AF_highest_donation_date
• AF_highest_native_amount
• AF_highest_native_currency
• AF_highest_usd_amount
• endowment_highest_donation_date
• endowment_highest_native_amount
• endowment_highest_native_currency
• endowment_highest_usd_amount

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

[wikimedia/fundraising/tools@master] Remove unneeded separate endowment & foundation fields

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

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

[wikimedia/fundraising/tools@master] Remove unneeded separate endowment & foundation fields

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

Change #1310227 abandoned by Lars SG:

[wikimedia/fundraising/tools@master] Remove unneeded separate endowment & foundation fields

Reason:

dupe

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

Change #1310248 abandoned by Lars SG:

[wikimedia/fundraising/tools@master] Remove unneeded separate endowment & foundation fields

Reason:

dupe

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

On deploy to Civi, the following fields can be deleted:

• total_{year}_{nextYear} — FY totals
• total_{year} — CY totals
• change_{year}_{nextYear} — CY change
• largest_donation
• endowment_total_{year}_{nextYear} — FY totals
• endowment_total_{year} — CY totals
• endowment_change_{year}_{nextYear} — CY change
• endowment_largest_donation

Additionally, date_of_largest_donation will need to be updated for all donors whose most recent donation is endowment.

Change #1310217 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Remove unneeded separate endowment & foundation fields

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

Change #1310249 merged by Eileen:

[wikimedia/fundraising/tools@master] Remove unneeded separate endowment & foundation fields

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