How to Create a Station Video Dashboard Using Google Data Studio
Last Updated by
“How well did our local show perform online?”
You may be hearing this question more from your programmers and marketers these days. You know the data is available, but you’re not quite sure how to find it. Our friends at PBS have built a powerful system for tracking online video watching, but it is, admittedly, a little hard to decipher.
Good news: thanks to the power of Google Data Studio, you can easily build an interactive dashboard that will allow you to easily look up data on your station’s online video analytics and generate regular reports. I’m going to provide you a template you can copy and step by step instructions on how to set it up with your station video analytics data. I won’t go into the details of why it’s set up the way that it is, but just trust me that it’ll work. ;)
This won’t be a how-to on Google Data Studio per se — there are plenty of videos and tutorials elsewhere — and in fact, after you’re done with setting up your template, you’ll have taken a step to the next level in Data Studio, because we’re going to use (drum roll, please) calculated fields to extract the information we want in a way that will make reporting so. much. easier.
First off, you need to have Google Analytics event tracking set up on your PBS videos. Don’t know if you have it? Ask your SPI rep.
What information will you get? You’ll find out how many views your local station videos received, plus any viewing that occurred on your PBS-hosted local station portal (in our case, video.kpbs.org), and Passport-only program viewing by your station members. This is kind of confusing, so here’s a brief overview (vetted and approved by PBS’ very own Dan Haggerty):
Type of Video
PBS owned video apps for Mobile and OTT devices
Local Station Videos
National Programs: Free Streaming Window
Streams by a station’s Passport members only
National Programs: Passport Streaming Window
So here’s a step-by-step tutorial:
A. Grab the Template:
Log in to your Google account that you use for your station Google Analytics (which you’ll also see me refer to as “GA”). Then open this link to my Data Studio template and click the USE TEMPLATE button in the upper right corner. If you haven’t used Google Data Studio yet, you’ll be asked to agree to their Terms of Service.
You’ll be prompted to connect a data source. Select CREATE NEW DATA SOURCE.
On the next few screens, select Google Analytics as your data source, and go through the various steps to authorize Google Data Studio to access your Google Analytics data. Connect to the specific Google Analytics property and view that your PBS Video Player event tracking data is fed to. In most cases, it will be a different property than your main website pageview tracking.
B. Prepare Your Fields
After you click the CONNECT button, you’ll see a rather overwhelming screen of dozens of dimensions (green) and metrics (blue). Don’t worry, we only need a few of these — and the ones that we use, we’ll be customizing and putting into plain English.
We’re going to translate some of the Google Analytics event tracking gobbledygook into terms that are easier to understand for reporting purposes. We’re also going to extract program names and episode titles from the existing data, so that we can aggregate program data.
Translation: Modify Field Names
First, we’re going to translate the GA “event tracking” fields into what they represent for PBS Video (you can find a full explanation on docs.pbs.org). Here’s a summary:
Google Analytics Term
Program and Episode
Platform (the video player or experience where the stream occurred)
Action (the video event that occurred, e.g. "MediaStart" when a stream begins)
From the screen listing all the fields available, enter “event” in the search box to the right. You can then click on the Field name and replace the existing field name (Event Label) with the new name (Program and Episode). Do the same for the fields listed in the table above.
C. Extract Program Names and Episode Titles
Now we start getting fancy. You’re going to create two new calculated fields that extract the program names and episode titles using RegEx, a super geeky and super handy way of filtering the data based on patterns. You don’t need to learn it to use it in this case -- just copy and paste what I show you below.
So, first, click the + ADD A FIELD link in the upper right corner of the Fields screen:
Enter “Program Name” for the field name, and paste the following into the formula box.
REGEXP_EXTRACT(Program and Episode, '(^.*?)\\s\\|.*\\|.*\\|?.*\\|.*$')
I also like to give the Field ID a more recognizable name, like calc_program_name, but it’s not necessary.
Click the SAVE button in the lower right, then click the ALL FIELDS link in the upper left when you’re done to go back to the main list of fields.
Create another calculated field, named “Episode,” with this formula:
REGEXP_EXTRACT(Program and Episode, '^.*\\s\\|(.*\\|.*)\\|.*$')
D. Create Field: Video Type
Next you’ll create a new calculated field that extracts whether a video is an episode, clip, or preview. We’re going to use this to filter ONLY views of program episodes, and exclude previews and clips, which can inflate the view count and deflate the average duration calculations (which I don’t cover in this tutorial).
Field Name: Video Type
REGEXP_EXTRACT(Program and Episode, '^.*\\s\\|.*\\|.*\\|(.*)$')
E. Start Editing the Report
Now that you’re done editing/creating fields, you can click the blue ADD TO REPORT button in the upper right corner.
You’re now in the EDIT view of the report. It looks broken, but don’t panic -- once we’ve connected all the fields up properly, and added filters, it’ll be a thing of beauty.
F. Update the missing Program dimension:
Since we’re relying on custom calculated fields for the Program dimension, we have to re-attach that field to the filters and charts that call on it.
First, go to the Resource menu at the top, and select Manage filters. Click the EDIT link for the MediaStart (Episode Only) filter.A. You’ll see that the second part of the filter is missing information:
B. Click on the first pink Missing field, and select “Video Type.” Then fill in the last field with “Episode.” Your completed filter should look like this:
C. Be sure to click SAVE at the bottom of the screen.
Next, Let’s start at the top. Cclick on the Program filter dropdown in the upper right corner. As soon as you’ve selected it, you’ll see that the right sidebar changes, displaying information about the data source, and in the Dimension section, it says “Invalid dimension”:
Click on the pink Invalid dimension field, and use the search function in the Dimension Picker dialog box to select your newly-created calculated field “Program Name”:
Repeat the process with the bottom two “Top Programs” charts on page 1 and the top chart on page 2 which displays Passport viewing data:
On the third page, the Episodes page, you’ll want to select Program Name and Episode (in that order) for the missing dimensions:
And that’s it! Now you can enjoy the fruits of your labor.
If you missed any of the field setup steps, you can go back to the Fields editing screen by selecting the Resource menu > Manage added data sources. However, bear in mind that if you created the data source without one of the custom fields, some of the filters may need to be fixed. Contact me if you need help with that process.
Do you need access to Media Manager, Curate, or just need a little help? File a ticket @ digitalsupport.pbs.org.