How to track email opens in a google sheet

How to Track Email Opens in a Google Sheet

Editorial Note: We may earn a commission when you visit links on our website.

Do you want to track email opens in a Google Sheet?

With email open data sent to your sheets, you can easily view which recipients opened specific emails sent from your WordPress site. This is a great way to measure if your emails are producing the desired results or if you need to make any changes to improve your open rates.

In this post, we’ll show you how to make an email open tracker in Google Sheets.

Why Should You Track Email Opens in a Spreadsheet?

For marketers, having full visibility into email performance is important for getting the best out of your campaigns. If you’re sending emails that no one is clicking open, you’re only wasting conversion opportunities.

WordPress doesn’t include built-in email tracking capabilities. However, you can use plugins like WP Mail SMTP to obtain email tracking data and even send this information to Google Sheets for easier access.

We’ve written a review of WP Mail SMTP if you’d like to learn more about it before we dive in.

How to Track Email Opens in a Google Sheet

1. Install WP Mail SMTP

To get started, first install WP Mail SMTP on your site. WP Mail SMTP is a proven solution for ensuring high levels of WordPress email deliverability. Besides, it also offers super useful extra features like email tracking.

Using Uncanny Automator, you can also automate processes like sending your WordPress email tracking data to spreadsheets.


Once you’ve installed WP Mail SMTP on your site, you’ll need to connect it with a mailer service. This is important for properly routing your emails through an SMTP mailer so that your emails are always delivered successfully.

Check out this guide about WP Mail SMTP mailers for tips on connecting with a mailer.

2. Install Uncanny Automator

After installing WP Mail SMTP, you’ll need to install another plugin called Uncanny Automator. With Uncanny Automator, you can connect multiple plugins and apps and automate processes between them.

We’ll need Uncanny Automator to integrate WP Mail SMTP with Google Sheets.

Uncanny automator

Uncanny Automator can be installed directly from the WordPress directory for free. But the free version gives you only 1,000 credits, so you’ll only be able to track up 1,000 email opens in Google Sheets. If you need more credits, you’ll need to upgrade to an Uncanny Automator paid license.

For setup instructions, you can refer to this beginner’s guide on installing WordPress plugins.

3. Create a New Automation Recipe

Once you’re done installing Uncanny Automator, navigate to Automator » Add New from your WordPress dashboard.

Add new recipe automator

On the next screen, you’ll see the recipe editor, where you can create automation between different apps.

Now, you need to select a recipe type. For this tutorial, it makes sense to pick Everyone as the recipe type since you’d want the automation to initiate for all users. Once you’ve done that, press the Confirm button to continue.

Select recipe type

In the next steps, we’ll select our triggers and actions to configure the automation recipe.

4. Choose WP Mail SMTP As the Trigger

On the next screen, you’ll see the recipe editor screen. Enter a title for your recipe and then scroll down to the Trigger section. Here, click on WP Mail SMTP Pro.

Select WP Mail SMTP trigger

After you’ve done that, you’ll see different trigger actions that you can choose from. We’ll select the “An email with specific text in the subject line is opened” as the trigger for this tutorial.

email open trigger

Now, you’ll see a Text to Match field where you can enter a specific email subject line text that you’d like to track.

For example, we want to track if customers are opening emails with a particular discount offer. So we’ll enter that offer as it will appear in our email subject line into the Text to Match field and press Save.

Enter text to match

That’s all you need to set up the trigger.

Email open trigger saved

In the next step, we’ll connect our Google Workspace account with Uncanny Automator to enable the Google Sheets integration.

5. Connect your Google Account with Uncanny Automator

In order to create an action using Google Sheets for our automation, we first need to connect our Google account with Uncanny Automator.

Scroll down to the Actions section and click Add Action.

Add action

At this point, you’ll see a list of apps that you can use to perform actions based on the previously selected trigger. Look for Google Sheets on this list and click on it to continue.

Select Google Sheets

Clicking Google Sheets will open a modal window showing you 2 steps to perform. First, you’ll need to create an Uncanny Automator account if you don’t already have one. Just follow the on-screen instructions to create your account.

Then, return to the modal screen and press the Connect Account button to start your Google integration process.

Connect Google account

This will pop open another modal window. Click on Sign in With Google to proceed.

Sign in with google

Next, select your Google account (if already saved in your browser) or log in to it. You’ll then be required to allow all requested permissions from Uncanny Automator in order to get the integration working.

Click the checkmark boxes to allow all permissions and press Continue.

UA permissions

After that, you should see a success message confirming that your Google account is connected with Uncanny Automator. Exit the modal window to return to the Uncanny Automator recipe editor page that we’ve been working on.

6. Set Up a Google Sheets Action

Back in the recipe editor screen, you can now select an action to perform with Google Sheets. Here, pick “Create a row in a Google Sheet” because we want to add email tracking data within rows in the spreadsheet.

Google sheets action

Once you select the action, you’ll see new menu options as below:

  • Drive – Select the Google Drive where your spreadsheet is saved.
  • Spreadsheet – Select the spreadsheet you want to add email open tracking data to.
  • Worksheet – Choose the worksheet within the spreadsheet for sending data.

Select drive spreadsheet and worksheet

Scroll down below and press the Get Columns button to see the column labels you’ve added within your sheet.

get columns

Once you’ve done that, Uncanny Automator will fetch column names from your sheet. You can now include values within each column pulled directly from your email data.

Press the asterisk (*) button next to the Value fields to select appropriate pieces of email data from WP Mail SMTP as you see fit.

Enter values

Since we set up our sheet to include 3 columns (log ID, email address, and email subject), we’re going to select matching values for these columns.

Save action

If you’re happy with how everything looks, press Save.

Scroll up to the top and use the toggle button to make the recipe Live.

recipe live

Great! Now, Uncanny Automator will send specific email data under your defined sheet columns every time an email with specific text is opened by users.

email tracking data in sheet

And that’s it! You’re now all set to start tracking email opens in a Google Sheet!

Next, Get More Value Out of Contact Forms

Contact forms are incredibly useful for businesses in a variety of ways. But you might be underutilizing your contact forms if you’re not using some of these advanced features. For tips on getting more value from contact forms, see our guide on how to grow your business using contact forms.

Create Your WordPress Form Now

Ready to build your form? Get started today with the easiest WordPress form builder plugin. WPForms Pro includes lots of free templates and offers a 14-day money-back guarantee.

If this article helped you out, please follow us on Facebook and Twitter for more free WordPress tutorials and guides.

Using WordPress and want to get WPForms for free?

Enter the URL to your WordPress website to install.

Add a Comment

We're glad you have chosen to leave a comment. Please keep in mind that all comments are moderated according to our privacy policy, and all links are nofollow. Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.

This form is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.