{"site":{"name":"Koji","description":"AI-native customer research platform that helps teams conduct, analyze, and synthesize customer interviews at scale.","url":"https://www.koji.so","contentTypes":["blog","documentation"],"lastUpdated":"2026-09-22T20:57:55.932Z"},"content":[{"type":"documentation","id":"eb22e26c-fd2d-446d-a2a0-bf1174e656f0","slug":"research-data-warehouse-bi","title":"Customer Research in Your Data Warehouse: Modelling Interview Data for Snowflake, BigQuery, and BI","url":"https://www.koji.so/docs/research-data-warehouse-bi","summary":"Interview data becomes far more useful once it sits in the warehouse beside revenue, product events, and support data, because warehouse dimensions supply segmentation the study never asked for and themes can be tied to later outcomes. Model it as four tables with explicit grain: research_studies (one row per study), research_participants (one row per completed interview, carrying quality_score and session metadata), research_answers (one row per participant per structured question, keyed on Koji stable question IDs so tracking waves join cleanly), and research_themes (one row per participant-theme pair, which is the grain teams most often get wrong). Load append-only with a loaded_at column so refreshed analysis does not destroy history. Extract via CSV export, JSON export into a VARIANT/JSON/JSONB staging table, or scheduled pulls through the API and MCP tools koji_get_study_data, koji_export_data, and koji_get_interviews. CSV and JSON export, the API and the MCP connector are all available on every account, including free. Join on a pass-through customer ID from personalised links rather than lead-form email where possible, and never resolve identity for studies promised as anonymous. Filter analytical views to quality_score >= 3, count with distinct interview_id to avoid multiple-choice and theme fan-out, keep transcripts out of the warehouse, and report theme frequency as unprompted mentions rather than population prevalence.","content":"# Customer Research in Your Data Warehouse: Modelling Interview Data for Snowflake, BigQuery, and BI\n\n**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.\n\n---\n\n## Why put qualitative data in the warehouse at all\n\nMost research tools treat export as an escape hatch. It is better understood as an integration point. Three questions justify the work:\n\n1. **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.\n2. **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.\n3. **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.\n\nThe 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.\n\n---\n\n## The four-table model\n\nGrain matters more than anything else here. Get it wrong and every dashboard double-counts.\n\n**`research_studies`** — one row per study.\n`study_id` (natural key), `title`, `research_goal`, `methodology`, `launched_at`, `closed_at`, `mode` (voice / text / mixed), `owner_team`.\n\n**`research_participants`** — one row per completed interview.\n`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.\n\n**`research_answers`** — one row per participant per structured question.\n`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.\n\n**`research_themes`** — one row per participant per theme.\n`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.\n\nTwo conventions save pain later:\n\n- **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.\n- **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.\n\n---\n\n## Getting the data out of Koji\n\nKoji 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.\n\n**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.\n\n**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.\n\n**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](/docs/mcp-overview) and the [research API guide](/docs/user-research-api-guide) for authentication and tool details, and [exporting research data](/docs/exporting-research-data) for the full field-by-field breakdown of each format.\n\n**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](/docs/webhook-setup) for both patterns in full.\n\n**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.\n\n---\n\n## Joining research to the rest of the warehouse\n\nThe join key is the hard part, and it is a governance question as much as a technical one.\n\n- **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.\n- **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.\n- **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.\n\nWhatever 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](/docs/research-data-retention-deletion) and [data residency and international transfers](/docs/research-data-residency-international-transfers). Deletion requests must reach the warehouse too, which is a strong argument for keeping raw transcripts out of it.\n\n---\n\n## Four dashboards worth building\n\n**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.\n\n**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.\n\n**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.\n\n**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.\n\nImplementation 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.\n\n---\n\n## Quality filtering: the step teams skip\n\nKoji 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.\n\nAdopt 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.\n\n---\n\n## Pitfalls\n\n- **Counting multiple-choice answers as participants.** One person selecting four options produces four rows. Always aggregate with a distinct count on `interview_id`.\n- **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.\n- **Loading transcripts into the warehouse by default.** Large, sensitive, rarely queried, and a retention liability. Store the pointer, not the payload.\n- **Overwriting on re-export.** Destroys the trend line the moment analysis is refreshed.\n- **Silent identity leakage.** An anonymous study joined to a customer table is a broken promise, however useful the result.\n\n---\n\n## Related Resources\n\n- [Exporting Research Data from Koji](/docs/exporting-research-data) — CSV, JSON, and transcript access field by field\n- [Structured Questions in AI Interviews](/docs/structured-questions-guide) — the six question types that make up your answers table\n- [Koji MCP Integration Overview](/docs/mcp-overview) — scheduled pulls via MCP tools\n- [User Research API](/docs/user-research-api-guide) — programmatic access and authentication\n- [Understanding the Koji Analytics Dashboard](/docs/understanding-analytics-dashboard) — what the platform already reports before you model anything\n- [Research Data Retention and Deletion](/docs/research-data-retention-deletion) — obligations that follow the data into the warehouse\n- [Customer Journey Analytics](/docs/customer-journey-analytics-qualitative-insights) — combining behavioural data with qualitative insight\n","category":"API Reference","lastModified":"2026-09-20T18:39:47.768695+00:00","metaTitle":"Research Data in Snowflake, BigQuery & BI: Schema Guide","metaDescription":"A four-table model for interview data in Snowflake, BigQuery, Redshift, or Postgres, the extraction pattern that works with Koji today, and the four dashboards to build in Looker, Tableau, Power BI, or Metabase.","keywords":["customer feedback data warehouse","qualitative data in bi tools","snowflake customer research","bigquery interview data","power bi customer feedback","research data pipeline","looker research dashboard","tableau qualitative data","research data modelling","export interview data warehouse"],"aiSummary":"Interview data becomes far more useful once it sits in the warehouse beside revenue, product events, and support data, because warehouse dimensions supply segmentation the study never asked for and themes can be tied to later outcomes. Model it as four tables with explicit grain: research_studies (one row per study), research_participants (one row per completed interview, carrying quality_score and session metadata), research_answers (one row per participant per structured question, keyed on Koji stable question IDs so tracking waves join cleanly), and research_themes (one row per participant-theme pair, which is the grain teams most often get wrong). Load append-only with a loaded_at column so refreshed analysis does not destroy history. Extract via CSV export, JSON export into a VARIANT/JSON/JSONB staging table, or scheduled pulls through the API and MCP tools koji_get_study_data, koji_export_data, and koji_get_interviews. CSV and JSON export, the API and the MCP connector are all available on every account, including free. Join on a pass-through customer ID from personalised links rather than lead-form email where possible, and never resolve identity for studies promised as anonymous. Filter analytical views to quality_score >= 3, count with distinct interview_id to avoid multiple-choice and theme fan-out, keep transcripts out of the warehouse, and report theme frequency as unprompted mentions rather than population prevalence.","aiPrerequisites":["A warehouse (Snowflake, BigQuery, Redshift, or Postgres) and a BI tool","Familiarity with basic dimensional modelling","At least one completed Koji study to load"],"aiLearningOutcomes":["Model interview data as four tables with correct grain","Choose between CSV, JSON, and scheduled API or MCP extraction","Join research data to warehouse dimensions without breaking anonymity promises","Build theme-trend, segment-distribution, theme-to-outcome, and evidence-lookup dashboards","Avoid fan-out double counting in Looker, Tableau, and Power BI","Apply quality-score filtering consistently in analytical views"],"aiDifficulty":"advanced","aiEstimatedTime":"12 min read"}],"pagination":{"total":1,"returned":1,"offset":0}}