Blog
I’ll take you through how to use YouTube Analytics to grab data from your YouTube channel, download it, handle it in Excel, and by a series of simple calculations figure out which videos on your channel is actually the most popular.
Estimated read time: 8 minutes
I’ve talked about using YouTube Analytics before, figuring out what kind of content your current subscribers view the most, but this time around I want to use Analytics to find your just generally most popular videos.
It should be obvious why knowing what the most popular videos on your channel are is important, but in case it’s not, let me break it down for you. Knowing the most popular videos on your channel, tells you what kind of content your viewers engage with, what they like.
It tells you what sort of openings worked well, what games or vlogs worked well with your style, what kind of graphics did well, length, mood, and so on.
And most importantly, it tells you what you can do to improve all your future videos!
You can use this information to figure out what videos you should use to get the most out of your paid campaign, which videos are best to promote, what videos to prioritize when optimizing your videos for organic search, and which videos are best to try and replicate.
In the vast majority of cases, when I ask anyone with a YouTube channel to show me their most popular videos, one of two things happen:
1) They show me the videos with the most views.
2) They show me the videos with the most likes.
Now, you might be thinking “OK, right so far.” But no, not right so far. So so wrong, and let me tell you why.
YouTube Views do not equal Popularity
Let’s start with the first one. Your most watched video isn’t necessarily your most popular one, chances are that it is merely the video on the channel, that happens to be ranking on a high-volume keyword by accident, it may have been mentioned by another large YouTuber, it may have been a successful post on Reddit, and so forth.
There are a million (well, maybe not million) different reasons that video could happen to have the most views on your channel, and the majority of them wouldn’t necessarily have anything to do with how much people actually liked that particular video, compared to your other videos.
This is simply because people forget how many ways there are for people to find and view their videos, and that these needs to be accounted for, if views are to be used for anything.
In fact, betting anything on the amount of views a video has, without looking at corresponding factors such as Viewer Retention (also available in your YouTube Analytics, by the way), is a very dangerous thing to do in any case.
Your most Liked video isn’t the most popular either
The next mistake is to take the video with the most Likes, but this falls into the same trap as above. See, it’s pretty logical that the more views a video gets, the more Likes it will most likely get. Why? Simply because there are more people on the video, actually able to “Like” it.
To clarify, let’s set up a hypothetical scenario: You have two videos, Video A, and Video B. Video A has 1000 views, and 100 Likes. Video B has 100 views, and has 80 Likes. Which video do you think people enjoy the most? Obviously Video B – while it only has 80 Likes, compared to the 100 Likes of Video A, almost every person who watches the video Liked it, where only 1 in 10 did so with Video A.
So it’s pretty obvious that total Likes isn’t a good indicator either, for your most popular video.
Additional factors to consider
Next up, is that there are many more factors to consider, than just “Likes” on your YouTube videos, and we can access all these through YouTube Analytics.
Playlist Adds (the number of times the video has been added to a playlist or Favorites) is a great indicator too. Why do you add something to a playlist? Either to watch it again later, or to collect it in a list that you want to share wither others. It’s essentially the “bookmark” of YouTube – a massive indicator of good content.
And what about Shares? Obviously if someone shared your video, that’s a pretty darn big indicator that they liked the video (or really disliked it!), otherwise they wouldn’t want to share it with others.
And last, Subscribes! The biggest “I liked this enough to want to be updated when you release more!” statement on YouTube! Sadly, most people go to the main channel page (most likely to check out what other content is on the channel) to subscribe, so often this number is very low on individual videos.
Comments is another great indicator. Sure, your comment section could all be filled with hatred, but let’s assume for a second that’s not actually the case, and like most channels the vast majority of your comments are in fact positive.
These are all obviously incredibly strong indicators of how much someone likes a video, and definitely need to be calculated in when we’re looking for the most popular videos on a channel.
What about Dislikes? As we discussed in the Native YouTube Ranking Factors analysis here, dislikes are most likely seen by the YouTube ranking algorithm as “controversial”, more so than “bad”. So if we’re analysing these videos for the purpose of optimizing them, we’d include this number in our calculation. However, since we’re analysing this more from a user perspective, we’ll forego it in this case. But again, it’s important to remember that “dislikes = // = bad”.
Getting the work done with YouTube Analytics and Excel
OK, so to get get started, log into your account, and go to your “Creator Studio” dashboard (click on your profile image in the top right of YouTube to get the menu option).
Once there, you need to go into your Analytics – it’s clearly labelled in the left side menu.
Once there, you need to do two things:
1) Go to the “Watch Time” tab, a bit further down the list. 2) In the top right corner, you’ll most likely see a tab that says “Last 28 days”, and next to it a little calendar icon. You need to click on that icon, and set the start date to the beginning of 2013 (we do this, because YouTube have changed analytics data over the years, and using “Lifetime” makes that analytics data unusable).
Obviously if your channel hasn’t existed that long, user a later date.
Once you’ve done that, you need to click on the “Export Report” report just above the button for choosing dates, and click on “Microsoft Excel (.xls)”. You’ll automatically download an Excel file – if you’re prompted first, accept the download and save it.
Open it up in Excel, and go to the far right, and add a new column and call it “Total Positive Interaction”. In the first cell beneath it, add the numbers for the following factors together:
Subscribers gained, Videos added to playlists, Comments, Shares, and Likes added. It should be the following formula: =BA2+AX2+AV2+AU2+AP2
This now gives you the total amount of positive interaction users have had, with that specific video. Once you have that, either copy/paste it all the way down for all videos, or simply grab the lower-right corner of the cell, and pull it all the way down. You have now that data for all your videos.
(Note, if you have a very large channel, YouTube only exports a set amount of videos – I believe it’s 200, and there’s no way around that)
But as we discussed earlier, having the total amount of something doesn’t mean a lot, because chances are the videos with more views (which we showed wasn’t a good indicator) will have more interaction.
So next up, we divide this number with the amount of views per video.
So make a new column after the previous one, and name it “Positive Interaction per View”. In it, we divide the number from “Total Positive Interaction”, with the total amount of views for that particular video. The formula should be: =BC2/F2
Once you have that, as before, drag that formula down for all the cells, so we can get the number for all your videos.
Sorting and Cleaning up the final Analytics data
Ok, so now we have the total amount of positive indicators for each video, divided by the amount of views that video has – “positive interaction per view”.
But it’s still pretty difficult to get an overview of this data. So what we need to do, is click on any of the top cells, go to the “Data” tab in Excel, and click “Filter”. It has a tract kind of icon, and sits right in the middle of the menu line.
Once you have that, click on the little grey box in the “Positive Interaction per View” cell, and select “Sort by Largest first” (you’ll have to excuse the Danish screenshot!).
What this has done, is sort the list of videos, with the videos that had the highest ratio for positive interaction per view, at the highest of that list. That’s great and all, but this isn’t a perfect method, and we need to clean up the data from here on.
You need to ensure that all the videos on this list, is in roughly (just roughly) the same view bracket – specifically, any videos with very few views, compared to the others, tend to dominate the list. This is because the relative importance of a single positive interaction, if the views are very low.
Exactly where you do this cutoff is up to you, but you can see an example from my own YouTube Analytics down below.
Finishing up and moving forward
And there you have it – how to find your actual most popular videos on your YouTube channel, by using YouTube Analytics, a bit of simple math, and Excel. This has been a long post, but once you get the process down it’s really quite easy, and shouldn’t take you more than 5 minutes to do.
Once you have this data, you should have a firm look at the top ranking videos on your final list. Identify anything that those videos do, or contain, that stands out compared to the rest of the videos. Perhaps look at the videos lowest on your list, and compare them to the videos highest on the list. What do you see?
Use this knowledge to improve all your future videos, as well as figure out what videos you should generally try to push, or feature on your channel.
Best of luck with your YouTube channel, and I hope to see you around!
Philip Zeplin
I hold a Masters Degree in Visual Communication from the Royal Danish Academy of Fine Arts, as well as having studied Marketing Economics at the Copenhagen Business School.
I used to work as a YouTube & SEO Consultant at iProspect in Copenhagen, one of the worlds largest online marketing firms companies, part of the Dentsu Aegis Network.
I now run my company NovelConcept, focused solely on organic marketing on YouTube, as well as the associated software Rankify Analytics.
Latest posts by Philip Zeplin (see all)
- Great things are coming to YouTube markerters in 2018 - December 22, 2017
- Key Takeaways from VidCon 2016 (3/3) – Have a strategy! - July 18, 2016
- Key Takeaways from VidCon 2016 (2/3) – Back to (Refined) Basics - July 12, 2016
