Monthly Maintenance Queries and Global Changes

Amanda Palmer

While I know that making Global Changes can be nerve-racking, there are some pretty simple maintenance queries that can assist in keeping your database clean. I will give you an example of some of the simple maintenance queries I have created. I run a monthly batch of global changes across my database to ensure the integrity and uniformity of the database. 


First I have created a query folder called "MAINT" and when I name the queries for this folder I use a naming convention that starts with "MAINT_" - this way if I am looking for these queries, I can just search MAINT and find them all quickly.


One of the first maintenance queries I created was for marital status = married; gender = female; title does not = Mrs. Then I created a global change to add/overwrite "Mrs" onto all of these records that meet the criteria. 


Another query I created was spouse name not blank; marital status blank - global change to add "Married" to marital status


I have a quick add for address ZIP blank - global change to check "No Valid Address". In fact, I just this second ran that global change to see how many were in there since the last time I ran it - 9 records updated. 


We have so many users entering data into our database and I cannot ensure consistently that everyone is entering the same way across the board. I use quality control reports for training, but I use these maintenance global changes to quickly correct some of the simple misses that happen throughout my data entry team. I have approx 50 maintenance queries/global changes that I run each month and it has helped me keep my database uniform and relatively clean. Creating this process has made my life much easier and I highly suggest using this process to help you as well! 


Happy cleaning!!

Joanne Felci 2

Amanda Palmer:

While I know that making Global Changes can be nerve-racking, there are some pretty simple maintenance queries that can assist in keeping your database clean. I will give you an example of some of the simple maintenance queries I have created. I run a monthly batch of global changes across my database to ensure the integrity and uniformity of the database. 


First I have created a query folder called "MAINT" and when I name the queries for this folder I use a naming convention that starts with "MAINT_" - this way if I am looking for these queries, I can just search MAINT and find them all quickly.


One of the first maintenance queries I created was for marital status = married; gender = female; title does not = Mrs. Then I created a global change to add/overwrite "Mrs" onto all of these records that meet the criteria. 


Another query I created was spouse name not blank; marital status blank - global change to add "Married" to marital status


I have a quick add for address ZIP blank - global change to check "No Valid Address". In fact, I just this second ran that global change to see how many were in there since the last time I ran it - 9 records updated. 


We have so many users entering data into our database and I cannot ensure consistently that everyone is entering the same way across the board. I use quality control reports for training, but I use these maintenance global changes to quickly correct some of the simple misses that happen throughout my data entry team. I have approx 50 maintenance queries/global changes that I run each month and it has helped me keep my database uniform and relatively clean. Creating this process has made my life much easier and I highly suggest using this process to help you as well! 


Happy cleaning!!

LOVE the idea of naming the beginning of all the queries with MAINT


thanks for the share Amanda!

Sunshine Reinken Watson

Amanda Palmer:

While I know that making Global Changes can be nerve-racking, there are some pretty simple maintenance queries that can assist in keeping your database clean. I will give you an example of some of the simple maintenance queries I have created. I run a monthly batch of global changes across my database to ensure the integrity and uniformity of the database. 


First I have created a query folder called "MAINT" and when I name the queries for this folder I use a naming convention that starts with "MAINT_" - this way if I am looking for these queries, I can just search MAINT and find them all quickly.


One of the first maintenance queries I created was for marital status = married; gender = female; title does not = Mrs. Then I created a global change to add/overwrite "Mrs" onto all of these records that meet the criteria. 


Another query I created was spouse name not blank; marital status blank - global change to add "Married" to marital status


I have a quick add for address ZIP blank - global change to check "No Valid Address". In fact, I just this second ran that global change to see how many were in there since the last time I ran it - 9 records updated. 


We have so many users entering data into our database and I cannot ensure consistently that everyone is entering the same way across the board. I use quality control reports for training, but I use these maintenance global changes to quickly correct some of the simple misses that happen throughout my data entry team. I have approx 50 maintenance queries/global changes that I run each month and it has helped me keep my database uniform and relatively clean. Creating this process has made my life much easier and I highly suggest using this process to help you as well! 


Happy cleaning!!

I have a folder of queries named "RE Clean-up" that I run periodically. It comes in handy on a slow day (is there such a thing?) or when a volunteer is coming in and can do some manual clean-up.

Jennifer Lucarelli

THIS IS A GREAT IDEA and saves so much time!!!  I have the same thing.... I have a folder in Query called Data Maintenance and I run whenever I have free time (yes, it does happen occasionally), but I also try to make a point to at least run them quarterly. For some I'll use global changes, others I change manually.  I check for things like address types, cons codes, the editable button (I hate this thing!), gender, addressee & salutation fields (I had no idea that so many folks don't know what the salutation field is for), and Title & Spouse Title fields. Additionally, I review ALL NEW ENTRIES since the date that I last checked it, just to keep up on what's coming into the database. We have over 80 users so it's really the only good way to know. 

Joy park

Indeed! I have an entire folder of CLEANUP queries...  addresses, salutations, spouses, solicit codes, attributes....


Most are run at least once a month, but there are a few that can take weeks (like my quadruple-whammy address/spousal head of household/DNS query!), so I poke at them throughout several months, and change around certain date ranges, so I know I'm only looking at records within the past 5 years.  

George Gomez

 ​I'm still new to RE but I have simplied queries running once a month during a mandatory DBA cleanup session that lasts about 2-4 hours.  Examples of my simple queries are gender = unknown, Zip = blank, and marital status = married and title != Mrs.   

Hilda Hernandez

Amanda Palmer:

While I know that making Global Changes can be nerve-racking, there are some pretty simple maintenance queries that can assist in keeping your database clean. I will give you an example of some of the simple maintenance queries I have created. I run a monthly batch of global changes across my database to ensure the integrity and uniformity of the database. 


First I have created a query folder called "MAINT" and when I name the queries for this folder I use a naming convention that starts with "MAINT_" - this way if I am looking for these queries, I can just search MAINT and find them all quickly.


One of the first maintenance queries I created was for marital status = married; gender = female; title does not = Mrs. Then I created a global change to add/overwrite "Mrs" onto all of these records that meet the criteria. 


Another query I created was spouse name not blank; marital status blank - global change to add "Married" to marital status


I have a quick add for address ZIP blank - global change to check "No Valid Address". In fact, I just this second ran that global change to see how many were in there since the last time I ran it - 9 records updated. 


We have so many users entering data into our database and I cannot ensure consistently that everyone is entering the same way across the board. I use quality control reports for training, but I use these maintenance global changes to quickly correct some of the simple misses that happen throughout my data entry team. I have approx 50 maintenance queries/global changes that I run each month and it has helped me keep my database uniform and relatively clean. Creating this process has made my life much easier and I highly suggest using this process to help you as well! 


Happy cleaning!!

Is anyone willing to share their list of maintanance queries and does anyone use RE Queue to help them run these queries and get emailed results?


Hilda

Jill Freidmutter

 

I've been doing periodic maintenance queries as described above, usually before mailings. I got an idea from another post to add some of these to my home page / dashboard. Now every day or so I refresh, then find and fix 1. constituent records that have been entered without a Primary Addressee / Primary Salutation, and 2. memorial gifts that don't have a tribute. These fields are missed occasionally when pulling gifts through from Sphere to Raiser's Edge.

Siobhan Johnson

Hilda Hernandez:

 

Amanda Palmer:

While I know that making Global Changes can be nerve-racking, there are some pretty simple maintenance queries that can assist in keeping your database clean. I will give you an example of some of the simple maintenance queries I have created. I run a monthly batch of global changes across my database to ensure the integrity and uniformity of the database. 


First I have created a query folder called "MAINT" and when I name the queries for this folder I use a naming convention that starts with "MAINT_" - this way if I am looking for these queries, I can just search MAINT and find them all quickly.


One of the first maintenance queries I created was for marital status = married; gender = female; title does not = Mrs. Then I created a global change to add/overwrite "Mrs" onto all of these records that meet the criteria. 


Another query I created was spouse name not blank; marital status blank - global change to add "Married" to marital status


I have a quick add for address ZIP blank - global change to check "No Valid Address". In fact, I just this second ran that global change to see how many were in there since the last time I ran it - 9 records updated. 


We have so many users entering data into our database and I cannot ensure consistently that everyone is entering the same way across the board. I use quality control reports for training, but I use these maintenance global changes to quickly correct some of the simple misses that happen throughout my data entry team. I have approx 50 maintenance queries/global changes that I run each month and it has helped me keep my database uniform and relatively clean. Creating this process has made my life much easier and I highly suggest using this process to help you as well! 


Happy cleaning!!

Is anyone willing to share their list of maintanance queries and does anyone use RE Queue to help them run these queries and get emailed results?


Hilda

That would be great if anyone is willing to share those queries. I would like to do better database cleanings on my system. Let me know if you would like to share that information.

 

Jill Freidmutter

Here are some of my maintenance procedures/queries. I'd love to know what yours are!

1. Memorial gifts without tributes. Add tribute
2. Constituents without salutations. Add salutations.
3. First names with " and " or " & ". Review and create spouse record if appropriate.
4. Contituents with contituent code of bereaved without a start date. Add start date.
5. Inactive constituents that need to be reactivated because of a recent gift or other activity that warrants them becoming active. Reactivate and add note why.
6. Active consitutents that need to be inactivated because of lack of donor activity or other connection. Inactivate and add note why.
7. Deceased constituents, check to see if living spouses remain in their constituent record. Create separate record for deceased, and have living spouse become primary on historic record.
8. Deceased constituents with addresses. Move address to note and delete from address.
9. "Apt" in address line 2. Move to address line 1.
10. About once a year I go through the list of Codes to see if I inadvertently added any. (KB 48704). Then I use queries to search for the errant codes, change them, and delete them from the table.

I am still working through my original list of 4000+ duplicates from the duplicate query tool. I suppose that when I finish that, I'll add duplicate resolution to the regular maintenance list.  

 

Gina Gerhard

I'm enclosing the listing of what I call my 'audit' queries (I've shared this many times before in other discussions).

I've tried to categorize these to make them more useful to others.


 

 

Hilda Hernandez:

Amanda Palmer:

While I know that making Global Changes can be nerve-racking, there are some pretty simple maintenance queries that can assist in keeping your database clean. I will give you an example of some of the simple maintenance queries I have created. I run a monthly batch of global changes across my database to ensure the integrity and uniformity of the database. 


First I have created a query folder called "MAINT" and when I name the queries for this folder I use a naming convention that starts with "MAINT_" - this way if I am looking for these queries, I can just search MAINT and find them all quickly.


One of the first maintenance queries I created was for marital status = married; gender = female; title does not = Mrs. Then I created a global change to add/overwrite "Mrs" onto all of these records that meet the criteria. 


Another query I created was spouse name not blank; marital status blank - global change to add "Married" to marital status


I have a quick add for address ZIP blank - global change to check "No Valid Address". In fact, I just this second ran that global change to see how many were in there since the last time I ran it - 9 records updated. 


We have so many users entering data into our database and I cannot ensure consistently that everyone is entering the same way across the board. I use quality control reports for training, but I use these maintenance global changes to quickly correct some of the simple misses that happen throughout my data entry team. I have approx 50 maintenance queries/global changes that I run each month and it has helped me keep my database uniform and relatively clean. Creating this process has made my life much easier and I highly suggest using this process to help you as well! 


Happy cleaning!!

Is anyone willing to share their list of maintanance queries and does anyone use RE Queue to help them run these queries and get emailed results?


Hilda

 

Austen Brown

Siobhan Johnson:

Hilda Hernandez:

 

Amanda Palmer:

While I know that making Global Changes can be nerve-racking, there are some pretty simple maintenance queries that can assist in keeping your database clean. I will give you an example of some of the simple maintenance queries I have created. I run a monthly batch of global changes across my database to ensure the integrity and uniformity of the database. 


First I have created a query folder called "MAINT" and when I name the queries for this folder I use a naming convention that starts with "MAINT_" - this way if I am looking for these queries, I can just search MAINT and find them all quickly.


One of the first maintenance queries I created was for marital status = married; gender = female; title does not = Mrs. Then I created a global change to add/overwrite "Mrs" onto all of these records that meet the criteria. 


Another query I created was spouse name not blank; marital status blank - global change to add "Married" to marital status


I have a quick add for address ZIP blank - global change to check "No Valid Address". In fact, I just this second ran that global change to see how many were in there since the last time I ran it - 9 records updated. 


We have so many users entering data into our database and I cannot ensure consistently that everyone is entering the same way across the board. I use quality control reports for training, but I use these maintenance global changes to quickly correct some of the simple misses that happen throughout my data entry team. I have approx 50 maintenance queries/global changes that I run each month and it has helped me keep my database uniform and relatively clean. Creating this process has made my life much easier and I highly suggest using this process to help you as well! 


Happy cleaning!!

Is anyone willing to share their list of maintanance queries and does anyone use RE Queue to help them run these queries and get emailed results?


Hilda

That would be great if anyone is willing to share those queries. I would like to do better database cleanings on my system. Let me know if you would like to share that information.

I'm a little late to the game here, but I'll share the types of things I check up on a monthly basis for the database.  I am working with an inherited database that has consistency issues, so when I run these monthly queries I am also cleaning up historical data as well.  Outside of what is listed below, I also run Address Accelerator for newly added constituent records to make sure that all addresses are in the DB according to organizational standards, and run the Duplicate Management Tool.

• Actions
• Addressee/Salutation Fields 
• Alias Fields
• Anonymous Status 
• Constituent Codes
• Constituent Record  (Includes everything listed on Bio 1/Org 1 of a record; Mostly required fields based on organizational standards)
• Constituent IDs
• Deceased Constituents
• Email Sign-ups
• Email Review (Removing duplicate emails from multiple constituent records)
• Gender 
• Gift Entry (Reviewing Campaign, Fund, Appeals - Making sure everything is coded correctly

Susan Chomsky

Sharing some of my maintence list: 

Address-Send Mail=NO

Blank Primary Addressee

Blank Primary Salutation

Constituent Code> 1

Former/Old Address = Send Mail

Gender and Title Mismatch

Key=IND Constituent Code done not=IND

Key=ORG Constituent Code=IND

Middle Name=not blank and No "."

ORG Record Preferred Address=Home

Print Org name w Address = NO

Print Position = NO

Salutation/Addressee EDITABLE = yes

Title = Blank

Chris Nungesser

Thank you to everyone on this thread. I appreciate you all taking the time to share this information. I'm working on improving our data maintenance planning. The information here has been really helpful and is giving me a lot to think about.

Judy Spigarelli

Thanks to everyone who shared their data clean-up queries!  Our Users' Group is meeting in two weeks and we're going to review this topic!  I'll try to get back here after our meeting to add great ideas they have.

Brittany Czekaj

Amanda Palmer‍  - thanks for sharing! We just converted our system to RE one-year ago and went live in January 2019. This will be a huge help and I plan to share with our team!

Christine Cooke 2

Yeah I have a folder of queries called Housekeeping and then the queries are broken into Monthly, Annually and then all the other ones that are ongoing cleanup projects that get attention a little bit at a time.

Becky Jones

Jill Freidmutter:

Here are some of my maintenance procedures/queries. I'd love to know what yours are!

1. Memorial gifts without tributes. Add tribute
2. Constituents without salutations. Add salutations.
3. First names with " and " or " & ". Review and create spouse record if appropriate.
4. Contituents with contituent code of bereaved without a start date. Add start date.
5. Inactive constituents that need to be reactivated because of a recent gift or other activity that warrants them becoming active. Reactivate and add note why.
6. Active consitutents that need to be inactivated because of lack of donor activity or other connection. Inactivate and add note why.
7. Deceased constituents, check to see if living spouses remain in their constituent record. Create separate record for deceased, and have living spouse become primary on historic record.
8. Deceased constituents with addresses. Move address to note and delete from address.
9. "Apt" in address line 2. Move to address line 1.
10. About once a year I go through the list of Codes to see if I inadvertently added any. (KB 48704). Then I use queries to search for the errant codes, change them, and delete them from the table.

I am still working through my original list of 4000+ duplicates from the duplicate query tool. I suppose that when I finish that, I'll add duplicate resolution to the regular maintenance list.  

 

Thank you so much for this list!   I have some maintenance queries as well, but my supervisor decided to just restrict data entry to me alone now.  We've had some questionable entries in the past and he says he only trusts me to do the entry of codes, address changes type changes.  Others can make notes, actions and comment on entries, but I'm the only one now that can create table entries.  I've tried to clean up my tables before and have gone through the process you describe, but when I try to delete those codes that I have replaced, I get a message that says something like "this code exists on a record, do not delete"...has this ever happened to you?  I've never, through queries, been able to find the record in RE that has that code.   Any ideas?  To be honest, I don't run this type of maint query unless I have spare minute or two.  Thanks again for your info and help!

Becky