Bulletin Address Coupons

The following contacts on CiviCRM (CRM for short) with NO EMAIL ID will be mailed a printed copy of Bulletin:

• Any Member or Associate with an Expiry Date greater than two years ago;
• Any Donor (i.e. not a Member or Associate) who has made a donation in the past two years; and
• Anyone else who has asked to be added to our Mailing List in the past two years (i.e. Inquiries).

Each copy is mailed with a return envelope and a donor coupon. The donor coupon also has the address positioned to fit in a standard #10 window envelope.

Two copies must be sent to Library and Archives Canada and to Jim McIntosh (or whoever is maintaining the archives for the Party) with no #9 return envelope.

Before sending Bulletin to the printer It is best to first determine the number of copies to be mailed. Additional copies are required for sending to new members. The number is usually rounded up by 15-25 for this purpose.

Extracting Data from CRM

NOTE: I have saved a file Bulletin Mailing Coupons Fall 2020.xlsx as the default mailing list. If you need an updated list for any reason, follow NEW CRM instructions.

NEW CRM

1. Search > Find Contacts > IN: Bulletin Mail Subscribers
2. Export Contacts.
   • There should be about 25 people on the list.
   • Select All
   • Actions > Export Contacts
   • Export PRIMARY fields > Continue
   • Save As > .xlsx
   • Double-check if there are new names from last time. There should not be any new names coming onto the mail out list. All new members/donors are expected to be email only. If there is no indication that the person should be receiving email, change the Edit Communication Preferences > Preferred Communication Method to Email.
3. Add in the contact information for the following people grandfathered to receive the Bulletin:
   • Mrs. Sherry Camboia        157 Lynedale Cres.    Woodstock    ON    N0J 1C0 (has been added to Bulletin Mail Subscribers list)
   • Library and Archives Canada    Attn: J. Emard, Legal Deposit Division    395 Wellington Street    Ottawa    ON    K1A 0N4 (2 copies)
   • James Tester McIntosh        3 Hills Road    Ajax    ON    L1S 2W2 (2 copies)
   • Ms. Karen Selick        Box 96    Bloomfield    ON    K0K 1G0 (has been added to Bulletin Mail Subscribers list)
   • Mr. Timothy Wilkins        R.R. #1, 364920 Evergreen St.    Burgessville    ON    N0J 1C0 (has been added to Bulletin Mail Subscribers list)
4. Update Membership Expiration Date and Msg from previous mailings.
5. Go to [MS Publisher and Window Envelopes] section.

OLD CRM

CRM has separate Search functions for Memberships, Donations and Inquiries. It is not possible to "Extract" donation information when searching for Members, or Membership information (other than Member or Associate) such as Start and End Dates when searching for donations. Make a separate extract for each category and merge the results.

A contact can have multiple addresses and Emails. Select only the "Primary" one for the Extract record.

Search for and Extract Members

1. Click on <Memberships - Find Memberships>.
2. Check the "Party Membership" box.
3. In the "End Date" select "Choose date range."
4. In the "From" date enter a date at the start of the month two years ago (01/mm/yy) and click on [Search].
5. Select all records and select "Action" "Export Members." Click on [Go] to bring up the Export page.
6. Click on "Select fields for export." Select "Bulletin Members" from the "Use Saved Field Mapping" list.
7. Click on [Continue]. This will bring up the page showing the fields to be extracted. The following fields are required for a donor/mail coupon for Members and Associates; Sort Name, Addressee, Supplemental Address 1, Street Address, City, State, Postal Code, Country, 2 Home and 2 Wok phones, Email, Member or Associate?, Membership Expiration Date, Do Not Mail and Is Deceased.
8. Click on [Export] and wait for it to show the downloaded file.
9. Open the downloaded file, rename the sheet "Members" and save it as the current Member Mailing List in the Ontario Libertarian Party/Members folder. (e.g. Mail 17-06.xlsx). 
10. Delete all records with a "1" in Is Deceased or Do Not Mail. Delete these two columns.
11. Change the Label on the last column from Membership ID to Msg.
12. Sort the file by Membership Expiration Date.
13. Copy the following formula to the Msg field of the first record and propagate it to all records - �="Your Membership expired "&TEXT(O2,"dd-MMM-yy")&". To renew please go to ontariolibertarian.ca/join"�(O2 is the cell containing the Membership Expiration Date for the first record.)
14. Scroll down to the first record with an expiry date in the publication month (March, June, September or December). Change the word "Expired" to Expires on" and propagate it to all remaining records.
15. Scroll down to the first record with a date about a month after the expected mailing date and replace the Msg with the following text and propagate it to all remaining records. "Please make a donation to the Party". If this is the final issue of the year, the message should be, "Please make a donation to the Party before December 31".
16. Check for out-of-province members. (Filter for all provinces except ON.) They are not allowed to make donations, only to pay the annual $10 membership fee.
    1. If their membership has expired or will in the next three months, "Please renew your membership for $10 at ontariolibertarian.ca/join. (The Party cannot accept donation from individuals who are not residents of Ontario.)"
    2. Otherwise, "Thank you for your support"
17. Copy the Msg column and <Paste Special-Values>.
18. Delete the Membership Expiration Date column.
19. Save the file.

Search for and Extract Donors

For Donors, instead of the membership Expiry date, We need the Receive Date of the last donation made in the last two years. To do this extract all donations received in the last 24 months. Eliminate donations from Members and Associates. Then eliminated additional donations from the same donor leaving only one record for each donor.

1. Click on <Donations - Find Donations>
2. Donation Dates <Choose Date Range>, From the beginning of this month 2 years ago.
3. Check the Donation Status Completed box and click [Search]
4. Export all donations, using Saved Field Mapping "Bulletin Donors."
5. Sort by Member or Associate? and delete all records for Members and Associates.
6. Fill this column with "Donor" in each record.
7. Delete all records with a "1" in Is Deceased or Do Not Mail and delete these two columns.
8. Eliminate duplicate Names
   1. Sort by Received Date, then by Sort Name.
   2. Insert a column after Sort Name, label it Last
   3. In the first row, insert the formula - =IF(A2=A3,0,1) in the Last column and propagate it to all rows. (All donations from the same person except the last one will contain a zero.
   4. Copy the contents of the column, and then <Paste - Special - Values>.
   5. Scan down the Last column for a Zero (0) and change the following "1" to "2" (to indicate more than one donation in the past two years). Repeat to the last row.
   6. Sort by Last and delete all rows with a "0" in Last.
9. Change the label in the last column to Msg.
10. Sort by Receive Date and Last.
11. For all records with a Receive Date before the beginning of this year insert a message such as; �"Thank you for your support. Please make a donation to help us compete in the next election."
12. For all records with a Receive Date in the current year and a "1" in Last, insert a message such as: �"Thank you for your donation. We appreciate your support." (Note the singular "donation.")
13. For all records with a Receive Date in the current year and a "2" in Last, insert a message such as: �"Thank you for your donations. We appreciate your support."
14. Check for out-of-province donors. There should not be any.
15. Delete the columns for Last and Receive Date.
16. Copy the sheet to the first file (Mail 17-06.xlxs) as the second sheet, rename it "Donors" and save the file.

 

Search for and Extract Inquiries (Executive decided to eliminate Inquiries from mailout in July 2020)

1. <Search - Advanced Search>
2. Under the Basic Criteria section Groups, select "Inquiries (no membership, no donation)".
3. Exclude "Do not mail"
4. Select <Change Log> bar and click the "Added" button (NOTE: There is no added button, tried Created Date - Coreen Corcoran, June 2020)
5. For "Added Between" in the "From" field select the first day of the current month 2 years ago.
6. Click on [Search] and select all records.
7. In Actions drop-down, select Export contacts.
8. Choose Select fields for export
9. In Use Saved Field Mapping select "Bulletin Mailing"
   1. For "Merge Options" select - Merge All Contacts with the Same Address.
   2. Check the "Postal Mailing" box.
10. [Continue] and [Download File].
11. Open the CiviCRM_Contact_Search downloaded file.
12. Remove everyone with an email address (unless the item is to be mailed to select or all inquiries).
13. Search CRM for the first name in the list. Check the Change Log and confirm the earliest entry is not more than 2 years ago. If it is, you will need to delete one or more records. The records are in sequence by "Internal Contact ID", which are assigned sequentially as records are added. Check every fifth record until you find one that was added less than two years ago. Check the previous records until you find the last one added more than two years ago, and delete it and all prior records.
14. Fill the Member or Associate? column with "Inquiry".
15. Change the heading on the next column to Msg.
16. Delete all columns after the Msg column.
17. Sort by country and delete all US addresses that look like test data.
18. For all records, insert a message such as; �"Please send a donation to help the Party, or better yet, join us at ontariolibertarian.ca"
19. Copy the sheet to the first file (Mail 17-06.xlxs) as the third sheet, rename it "Inquiries" and save the file.

Select and Export Exchanges

At one time (i.e. when the Party rented an office) the Party would exchange newsletters with other organizations, such as the Libertarian Party of Canada. Now we use it primarily for Library and Archives Canada. We are required to send two copies of all Party Publications to them. We also classify a few other individuals as part of the CRM "Exchange" group, mainly past members, such as Karen Selick, one of the early members who is still active in several libertarian organizations. Sherry Camboia is daughter of former and deceased Party Leader Kaye Sargent, and Timothy Wilkins was the CFO for the Oxford Libertarian CA lead by Kaye Sargent. These are choices made by Jim McIntosh for what might be called sentimental reasons.

1. <Search - Advanced Search>
2. For Groups select "Exchange".
3. Click on [Search].
4. Select all records.
5. Export selected records using Field Mapping "Bulletin Mailing"
   1. For "Merge Options" select - Merge All Contacts with the Same Address.
   2. Check the "Postal Mailing" box.
6. [Continue] and [Download File].
7. Open the CiviCRM_Contact_Search downloaded file.
8. Delete all columns after the Email column.
9. Label the column after Email as Member or Associate?. Fill the column with "Exchange".
10. Label the next column as Msg.
11. For Library and Archives insert the message, "Please find enclosed two copies of our current newsletter."
12. For all other records insert a message such as; �"Please send a donation to help the Party, or better yet, join us at ontariolibertarian.ca".
13. Copy the header row and the Exchange contacts' rows and Insert them just below the Header row of the Inquiries sheet.
14. Make sure all columns are aligned properly and then delete the inserted header row.
15. Save the file.

At this point close all the CiviCRM Search files and delete them from your Downloads folder.

MS Publisher and Window Envelopes

The donor/mail coupon is formatted so that the address will show in a #10 window envelope. These are printed 3 per page with a Publisher Mail Merge file, Mail Coupons Merge.pub. (MS Publisher is used so that the 3 address boxes can be fixed at the correct positions on the page. MS Word does not allow Merge Fields in a text box so is difficult to format properly.)

MS Publisher does not have a "Next Record" merge command, so each sheet of 3 address coupons must be one record, or one row in the spreadsheet. The second and third coupons must have unique names or column headers.

MS Publisher supports the "Address Block" which allows for extra lines in the address (e.g. Supplemental Address 1 and 2) but does not 'print' blank lines if the field is blank. However the Address Block is matched to only one set of address fields in each row of 3 addresses. Therefore the second and third addresses on the page must spell out each line. If the Supplemental Address 1 is blank, a blank line will be 'printed.' Accordingly, all addresses with data in the Supplementary Address field(s) should be in the first Contact record of each row. The second and third contacts on the page will not include a Supplemental Address.

One other limitation of MS Publisher; if you change the list of contacts all of the Merge Fields are removed and new ones must be inserted. To avoid this, copy the records to the same Excel spread sheet used last time, e.g. Bulletin Mailing Coupons.xlxs.

 

Create 3-up Merge File

Copy each group of extracted contacts (Members, Donors, Inquiries and Exchanges), one below each other, into the print file, Bulletin Mailing Coupons workbook. Make sure all columns for each group are aligned.

1. Open the print file Bulletin Mailing Coupons.xlxs and delete all data rows.
2. Copy the Member contacts to the Print file.
3. Align the columns with the headings in the Print File. If necessary, insert headings in the Print file to line up with the Member file. For example, there are four phone numbers in the Member, Donor, Inquiry and Exchange sheets, but only three (Cell, Home and Work) are printed on the address/donor coupon. Insert an extra heading in the print file for Work-Phone-Mobile. (See below about fixing phone numbers.)
4. Copy the Donors to the Print File, including the Header row,
5. Make sure this Header row matches the Print file Header row and delete it
6. Repeat these last two steps for the Inquiries and Exchanges contacts.You will need to Insert Cells to create a column for Membership Expiration Date. (Add to Export template?)

If Bulletinis being mailed only to contacts with no email ID Sort by email ID and delete all rows with an email ID.

If a Bulletin (or any mail) is returned, the Address is flagged with "***MOVED***". There is no point in mailing another Bulletin to this address. before making any of the following changes delete all such records.

1. Use <Filter - Text Filters - Contains...> MOVED. This will hide all records that do not have MOVED in the address.
2. Select and delete all displayed rows. This does not delete hidden rows.
   1. Clear the Filter.

Merge contacts at the same address

This will reduce the number of mailings:

1. Sort the (1-up) file by Postal Code (Column G);
2. Insert a column labeled Duplicates after the Postal Code;
3. In the first record in Duplicates, insert the following formula and propagate to all records. This will flag records with duplicate Postal Codes.
   =IF(OR(G2=G1,G2=G3),"DUP","")
4. Sort the Duplicates column to bring all the duplicates to the top. Scan for duplicate Street Addresses.
   1. In some cases it may be the same contact. This happens when the contacts Membership has expired. Keep the Member record and delete the Donor or Inquiry record. It will also happen if the membership expired and the contact renewed some time later; there will be two membership records. Delete the record with the earlier Membership Expiration Date.
   2. One may be a company (delete) and the other the owner (keep).
   3. If both are Members keep both.
   4. If the last name is the same, change one to "Mr. & Mrs." and delete the other.
   5. If they have different last names, copy the name from one record (a Donor or Inquiry) and paste it into the "Supplemental Address 1" of the other record and delete the first record.

Fix Phone Numbers

CRM allows for five Phone Locations (Home, Work, Billing, Main and Other) and five Phone Types (Phone, Mobile, Fax, Pager and Voicemail). In theory a contact could have 25 different phone numbers. In practice, the page where they enter their contact information only allows for two numbers; "Primary Tel# - (for voice)", saved as Home-Phone-Phone, and "Tel# Mobile (for SMS)", saved as Home-Phone-Mobile. Many people with a cell phone enter the same number in both places.

It might help if the "Join Us" page put "Cell Phone" first and then "Home Phone (if different)" second. It might also be useful to have a field on the Join Us screen for "Work Phone." The previous Excel Contact list also had a Work phone number, which could be in either the Phone or Mobile Type. Alternatively, we could ignore work phones and not print them on the address/donor coupons. Or we could change the few Mobile Work numbers to Phone Work numbers and put "(cell)" in the Extension field.

The extracts includes four columns for Phone Numbers; Home-Phone-Phone, Home-Phone-Mobile, Work-Phone-Phone and Work-Phone-Mobile. We want to print on the Address/Donor coupon Home, Cell and Work.

1. Filter the Work_Phone_Mobile column to show all but blanks.
2. For each Work-Phone-Mobile number cut and past the mobile number to the "Work" column.
3. Insert a formula =IF(I2=J2,"",I2) in the Work-Phone-Mobile column where I2 and J2 are the Home and Cell phone columns 
4. Convert the column from formulae to values. Copy the column, then Paste values (Alt-H-V).
5. Replace all "0" cells with null. Use the Option "Match entire cell contents."
6. Copy the Work-Phone-Mobile column to the Home Column. 

Fix Postal Codes

Different people enter postal codes on different ways (lower case, no space). For new members they should be corrected to the proper format before the New Member Kit is sent. However, no such edit is done for new donors or inquiries.

Insert the following formula in a new (temporary) column to the right of the Postal Code (assumed to be in Column G) and propagate it to all rows. This will create properly formatted Postal Codes.

=UPPER(LEFT(G2,3)&" "&RIGHT(G2,3)

If the results look good, Copy the column starting from the first Canadian Postal Code and Paste-Special-Values in the Postal Code column, Delete the new (temporary) column.

 

 

Create 3-up format

Note that the Bulletin Mailing Coupons spreadsheet has three sets of labels; the second and third sset have the same labels with an "x" and "y" suffix. Follow these instructions to create three contacts per row.

1. Move any records with a "Supplemental Address 1" to the top of the file.
2. Move the Library and Archives record to the top of the file, as the first record.
3. Move Jim McIntosh (or whoever is the current Party Archivist) to be the second record. Both get two copies of Bulletin, no return envelope and require extra postage.
4. Save the file, preferably with the current Bulletin date (i.e. Bulletin Mailing Coupons_June2020.xlsx)
5. Determine the number of pages by dividing the number of records by 3. For example, if there are 520 records, there will be 173.333 or 174 pages.
6. Starting at the row number equal to the number of pages + 2 cut and paste (move) the remaining records into the second set of columns.
7. Determine the number of blank coupons. Blank coupons should be at the bottom of the third and second set of columns. In the example, there will be 2 (174 x 3 - 520 = 2).
   1. If there is only 1, it should be in the third set of columns. The second set should have the same number of records as the first.
   2. If there are 2 blank coupons, the second set will have one less record as well.
   3. Move the excess records to the third set of columns.

 

Create Donor/Address coupons

1. Start MS Publisher and open the Mail Coupons.pub file. Confirm you want to use the Bulletin Mailing Coupons file as the merge file. It will be looking for a file on the D:\ drive but click through until you can select the file you just created.
   NOTE: HAVING ISSUES WITH THE ADDRESSEE NOT SHOWING UP FOR THE FIRST RECORD ON EACH PAGE. MANUALLY ADDED THEM USING ADOBE ACROBAT - MAKE SURE YOU DELETE EXTRA SPACES.'
2. Update the "Date" text box in the lower right corner of each of the three coupons to show the current month. This is useful for reporting the Source for donations sent in by mail. It also is useful on returned mail.
3. If you have a colour printer, merge to the Printer.
4. If you need to send to a printer (Staples) save as PDF.
   1. Confirm that all sheets have been printed to PDF. Send for print or go in and print them yourself.
5. The printed sheets have guide marks on the sides for cutting the pages into coupons. Cut them manually with an Exacto or hobby knife, or take them to a copy centre or office supply store and have them cut the pages. (This expenses is recorded as "Office Supplies".)
6. Keep the cut pages in sequence (top third on top, middle third next and bottom third last).
7. Remove any blank coupons from the back of the second and third batches.
8. Separate the Address coupons that require two copies (e.g. Library and Archives).

 

Stuff and Mail Bulletins

Currently Maureen Dance is paid $200 per issue to stuff the envelops with the Address/Donor Coupon, Bulletin and #9 Return Envelope. Since the Jim McIntosh currently prints the address coupons, he purchases the required stamps. Either he will pick up the Bulletins from the printer and deliver them with the address coupons and stamps to Maureen, or he will arrange for MDS Express to pick up and deliver them. Alternatively, if Maureen is willing to buy stamps (e.g. on her credit card) and be reimbursed with her fee, the coupons could be printed and cut before Bulletin goes to the printer and mailed to her.

Maureen will then mail any excess copies of Bulletin to the Party Secretary or whoever is responsible for for sending out New Member Kits. She will include receipts for any postage she has purchased with the two copies of Bulletin sent to the Treasurer. If Jim delivers the Bulletin to Maureen, he will (usually) remove the copies intended for New Member Kits and mail or deliver them to the Secretary and include receipts for any postage she has purchased with the two copies of Bulletin sent to the Treasurer.

The Treasurer will prepare a 2-up invoice (one for Maureen and one for the Party) for Maureen's services (Professional Fees) and for any stamps she purchased (Postage) to go with the cheque for her services (categorized as "Professional Services" for Elections Ontario).

Resend as Required

1. Some Bulletins will be returned by the post office, either because the person has moved or the address is incomplete (e.g. missing unit number).
2. If the contact has an email ID, Email the contact and then flag the contact's address with ***MOVED***. There are two email templates in CRM to facilitate this process; "Moved" and "Moved - donor/candidate". If contact does not have an email ID and has made a donation and has not yet received an Official Receipt, follow up by telephone.

The contact may

1. Reply to the email, in which case update the contact address
2. Use the link in the email to update the address on their own, in which case the MOVED flag will be removed.
3. Take no action, in which case the contact's record will not be updated. If this is a contact who has made a donation and has not yet received an Official Receipt, follow up by telephone.