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
In This Article
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 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.
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.
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.
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.
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.
That’s all you need to set up the trigger.
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.
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.
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.
This will pop open another modal window. Click on Sign in With Google to proceed.
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.
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.
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.
Scroll down below and press the Get Columns button to see the column labels you’ve added within your sheet.
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.
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.
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.
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.
And that’s it! You’re now all set to start tracking email opens in a Google Sheet!