STC Email List Training
How to purge non-members from a Lyris list

Each year at the end of March, STC deletes non-renewing members from the membership lists. Therefore it is good practice to purge STC email lists of non-members in April, when STC notifies you that the roster of members has been purged.

The steps are explained in each section.

Retrieve the list of subscribers from the list server

Send an email message to lyris@lists.stc.org with the following text:

login YOUR_PASSWORD
review LISTNAME names

Create a CSV file of the list of subscribers

You will receive an e-mail message that lists the names and e-mail addresses of all subscribers to the list.

  1. Copy the text of the message into MS Word.
  2. Delete the list summary text at the start and end of the message.
  3. Visually scan all the names to make sure they are in the standard format of (FirstName LastName). If you see anything besides the standard format (such as additional names, commas, or parentheses), make the necessary adjustments. No name, or just a first name is fine. Note: Some people have a two-word last name (such as “de” + name). If you see someone with a two-word last name, look for that person on the current membership list. If that person is on the membership list, delete the person from the review report. If the person is not on the membership list, delete the person manually from the email list.
  4. Do the following global-search-and replaces (they are needed to create the CSV file):

    Replace Space + open parenthesis With ,
    Replace ) with Nothing (that is, leave blank, or delete the comma)
    Replace Space with ,

  5. Save the file as a text file with the name emailist.csv.
  6. Close the file in MS Word.
  7. Open the text file of membership data sent by the STC Office > follow the wizard to create a spreadsheet; the data is comma delimited > save the spreadsheet as a CSV file with the name members.csv.

Add the year in the subscribers and current membership list

You should have two files, emailist.csv and members.xls, the Excel spreadsheet of the purged membership list you received from the STC Office.

  1. Open emailist.csv in Excel. Notice that there are three columns.
  2. Insert a blank row at the top. This row will be for the column names.
  3. Name the first four columns Email, FirstName, LastName, Year.
  4. Enter the previous year in each cell in the fourth column; enter the year in the first two rows, highlight both cells, and drag the handle (small black square in the lower left) down across all rows.
  5. Save the file with the name emailist.xls.
  6. Open members.xls. Delete the columns except FirstName, LastName, Email.
  7. Add a column with the name Year.
  8. Enter the current year in each cell.
  9. Save the file with the name currentmem.xls.

Use a database to merge the subscribers and current membership lists

Here we have no instructions, but would use input .csv files with the year from the preceding steps.

Create a report of non-renewing members

Here we have no instructions. This would be an output report from the database.

Compare emailist.xls and currentmem.xls and delete the current members

These steps are manual. If they can be automated, we would like to know how to set that up.

  1. Open emailist.xls.
  2. Open currentmem.xls.
  3. From the Excel Window menu, select Compare side by side.
  4. From the emailst.xls file delete the members who also appear in currentmem.xls. Periodically save the file as emailistdel.xls.

Be sure the Bye message on the list explains that non-renewing members are purged

  1. Go to the list website and log in.
  2. Click List Admin.
  3. Click Documents
  4. Select the Bye document and click Edit.
  5. If necessary, add the text suggested in "How to create the Confirm, Hello, and Bye documents."

Send the command to delete the non-renewing members

  1. Copy the columns with data in emailistdel.xls, except the row with the column headings.
  2. Paste the columns into an MS Word document.
  3. Convert the table to text with a space as the separator.
  4. Copy the text and paste into the delete message.

Send the delete message

  1. Open a new email message and put lyris@lists.stc.org in the To field.
  2. Delete your signature and any other characters.
  3. In the blank message, paste the following:

login YOUR_PASSWORD
delete LISTNAME user1@nowhere.com user2@nowhere.com

where there may be any number of email addresses to delete.

Acknowledgements

This training was developed by Michele Marques, manager of the STC Single Sourcing SIG and long time list manager for that SIG. Significant additions were made by the Online SIG.