building a forensic pipeline for my own digital humiliation
you can never really know how bad a conversation is going until you write a script to calculate your own double-text ratio
the backup gatekeep
i started by trying to pull my sms database using iMazing. the app does a full device backup to get access to the filesystem, so i sat there for an hour and a half watching a progress bar crawl across my screen. when it finally finished, iMazing hit me with a 30 dollar license paywall to actually export anything. classic
at that point i figured i'd do it the official way through apple devices. i configured a local unencrypted backup and waited an hour for it to finish. halfway through, i realized something deeply embarrassing
the appdata method
iMazing had already dumped the backup on my hard drive. the full sqlite database containing every text i've ever sent was just sitting in my appdata directory the entire time i was trying to find another way to get it:
C:\Users\sawyer\AppData\Roaming\iMazing\Caches\00008150-000824211A20401C\PartialBackup\00008150-000824211A20401C\3d\3d0d7e5fb2ce288813306e4d4636395e047a3d28that hex string at the end is the SHA-1 hash of HomeDomain-Library/SMS/sms.db. i found the exact path from this blog post that is somehow still accurate 14 years later
i cancelled the apple devices backup and opened the database
we're looking at about 2 hours of waiting around and not once did i ever consider this was a bad idea. i was actually in a call with my friends the entire time and i fully blame erin and jake for not stopping me
metadata laundering
the sql is trivial. join the message table to the handle table so you can actually see who you're talking to, and pull the timestamps:
query = """
SELECT m.is_from_me, m.date, m.text
FROM message m
JOIN handle h ON m.handle_id = h.ROWID
WHERE h.id = ?
ORDER BY m.date ASC
"""since i did not want to accidentally expose my desperate ass texts to the entire internet, the extraction script replaces every message body with a character count before it ever makes it to the csv. it's metadata laundering, the analytics work the same but nobody gets to see what i actually said at 2am on a saturday
is_from_me,date,char_count
0,2026-06-11 21:56:47,44
0,2026-06-11 21:57:12,88
1,2026-06-11 23:09:02,58the one genuinely annoying part was apple's timestamp format. ios doesn't store normal timestamps. it stores seconds (or sometimes nanoseconds??) since january 1, 2001, because apparently the unix epoch wasn't special enough. you have to detect which format you're dealing with and add 978307200 seconds to convert back to something usable:
if date_val > 1000000000000000: # nanoseconds
seconds_since_2001 = date_val / 1000000000
else: # seconds
seconds_since_2001 = date_val
unix_timestamp = seconds_since_2001 + 978307200the full extraction script is here
the analytics
the second script is where the actual damage happens. it reads the sanitized csv and computes every metric you see in the profiler below
the first thing it does is group individual messages into "turns." if the same person sends multiple messages within 4 minutes, those get collapsed into a single turn. this also tracks burst speed: how fast someone fires off back-to-back texts. four minutes felt like the right cutoff. i did not test other values. i did not need to:
for msg in messages:
if not curr_turn:
curr_turn.append(msg)
else:
gap = (msg['date'] - curr_turn[-1]['date']).total_seconds()
same_sender = msg['is_from_me'] == curr_turn[-1]['is_from_me']
if same_sender and gap <= 240:
if msg['is_from_me'] == 1:
stats['burst_gaps_me'].append(gap)
else:
stats['burst_gaps_her'].append(gap)
curr_turn.append(msg)
else:
turns.append(curr_turn)
curr_turn = [msg]once you have turns, everything works off the gaps between them. a gap over 20 minutes means the conversation is still going but the response was "cold" (not rapid-fire). a gap over 3 hours means the conversation died and whoever texts next is the conversation starter. if the same person sent the last turn and the next one across a 3-hour gap, that's a double text:
if gap_seconds >= 10800:
if prev_is_me:
stats['ghosted_by_her_3h'] += 1
else:
stats['ghosted_by_me_3h'] += 1
if curr_is_me == prev_is_me:
if curr_is_me:
stats['double_texts_me'] += 1
stats['double_text_attempts_me'] += 1double text success survival is whether the other person responded within 12 hours. the script scans forward through the remaining turns looking for a reply. there's a technical term in the DSM-5 for scanning your own message history to calculate whether double-texting worked. i don't know it. i just wrote the loop:
for j in range(i+1, len(turns)):
if turns[j][0]['is_from_me'] == 0:
if (turns[j][0]['date'] - curr[0]['date']).total_seconds() <= 43200:
stats['double_text_success_me'] += 1
break
if (turns[j][0]['date'] - curr[0]['date']).total_seconds() > 43200:
breaki also had to write custom logic for response times. the script pauses the timer during sleeping hours (1am–8am) so a message sent at midnight and answered at 9am logs as a 2-hour delay, not a 9-hour delay. yes, i though about this enough to build a sleep schedule into the analysis, but i didn't stop to think how insane it was:
def get_active_seconds(start_dt, end_dt):
current = start_dt
active_seconds = 0
while current < end_dt:
next_day = (current + timedelta(days=1)).replace(hour=0, minute=0, second=0)
sleep_start = current.replace(hour=1, minute=0, second=0)
sleep_end = current.replace(hour=8, minute=0, second=0)
step_end = min(end_dt, next_day)
# count hours before sleep (midnight–1am)
p1_start = max(current, current.replace(hour=0, minute=0, second=0))
p1_end = min(step_end, sleep_start)
if p1_start < p1_end:
active_seconds += (p1_end - p1_start).total_seconds()
# count hours after sleep (8am–midnight)
p2_start = max(current, sleep_end)
p2_end = step_end
if p2_start < p2_end:
active_seconds += (p2_end - p2_start).total_seconds()
current = step_end
return active_secondsthe full analytics script is here
for the record: none of this includes about a week of texting before our first date, since that happened over a different platform and never touched iMessage. also, if you're in a group chat with someone, you'll need to filter those messages out or they'll pollute your 1-on-1 stats
the system profiler
i dumped all of this in to a frontend to render these stats. i spent a couple hours styling the ui like the OS X 10 system profiler. tables, split panes, and a live activity monitor tracking the slow decline of my talking stage
| Field | Value |
|---|---|
| Data Source | SQLite Database Dump (Parsed JSON) |
| Total Conversations | 125 |
| Average Conversation Length | 4.2 messages |
| Conversations Past 5 Messages | 9% |
| Most Active Hour | 11:00 PM |
| Metric | Party A (Sawyer) | Party B |
|---|---|---|
| Total Messages Sent | 280 | 248 |
| Average Characters per Message | 39 | 26.5 |
| Time Between Back-to-Back Texts | 25s (Avg gap between texts in a burst) |
14s (Avg gap between texts in a burst) |
| Metric | Party A (Sawyer) | Party B |
|---|---|---|
| Average Cold Response Time | 1h 19m | 3h 23m |
| Response Time Consistency | ± 2h 6m (Standard Deviation) |
± 6h 52m (Standard Deviation) |
| Fast Reply Rate (<30m) | 42% (Chance you reply quickly) |
36% (Chance she replies quickly) |
| Longest Ghosting Duration | 18h 26m (Longest time Sawyer ghosted) |
2d 11h 56m (Longest time Party B ghosted) |
| Reply Time After Being Ghosted (>1h) | 38m (after 7h 11m wait) (Your reply time after she delays >1h) |
2h 30m (after 3h 33m wait) (Her reply time after you delay >1h) |
| Metric | Party A (Sawyer) | Party B |
|---|---|---|
| Double Texts (>3h gap) | 13 | 2 |
| Double Text Success Rate | 62% | 100% |
| Message Length After Ghosting | 54 chars | 41 chars |
| Metric | Party A (Sawyer) | Party B |
|---|---|---|
| Sent Last Message Before Convo Died | 30 | 12 |
| Conversation Starters (>3h gap) | 23 | 19 |
the raw numbers are one thing. watching them move week to week is another. here's where the volume actually drops off
post-mortem
the activity monitor tells the whole story in one chart. the week of june 15th was peak volume: 86 messages from me, 73 from her. by june 29th it was 29 and 17. only 9% of our conversations made it past 5 messages
the data doesn't lie, and that's the problem. i reply faster. i double text more. i send longer messages after being ghosted. i'm the last one to text in 71% of dead conversations. if this were any other system i'd call it a resource leak. one node dumping packets into a connection that stopped sending ACKs weeks ago
the worst part isn't even the numbers. it's that i had to build an entire local environment just to confirm what i already knew. sometimes you don't need a statistical analysis to know you're the one trying harder. but apparently i did1
update: the stats were enough. i brought it up and broke things off.