Building a Conversational Analytics System: Natural Language Queries, Dashboard Generation, and Data Storytelling with LLMs

Most analytics products have a graveyard of abandoned features. Custom report builders that nobody learned. Dashboard editors with 47 configuration options. Filter panels that require a PhD in your own data model to use effectively.

The pattern is consistent: data is there, the product is there, but the interface between user intent and query result is too high-friction. Users who would benefit from the data stop trying.

Conversational analytics closes that gap. Users ask a question in plain English. The system figures out what they mean, queries the right data, picks the right visualization, and explains what it found. Done well, it turns a non-technical SaaS founder into someone who can explore their own metrics without filing a support ticket every time.

Done badly, it confidently returns the wrong number. Or runs a query that scans your entire events table at 3 AM. This article covers how to do it well.

The Architecture

A conversational analytics pipeline has five layers. They do not run serially on every request, but you need all of them:

User question
    → Intent classification
    → Schema-aware SQL generation
    → Safe SQL execution
    → Visualization selection
    → Narrative summarization

Each layer can fail independently. A good intent classifier still sends bad inputs to SQL generation. A clean SQL result still gets paired with the wrong chart type. The layers need to be independently testable and individually observable.

Intent Classification

Before generating SQL, classify the question. This is often skipped in quick implementations, and it causes two problems: the system tries to run SQL for questions that should not produce SQL, and it treats every question as equivalent when some require clarification first.

type IntentType =
  | "metric_lookup"    // "What was our MRR last month?"
  | "trend_analysis"   // "How has churn changed over the last quarter?"
  | "comparison"       // "Which plan tier has the lowest retention?"
  | "segmentation"     // "Break down signups by country"
  | "anomaly_check"    // "Why did traffic spike on Tuesday?"
  | "definition"       // "What does 'active user' mean in our system?"
  | "out_of_scope";    // "Write me a Python script"

interface ClassifiedIntent {
  type: IntentType;
  confidence: number;
  extractedEntities: {
    metrics?: string[];
    dimensions?: string[];
    timeRange?: string;
    filters?: string[];
  };
  clarificationNeeded: boolean;
  clarificationPrompt?: string;
}

async function classifyIntent(
  question: string,
  recentContext: ConversationTurn[]
): Promise<ClassifiedIntent> {
  const response = await openai.chat.completions.create({
    model: "gpt-4o",
    temperature: 0,
    messages: [
      {
        role: "system",
        content: INTENT_CLASSIFIER_PROMPT,
      },
      {
        role: "user",
        content: buildClassificationInput(question, recentContext),
      },
    ],
    response_format: { type: "json_object" },
  });

  return JSON.parse(response.choices[0].message.content!) as ClassifiedIntent;
}

The INTENT_CLASSIFIER_PROMPT should include a few-shot examples per intent type. Do not rely on zero-shot classification for a production system; the boundary between metric_lookup and trend_analysis is ambiguous without examples to anchor it.

When clarificationNeeded is true, return the clarification prompt immediately rather than attempting SQL generation. “Which signup do you mean: free trial starts or completed onboarding?” is far better than silently picking one.

Handle out_of_scope at this layer. You want to return a clear message rather than sending nonsense to your SQL generator.

Schema-Aware SQL Generation

SQL generation is where most tutorials stop, and where most production systems break. The gap between a working demo and a reliable pipeline comes down to schema context quality.

The LLM does not know your schema. It needs to be told: which tables exist, what the columns mean in your business context, which relationships to use, and what values are valid for categorical columns.

interface SchemaContext {
  tables: TableDefinition[];
  relationships: JoinHint[];
  businessGlossary: Record<string, string>;
  knownValues: Record<string, string[]>;
}

interface TableDefinition {
  name: string;
  description: string;
  columns: ColumnDefinition[];
  rowCountEstimate?: number;
  commonQueryPatterns?: string[];
}

interface ColumnDefinition {
  name: string;
  type: string;
  description: string;
  nullable: boolean;
  example?: string;
  isHighCardinality?: boolean;
}

function buildSchemaContext(
  question: ClassifiedIntent,
  fullSchema: SchemaContext
): SchemaContext {
  // Prune to relevant tables only
  const relevantTables = selectRelevantTables(
    question.extractedEntities,
    fullSchema.tables
  );

  // Resolve glossary terms in the question
  const resolvedGlossary = resolveGlossaryTerms(
    question.extractedEntities.metrics ?? [],
    fullSchema.businessGlossary
  );

  return {
    tables: relevantTables,
    relationships: fullSchema.relationships.filter(r =>
      relevantTables.some(t => t.name === r.fromTable || t.name === r.toTable)
    ),
    businessGlossary: resolvedGlossary,
    knownValues: filterKnownValues(relevantTables, fullSchema.knownValues),
  };
}

The businessGlossary is critical and consistently underbuilt. Your analytics system likely has domain-specific meanings: “active user” means logged in within 30 days and has completed at least one key action. “MRR” means sum of current subscriptions excluding trials. “Churn” means cancellation within the subscription period, not non-renewal. None of this is derivable from column names. You have to inject it.

For the SQL generation prompt itself, structure it as:

function buildSQLPrompt(
  question: string,
  schema: SchemaContext,
  dialect: "postgresql" | "bigquery" | "snowflake"
): string {
  return `You are a SQL query generator for a ${dialect} database.

## Business Context
${formatGlossary(schema.businessGlossary)}

## Available Tables
${schema.tables.map(formatTableDefinition).join("\n\n")}

## Join Patterns
${schema.relationships.map(formatJoinHint).join("\n")}

## Rules
- Return read-only SELECT queries only. No INSERT, UPDATE, DELETE, DROP, or TRUNCATE.
- Always include a LIMIT clause. Maximum 10000 rows.
- Use parameterized date ranges using :start_date and :end_date placeholders.
- Do not use subqueries where a CTE improves readability.
- Flag any assumptions made about ambiguous terms in a comment at the top of the query.

## Question
${question}

Return JSON with fields: { sql: string, assumptions: string[], missingContext: string[] }`;
}

The missingContext field matters. When the model cannot confidently answer with the available schema, it should tell you. A question like “what percentage of users upgraded?” is ambiguous if you have multiple upgrade paths. Surface that ambiguity rather than silently picking one.

Safe SQL Execution

SQL execution is where you enforce non-negotiable constraints regardless of what the model returns. Three invariants that cannot be optional:

Read-only enforcement. Parse the generated SQL and reject anything that is not a SELECT statement. Do not rely on the model following instructions.

import { Parser } from "node-sql-parser";

const parser = new Parser();

function validateReadOnly(sql: string): void {
  const ast = parser.astify(sql);
  const statements = Array.isArray(ast) ? ast : [ast];

  for (const stmt of statements) {
    if (stmt.type !== "select") {
      throw new Error(
        `Rejected: non-SELECT statement detected (${stmt.type})`
      );
    }
  }
}

Query timeout. Set a hard timeout at the database connection level, not in application code. Application timeouts can fail to cancel the in-flight query, which keeps burning resources.

async function executeWithTimeout(
  sql: string,
  params: Record<string, unknown>,
  timeoutMs: number = 10_000
): Promise<QueryResult> {
  const client = await pool.connect();
  try {
    await client.query(`SET statement_timeout = ${timeoutMs}`);
    const result = await client.query(sql, Object.values(params));
    return result;
  } finally {
    client.release();
  }
}

Row limits. Even SELECT queries can return 5 million rows if the model forgets LIMIT. Enforce a maximum at the execution layer.

function enforceRowLimit(sql: string, maxRows: number = 10_000): string {
  const ast = parser.astify(sql) as any;

  if (!ast.limit) {
    return `SELECT * FROM (${sql}) _limited LIMIT ${maxRows}`;
  }

  const requestedLimit = ast.limit.value[0]?.value;
  if (requestedLimit > maxRows) {
    return sql.replace(
      /LIMIT\s+\d+/i,
      `LIMIT ${maxRows}`
    );
  }

  return sql;
}

Run all three checks before executing anything. Log every query with its classification, the question that generated it, and execution time. You need this when a user reports “the number is wrong.”

Visualization Selection

Once you have results, you need to decide what to show. This is a classification problem over the result shape and the original intent.

type ChartType =
  | "bar"
  | "line"
  | "pie"
  | "table"
  | "single_metric"
  | "heatmap"
  | "scatter";

interface VisualizationRecommendation {
  primary: ChartType;
  config: Record<string, unknown>;
  rationale: string;
  alternativeTypes: ChartType[];
}

function selectVisualization(
  intent: ClassifiedIntent,
  result: QueryResult
): VisualizationRecommendation {
  const { columns, rows } = result;
  const hasTimeColumn = columns.some(c => isTemporalType(c.type));
  const numericColumnCount = columns.filter(c => isNumericType(c.type)).length;
  const categoricalColumnCount = columns.filter(
    c => !isNumericType(c.type) && !isTemporalType(c.type)
  ).length;

  // Single numeric value: show as a metric card
  if (columns.length === 1 && numericColumnCount === 1 && rows.length === 1) {
    return {
      primary: "single_metric",
      config: { value: rows[0][columns[0].name], label: columns[0].name },
      rationale: "Single aggregate value",
      alternativeTypes: [],
    };
  }

  // Time series: line chart
  if (
    hasTimeColumn &&
    numericColumnCount >= 1 &&
    intent.type === "trend_analysis"
  ) {
    return {
      primary: "line",
      config: {
        xAxis: columns.find(c => isTemporalType(c.type))!.name,
        yAxis: columns.filter(c => isNumericType(c.type)).map(c => c.name),
      },
      rationale: "Time dimension present with trend analysis intent",
      alternativeTypes: ["bar"],
    };
  }

  // Category breakdown: bar chart
  if (categoricalColumnCount === 1 && numericColumnCount === 1) {
    const rowCount = rows.length;
    return {
      primary: rowCount > 8 ? "table" : "bar",
      config: {
        xAxis: columns.find(c => !isNumericType(c.type))!.name,
        yAxis: columns.find(c => isNumericType(c.type))!.name,
      },
      rationale: rowCount > 8
        ? "Too many categories for a bar chart; table is more readable"
        : "Single categorical dimension with numeric metric",
      alternativeTypes: rowCount <= 5 ? ["pie"] : ["table"],
    };
  }

  // Default: table
  return {
    primary: "table",
    config: { columns: columns.map(c => c.name) },
    rationale: "Multi-dimensional result or ambiguous shape; table is safest",
    alternativeTypes: [],
  };
}

The key insight: visualization selection based solely on chart type heuristics without considering row counts produces bad output. A pie chart with 14 slices is worse than a table. A line chart with 3 data points looks wrong. The rationale field feeds into the narrative layer, which explains to the user why they are seeing what they are seeing.

Narrative Summarization

The chart answers “what.” The narrative answers “so what.” This is where conversational analytics earns its name.

async function generateNarrative(
  question: string,
  sql: string,
  result: QueryResult,
  visualization: VisualizationRecommendation,
  companyContext: string
): Promise<string> {
  const resultSummary = summarizeResult(result);

  const response = await openai.chat.completions.create({
    model: "gpt-4o-mini",
    temperature: 0.3,
    messages: [
      {
        role: "system",
        content: `You generate concise data narratives for analytics dashboards.
Rules:
- Lead with the direct answer to the question. Do not hedge.
- Include the most notable number in the first sentence.
- Note any significant trend, outlier, or comparison if visible in the data.
- Flag uncertainty if the data might not fully answer the question.
- Keep it under 4 sentences. No filler.
- Do not suggest actions. Describe what the data shows, nothing more.
Company context: ${companyContext}`,
      },
      {
        role: "user",
        content: `Question: ${question}

Data summary: ${resultSummary}

Visualization: ${visualization.primary} chart showing ${visualization.rationale}`,
      },
    ],
  });

  return response.choices[0].message.content!;
}

function summarizeResult(result: QueryResult): string {
  const { columns, rows } = result;
  if (rows.length === 0) return "No data returned.";
  if (rows.length === 1) return JSON.stringify(rows[0]);

  // For larger results, summarize key statistics rather than dumping all rows
  const numericColumns = columns.filter(c => isNumericType(c.type));
  const stats = numericColumns.map(col => {
    const values = rows.map(r => Number(r[col.name])).filter(v => !isNaN(v));
    return {
      column: col.name,
      min: Math.min(...values),
      max: Math.max(...values),
      avg: values.reduce((a, b) => a + b, 0) / values.length,
      count: values.length,
    };
  });

  return JSON.stringify({ rowCount: rows.length, stats, sampleRow: rows[0] });
}

Use a smaller, cheaper model for narrative generation. GPT-4o-mini is enough. Save the more capable model for SQL generation where precision matters more. The narrative prompt explicitly forbids action suggestions because they are usually wrong and annoying, and because your product should not be giving business advice.

Caching and Cost Management

Conversational analytics has a cost structure that catches teams off guard. Every question fires an LLM call for classification, one for SQL generation, and one for the narrative. At scale with many concurrent users, that adds up fast.

Three caching strategies that stack well:

Semantic question cache. If two users ask “what was MRR last month?” and “show me MRR for the previous month,” they should hit the same result. Cache on a normalized intent fingerprint, not the raw question text.

async function getOrGenerateResponse(
  question: string,
  userId: string,
  tenantId: string
): Promise<AnalyticsResponse> {
  const intent = await classifyIntent(question, []);

  // Build a cache key from the normalized intent, not the raw question
  const cacheKey = buildIntentCacheKey(intent, tenantId);
  const cached = await cache.get(cacheKey);

  if (cached && !isCacheStale(cached, intent)) {
    return { ...cached, fromCache: true };
  }

  const response = await executeFullPipeline(question, intent, tenantId);
  await cache.set(cacheKey, response, getCacheTTL(intent));
  return response;
}

function getCacheTTL(intent: ClassifiedIntent): number {
  // Real-time metrics expire quickly; historical queries can live longer
  const timeRange = intent.extractedEntities.timeRange;
  if (timeRange === "today" || timeRange === "last_hour") {
    return 60 * 5; // 5 minutes
  }
  if (timeRange === "last_7_days" || timeRange === "last_30_days") {
    return 60 * 60; // 1 hour
  }
  return 60 * 60 * 24; // 24 hours for historical ranges
}

SQL result cache. Cache the query result separately from the narrative. If the same SQL runs again with the same parameters, skip the database round-trip. Store these with shorter TTLs than the full response cache.

Model routing. Use a fast, cheap model for intent classification. Use GPT-4o or an equivalent for SQL generation. Use GPT-4o-mini for narrative generation. The total cost per request drops significantly once you stop using the most capable model for every step.

Function Calling vs. Agent Loops

There are two structural approaches to building this pipeline: a function-calling architecture where the LLM invokes defined tools, or an agent loop where the LLM plans and executes steps iteratively.

Approach	Latency	Cost	Control	Failure modes
Function calling	Low (single round-trip)	Low	High	Wrong function selection, missed parameters
Sequential pipeline	Low (parallel-friendly)	Low	High	Error propagation across stages
Agent loop (ReAct)	High (multiple round-trips)	High	Low	Unbounded loops, compounding errors
Hybrid (classify then call)	Medium	Medium	High	Misclassification routes to wrong path

For analytics specifically, the agent loop approach is almost always wrong. Users want fast answers. An agent that iteratively refines its query strategy might be technically impressive but it adds 3-5 seconds of latency per refinement step. For a dashboard interaction, that is unacceptable.

The hybrid approach works well: use the LLM to classify intent and route to a deterministic pipeline per intent type. Only use an agent loop for genuinely ambiguous multi-step questions (“compare our top 10 customers by revenue to those same customers’ support ticket volume over the last year”), and expose that as a distinct “deep analysis” mode with explicit latency expectations set in the UI.

For function calling, define your analytics tools narrowly:

const analyticsTools = [
  {
    type: "function" as const,
    function: {
      name: "query_metric",
      description: "Query a single business metric for a given time range",
      parameters: {
        type: "object",
        properties: {
          metricName: { type: "string", enum: KNOWN_METRICS },
          timeRange: { type: "string" },
          groupBy: { type: "string", optional: true },
          filters: { type: "object", optional: true },
        },
        required: ["metricName", "timeRange"],
      },
    },
  },
  {
    type: "function" as const,
    function: {
      name: "query_custom",
      description:
        "Run a custom natural language query that requires SQL generation",
      parameters: {
        type: "object",
        properties: {
          question: { type: "string" },
        },
        required: ["question"],
      },
    },
  },
];

The query_metric tool handles the common case: known metrics the model can map directly to a prebuilt query template, bypassing SQL generation entirely. The query_custom tool handles the long tail. Most SaaS analytics products have 15-20 metrics that account for 80% of questions. Template-route those and your SQL generator only handles the genuinely novel questions.

Production Considerations

Multi-tenancy isolation. Every query must be scoped to the tenant’s data. The safest approach is separate schemas or databases per tenant so the connection itself cannot cross tenant boundaries. If you use row-level isolation, add a tenant filter validator that inspects every generated query and injects the tenant condition if missing.

Audit logging. Log every question, the generated SQL, the classification, the execution time, and the row count returned. When something goes wrong, you need this. Store it in an append-only table, not general application logs that rotate.

Observability on the pipeline. The failure modes are subtle. Classification confidence below 0.7 is worth alerting on. SQL execution time over 5 seconds is a sign the model generated a bad query. Zero-row results might indicate schema drift (a column was renamed). Add counters for each stage and trend them.

Schema versioning. When your database schema changes, the analytics system needs to know. Store your schema context as a versioned artifact, not a runtime introspection call on every request. When you alter a table, regenerate and redeploy the schema context. Otherwise the model generates SQL against columns that no longer exist.

Handling “I don’t know.” Build an explicit fallback for when the pipeline cannot produce a reliable answer: confidence below threshold, missingContext returned from the model, zero-row results on a question that should return data. Return the clarification prompt, not a blank chart. Users tolerate “I need more context to answer that” far better than a chart that shows nothing without explanation.

The Payoff

The average analytics user in a SaaS product asks fewer than three questions per session before giving up. The barrier is not curiosity, it is friction. Conversational analytics removes that friction, but only if the system is reliable enough that users trust the answers.

That trust is earned by one thing: the system being right more often than it is wrong, and being honest when it is uncertain. The technical work is building the pipeline layers that make that possible at query volume, with acceptable latency, and without unpredictable cost spikes.

Start with the template-routed common case. Instrument every stage before adding capabilities. Let real question patterns drive which parts of the pipeline you invest in. The classification layer will tell you exactly what your users actually want to know.