In this guide, we’ll show you how to automatically track email opens in Google Sheets…
At Uncanny Owl, we have built public and custom reporting solutions for over 5 years. Our Tin Canny, Uncanny Groups and Continuing Education Credits plugins all have different types of LearnDash reports for different audiences. While they have helped many sites, users always want more. How do I add this extra column? Can you add a date range filter? How do I see a particular type of data a particular way?
We have increasingly found that we will probably never satisfy everyone, and building more reports for our plugins isn’t the answer. Users end up confused by the choices and still unsatisfied that our reports don’t present data in that one specific way they need it. For many, custom reports are out of reach because of the cost.
There’s an easier answer, and it’s with Uncanny Automator and Google Sheets. Connected together, you can capture and report on data for almost anything users can do with LearnDash. And because all of the data is in Google Sheets, you can filter, sort, chart and manipulate data however it makes sense to you. No development is required and the solution is highly scalable.
Benefits of Custom LearnDash Reports
Before we get into how to set up custom LearnDash reporting in Google Sheets, let’s talk about why you might want to connect them together. To do that, let’s revisit some of the requests we often hear from users.
I want to know who has completed a course, lesson, topic, quiz, submitted an assignment or joined a group.
Reports for course completion are easy; there are lots of those in reporting plugins because it’s what everyone wants. You can probably get a lot of data about the user, the course, and overall progress from those. Tracking other data though is much harder; as an example, there is no way to see who has completed a particular topic and when they completed it.
But with Uncanny Automator, you can report on any LearnDash activity that it can capture. Here are a few examples of activity from the full list:
- A user completes a lesson
- A user completes a topic
- A user passes/fails a quiz
- A user submits an assignment
- A user is added to a group
Every time one of these things happens, you can add the record to a Google Sheet along with any extra data you want. Maybe whenever users complete any lesson, you pass the user’s name, email address, current date, course name and lesson name to Google Sheets.
I need these extra columns in my report.
One of the great things about building reports with Automator is that you can add data for any columns you want. Automator does this using the magic of what are called “tokens”. These are basically variables with information about the user and whatever objects are in the recipe. So, for example, if the recipe is intended to track topic completions, tokens with the associated course and lesson names could be sent to Google Sheets.
Of course, the most popular complaint about canned reports is, “I need it to include a column name for the custom field I have for users”. No problem, any usermeta record can be added as a column in a Google Sheet. Pass data for job title, phone number, department, whatever user data you have stored.
I need to report on other WordPress data too.
This is another type of request we see all the time. When did a user last log in? How do I tell if users visited the first lesson of a course? How do I know what users watched a video?
Uncanny Automator of course supports more than just LearnDash, so here a few other trigger examples from other integrations that can be turned into LearnDash reports:
- A user views a page (WordPress core)
- A user logs in to the site (WordPress core)
- A user joins a group (BuddyBoss)
- A user watches at least 80% of a video (Presto Player)
- A user submits a course evaluation form (WPForms)
If you can think of an activity users might perform on your WordPress site, you can probably report on it. Better still, you can probably build it yourself in 5 minutes with no code whatsoever.
Connect LearnDash to Google Sheets
To connect Google Sheets to your WordPress sites, perform the following:
- Visit Automator > Settings in /wp-admin/ on your website.
- In the Google tab, click Connect an account.
- During the Google Sheets beta, you will see a warning that Google hasn’t verified the app. Click the Advanced link, then the Go to automatorplugin.com link.
- Click Allow to let Automator access your Google Drive files and spreadsheets.
That’s it! Once that’s done, you can pass data to Uncanny Automator in your recipes.
Building your first recipe with Google Sheets is a bit harder, but we’re almost there. The trigger part is easy. LearnDash students will always be logged in, so create a new recipe and choose Logged-in as the type. Choose LearnDash as the integration and then click the trigger you want to report on. Make the appropriate selections and proceed to the Actions section. This is the harder part.
To configure the Create a row action in Google sheets:
- Choose the Google Sheets integration in the Actions list.
- Choose the action to use in your recipe.
- Make selections for the Drive, Spreadsheet and Worksheet for your account. These values determine where the rows will be added and what columns will be available.
Tip: Add all the columns that you want in your spreadsheet before you create your recipe. Columns must be created in Google Sheets, Automator can only map to existing columns.
- After clicking the Get columns button, you will see a list of columns for your worksheet on the left and a list of values on the right.
- For each row, populate the Value that you want to add. In most cases, you will want to choose tokens from the triggers or the system by choosing the * icon to the right of the field.
- Save your updates and make sure the triggers, actions and recipe are all live.
Step 5 above (and in the screenshot) are where you will add all your user data and any information you want in your reports. For common information about the user, look for tokens in the Common section. For passing custom usermeta, use the Advanced section and this article. And for information related to your triggers, like course, lesson, quiz and other data, drill down into the list of tokens for that trigger.
One your triggers, actions and recipe are live, data will start flowing into Google Sheets automatically. Try out a sample and make sure everything is being sent as expected!
Customizing Google Sheet Reports
Now that you have data flowing into Google Sheets, it’s time to do something with the information and make it more useful. Because it’s Google Sheets, there are built in tools for sorting data, filtering data, charts, functions and more.
Maybe you created a report that shows a list of all lesson completions, but you only want to see completions for users in a particular department, for a specific lesson, within a specific date range. By selecting your data range and choosing Data > Create a filter inside Google Sheets, you can set up that exact view of your captured data.
Perhaps what’s most useful is a visual way of reviewing your data. In other words, instead of looking at thousands of reviews to try to figure out how lesson completions are trending (as a measure of learning activity), you can create a simple chart to show you completions over time. The Google Sheets chart function makes this easy (look for Insert > Chart to get started).
Linking LearnDash to Google Sheets with Uncanny Automator opens a huge number of reporting possibilities, but there are still some limitations that keep it from being the perfect reporting solution. Before jumping in, here are a few limitations that should be considered:
- You can’t limit data to certain groups only. This is largely a solution for admin or top-level reporting and Google Sheets can’t consider Group Leader access restrictions. Make sure only authorized users get access to the Google Sheets that receive data.
- You can’t edit or remove data. If an admin edits a user and resets progress for a course, there is no way to remove that progress in Google Sheets automatically. In other words, using manual overrides to undo completion records that are sent to Google Sheets would have to be reconciled manually.
- Google Sheets can store a maximum of 5 million cells of data. That might seem like a lot, but there’s a reason this article doesn’t mention using this solution for xAPI data. And, if you’re sending 10 columns of data every time someone completes a topic, and you have thousands of users and hundreds of topics, hitting the 5 million limit may be closer than you think.
- Automator can only send new data to Google Sheets. Since data is populated by recipes, records can only be sent for new activity after the recipes to create the reports are set up.
Ready to get started with passing your LearnDash records to Google Sheets for easier analysis? Here’s what you’ll need to start creating reports:
Don’t forget to share some of your favourite reports that you’ve built with Automator and Google Sheets in the comments!