How to Create a Station Video Dashboard Using Google Data Studio
Last Updated by
Updated October 2018 to include full_length video types in episode filters. Also note that in the original post, I referred to the “Episode” field; I have since renamed it the Video Title field, so that it applies to clips and previews as well.
For those of you who used the original method, please refer to this fix.
“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 (Video) 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” (or “Video Title” if you’re going to report on clips and previews also) 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.
You’ll see that the second part of the filter is missing information:
[UPDATED OCTOBER 2018] Click on the first pink Missing field, and select “Video Type.” Make sure it says RegExp Contains in the middle sector, and then fill in the last field with “(?i)Episode|full_length.” Your completed filter should look like this:
Be sure to click SAVE at the bottom of the screen.
Next, click 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:
G. Clips and Previews [ADDED OCTOBER 2018]
Many of you asked how to display clips or previews. To create a Clips and Previews table, copy the Episodes table. Remove the MediaStart (Episodes Only) filter from the new table, and click ADD A FILTER in the sidebar to create a new filter named MediaStart (Clips or Previews) as follows:
Include > Action > Equal to > Media Start
Include > Video Type > RegEx Contains > (?i)(clip|preview)
This table is built in to the updated Data Studio template, so if you’re just now getting started with your copy of the template, start by selecting the filter on the existing table, and update the Video Type RegExp Contains field.
Now, you may be wondering, What’s with the (?i) in front of the episode|full_length and clip|preview? Was that a typo? Can I just delete it cuz it’s ugly? Gentle reader, do not fear that which is unfamiliar. The (?i) is a regular expression that tells the system, “Pay no heed to the capitalization of the following words.” In other words, it makes the filter case insensitive. Why is that important? Because, up until this summer, Episode, Clip, and Preview were all listed in Title Case. Over the summer, that convention was switched to lower case full_length, clip, and preview. By making the filter case-insensitive, we are ensuring we get both old data and future data. Tada!
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.