How to Automatically Track Email Opens in Google Sheets

In this guide, we’ll show you how to automatically track email opens in Google Sheets so that you can optimize your email marketing campaigns. No exporting, no file transfers, and absolutely no code required!

If you’re anything like us, you’ve spent days (probably weeks) crafting the perfect email marketing campaign.

You created various streams for different mailing lists and carefully composed each sentence as if you were Beethoven writing a sonata. You poured over images, editing the RGB of each pixel until your emails looked like they could hang in the Louvre. And then, finally, you hit Send and… radio silence.

Did your contacts enjoy your smartly-worded puns? Did they appreciate your image editing skills? Did they even read your email? Heck, did they even open the thing? Given that the average email open rate across all industries hovers around 21%, the answer might disappoint you.

However, fret not, my fellow email artist. By the end of this guide, you’ll be able to track your email opens in Google Sheets to improve and optimize your email marketing campaigns. And the best part? You won’t need to deal with messy file transfers or scary-looking code.

Get your quill ready!

What You’ll Need

Email tracking might sound sophisticated but, with the right tools, tracking your emails will be a lot easier than writing them.

WPMail SMTP Pro

To start tracking your emails, you’ll need a mailer plugin for your WordPress website. And WP Mail SMTP Pro is the most popular mailer plugin there is, used by more than 3 million websites.

In addition to guaranteeing that your mail won’t get lost in cyberspace, WP Mail SMTP Pro offers an easy setup process, open and click tracking and a host of other top-notch features.

Click here to check out WP Mail SMTP Pro’s pricing schedule. They also have a 14-day money back guarantee if you don’t start to see results immediately. But, we promise you, this is one carrier pigeon that always delivers its mail!

For assistance getting started, check out WP Mail SMTP’s Setup Wizard under WPMail Mail SMTP > Settings > General in your WordPress Admin Sidebar.

Note: In order to enable email tracking, you’ll have to configure your WP Mail SMTP Pro settings. To do so, from your WordPress Admin Sidebar, navigate to WP Mail SMTP > Settings > Email Log.

Check the following boxes: Enable log, Open Email Tracking, and Click Link Tracking.

Uncanny Automator

WP Mail SMTP will ensure that your emails get delivered to your contacts’ inboxes. But you’ll still need a plugin that can track your email opens in Google Sheets. Enter: Uncanny Automator.

As the #1 WordPress automation plugin, Uncanny Automator connects all of your favorite apps and plugins to create seamless workflows and unburden you from repetitive tasks.

In addition to automatically syncing your WP Mail SMTP data to Google Sheets, Automator can help you track the success of your various promotional campaigns with form-building and e-commerce integrations such as WP Forms, Fluent Forms, WooCommerce and Upsell Plugin.

To follow along, you can download Automator for FREE—just don’t forget to register your account to receive complimentary app credits for app integrations such as Google Sheets, Facebook, Slack and Help Scout. Alternatively, check out Automator’s pricing schedule to gain full and unlimited access to more time and cost saving features!

Connect Google Sheets to WordPress

Once you’ve downloaded Automator, you’ll need to connect Google Sheets to your WordPress website. You can click here for a detailed guide or find the abridged version below:

1. From your WordPress Admin Sidebar, navigate to Automator > Settings.
2. Click the App Integrations tab and scroll down to Google Sheets.
3. In the bottom left-hand corner, click Sign in with Google and follow the prompts to complete the process.

That’s it!

Start Tracking Your Email Opens in Google Sheets

Now that you have the right tools, you’re ready to start tracking your email opens in Google Sheets.

Automator works using recipes, i.e. combinations of actions and triggers that connect your apps and plugins to automate your workflows. We’ll walk you through the basics, then, once you’ve gotten the hang of cooking up recipes Automator-style, you can start to explore some of the more advanced features.

Step 1: Create a New Recipe

From your WordPress Admin Sidebar, navigate to Automator > Add New. In the pop-up window that appears, select Everyone.

Step 2: Name Your Recipe

Just like with your emails, you’ll want to give your Automator recipes names that are descriptive, yet short. As such, it’s best to come up with a naming convention. For example, we’ve named this recipe WPMAil—Google Sheets: Email Opens Tracking based on the integrations involved and its purpose.

Step 3: Configure Your Trigger(s)

In the Triggers panel, from the menu of available integrations, click WP Mail SMTP Pro.

From the drop-down list that appears, select An email with specific text in the subject line is opened.

Automator will prompt you to fill in the field labeled Text to match. This is how Automator will know which emails you’re going to track. As part of our example, we’ve entered “HUGE Sale”.

When you’re finished, click Save. Your trigger should look something like this:

Step 4: Configure Your Action

In the Actions panel, click Add action. From the menu of available integrations, select Google Sheets.

From the drop-down list, you can either select Create a row in a Google Sheet or Update a row in a Google Sheet. This will depend on how you’ve configured your Google Sheet to track your email opens. We’ll walk you through both options.

If you have an unpopulated Google Sheet like the one above (i.e., a Google Sheet that you’ve configured to track email opens that doesn’t have any data yet) you’ll want to select Create a row in a Google Sheet.

Automator will prompt you to select a specific Drive, Spreadsheet and Worksheet.

Next, in the bottom right-hand corner, click Get columns. Automator will retrieve the top entry in each column to populate the Column field.

You can fill in the Value fields manually if certain cells are supposed to contain static data. Otherwise, you can use tokens (i.e., dynamic pieces of data from your website and/or within recipes) to create a more responsive automation.

To use tokens, click on the Asterisk next to each field and select the appropriate dynamic value. For example, in our Customer Email column, we’ve selected the To email trigger token.

Once you’ve filled in your Value fields, your screen should look something like this:

Click Save. Your Trigger panel should look like this:

With this option, Automator will create a new row with the data you’ve selected in the first empty row in the sheet.

If the Google Sheet you are using to track email opens, like the one above, already has data in it that you want to update to reflect that your contact has opened the email, select Update a row in a Google Sheet.

Automator will prompt you to select your Drive, Spreadsheet and Worksheet as well as a search column.

Next, Automator will prompt you to select the values that you want to update. Toggle the Update button from “No” to “Yes” if you want to update the value in that column and set the value.

When you’re finished, click Save. Your action should look like this:

Step 5: Go Live!

In the upper right-hand corner, toggle your recipe from Draft to Live and start tracking your emails like a hunter on the trail!

Step 6 (Optional): Track Email Opens with Unix Timestamps

In some of our previous screenshots, you might have seen Automator’s Current Unix Timestamp token. In combination with Automator’s trigger and action tokens, you can use this date token to track how long it takes your contacts to open your emails, right down to the very second.

Click the link below to learn how to automatically convert Unix timestamps to dates in Google Sheets.

 

Keep Tracking! 

Now that you’ve gotten the hang of tracking your email opens in Google Sheets, it’s time to start using some of Automator’s more advanced features.

In addition to tracking your email opens, take advantage of Automator Pro’s click-tracking features to find out exactly how effective your email marketing campaigns are at driving traffic.

Then take advantage of some of Automator Pro’s most powerful features such as post and user meta to create your own key metrics.

 

How do you plan on automating your email tracking process? Let us know in the comments sections below. Until then, keep writing more great email campaigns and track your way to success!