what are you not asking your own data?

my own analytics told me the post format i'd used all week was my worst performer.

twelve posts. every single one in that format.

the data had been sitting there the whole time. i just never asked it anything.

the setup: every post lands in a table

before any of this makes sense, here's the plumbing.

every x post i publish gets recorded as a row the moment it goes out. an agent tags it with its format, the day and slot it went out, my follower count at the time, and how many other times i posted that day. i learned to record the confounders too, because a post's reach is never just about the format.

then, on a schedule, a job pulls the live metrics off the x api and writes a snapshot of each post's numbers at a fixed age. not "impressions right now" but "impressions at 24 hours old," so i'm always comparing posts at the same stage of their life. a post from last week has had seven extra days to collect impressions. comparing its raw total to a fresh post is meaningless.

it's a plain sqlite file. three tables do the work:

-- what format was this post? (written when the post goes out)
CREATE TABLE assignment (
  content_id  TEXT NOT NULL,   -- the post
  arm_id      INTEGER NOT NULL, -- the format bucket
  posted_at   TEXT,
  day_of_week INTEGER,
  posts_that_day          INTEGER, -- confounder
  follower_count_at_post  INTEGER, -- confounder
  PRIMARY KEY (content_id)
);

-- the format buckets: "statement opener", "question closer", "listicle", ...
CREATE TABLE arm (id INTEGER PRIMARY KEY, label TEXT NOT NULL);

-- metric snapshots, captured at fixed ages by the sync job
CREATE TABLE observation (
  content_id TEXT NOT NULL,
  age_hours  REAL NOT NULL,     -- 24, 48, 72 ...
  numerator  REAL,              -- impressions
  PRIMARY KEY (content_id, age_hours)
);

the hardest part of this wasn't the schema. it was the tagging. my first version asked me to label each post's format by hand. that discipline rotted in about four days. so i moved the tagging onto the same scheduled job that syncs the metrics. the rule i keep relearning: a step that depends on me remembering is a step that doesn't happen.

the question i finally asked

one night i actually queried it. group posts by format, average the impressions at 24 hours, sort worst to best.

SELECT arm.label            AS format,
       COUNT(*)             AS posts,
       ROUND(AVG(o.numerator)) AS avg_impressions
FROM observation o
JOIN assignment a ON a.content_id = o.content_id
JOIN arm        ON arm.id = a.arm_id
WHERE o.age_hours = 24
GROUP BY arm.label
ORDER BY avg_impressions ASC;

the format i'd leaned on all week came dead last.

i almost posted the confession right there. "i wasted a week on my worst format." good hook, honest, a little self-deprecating, the kind of post that tends to do well. i had the draft open.

then i re-ran it with one change.

the part where i fooled myself

AVG(). i had compared averages. means.

the "typical post" bucket i was measuring against had four posts from april sitting in it that went semi-viral. four lucky outliers, quietly hauling the whole average up. i wasn't comparing my new format to a normal post. i was comparing it to a normal post plus a handful of once-a-quarter spikes.

the fix is one word, median, but sqlite has no MEDIAN(). that's the tell. the wrong query is a single built-in function. the right one makes you work for it:

WITH ranked AS (
  SELECT arm.label AS format,
         o.numerator AS impressions,
         ROW_NUMBER() OVER (PARTITION BY arm.label ORDER BY o.numerator) AS rn,
         COUNT(*)     OVER (PARTITION BY arm.label)                      AS n
  FROM observation o
  JOIN assignment a ON a.content_id = o.content_id
  JOIN arm        ON arm.id = a.arm_id
  WHERE o.age_hours = 24
)
SELECT format,
       ROUND(AVG(impressions)) AS median_impressions
FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)  -- middle row(s)
GROUP BY format
ORDER BY median_impressions DESC;

swap mean for median and the picture flips. the format i'd just called my worst performer actually beats my typical post. those twelve posts were fine. better than fine.

median, not mean. opposite conclusion.

the real lesson isn't median

it would be easy to stop at "use median." true, but small.

the bigger thing is that the tool had been there the entire time. i spent a week making a call by vibe, then nearly made a second call off a query i never sanity-checked. the data didn't fail me twice. i failed it twice. once by not asking, once by asking sloppily.

that's the pattern i keep hitting as a solo founder. i have more instrumentation than i use. analytics on the site, stripe on the revenue, every post i've ever written sitting in a table i can query in seconds. the constraint was never access. it was the discipline to ask, and the half-second of doubt to ask again.

what the data says this week

with the tagging automated and the median query wired into the scheduled job, the honest read as of now: statement-style openers edge out question-closers, and both beat listicles. posts with no link outperform posts with a link. the samples are still small, so i hold all of it loosely and let the job keep counting.

but the meta-question is the one worth passing on.

what are you not asking your own data?

you almost certainly have the answer to something that's been nagging you, sitting in an export you've never opened. go ask it. then check whether you asked it right.