A Basic Client Database and Conditionally Sending Emails - How?

September 11, 2017 9:18 AM

For my job I have a list of clients. Every client has one or more contacts (name, email). Each client needs to do a small number of things, which I keep track of. I need to export lists of clients who have and have not done those things, then send out reminder emails to the ones who haven't. I've been using a simple excel list and manually emailing, but the number of clients is getting too large to handle this way. What is my alternative?

Is this something that can be done through better Excel skills, or is there a simple alternative for me?

posted by FakeFreyja to Work & Money (8 answers total)

Word recognizes Excel as a database and can do mail merges based on queries using the excel data. Moving to a database program is an option, but is always way more work than anybody thinks. Presumably you have boilerplate emails and text that make the process somewhat automated. Does your employer have in IT specialist or chief nerd who can assist?
posted by theora55 at 9:40 AM on September 11, 2017 [1 favorite]

There are products that are specifically designed to automatically send emails based on things that people in a database have or have not done. Look for "marketing automation," "automated email," or "triggered email" solutions. At the high end they are extremely expensive, but there are some smaller ones made for small business. I don't have direct experience with any of them, so I can't recommend a specific one.
posted by primethyme at 9:45 AM on September 11, 2017

I wonder if something like MailChimp would be able to work. It looks like you can connect Excel or Google Sheets to MailChimp with Zapier. MailChimp is super easy to use, as is Zaper (although I haven't used MailChimp in several years, so I'm not super familiar with their "automation" features).
posted by radioamy at 10:35 AM on September 11, 2017 [1 favorite]

And even if MailChimp wouldn't work to connect to your spreadsheet, it might be better to switch to that for sending these emails, even if you have to export and upload a CSV. It's much cleaner to send bulk emails with templates from MailChimp. Plus it's got better deliverability, meaning you won't get marked as a spammer.
posted by radioamy at 10:36 AM on September 11, 2017 [1 favorite]

better Excel skills
Seconding Mail Merge, which is an Office skill that uses Word to create the template and then any one of a variety of sources to fill in the blanks. It will let you preview your messages and if you are comfortable with Excel, you will be comfortable using it after a few times.
posted by soelo at 12:06 PM on September 11, 2017 [1 favorite]

What you are looking for is a CRM. You will set up custom workflows with email reminders (your clients' tasks), import your client list, and voila, it does the work for you. There are literally hundreds upon hundreds of CRM's out there to choose from, I would recommend looking at Highrise to start.
posted by rada at 12:09 PM on September 11, 2017 [1 favorite]

There's a pretty small use case for this, but one option you could consider if the emails are usually boilerplate, is just setting up an email link via cell formulas in the Excel workbook itself.

So e.g., if you've got first names in column A, last names in column B and email addresses in column C, you could set up column D with a link to send "Reminder 1" to that person, and column E with a link to send "Reminder 2" to that person. Then:

In D1: =HYPERLINK("mailto:"&C1&"?subject=First Reminder&body=This is your first reminder", "Send Reminder 1")

In E1: =HYPERLINK("mailto:"&C1&"?subject=First Reminder&body=This is your first reminder", "Send Reminder 1")

... and copy the formulas in D1 and E1 all the way down. Then to send an email you just click the link (which will open up a new pre-populated email in your default email client) and then hit "Send."
posted by Doofus Magoo at 12:18 PM on September 11, 2017

MailChimp is possibly not what you want.

A while ago I had a similar question about email lists, and a friend at MC told me that “having a list of emails where the recipients have not opted in is a big problem for us.” If any were to lead to spam reports from the recipients, it would impact their ability to send email. So, MailChimp needs to be able to trace everything they send back to an explicit opt-in.
posted by migurski at 10:50 PM on September 11, 2017