Database Cleanup Best Practices
Cleaning and Maintaining Your Data

Getting Started:

Periodically purge the junk

The first time is always the hardest. After you conduct your first data cleanup/purgeimmediately institute routine database cleanup practices and create the scheduled frequency of when routine data cleanup will take place. Avoid any major database cleanup during the period from December thru March, year end and audit season. Your church Business Administrator will thank you. Once database cleanup is routine it becomes easier to maintain.

Designate a Database Champion and a Database Management Team

Assign a Database Champion and assign database cleanup oversight to one person. Depending on your church size and use, a Database Administrator/Manager will be needed. Again, depending on your church size, you may also need someone to assist the Database Champion with routine database cleanup. This should be a dedicated staff person’s responsibility, not a task that rotates through the Administrative Staff.

A Database Management Team is quickly becoming a need in churches today. The Database Management Team could consist of the Pastor or Director the Database Champion reports to, a database assistant, someone from IT (staff or consultant), someone from Communications and someone from Finance. When the Database Champion is working on a big project such as the first database cleanup, weekly meetings would be helpful. Thereafter meetings could take place once or twice a month to discuss pertinent database strategies for the staff, volunteers and congregants.

Data integrity is every staff member’s responsibility, database cleanup is not. In terms of data integrity, rely on each staff member to be a stake holder in data integrity, meaning any congregant they interact with on a regular basis, volunteers especially, but also including anyone they minister toon a regular basis,to obtain up to date data (address, phone, email, etc.)

Create saved search criteria

Searches should be performed on a regular basis to maintain a clean, current database. This will make database cleanup much easier. The saved search criteria will make database cleanup tasks more efficient and more effective, saving your Database Champion valuable time.

Limit the number of people who add new individuals to ACS(creating a new record)

One of the best methods to keeping your data clean is to limit the number of people who have rights to create new profiles or add family members. Set guidelines for that select group of individuals on what fields must be populated when new profiles are created. Create accountability for that group of select users by conducting weekly searches on the entry date field of profiles created within the last week. Then customize the Search results screen to see at a glance all the fields that should be populated. Provide feedback as necessary when the policy guidelines aren’t being followed.

Another idea if you are an Access ACS user is to allow staff or leaders to add new records through Access ACS and leverage the change request as an opportunity to scan the newly created record. This would allow your Database Champion to make sure the proper fields are populated accurately, verify content is formatted correctly and avoid duplicating information that is already in the database.

Create a database policy/instruction manual

Set standards for how your staff uses all ACS fields. Create a policy/instruction manual with guidelines, to includestep by step instructions and train your staff on what they are responsible to know. (For example: processing an address changes.) Use the ACS Wiki as your guide to starting your own database policy/instructional manual.

Defining Involvement & Tracking Movement

How you define involvement (if someone is active or inactive) will depend on how your ministry uses ACS. For instance if your church has aligned many of your processes into ACS, using a variety of Modules, then your search for identifying inactive people could include leveraging the following areas to identify a congregant’s involvement:

  • Giving
  • Connections
  • Group Rosters - Activity Group, Class Group or Small Group
  • Attendance Markings

If congregant movement is important to the church then one of the best ways to track movement and accurately reflect an individual’s status is to leverage the Member Status Type classifications. Member Status type classifications should be defined in a way to help the church determine the actual status of the individual—are they actively involved in the church, only worship attendees, do they participate in activities, etc. The Member Status should be used as an assimilation guide for how well an individual is affiliated with the church. Then this can be leveraged during routine database cleanup to track a congregant’s movement.

Levering Processes to aid in data cleanup

Every church has existing processes, some are being tracked in ACS and some are not. Likewise, every church also has the need to create new processes to track strategies at the church. These processes help a church greatly in defining involvement and tracking movement as discussed early. In short it can help a church track your Discipleship Pathway, or create one, in ACS. Leverage existing and new processes and begin tracking those processes in ACS to aid you in identifying what fields or areas in a profile to examine during data cleanup. You can also leverage the data you gather in this stage for a designated Pastor or Director to use for follow up with Congregants.

Below are potential existing processesto consider:

  • Membership cleanup
  • How does your church define membership or inactive membership? In most instances Church “Bylaws” will define this. Put the bylaws into practice in your database.
  • Assimilation cleanup/Tracking movement
  • How does your church track movement of a guest or congregant?
  • Age youth to single
  • How often and what age range do you move people from child in household to their own household?

Below are potential new processes to consider:

  • Annual Church Census
  • A great process to leverage is creating an Annual Church Census campaign using different resources (paper, electronic, mass emails, etc.) to ask congregants and members to update their information.
  • Entry points – doorways someone enters the database
  • What are your entry points where someone fills out a guest card and that guest card is recorded in ACS.
  • First time gifts – what dollar amount constitutes a profile being created for a first time guest?
  • Are your follow up guest assimilation steps also being recorded in ACS to include possible responses?
  • Children and Student Ministries Guest assimilation
  • Adult Guest assimilation

Inactivating vs deleting

Adopt the philosophy to be liberal to inactivate a profile and conservative in deleting a profile. Inactivate profiles for a certain period of time (to be determined and defined by the church Database Management Team) and later delete the profile if the individual remains inactive for the set time period. Identify what the church archive procedure will be for records you will be deleting. For example, will you create a second dataset for archived records or will you export those archived records to be stored on the server.

Inactivating records could be based on the following criteria:

  • The individual is deceased
  • The individual has been dismissed
  • The individual requested to be removed from the church’s records
  • The individual has been inactive over a specified time period (determined by church)
  • The individual has transferred their membership to another church

Routine Database Cleanup:

Saved Searches

Save yourself heartache by creating saved Search Criteria to be used for routine database cleanup. Create a common naming convention for recurring maintenance searches.

Below are some naming conventionexamples:

Weekly Searches: MX WK “Name of Search”

Monthly Searches: MX MO “Name of Search”

Quarterly Searches: MX QT “Name of Search”

Annual Searches: MX YR “Name of Search”

Below is an example of saved search criteria:

MX MO Child to Head – MX stands for maintenance, MO stands for a monthly search and Child to Head is one of the suggested monthly routine database cleanup items.

Recommended frequency for database cleanup

Weekly

  • New profiles created in the last week.
  • Search criteria should be based on prior week Entry Date (Ex. date range of 01/01/2013 thru 01/07/2013
  • Customize Search results to view missing data at a glance for blank data fields (incomplete profiles) that should be populated when creating new profiles:
  • Missing gender
  • Missing marital status
  • Missing member status type
  • Missing family position
  • Missing Title (Primary or Secondary record)
  • Missing address, city, state or zip code

Monthly

  • First Time Guests - Tracking a guest’s movement after three visits. (Example: Changing Member Status Type from Guest to Attendee). Define what your process is, leverage ACS to track the process and to identify when a guest has transitioned to a regular attendee.
  • Deceased Individuals –Throughout the month as you are notified of a congregants passing, ensure that the Database Administrator has been informed and that action is taken to inactivate the deceased individuals profile in ACS.
  • Minors becoming Adults - Perform searches to identify young adults who have reached the age as defined by the church, for example age 21, of becoming an adult the previous month and separate their record from their parents household. If you are using a distinct marital status for minor-aged singles, be sure to change that to the adult-age single status.

Quarterly

  • Leverage attendance markings to identify inactive individuals from the prior quarter. Define what is considered inactivity after how many absences.
  • Below are two groupings to consider for quarterly cleanup:
  • Group 1: Profiles with no contribution history:
  • Delete profiles with no contribution history, no activity/small group involvement, including attendance, or no connections for the last 12-24 months
  • Group 2: Profiles with contribution history: (these are the profiles you will typically keep inactive for a longer period of time based on your Business Administrators guidance on how long the church needs to keep the contribution history in current dataset. After a specified time these profiles should be archived and deleted as profiles with gifts and dated.)
  • Inactivate profiles who have no gifts in the last 12 months, no activity group/class group, small group involvement, including attendance, no current year connections, or Date Last Changed is over two years old
  • A good time for bulk Semi-Annual cleanup would be at the end of March and at the end of October.

Annually

  • Annual Purge time! A good time for the Annual purge would be June. Define what month is best for the church to conduct the big annual purge.
  • Grade Promotion (For Children and Youth Ministry) This is best between June and August before the school year or programs begin again.
  • Document what the procedure and policy will be for Grade Promotion of grade level classes groups.
  • Who performs the task of Promotions
  • What month/date of the year will this take place
  • What are the steps that will be followed
  • How it will be communicated to those departments affected

Steps to follow to Inactivate Profiles:

Below are steps you can customize for your Database Champion to use during Database Cleanup once you identify profiles to be inactivated or periodically when you’re notified that an inactive congregant has become active again.

After performing searches check the following before inactivating the profile:

  • Check Connections to see if any recent connections have been made by the individual in the last 6 months.
  • Check Comments to see if there were recent comments in the last 6 months.
  • Check Group activity to determine if involved in a volunteer group, class or small group.

If inactivating due to death or the individual left church, also add a Database Update comment indicating the reason for inactivating profile as well as drop the individual from any rosters they may be a part of.

  1. Locate the individual’s profile.
  2. Go to the Profile tab and change the following:
  3. Change Member Status Type to Inactive (If using this Member Status type.)
  4. Check the Inactive box
  5. Go to Additional Fields Tab (If using these fields)
  6. Date Deactivated field: enter the current date
  7. Reason Deactivated field: enter Database Cleanup
  8. Click Ok in the top right corner.
  9. Repeat the steps to inactivate profiles for any other family member profiles within the household.
  10. Comments tab: If reason deactivated warrants a comment being added.
  11. Groups tab: If reason deactivated warrants dropping an individual from a roster.

ACS Technologies | Database Cleanup Best Practices / 1