Best Practices for New Record Data Clean Up and Merges

You are welcome!  But to give credit where credit is due, I stole that idea from Beth Hawryluk.  So thanks, Beth!

We have 13 historic sites around the State that use Tessitura for ticketing.  If they suspect a duplicate, then they submit a CSI with the ids so someone in the box office or membership office can take a closer look and schedule the merge.  We also play around with the parameters in the procedure that identifies potential duplicates and our Box Office/Membership keep an eye on that.

With enough people looking at the merge process, it seems to stay relatively under control on our end.  

Really?  In 2019 I contacted support about an accidental merge and got the below response: 

Once constituents have been merged, there is no standard process to unmerge constituent records. If the records haven't been merged in your test system, you could use that as a guide to reassign the constituent data. 

Any orders which can be determined to belong to the constituent deleted in error can be changed to the correct customer_no by opening the order within Tessitura and changing the constituent ID for the order. The system will prompt that you are changing the owner of the orders, which you can accept.

Kanani Reichlin said:

Once constituents have been merged, there is no standard process to unmerge constituent records.

Note the "standard". Yes, there is no standard way to un-merge records, i.e. in the client, using client tools. If you are able to access your data via SSMS, though, you can do un-merges there, via SQL.

You basically have to set the "delete" constituent back to "active", and then reverse the steps taken by AP_MERGE_CUSTOMER, plus any custom tables you might update via LP_CONST_MERGE. It's not rocket science, but it can be fiddly and time consuming, depending on how complex the two merged records were.

Years ago a query about un-merges sent to support would get this kind of, "you can do it, but you're kind of on your own" response. It's sad to see that they just say, "you can't do it" more recently.

Thanks for the mention, Tom Brown (Past Member)!

Autumn Sanders, et al, I have done a lot of work over the years on getting our consortium's constituent merge process streamlined as much as possible, and I honestly love to talk about it. :)

The discussion over automated vs manual duplicate identification and merging is an interesting one. I heard the phrase "curated merging" at a TLCC once and that seems like the best way to describe my consortium's approach. We do automate a fair deal of duplicate identification and merge scheduling, and we also encourage members at all of our 20+ consortium organizations to take part in the fun of scheduling merges. (They know their patrons the best after all!) However, even with the automated dup identification and scheduling of merges, there are a whole slew of issues that might cause a merge either to fail or lose data, and I'm talking way beyond questions like which primary address is the preferred one. I did at one point share my customizations with the Network, but I think it's been so long they were taken down from the shared reports site (which is probably fine, as I'm sure they needed some updates).

I have presented in past TLCCs on duplicate as well as other types of data management, and here's a PowerPoint from a presentation I gave at our Philly regional community group in 2019. I'd love to reshare my code as well, but it's always one of those back-burner projects and I wonder as well if it's as useful to orgs that don't have a DBA/SQL programmer on hand.

If we didn't have someone with SQL skills on hand I think I'd look into having a consultant write something to at least automate the merge scheduling process. Then, if you have the merge job run once a week you could review the merges the day before the job run and unschedule any obvious false positives. Bad merges happen, if with the most careful setup, but if you feel confident enough in the merge scheduling process they should be few and far between. (And while reversing a merge isn't straightforward, it's not impossible, as others have said, but it does require SQL knowledge.)

Happy duplicates handling!

I have written and implemented automated duplicate detection and merge scheduling for several Tessitura organizations, both consortium and not.  The duplicate detection is customized to the organization's needs and priorities.  As part of the process, the void merge attribute is added to certain customer records automatically each night, again based on customized requirements.  The merge scheduling looks at the potential duplicates and schedules the merge / which record is kept based on a variety of factors such as contributions, ticket history, constituencies, planned giving, etc.  The process consists of a scheduled report that searches for the potential duplicates and schedules them based on the criteria.  The report is set up to be reviewed weekly, generally by someone in Development due to concerns on donors.  There is a delay set up between that and a separate scheduled report that actually performs the merges to enable the constituents to be taken off the merge schedule if desired.  

Hi Carol, 

Are any of these organisations on RAMP? Would you be able to migrate the set up to other orgs?

Thanks

Louise

Yes, the majority of them were on ramp.  I would be able to implement it elsewhere.  You can contact me directly at ckeeney@arkmfa.org if you'd like to discuss it further.