We’ve been asked many times in the past for episode transcripts for the Drill to Detail Podcast, and I’m therefore pleased to launch our new Drill to Detail Podcast website where you can not-only listen to all our past episodes but also view transcripts along with analysis and key quotes from the episode’s special guest.

Until recently doing episode transcription manually or through one of the first-generation online services was a long and tedious process but with services such as OpenAI’s Whisper API service now available that use generative AI technology, we created a BigQuery Colab Enterprise notebook that not only transcribed all of our past episodes but also generated episode summaries, lists of key points and speaker quotes that we wrote back to a BigQuery table and used as the data source for this new web app.

Leveraging BigQuery Colab Notebooks and the OpenAI Whisper API

If you’re interested in how we pulled these transcripts and episode insights together, the full python code can be found in this BigQuery notebook and, skipping-over the initial imports and setup of the BigQuery and OpenAI API clients we started first by defining functions that take a given podcast episode URL and download the episode MP3 file, then split that file into chunks to fit within the Whisper API file size limit.

The next function performs the actual transcription, passing the mp3 file chunks to the Whisper API and returning the speech transcription.

And now the main body of code for the notebook sets-up modules to parse the RSS feed that we pass to the notebook in order to extract episode titles, links, descriptions and other metadata, process each episode in the feed and assemble the full episode transcription from all each episode’s chunk transcriptions.

After each episode is transcribed, we take that transcription and then pass it back to the OpenAI LLM to classify the episode using one primary, and multiple secondary, tag values.

We then create another LLM prompt that, with the guest name added as function parameter too, summarises the episode contents, extracts three key insights or trends from the episode together with three notable quotes from the episode guest.

As the raw Whisper API transcription output doesn’t distinguish between speakers, this function splits the transcription up into individual sentences and asks the LLM to try and assign those sentences to myself and the episode guest based on who logically should be saying what at various points.

The transcription and all of the additional insights we’ve derived are written to a Google BigQuery table and dataset.

Viewing the resulting BigQuery table we can see the episode transcripts along with all the other derived data and episode metadata.

And finally the BigQuery dataset then becomes the data source for the https://podcasts.rittmananalytics.com web application, with the screenshot below showing an example episode transcript.

INTERESTED? FIND OUT MORE!

Rittman Analytics is a boutique data analytics consultancy that helps ambitious, digital-native businesses scale-up their approach to data, analytics and generative AI.

We’re authorised delivery partners for Google Cloud along with Oracle, Segment, Cube, Dagster, Preset, dbt Labs and Fivetran and are experts at helping you design an analytics solution that’s right for your organisation’s needs, use-cases and budget and working with you and your data team to successfully implement it.

If you’re looking for some help and assistance with your AI initiative or would just like to talk shop and share ideas and thoughts on what’s going on in your organisation and the wider data analytics world, contact us now to organise a 100%-free, no-obligation call — we’d love to hear from you!