Adding AI-Powered Insights to Plausible Analytics

Updated Aug 2026

verified on Ubuntu 26.04 · Aug 2026

Enhance your self-hosted Plausible analytics with AI insights — use LLMs to analyze traffic patterns, generate reports, and answer questions about your data.

Before you start

Plausible running on a VPS (see Deploy Plausible on a VPS)
Ollama or another LLM backend available
Basic understanding of SQL and data analysis

Why AI + Plausible?

Plausible is a privacy-friendly, self-hosted analytics tool. It gives you clean, simple metrics without cookies, trackers, or GDPR headaches. But Plausible's strength — simplicity — also means it doesn't do deep analysis. Adding AI lets you ask questions about your data in natural language and get insights that would require writing custom SQL queries.

The appeal is conversational analytics. Instead of clicking through dashboards or writing SQL, you can ask: "What pages got the most traffic last month?" or "Why did my signups drop in March?" and get a clear answer with context.

This guide assumes you already have Plausible running. If not, set up a self-hosted instance first, then return here to add AI capabilities.

Architecture overview

The approach:

Plausible stores your analytics data in ClickHouse
A middleware service connects to both ClickHouse and your LLM
The AI translates natural language questions into SQL, runs them, and interprets the results

This keeps your analytics data in Plausible where it belongs, while adding an AI layer on top.

Set up the middleware

Create a simple API that connects ClickHouse to your LLM:

// plausible-ai/index.ts
import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { createClient } from "https://deno.land/x/clickhouse@v0.0.14/mod.ts";

const clickhouse = createClient({
  url: Deno.env.get("CLICKHOUSE_URL") || "http://clickhouse:8123",
  username: "clickhouse",
  password: Deno.env.get("CLICKHOUSE_PASSWORD"),
});

serve(async (req) => {
  const { question } = await req.json();
  
  // 1. Generate SQL from natural language
  const sql = await generateSQL(question);
  
  // 2. Execute the query
  const result = await clickhouse.query({
    query: sql,
    format: "JSONEachRow",
  });
  
  // 3. Interpret the results
  const insight = await interpretResults(question, sql, result);
  
  return new Response(JSON.stringify({ sql, results: result, insight }), {
    headers: { "Content-Type": "application/json" },
  });
});

Generate SQL from natural language

Use your LLM to translate questions into SQL:

async function generateSQL(question: string): Promise<string> {
  const schema = `
Tables:
- events: name, pathname, hostname, timestamp, user_id, session_id
- sessions: session_id, user_id, timestamp, duration, source, referrer, utm_*
- pageviews: event_id, pathname
- custom_events: event_id, name, props
`;
  
  const response = await fetch("http://host.docker.internal:11434/api/generate", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      model: "llama3.2",
      prompt: `You are a ClickHouse SQL expert. Given this schema:
${schema}

Convert this question to SQL:
${question}

Return ONLY the SQL query, no explanation.`,
      stream: false,
    }),
  });
  
  const { response: sql } = await response.json();
  return sql.trim();
}

Interpret results with AI

Raw data isn't always insightful. Use the LLM to interpret:

async function interpretResults(
  question: string,
  sql: string,
  results: any[]
): Promise<string> {
  const response = await fetch("http://host.docker.internal:11434/api/generate", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      model: "llama3.2",
      prompt: `Question: ${question}

SQL executed: ${sql}

Results (first 20 rows):
${JSON.stringify(results.slice(0, 20), null, 2)}

Provide a clear, concise insight based on these results. What's interesting? What should the user pay attention to?`,
      stream: false,
    }),
  });
  
  const { response: insight } = await response.json();
  return insight;
}

Example questions you can ask

With this setup, you can ask questions like:

Traffic analysis:

"What were my top 10 pages last month?"
"How much traffic did I get from Google vs Twitter?"
"What's my average session duration?"

Trend analysis:

"How has my traffic changed over the last 6 months?"
"Which pages are growing the fastest?"
"What day of the week gets the most traffic?"

User behavior:

"What's my most common exit page?"
"How do users navigate through my site?"
"What referrers bring the most engaged users?"

Content performance:

"Which blog posts get the most views?"
"What's my bounce rate by page?"
"How do new vs returning visitors behave differently?"

Adding a chat interface

Create a simple web interface for asking questions:

<!DOCTYPE html>
<html>
<head>
  <title>AI Analytics</title>
  <style>
    body { font-family: sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; }
    .chat { margin-top: 20px; }
    .message { margin: 10px 0; padding: 10px; border-radius: 8px; }
    .user { background: #f0f0f0; }
    .ai { background: #e3f2fd; }
    pre { background: #f5f5f5; padding: 10px; overflow-x: auto; }
  </style>
</head>
<body>
  <h1>AI Analytics</h1>
  <input type="text" id="question" placeholder="Ask about your analytics..." 
         style="width: 70%; padding: 10px;">
  <button onclick="ask()" style="padding: 10px 20px;">Ask</button>
  
  <div class="chat" id="chat"></div>

  <script>
    async function ask() {
      const question = document.getElementById('question').value;
      const chat = document.getElementById('chat');
      
      // Show user question
      chat.innerHTML += `<div class="message user">${question}</div>`;
      
      // Get AI response
      const response = await fetch('/api/ask', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ question })
      });
      
      const data = await response.json();
      
      // Show AI insight
      chat.innerHTML += `<div class="message ai">${data.insight}</div>`;
      chat.innerHTML += `<div class="message"><pre>${data.sql}</pre></div>`;
      
      document.getElementById('question').value = '';
    }
  </script>
</body>
</html>

Security considerations

Read-only access: The AI should only read data, never modify it. Create a ClickHouse user with SELECT-only permissions:

CREATE USER analytics_reader IDENTIFIED BY 'secure_password';
GRANT SELECT ON plausible.* TO analytics_reader;

SQL injection protection: The LLM generates SQL, which is risky. Add validation:

Only allow SELECT queries
Block DROP, DELETE, UPDATE, INSERT
Limit query execution time
Set maximum result set size

API authentication: Protect the /api/ask endpoint with authentication. Use API keys or session-based auth.

Performance tips

Cache common questions: If you ask the same question repeatedly, cache the results. Store them in Redis or a simple file cache.

Query timeouts: ClickHouse queries should be fast, but set timeouts to prevent runaway queries:

const result = await clickhouse.query({
  query: sql,
  format: "JSONEachRow",
  settings: { max_execution_time: 30 },  // 30 seconds
});

Limit result size: Always add LIMIT to generated SQL to prevent massive result sets:

SELECT ... FROM ... LIMIT 100

Troubleshooting

ClickHouse connection fails. Verify ClickHouse is running and the credentials are correct. Check the ClickHouse logs for connection errors.

LLM generates invalid SQL. The model may not understand your schema. Improve the schema description in the prompt, or use a larger model for SQL generation.

Slow responses. AI inference takes time, especially for complex queries. Consider using a faster model or caching frequent queries.

Results don't match the question. The LLM may misinterpret the question. Refine your prompt or add examples of good question-to-SQL translations.

Out of memory. Large result sets can use significant memory. Always add LIMIT clauses and paginate results.

Verification + next steps

You're done when you can: ask natural language questions about your analytics, get SQL queries and results, and receive AI-interpreted insights. Test with simple questions first, then try more complex analysis.

From here, explore advanced features like automated daily reports, anomaly detection, and natural language alerts. For the base Plausible setup, see Deploy Plausible on a VPS. For database-backed AI, see Supabase with AI. A Hetzner CX22 (2 vCPU / 4 GB) handles Plausible with AI; for heavy analytics workloads, consider a CX32 (4 vCPU / 8 GB). See Best VPS for AI & ML Workloads for the ranked picks.

Next steps

Adding AI-Powered Insights to Plausible Analytics

#Why AI + Plausible?

#Architecture overview

#Set up the middleware

#Generate SQL from natural language

#Interpret results with AI

#Example questions you can ask

#Adding a chat interface

#Security considerations

#Performance tips

#Troubleshooting

#Verification + next steps