I understand that you still want to use your email outside of Airtable. Sorry if I wasn’t clear about that. That’s why I was suggesting that sync’d emails end up in a separate table. That said, if you want to limit which emails gets sync’d to Airtable based on the contact list you store in a separate table in your base you can definitely do that—it’s just a lot more involved than separating sync’d emails from the ones you send. But I suppose if this is a CRM you don’t really want messages in two places.
So, let’s address that first because it’s one of those things that’s elementary in regards to the steps you need to take to implement but the sort of thing that will tie your brain in knots as you sort out which steps you need to take. Here’s an outline of how a scenario in Integromat (or zap in Zapier or any similar trigger/action integrations service) would work to sync emails you receive:
Trigger: Set up an Email trigger (that uses an IMAP connection to your email account—you’ll set this up similarly to any desktop or smartphone mail client) that watches for new messages as they come in. Make sure you set your connection up to look for mail in your inbox (or a folder that contains the messages you want Integromat to look for) and to limit messages per cycle to just one. By default, Integromat will pull two new messages at a time. That’s a nice feature but it you don’t want to use it in this situation. You can also use advanced settings to filter emails in advance, which can be useful if you know the emails you want to get into Airtable will always contain a specific word or phrase in them.
Action: Set up a search action that looks at the contacts table in your Airtable base. You’ll need to filter the records to only show a single recipient by using an Airtable formula. That setup will look something like this:
- Action: Set up an action to add a new record to a table in your base that’s solely for messages. We’ll need to add a condition afterwards but it’s easier to just finish this step first. Basically, you just want to map the relevant parts of an email to the fields in your messages table. It’ll look something like this:
You’ll notice that the contact field has a blue ID tag in it instead of the usual read ones. The red ones refer back to the email message that was caught by Integromat and the blue ID comes from the contact record Airtable found. This is because I’m showing you the most reliable and manual setup.
You can ask Integromat to try and map fields for you and you can also request that it let you use the email address instead of the record ID as a means of linking one record to another. This is a really nice feature to simplify the process. Currently, Zapier doesn’t allow this so I’m trying to keep things more generic in case someone using Zapier wants to do this someday and finds this post. Also, you’re less likely to run into problems if you work with IDs because they’ll always be unique. (There are a lot of specific reasons why, but they’re not worth getting into right now.)
- Filter: create a filter in between the the search action and the add record action. (For Zapier users, filters are not the same thing. You need to set up a path. Generically-speaking, you’re creating a conditional action regardless of what service you use but they all like to come up with their own names so it can get confusing sometimes.) Basically, you just want to check that the email address of the message’s sender matches the one in the record found in your contacts table. If it doesn’t, that’s because it didn’t exist in your contacts table and the message will, as a result, never get logged in Airtable. If it does match, however, it will get logged and correctly linked to the appropriate contact automatically. The filter setup is pretty obvious at this point, but here’s an example screenshot anyway.
After that you should just send a test email to yourself (from an address in your contacts table) and run the scenario once to make sure it works. If it does, set it on a schedule and all email senders that match the addresses in your base’s contacts table will be logged to the messages table automatically.
I threw a very quick base together just to make sure I didn’t give you any bad information/miss anything important, so you can check that out below if you want to see how everything matches up with the Integromat screenshots.
Start out by getting that to work with your base and email account and then we can talk about sent messages. It’s a bit more complicated to sync those because you’re sending from within Airtable as well. I think the best approach would be to send messages in Airtable from a separate table so you can just sync sent messages freely. That would treat the messages table as more of a message log so that messages you send from within Airtable don’t need complex routing and logic to deal with duplicate detection and other really annoying problems. Code or no code, I promise that’s a headache you want to avoid.
If you just sent up sync the simple way, you can basically just create an almost identical scenario that looks for sent mail instead. The easiest option would be to set up a separate connection to your email that monitors the sent folder instead of your inbox, duplicate the received email scenario, tell it to look at the recipient instead of the sender (because you’re the sender in this case), and then leave everything else the same. But we can worry about that once you have this first part up and running.
Oh, and just in case this wasn’t assumed, you can absolutely work with more fields than I did in this example. I just wanted to keep it simple for explanation purposes/to save time. There’s a TON of other metadata you can save from every email so you can add as much as you want.
Now, for the open/click tracking, the situation is like this: if you use a third party service or roll your own solution you’re still going to be dealing with tracking pixels. I’m not aware of any other way to track opened emails and a quick search suggests there isn’t any other method currently available. Pretty much every third-party email marketing tool/service will provide this kind of tracking but, like I said before, it’s not the most reliable and there are downsides to implementing this yourself and using a service to do it.
I’m not familiar enough with email marketing services to know which ones allow for specific API calls to grab tracking data from their platforms. After looking through many options on Integromat, however, they’re just not available as triggers you can use. There’s no way to sync this data without making an API call and that requires the service to support that type of API call in the first place. Beyond that, each service has a different set of requirements for sending messages and that can include having all recipients registered with that service. That would mean you’d also need to sync all your contacts in Airtable through a separate Integromat scenario. This isn’t hard to do but it requires Integromat to check and update a lot more which means the likelihood of additional costs. That said, I don’t think it’d take you over the $9/month plan—it’s just likely to make it impossible to use the free tier and it’s one more moving part in your setup. You want as few scenarios as possible because if something goes wrong you’ll have an easier time figuring out the source of the problem.
Anyway, if you want to go this route you’ll need to make an API call. I don’t know enough about the pros and cons of these services to recommend the most ideal candidate but I am familiar with Sendgrid and it does support the API call you need to make (I can’t link to it directly but you want to look in the email activity section). You can track all kinds of message-related events through Sendgrid but you will have to poll the API frequently, for every message, in order to do this. If you had 1,000 messages in your base you would hit the action cap of the $9/month tier on Integromat in a matter of hours. It’s just a really inefficient method.
Webhooks are used to avoid all this unnecessary polling. Basically, the service you choose would instead poll Integromat directly whenever a specific event occurred (an opened email or a clicked link). If you look at Sendgrid’s API documentation you’ll see they have a webhooks section and it also supports the events you want. So while I can’t recommend a service by comparison I can say that Sengrid can do what you want and it’s possible it can do it for free depending on your volume. If your volume is over 100 messages per month, however, you’d go from free to $15/month right away.
Also, this kind of webhook setup isn’t an easy one. You have to set up a webhook trigger scenario in Integromat, get the URL it generates, provide that URL to Sendgrid (or whatever other service you choose), send test data to the webhook through Sendgrid (or other service), see what you get in Integromat, create a conditional router to determine the event status, and then have that router lead to a series of actions very similar to the email sync setup—but for every possible route. It’s not a simple setup. It’s do-able, but it’s a lot of time and effort for one little thing. That’s why I suggested skipping this or just implementing it last because it’s the hardest part. At least, at the end, you’ll have more experience and it won’t feel as complicated to pull off.
But there’s also the DIY option. You can use Integromat’s webhooks to both create the webhook URL and generate the desired response. I haven’t tried this myself, but since you can set the headers of the response you technically should be able to make it provide data that’s considered an image and can be used as a tracking pixel. You’d just need to have a unique tracking URL per message, but that’s easy to generate in Airtable and I’m trying to avoid the specifics until you know how you want to do this (and have the easier stuff taken care of). Since you can set headers it’s also potentially possible that you can create an HTTP redirect this way, and that’ll be necessary for tracking clicks in your emails. That part should be a bit easier to sort out.
The DIY option will require less work to set up but slightly more effort to facilitate (most likely) when you want to track things. Getting it to happen automatically might be possible but it’ll depend on how you want to compose messages sent through Airtable. Right now you can’t officially use rich text in your base so you’d have to send naked URLs. That said, the rich text field is in beta right now (I think it has been since October) and it solves that problem. Regardless, you still have to assemble tracking URLs somewhat manually. I know some workarounds to make this really simple but we can cross that bridge if we come to it.
Anyway, make sure you can get your received messages sync’d to your base with the process I described earlier and we can tackle sent messages next. Also let me know if you come to a conclusion about how you want to deal with the tracking situation. If you want recommendations on a specific service to use, I’d consider posting about that explicitly on the forum. Just something like “what email marketing service would you recommend?” Then just say it needs to work with Integromat and have tracking features. I think you’ll get more responses with something short and straightforward like that. Also, you can probably get a good recommendation from Zapier. They’re very helpful. Everyone in the company handles customer support regardless of their position so they’re pretty active on that front. Ask them what they’d use in your situation. I’d be very surprised if they didn’t have some recommendation. They may have even written a blog post about it.