The whole warehouse in one connected view: dimensions on the left, per-source facts in the centre, the computed layer on the right — with the actual join lines drawn between them. Hover a table to trace its connections and see where its data comes from and which questions it feeds; click it for the full column detail.
The big revisions since the first design — each one driven by the team's feedback or by what the real exports showed.
Wastopics were the unit everything hung off.
Nowthe grain is the prompt, decorated with its topic and tags — exactly as requested. The real Profound export confirmed it: one row per prompt × engine × day.
Wasa "content inventory" table mapped every URL to topics.
Nowremoved entirely — query fan-out means one URL feeds many prompts, so the mapping was never clean, and no report needed it. Its jobs moved to better homes (change tracking → the interventions log; intent → prompt tags).
Wasthe plan was to store Profound's published score.
Nowthe raw export has no score column, so we compute visibility, share of voice, and position using Profound's own rule — and validated it against their dashboard: 57.2% vs 55.6%, 12.3% vs 11.9%, 2.15 vs 2. Same math then runs for every competitor.
Wasone generic Bing table.
Noworganic via API, and the AI layer as weekly exports — with the Pages file elevated to the system's leading indicator: it's where a new URL first shows up as cited, ahead of Profound. The dateless files load as proper dated snapshots.
Wasone change-date field per page.
Nowevery launch and update is its own logged event, so the before/after impact question works per intervention — even when a page is touched repeatedly.
Wasone sessions table carrying everything.
Nowsessions and goal conversions are separate tables built from the same export, sharing page and channel — so conversion rates divide cleanly and nothing double-counts.
Addedprompt versioning (rewording a prompt no longer resets its history), brand-spelling unification (the engines write "Spear", "Spears", and "Speare" — all one brand now), and URL cleaning (~4,000 citation links carried tracking junk that would have split pages apart).
Addedper-client upload folders, file fingerprinting, and batch tracing on every row — a misplaced file gets quarantined instead of landing in another client's dashboard. Build order now follows the team's own matrix tiers.
client_id and a date (or snapshot) sit on every fact row — those joins to dim_client / dim_date are universal, so they aren't drawn as lines; every other join is. Hover to isolate a table's connections; click for full columns.
✕ Removed from earlier designs: content_inventory (page↔topic bridge) — prompts are the grain and one URL feeds many prompts via query fan-out, so the mapping was never clean and no report needed the join.
Colour of the top edge of each table = its layer/status; line colour = the kind of connection.
Every question from the requirements doc, the tables that answer it, and how the answer is produced. Click a table name to jump to it in the diagram.
| Question | Answered by | How it's answered |
|---|
The model is ready to build. These five inputs lock the remaining pieces — each is a request, not a redesign.
One sentiment export for the same 7-day window as the raw export. This completes the "favorable" half of the citation analysis (Q11) — the raw export proves sentiment runs happen but doesn't carry the values.
One sample each of Position Tracking, Advertising Research, and Keyword Gap for a live client. Confirms the two competitor tables (Q15/Q16) and feeds the "what to create" demand signal (Q18).
Confirm the BigQuery exports are switched on for every property (history only accumulates from that day — flip them now), and each client's goal-event list (e.g., Spear = free-trial signup).
The agreed source for "this page went live / was updated on this date" — it powers the interventions log that the entire before/after impact story (Q1) anchors on.
Competitor list, owned domains, branded terms, and prompt priorities / must-wins per client. Small lists, big payoff: they sharpen Q4, Q5, Q9, Q10, and Q11 from "working" to "exactly right."