Change 935178 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/tools@master] Add fields newly added to our production civicrm instance to minimal_schema.mysql

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

@KHaggard I just want to highlight to you that we need to do a re-mapping of Acoustic soon. We will be adding the fields

AF_usd_total_2024,
AF_usd_total_2025,
donor_segment,
donor_status,

And removing

AF_usd_total_2014,
AF_usd_total_2015,
AF_usd_total_2016,
AF_usd_total_2017,

The new fields will not have much data initially but we might as well get the re-mapping done

Obviously the removals are negotiable but I'm working off the 5-years-of-data advice.

Change 935182 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/tools@master] Update Silverpop export to include new fields

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

Change 935178 abandoned by Eileen:

[wikimedia/fundraising/tools@master] Add fields newly added to our production civicrm instance to minimal_schema.mysql

Reason:

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

Hi @Eileenmcnaughton The removals make sense to me. We haven't been using the calendar year USD total fields at all, but perhaps if we switch it to fiscal year USD totals that would be more useful. Is that possible to do?

As for donor_segment and donor_status, can you help me understand what those are?

When were we planning on deploying this change? I'm not seeing much time available in the calendar to get this done, as we have our fr-online offsite next week and then we have emails deploying constantly until the end of the year.

cc: @DBu-WMF

@KHaggard the 2 new fields are the Segmentation fields that @ERoden-WMF and Sheetal (not sure the phab account) have been working on.

Regarding the year fields I don't know how you use them but we are currently sending a bunch of calendar year fields but could switch to fiscal year fields.

We would need to know which years & which fund/s (all funds, annual fund or endowment fund)

In terms of timing my understanding is @ERoden-WMF is pretty keen to start to get the segmentation fields operational (we are still tying down some details of the spec but that should happen pretty quickly)

Oh ok, can you give a quick value example of the difference between donor_segment and donor_status? Are they both text fields?

Let me sync with Danny on timing for this. I understand this is a rush job but I need to get on same page with the team real quick since this work requires us to shut down email deployments.

But yes as far as the year fields, here's what I suggest we do instead:

AF_usd_total_2018        switches to      both_funds_usd_total_fy1819 
AF_usd_total_2019        switches to      both_funds_usd_total_fy1920
AF_usd_total_2020        switches to      both_funds_usd_total_fy2021 
AF_usd_total_2021        switches to      both_funds_usd_total_fy2122
AF_usd_total_2022        switches to      both_funds_usd_total_fy2223
AF_usd_total_2023        switches to      both_funds_usd_total_fy2324 
AF_usd_total_2024        switches to      both_funds_usd_total_fy2425 
AF_usd_total_2025        switches to      both_funds_usd_total_fy2526

@KHaggard OK - I can do those fields.

Donor Status & Segment are currently set up as text fields- but actually it does depend a bit what we export as store a number in the database (e.g 100) and then that has a label - e.g 'major gifts' - or for the status the field 20 maps to Active (from memory)

So we could either export as a number or a text string. The advantage of the numbers is that you potentially can do operations on them - ie. (from memory) < 301 would be Major gits, Mid Tier & Mid tier prospects - if you can log into Civi you can see the WIP segments here

https://civicrm.wikimedia.org/civicrm/admin/custom/group/field/option?reset=1&action=browse&gid=18&fid=315

So, depending on how you would use them potentially a numeric field is best.

Thanks @Eileenmcnaughton - Okay so just to recap so I understand: you're saying that
donor_status can be a numeric value (i.e. 100, 200, 1000)
donor_segment can be a text string label (i.e. "Major Donor")

So in Acoustic, we can run operations on both text fields and numeric fields but they differ a little bit in what they offer. For example,

Text Strings:

is equal to 
is not equal to
is equal to one of the following 
is not equal to one of the following 
is blank 
is not blank 
contains
does not contain
contains one of the following 
does not contain one of the following 
starts with
does not start with
starts with one of the following 
does not start with one of the following 
ends with
does not end with

Numeric:

is equal to 
is not equal to
is equal to one of the following 
is not equal to one of the following 
is less than
is less than or equal to 
is greater than
is greater than or equal to 
is between 
is blank
is not blank

I'm also not entirely sure how we will end up using these... I think I'm leaning more towards numeric too.

@KHaggard no - they are both numeric & both have labels

segment is about their giving level (major donor, mid tier etc, grassroots plus, grass roots)
status is about the current-ness - new, consequetive, active, lapsed, deep lapsed

The only downside to using the number is that you might need to look up the translation from time to time. We have a page in CiviCRM with them on it (although as I mentioned they might change) - but obviously that requires logging into CiviCRM to access (which I encourage)

Ohh ok thanks for clarifying @Eileenmcnaughton I have Civi access and I'm looking at those pages now.
I wasn't expecting two fields for these definitions, but it does makes sense to separate them like that.
I'm surprised to see Deep lapsed, Ultra Lapsed, and NonDonors underneath "Segments" - those seem like just Status fields to me - just my two cents though.

@KHaggard Yes - I think Deep Lapsed etc are likely to move out of there - the spec is still being finalised

I am concerned that Katie would need to do QA on this during our advancement offsite next week (7/8 - 7/15). Unfortunately, I think this needs to wait until we can find the time for her to fully review this carefully.

Please ensure that any phab ticket that involves Acoustic fields included
Katie Haggard. I don't see her on this list and it makes me uncomfortable.

In T339296#9027933, @DBu-WMF wrote:

Please ensure that any phab ticket that involves Acoustic fields included
Katie Haggard. I don't see her on this list and it makes me uncomfortable.

Hi @DBu-WMF, it looks like she is on the subscriber's list for this one.

I added her to this a few minutes ago.

Hey @DBu-WMF. It looks like Erica first added her to the subscriber list on June 21st.

I had a look at the subscriber list right after I saw @DBu-WMF 's note, and I don't remember seeing her - but I just did a quick scan.

@AKanji-WMF did you expand to see the full list?

I'm pretty sure she will have automatically been added to the subscriber list after posting on July 5th. I can now see, since posting myself, that I'm on the subscriber list when I wasn't in the original screenshot of the subscribers I posted in my first reply. https://phabricator.wikimedia.org/T339296#9028060 The subscriber list increased from 10 to 11 since that post.

Acoustic would have added Katie when I pinged Katie on Jul 4. The phab this is a sub-task of https://phabricator.wikimedia.org/T335376 didn't have Katie on it (when you create a sub task it picks up the parent's list).

@KHaggard if you let us know when it suits you to do the mapping we can work to that. If the work is not fully done on our end we can compensate with place holders.

At this stage the plan is

• remove these fields that you do not use

AF_usd_total_2014,
AF_usd_total_2015,
AF_usd_total_2016,
AF_usd_total_2017,
AF_usd_total_2018
AF_usd_total_2019
AF_usd_total_2020
AF_usd_total_2021
AF_usd_total_2022
AF_usd_total_2023

Add these fields per your request
all_funds_usd_total_2018_2019
all_funds_usd_total_2019_2020
all_funds_usd_total_2020_2021
all_funds_usd_total_2021_2022
all_funds_usd_total_2022_2023
all_funds_usd_total_2023_2024
all_funds_usd_total_2024_2025
all_funds_usd_total_2025_2026

Add these fields per @ERoden-WMF request

• donor_segment
• donor_status

Note these last 2 we are still finalising details of - the phab for that is https://phabricator.wikimedia.org/T342165 and the current spreadsheet is https://docs.google.com/spreadsheets/d/1qM36MeKWyOENl-iR5umuLph5HLHG6W_6c46xJUdE3QY/edit#gid=1024386933

Thanks for summarizing @Eileenmcnaughton - That list of fields looks right to me.

As far as timing, we don't have much room at all in our email calendar to do mapping or field work until after Big English wraps up. For those who don't know, it's a requirement to disable all email sends in order to perform field mapping. Our schedule has emails going out every week for the rest of the calendar year. We won't be able to use these new segmentation fields for Big English this time, but looking forward to incorporating them after Q2.

Thanks everyone. Confirming that we'll pause on this task and pick up in Q3 (or late Q2).

Change 935182 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Update Silverpop export to include new fields

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

Change 994348 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/tools@master] Fix fix again

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

Change 994348 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Fix fix again

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

Change 994349 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/tools@master] Do some coalescing

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

Change 994349 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Do some coalescing

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