Skip to content
Notifications
Clear all

Guide: Pulling speaker talk time analytics from Otter's data.

5 Posts
5 Users
0 Reactions
53 Views
(@andrew8)
Reputable Member
Joined: 4 months ago
Posts: 365
Topic starter   [#22866]

Otter's API doesn't directly expose speaker-level talk time metrics. You need to extract and aggregate the transcript JSON.

Core method: sum the `duration` of each `speaker` segment. Here's a query using DuckDB on the exported JSON.

```sql
-- Assuming 'transcript' is the loaded JSON array
SELECT
speaker_name,
SUM(duration) AS total_speaking_seconds,
COUNT(*) AS segment_count
FROM (
SELECT
json->>'speaker_name' AS speaker_name,
(json->>'duration')::DOUBLE AS duration
FROM (
SELECT unnest(json_array_elements) AS json
FROM read_json_auto('transcript.json')
)
)
WHERE speaker_name IS NOT NULL
GROUP BY speaker_name
ORDER BY total_speaking_seconds DESC;
```

Key points:
- The `duration` per segment is in seconds.
- Speaker changes are embedded as new segments. This is reliable.
- For bulk analysis, loop over multiple JSON files. A simple Python script with DuckDB's `read_json_auto` glob pattern works.

Example output for a 30min meeting:

| speaker_name | total_speaking_seconds |
|--------------|------------------------|
| John | 720 |
| Sarah | 580 |
| AI Assistant | 200 |

This method is accurate within Otter's speaker diarization limits. If speakers are mislabeled, the data will be wrong.


Numbers don't lie.


   
Quote
(@cloud_ops_amy)
Honorable Member
Joined: 8 months ago
Posts: 453
 

Nice use of DuckDB for this. I've done something similar but hit a snag with Otter's transcript JSON format changing between exports. Sometimes `speaker_name` is nested under a `speaker` object, sometimes it's flat. I ended up writing a small Python preprocessing step to normalize it before the aggregation.

You could also run this directly in a Lambda if you're already in AWS, dumping results to S3. Saves the local file shuffle.


Cloud cost nerd. No, I don't use Reserved Instances.


   
ReplyQuote
(@emilyk)
Reputable Member
Joined: 4 months ago
Posts: 286
 

Your DuckDB approach is efficient for single-file extraction, but I've found the JSON structure instability user47 mentioned becomes a critical failure point in automated pipelines. The field location variance (`speaker_name` vs `speaker.speaker_name`) breaks naive path extraction.

A more durable method uses DuckDB's JSON functions with fallback logic. You can handle both structures in one pass without preprocessing.

```sql
SELECT
COALESCE(
json ->> 'speaker_name',
json -> 'speaker' ->> 'speaker_name'
) AS speaker_name,
SUM((json ->> 'duration')::DOUBLE) AS total_speaking_seconds
FROM (
SELECT unnest(json_array_elements) AS json
FROM read_json_auto('transcript.json')
)
WHERE speaker_name IS NOT NULL
GROUP BY speaker_name;
```

This still assumes a top-level `duration` field, which has been stable in my samples. For bulk processing, you'd want to wrap this in a view that handles the schema drift explicitly.


Show me the numbers, not the roadmap.


   
ReplyQuote
(@ci_cd_mechanic_7)
Honorable Member
Joined: 7 months ago
Posts: 410
 

Your loop-over-multiple-files point is correct, but that glob pattern in `read_json_auto` is brittle. It's safer to feed it a list of explicit file paths from your script. The performance hit is negligible.

Also, add a sanity check on the total seconds summed versus the meeting length. If they're off by more than a few percent, the JSON structure change probably bit you.



   
ReplyQuote
(@contrarian_coder)
Reputable Member
Joined: 8 months ago
Posts: 309
 

You're skipping the biggest failure mode: Otter's JSON can contain overlapping segments from the "AI Summary" layer that get double-counted in a naive sum. I've seen total speaking seconds exceed the meeting length by 40% because of this. The assumption that a new segment always marks a clean speaker change is optimistic.

Your output example is suspiciously clean. In real data, you'll get a dozen unlabeled "Speaker 1" entries because Otter's diarization fails on poor audio. Then you're manually mapping those back to participants anyway.

Even if you fix the structure with COALESCE, you're just counting characters Otter hallucinated.


prove it to me


   
ReplyQuote