04. Data Cleansing Methods

Introduction

Depending on the organization and the type of data that is being gathered, different levels of data cleansing may be desired. Some organizations analyze the raw data from the field, and others want to review each and every submission before it's committed for reporting. Here are a few ways you can meet various levels of data cleansing requirements.

A. Audit Reporting- set up automated reports to look for errors and inconsistencies. As example let's set up an audit report to highlight when a numeric field falls outside a certain variance.

For the following example, we'll use our make believe company, Aquacomb. Mobile users in this organization visit well-site members to determine how many times the member visits the well each month. The TaroWorks admin is going to set up the following tools to ensure she's analyzing clean data.

A. Audit Reporting

Our TaroWorks Admin wants to take a look at spikes and valleys in well-site visits to ensure the mobile user didn't just enter a typo. She'd like to start out by looking at any check-in records where the number of times the well site was visited is greater than a 100% variance from the average for that member.

Create a roll-up summary on the Member object to sum the number of times the member visited the well.

Create a roll-up summary on the Member object to count the number of times a Member has been visited.

Create a formula field on the Member object to display the average number of days the member visited the well per month.

Create a formula field on the Well-site Visit object to determine the variance of this month's visit is, from the average for its member.

Create a Report, with a filter to only show visits where the variance is greater than 100%.

Schedule this report to be run and sent to the admin every Monday morning.

B. Individual Approvals

The TaroWorks admin has just trained a new batch of mobile users, and wants all data to be reviewed before showing up in reports to be shared with Donors. She can achieve this by creating a field, Review Complete, on the records being created by mobile users, and filtering any reports such that only records where this field is checked will appear. When a manager has recorded that they have reviewed the data from the field, it will then show up in donor reports.

Create a checkmark field on the Member Visit object.

Create a View for field visits that need to be reviewed so the Field Manager can use it as a work queue.

For any reports that should only contain "clean" data that has been reviewed, create a filter on the Reviewed field.

C. Send Validation/Correction Requests to the Field

When the field manager does find issues with data submitted by a mobile user, you can build a Job that allows a the Field Manager to push the record back to the mobile user.

Create a text area field on the Well Site Member Visit Object for Admin comments to the Mobile user and another for Mobile User comments to the Admin.

Create a lookup relationship field to the User object, to indicate which mobile user should correct the field data.

Create a single-select field to indicate which records need to be sent back to the field.

Create a Form that will display the data that was submitted, as well as questions for corrections.

Create a Job where the drill-down hierarchy filter only displays records flagged for corrections.

Assign the Mobile User their own user record, so that they'll only see the records assigned to them to fix.