Using Salesforce to Manage a Print Newsletter

First of all, this is far from managing everything. Really all we’re doing is recording subscriptions, removing subscriptions, maintaining addresses, and generating mailing database. The content is created manually with no magic what-so-ever. But all of these things are easily done in Salesforce using NPSP.

Subscriptions. This is done using a multi-valued picklist on the contact. Why? Because we expose it on our website for self-service sign-up. See sign-up.StreetYouthMinistry.org.
We have a campaign called master-print newsletter which has everyone who has a subscription.
We run a report from time to time (scheduled automatically) that pulls out contacts who have this print newsletter in the picklist but who do not have a membership in our master print newsletter campaign. When this report isn’t empty, we add the contacts to the campaign with status “completed” and that’s it. Subscription done! We have considered emailing people to let them know that we’ve added them to the newsletter but we don’t currently do that. If we did, it would be an automatic email triggered by adding someone to the campaign with the status “confirmed,” and sending the email would update the status to “completed.”
Sanity Checking. From time to time a scheduled report comes out that lists all members of the campaign grouped by status. The report is sub-grouped by the Newsletter field. So we can quickly see if there is anyone who doesn’t have the print newsletter in the picklist field but who was in the past added to the master campaign. And we can see quickly anyone who has asked to be removed but still have the value set. This audit just helps make sure our data makes sense. We could do this by process builder and assigned tasks when member status changes or when newsletter picklist value changes. But it’s a low volume thing so we just scan through the report once in a while.
Address Checking. We haven’t yet enabled address verification but we do have a report that notices people we should be able to mail but can’t. These include donors (we try hard to have mailing addresses for all donors) and print newsletter subscribers. The report notices those people who have incomplete addresses (missing street or city). We fix these as a development task. A separate report notices people who have incomplete addresses that can be fixed — city without state or zip; zip without city or state. We regularly fix these items as an administrative task.
Address maintenance. When a letter comes back to us, we correct the address if a forwarding address has been provided. If no address is provided, we remove the street address. Removing the street is enough to keep up from doing future mailings. We don’t remove them unless they ask. Often we find the address again after some time.
Subscription maintenance. When someone asks to be dropped, we change the status from “completed” to “asked to be removed.”
When we notice that we cannot reach someone who subscribed to our print letter, we add them to our email version if we have email address. This might be a risky step but it’s a rare thing anyway.
Mailing List Generation. This is what we do each quarterly. I have a HOW TO folder of documents and sending the print letter is one of them. Here are the steps that involve Salesforce:
  1. Run the report on people who signed up for print newsletter and add them to the master-campaign.
  2. Run the report for print subscribers missing street address and see if we can fix any of them.
  3. Create a new campaign for this newsletter. We use a campaign to plan our newsletter dates so something like 2016 Summer Quarterly Newsletter already exists. I just need to make a new campaign called 2016 Summer QNL – Print and set the parent to the existing newsletter. We do this often enough that we have a little flow that picks on of the planned newsletter and adds this child campaign with the same date settings and status. Type would be direct mail.
  4. Run the report on print newsletter contacts and add all contacts to the new child campaign.
  5. Go to the campaign and run the Household Mailing List report to see how many unduplicated letters there are. We send 200 letters each quarter and our print subscriber list isn’t quite that big. We found that sending extra letters to people in our database isn’t a bad thing. So we “pad” the mailing list to 200 (or sometimes larger) depending on our needs. This past month the list was 175. So I need 25 extra addresses.
  6. Go to Apsona and run a saved filter on contacts called “Donor with address and not email subscriber.” This pulls up contacts with complete mailing address but who are not currently receiving any type of quarterly newsletter (email or print). Using Apsona, I add these to the campaign with priority P2.
  7. I return to the campaign and rerun the household mailing list. If it’s oversized now, I go back to Apsona and run a filter on campaign member to find P2 members of the campaign. I display total giving and last gift date. I eliminate using these fields until I have the right number.
  8. If I needed more names, I would then run another Apsona saved filter on contacts to find non-donors with complete addresses who do not receive our newsletter. I could repeat the method to get the size I want.
  9. Sometimes we add some random people with complete address who already receive our email quarterly newsletter but show no signs of engagement (opening, clicking). Print is king sometimes!
  10. I finally return to the campaign and run the household mailing list. Now I download the details which includes some columns I don’t want to send to our printer. I edit the spreadsheet and remove those columns. This is also where I remove any +4 zip fields. I also have a few zip codes that start with a leader zero. I fix those and save the spreadsheet. It’s all ready for mail merging to the envelopes now!

Leave a Reply

Your email address will not be published. Required fields are marked *