Hi @Eileenmcnaughton could you please give the segments a once over, wanted to confirm I've laid them out in a way that makes sense. Dami pointed out that the recurring donor range seems a little off (i.e. more than 36 months).

@AKanji-WMF this page https://civicrm.wikimedia.org/civicrm/wmf-segment is definitely the master list for these - so as of today the recurring range is

" has made a recurring donation in last 36 months - ie
at least $0.01 between 2020-12-25 19:05:23 and 2024-06-30 23:59:59 AND donation is recurring"

Note that in most cases the segments are aligned to the fiscal year so come 1 July we will need to roll them over (we don't have a process for that yet so it is a task for the next few months) but with the recurring ones we do have a job that updates the rows aging out of a category on a daily basis - ie we can't recalculate our entire data base daily - but we can do targetted daily updates on these recurring ones

@Eileenmcnaughton do we need to do any updates during maintenance week before pulling this? Or does it otherwise make sense to pull this data during maintenance week?

cc: @ERoden-WMF

@AKanji-WMF - doing this isn't tied to maintenance week - we could just search through the UI for each segment in turn & put people into a group & if necessary export from CiviCRM via the UI

Here are the numbers in each segment (translation of the numbers over at https://civicrm.wikimedia.org/civicrm/wmf-segment)

select donor_segment_id, count(*) FROM wmf_donor d INNER JOI
N civicrm_contact c ON d.entity_id = c.id AND c.is_deleted = 0 GROUP BY donor_se
gment_id ;

select donor_status_id, count(*) FROM wmf_donor d INNER JOIN
civicrm_contact c ON d.entity_id = c.id AND c.is_deleted = 0 GROUP BY donor_status_id ;

@JMando we just chatted & felt that the above queries would be useful to have as dashboards in superset?

To get a random selection it is possible to do

select donor_status_id, entity_id  FROM wmf_donor d INNER JOIN civicrm_contact c ON d.entity_id = c.id AND c.is_deleted = 0 AND donor_status_id = 100 ORDER BY RAND() LIMIT 25 ;

That just does one segment but could be combined with a UNION

@Eileenmcnaughton I'll make a task for myself to get those into superset!

@Eileenmcnaughton
See below for some possible errors I found. Note some of these could come down to what dates the donation history is being compared to (today, this month, this fiscal year, etc):

• Contact ID 11749254 last gave a recurring on September 6th, 2023 (over four months ago relative to today) so I believe they should be Recent lapsed Recurring but are labeled currently as Delinquent Recurring.
  • This seems to be the case for Contact ID 29916568
• Contact ID 51559073 last gave a recurring on July 21st, 2023 (over six months ago relative to today) so I believe they should be Deep lapsed Recurring but are labeled currently as Delinquent Recurring.
• Contact ID 30797568 last gave December 30th, 2018 (over five fiscal years ago relative to today) so I believe they should be Ultra lapsed but are labeled currently as Deep Lapsed.
• Contact ID 43752231 gave on August 4th, 2020, but is labeled as a Non donor
  • This seems to be the case for Contact ID 45105075
• I do not see any donors labeled as donor_status_id = 90 (Recent lapsed Recurring)

And a couple clarifying points/questions I thought of after the QA:

• It looks like these segments are not bound by fund type (annual or endowment). Is that correct? Meaning I would be in the Major Donor segment if I gave $10,000+ in one of the past 5 financial years, or in the current financial year so far, in any of these scenarios:
  • $10,000+ just to annual fund
  • $10,000+ just to endowment
  • Or $5001 to annual and $5000 to endowment
• If I sign up for recurring post-payment, I will not be considered a recurring donor til the next month when the first recurring donation is charged?
  • In reporting for post-payment, I consider their recurring starting on that "one time donation" when they select to sign up for recurring in the upsell flow (so as soon as there is an entry in civicrm.civicrm_contribution_recur)
  • I only count the recurring sign-up amount in the recurring revenue, not the one one time donation
• Those who have donated once and had it refunded should be in the Non Donors segment?
  • They currently are, which does make sense to me. But just clarifying.

removed comment to use sub-tasks (& couldn't bothered to 2FA to be allowed to delete it)

I'm gonna create subtasks for the issues

@JMando thanks for the QA - I have created 3 subtasks that cover what appear to be the distinct issues amongst the things you raise. None are hard to fix and none impact the work next week to start putting the data into Acoustic. I think https://phabricator.wikimedia.org/T355852 is the trickiest of them because it involves figuring out how to interpret the spreadsheet into actual date ranges - but at a code level it is easy & if we do what I suggested on the phab then it is super easy.

To the questions

1. yes, the fund is not considered
2. you are correct in how is is calculated - see https://docs.google.com/spreadsheets/d/1qM36MeKWyOENl-iR5umuLph5HLHG6W_6c46xJUdE3QY/edit#gid=1024386933 but I think you raise some good points - I'm not sure exactly who we should discuss that further with
3. yes that seems correct

@Eileenmcnaughton just wanted to check in here and see if the changes had happened and the data was ready to be QA'd again? @Ejegg mentioned the process may need to be re-ran.

Specifically, do we need to re-calculate the wmf_donor segments for all those deep lapsed folks that were between 2-3 years (in the coverage gap), and do we need to re-calculate folks who missed their segment aging job?

In T353264#9518426, @JMando wrote:

@Eileenmcnaughton just wanted to check in here and see if the changes had happened and the data was ready to be QA'd again? @Ejegg mentioned the process may need to be re-ran.

@JMando I asked @Eileenmcnaughton in the tech talk today and she says all those recalculations should be already done.

Did another round of QA.

Starting with the ones I highlighted last time:

• Contact ID 11749254 last gave a recurring on September 6th, 2023 (about five months ago relative to today) so I believe they should be Recent lapsed Recurring but are labeled currently as Deep lapsed Recurring. Is this wrong or does https://civicrm.wikimedia.org/civicrm/wmf-segment need updated?
• Contact ID 51559073 is correctly marked as Deep lapsed Recurring.
• Contact ID 30797568 still seems wrong. Last gave December 30th, 2018 (over five fiscal years ago relative to today) so I believe they should be Ultra lapsed but are labeled currently as Deep Lapsed.
• Contact ID 43752231 gave on August 4th, 2020, but is still labeled as a Non donor.

Looking at some further random examples, I saw further errors in donor status non donor:

• Contact ID 43613353 is marked as donor status non donor despite having given August 2nd, 2020.
• Contact ID 48493637 is marked as donor status non donor despite having given August 2nd, 2020.
• Contact ID 50951405 is marked as donor status non donor despite having given December 2nd, 2020.

I also QA'd some of the counts:

When I query for new donors this year excluding recurring sign ups (see query below), I get 2,043,383 vs 2,052,150 given if looking at donor_status_id = 10. Pretty close.

WITH all_funds_first_donation AS (
    SELECT
        entity_id AS contact_id
        ,LEAST(COALESCE(d.first_donation_date, d.endowment_first_donation_date),
            COALESCE(d.endowment_first_donation_date,d.first_donation_date)) AS all_funds_first_donation_date
    FROM civicrm.wmf_donor AS d )
    
SELECT
	count(distinct c.contact_id)
FROM civicrm.civicrm_contribution AS c
LEFT JOIN all_funds_first_donation as aff on c.contact_id = aff.contact_id
WHERE
    c.receive_date >= '2023-07-01'
    AND aff.all_funds_first_donation_date >= '2023-07-01'
	AND c.contribution_status_id = 1 /* only pull completed transations */
	AND c.financial_type_id <> 14 /* remove refunds */
	AND c.contact_id <> 42572457 /* remove WMF fund-to-fund internal transfers */
        AND c.contact_id NOT IN (SELECT * FROM (SELECT distinct contact_id FROM civicrm.civicrm_contribution_recur)s);

When I query for active donors excluding recurring sign ups and new donors (see query below), I get 1,475,954 vs 1,488,781 given if looking at donor_status_id = 30. Close enough for me here as well.

WITH all_funds_first_donation AS (
    SELECT
        entity_id AS contact_id
        ,LEAST(COALESCE(d.first_donation_date, d.endowment_first_donation_date),
            COALESCE(d.endowment_first_donation_date,d.first_donation_date)) AS all_funds_first_donation_date
    FROM civicrm.wmf_donor AS d )
    
SELECT
	count(distinct c.contact_id)
FROM civicrm.civicrm_contribution AS c
LEFT JOIN all_funds_first_donation as aff on c.contact_id = aff.contact_id
WHERE
    c.receive_date >= '2023-07-01'
    AND aff.all_funds_first_donation_date < '2023-07-01'
	AND c.contribution_status_id = 1 /* only pull completed transations */
	AND c.financial_type_id <> 14 /* remove refunds */
	AND c.contact_id <> 42572457 /* remove WMF fund-to-fund internal transfers */
    AND c.contact_id NOT IN (SELECT * FROM (SELECT distinct contact_id FROM civicrm.civicrm_contribution_recur)s)
    AND c.contact_id NOT IN (SELECT * FROM (SELECT distinct contact_id FROM civicrm.civicrm_contribution where receive_date >= '2022-07-01' AND receive_date< '2023-07-01')s)

I can check more counts, but wanted to wait til some of the errors I pointed out above are looked at.

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

[wikimedia/fundraising/crm@master] Tweak definition on Ultra-lapsed

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

1. "Contact ID 11749254 "

Fixed - - it looks like we recalculated statuses for those too old for their statuses - but not for those who were cause by the recent lapsed vortex after addressing https://phabricator.wikimedia.org/T355851 - I re-ran the update on Deep lapsed whose last donation date was less than 7 months ago & it cleaned up that one

2)"Contact ID 30797568 still seems wrong. Last gave December 30th, 2018"

So this contact is right according to the definitions / dates but it is super confusing. The definitions of 'x financial years ago' make sense on the first day of the financial year but become increasingly confusing as we get to the end of the financial year & it it feels so long ago. I had a lot of trouble interpreting it from the spec given & hence each donor range has the dates used by the code displayed at https://civicrm.wikimedia.org/civicrm/wmf-segment (there is a slight issue with ultra lapses which I'm fixing cos it looks like it overlaps with deep lapsed right now. ) - open to improvements / ways to make it clearer here.

3. "Contact ID 43752231 gave on August 4th, 2020, but is still labeled as a Non donor."

I'm finding a chunk on non-donors so it seems like the script didn't code them initially - the contact you gave me did code correctly once I targetted her with the script. It's so long since I originally ran the script I can't really remember what might have happened at the time but I'm gonna try to re-run it on the non-donors

Hi @Eileenmcnaughton as discussed we'd like to add the mid-value prospect tier back into the segmentation.

Screenshot attached, and it's included in the master segmentation doc under the screenshot from June 14th.

Thanks

@Eileenmcnaughton @ERoden-WMF

I just finished qa'ing and all the donors I checked (a few from each status and segment) were correct:

donor_segment_id
100 - checked and passed
200 - checked and passed
400 - checked and passed
500 - checked and passed
600 - checked and passed
900 - checked and passed
1000 - checked and passed

donor_status_id
80 - checked and passed
85 - checked and passed
90 - checked and passed
95 - checked and passed
10 - checked and passed
20 - checked and passed
30 - checked and passed
35 - checked and passed
50 - checked and passed
60 - checked and passed
70 - checked and passed
100 - checked and passed

I also checked some of the counts versus some queries I wrote and I think they were close enough, but please review. Likely the differences come down to how I am excluding recurring:

donor_segment_id = 100 gives 571 and I get 574 in the below query (close enough):

With contacts as
(SELECT
    c.contact_id,
    SUM(CASE WHEN DATE(c.receive_date) >= '2018-07-01' AND DATE(c.receive_date) < '2019-07-01' THEN c.total_amount end) as 'FY1819',
    SUM(CASE WHEN DATE(c.receive_date) >= '2019-07-01' AND DATE(c.receive_date) < '2020-07-01' THEN c.total_amount end) as 'FY1920',
    SUM(CASE WHEN DATE(c.receive_date) >= '2020-07-01' AND DATE(c.receive_date) < '2021-07-01' THEN c.total_amount end) as 'FY2021',
    SUM(CASE WHEN DATE(c.receive_date) >= '2021-07-01' AND DATE(c.receive_date) < '2022-07-01' THEN c.total_amount end) as 'FY2122',
    SUM(CASE WHEN DATE(c.receive_date) >= '2022-07-01' AND DATE(c.receive_date) < '2023-07-01' THEN c.total_amount end) as 'FY2223',
    SUM(CASE WHEN DATE(c.receive_date) >= '2023-07-01' AND DATE(c.receive_date) < '2024-07-01' THEN c.total_amount end) as 'FY2324'
FROM civicrm.civicrm_contribution AS c
WHERE
    c.receive_date >= '2018-01-01'
	AND c.contribution_status_id = 1 /* only pull completed transations */
	AND c.financial_type_id <> 14 /* remove refunds */
	AND c.contact_id <> 42572457 /* remove WMF fund-to-fund internal transfers */
GROUP BY
    contact_id)
    
SELECT count(distinct contact_id)
FROM contacts
where 
    FY1819 >= 10000
    OR FY1920 >= 10000
    OR FY2021 >=10000
    OR FY2122 >= 10000
    OR FY2223 >= 10000
    OR FY2324 >= 10000;

donor_status_id = 10 gives 2,059,745 and I get 2,051,094 in the below query (close enough):

WITH all_funds_first_donation AS (
    SELECT
        entity_id AS contact_id
        ,LEAST(COALESCE(d.first_donation_date, d.endowment_first_donation_date),
            COALESCE(d.endowment_first_donation_date,d.first_donation_date)) AS all_funds_first_donation_date
    FROM civicrm.wmf_donor AS d )
    
SELECT
	count(distinct c.contact_id)
FROM civicrm.civicrm_contribution AS c
LEFT JOIN all_funds_first_donation as aff on c.contact_id = aff.contact_id
WHERE
    c.receive_date >= '2023-07-01'
    AND aff.all_funds_first_donation_date >= '2023-07-01'
	AND c.contribution_status_id = 1 /* only pull completed transations */
	AND c.financial_type_id <> 14 /* remove refunds */
	AND c.contact_id <> 42572457 /* remove WMF fund-to-fund internal transfers */
    AND c.contact_id NOT IN (SELECT * FROM (SELECT distinct contact_id FROM civicrm.civicrm_contribution_recur)s)

donor_status_id = 30 gives 1,495,649 and I get 1,482,399 in the below query (close enough):

WITH all_funds_first_donation AS (
    SELECT
        entity_id AS contact_id
        ,LEAST(COALESCE(d.first_donation_date, d.endowment_first_donation_date),
            COALESCE(d.endowment_first_donation_date,d.first_donation_date)) AS all_funds_first_donation_date
    FROM civicrm.wmf_donor AS d )
    
SELECT
	count(distinct c.contact_id)
FROM civicrm.civicrm_contribution AS c
LEFT JOIN all_funds_first_donation as aff on c.contact_id = aff.contact_id
WHERE
    c.receive_date >= '2023-07-01'
    AND aff.all_funds_first_donation_date < '2023-07-01'
	AND c.contribution_status_id = 1 /* only pull completed transations */
	AND c.financial_type_id <> 14 /* remove refunds */
	AND c.contact_id <> 42572457 /* remove WMF fund-to-fund internal transfers */
    AND c.contact_id NOT IN (SELECT * FROM (SELECT distinct contact_id FROM civicrm.civicrm_contribution_recur)s)
    AND c.contact_id NOT IN (SELECT * FROM (SELECT distinct contact_id FROM civicrm.civicrm_contribution where receive_date >= '2022-07-01' AND receive_date< '2023-07-01')s)

donor_segment_id = 500 gives 1,417,983 and I get 1,387,094 in the below query (close enough):

With contacts as
(SELECT
    c.contact_id,
    SUM(CASE WHEN DATE(c.receive_date) >= '2018-07-01' AND DATE(c.receive_date) < '2019-07-01' THEN c.total_amount end) as 'FY1819',
    SUM(CASE WHEN DATE(c.receive_date) >= '2019-07-01' AND DATE(c.receive_date) < '2020-07-01' THEN c.total_amount end) as 'FY1920',
    SUM(CASE WHEN DATE(c.receive_date) >= '2020-07-01' AND DATE(c.receive_date) < '2021-07-01' THEN c.total_amount end) as 'FY2021',
    SUM(CASE WHEN DATE(c.receive_date) >= '2021-07-01' AND DATE(c.receive_date) < '2022-07-01' THEN c.total_amount end) as 'FY2122',
    SUM(CASE WHEN DATE(c.receive_date) >= '2022-07-01' AND DATE(c.receive_date) < '2023-07-01' THEN c.total_amount end) as 'FY2223',
    SUM(CASE WHEN DATE(c.receive_date) >= '2023-07-01' AND DATE(c.receive_date) < '2024-07-01' THEN c.total_amount end) as 'FY2324'
FROM civicrm.civicrm_contribution AS c
WHERE
    c.receive_date >= '2018-01-01'
	AND c.contribution_status_id = 1 /* only pull completed transations */
	AND c.financial_type_id <> 14 /* remove refunds */
	AND c.contact_id <> 42572457 /* remove WMF fund-to-fund internal transfers */
    AND c.contact_id NOT IN (SELECT * FROM (SELECT distinct contact_id FROM civicrm.civicrm_contribution_recur)s)
GROUP BY
    contact_id)
    
SELECT count(distinct contact_id)
FROM contacts
where 
    (FY1819 >= 50 AND FY1819 < 1000)
    OR (FY1920 >= 50 AND FY1920 < 1000)
    OR (FY2021 >= 50 AND FY2021 < 1000)
    OR (FY2122 >= 50 AND FY2122 < 1000)
    OR (FY2223 >= 50 AND FY2223 < 1000)
    OR (FY2324 >= 50 AND FY2324 < 1000)

#donor_segment_id = 600 gives 21,191,240 and I get 21,682,808 in the below query (close enough):

SELECT
    c.contact_id,
    SUM(CASE WHEN DATE(c.receive_date) >= '2018-07-01' AND DATE(c.receive_date) < '2019-07-01' THEN c.total_amount end) as 'FY1819',
    SUM(CASE WHEN DATE(c.receive_date) >= '2019-07-01' AND DATE(c.receive_date) < '2020-07-01' THEN c.total_amount end) as 'FY1920',
    SUM(CASE WHEN DATE(c.receive_date) >= '2020-07-01' AND DATE(c.receive_date) < '2021-07-01' THEN c.total_amount end) as 'FY2021',
    SUM(CASE WHEN DATE(c.receive_date) >= '2021-07-01' AND DATE(c.receive_date) < '2022-07-01' THEN c.total_amount end) as 'FY2122',
    SUM(CASE WHEN DATE(c.receive_date) >= '2022-07-01' AND DATE(c.receive_date) < '2023-07-01' THEN c.total_amount end) as 'FY2223',
    SUM(CASE WHEN DATE(c.receive_date) >= '2023-07-01' AND DATE(c.receive_date) < '2024-07-01' THEN c.total_amount end) as 'FY2324'
FROM civicrm.civicrm_contribution AS c
WHERE
    c.receive_date >= '2018-01-01'
	AND c.contribution_status_id = 1 /* only pull completed transations */
	AND c.financial_type_id <> 14 /* remove refunds */
	AND c.contact_id <> 42572457 /* remove WMF fund-to-fund internal transfers */
    AND c.contact_id NOT IN (SELECT * FROM (SELECT distinct contact_id FROM civicrm.civicrm_contribution_recur)s)
GROUP BY
    contact_id)
    
SELECT count(distinct contact_id)
FROM contacts
where 
    FY1819 < 50
    OR FY1920 < 50
    OR FY2021 < 50
    OR FY2122 < 50
    OR FY2223 < 50
    OR FY2324 < 50

I did notice here https://civicrm.wikimedia.org/civicrm/wmf-segment that the definitions of deep lapsed and ultra lapsed have overlap:

• # Deep Lapsed - between 2018-07-01 00:00:00 and 2021-07-01 00:00:00
• # Ultra Lapsed - between 1999-06-30 23:59:59 and 2019-06-30 23:59:59

With FY1920 in both. Has this been fixed, but just not updated on that page? In the table you provided, the overlap is not there:

I relied on that table for QA this round as it was easy to follow.

on the last point - there is a patch for that waiting for code review https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/1005208 - it is only cosmetic because of the fall through mechanism - ie because the overlap is 'caught' by row 4 it never gets to row 5

@Eileenmcnaughton lifting up the ask to add in the mid-value prospect tier back into the segmentation.

Change 1005208 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Tweak definition on Ultra-lapsed

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

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

[wikimedia/fundraising/tools@master] Add mid-value prospect back into the Acoustic export

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

The option is back & the triggers are updated for mid-value prospect. I also put a patch up to add it into the Acoustic backport. However, there are only around 1000 contacts in this group at the moment because we haven't done a data backfill yet

I've tried updating like this

echo '{"values":{"donor_segment_id":"","donor_status_id":""},"where":[["id","BETWEEN",[1000000,3000000]],["wmf_donor.last_donation_date",">=","2018-07-01"],["wmf_donor.donor_segment_id","IN",[400,500]]]}' | cv api4 WMFDonor.update --in=json

Seems to be catching them - ie based on them already being segment 400 or 500 & having given since 2018-07-01 - but I have to run an id range

Note I've done a handful - up to

echo '{"values":{"donor_segment_id":"","donor_status_id":""},"where":[["id","BETWEEN",[34000000,38000000]],["wmf_donor.last_donation_date",">=","2018-07-01"],["wmf_donor.donor_segment_id","IN",[400,500]]]}' | cv api4 WMFDonor.update --in=json

Change 1009587 merged by jenkins-bot:

[wikimedia/fundraising/tools@master] Add mid-value prospect back into the Acoustic export

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