Use this link to share with your colleagues:
Diagnostic Reporting: How Healthy is Your Data?: https://help.pm.leapevent.tech/a/1669134
Clean data is incredibly important to your organization because you depend on it for just about everything you do. It's hard to run a mailing list if the State field isn't filled in correctly, and it's downright impossible to get an accurate donor list for your program book if Donations aren't credited correctly. But you have thousands of records - many of them years old! How do you even begin to check for data cleanliness?
The answer: diagnostic reporting.
In this article, we'll cover:
- How to think about diagnostic reporting for your organization
- Good reports to start from, and where to go from there
- What do what to do if you find something you need to fix
Let's get started!
Whoa there, partner! This article assumes that you already have a pretty good handle on reporting. If you need a refresher, we recommend reviewing the following articles:
What's diagnostic reporting, and how do I use it?
Diagnostic reporting uses reports to show you where you data might need some cleanup. Maybe you're missing the Formal Address or Informal Address fields for a lot of your records, or maybe there are a lot of misspelled or improperly formatted Street Addresses.
In short: if you're not sure how clean your data is, then diagnostic reporting will help you to find out where problems are (or if there are any!).
The key to diagnostic reporting is to think about what you use your reports for, look at the filters and output for those reports, and then ask yourself:
- What problems with my data would make this report appear incorrect?
- You're looking at your Campaigns to see how your development initiatives have performed this year, but not all of your Donations have a Primary Campaign Source filled out, so you're not getting accurate roll-up values in your Campaign hierarchy.
- Am I using any reports and then correcting data in Excel or Google Sheets later on a regular basis?
- You're creating mailing lists, but you need to correct capitalization in your Street Addresses, replace "street" with "St.", and fill in Formal Address names.
What kinds of reports should I start with to find the data I need to fix?
We suggest starting with running reports that contain the data you use the most. Below, we have some specific reports that we think are particularly helpful!
Mailing Reports
If you send out hard copies in the mail, like flyers, acknowledgment letters, tickets, gala invitations, etc., we recommend running these reports to make sure your address labels are nice and clean.
If you want to make sure that States, State abbreviations, and Countries are also standard, we recommend setting up State and Country Picklists!
To find Accounts that are missing name fields for mailing labels or envelopes, we recommend running a report:
- Using the Accounts report type
- Setting filters to find where the field you need may be empty
- In this example we've added the Formal Address Name, Informal Address Name, Formal Salutation, and Informal Salutation. You can use the field(s) that you use!
To find Accounts that use full Street Addresses instead of abbreviations (i.e. the Account has "Drive" instead of "Dr.") and fix them up, we recommend running a report:
- Using the Accounts report type
- Setting a filter for Billing Street not equal to ""
- This will filter out any Accounts that don't have an address
- Setting a filter for Billing Street contains drive,lane,street,circle,place,avenue,alley,boulevard,highway
- You can add additional common Street Addresses as you see fit
Pro-tip! If you want your addresses to have full Street Addresses instead of abbreviations (i.e. you want to see "Drive" instead of "Dr." for addresses), change the second filter to
Billing Street contains dr.,st.,cir.,pl.,ave.,aly.,aly,blvd,hwy
To find Accounts that don't have a Zip Code or Postal Code, but do have a Billing Street, we recommend running a report:
- Using the Accounts report type
- Setting a filter for Billing Street not equal to ""
- This will filter out any Accounts that don't have an address
- Setting a filter for Billing Zip/Postal Code equals ""
Donation Reports
Depending on how you track Donations, it can be helpful to make sure that the fields you want are filled in. While we have some suggestions below, your organization may have other specific fields that you use for tracking, and you can apply these same concepts to other fields!
While the Donor field should always be filled in on Donations to connect Donations to Contacts, if a Donation is entered without one, you might exclude patrons from your Contact-based reports.
To find Donations that don't have a Donor we recommend running a report:
- Using the Donations report type
- Setting a filter for Donor equals ""
If you use Campaign to track your development efforts, it's important to make sure Donations have the Primary Campaign Source field filled in. This will allow your Campaigns and Campaign hierarchy to accurately calculate the efficacy of your development efforts.
To find Donations that don't have a Primary Campaign Source we recommend running a report:
- Using the Donations report type
- Setting a filter for Primary Campaign Source equals ""
If you categorize your Donations via the Fund field or a GL Code field, you'll want to make sure that those are filled in for all of your Donations.
To find Donations that don't have a Fund or GL Code we recommend running a report:
- Using the Donations report type
- Setting a filter for Fund or GL Code equals ""
- In this example, we've added filters for both fields; and our GL Code field is titled "General Ledger Code"
Event Reports
If you and your finance or accounting team use GL codes to keep track of revenue from your performances, it's important to make sure that you fill in the GL Code on every Ticketable Event.
To find Ticketable Events that don't have the GL Code filled in, we recommend running a report:
- Using the TEs with EIs report type
- Setting a filter for GL Code equals ""
- In this example, our GL Code field is titled General Ledger Code
If you use the Event Category picklist on your Ticketable Events for reporting or to categorize events on your Public Ticketing Site (PTS), you should make sure that the Event Category field is filled in for all of your events.
To find Ticketable Events that don't have an Event Category, we recommend running a report:
- Using the TEs with EIs report type
- Setting a filter for Event Category equals ""
How do I fix what I found?
Depending on the type of data you need to fix, there are a few different ways to fix any oversights that you catch in your reports.
1. Field Editing in Reports
If the data you'd like to edit is a Text field and is available for editing via your page layouts, you can make any necessary adjustments right from your report if you Enable Field Editing!
From our example reports above, the following can be fixed with Field Editing directly from the report:
- Formal Address Name, Informal Address Name, Formal Salutation, and / or Informal Salutation
- Street Abbreviations
- Zip / Postal Codes
We're so glad you asked!
1.1. From your report, click Enable Field Editing
1.2. Hover over the cell you want to edit and click the pencil icon
1.3. Edit the information
In this case, we edited "25 Naptown Drive" to "25 Naptown Dr."
1.4. Click out of the cell
1.5. Repeat steps 2-4 for any other cells you want to edit
1.6. Once you've made your changes, click Save
1.7. Click the Refresh icon to refresh your report results
2. Manual Editing
If the data you'd like to fix can't be edited with Field Editing in reports, and if you have fewer than 25 records that need to be fixed, we recommend making the edits you need manually.
To manually make edits, go straight to each record and edit them there.
3. Data Update
If you have a lot of records to fix, or if Field Editing from the report isn't possible, you can do your updates in bulk via a data update. To do this, you'll export your report to Excel, fix the data, then batch update the data.
Want to learn more about making batch updates? Check out our DIY Data resources!
3.1. Make sure there's an ID number on your report
If there isn't you can add a column for the record IDs you need to adjust.
3.2. Export the report to Excel
When you export your report, make sure to export it with these settings:
- Export View: Details Only
- Format: Comma Delimited .csv
- Export
3.3. Adjust your data in Excel
Here, you can correct misspellings or capitalization errors, fix text formatting, add IDs for lookup fields, or add picklist values. Whatever you need to update, now's your time!
If you're updating a lookup field, like Donor or Primary Campaign Source, you'll use the ID for that record in Excel.
For example, when updating a Donor on a Donation, you'll use the ID for the donor's Contact record. On the other hand, when updating the Primary Campaign Source on a Donation, you'll use that Campaign's ID.
We go over finding a Campaign's ID here. The process is the same for Contact records, only you'll grab the Contact ID by navigating to each individual Contact.
We recommend keeping a column in your Excel sheet for your old values (what you're fixing) and your new values (what you fix the data to). This is a great safeguard to have in place just in case you need to revert back to the old data for some reason.
Want some Excel tips and tricks? We've got your back.
3.4. Save your Excel file
3.5. Use your file to update your data in PatronManager via Dataloader
You'll match up the column header to the field you need to update and use the ID numbers you exported as the key field. This turns hours worth of updating data manually into just a few clicks of a button.
Doing this will make a mass update to your data - so exercise caution here! Make sure that your data is correct before uploading it.
For specifics on using Dataloader, we have a video for you here.