Giving Analytics results do not match actual Transactions

[EthanW96]

A Picture Is worth a Thousand Words

Description

The Giving Analytics block has a bug where occasionally it will show an unexpectedly high number of results that shows different people donated with the exact same amount (in the screenshot above $200 apologies for the partial screenshot, this issue is hard to capture). These results do not correspond to actual transactions in the Financial Transactions table and when a user inspects a Giver's profile that appeared in the Results the profile does not contain a transaction that corresponds to the Giving Analytics Results. The same proof can be found via the Transactions page and using the Grid Filter to check a Giver's transactions.

Steps to Reproduce

It is difficult to determine how to recreate the transaction -> giver (Person) relationship that causes this issue, but here are the steps once that state has been setup.

1. Go to Giving Analytics page
2. Setup the filters to overlap the date of the Offending Transaction and the other properties of the Offending Transaction so it appears in the Results.
3. Click Update and see the extra Results that are not representative of the actual Transactions on the database.

Steps to Fix (since my Steps to Reproduce aren't very helpful but perhaps the fix is a clue)

1. Find the Offending Transaction by using the Transaction List filters to search by the Amount and Date Range the Giving Analytics report is lined up to show (usually it is obvious what amount the Offending Transaction is and most Giving Analytics reports are filtered by date).
2. Usually only a few Transactions will appear in the Transaction list after filtering by Amount and Date Range and you can then further narrow down by navigate to the Contribution Tab of the people who gave those Transactions and see that one of those people have not been credited in the Transaction Tab with the Transaction the Transaction List says they should have. This is the Offending Transaction.
3. Create a person with at least some of the same info as the person who is tied to the Offending transaction.
4. Merge the new person and the original person (picking the original person's info but the new person's family)
5. Confirm this fixed the issue by reloading the Giving Analytics report
6. Go to the Original Person's Family and navigate to the Edit Family screen and click Add Person (Add Existing Person) and search for the Merged Person, adding the Merged Person to the Original Family so the original family situation remains intact.
7. The Merged Person should be added back into the Original Family now and the transaction that was tied to them should appear in the Transactions Tab now (it didn't show up before) and the Giving Analytics report that was bugged should now work correctly.

Expected behavior:

The Results of the Giving Analytics block represent the transactions that appear on the database and no apparent duplication (which causes Finance department stress) of transactions with people that have not given according to the filters selected.

Actual behavior:

Results in the Giving Analytics block appear that do not correspond to an actual transaction that individual has donated. When a person's profile under Transaction tab is inspected for one of the Results that Transaction grid does not contain a transaction that corresponds to the Giving Analytics block Results. The discrepancy causes incorrect "transactions", totals, and people to appear in the Giving Analytics report.

Versions

• Rock Version: 1.12.3.1
• Client Culture Setting: en-US

[cwomack]

@EthanW96 I'm having trouble reproducing your issue on Rock v12.3 and other versions. Are you able to provide any other steps for the setup to see it? Was there a typical amount, type, record status, or anything else that made the "offending transaction" appear?

[ConnectionChurch]

@cwomack so @EthanW96 is working with me on this issue. It has actually just happened again and this time the amount again is $200 like it was last time. It seems to be that some "ghost" account gets created without a name and then this affects everything else. I do not believe we have started doing anything different with our offering import process.

[cwomack]

@ConnectionChurch, appreciate the follow up and additional screenshots. I'm still not able to make this "ghost account" or duplicated transaction happen on the demo site or my local environment for v12.3. Are you able to share more about what happens for the import process mentioned (or what is being imported to Rock)?

[cwomack]

@EthanW96 Are you able to share more about what happens for the import process mentioned (or what is being imported to Rock)?

[TheCoreyHall]

@cwomack I am with BEMA, representing Waypoint Church, and I believe they are running into this same issue. They are on 12.3, and when filtering by account, get multiple duplicates of the same transaction similar to the screenshots above. When you go to click on the person's profile, they have no transactions that match this result set.

The thing to note for them is that it only happens when you select an Account to filter by. If you just do date range, there is no issue.

[TheCoreyHall]

Also take note, I updated the client to 12.4 and the issue persists.

[MikeDPeterson]

@EthanW96 This is probably due to the Person.GivingId and Person.GivingLeaderId getting set incorrectly. If you have an import process that directly populates the Person table instead of going thru Rock, that is mostly likely the issue. Looking at the screenshots, it looks like there are multiple Person records that have a blank GivingId (which would mean they are all in the same giving group), and multiple GivingLeaderIds in that same blank GivingId. (There should only be one GivingLeader per GivingId).

There are several fields that Rock core calculates before saving, GivingId, GivingLeaderId are a couple of those. In addition, RockCleanup will correct any Person records that may have snuck in with invalid GivingId and GIvingLeaderIds. So you could either make sure to run RockCleanup after any direct SQL operations on Person, or figure out a way to update those fields in your process. You can look at https://github.com/SparkDevNetwork/Rock/blob/develop/Dev%20Tools/Sql/Populate_Plethora_of_Person_wRandomNames.sql#L12534, for a few examples of updating those fields. But you'll probably want to run RockCleanup to make sure everything gets set correctly.