Tracking My Podcast Listening Habit

August 5th, 2019 · 1 min read

My effort to back up my social media activities involves Momento, an iOS private journal app that I wrote about in my last post. As the number of my listened podcast episodes over the years crossed 500, I wished to import my listening history into Momento as well. It was a cumbersome and convoluted process involving a lot of tools since there is no official way to do that in Castro, my podcast player of choice.

By emailing Castro’s creator, I learned that the app includes a hidden feature to give access to the underlying SQLite database for technical support:

Open the in-app Settings screen
Navigate to Support and long tap on Email Support
Choose “Email with Database And Logs” and send the email to my address

I then ran the SQL query below to retrieve all played/starred episodes and exported the results as JSON:

	SELECT title, name, starred, lastPlayed, 'https://castro.fm/episode/' \|\| e.shortId url
	FROM supepisode e, suppodcast p
	WHERE e.podcastId = p.id AND (lastplayed > 0 OR starred = 1) AND e.shortId != ''
	ORDER BY lastPlayed DESC

view raw castro-history.sql hosted with ❤ by GitHub

The output looked like this:

	[
	{
	"title" : "Cortex Airport",
	"starred" : 1,
	"name" : "Cortex",
	"lastPlayed" : 1564865481.72277,
	"url" : "https:\/\/castro.fm\/episode\/MdiHr4"
	},
	{
	"title" : "for 2019\/07\/31 at 10:00 EDT",
	"starred" : 0,
	"name" : "CBC News: The World This Hour",
	"lastPlayed" : 1564585231.21373,
	"url" : "https:\/\/castro.fm\/episode\/HJOqlw"
	}
	]

view raw castro-history.json hosted with ❤ by GitHub

Notice that I linked each episode to its page on Castro’s server (see example). This came in handy as I later used Ruby to convert the JSON file to… Pocket’s exported bookmark format for use with Pinboard’s Import Bookmarks feature (crazy, I know):

	require 'json'

	puts <<HERE
	<!DOCTYPE html>
	<html>
	<!--So long and thanks for all the fish-->
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>Pocket Export</title>
	</head>
	<body>
	<h1>History</h1>
	<ul>
	HERE

	episodes = JSON.parse(File.read('castro-history.json'))
	episodes.each do \|episode\|
	puts <<HERE
	<li><a href="#{episode['url']}" time_added="#{episode['lastPlayed'].to_s.split('.')[0]}" tags="castro#{episode['starred'] == 1 ? ',castro-starred' : ''}">#{episode['name']} · #{episode['title']}</a></li>
	HERE
	end

	puts <<HERE
	</ul>
	</body>
	</html>
	HERE

view raw castro-to-pocket.rb hosted with ❤ by GitHub

I named the resulting file ril_export.html and fetched it to Pinboard:

	<!DOCTYPE html>
	<html>
	<!--So long and thanks for all the fish-->
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>Pocket Export</title>
	</head>
	<body>
	<h1>History</h1>
	<ul>
	<li><a href="https://castro.fm/episode/MdiHr4" time_added="1564865481" tags="castro,castro-starred">Cortex · Cortex Airport</a></li>
	<li><a href="https://castro.fm/episode/HJOqlw" time_added="1564585231" tags="castro">CBC News: The World This Hour · for 2019/07/31 at 10:00 EDT</a></li>
	</ul>
	</body>
	</html>

view raw ril_export.html hosted with ❤ by GitHub

Thanks to my archival account, Pinboard quickly imported the list and saved a permanent copy for each page.

Finally, I set up a RSS feed to the tag in Momento to show off my listening habit:

It was worth it.

Owning My Data

View Past Tweets on Google Maps