Memory Lane, by Trial and Error: Parsing 24,000 iMessages

What five people's group chat looks like once you stop scrolling it and start querying it.

Every so often I get the urge to point a model at something that isn't a spreadsheet of public data. This time it was my toronto group chat. We migrated it to imsg just recently from instagram starting in june so the numbers are a bit skewed, but it's still a decent sample size. It's sitting in a SQLite file on my own laptop the whole time.

~/Library/Messages/chat.db. No API, no rate limit, no auth flow. Just a database Apple has been quietly writing to since the day I set up iMessage. I opened it expecting a SELECT * FROM messages afternoon project. It was not that.

Bar chart, Message Count per Person: Me 3659, rz 1833, bt 1902, jin 2618, jess 194. — The imbalance you already knew was there, now with a denominator. Five people, 24,000 messages total across every thread in the export, and this one group's split alone spans a 19x range top to bottom.

The text isn't where you think it is

First trap: SELECT text FROM message returns NULL for a huge chunk of recent rows. The column isn't gone, it's just not where iMessage puts text anymore. Since a macOS update years back, message bodies live inside attributedBody, an NSKeyedArchiver-serialized blob, because a chat bubble apparently needed to remember its own font. Getting a plain string back out means unarchiving a binary plist and walking the object graph for the NSString payload. The text column still works for old messages and reactions; everything else needed the blob path.

Timestamps lie if you assume Unix epoch

Second trap, and the one that cost the most time: date in the messages table isn't Unix time. It's nanoseconds since January 1, 2001, Apple's Core Data epoch, and for a while my hourly activity numbers were shifted so hard that everyone appeared to text most heavily sometime around 2001. Once corrected, the pattern actually made sense.

Heatmap of message activity by hour of day for each of the five people, showing an evening peak. — Me at a clean peak around 6–7pm, jin right behind, jess barely registering outside a narrow afternoon band. Nobody in this chat is texting at 3am. We're boring that way.

A reaction is a message pretending to be a reply

Tapbacks (heart, thumbs up, double exclamation) aren't in a separate table either. They're rows in the same message table, linked to the original via associated_message_guid, prefixed with a type code that tells you whether it's a love, a laugh, or a removed reaction undoing an earlier one. Skip filtering these out and every count in this post, message totals, word counts, everything, quietly inflates.

Once the data's honest, the fun stats show up

With text decoded, timestamps fixed, and reactions separated from real messages, the rest was just counting things properly.

Bar chart, Conversation Starters, first message after a 4 hour or more gap: Me 29, rz 7, bt 8, jin 33, jess 5. — jin opens the chat back up more than anyone (33 times), followed closely by me (29). rz, bt, and jess mostly wait to be texted first.

Two bar charts side by side: Distinct Words Used per Person, and Vocabulary Richness as distinct over total words, for each of the five people. — I've sent the most messages and the most distinct words in absolute terms, but the lowest type-token ratio in the group (0.159), I say a lot, but I repeat myself doing it. jess, at the other end, has the smallest vocabulary in raw count but the highest richness (0.452): fewer messages, less repetition per message.

Bar chart, Name Drops per Person, split by mentions from others versus mentions by self. — bt is talked about more than anyone else in the chat (323 mentions) despite not being the most frequent texter. Self-mentions are near zero across the board, nobody in this group refers to themselves by name, which in hindsight is just how texting works.

Parsed directly from chat.db via sqlite3 in Python; text extracted from attributedBody where the plain text column was empty, timestamps converted from Apple's Core Data epoch, and reactions filtered out via associated_message_guid before counting.