I ran a query to check which fund lifetime_usd_total points to, when I noticed discrepancies in the individual contact records.
Query:
WHEN lifetime_usd_total is greater than 0
AND (latest_donation_date is blank
OR latest_usd_amount is equal to 0)
AND endowment_last_donation_date is not blank
I expected this query to return 0 contacts because my inclination is that lifetime_usd_total only applies to annual fund donations, not endowment. But instead, 21 contacts populated this query. I looked at the first two contact records and found that latest_donation_date is blank, but the rest of their record shows recent donations to the annual fund within the last 5 years.
ContactID 30996345
ContactID 1764053
I compared what I saw in Acoustic to Civi and found out:
- ContactID 30996345 has no record for ever donating to the annual fund in civi, yet this donor has a lifetime_usd_total over $100 and shows a donation every year since 2014 in Acoustic. Acoustic also shows that their first donation was in 2007 when the latest_donation_date is blank.
- ContactID 1764053 matches what I found in civi, although the following fields are blank or zero in Acoustic when they shouldn't be: highest_donation_date, highest_native_amount, highest_native_currency, highest_usd_amount, latest_native_amount. This contact's first donation date is 2008.
Essentially, my concern is that annual fund donors appeared in a query meant to remove all annual fund donors. We heavily rely on latest_don_date and first_donation_date as prime fields for targeting/segmentation. This shows me that it's possible we have been including contacts we don’t want for our email sends.
Can fr-tech identify what's happening here? Should we refrain from using "is blank" in queries and rely on "is equal to 0" instead?