AI Analytics Assistant (Ask) — Phased Roadmap

This roadmap plans a prompt-based analytics assistant where stakeholders ask natural language questions about traffic quality and lead quality. The assistant uses Worker AI to plan a query (structured plan only, no raw SQL), executes predefined analytics queries, then uses AI again to summarize results into a stakeholder-friendly answer.

Data source: lead_submissions (Analytics Engine, 3-month retention).
Runtime: Cloudflare Workers + Worker AI.
Safety: Only predefined query types; no SQL generation.
Performance: Pre-aggregated data only; target AI + query latency < 2 seconds.

Current State (What Exists)

Analytics Engine: LEAD_ANALYTICS → lead_submissions; SQL via lib/analyticsEngineSql.ts.
Moderation queries: src/tracking/services/leadModerationQueries.ts — campaign quality, high bot by source/campaign, suspicion by source, suspicious referers, country risk, visitor abuse, block candidates, traffic quality, aggregations (by source, utm_campaign, referer, country, visitor).
API: /analytics/moderation/* endpoints for direct query access.
Worker AI: Not yet bound (same as AI moderation report; can share binding and types).

Target Architecture (High Level)

┌─────────────────────────────────────────────────────────────────────────┐
│  POST /analytics/ask  { "question": "Which campaigns produce the most    │
│                        bots?" }                                          │
└─────────────────────────────────────────────────────────────────────────┘
                                      │
                                      ▼
┌─────────────────────────────────────────────────────────────────────────┐
│  1. Query planner (Worker AI)                                            │
│     Question → structured QueryPlan (analysis_type, filters, group_by,   │
│     metrics). Only allowed analysis_type values; no SQL.                 │
└─────────────────────────────────────────────────────────────────────────┘
                                      │
                                      ▼
┌─────────────────────────────────────────────────────────────────────────┐
│  2. Query execution (safe dispatcher)                                   │
│     QueryPlan → call predefined getXxx(env, params) → aggregated result   │
└─────────────────────────────────────────────────────────────────────────┘
                                      │
                                      ▼
┌─────────────────────────────────────────────────────────────────────────┐
│  3. Result summarization (Worker AI)                                   │
│     Aggregated result + original question → answer, insights,           │
│     recommended_actions, possible_blocks                                │
└─────────────────────────────────────────────────────────────────────────┘
                                      │
                                      ▼
┌─────────────────────────────────────────────────────────────────────────┐
│  Response: { answer, insights, recommended_actions, ... }               │
└─────────────────────────────────────────────────────────────────────────┘

Phase 1: Query Plan Schema & Safety Model

Goal: Define the only shapes the AI is allowed to produce. No free-form SQL; only a fixed set of analysis types that map to existing query functions.

1.1 Query plan type (structured output)

Define a QueryPlan interface that the planner AI must output (and that we validate):

type TimeRange = 'last_24_hours' | 'last_7_days' | 'last_30_days';

type AnalysisType =
  | 'campaign_quality'      // → getCampaignQuality
  | 'campaign_bot_rates'    // → getHighBotRateByCampaign
  | 'source_bot_rates'      // → getHighBotRateBySource
  | 'source_suspicion'      // → getHighSuspicionBySource
  | 'referer_spam'          // → getSuspiciousReferers
  | 'country_risk'         // → getCountryRisk
  | 'visitor_abuse'        // → getVisitorAbuseCandidates
  | 'block_candidates'     // → getBlockCandidates
  | 'traffic_quality'      // → getTrafficQualityMetrics
  | 'aggregation_by_source'
  | 'aggregation_by_campaign'
  | 'aggregation_by_referer'
  | 'aggregation_by_country'
  | 'repeat_submissions_by_visitor';

interface QueryPlan {
  analysis_type: AnalysisType;
  filters: {
    time_range: TimeRange;
    project?: string;
    source?: string;
  };
  group_by?: string;   // optional refinement, e.g. "utm_campaign"
  metrics?: string[];   // optional hint, e.g. ["bot_rate", "avg_suspicion_score"]
}

analysis_type is an enum; the execution layer only calls a function for a known type. Unknown type → return error or fallback to traffic_quality.
filters are passed as params (e.g. days: 7, project, source) to the existing moderation query APIs.
group_by and metrics are optional hints; execution can ignore them or use them to choose among overloads (e.g. aggregation_by_campaign vs campaign_quality).

1.2 Allowed analysis types → function mapping

Document and implement a single dispatch table:

analysis_type	Query function	Params derived from plan
campaign_quality	getCampaignQuality	days from time_range, project, source
campaign_bot_rates	getHighBotRateByCampaign	same
source_bot_rates	getHighBotRateBySource	same
source_suspicion	getHighSuspicionBySource	same
referer_spam	getSuspiciousReferers	same
country_risk	getCountryRisk	same
visitor_abuse	getVisitorAbuseCandidates	same
block_candidates	getBlockCandidates	same
traffic_quality	getTrafficQualityMetrics	same
aggregation_by_source	getAggregationBySource	same
aggregation_by_campaign	getAggregationByUtmCampaign	same
aggregation_by_referer	getAggregationByReferer	same
aggregation_by_country	getAggregationByCountry	same
repeat_submissions_by_visitor	getRepeatSubmissionsByVisitor	same

No other analysis_type is accepted. If the planner returns an unknown type, the API returns a safe error or a generic “traffic_quality” result.

1.3 Time range mapping

last_24_hours → days: 1
last_7_days → days: 7
last_30_days → days: 30

Deliverables:

QueryPlan, AnalysisType, TimeRange types in a shared types file (e.g. src/ai/ask/types.ts or src/analytics/ask/types.ts).
Documented mapping from analysis_type to existing functions and params.
Validation helper: isValidQueryPlan(plan: unknown): plan is QueryPlan (checks analysis_type in allow list, filters.time_range in allow list).

Phase 2: Query Planner (Question → QueryPlan)

Goal: Use Worker AI to convert the user’s question into a QueryPlan. Output must be valid JSON matching the schema; only allowed analysis_type and time_range.

2.1 System prompt (planner)

Describe the domain: lead submission analytics, traffic quality, bot rate, suspicion, campaigns, referers, countries, visitor abuse, block candidates.
List the allowed analysis types and what each is for (e.g. “campaign_bot_rates: which campaigns have high bot share”).
Instruct: output only a JSON object that conforms to QueryPlan; no SQL, no explanation, no markdown.
Include the schema (or a short version) in the prompt so the model stays within bounds.

2.2 User prompt (planner)

User question only (e.g. “Which campaigns are generating bot traffic?”).
Optional: “If the question is unclear, prefer time_range: last_7_days and analysis_type: campaign_bot_rates or traffic_quality.”

2.3 Planner client

planQuery(env, question: string): Promise<QueryPlan | null>.
Call Worker AI with the planner system + user prompt.
Parse response as JSON; strip markdown code fences if present.
Run isValidQueryPlan(plan); if invalid, return null or a default plan (e.g. traffic_quality, last_7_days).
Do not expose any SQL or raw query to the model.

Deliverables:

src/ai/ask/plannerPrompt.ts (or under src/ai/) with system and user prompt builders.
planQuery(env, question) in e.g. src/ai/ask/queryPlanner.ts.
Unit test with mocked AI returning valid and invalid plans; assert only allowed analysis_type can be returned.

Phase 3: Query Execution Layer (Plan → Aggregated Result)

Goal: A single execution function that takes a validated QueryPlan and returns the aggregated result from the existing moderation/aggregation APIs. No SQL construction; only calls to known functions.

3.1 Executor

executeQueryPlan(env, plan: QueryPlan): Promise<AggregatedResult>.
Map plan.analysis_type to the corresponding function from the table in Phase 1.
Build params from plan.filters: days from time_range, optional project, source, and min_submissions (e.g. 5).
Call the function; normalize the result into a generic shape (e.g. array of rows or single object) so the summarizer AI gets a consistent structure.
Type AggregatedResult as something like { analysis_type, data: unknown, row_count?: number } so we never send raw submission rows—only what the existing functions return (already aggregated).

3.2 Error handling

If the underlying Analytics Engine or query fails, return a structured error (e.g. { error: 'query_failed', message }) so the summarizer can say “data temporarily unavailable” instead of hallucinating.

Deliverables:

src/analytics/ask/queryExecutor.ts (or src/worker/ask/queryExecutor.ts) with executeQueryPlan(env, plan).
All branches use only the predefined function map; no dynamic SQL.
Tests with mocked Env and mocked moderation functions to assert correct dispatch and param mapping.

Phase 4: Result Summarization (Result → Answer, Insights, Actions)

Goal: Send the aggregated result (and original question) to Worker AI and get back a stakeholder-friendly answer plus structured fields: explanation, insights, recommended_actions, possible_blocks.

4.1 Summarizer system prompt

Role: analytics assistant for marketing stakeholders.
Input: the user’s question and the aggregated query result (e.g. top N rows or summary stats). Emphasize that the result is pre-aggregated; do not ask for more data.
Output: valid JSON only, with keys: answer (string), insights (string[]), recommended_actions (string[]), possible_blocks (string[], optional—e.g. “Consider blocking visitor X or source Y”).
Tone: clear, non-technical, actionable.

4.2 Summarizer user prompt

Include the user question and a stringified/summarized version of the aggregated result (e.g. first 20 rows or summary stats). Keep payload small to stay under token limits and < 2 s.

4.3 Summarizer client

summarizeResult(env, question: string, result: AggregatedResult): Promise<AskResponse>.
Call Worker AI; parse JSON; validate shape (answer string, insights array, etc.).
If parsing fails, return a fallback: e.g. “Summary unavailable” and empty arrays.

4.4 Response type

interface AskResponse {
  answer: string;
  insights: string[];
  recommended_actions: string[];
  possible_blocks?: string[];
}

Deliverables:

src/ai/ask/summarizerPrompt.ts and summarizeResult() in e.g. src/ai/ask/resultSummarizer.ts.
AskResponse type and optional validation.
Test with mocked AI and sample aggregated result.

Phase 5: Worker API (POST /analytics/ask)

Goal: Single endpoint that ties planner → executor → summarizer and returns a consistent JSON response.

5.1 Endpoint

POST /analytics/ask
Body: { "question": "Which campaigns produce the most bots?" } (question required).
Auth: Reuse existing analytics auth (e.g. Bearer or X-API-Key with ANALYTICS_API_KEY or marketing key). Restrict to authenticated users only.
Response (200):
{ "answer": "...", "insights": [], "recommended_actions": [], "possible_blocks": [] }
Optionally include query_plan (sanitized) and/or analysis_type for debugging (e.g. in non-production or with a header).

5.2 Orchestration

Validate body (question present, string, max length e.g. 500 chars).
Call planQuery(env, question).
If plan is null, return 400 or a generic answer: “I couldn’t map your question to a report. Try asking about campaign quality, bot rates by source, or visitor abuse.”
Call executeQueryPlan(env, plan).
If execution fails, return 503 or 200 with answer “Data is temporarily unavailable” and empty insights/actions.
Call summarizeResult(env, question, result).
Return 200 with AskResponse.

5.3 Routing

In src/index.ts (or analytics router), add POST route for /analytics/ask that calls the orchestration function and returns JSON.

Deliverables:

Orchestration function in e.g. src/worker/askAnalyticsAssistant.ts or src/analytics/ask/handler.ts.
Route + auth for POST /analytics/ask.
Integration test or manual test: POST with a sample question, assert 200 and presence of answer/insights/recommended_actions.

Phase 6: Performance & Safety Hardening

Goal: Keep latency under 2 seconds where possible; enforce that only aggregated data is used and no SQL is ever generated from user input.

6.1 Performance

Planner: Use a small/fast model if available (e.g. Llama 3 8B or a smaller variant); single short response (JSON only).
Executor: Existing queries are already aggregated; avoid calling multiple analysis types in one request unless we explicitly support “multi-query” later. For now, one plan → one query.
Summarizer: Limit the result payload sent to the AI (e.g. top 20 rows or a capped summary string) to reduce tokens and latency.
Time budget: Optionally add a timeout (e.g. 1.5 s for AI calls) and return a partial response if exceeded.

6.2 Safety checklist

No raw SQL: Planner returns only QueryPlan; executor uses only the predefined function map. No string concatenation into SQL.
No raw rows to AI: Only the output of getTrafficQualityMetrics, getCampaignQuality, getSuspiciousReferers, etc. (already aggregated) is sent to the summarizer. Never pass raw lead_submissions rows.
Input validation: Question length limit; reject non-JSON or unknown analysis_type in plan validation.
Auth: Only authenticated analytics users can call POST /analytics/ask.

Deliverables:

Document performance assumptions and any timeout.
Code review checklist or comment in executor: “Only predefined functions; no SQL generation.”
Optional: add a test that asserts no SQL string is built from user question.

Phase 7: Documentation & Examples

Goal: Document the assistant for stakeholders and developers.

7.1 Docs

docs/analytics/ai-analytics-assistant.md:
- Purpose: natural language questions about traffic/lead quality.
- API: POST /analytics/ask, body, response shape, auth.
- Allowed question types (mapped to analysis_type) and example questions.
- Safety: predefined queries only; no SQL.
- Performance and limits (question length, time range).

7.2 Example questions and expected behavior

“Which campaigns are generating bot traffic?” → campaign_bot_rates or campaign_quality.
“Why did lead quality drop yesterday?” → traffic_quality (last_24_hours) + maybe comparison note in prompt.
“Which countries generate the most suspicious leads?” → country_risk.
“Which visitors are abusing the form?” → visitor_abuse.
“Which referers produce spam leads?” → referer_spam.

Add these to the doc and optionally to integration tests (assert correct analysis_type or answer presence).

Deliverables:

docs/analytics/ai-analytics-assistant.md.
Example questions in doc; optional test cases.
ARCHITECTURE.md update: add /analytics/ask and the planner → executor → summarizer flow.

Phase Summary Table

Phase	Focus	Key deliverables
1	Query plan schema & safety	QueryPlan type, AnalysisType enum, allowed → function mapping, validation
2	Query planner (AI)	Planner prompt, planQuery(env, question), validation of plan
3	Query execution	executeQueryPlan(env, plan), dispatch to existing getXxx only
4	Result summarization (AI)	Summarizer prompt, summarizeResult(), AskResponse type
5	Worker API	POST /analytics/ask, orchestration, auth, response shape
6	Performance & safety	Payload limits, no-SQL guarantee, auth, optional timeout
7	Documentation	ai-analytics-assistant.md, example questions, ARCHITECTURE

Dependency Order

Phase 2 depends on Phase 1 (QueryPlan type and validation).
Phase 3 depends on Phase 1 (mapping and params).
Phase 4 depends on Phase 3 (AggregatedResult shape).
Phase 5 depends on Phases 2, 3, 4.
Phase 6 can be done in parallel with 5 and finalized after 5.
Phase 7 can be done in parallel and updated after 5–6.

Suggested File Layout

src/
├── ai/
│   ├── ask/
│   │   ├── types.ts           # QueryPlan, AnalysisType, TimeRange, AskResponse
│   │   ├── plannerPrompt.ts   # system + user prompt for planner
│   │   ├── queryPlanner.ts    # planQuery(env, question)
│   │   ├── summarizerPrompt.ts
│   │   └── resultSummarizer.ts  # summarizeResult(env, question, result)
│   ├── moderationPrompt.ts   # (from AI moderation report roadmap)
│   └── aiClient.ts            # shared run model + parse JSON if needed
├── analytics/
│   └── ask/
│       ├── queryExecutor.ts   # executeQueryPlan(env, plan) → AggregatedResult
│       └── handler.ts        # orchestration for POST /analytics/ask (or under worker/)
├── worker/
│   └── askAnalyticsAssistant.ts  # optional: thin wrapper + route handler
└── tracking/
    └── services/
        └── leadModerationQueries.ts  # unchanged; called by queryExecutor

Execution layer can live under src/analytics/ask/ or src/worker/ depending on where you want the “ask” feature to sit; handler can be the same file as orchestration.

Relation to AI Moderation Report

Shared: Worker AI binding (wrangler + Env), optional shared aiClient for “run model + parse JSON.”
Different:
- Moderation report: fixed summary (24h) → one AI call → report.
- Ask: user question → planner AI → predefined query → executor → summarizer AI → answer.
Both use only aggregated data and the same lead_submissions dataset and existing moderation query functions.

Risks and Mitigations

Risk	Mitigation
Planner returns invalid or unsafe analysis_type	Strict allow list; isValidQueryPlan(); fallback to traffic_quality.
Model outputs non-JSON	Strip markdown; retry with “output only JSON”; fallback to “I couldn’t interpret that.”
Latency > 2 s	Use small model; limit summarizer input; optional timeout; document.
User tries to get raw data	No SQL path; only predefined queries; result is always aggregated.

Next Steps

Phase 1: Define QueryPlan, AnalysisType, mapping table, and validation.
Phase 2: Implement planner prompt and planQuery; test with allowed and disallowed outputs.
Phase 3: Implement executeQueryPlan with the fixed function map.
Phase 4: Implement summarizer prompt and summarizeResult.
Phase 5: Add POST /analytics/ask and orchestration.
Phases 6–7: Harden performance/safety and write documentation.

This roadmap is ready for discussion and phase-by-phase implementation.

AI Analytics Assistant (Ask) — Phased Roadmap ​

Current State (What Exists) ​

Target Architecture (High Level) ​

Phase 1: Query Plan Schema & Safety Model ​

1.1 Query plan type (structured output) ​

1.2 Allowed analysis types → function mapping ​

1.3 Time range mapping ​

Phase 2: Query Planner (Question → QueryPlan) ​

2.1 System prompt (planner) ​

2.2 User prompt (planner) ​

2.3 Planner client ​

Phase 3: Query Execution Layer (Plan → Aggregated Result) ​

3.1 Executor ​

3.2 Error handling ​

Phase 4: Result Summarization (Result → Answer, Insights, Actions) ​

4.1 Summarizer system prompt ​

4.2 Summarizer user prompt ​

4.3 Summarizer client ​

4.4 Response type ​

Phase 5: Worker API (POST /analytics/ask) ​

5.1 Endpoint ​

5.2 Orchestration ​

5.3 Routing ​

Phase 6: Performance & Safety Hardening ​

6.1 Performance ​

6.2 Safety checklist ​

Phase 7: Documentation & Examples ​

7.1 Docs ​

7.2 Example questions and expected behavior ​

Phase Summary Table ​

Dependency Order ​

Suggested File Layout ​

Relation to AI Moderation Report ​

Risks and Mitigations ​

Next Steps ​

AI Analytics Assistant (Ask) — Phased Roadmap

Current State (What Exists)

Target Architecture (High Level)

Phase 1: Query Plan Schema & Safety Model

1.1 Query plan type (structured output)

1.2 Allowed analysis types → function mapping

1.3 Time range mapping

Phase 2: Query Planner (Question → QueryPlan)

2.1 System prompt (planner)

2.2 User prompt (planner)

2.3 Planner client

Phase 3: Query Execution Layer (Plan → Aggregated Result)

3.1 Executor

3.2 Error handling

Phase 4: Result Summarization (Result → Answer, Insights, Actions)

4.1 Summarizer system prompt

4.2 Summarizer user prompt

4.3 Summarizer client

4.4 Response type

Phase 5: Worker API (POST /analytics/ask)

5.1 Endpoint

5.2 Orchestration

5.3 Routing

Phase 6: Performance & Safety Hardening

6.1 Performance

6.2 Safety checklist

Phase 7: Documentation & Examples

7.1 Docs

7.2 Example questions and expected behavior

Phase Summary Table

Dependency Order

Suggested File Layout

Relation to AI Moderation Report

Risks and Mitigations

Next Steps