Updated 2026-08-02

Analyzing a Cerebro export in Excel: how far a spreadsheet actually gets you

Excel can take you through cleaning, provenance triage and a first demand-vs-Title-Density flag — a genuinely useful first pass. It cannot collapse phrasings into demand families, it cannot aggregate metrics per family with per-column rules, and it cannot look at page 1. Here is the honest workflow, wall by wall.

Step 1 — Clean the data before any formula

Real Cerebro exports break naive spreadsheets in three ways (the full trap list):

1. Floors: >10,000 in Competing Products means at least 10,000. Split into a numeric column plus an is_floor flag; never strip the > silently.
2. Text numbers: 1,482 with a comma imports as text in many locales — convert explicitly, then check SUM actually moved.
3. Unknown ≠ zero: - and N/A mean "no data". Leave them blank; a formula that treats them as 0 corrupts every average and can rank an unknown above a measured value.

Step 2 — Provenance triage in two formulas

Add a bucket column: =IF(SmartComplete=1,"SC",IF(AmazonRecommended=1,"AR",IF(Organic>0,"ORG_ONLY","NONE"))) then pivot on it. On a real 11,547-keyword corpus this splits 60% SC / 6% AR / 34% organic-only — and that last third deserves suspicion, not equal reading time. (Why the provenance prior works.)

Step 3 — Flag demand-vs-titling outliers

Filter: Search Volume above a floor you choose (start with your corpus median), Title Density ≤ 2, bucket ≠ ORG_ONLY. Sort by volume. This shortlist — usually a few dozen rows — is the only part of the export worth reading one by one. Keep the low-TD trap in mind for every row.

Wall 1 — The collapse

"car phone holder", "phone holder for car", "car phone holders": three rows, one demand. A real corpus holds that demand in 29 phrasings. Grouping them needs normalization, word-order insensitivity, plural rules and size-folding — a deterministic rules engine. TEXTJOIN and SORT heroics will not stay correct or maintainable past the first edge case, and every keyword-level metric you computed above stays biased against fragmented demands until this is solved. (What the collapse involves.)

Wall 2 — Per-family aggregation rules

Once families exist, each column needs its own aggregation: volume sums across phrasings, Title Density takes the max (one titled variant kills virginity), cost-of-entry takes the min. A pivot table gives you one aggregation for everything — which quietly averages away the single decisive fact in each column.

Wall 3 — The page

No spreadsheet, ours included, can tell a gap from an irrelevant phrasing. The verdict lives on page 1: what products actually rank, what segments they form, whether the demand you measured is served under other words. The export is the map; the page is the territory. The full sequence, walls included: You ran Cerebro. Now what?