Email and Name Clean Up

The exported worksheet includes more fields than you will import back into Altru. Only certain fields are “collection fields” that you will edit to meet standards, others can be deleted, and some are retained for constituent matching.

Also note that exported column headers do not match the import headers. Column headers need to be replace from import header template prior to importing back into Altru.

Columns that should be reviewed and edited to standards.

• First name
• Last/Organization/Group/Household name
• Email Address
• Email type

Process Steps:

1. Go to Analysis Menu > Information Library > Data Cleanup folder
2. Click the hyperlinked query name Primary Email Type Cleanup 2023
3. Click the Export to Excel button and select Export to .XLSX file
4. In the worksheet, delete the following rows (you can also save this step until the end and use as reference while editing addresses)
   1. Email Addresses\Date added
   2. Name (combined column with First and Last)
   3. Email Addresses\Information source
   4. Email Addresses\Information source comments
   5. Email Addresses\End date
   6. Email Addresses\Start date
   7. Email Addresses\Origin
   8. Email Addresses\Origin information
   9. QUERYRECID
   10. Email Addresses\Bounced (check all YES rows for possible corrections to email address prior to deleting)
5. Edit the Update columns as necessary. (see details below)
6. Open Email import header template to copy column headers and paste into your worksheet.
7. Save file as .CSV in this format: 2024.03.08 email import.csv
   1. Save worksheets in Box-Box/Altru Processes/Altru_Data Management Guidelines/Data Cleaning
8. In Altru, go to Administration > Import
9. Click the double green arrows for the Import Process: Email Type and Standardization
10. Click the Edit button
11. Select Local file as the Import Source
12. Click the green arrow to upload a local file from the shared folder in Box
13. Click Next to Map Fields
14. Use the Auto Map button to map the column headers of the excel sheet to the Altru constituent fields.
15. Select the Address row of “Collection fields” and then click Map Collection Fields
16. Use the Map Fields button in the pop-up window.
17. Click Save and Next to move to review the Import Workflow options
    1. Only “Validate new batches but do not commit any records” should be selected.
18. Click Save to continue and return to the Import processes screen
19. Select the double green arrows for the Address import process again, and click the purple Start Import icon.
20. Once the Import is started, the import status page will open showing the process details and completion time, as well as the number of errors found in the worksheet.
21. Once complete, click the Go to batch entry link at the lefthand Tasks column
22. Click the double green arrows next to your new batch to open details
23. If there are exceptions found, click the hyperlinked text Errors from validation to review issues with your import data.
24. Click “show all” to see the error messages.
25. The most common error is that the address already exists for the constituent. If that is the only error, you can proceed to commit the batch. Exit the pop-up to return to the Batch entry window.
26. Use the green Commit button to import the updated addresses.
27. An exception batch will be created holding any entries with errors. Open that batch and review errors to determine whether the constituent address is OK as-is, or resolve them directly in the constituent record.

Columns to Review and Standardize

1. First Name
2. Last Name

Check for letter case (i.e., all lowercase, all caps)

Use this formula in a new column at the end of the worksheet to review name columns. The highlighted text is the reference to your name columns, change as needed.

=SUMPRODUCT(LEN(E2)-LEN(SUBSTITUTE(E2,CHAR(ROW(INDIRECT("65:90"))),"")))

Apply the filter option to your worksheet.

Copy the formula above into the second row and copy down the column for all rows. Double click the + symbol or click and drag to copy down. (The highlighted cell will automatically update the row number in the formula.

Use the Ascending Sort for this Caps Count column to bring rows with all lowercase to the top for review. Use Descending sort to find names in all caps. Edit the names into proper case as needed and then delete the count column.

1. Email address
   1. Use the email bounced column to identify any email addresses with typos.
   2. Check for common typos like
      1. .con
      2. Gnail.com
      3. Yagoo.com
   3. Email type
      1. Personal
      2. Business