Lauren Gilchrist Data Wizard Tweet 10 Ways to Keep Your Data Clean I’ll be honest. I wish I could tell you a magic spell that, when spoken in a clear voice and directed toward your computer, would instantly clean up all of your data. Woosh. Done. Unfortunately, that’s not the case. So instead, I’d like to bring you the next-best thing—an overview of some almost-magic Skyward tools that can help you clean up your data. But before we jump into ways you can create a clean database, let’s discuss some reasons why you may have “dirty” data in the first place. Unavoidable reasons Some things are simply out of your control. For instance, a high rate of employee turnover can lead to your district having an excessive number of active employees. (This is especially true for casual employees like substitutes.) When it comes to placing orders, changes in accounting can result in your district having a long list of available accounts, making it cumbersome for employees to identify the appropriate account and easy for them to make a mistake. Avoidable reasons Two major reasons for dirty data fall into the “avoidable” category. The first is incomplete data—when employees fail to enter all of the appropriate fields during data entry. The second is duplicate data. Say, for instance, a user creates a street name that already exists in the database under a slightly different name (Main St versus Main Street). How can we go about solving problems like these? Let’s dive into that next. Jump to Student Management Suite Solutions Jump to School Business Suite Solutions Student Management Suite Solutions Note: All of these features are available in SMS 2.0. Features that are also available in Qmlativ are noted by the addition of a Qmlativ menu path. 1. Tools for consistent address entry Addresses are a common culprit of “ugly data.” Street names, for instance, often lack consistency. Fortunately, Skyward has three tools to help keep address entries uniform: a. Address ranges: Address ranges will help you create standard, non-duplicated entries. To use this feature, you’ll need to first set up your ranges. SMS 2.0 menu path: Advanced Features > Address > Setup > Utilities (WS\AF\AD\PS\UT) Note that you’ll also need to enable the Address Validation field (Product Setup > Skyward Contact Access > Student Management > Configurations). When the Validate Using Address Range Defaults Table option is set to 'Yes,' a user will not be able to add a new address unless there is an address range defaults record in the system that allows for the new address. Qmlativ menu path: Demographics > Codes > Address Range Default b. Address utilities: If your addresses are already screaming to be organized, you’ll find several tools in the address utilities area that can help you clean them up. These options include: merge duplicate zip codes (primary zip only), remove PO box from street name, remove street number from street name, remove apartment from street name, remove direction from start of street name, remove direction from end of street name, standardize suffix abbreviations, and individual street name changes. SMS 2.0 menu path: Advanced Features > Address Setup > Utilities (WS\AF\AD\PS\UT) You can also use the Duplicate Address Merge tool to eliminate duplicate entries. If the update option is selected, duplicate addresses will be combined, occupants of those addresses will be assigned to the combined address, and unused records will be deleted. Note that the Report and Update process cannot be reversed, so be sure you have a current backup before updating! c. Security groups: A final, simple way you can control street name entry is via security groups. Set up your security groups to prevent the creation of new street names by users within those groups. 2. SkyCoder SkyCoder is an advanced feature that gives you the power to make Skyward your own. You can make changes to nearly any screen, customize field rules, and add additional data validation to screens where you feel it’s necessary. A custom-built system can go a long way in helping you keep your data clean! SMS 2.0 menu path: To learn more about this function, log in to SkyDoc and type in 'SkyCoder.' You’ll find several resources available to help you with this tool. 3. Tools for finding duplicate and mismatched data Here are 10(!) different reports to help you find and eliminate duplicate and mismatched data: a. Duplicate alphakey(s) report: This report bases its search on comparison options you choose, then displays a list of names that may be duplicates. SMS 2.0 menu path: Students > Reports (WS\ST\RE\DA) b. Duplicate Other ID(s) report: This report tallies and prints students that share the same Other ID. SMS 2.0 menu path: Students > Reports (WS\ST\RE\DO) c. Enrollment discrepancy report: This report identifies students who are enrolled over or under 100%. SMS 2.0 menu path: Students > Reports (WS\ST\RE) d. Grad year discrepancy report: This report reads through all students in the district, looking for entry/withdrawal records with entry dates greater than today’s date. Then it analyzes the records to see if entry/withdrawal dates overlap, or if the graduation years do not match. SMS 2.0 menu path: Students > Reports (WS\ST\RE) e. Duplicate family report: This report finds families who are listed as living at the same address and/or having the same phone numbers. The report also shows students attached to each family to aid in the verification and clean-up process. SMS 2.0 menu path: Families > Reports (WS\FA\RE) f. Duplicate email address report: This report prints names of the selected name type that share the same email address. You can also print a list of the names that have blank email addresses on file. SMS 2.0 menu path: Advanced Features > Name Maintenance > Reports (WS\AF\NA\RE) g. Student address mismatch report: This report shows student addresses that are missing or mismatched (not in sync with the primary guardian’s address). You can even configure the report to correct mismatched addresses as it finds them. SMS 2.0 menu path: Advanced Features > Address > Reports (WS\AF\AD\RE) h, i. Schedule and enrollment inconsistencies report, Suspect or questionable attendance report: These reports help you clean up attendance records. The first displays students whose attendance records show days when they either were not scheduled into a class or were not enrolled in an entity. The second analyzes records for a specified date range and looks for patterns of inconsistent attendance types that could be considered questionable. SMS 2.0 menu path: Office > Attendance > Reports (WS\OF\AT\RE) j. Suspension/expulsion and attendance audit report: This report identifies any discrepancies between discipline and attendance. SMS 2.0 menu path: Office > Attendance > Reports (WS\OF\AT\RE) 4. Tools for merging duplicate student and staff records Duplicates of staff and student records are a sure-fire way to make your data messy. These two tools can help you abolish copycats: a. Name Merge utility: When you’ve found duplicate staff names, you can use the Name Merge utility to combine those names into one record. (Note that this utility does not merge student information, only staff.) SMS 2.0 menu path: Product Setup > System Administration > Name Merge > Merge Process (PS\SA\NM\MP) b. Merge Student Records: When multiple name records exist for the same student, use the Merge Student Records process to merge all data from the source student with the target student. This process will merge family information (only target student data will be retained), classes, and grade history. In addition, you can use this utility to merge multiple records from different student name keys for the same student. SMS 2.0 menu path: Student Management > Students > Setup > Utilities (WS\ST\PS\UT\MS) Qmlativ menu path: Demographics > Utilities > Name Merge 5. EEL security measures EELs (lookup/dropdown fields) are prevalent throughout Skyward. Set up proper security measures so only authorized staff can add, change, or delete codes from the EELs. To make this change, find the lookup access for each security group and set it to 1. Bonus tip: Train your staff! Skyward is designed to stop duplicate record creation. Whenever a new name is entered, the system will automatically pull up possible existing matches. Teaching your staff to properly recognize and use this feature is an essential step on your way to keeping data clean. School Business Suite Solutions Note: All tools featured in this article are available for SMS 2.0 users. 1. Inactivate Employees/Subs If your district has a high rate of employee turnover, this feature will be especially useful for you. For employees marked in your system as inactive, use this tool to select which of their records should be made inactive (contracts, pay records, org charts, etc.). Inactivating these records will make your reports and utilities more accurate, since they’ll only portray employees who are actually working in your district. Menu path: Human Resources Dashboard > Employee > Employee Profile (WH\EP\TB\EP\PE) 2. Inactivate Vendors Purchasing merchandise from many vendors, or switching to new ones, can leave you with a lot of vendor options in your system. The Mass Inactivate tool is a simple way to review all of your active vendors and remove any you are no longer doing business with. Taking this step now should help you simplify the purchasing process down the road. Menu path: Financial Management Dashboard > Vendors > Mass Inactivate (WF\VE\MI) 3. Inactivate Accounts The Inactivate Accounts tool can help you solve the problems that tend to arise with changes in accounting. You can use this tool to inactivate accounts, as well as to test an account for activity before switching it to inactive. Menu path: Financial Management Dashboard > Account Management > Utilities (FM\AM\UT\AM\AU\IA) 4. SkyCoder Make incomplete data entry a thing of the past. Use SkyCoder to customize your Skyward experience. Set up the system to require, hide, change, or include custom fields. A custom-built system can go a long way in helping you keep your data clean! 5. Name Merge Use this tool to eliminate data duplicates, whether they take the form of an address (Fourth Ave, Fourth Avenue), or a name (Andy Smith, Andrew Smith). Menu path: Product Setup Dashboard > System Administration > Name Merge > Merge Process (PS\SA\NM\MP) Bonus tip: Train your staff! Skyward is designed to stop duplicate record creation. Whenever a new employee or vendor is entered, the system will automatically pull up possible existing matches. Teaching your staff to properly recognize and use this feature is an essential step on your way to keeping data clean. And there you have it—tons of tools to help tackle messy data! Start the cleanup process today and make mismatched, misplaced, and duplicate records a thing of the past. Follow up resource: Record retention advice While the clean-up process is in full swing, why not talk about record retention? To keep or to purge? That is the question we tackle in Record Retention 101.