Hello Everyone,
At the start of the pandemic nearly all of our staff was laid off and as a result many non-critical tasks like reviewing new records and merging constituents fell by the way side. So... two years later we have around 60,000-120,000 new records and over 3,000 merges to complete. In the past we delegated data clean up and merging to the box office, but due to continuing staffing shortages we'll need to find another way.
Does anyone have experience using automated procedures to clean up data and schedule merges. Is this possible? We'd like to add CSIs to new records with invalid or missing addresses. I vaguely remember reading a forum post a while back about some tessitura organizations who outsourced data clean up and merges. I can't seem to find that post again. Has anybody tried this? Anyways, I'd love to hear what you all do and discuss the best ways to insure clean consistent data.
Thank you,
Joseph
Kathleen Smith said:Are you merging them into existing accounts if there's a match, or do you leave guest checkout accounts alone?
We always merge if there's a dupe. The goal is always: no dupes, though we will always fall short...
Tom Brown (Past Member) said:We all are showing signs of a condition I call the "fear of the one way merge".
This is sad. Of course, merges aren't irreversible, with access to SSMS.
It would be great if the network created an un-merge utility, though I can easily imagine why they don't.
We merge into an existing account if there is one. However, we look at both accounts and if the guest account has more valuable information than the existing than we remove the guest attribute and we will merge the old account into the guest account.
I'm curious as to how consortiums handle merging - I am always hesitant to merge accounts for our partner orgs, even if it is an obvious duplicate. We also check to see if the accounts we want to merge have contributions before we schedule anything, I wonder if anyone else does the same.
Our system won't allow us to delete an account if it has a donation on it. So, if we merge, we always have to keep the donor account. We get a warning that pops up as well. We merge subscriber and donor accounts, but keep the account with the subscription or the donation.
In our consortium here in Tulsa, nobody at our sublicense organizations is able to schedule merges due in part to this reason. Instead, we ask the staff at our member organizations to submit a help desk ticket to our consortium staff, and then we take on the research responsibility to evaluate the records and loop in staff from organizations affected by the request to assure that everything is correct before our team runs the merge.
Autumn Sanders
Katie Lachance-Duffy Director of Database Administration and Support Services at the Philadelphia Regional Arts Consortium is a master of the consortium account merge and purge process across organizations. Several years back now I heard her speak about their process.
Joseph,
Add me down as another person who would not trust an auto-merge; too dangerous. Admittedly, our database is one of the smaller ones, but these are our processes.
The Box Office runs the "New Account Summary" report every day; wide open to get all new accounts created since it was last run (the previous day or over the weekend). They then go through and do a thorough clean-up of those accounts, prepping for merge any accounts they find with duplicates; those get sent over to the Patron Services Manager. She then verifies that it is good and schedules the merge; she is also in charge of scheduling from the potential duplicated list. We have one staff member from Development who is in charge of merging donor accounts. Lastly, I will sometimes step in for merges when there are questions as to our sub-licensees with donors and ticket purchases (we are in a consortium environment).
We also then have a set of list manager lists, each owned by one of the part time staff. Like looking for the word "street" fully spelled out to catch and correct those issues. They will maintain their lists to keep them to zero (with another list used as an exception list for those oddly named addresses like "124 Streeter Blvd", as that would clog up the results). Then, once a month, I run an account audit, where I look at all new accounts created that month to find any that got missed in terms of standard clean-up as well as any other oddities I can find through a database search, and send them back to the Box Office for correction.
As long as we do not get too far behind (we did get so during Covid, but we are pretty nearly caught back up now), it is a really manageable process. I only have to do a big dump once a month, and it is a pretty quick one. The Box Office stays on top of the daily stuff during down time, and the List Manager elements help to catch issues that arise due to patrons changing/updating things online.
Best of luck!
John A. Moskal II
I love the idea of creating a list manager list to find common errors in records. Thank you!
Some really interesting things here.
Auto Merging - Auto merging was set up at implementation well before I started here, by the Tessitura team. The prime consideration for this is the volume of data imports from venues and partner orgs. Some of the matching issues like how often are email addresses shared across accounts are also issues for email marketing and communication and need to be part of that strategy.
Cleaning of identifiers like constituents and attributes (and relationships) often need to be handled by stakeholders so a good data governance plan, regular schedule of cleaning and some resources to make that happen are important. I recently built a attribute/relationship report to assist us with working out who was in what category and what relationships do they have. Really handy (IMHO) for tidying venues (for touring), corporate contacts, schools, and government.
Cosmetic cleaning - things like phone formatting, Proper or Allcaps fields, incorrect emails - can be cleaned up in bulk (after a visual check) with some quick SQL. I know of some folks that do that as a automated nightly SSAgent job.
Love the new record report idea.