Tracking an email reply and linking it to a record?

[Stephen_Turban]

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! 

[Stephen_Turban]

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! 

[Steve_Haysom]

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.

[Steve_Haysom]

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]

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