How to Track iTunes Podcast Rankings Automatically with Google Sheets, Xpath and RSS Feeds

Posted on Categories Uncategorized 2
podcast rank tracking

Edit March 12, 2018: The iTunes xml that this method uses to fetch the data seems to work again. The method should we valid as is but be aware that if the iTunes feed breaks or they change something, you won’t get data anymore.

Edit, February 24, 2018: The iTunes xml that I used to fetch the data from has apparently been changed. A URL such as https://itunes.apple.com/fi/rss/topaudiopodcasts/limit=100/genre=1301/xml is no longer valid, so the method fails. If you find a working RSS feed for your genre, the methodology is still valid though. Best of luck!

Do you want to track the iTunes Chart rankings of your podcast but you don’t want to do it manually? Luckily there’s a fairly simple way to automate tracking your podcast rankings with Google Sheets, Xpath and the iTunes Chart RSS feeds.

You don’t need to be an expert to follow along. I’ve compiled this method through trial and error, and this article also includes a sample Google Sheet you can reference.iTunes podcast rankings

What You’ll Learn in This Article

1. How to Find the RSS Feed for the iTunes Podcast Chart you want to follow

2. How to use the IMPORTXML function to pull your podcast rankings to Google Sheets

3. How to automate a script that saves your rankings daily

What You’ll Need

  •  A Google account
  • Mean copy & paste skills
  • Some GoogleFu

Questions You Need to Answer Before Getting Started

What is the iTunes Chart I want to track? For example, the relevant genre for my literature podcast Kertojan ääni is Arts, so I want to track how it’s doing in the iTunes Top Audio Podcasts in Arts Chart.

You can choose all podcasts, a certain genre (Arts) or a certain sub-genre (Literature).

Do I want to track only my podcasts or also my main competitors? This article focuses on tracking only your own podcast but tracking multiple podcasts is easy too – just a matter of duplicating the process.

Throughout this article I’ll use my podcast as an example. To make it work for you, just substitute your podcast with mine.

1. How to Find the RSS Feed for the iTunes Podcast Chart You Want to Follow

You’ll be scraping the podcast rankings from the official iTunes Podcast RSS feeds, so first you’ll need to find the relevant iTunes RSS Feed. Not the feed for you podcast, mind you, but the one for top podcasts in iTunes.

The format for the RSS Feeds is this:

https://itunes.apple.com/fi/rss/topaudiopodcasts/limit=200/genre=1301/xml

I’ve bolded the sections you need to change to find your chart. ”fi” refers to the country (Finland) and genre=1301 refers to the genre (in this case Arts).

Let’s say you want to track the top audio podcasts in the US and the genre Business. First, you’ll need to get the code for the Business genre from iTunes. Here’s a page that lists all the genres. Find your genre or subgenre and copy the four-digit code. For Business it’s 1321. Then you’ll need to change your country. The country code for the US is us.

With these parameters you can construct the RSS Feed for the chart you want to track:

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

With the Chrome browser you can click the link and make sure the fifth row <title> includes your genre. Make a note of this URL, you’ll need it in the next phase.

2. How to Use the IMPORTXML Function to Scrape Your Podcast Rankings to Google Sheets

In this phase, you’ll use the IMPORTXML function in Google Sheets to find your podcast (it needs to be among the 200 top podcasts to show up) and scrape your ranking from the iTunes Chart.

1. Make a new Google Sheets document

2. In cell A1 write =now()

3. In cell A4 write Date

4. In cell B4 write Your Daily Podcast Rank (or whatever you want)

5. In cell B1 copy and paste this function:

=IMPORTXML("https://itunes.apple.com/fi/rss/topaudiopodcasts/limit=200/genre=1301/xml","count(//*[local-name()='entry'][contains(.,'Kertojan ääni | Kirjallisuus ja kirjoittaminen - Essi Pulkkinen ja Jukka Ahola')]/preceding-sibling::*)")-7

Test that it works – that is, that it fetches a number. If it doesn’t, there’s something wrong, because that’s the function that works for me and the example Google Sheet. However, check that you’ve copied the whole thing and that you’re using the right type of quotation marks (” and ‘). This page changes the quotes to curly quotation marks, so you may need to change them manually to what you see in the image below. Note that you also need to change the single quotation marks.

What type of quotation marks to use

6. Copy to clipboard the URL you made in phase 1.

7. Replace the URL that’s within the function in cell B1 with that URL.

When you replace the url in bold with the URL for your iTunes Chart:

=IMPORTXML("https://itunes.apple.com/fi/rss/topaudiopodcasts/limit=200/genre=1301/xml","count(//*[local-name()='entry'][contains(.,'Kertojan ääni | Kirjallisuus ja kirjoittaminen - Essi Pulkkinen ja Jukka Ahola')]/preceding-sibling::*)")-7

it becomes this

=IMPORTXML("https://itunes.apple.com/us/rss/topaudiopodcasts/limit=200/genre=1321/xml","count(//*[local-name()='entry'][contains(.,'Kertojan ääni | Kirjallisuus ja kirjoittaminen - Essi Pulkkinen ja Jukka Ahola')]/preceding-sibling::*)")-7

8. Replace the <title> that’s within the function in cell B1 with the <title> of your podcast.

When you replace the title in bold with the title of your podcast:

=IMPORTXML("https://itunes.apple.com/fi/rss/topaudiopodcasts/limit=200/genre=1301/xml","count(//*[local-name()='entry'][contains(.,'Kertojan ääni | Kirjallisuus ja kirjoittaminen - Essi Pulkkinen ja Jukka Ahola')]/preceding-sibling::*)")-7

it becomes this

=IMPORTXML("https://itunes.apple.com/us/rss/topaudiopodcasts/limit=200/genre=1321/xml","count(//*[local-name()='entry'][contains(.,'The Indicator from Planet Money - NPR')]/preceding-sibling::*)")-7

(Please note that in the previous example the name of my podcast is The Indicator from Planet Money – NPR. Remember to check the single quotes and the exact spelling of your title.)

9. The cell B1 in your Google Sheet will now take some time to load your rank but if everything works correctly, you should now see your rank in cell B1. If you see an error message, check the spelling of the function.

automatically save podcast ranks

3. How to Automate a Script that Saves Your Rankings Daily

In this phase you’ll create a Google Sheet script that saves your podcast rankings daily. For the script, I’d like to thank the writer of this wonderful post about saving data with a Google Script.

1. In Google Sheets, press Tools > Script Editor…

2. Overwrite the code in the field with this:

// 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]);
}

podcast script

3. Name the script SaveData, then save the script

4. Refresh the Google Sheet you made in Phase 2

5. Locate the new Custom menu that you made with the script. It’s the last menu item.

6. Press Custom menu > Save Data

7. Authorize the Script and give it the permission it needs (this is safe)

8. Check that in row 5, you see a timestamp (A5) and your rank (B5)

You can now run the script manually and a new row will be appended to the sheet. However, we’ll want to create a trigger that runs automatically every day. This way you’ll podcast rankings will be saved even if you don’t look at the sheet every day.

9. Open Tools > Script Editor… again

10. Press Edit > Current Project’s Triggers

11. Click the ”No triggers set up…” text

12. Choose saveData for the first dropdown, Time-Driven for the second, Day timer for the third and 5am to 6 am for the fourth. It should look like this:

(Note that you can set the time it runs according to your wishes: daily, hourly, weekly)

13. Press Save and you’re done!

Final Thoughts

With this setup, your Google Sheet will be updated daily with the podcast ranks for your podcast. The setup is fairly brittle because it relies on the iTunes RSS feed not changing. If the URL or the xml structure changes, the code breaks and you’ll need to do some sleuthing to get it working again.

It’s also important to note that the iTunes RSS feed that you’re scraping doesn’t update at the same time as the iTunes chart in your phone. This may lead to your phone and the sheet showing different rankings if you check them at different times.

Also, if you want to track more than one podcast or more than one podcast chart, you’ll have to modify the script which is not difficult. I’ll leave you with the script for tracking two podcasts at the same time:

// 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();

var rivalpodcast = sheet.getRange(’C1′).getValue();
sheet.appendRow([date,yourpodcast]);

}

In addition to the change in script, you’ll need to do a similar process to get the rankings of your rival podcast to cell C1.

Thanks for following along, I hope this helps you or your company to start tracking your podcast rankings. If you’re a fan of Finnish literature podcasts, be sure to check out our podcast Kertojan ääni.


Jukka Ahola is a Finnish writer, podcaster and marketer.

podcast rank tracking

Comment

*

  1. I think the rss generator omits podcasts that are flagged explicit. Do you know a way to get around that and show both explicit and non explicit podcasts?

Other articles