Adding AI-Powered Insights to Plausible Analytics

#Generate SQL from natural language

Use your LLM to translate questions into SQL:

copyasync 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:

copyasync 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:

copy<!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:

copyCREATE 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:

copyconst 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:

copySELECT ... 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 2 vCPU / 4 GB VPS handles Plausible with AI; for heavy analytics workloads, consider 4 vCPU / 8 GB. See Best VPS for AI & ML Workloads for the ranked picks.