Customer Research in Your Data Warehouse: Modelling Interview Data for Snowflake, BigQuery, and BI

The bottom line: Qualitative research becomes far more valuable the moment it can be joined to the rest of your data. Once interview themes, structured answers, and quality scores live in the warehouse next to revenue, product events, and support tickets, you can answer questions neither source answers alone — such as which reported friction predicts churn, or whether the segment that ranks a feature highest is the one that renews. This guide gives you a four-table model, an extraction and loading pattern that works with Koji today, and the dashboards worth building on top.

---

Why put qualitative data in the warehouse at all

Most research tools treat export as an escape hatch. It is better understood as an integration point. Three questions justify the work:

1. Attribution. "Customers who mentioned onboarding friction in Q2 — what did they do in Q3?" That requires the interview record and the behavioural record in the same place.
2. Segmentation you did not plan for. Warehouse dimensions you already maintain (plan tier, ARR band, region, lifecycle stage, acquisition channel) become free cuts on every study, without asking participants for information you already have.
3. Longevity. Studies end; the warehouse does not. A theme table spanning two years of research is an institutional asset. A folder of CSVs is not.

The counter-argument — that qualitative data resists tabular modelling — is half true. Transcripts do. Themes, structured answers, quality scores, and per-participant metadata do not, and those are the parts you want to join anyway. Keep the narrative in the research platform where it stays readable, and land the countable layer in the warehouse.

---

The four-table model

Grain matters more than anything else here. Get it wrong and every dashboard double-counts.

research_studies — one row per study. study_id (natural key), title, research_goal, methodology, launched_at, closed_at, mode (voice / text / mixed), owner_team.

research_participants — one row per completed interview. interview_id, study_id, participant_ref, source (organic, custom link, CSV import), started_at, completed_at, duration_seconds, message_count, mode, quality_score, plus any lead-form fields you collected. This is your fact table for counting people.

research_answers — one row per participant per structured question. interview_id, study_id, question_id, question_text, question_type, answer_numeric, answer_text, answer_option, option_rank. Because Koji questions carry stable IDs, question_id is a durable join key across waves of the same tracking study — the single most useful property of this table.

research_themes — one row per participant per theme. interview_id, study_id, theme, sentiment, evidence_quote. This is the table people model wrongly. A theme is not an attribute of the study; it is a many-to-many between participants and topics. One row per pair keeps the counting honest.

Two conventions save pain later:

• Append-only with a loaded_at column. Re-running an export after a report refresh should insert a new snapshot, not overwrite history. Analysis re-runs change theme labels, and you will want to know what the dashboard said in March.
• Store question_type alongside every answer. Downstream logic differs by type: scale answers average, single_choice answers count, ranking answers need mean position, multiple_choice answers must never be counted as if each participant contributed one row.

---

Getting the data out of Koji

Koji supports three extraction paths. CSV and JSON export are available on every account, including free: credits gate interviews, not data access. The API and MCP connector are available on every account too, including free. Outbound webhooks are not available yet, so event-driven loads are built on polling or the embed events.

1. CSV export (Interviews page). The flat participant view: metadata, session data, AI summary, quality score, theme tags, intake-form fields, and one column per structured question. This is the fastest route into research_participants and, after an unpivot, research_answers. Ideal for a manual or lightly scripted weekly load.

2. JSON export. The full structured analysis per participant, including nested data that does not flatten cleanly. Use it when you want research_themes with evidence quotes rather than a flattened tag string. Land the raw JSON in a staging table (VARIANT in Snowflake, JSON in BigQuery, JSONB in Postgres) and model downstream — the standard ELT pattern, and it means a change in the export shape does not break yesterday's load.

3. The API and MCP server. For scheduled pipelines, koji_get_study_data returns per-question aggregations (averages, distributions, option counts) and koji_export_data returns the brief, respondents, report summary, and paginated transcripts. koji_get_interviews includes the per-interview quality score. Run these on a schedule from your orchestrator and write the results to staging. See the MCP integration overview and the research API guide for authentication and tool details, and exporting research data for the full field-by-field breakdown of each format.

4. Near-real-time loads without webhooks. Koji has no outbound webhooks yet, so there is nothing to register an ingest URL with. Two patterns get close. A small worker can poll GET /api/v1/interviews/{id} for the ids it is waiting on and append each completed interview to staging. If the interview runs in the embed widget, the browser fires koji:interview_completed, which you can forward to your own ingest function as a fast path. See webhook setup for both patterns in full.

Which to choose: a polling worker or the embed event for freshness, a scheduled pull for backfills, reconciliation, and anything that must be idempotent. Most teams run both: a short poll for the stream, a weekly full pull to repair gaps. Interview data arrives at human speed, so a per-minute poll is plenty; the weekly reconciliation job is what actually protects you.

---

Joining research to the rest of the warehouse

The join key is the hard part, and it is a governance question as much as a technical one.

• Lead-form email is the most common key. It only exists where you collected it, so expect partial coverage and never assume the matched subset is representative.
• A pass-through parameter on a personalised interview link is cleaner: you already know who you invited, so carry your own customer ID and join on that. This is the recommended pattern for customer studies launched from a CRM or product event.
• Anonymous studies should stay unjoined. If you promised anonymity, resolving identity in the warehouse breaks that promise regardless of intent. Load those studies as aggregate-only, and enforce it with a minimum group size in the view rather than trusting analyst discipline.

Whatever you choose, keep the identity resolution in one modelled layer — a single research_identity_map — rather than scattering LOWER(TRIM(email)) joins through twenty dashboards. Your obligations under retention and residency rules apply to the warehouse copy exactly as they do to the source; see research data retention and deletion and data residency and international transfers. Deletion requests must reach the warehouse too, which is a strong argument for keeping raw transcripts out of it.

---

Four dashboards worth building

1. Theme trend. Participants mentioning each theme, by month, as a share of participants in that wave — never as a raw count, or a bigger study will masquerade as a rising problem. Add a small-sample flag under, say, 20 participants.

2. Structured-answer distributions by segment. Scale questions (NPS, CSAT, satisfaction, ease) cut by plan tier, ARR band, or lifecycle stage from your existing dimensions. This is where warehouse joins earn their keep: the research platform does not know a participant is a 40k-ARR enterprise renewal in ninety days, but your warehouse does.

3. Theme-to-outcome. Join research_themes to churn, expansion, or activation events with a forward-looking window. Treat the result as hypothesis generation, not causal inference — participants self-select into studies, and that selection is rarely orthogonal to the outcome.

4. Evidence lookup. A humble table: theme, quote, participant metadata, link back to the transcript. It is the most-used research dashboard in most companies, because it is what a PM opens the day before a roadmap review.

Implementation notes by tool: in Looker, model the four tables as separate explores joined on interview_id and set symmetric aggregates carefully — the participants-to-themes fan-out is exactly the case that inflates counts. In Tableau and Power BI, build a star with research_participants as the fact and studies as a dimension; put themes in their own bridge and use DISTINCTCOUNT / COUNTD on interview_id rather than row counts. In Metabase, a saved question per grain avoids the fan-out entirely.

---

Quality filtering: the step teams skip

Koji assigns a 1-to-5 quality score to every conversation. Low-effort or abandoned sessions are exactly the rows that distort a warehouse table, because nobody re-reads them the way they would in a report.

Adopt one rule and encode it in the model: filter to quality_score >= 3 in the analytical views, keep everything in the raw layer. That preserves auditability while making the default query honest. Note that Koji only consumes a credit for conversations scoring 3 or above, so the same threshold that governs your billing governs your analysis — a convenient alignment when someone asks why the dashboard count differs from the invite count.

---

Pitfalls

• Counting multiple-choice answers as participants. One person selecting four options produces four rows. Always aggregate with a distinct count on interview_id.
• Treating theme frequency as prevalence. Ten of thirty participants mentioning slow search means ten people raised it unprompted, not that 33% of your customer base experiences it. Label the axis accordingly, every time.
• Loading transcripts into the warehouse by default. Large, sensitive, rarely queried, and a retention liability. Store the pointer, not the payload.
• Overwriting on re-export. Destroys the trend line the moment analysis is refreshed.
• Silent identity leakage. An anonymous study joined to a customer table is a broken promise, however useful the result.

---

Related Resources

• Exporting Research Data from Koji — CSV, JSON, and transcript access field by field
• Structured Questions in AI Interviews — the six question types that make up your answers table
• Koji MCP Integration Overview — scheduled pulls via MCP tools
• User Research API — programmatic access and authentication
• Understanding the Koji Analytics Dashboard — what the platform already reports before you model anything
• Research Data Retention and Deletion — obligations that follow the data into the warehouse
• Customer Journey Analytics — combining behavioural data with qualitative insight