| Subject | Repo | Branch | Lines +/- | |
|---|---|---|---|---|
| Merge foundation and endowment latest fields for Acoustic export | wikimedia/fundraising/tools | master | +74 -147 |
Details
Details
Related Changes in Gerrit:
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Open | None | T418885 Remove un-needed separate endowment/foundation calculated fields from Civi & Acoustic | |||
| Open | Lars | T422689 Merge foundation and endowment latest fields for Acoustic export |
Event Timeline
Comment Actions
Change #1269019 had a related patch set uploaded (by Lars SG; author: Lars SG):
[wikimedia/fundraising/tools@master] Merge foundation and endowment latest fields for Acoustic export
Comment Actions
Change #1269019 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Merge foundation and endowment latest fields for Acoustic export
Comment Actions
silverpop database updates for this and T422533: Change AF_lifetime_usd_total in Acoustic to both_funds_lifetime_usd_total
-- silverpop_export_stat
ALTER TABLE silverpop_export_stat
RENAME COLUMN foundation_lifetime_usd_total TO all_funds_lifetime_usd_total,
DROP COLUMN foundation_last_donation_date,
DROP INDEX endowment_last_donation_date,
DROP COLUMN endowment_last_donation_date;
-- silverpop_export
ALTER TABLE silverpop_export
RENAME COLUMN lifetime_usd_total TO all_funds_lifetime_usd_total,
RENAME COLUMN foundation_latest_currency TO latest_currency,
RENAME COLUMN foundation_latest_currency_symbol TO latest_currency_symbol,
RENAME COLUMN foundation_latest_native_amount TO latest_native_amount,
RENAME COLUMN foundation_last_donation_date TO all_funds_latest_donation_date,
DROP COLUMN endowment_last_donation_date;
-- Drop the endowment_latest table
DROP TABLE IF EXISTS silverpop_endowment_latest;Comment Actions
New table layouts:
MariaDB [silverpop]> describe silverpop_export_stat; +--------------------------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------------+------------------+------+-----+---------+-------+ | email | varchar(255) | NO | PRI | NULL | | | all_funds_latest_donation_date | datetime | YES | MUL | NULL | | | all_funds_total_2018_2019 | decimal(20,2) | YES | | NULL | | | all_funds_total_2019_2020 | decimal(20,2) | YES | | NULL | | | all_funds_total_2020_2021 | decimal(20,2) | YES | | NULL | | | all_funds_total_2021_2022 | decimal(20,2) | YES | | NULL | | | all_funds_total_2022_2023 | decimal(20,2) | YES | | NULL | | | all_funds_total_2023_2024 | decimal(20,2) | YES | | NULL | | | all_funds_total_2024_2025 | decimal(20,2) | YES | | NULL | | | all_funds_total_2025_2026 | decimal(20,2) | YES | | NULL | | | all_funds_lifetime_usd_total | decimal(20,2) | YES | | NULL | | | foundation_donation_count | int(10) unsigned | NO | | 0 | | | foundation_first_donation_date | datetime | YES | | NULL | | | foundation_highest_usd_amount | decimal(20,2) | YES | | NULL | | | donor_segment_id | decimal(20,2) | YES | | NULL | | | donor_status_bin | int(10) unsigned | YES | | NULL | | | endowment_first_donation_date | datetime | YES | | NULL | | | endowment_number_donations | int(10) unsigned | NO | | 0 | | | endowment_highest_usd_amount | decimal(20,2) | YES | MUL | NULL | | +--------------------------------+------------------+------+-----+---------+-------+ 19 rows in set (0.002 sec)
MariaDB [silverpop]> describe silverpop_export; +---------------------------------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------------------------------------+---------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | modified_date | datetime | YES | MUL | NULL | | | contact_id | int(10) unsigned | YES | UNI | NULL | | | contact_hash | varchar(32) | YES | | NULL | | | first_name | varchar(128) | YES | | NULL | | | last_name | varchar(128) | YES | | NULL | | | preferred_language | varchar(12) | YES | | NULL | | | email | varchar(255) | YES | UNI | NULL | | | opted_in | tinyint(1) | YES | | NULL | | | opted_out | tinyint(1) | YES | | NULL | | | do_not_solicit | tinyint(1) | YES | | NULL | | | sms_consent | tinyint(1) | YES | | NULL | | | employer_id | int(10) unsigned | YES | | NULL | | | employer_name | varchar(255) | YES | | NULL | | | direct_mail_latest_appeal | varchar(255) | YES | | NULL | | | matching_gifts_provider_info_url | varchar(255) | YES | | NULL | | | guide_url | varchar(255) | YES | | NULL | | | online_form_url | varchar(255) | YES | | NULL | | | foundation_has_recurred_donation | tinyint(1) | YES | | NULL | | | foundation_has_active_recurring_donation | tinyint(1) | YES | | NULL | | | foundation_recurring_first_donation_date | datetime | YES | | NULL | | | foundation_recurring_latest_donation_date | datetime | YES | | NULL | | | foundation_recurring_active_count | tinyint(3) unsigned | YES | | NULL | | | foundation_recurring_latest_contribution_recur_id | int(10) | YES | | NULL | | | recurring_has_upgrade_activity | tinyint(1) | YES | | NULL | | | foundation_highest_usd_amount | decimal(20,2) | YES | | NULL | | | foundation_highest_native_amount | decimal(20,2) | YES | | NULL | | | foundation_highest_native_currency | varchar(3) | YES | | NULL | | | foundation_highest_donation_date | datetime | YES | | NULL | | | all_funds_lifetime_usd_total | decimal(20,2) | YES | | NULL | | | donation_count | int(10) unsigned | NO | | 0 | | | all_funds_total_2018_2019 | decimal(20,2) | YES | | NULL | | | all_funds_total_2019_2020 | decimal(20,2) | YES | | NULL | | | all_funds_total_2020_2021 | decimal(20,2) | YES | | NULL | | | all_funds_total_2021_2022 | decimal(20,2) | YES | | NULL | | | all_funds_total_2022_2023 | decimal(20,2) | YES | | NULL | | | all_funds_total_2023_2024 | decimal(20,2) | YES | | NULL | | | all_funds_total_2024_2025 | decimal(20,2) | YES | | NULL | | | all_funds_total_2025_2026 | decimal(20,2) | YES | | NULL | | | endowment_first_donation_date | datetime | YES | | NULL | | | endowment_number_donations | int(10) unsigned | NO | | 0 | | | endowment_highest_usd_amount | decimal(20,2) | YES | | NULL | | | latest_currency | varchar(3) | YES | | NULL | | | latest_currency_symbol | varchar(8) | YES | | NULL | | | latest_native_amount | decimal(20,2) | YES | | NULL | | | all_funds_latest_donation_date | datetime | YES | | NULL | | | foundation_first_donation_date | datetime | YES | | NULL | | | city | varchar(128) | YES | | NULL | | | country | varchar(2) | YES | | NULL | | | state | varchar(64) | YES | | NULL | | | postal_code | varchar(128) | YES | | NULL | | | donor_segment_id | int(11) | YES | | NULL | | | donor_status_bin | int(10) unsigned | YES | | NULL | | | double_opt_in_activity | tinyint(1) | YES | | 0 | | +---------------------------------------------------+---------------------+------+-----+---------+-------+ 54 rows in set (0.002 sec)
MariaDB [silverpop]> DROP TABLE IF EXISTS silverpop_endowment_latest; Query OK, 0 rows affected (0.008 sec) MariaDB [silverpop]> describe silverpop_endowment_latest; ERROR 1146 (42S02): Table 'silverpop.silverpop_endowment_latest' doesn't exist MariaDB [silverpop]>
Comment Actions
@ppenloglou For visibility, the following fields are no longer in Acoustic:
AF_latest_currency,
AF_latest_currency_symbol,
AF_latest_donation_date,
AF_latest_native_amount,
endowment_latest_donation_date,
endowment_latest_currency,
endowment_latest_native_amount,
We will use the both_funds versions going forward.