Help

Tracking an email reply and linking it to a record?

Solved
Jump to Solution
5583 13
cancel
Showing results for 
Search instead for 
Did you mean: 
Stephen_Turban
6 - Interface Innovator
6 - Interface Innovator

Hi Everyone! For our base, we do a lot of emails out to external people (students, contractors, etc.) . We generally want them to reply to our emails and so we set up other automations to bump them if they do not fill out a form. 

One issue we have is that often times people will reply to our email but not fill out the form. We're not currently able to address that - and so have to manually record that they've replied in the base so that we don't bump them. 

Has anyone found a workaround to track "email replies" such that we don't keep bumping them? I know usually this is done by attaching some sort of cookie to an email or similar. I know this is possible in Hubspot sequences, but I haven't found a similar solution in Airtable. This would be a big win if we could have something like this! 

1 Solution

Accepted Solutions

You could add a field called "email message" and put the email message you want to send to them in the airtable record, then let google apps script handle the sending out and tracking of the email, with the record id sent in the body/subject line of the email. A bit more complicated to set up though, but you could then track by airtable record and email conversation.

See Solution in Thread

13 Replies 13
Steve_Haysom
8 - Airtable Astronomer
8 - Airtable Astronomer

Hi Stephen

yes, I use google apps script to monitor email replies and update airtable when a reply comes in. You would just have to forward replies to a gmail account and set up the script.

You could potentially use a third party service like Zapier to create one record per email in your base, and then use automations from there to link the email to the person?

This is great - thank you.

Have you figured out an easy way to link it back to the original email that is sent out? Or the original record that kicked off the email automation? 

For example, if I send out an email to a customer saying "Please put down your deposit" (Let's call this the deposit email). I then receive a response from that customer on that deposit email chain and I want to link it back to both the email that was sent and the person who sent it  - have you found an easy way to do that? I know that you could potentially do some type of look-up on the email or try to pass the record-id somehow using a meta tag. But, if you have any simple ways to do it, that would be a big help! 

Really appreciate your response - very kind of you.  



Definitely that's possible - I think what I'd like to do is not to track whether a person has replied at all but whether they have replied to that exact email. For example, if I send out a deposit email, I'd like to know if they replied to that deposit email. But, after they pay deposit, I may send a new email that then asks for another payment. I'd want to then only track if they responded to that email payment email! 

Hm, I think I'm not understanding something.  If the person replies to Email 1 and Zapier or some such picks it up, you can then create a record in your base with the subject title and the sender (Email 1A).  Assuming "Email 1" exists in your base in some form, could you not use the subject title + sender from Email 1A to find the Email 1 and link it together?


You could add a field called "email message" and put the email message you want to send to them in the airtable record, then let google apps script handle the sending out and tracking of the email, with the record id sent in the body/subject line of the email. A bit more complicated to set up though, but you could then track by airtable record and email conversation.

I think that definitely could work - the key would be able to match up the email subject line (may need to remove the re: ______ piece) and then also the sender. I'm just wondering if there's an easier solution that is attached to a unique record ID of some sorts - I find that solutions that use look-ups are a bit brittle! 

I really appreciate this! 

It sounds like I should consider using Google App scripts to send out the email, which then has some built in functionality to track if an email was read/responded to, etc. Thank you for this!! 

was thinking about this further and the code I use takes advantage of the fact that gmail automatically groups messages by conversation, according to these rules so you can tell if someone has replied by simply looking at the number of messages in a thread.

Heres a bit of code perhaps you could start with:

const hasReplied = GmailApp.search("subject: " + recordId)[0].getMessages().length > 1 ? true : false

ie find the first thread that has messages with recorId in the subject and count the  messages in the thread, returning true if more than 1, as the first one is the one initially sent.

Do you know is there a way to add in reminder emails such that they are part of a thread of existing conversations? Right now, we have it implemented that we have 3-4 functions that kick-off reminder automations that then send separate emails. So, if we were to track we would need to then track all of the reminders emails separately (not the end of the world, but a little bit of leg-work). Whereas the desired behavior would be more like "If there is a response to any of the emails in this sequence of emails, then note that in Airtable." 

Really appreciate you thinking on this! 

Possibly, by making the subjects the same, although I'm not sure that it would be that much extra work in google apps scripts to track the separate emails, as long as they reference the same recordId in the subject/body, ie the code above could be expanded to:

 

const hasReplied = false
const
 threads = GmailApp.search("subject: " + recordId)
for(const thread of threads){
if(thread.getMessages().length > 1){
hasReplied = true
}
}

although you would have to allow for the fact that there may be more than one sent email in the thread so the code above would need to be modified a bit to test for To: and from emails.

According to the gmail threading rules :

Additional details

If you are managing a system that sends email notifications to users and want your emails to be threaded in Gmail conversation view, then you have to ensure that your notifications:

  • Have the same subject

  • Have reference headers that reference IDs seen earlier in the thread, or have references headers that consistently refer to the same message ID


Additionally, if you don’t want your messages to be threaded in Gmail, you can either have different subjects or send each message with a unique References header value that will never match another message.

Another solution which ignores threading, ie just search through all messages sent in the last day or so for any email sent to you which has a recordId in the subject:

 

const endDate = new Date()
let startDate = new Date()
startDate.setDate(startDate.getDate() - 2) // set to 2 to allow for any daylight saving issues
const threads = GmailApp.search("after:" + formattedDate(startDate) + " before:" + formattedDate(endDate) , 0, 500)
for (const thread of threads){
const messages = thread.getMessages()
for (const message of messages){
const subject = message.getSubject()
const recordId = subject.match(/\b(rec[a-zA-Z0-9]{14})\b/)?.[0] // recordids are always 17 characters long
if(message.getTo().match(/youremailaddress/i) && recordId){
updateRecord(recordId)
}
}
}

function formattedDate(mydate){
let day = mydate.getDate()
if(day < 10){day = "0" + day}
let month = mydate.getMonth() + 1
if(month < 10){month = "0" + month}
return mydate.getFullYear() + "-" + month + "-" + day
}
 
 
ScottWorld
18 - Pluto
18 - Pluto

These are great solutions!

And for people reading this who don’t want to dive into writing custom JavaScript code, I have setup this exact same things for several clients — with absolutely no coding at all — by using Make’s automations and integrations.

If anybody needs help with this and would like to hire an expert Airtable consultant to help with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld