Import new retirees

Once a calendar quarter, about the 7th of January, April, July, and October, send an email to Katie Aguilar in ASU Human Resources. Ask her to run the process to generate a file of people who retired during the past quarter. She will send an Excel spreadsheet in response.

When you receive the spreadsheet:

1. Rename it to yyy_nQ_Retirees.xlsx, where yyyy is the calendar year n is the quarter of the calendar year just completed (1 for Jan-Mar, 2 for Apr-June, 3 for Jul-Sep, 4 for Oct-Dec). 
2. Upload a copy to the QuarterlyImports folder in the Wild Apricot file management area. This will provide something to start over with if necessary, and also provide a record for future reference. 

To avoid duplicating member records and thus creating confusion, it is necessary to compare the names in the spreadsheet to names in the Contacts list in Wild Apricot. See Navigating WA's public and admin menus if you have trouble finding the Contacts list. See Member Profile Update if you need help making changes to records in Wild Apricot.

To find active members, use the simple search and no filter. Type in last name. If you see someone that looks like a match, look at both records to determine whether it really is the same person. As an example, if phone number matches you can be pretty sure it is the same person.

• If a match is found  make any updates to their record in the database that seem appropriate given the spreadsheet data. For example, make sure Date Retired is present and correct in the Wild Apricot record. Delete the person from the spreadsheet so they will not be added as an introductory member.

To find archived records, use the simple search and a filter of "Archived (excluded elsewhere)". If you find a match, look at the Internal use notes for the person (on the Contact details tab).

• If you see that status has been changed from active to lapsed, delete the person from the spreadsheet so they won't be included as an introductory member.
• If the notes don't show any indication of previous membership, delete the person from the archive (delete button at the top of the person's record), and leave them in the spreadsheet so they will receive introductory membership status.

Make sure the data in the spreadsheet is what is needed for the import process. Columns should match the following list. If a column is missing, ask HR for a new file. If columns are in a different order, cut/paste in Excel until they are in the shown order. Save after making any needed changes:

A = EmplId

B= Last Name

C = First Name

D = Middle Name

E = Department Descr

F = Job Indicator

G = Job Title

H = Retirement Date

I = Orig Hire Date

J = Address1

K = Address2

L = Address3

M = City

N = State

O = Zip Code

P = Country

Q = Empl Class Descr

R = Dept Hr Vp Collg Campus

S = ASU Campus Affil

T = Email Address

U = Home Phone Num

V = Local Phone Num

W = Cell Phone Num

X = Descr (This contains the “Major Unit”, e.g. College)

 

The data as we receive it from HR is not all formatted in a way that will allow it to successfully import to our member database. The following changes have to be made to the spreadsheet to rectify this:

1. Select the "Middle Name" column D:
   
   
   
   Open the Replace function (in the Find & Select menu of the Editing block). Click on the Options button and then check “Match entire cell contents”. Put a hyphen (-) in the Find what box and leave the Replace with box empty. Click on Replace all. Uncheck the “Match entire cell contents”.
2. Select the Address2 column K:
   
   
   
   Open the Replace function (in the Find & Select menu of the Editing block). Click on the Options button and then check “Match entire cell contents”. Put a hyphen (-) in the Find what box and leave the Replace with box empty. Click on Replace all. Uncheck the “Match entire cell contents”.
3. Select the Country column P:
   
   
   
   Use the Replace function to replace all "USA" with "United States".
4. Select the Empl Class Descr column,Q,
   1. Use the replace function to replace all "Classified" with "Staff"
   2. Use the replace function to replace all “Academic Prof w/Admin Appt” with “Academic Professional”.
5. Select the Dept Hr Vp Collg Campus column R:
   1. Open the Replace function (in the Find & Select menu of the Editing block). Click on the Options button and then check “Match entire cell contents”. Put a hyphen (-) in the Find what box and leave the Replace with box empty. Click on Replace all. Uncheck the “Match entire cell contents”.
   2. Use the replace function to replace all "Other AZ" with "Other Arizona".
6. Select the Asu Campus Affil column S.
   1. Use the replace function to replace all "DTPHX" with "Downtown Phoenix"
   2. Use the replace function to replace all "Poly" with "Polytechnic"
   3. Use the replace function to replace all “ONLNE” with “Online”
   4. Use the replace function to replace all "OtherAZ" with "Other Arizona".
   5. Select Column E, Department Descr” and the Find command to look for “ABOR”. For each person who has ABOR in the Department Descr, change the “ASU Campus Affil” to “Board of Regents”.
7. Create and fill new column T, Campus.
   1. Copy the Email Address column T to the first empty column, which should be Column Y.
   2. Make sure column T is selected, then right click and choose “Clear Contents”.
   3. Type the heading "Campus" in column T.
   4. Place the following formula in cell T2: =IF(S2="",R2,S2).
   5. Copy the formula just entered down for all the rows: Select cell T2, then drag down through the last filled cell in the column to select all of the cells in the column except the header. Type ctrl-D to fill all the cells selected with the formula from cell T2. Every person should now have a valid campus name in column T.
8. Select the Home Phone Num column U:
   1. Open the Replace function (in the Find & Select menu of the Editing block). Click on the Options button and then check “Match entire cell contents”. Put a hyphen (-) in the Find what box and leave the Replace with box empty. Click on Replace all. Uncheck the “Match entire cell contents”.
   2. Use the replace function to replace all “/” with “-“.
9. Select the Cell Phone Num column W:
   1. Open the Replace function (in the Find & Select menu of the Editing block). Click on the Options button and then check “Match entire cell contents”. Put a hyphen (-) in the Find what box and leave the Replace with box empty. Click on Replace all. Uncheck the “Match entire cell contents”.
   2. Use the replace function to replace all “/” with “-“.
10. Save the modified Quarterly Retirees spreadsheet (which you have named yyyy_nQ_Retirees.xlsx ) on your hard drive, but keep it open.

To have a successful import to our member database we need a spreadsheet that has headings that match field names in the database and one that creates default values for some fields. To do this, we need to copy data from the spreadsheet we have been working on (called the Quarterly Retirees spreadsheet) to a new spreadsheet and fill columns with default values. We will refer to the new spreadsheet as the Quarterly Import spreadsheet.

Open the Quarterly Import Template. This is a spreadsheet named QuarterlyImportTemplate.xlxs that is stored in the QuarterlyImports folder of the file section of Personify/Wild Apricot Files. Save it to your hard drive with a name you will recognize, such as yyyyQnImport, where yyyy is the current year, and n is the current calendar quarter.

Copy data from the Quarterly Retirees spreadsheet to the Quarterly Import spreadsheet. For each source column listed below in Table 1, select all the data except that in the header row, right-click somewhere in the selected column and choose Copy. Place your cursor in the 2nd cell of the target column in the Quarterly Import spreadsheet and type Ctrl-V.

Table 1: Mapping columns from Quarterly Retirees to Quarterly Import

Note 1: If there is a non-blank Address2 or Address3 in the Quarterly Retirees spreadsheet for someone, hand-enter these fields after the first line of Street Address in the Quarterly Import sheet. You add a line by pressing Alt-Enter at the end of the row after which you're adding something.

Note 2: When copying from column T - Campus, use "paste values". Otherwise you may be pasting the formulas, which will not give the desired results.

Create and copy default values for the remaining columns in the Quarterly Import spreadsheet. For each column listed in Table 2 below type the indicated value in cell 2 (almost all already have this value in it), then drag down through the last filled cell in the column. This will select all the cells in the column except the header. Type ctrl-D to fill all the cells selected with the value from cell 2. Every row should now have a valued in it that matches the value in the first non-header row.

Table 2: Fill values from quarterly import columns

Note 1: The Renewal Due date should be July 1, yyyy, where yyyy is next year. For example, for the January 2022 import, the date would be July 1, 2023.

SAVE your completed Quarterly Import spreadsheet.

From the Contacts menu (available when in Admin view in Wild Apricot), choose Import. In the Import contacts page that opens, Browse to the Quarterly Import spreadsheet that you created in the previous step, then click on "Upload". 

In the import mapping step that follows, look for any problems. If there are some, you can cancel the import and get them fixed before proceeding:

• If any field names are struck through, it means that field name doesn't match anything in the database. In that case, review the headings in your spreadsheet against the names in the Quarterly Import file and make any corrections to the Quarterly Import heading that are needed to get a match. Note that you may also need to change the Quarterly Import Template in the Wild Apricot files if the problem came from there.
• Look at the samples of what will be imported, listed below each field name. Make sure they look right for the field. If they don't, check that the data is in the correct columns in the spreadsheet.

Complete the import and check your results.

• The total number of records processed should match the number of people in the spreadsheet that you imported, and they should all be new members. If there were fewer new members than the total number of records processed there were some matches to the existing database that were missed in the step titled "Prevent duplicate member records". You will need to research that to see whether this resulted in someone being added as an introductory member who should not have been.
• If the import results indicate that there were problems, research those. Assuming the headings in your spreadsheet were all correct, the next most likely problem is that there were some values in records that didn't match the expectations in the database. It may be that HR has added some new values for things like Campus or Employment Category, and we would need to update the field definitions in our database or these instructions to accommodate. If you can't resolve problems, consult with the Technology Manager.
• If you think you may have imported duplicates, you can find them as follows:
  • Export the entire contact list.
  • Open the resulting file in Excel (it will be where you have set your Browser to store downloads, by default usually in the Downloads folder for a Windows computer).
  • In Excel, sort the sheet on last name and first name, then insert a column that contains a formula that creates a value of "bad" or "good". For example, if name is in columns B and D, as it normally will be, then in your new column use the formula =if(b2&d2 = b3&d3,"bad","good"). Make sure you pull it down to fill all rows.
  • For ease in searching the result, apply highlighting to the column in question. Directions for doing this can be found by searching Excel help on "conditional formatting". If you find duplicates either merge them in Wild Apricot or delete the one that just got added.

Wild Apricot does not have a means of sending automatic notices to imported members. We therefore use a Wild Apricot email blast to welcome them. 

1. Navigate to the Saved Contacts Search entitled ImportedMembersEmail.
   
   Click on the saved search and change the Member Since date to the date on
   
   which the members were imported. Save the revised search under a new
   
   name, such as yyyy Qn Imports, where “yyyy” and “n” match the year and
   
   quarter during which the people retired.
2. Navigate to Email templates. Click on the template named "Imported New Members Welcome Letter," then click on the "Send email" button at the top.
3. Click on the "4. Recipients" tab. You don't need to do any design or preview, so you can skip tabs 2 and 3. Click on "+Contact list", and then in the column "Saved contact search" check the box next to the name of the search you saved above (e.g. 2022 Q1 Imports). Then click on "Add selection" at the bottom of the "Add contact list" window.
4. Click on "Change reply to", and in the Select contact window type "asura" (without the quotes) in the search box. You should see "ASURA, Contact". Click on it - it will be highlighted in green -- then click on the Select box at the bottom of the Select contact window.
5. Make sure the checkbox next to "Enable link and open email tracking" is checked, and then click on "5. Review and Send" in the gray menu bar. Review the number of recipients to make sure you have what you expected, then if all is well click on the green Send button.
6. Delete the saved search that you created in Step 1. It won't be needed again.

If any newly-imported members had bad email addresses the welcome sent by email in the prior step will not reach them, so those people should receive paper welcome letters by US mail. 

Therefore, you should notify the Membership Operations Manager that the import has been completed, and alert them to the need to check for bad email addresses and send a paper welcome letter.

ASURA leadership wants the opportunity to personally welcome new introductory members to the organization. To facilitate this, we send a list of new members each quarter when the import process is complete.

You can send the Excel spreadsheet received from HR or you can print a custom roster using a selection of everyone whose "member since" date is the date of the import. If you do that, name it something like yyy_nQ_RetireesList.pdf, where yyyy is the year and n is the quarter that the list pertains to. Upload it to the QuarterlyImports folder in Wild Apricot.

To send the list:

1. Navigate to Email templates using the dark gray menu's "Emails" and the light gray menu's "Email templates". Click on the template named "New Retirees List", then click on the "Send email" button near the top.
2. In the email body, start a new paragraph at the end of the text. Then:
   1. If you are going to send a file, click on the "File" icon in the gray stripe above the email. It looks like a paperclip and is labeled "File". Click on the "resources" link at the top of the "insert file" window that opens, then navigate to the Excel or PDF file that contains the roster. This will insert a link in the email.
   2. You can alternatively copy the list of new members into the email from the Excel list. If you do, please be sure to include full contact  and identification information: name, address, email, major unit, minor unit, employment type, and phone.
3. Click on the "3. Preview" tab at the top of your window to make sure the email looks the way you want it to, then click on the "4. Recipients" tab. Click on "+Contact list" and then in the column "Saved contact search" check the box next to "ASURA Board Meeting".. Then click on "Add selection" at the bottom of the "Add contact list" window.
4. Leave "Reply to" information alone - it should have your name and email in there, so you'll receive any replies that a Board member or committee chair wants to make.
5. Make sure the checkbox next to "Enable link and open email tracking" is checked and then click on "5. Review and Send" in the gray menu bar. Review the number of recipients to make sure you have what you expected, then if all is well click on the green Send button.