Editing Bulletin Address Coupons

The following contacts on CiviCRM (CRM for short) 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).  

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

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. 

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 <Memberships - Find Memberships>.  
# Check the "Party Membership" box.  
# In the "End Date" select "Choose date range."
# Click on the calendar for the "From" date.
# In the "Year" drop down field, select two years back and click on the first day of the month.  Leave the "To" date blank
# 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 "Merge All Contacts with the Same Address" and "Postal Mailing Export" to exclude contacts with no street address, or who are deceased.
# 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 and save it as the current Member Mailing List in the Ontario Libertarian Party/Members folder. (e.g. Mail 17-06.xlsx).  Rename the sheet "Members."
# 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 a record with an expiry date in the current month.  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 formula and propagate it to all remaining records. �="Membership Expiry Date: "&TEXT(N143,"dd-MMM-yy")&". Please Make a donation to the Party"
# Copy the Msg column and <Paste Special-Values>.
# 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.  
# Click on <Donations - Find Donations>
# Donation Dates <Choose Date Range>, 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 <Paste - Special - Values>.  
## Sort by '''Last''' and delete all rows with a "0" in '''Last'''.  
## Delete the '''Last''' column
# Change the label in the last column to '''Msg'''. 
# Sort by '''Receive Date'''. 
# For all records with a '''Receive Date''' more than a year ago, 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 '''Receive Date''' in the last year, insert a message such as: �"Thank you for your donations.  We appreciate your support." (There may be a way to select "donations" or "donation" depending on the donor.) 
# Copy the '''Msg''' column and <Paste-Special-Values>. 
# Delete the columns for '''Receive Date''' and '''Total Amount'''.
# Copy the sheet to the first file (Mail 17-06.xlxs) as the second sheet and rename it "Donors"

== Search for and Extract Inquiries==

# <Search - Advanced Search> 
# For Groups select "Inquiries (no membership, no donation)". 
# Exclude "Do not mail"
# Select <Change Log> bar
# For "Modified Between" in the "From" field select the first day of the current month 2 years ago. 
# 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. 
# Sort by country and delete all US addresses that look like test data
# Save as the current Inquiries Mailing List in the Ontario Libertarian Party/Members folder. (e.g. Mail 17-06 Inquiries.xlsx)
# Change the label in the last column to "Msg" 
# For all records, insert a message such as; �"Please send a donation  to help the Party, or better yet, join us at ontariolibertarian.ca"
# Fill the Member or Associate? Column with "Inquiry".  
# 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. 
# Change the label in the last column to "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 file.  
# Make sure all columns are aligned properly and then delete the inserted header row. 
# Save the Inquiries file.
# Delete the CiviCRM_Contact_Search file with the Exchanges contacts.

== Search for and Extract Exchanges == 
A few records are identified as "Exchange."  The primary one is Library and Archives Canada, and we must send two copies of each issue to them.  Another is Karen Selick, an early member and active in a number of liberty oriented groups. 

= 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 in the same sequence. 
# Copy the Member contacts to the Print file
# 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.

== 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", and "Tel# Mobile (for SMS)", saved as "Home Mobile".  Most people with a cell phone enter the same number in both places.  (It might help if the 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 include 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.  
# Change the Label (column Heading) "Home-Phone-Phone" to "Home", "Home-Phone-Mobile" to "Cell" and "Work-Phone-Phone" to "Work." 
# Filter the "Work_Phone_Mobile" column to show all but blanks. 
# For each Mobile number cut and past the mobile number to the "Work" column. 
# Insert a formula in the "Work-Phone-Mobile" column to identify rows were Home and Cell are identical - =IF(I2=J2,1,0), where I2 and J2 are the Home and Cell phone numbers. 
# Filter the file by this column so only "1" rows show.  
# Delete all values in the "Home" column and clear the filter. 
# Delete the column for "Work-Phone-Mobile."

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. Delete all such records. 
# Use <Filter - Text Filters - Contains...> 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 same address ==
Before organizing into 3-up, in order to 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 formula [=IF(OR(G2=G1,G2=G3),"DUP","") and propagate to all records. This will flag records with duplicate Postal Codes. 
# Then scan for duplicate addresses.  
## In some cases it may be the same contact.  Keep the Member record and delete the Donor or Inquiry record. 
## 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 (preferably an Inquiry) and paste it into the "Supplemental Address 1" of the other record and delete the first record.  
# 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. 
# Move Jim McIntosh (or whoever is the current Party Archivist) to be the next record.  Both get two copies of Bulletin, no return envelope and require extra postage. 

== Create 3-up format ==

# 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 = 522).  
## 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 Print file as the merge file.  
# 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".)
# Remove any blank coupons.  

= 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 Treasurer 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 Campaign Director or whoever is responsible for for sending out New Member Kits. 

Maureen will 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). 


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