How To Automatically Track Your Podcast Ranking In Google Sheets

how to track your podcast rank
Share on facebook
Share on linkedin
Share on pinterest
Share on google
Share on twitter
Share on reddit
Share on email
By the end of this article, you will be able to track your podcast in the top 200 of your category in the Apple Podcasts charts.

Unfortunately, there isn’t an easy way to track your podcast ranking compared to the competition.

Yes, you can search on the Apple Podcast app to see how your podcast SEO is doing but this doesn’t factor into your ranking.

The ranking is how you stack up next to other podcasts in your category. I’ll show more about podcasts categories below.

I’ve found a way to track your podcast ranking each day automatically. So if you have wanted to know how to track your podcast ranking then keep reading.

I’ll show you how to do this in a Google Spreadsheet. (<<< if you don’t already have a Google account, use that link)

I originally saw instructions on how to do this on the Jukka Ahola blog.

 

Step 1: Find your RSS feed to track in Apple Podcast

So, in order to track your podcast ranking we need to find the correct RSS feed within iTunes it should look at.

Below is the format of the RSS feed you will need.

https://itunes.apple.com/us/rss/topaudiopodcasts/limit=200/genre=1321/xml

You’ll notice there are two bolded parts of the above RSS feed. The “us” tells it to look at the USA podcast charts. The “1321” tells it to look in the Business section.

Here you can find the complete Apple Podcast category and subcategory codes.

When I created my tracker I used “us” and “1413”, which is for Marketing and Management.

https://itunes.apple.com/us/rss/topaudiopodcasts/limit=200/genre=1413/xml

Copy and paste that into your URL bar and go to the webpage. You should see the name of podcast category you input in the 5th or 6th row, as shown below.

track your podcast ranking

 

Check out the 26,000 word Ultimate Podcasting Guide now!
 

Step 2: Get your podcast rank by using the IMPORTXML function in Google Sheets

Follow the steps below to set-up your Google Sheet so that you can track your podcast ranking.

  • Open a new Google Sheet
  • In cell A1 write =now()
  • In cell A4 write Date
  • In cell B4 write Your Daily Podcast Rank (or whatever you want)
  • In cell B1 copy and paste this function:

=IMPORTXML(“https://itunes.apple.com/us/rss/topaudiopodcasts/limit=200/genre=1413/xml”,”count(//*[local-name()=’entry’][contains(.,’Hustle To Freedom: Everyday People Creating Extraordinary Side Hustles’)]/preceding-sibling::*)”)-7

google sheet formula for podcast ranking

To pull in the information for your podcast, you’ll need to update some of the information in that formula shown above.

=IMPORTXML(“https://itunes.apple.com/us/rss/topaudiopodcasts/limit=200/genre=1413/xml“,”count(//*[local-name()=’entry’][contains(.,’Hustle To Freedom: Everyday People Creating Extraordinary Side Hustles’)]/preceding-sibling::*)”)-7

Change the above-bolded section to the information of your podcast. Meaning, the country and the category associated with your podcast.

=IMPORTXML(“https://itunes.apple.com/us/rss/topaudiopodcasts/limit=200/genre=1413/xml”,”count(//*[local-name()=’entry’][contains(.,’Hustle To Freedom: Everyday People Creating Extraordinary Side Hustles‘)]/preceding-sibling::*)”)-7

Change the above-bolded section to your exact podcast title.

Note: If you are having issues, check the single quotes used in your function. The issue may be due to formatting in the page copied from. You can try to delete and retype every single quote. I explain this in the video course in detail.

Now, the Google Sheet should display your function will output your current podcast ranking. If you get a “-7” in the rank field this just means that your podcast was not found in the top 200. If you get some other error, check the spelling within the function.

daily podcast rank tracker

Step 3: Automatically track your podcast ranking daily

It’s cool to know what your podcast ranking is in the current time, but what is even cooler is to be able to track your podcast over a period of time. In order to do this, you will need to create a script that runs in the background to save this data each day.

Now, let’s take a look at how to do this:

  • In your Google Sheet we just added that function to, go into the top navigation menu and navigate to Tools > Script Editor
  • Delete any code that you see
  • Paste in the below:

// custom menu function
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom Menu’)
.addItem(‘Save Data’,’saveData’)
.addToUi();
}
// function to save data
function saveData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var date = sheet.getRange(‘A1’).getValue();
var yourpodcast = sheet.getRange(‘B1’).getValue(); sheet.appendRow([date,yourpodcast]);
}

google sheet script for tracking podcast rank

  • Name your script then click save
  • Go back to your spreadsheet and refresh the page
  • After refreshed, notice the new Custom Menu as shown below

Note: If you are having issues, check the single quotes used in your function. The issue may be due to formatting in the page copied from. You can try to delete and retype every single quote. I explain this in the video course in detail.

podcast ranking tracker in Google Sheets

  • Now, click Custom Menu > Save Data
  • It will ask for authorization to run, click accept.
  • The script is programmed to input data into Row 5 so you should now see data in cells A5 and B5.

As is, the script will only run when you press the button, like we just did. But we want it to run everyday so let’s keep going with the setup.

  • Open the script editor again. Tools > Script Editor
  • Go to Edit > Current Project’s Triggers

custom formula to track podcast ranking in google sheets

  • Click on “no triggers set up”
  • Choose the below settings

project trigger script for podcasts

  • Click Save

Now your script will be running daily. By adjusting the right-hand dropdown, you can update the time to have it run whenever you want.

You can repeat this process with a competitor and how the information input into the column next to your rank. This way you can see how your ranking fluctuates compared to someone in your category.

Note: This script could break if anything changes in the Apple Podcast RSS structure. If it breaks, just send me a message on Facebook and I’ll fix it.

 

Ryan Helms

Ryan Helms

Ryan is the co-founder of Legacy Podcasting. He writes about how entrepreneurs and brands can leverage audio as a content platform to grow their authority and reach, while building a stronger community.

Leave a Replay

Download the Ultimate Guide To Podcasting now!

Get our 100+ page guide that will walk you step-by-step through launching a podcast that skyrockets your authorityreachleads, and community of people that eat up your every word.

the most comprehensive resource about podcasting on the entire internet!

Get our 100+ page guide that will walk you step-by-step through launching a podcast that skyrockets your authority, reach, leads, and community of people that eat up your every word.

JOIN THE 21-DAY PODCAST LAUNCH CHALLENGE NOW!

Launch A Successful Podcast In 21-Days With NO Experience, Education, OR Tech-Skills Required!