Last week we posted our first blog post on how to use Zapier to create marketing automation workflows for your webinars.
This week, I decided to publish a small experiment I did while playing around with Zapier.
If you are a spreadsheet nerd like me, you are probably going to like this.
Long story short, I created a very rough webinar dashboard inside a spreadsheet based on the data fetched via Zapier.
In this post, I’ll show you step-by-step how to achieve this.
Feel free to copy the spreadsheet and reuse it for your own webinars.
Let’s get to it.
How to Start Building a Webinar Dashboard in Google Sheets
As I said the easiest way to get started is to copy the spreadsheet. I won’t get too much into the formulas details, but I’ll give you a quick tour.
You have 4 sheets in the file:
- logs leads: log all the data every time someone joins or registers to a webinar.
- logs webinars: log all the webinars data (name, date, presenter, etc.).
- leads: a automated list of all the unique leads.
- dashboard: global metrics and graphs on your webinar activity.
What Metrics Should my Webinar Dashboard Display?
So far, I gathered a few very basics metrics:
- Number of companies
- Number of leads
- Number of qualified leads: count unique leads who said “Yes” to a demo question / number of webinars
- Average number of qualified leads per webinar: count unique leads / number of webinars.
- Average number registrants per webinar: number of events “registered webinar” / number of webinars.
- Average number attendees per webinar: number of events “joined webinar” / number of webinars.
- Average attendance ratio per webinar
You can add a lot more metrics like: the number of webinars per month (probably require a pivot table) or the best performing webinar based on the number of leads.
If you have a Google Analytics integration on your webinar registration page, you can fetch your pages data on Google Sheets with the GA extension.
And, BTW we wrote an email crash course recently in case you missed it, in which we mention how and what to monitor your webinar performance.
How To Set Up Your Zapier Triggers For Your Webinar Dashboard
To make this webinar dashboard you will need three zaps:
- One for when an attendee registers
- One for when an attendee joins
- One for when a webinar is published
First, let’s set up a Zap with our trigger “when an attendee registers” to a webinar.
Now, we need to add a filter we because Livestorm is sending me timestamps and I need to format that date in order to reuse it in my dashboard.
Third step, let’s template the spreadsheet. Just paste the right data in the right columns. Remember, you can add as many columns as you want. The idea is to create a a very detailed log sheet of all your attendees.
Now your data is flowing from your webinars to your spreadsheet. All you need to do now is:
- Repeat the process for when an attendee joins the webinar
- Repeat for when a webinar is published
7 Other Apps You Can Use to Get The Most of Your Webinar Dashboard
There are several ways push this dashboard even further. Using Zapier, there are a bunch of apps that can push this webinar way beyond my very rough example.
1. Enrich your leads with Clearbit
I mentioned Clearbit in my previous post but what I forgot to tell you guys is that Clearbit has an official Zapier app!
So, basically, if you want to enrich your leads before sending them to your dashboard, all you have to do is add a step before the Google Sheet action. Then add Clearbit and choose “Find Person and Company information”.
2. Filter leads to evacuate existing profiles in your CRM
This can be helpful if you don’t want to add profiles that already exist in your user base.
The logic is simple:
- if someone registers to a webinar, find him in a Google Sheet or a Mailchimp list.
- Then add a filter that adds an “only continue if” statement only in the case the lookup is empty (aka Zapier did not find the corresponding row).
- Finally, add the lead to your Google Sheet webinar dashboard
4. Sync your CRM and your webinar dashboard every day
Sync your dashboard with your CRM by doing scheduled syncs every day after work between your CRM and your webinar dashboard spreadsheet.
This is pretty cool for two reasons:
- It’s a no-brainer. It works in the background
- It allows you to play around with your dashboard during the day
First, create a Google Sheet trigger that will look for new updates on your leads sheet.
Now, you want to delay those updates by 12 hours so that you make sure to push everything when you’re done working on it.
5. Store your data in a DB for powerful SQL queries + beautiful webinar dashboards
This is really advanced, but let’s say you want to display your spreadsheet data on a TV screen with a cool dashboard. To do so, you a need a real DB in which you will be able to perform complex queries.
Fortunately, Zapier provides an integration with SQL databases, Postgres, MongoDB, and even Amazon DynamoDB.
From there you can add a step to create an item in your DB of choice.
6. Push your webinar dashboard updates to a webhook
Another trick if you want to push this data to another app, or an app that does not integrate with Zapier (Customer.io is a good example), is to use the app’s incoming webhook and push everything in there.
Perform a POST request to your webhook and send you webinars data in there.
7. Push to your marketing automation software and retarget your Lead
Last use case. Let’s use our webinar dashboard to retarget my leads in whatever channel.
I could use Zapier to retarget directly, but if you’re like me you know that Zapier can only handle a certain level of complexity.
So let’s just push the lead to your marketing automation software and create your own powerful retargeting process. Let’s just use Zapier to push your leads from one software to another (that is until we deploy our native integration ;))