Automatically Setting Merge tags Based on Quoted Recommendations

In order to improve marketing and responses to follow up emails, I created a feature in the Google Sheets tracking software we use to record issues found in a clients website that automatically sets merge fields in MailChimp that can later be used in automated email campaigns.


This was done for The Blog Fixer, a company that specializes in fixing common issues in WordPress post contents (everything from usability to FTC and Google compliance). The way they work, you can sign up for a Site Scan, which will find the issues that they can fix. For example, Link Target, which will make links open in a new tab. These numbers are all then given to them in the completion email, where they can buy a package to fix it all.

Unfortunately, it wasn’t uncommon to not get a response after the site scan was complete. Some of the issues, if not fixed, could lead to being penalized by Google or having their Amazon affiliate account shut down. The Blog Fixer had a follow-up email automation to ask if they had a chance to review the results of the site scan, but it was a fairly generic email.

To solve the issue and make the emails pop out a bit more, I created a feature for them that would allow them to use their recommendations in the MailChimp automations. MailChimp actually allows for custom variables (usually used for ecommerce), and even better allow for some variable logic. Specifically, the numbers I integrated with MailChimp are the number of individual changes The Blog Fixer can make for them, for each fix.

The end result is that they can now send out MailChimp automation emails that remind the client that they have very specific issues. For example “You have 12 places in your posts that break Amazon’s affiliate terms!” or “You have 80 Amazon links missing an affiliate tag!”. Hopefully, this will improve their site scan “bounce” rate, especially through bundles they enter like Ultimate Bundles.

The way I did it is by integrating Google Sheets & Scripts with MailChimp (no external service like Zapier required!). All of the service tracking and recording is done through Google Sheets, via generated sheets for every client. Using a menu item, Google’s URLFetchApp scripts library, and MailChimp’s API I was able to create a button in the sheet that automatically sets the merge field values based on what was entered in the spreadsheet.