James Cridland

Data mining the Podcast Index

James Cridland and Adam Curry

The Podcast Index - released by Adam Curry and Dave Jones more than five years ago - is a super-useful thing to go data mining into the wide world of podcasts.

There’s a weekly-updated file containing every public feed in the Podcast Index - it’s here, and is about 1.8GB. Unzip it, and it becomes a 5GB file, which you can “easily” interrogate using a thing like this SQLite browser.

Once you’ve installed the SQLite browser - if you have Homebrew installed, it’s just brew install --cask db-browser-for-sqlite …

  • Open the podcastindex_feeds.db file.
  • Hit the “Execute SQL” tab.
  • Post one of the SQL statements below.

I’m mostly posting this so I have a note of them.

Here is the SQLite browser

How many podcasts have been updated in the past year?

This looks at your data dump to find out the latest updated show, and then displays a count for how many were updated in the year up to that date.

SELECT
    SUM(IIF(newestItemPubdate > ((SELECT MAX(newestItemPubdate) FROM podcasts) - 31556952), 1, 0)) AS inlastyear,
    SUM(IIF(newestItemPubdate < ((SELECT MAX(newestItemPubdate) FROM podcasts) - 31556952), 1, 0)) AS older
FROM podcasts;

How many podcasts have only published one episode and then failed?

(This query ignores the last 30 days)

select count(*) from podcasts where episodeCount=1 AND newestItemPubdate < ((SELECT MAX(newestItemPubdate) FROM podcasts) - 2592000)

How many active podcasts have only published one episode in the last 12 months?

(Again, this query ignores the last 30 days)

SELECT
    SUM(IIF(episodeCount=1 AND newestItemPubdate > ((SELECT MAX(newestItemPubdate) FROM podcasts) - 34148952), 1, 0) AND newestItemPubdate < ((SELECT MAX(newestItemPubdate) FROM podcasts) - 2592000)) AS onlyoneepisode,
    SUM(IIF(newestItemPubdate > ((SELECT MAX(newestItemPubdate) FROM podcasts) - 34148952), 1, 0) AND newestItemPubdate < ((SELECT MAX(newestItemPubdate) FROM podcasts) - 2592000)) AS allactive
FROM podcasts;

How many shows are copied on Spreaker?

This lists established shows that exist at least twice in the Apple Podcasts database - once on Spreaker, once somewhere else. The Spreaker copy needs to have the same number of episodes as the other one. Ignore Librivox shows.

SELECT id, title, itunesid, episodecount, itunesauthor, host, url
FROM podcasts
WHERE NULLIF(TRIM(title), '') IS NOT NULL
  AND NULLIF(TRIM(itunesid), '') IS NOT NULL
  AND itunesid <> 0
  AND host <> 'librivox.org'
  AND (title, episodecount) IN (
      SELECT title, episodecount
      FROM podcasts
      WHERE NULLIF(TRIM(title), '') IS NOT NULL
        AND NULLIF(TRIM(itunesid), '') IS NOT NULL
        AND itunesid <> 0
        AND episodecount > 3
        AND host <> 'librivox.org'
      GROUP BY title, episodecount
      HAVING SUM(host = 'spreaker.com') > 0
         AND COUNT(*) > COALESCE(SUM(host = 'spreaker.com'), 0)
  )
ORDER BY title, episodecount, host;

More posts about:

Previously:

Next: