ChatGPT cleaned up my podcast subscription mess

My podcast player of choice has been Castro. It’s designed for efficiently sorting through a large number of subscriptions. I explained how I use it in a previous post.

After years, the ease in which I can add podcasts lead me to amass a list of 432 subscriptions. What was once a relaxing daily activity of adding to my listening queue became a slog. Some days, there were 100 or more episodes waiting for me to triage. I also noticed Castro slowing to a halt and sometimes taking seconds to respond.

I decided it was time to do a reset and cull my podcast subscription list. Unfortunately, while Castro makes it easy to subscribe to many podcasts, it doesn’t make it easy to reduce that list down. The only way I found was to manually unsubscribe one-by-one, which takes seconds per podcast. I knew I needed a computer-aided method.

I would normally not think it worth coding up a tool for this. However, with ChatGPT at my disposal, what would have taken half a day took under an hour. Keep reading for the exact steps I took.

Extract Castro’s database

To get started, I extracted Castro’s app database (something I learned from this blog post).

First, I opened up Castro’s Settings. Then, I tapped Support.

Then, I long-tapped “Email Support”.

The long-tap revealed a hidden option to “Email with Database and Logs.”

By changing the recipient address to my own and using Mail Drop (the sqlite database file is quite large), I was able to access the complete state of my Castro app.

Get list

Next, I wanted to look at the list to determine criteria by which I can filter the list. I downloaded DB Browser for SQLite, which makes it easy to manipulate SQLite databases.

Specifically, I wanted a table with podcast names, total number of episodes, episodes listened, and ratio of episodes listened to total number of episodes. Given that the data is normalized, I had to write a SQL query to join a few tables. For this task, I turned to ChatGPT, which returned the following query.

SELECT
  SUPPodcast.name,
  COUNT(SUPEpisode.id) AS total_episodes,
  COUNT(
    CASE WHEN SUPEpisode.lastPlayed <> 0 THEN 1 END
  ) AS episodes_listened,
  CAST(
    COUNT(
      CASE WHEN SUPEpisode.lastPlayed <> 0 THEN 1 END
    ) AS FLOAT
  ) / COUNT(SUPEpisode.id) AS listen_ratio
FROM
  SUPPodcast
  LEFT JOIN SUPEpisode ON SUPPodcast.id = SUPEpisode.podcastId
GROUP BY
  SUPPodcast.id,
  SUPPodcast.name

The query outputted exactly the list I was looking for, which I then exported as a .csv file.

Trim the list

To trim the list down, I started by bringing the .csv file into my favorite app for manipulating tabular data, Google Sheets.

I immediately noticed many had zero episodes_listened . Some were duplicates that may have been created in error. Others were ones that I had subscribe to, but never listened to. I therefore filtered out the zeros, which brought the list down from 432 to 71.

I then calculated the median of episodes_listened and the median of the listen_ratio. The aim was to highlight podcasts based on sheer number of episodes listened to and completion rate. I then manually deleted any podcasts I no longer wanted to listen to. The end list had 52 podcasts, just around 12% of the original amount.

Trim the OPML file

Next, I wanted to change Castro to reflect my list. For this step, I turned to Castro’s support for OPML, a structured format for representing subscriptions.

To download the OPML file, I went to Castro’s settings and tapped “User Data.”

I then tapped “Export Subscriptions” and AirDropped the OPML file to my Mac.

At this point, the challenge was to remove all but the podcasts I had chosen earlier. I turned to ChatGPT again. With some back and forth and manual edits, I created a simple tool that does the trick. I’ve hosted it at opml-trimmer.arun.is (code on GitHub).

The tool takes in a list of titles separated by new lines (which I copied from my spreadsheet), and an OPML file. It then trims down the list and offers a new file for download. So, I had a new OPML file in hand.

Clean up subscriptions

The first step to changing Castro’s subscriptions to reflect my new list was to reset Castro. The app does support automatic backups, so I knew I could go back if anything went awry. So, under User Data, I hit Reset Castro to bring it start fresh.

I then AirDropped my trimmed OMPL file to my iPhone and saved it to Files.

From the Files app, I hit the share icon to open the share sheet and scrolled all the way to the right to the More button.

From the list, I selected Castro, which automatically opened up Castro to the import dialog. I then sit back and relaxed as Castro (slowly), imported all of my subscriptions.

Now, Castro is running quickly again and I no longer dread my episode triage process!

---

Thanks to Q for reading drafts of this.