Hi there,
Requesting the addition of a field that tracks the number of donations by fiscal year or calendar year. Currently we only have the number of donations all-time.
thank you!
Description
Description
Related Objects
Related Objects
Event Timeline
Comment Actions
Request is to have this in place for April 1, TBD in context of other requests. Will require trigger updates, which would have to be outside of a maintenance window if done before April 1.
Comment Actions
Also would be good to have more clarity on fiscal year versus calendar year for fields in general before doing this. It may make sense to shift all calculated fields to calendar year to align with donor expectations.
Comment Actions
@Lars Could we first pull a Civi search kit to understand the volume before we prioritise this for April 1? Depending on volume, it might not be so urgent. We have found an alternative way to identify loyal donors
Comment Actions
@MSuijkerbuijk_WMF We can't get this from SearchKit sadly, as it doesn't do subqueries, so we have to query directly. This the number of contacts with a specific contribution count this fiscal year (non-recurring, completed contributions only):
SELECT
contribution_count,
COUNT(id) AS contact_count
FROM (
SELECT
`a`.`id` AS `id`,
COUNT(`Contact_Contribution_contact_id_01`.`id`) AS contribution_count
FROM `civicrm_contact` a
INNER JOIN (`civicrm_contribution` `Contact_Contribution_contact_id_01`)
ON `a`.`id` = `Contact_Contribution_contact_id_01`.`contact_id`
AND (`Contact_Contribution_contact_id_01`.`contribution_recur_id` = 0 OR `Contact_Contribution_contact_id_01`.`contribution_recur_id` IS NULL)
AND `Contact_Contribution_contact_id_01`.`contribution_status_id` = "1"
AND `Contact_Contribution_contact_id_01`.`receive_date` BETWEEN "20250701000000" AND "20260630235959"
WHERE (`a`.`is_deleted` = "0")
GROUP BY `a`.`id`
) AS contribution_counts
GROUP BY contribution_count
ORDER BY contribution_count ASC+--------------------+---------------+ | contribution_count | contact_count | +--------------------+---------------+ | 1 | 4274313 | | 2 | 439491 | | 3 | 52345 | | 4 | 8983 | | 5 | 2318 | | 6 | 886 | | 7 | 759 | | 8 | 361 | | 9 | 99 | | 10 | 74 | | 11 | 58 | | 12 | 52 |
This continues, but I imagine the rest isn't relevant.
Comment Actions
This is such a great insight @Lars The size is greater than expected.
Any chance I could see this for U.S. only? Yes One-time gifts only, not recurring.
I see the limitation on Civi, but, depending how the size looks for the U.S., is there any way to export the list of those giving 2 or more?
FYI @NNgu-WMF
Comment Actions
+--------------------+---------------+ | contribution_count | contact_count | +--------------------+---------------+ | 1 | 2360610 | | 2 | 271296 | | 3 | 33401 | | 4 | 5804 | | 5 | 1482 | | 6 | 579 | | 7 | 548 | | 8 | 267 | | 9 | 56 | | 10 | 41 | | 11 | 37 | | 12 | 32 |
For a list of contacts, we should be able to do that in SK. What format do you want or what do you want to do with it?
SELECT
contribution_count,
COUNT(id) AS contact_count
FROM (
SELECT
`a`.`id` AS `id`,
COUNT(`Contact_Contribution_contact_id_01`.`id`) AS contribution_count
FROM `civicrm_contact` a
LEFT JOIN civicrm_address `address_primary_1`
ON `a`.`id` = `address_primary_1`.`contact_id`
AND `address_primary_1`.`is_primary` = 1
INNER JOIN (`civicrm_contribution` `Contact_Contribution_contact_id_01`)
ON `a`.`id` = `Contact_Contribution_contact_id_01`.`contact_id`
AND (`Contact_Contribution_contact_id_01`.`contribution_recur_id` = 0 OR `Contact_Contribution_contact_id_01`.`contribution_recur_id` IS NULL)
AND `Contact_Contribution_contact_id_01`.`contribution_status_id` = "1"
AND `Contact_Contribution_contact_id_01`.`receive_date` BETWEEN "20250701000000" AND "20260630235959"
WHERE (`a`.`is_deleted` = "0")
AND (`address_primary_1`.`country_id` = "1228")
GROUP BY `a`.`id`
) AS contribution_counts
GROUP BY contribution_count
ORDER BY contribution_count ASCComment Actions
@NNgu-WMF can talk about the format - we would upload it to Acoustic to create a list that we will target in our next fundraiser.
Comment Actions
I would need a list of emails, please! I tried to upload lists of Contact IDs to Acoustic as a contact list, and those jobs failed.
Confirming what Mariana said regarding what we want to do with the lists: we would upload it to Acoustic to create a list that we will target in our next fundraiser.
Comment Actions
What's the timeline for this? If there's enough time, we might be able to get the SK -> Acoustic connection working and be able to create a contact list in Acoustic directly.
Comment Actions
@Lars It would usually take me a day or two to create contact lists and incorporate them into the queries, so having this done by April 1 makes sense to me. Springlish is currently slated to start around April 13, so getting the list into Acoustic by April 6th would be fine too if April 1st is too much of a time crunch.
Comment Actions
Thanks @NNgu-WMF, we'll see if we can get the export to Acoustic working (T301514: Civi Searchkit should allow user to export list to acoustic) before then and if not we'll do it the old school way.
Comment Actions
Sounds good, I've made a note to self to check back in on this task around April 1st to see what we need to do
Comment Actions
Hi @Lars ! Update on this, we won't be using CY fields for Springlish but still see this as beneficial for future campaigns!
Attaching the brief for ask calculations as it's related https://docs.google.com/document/d/1e49LwigdU5vdOu63uTLmIdtf79DBcJF7g2PC1dMcvhs/edit?tab=t.0
Comment Actions
@NNgu-WMF Thanks for the update. Just FYI, we can push this (or other groups) to a group in Acoustic directly from CiviCRM now. There is a bit of work needing to make it clear the contact list automatically for repeated pushing, but we can handle that manually for now if this is needed.