Editing 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 =

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 ==

#Click on &lt;Memberships - Find Memberships&gt;.
#Check the "Party Membership" box.
#In the "End Date" select "Choose date range."
#In the "From" date enter a date at the start of the month two years ago (01/mm/yy) and click on [Search].
#Select all records and select "Action" "Export Members." Click on [Go] to bring up the Export page.
#Click on "Select fields for export." Select "Bulletin Members" from the "Use Saved Field Mapping" list.
#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.
#Click on [Export] and wait for it to show the downloaded file.
#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).&nbsp;
#Delete all records with a "1" in '''Is Deceased''' or '''Do Not Mail'''. Delete these two columns.
#Change the Label on the last column from '''Membership ID''' to '''Msg'''.
#Sort the file by '''Membership Expiration Date'''.
#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.)
#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.
#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".
#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.
##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.)"
##Otherwise, "Thank you for your support"
#Copy the '''Msg''' column and &lt;Paste Special-Values&gt;.
#Delete the Membership Expiration Date column.
#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.  

#Click on &lt;Donations - Find Donations&gt;
#Donation Dates &lt;Choose Date Range&gt;, From the beginning of this month 2 years ago.
#Check the Donation Status Completed box and click [Search]
#Export all donations, using Saved Field Mapping "Bulletin Donors."
#Sort by '''Member or Associate?''' and delete all records for Members and Associates.
#Fill this column with "Donor" in each record.
#Delete all records with a "1" in '''Is Deceased''' or '''Do Not Mail''' and delete these two columns.
#Eliminate duplicate Names
##Sort by '''Received Date''', then by '''Sort Name'''.
##Insert a column after '''Sort Name''', label it '''Last'''
##In the first row, insert the formula - =IF(A2=A3,0,1) in the '''Last''' column and propagate it to all rows.
##Copy the contents of the column, and then &lt;Paste - Special - Values&gt;.
##Scan down the&nbsp;'''Lasl'''&nbsp;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.&nbsp;
##Sort by '''Last''' and delete all rows with a "0" in '''Last'''.
#Change the label in the last column to '''Msg'''.
#Sort by '''Receive Date&nbsp;'''and '''Last'''.
#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."
#For all records with a&nbsp;'''Receive Date'''&nbsp;in the current year and a "!" in '''Last''', insert a message such as: �"Thank you for your donation. We appreciate your support." (Note the sigular for "donation.")
#For all records with a '''Receive Date''' in the current year&nbsp;and a "2" in&nbsp;'''Last''', insert a message such as: �"Thank you for your donations. We appreciate your support."&nbsp;
#Check for out-of-province donors. There should not be any.
#Delete the columns for '''Last''' and&nbsp;'''Receive Date'''.
#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 ==

#&lt;Search - Advanced Search&gt;
#For Groups select "Inquiries (no membership, no donation)".
#Exclude "Do not mail"
#Select &lt;Change Log&gt; bar and click the "Added" button
#For "Added Between" in the "From" field select the first day of the current month 2 years ago.
#Click on [Search] and select all records.
#Export selected records using Field Mapping "Bulletin Mailing"
##For "Merge Options" select - Merge All Contacts with the Same Address.
##Check the "Postal Mailing" box.
#[Continue] and [Export].
#Open the CiviCRM_Contact_Search downloaded file.
#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.
#Fill the&nbsp;'''Member or Associate?'''&nbsp;column with "Inquiry".
#Change the heading on the next column to&nbsp;'''Msg.'''
#Delete all columns after the '''Msg&nbsp;'''column.
#Sort by country and delete all US addresses that look like test data.
#For all records, insert a message such as; �"Please send a donation to help the Party, or better yet, join us at ontariolibertarian.ca"
#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. 

# <Search - Advanced Search> 
# For Groups select "Exchange". 
# Click on [Search]. 
# Select all records.
# Export selected records using Field Mapping "Bulletin Mailing"
## For "Merge Options" select - Merge All Contacts with the Same Address.
## Check the "Postal Mailing" box.
# [Continue] and [Export].
# Open the CiviCRM_Contact_Search downloaded file. 
# Delete all columns after the '''Email''' column. 
# Label the column after '''Email''' as '''Member or Associate?'''.  Fill the column with "Exchange". 
# Label the next column as '''Msg'''.
# For Library and Archives insert the message, "Please find enclosed two copies of our current newsletter."
# 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".
# Copy the header row and the Exchange contacts' rows and Insert them just below the Header row of the Inquiries sheet.
# Make sure all columns are aligned properly and then delete the inserted header row.
# 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 and Inquiries), one below each other, into the print file, '''Bulletin Mailing Coupons.xlxs''' workbook. Make sure all columns for each group are aligned.

#Open the print file '''Bulletin Mailing Coupons.xlxs''' and delete all data rows.
#Copy the Member contacts to the Print file.
#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.)
#Copy the Donors to the Print File, including the Header row,
#Make sure this Header row matches the Print file Header row and delete it
#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 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.

#Use &lt;Filter - Text Filters - Contains...&gt; MOVED. This will hide all records that do not have MOVED in the address.
#Select and delete all displayed rows. This does not delete hidden rows.
#Clear the Filter.

== Merge contacts at the same address ==

This will reduce the number of mailings:

#Sort the (1-up) file by Postal Code (Column G);
#Insert a column labeled Duplicates after the Postal Code;
#In the first record in Duplicates, insert the following formula and propagate to all records. This will flag records with duplicate Postal Codes.<br> =IF(OR(G2=G1,G2=G3),"DUP","")
# Sort the '''Duplicates''' column to bring all the duplicates to the top. Scan for duplicate '''Street Addresses'''.
##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'''.
##One may be a company (delete) and the other the owner (keep).
##If both are Members keep both.
##If the last name is the same, change one to "Mr. & Mrs." and delete the other.
##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.

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

== 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. 

#Move any records with a "Supplemental Address 1" to the top of the file.
#Move the Library and Archives record to the top of the file, as the first record.
#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.
# 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.  
# 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.  
# 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).  
## 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.   
## If there are 2 blank coupons, the second set will have one less record as well. 
## Move the excess records to the third set of columns.

= Create Donor/Address coupons =

#Start MS Publisher and open the Mail Coupons.pub file. Confirm you want to use the '''Bulletin Mailing Coupons'' file as the merge file.
#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.
#Either merge to the Printer, or to a new file and print the new file.
#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".)
#Keep the cut pages in sequence (top third on top, middle third next and bottom third last).
#Remove any blank coupons from the back of the second and third batches.
#Separate the Address coupons that require two copies (e.g. Library and Archives) or have USA addresses and attach the stamps required (USA stamps).

= 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 =
# 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).  
# 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 
# Reply to the email, in which case update the contact address
# Use the link in the email to update the address on their own, in which case the MOVED flag will be removed. 
# 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.