Export your starred episodes in Overcast
Overcast has been my primary podcast app for many years now. This years-long history of listening has produced a lot of data, most of which is lost to time.
Some of it isn’t though. What I have favorited (or starred, or recommended) is not lost to time. With the release of playlists in Overcast, I could see all my starred episodes. The data was somewhere! I emailed Marco, its creator and developer, to ask if he could offer some kind of export mechanism for this from the production app. I also signed up for the TestFlight and submitted a feature request through the beta app, but I didn’t hear back.
I ruminated on this topic for a few months until a few nights ago. I wanted to look up a starred episode, but got frustrated scrolling through the UI. I realized that it would be more efficient to scroll on my Mac, so I opened Overcast on the Mac. The idea struck me instantly: Marco has mentioned before that he uses sqlite. Surely I could find the database file on my Mac?
After some hunting, I found it: oc.db in
~/Library/Containers/[App UUID]/Data/Documents. I held my breath and opened it
with sqlite3. It worked! Now, how do I find the starred data? Turns out that
Marco has indicated this with a field called
OCFeedItem.userRecommendedTime. When a user stars an episode, the time is taken and that’s
written to their database and to the servers and used for Overcast’s recommendation engine.
I wrote some SQL to pull out the episodes and piped it through
Simon Willison’s excellent sqlite-utils:
$ sqlite-utils ~/Library/Containers/.../Data/Documents/oc.db "$(<~/Downloads/overcast_export_recommended_episodes.sql)"
I finally had the data I was looking for. A full list of every episode
that I have ever starred as beautiful JSON, going back to 2017.
I decided to publish my favorites since the reason to star them is to remember them and to share them with the world. You can view the code if you want to emulate this. If you use my script, consider joining me in subscribing to Overcast Premium so that Marco can continue to offer Overcast on the Mac.
I’m extraordinarily excited to finally have access to my data. With the recent rise of federated social networks and the talk of owning one’s data (or at least a copy!), I am put at ease that if Overcast went out of business tomorrow, I would still have a historical record of my favorite podcast episodes to share with friends and strangers alike.
My thanks to Marco for allowing Overcast to run on the Mac so that I could grab a copy of my data, and for guiding me through generation of the Overcast.fm permalinks