Use this link to share with your colleagues:
How to Clean Up Your Email Data: https://help.pm.leapevent.tech/a/982192
Your first step when getting ready to migrate to a new email system is cleaning up your PatronManager email data. This article will walk you through the steps you'll need to take to clean up this data on your own.
Are you in the middle of a historical data import? No need to read any further; our data team will help walk you through this project, and you're not expect to complete it on your own.
A) Create and export a report of your Contact records
In order to deduplicate our email addresses, we first need to know the email addresses related to all the Contact records in your PatronManager account. Let's pull that report.
1. Navigate to Reports
2. Click "New Report"
3. Select the Contacts & Accounts report type and click "Continue"
4. Click on the "Filters" tab
5. Set the Show Me menu as "All Accounts" and the Created Date menu to "All Time"
6. Add a filter for the Email field, and set it to Email is not equal to (Blank); when you're done, click "Apply"
7. Click onto the "Outline" tab
8. Add the Contact ID field to the columns list
9. Click "Save & Run"
10. Fill in a report name and select a report folder for this report to live in; then, click "Save"
11. Click the downward arrow, then click "Export"
12. Click "Details Only", then click "Export"
B) Deduplicate the email addresses in Excel
In order for your email marketing data to sync back and forth from Emma and PatronManager, each email address in your database can only be assigned to one contact. Otherwise, you might end up updating John Smith's record with Jane Smith's name, and nobody wants that. In this step, we'll eliminate any duplicate email addresses associated with more than one contact. To do that, we're removing any rows that already have unique email addresses, and then adjusting rows so that each email address in the file is unique.
In the following steps, we're working within Microsoft Excel. These steps are also possible using other spreadsheet programs, such as Google Sheets, but you'll have to follow the general outline rather than the specific steps.
These screenshots were taken using Windows Microsoft Excel 2013; if you're using another version, you may find the icons or menu options in slightly different locations.
1. Open the report in Excel
Click on File...
Then Open...
Then, find the report file you've just downloaded from PatronManager.
2. Select the first row
3. Click on the Sort and Filters icon, and then clicking "Filter"
4. Click on the downward error on the Email Address column, and click "Sort A to Z"
5. Highlight the entire Email column
6. Click on the Conditional Formatting menu. Click on Highlight Cell Rules, then on Duplicate Values
7. Click "OK" on the pop-up
8. Click on the downward arrow on the Email column, then "Sort by Color". Select the fill color for the duplicate value cells
9. Highlight the rows that do not include duplicate values and delete them
10. Choose one Contact to keep associated with the email address per group
Your report will provide you with two scenarios:
1. Multiple Contacts with the same email address, but different names
These Contacts likely came from your old database and were never fixed up in PatronManager.
For the purposes of this project, you will need to select one Contact record to retain that email address and delete the email from the others, as pictured:
2. Multiple Contacts with the same email address and the same name
These contacts are "true" duplicates.
For the purposes of this project, the bare minimum cleaning you will need to complete for these records is to select one Contact record to retain that email address and delete the email from the others, as pictured:
For these records, you may also want to merge their Contacts and / or Accounts in PatronManager - but please wait to do so until you've uploaded the updated Contact records. Once that's completed, you can jump over to our How to Merge Accounts and Contacts article for detailed instructions.
11. Repeat this process until each email address is only represented once
12. Save your file as a .CSV file type
C) Update your Contact records using DataLoader.io
Now that we've cleaned the data up in Excel, we need to update the records in PatronManager with DataLoader.io. Let's go for it!
Updating over 7,500 records? You'll need to use the Salesforce.com Data Import Wizard.
2. Click "Login with Salesforce"
3. Make sure the Environment is set to "Production", and click "Login"
4. Click "New Task", then "Import"
5. Click "Update"
6. Select "Contact" from the object menu, then click "Next"
7. Click "Upload CSV" to upload the file you adjusted in the previous step
8. Map your fields
You only need to map two fields - the Contact ID and the Email Address. For any other fields, click the trash can to delete the mapping.
This is roughly what you should see before you click "Next".
9. Click "Next"
10. Click "Save and Run"
11. Wait for the update to complete, then check for errors
If you see the checkmark, everything went according to plan and you're all done!
If you see the red exclamation point, you need to click on the section that says X errors - in our example, it says 2 errors. Click on the X errors link. This will download a file of the results from your upload, and help explain why you had errors in your update. Fix those errors in your source file, and try again.
If you had successes and errors in one update, you do not have to re-upload the records that were successful! Just re-upload the ones that had issues. If you do re-upload the successful records, you'll just be duplicating the work you've already done.
12. Boom! Your data is ready for Emma!
