AI Marketing Intelligence Suite

[mustafaneguib]

[PHASE 4] AI Marketing Intelligence Suite

Phase

Phase 4 — AI Intelligence | Depends on: Issues 01, 02, 05, 06. Best after Issues 09, 10 for richer data.

Description

The platform already has a functional AI layer for the Data Modeler (GeminiService, RedisAISessionService, DRAAIInsightReport, background job infrastructure via SchedulerService and QueueService, and a full notification system via NotificationHelperService). This infrastructure is reusable for marketing AI features without the need to build another AI model or integration.

This issue adds five AI-powered marketing intelligence features, surfaced across three locations in the platform:

1. Weekly Performance Summary — automatically-generated narrative email + in-app digest, scheduled weekly
2. Anomaly Detection — cron job comparing current week's metrics to trailing 4-week average; fires notifications on significant deviations
3. Budget Recommendation Engine — Gemini-powered analysis of ROAS by channel, generating reallocation suggestions
4. UTM Builder & Generator — interactive tool for creating correctly formatted UTM-tagged URLs; feeds back into the Attribution Engine
5. Natural Language Query — plain-English questions answered with chart-ready data or annotated SQL

These features make the platform proactive rather than reactive — instead of requiring a user to open a dashboard to notice a problem, the platform surfaces insights where and when they are needed.

What already exists (do NOT rebuild)

Layer	File	Status
Gemini 2.0 Flash integration	backend/src/services/GeminiService.ts	✅ Complete
Redis AI session management	backend/src/services/RedisAISessionService.ts	✅ Complete
Scheduler service	backend/src/services/SchedulerService.ts	✅ Complete — cron pattern
Background job queue	backend/src/services/QueueService.ts	✅ Complete
Notification system	backend/src/services/NotificationHelperService.ts	✅ Complete
Email service	Existing email templates from account/invite system	✅ Pattern to follow
UTMParameterService	backend/src/services/UTMParameterService.ts	✅ Core UTM logic exists
DRAAIInsightReport model	backend/src/models/DRAAIInsightReport.ts	✅ Add marketing insight types
Marketing data access	MarketingReportProcessor.getMarketingHubSummary() (Issue 06)	✅ On completion of Issue 06

---

First Principles Rationale

The difference between a BI tool and an intelligence platform is whether the system tells the user something they didn't know, versus waiting for the user to query for it. Weekly summaries convert marketing data into narrative context. Anomaly detection converts threshold crossings into actionable alerts. Budget recommendations convert ROAS differentials into specific spending decisions. UTM Builder lowers the barrier to accurate tracking data entry. NL Query removes the need to know SQL. Together these five features are the reason a CMO would describe this as an "intelligence platform" rather than a "reporting tool."

---

Acceptance Criteria

Feature 1: Weekly Performance Summary

• A weekly cron job runs every Monday at 8:00 AM (project owner's timezone) via SchedulerService
• For each project with at least one connected ad source, Gemini generates a 3-paragraph narrative summary of the prior week's performance
• Summary compares: best-performing channel, worst-performing channel, total spend vs prior week, total leads vs prior week, notable campaign activity
• Summary is stored as a DRAAIInsightReport with report_type = 'weekly_summary'
• An in-app notification is sent to all project CMO-role members with a preview of the summary
• An optional email version is sent if email_preferences.marketing_summaries = true (new preference column)
• Summary appears as a "Health Check" card on the Project Overview page (Marketing Hub — /marketing-projects/[id])

Feature 2: Anomaly Detection

• A daily cron job runs at 6:00 AM, comparing each KPI (spend, leads, CPL, ROAS) for each connected channel against the trailing 4-week average
• If a metric deviates by more than the configured threshold (default: 20%), an anomaly record is created
• Anomaly records are stored in a new dra_marketing_anomalies table
• Anomalies surface as:
  • anomaly_alert_card widget on dashboards (Issue 08)
  • In-app notification: "⚠️ Google Ads CPL increased 35% vs 4-week average"
  • Red badge on the Marketing Hub sidebar item
• Anomaly thresholds are configurable per project (project settings > AI Alerts)

Feature 3: Budget Recommendation Engine

• Available as a button "Get Recommendations" on the Campaign Detail Summary tab (Issue 02) and on the Marketing Hub channel comparison table (Issue 06)
• On click, sends current period ROAS, spend, and CPL data for each channel to Gemini with a system prompt
• Gemini returns 3 specific recommendations (e.g., "Increase LinkedIn Ads budget by 20% — ROAS is 4.1× vs 2.8× platform average. Reduce TikTok Ads spend by 15% — CPL has risen 40% over 3 weeks")
• Recommendations are displayed in a side panel with "Apply to Campaign Budget Plan" and "Dismiss" buttons
• Applying a recommendation updates the dra_campaign_channels budget allocation (not the total campaign budget — it redistributes the existing budget)
• Rate-limited using aiOperationsLimiter (20 calls/hour)

Feature 4: UTM Builder & Generator

• Accessible from the Data Connectivity section sidebar: /marketing-projects/[id]/data-sources/utm-builder
• Form fields: Campaign Source, Campaign Medium, Campaign Name, Campaign Term (optional), Campaign Content (optional), Destination URL
• Live-preview of the generated UTM URL below the form (reactive)
• "Copy URL" button copies to clipboard (guarded with import.meta.client)
• "Save as Template" stores commonly used UTM combinations in localStorage as presets (max 10 saved)
• Generated URLs are validated (destination URL must be a valid URL)
• UTM parameters are auto-seeded from linked campaign name when in campaign context (/campaigns/[cid]/utm-builder)

Feature 5: Natural Language Query

• Available as a chat interface on the Marketing Hub page: "Ask your data..."
• Powered by GeminiService with a marketing-specific system prompt containing the current project's data schema context
• Gemini returns one of: (a) a prose answer, (b) a SQL query + result table, (c) a chart configuration JSON that the frontend renders as a chart
• Session history persists in Redis (RedisAISessionService) for 24 hours
• Session can be saved to dra_ai_insight_reports (new report type: natural_language_query)
• Rate-limited using aiOperationsLimiter
• NL Query is aware of connected sources — system prompt includes classification-filtered schema context

---

Technical Implementation Plan

1. New Database Table: dra_marketing_anomalies

Migration [timestamp]-CreateMarketingAnomaliesTable.ts:

CREATE TABLE dra_marketing_anomalies (
    id              SERIAL PRIMARY KEY,
    project_id      INTEGER NOT NULL REFERENCES dra_projects(id) ON DELETE CASCADE,
    data_source_id  INTEGER REFERENCES dra_data_sources(id) ON DELETE SET NULL,
    channel_type    VARCHAR(50) NOT NULL,
    metric          VARCHAR(50) NOT NULL,      -- 'spend' | 'impressions' | 'cpl' | 'roas' | 'conversions'
    detected_at     TIMESTAMP DEFAULT NOW(),
    current_value   NUMERIC(15,4) NOT NULL,
    baseline_value  NUMERIC(15,4) NOT NULL,    -- 4-week average
    deviation_pct   NUMERIC(8,2) NOT NULL,
    direction       VARCHAR(10) NOT NULL,       -- 'spike' | 'drop'
    is_resolved     BOOLEAN DEFAULT FALSE,
    resolved_at     TIMESTAMP,
    severity        VARCHAR(20) NOT NULL        -- 'warning' (20-40%) | 'critical' (>40%)
);

2. MarketingAIService (backend/src/services/MarketingAIService.ts)

export class MarketingAIService {
    private static instance: MarketingAIService;
    public static getInstance(): MarketingAIService { ... }

    // Feature 1: Weekly Summary
    async generateWeeklySummary(projectId: number): Promise<string>
    // Calls GeminiService.sendMessage() with prior week's IMarketingHubSummary as context
    // Returns Gemini's narrative text

    // Feature 2: Anomaly Detection
    async detectAnomalies(projectId: number): Promise<IMarketingAnomaly[]>
    // Compares current week vs 4-week trailing average across all channels
    // Inserts anomaly records for deviations > threshold

    // Feature 3: Budget Recommendations
    async generateBudgetRecommendations(
        projectId: number,
        campaignId?: number
    ): Promise<IBudgetRecommendation[]>
    // Sends channel ROAS + spend data to Gemini
    // Returns 3 structured recommendations with channel, action, rationale

    // Feature 5: NL Query
    async processNaturalLanguageQuery(
        projectId: number,
        userId: number,
        sessionId: string,
        question: string
    ): Promise<INLQueryResponse>
    // Session managed via RedisAISessionService
    // Returns type: 'prose' | 'sql_result' | 'chart_config'
}

Gemini system prompt for Budget Recommendations:

You are a marketing budget optimisation assistant. 
Analyse the following channel performance data and provide exactly 3 specific budget reallocation recommendations.
Format each as: {"channel": "...", "action": "increase|decrease|maintain", "pct_change": 20, "rationale": "..."}
Only recommend changes where the data clearly supports the decision.
Current project data: [JSON of IMarketingHubSummary]

Gemini system prompt for NL Query:

You are a marketing analytics assistant with access to the following data sources for this project:
[List of connected sources with classification]
[Schema summary: table names, key columns]

The user will ask marketing performance questions. You can respond with:
1. A direct prose answer 
2. A PostgreSQL query (wrap in ```sql ... ```) — I will execute it and show results
3. A chart config (JSON with type, x, y, series) — the frontend will render it

Always cite which data source you used. If the data is insufficient, say so clearly.

3. Scheduled Jobs

WeeklySummaryJob (backend/src/jobs/WeeklySummaryJob.ts):

Follows the ScheduledDeletionJob pattern:

export class WeeklySummaryJob {
    public static schedule(): void {
        // Every Monday at 8:00 AM UTC
        cron.schedule('0 8 * * 1', async () => {
            const projects = await projectRepo.find({ where: { /* has marketing data */ } });
            for (const project of projects) {
                await QueueService.getInstance().enqueue({
                    type: 'weekly_summary',
                    projectId: project.id
                });
            }
        });
    }
}

AnomalyDetectionJob (backend/src/jobs/AnomalyDetectionJob.ts):

// Daily at 6:00 AM UTC
cron.schedule('0 6 * * *', async () => {
    const anomalies = await marketingAIService.detectAnomalies(projectId);
    for (const anomaly of anomalies) {
        await NotificationHelperService.getInstance().sendNotification({
            userId: projectOwnerId,
            type: 'marketing_anomaly',
            title: `${anomaly.direction === 'drop' ? '⚠️' : '📈'} ${anomaly.channel_type} ${anomaly.metric} ${anomaly.direction}`,
            message: `${Math.abs(anomaly.deviation_pct)}% ${anomaly.direction} vs 4-week average`,
            link: `/marketing-projects/${projectId}/marketing`
        });
    }
});

Add 'marketing_anomaly' to dra_notifications_type_enum via migration.

4. Backend Routes (backend/src/routes/marketingAI.ts)

POST /marketing-ai/weekly-summary/:projectId       — manual trigger (for testing)
GET  /marketing-ai/anomalies/:projectId            — list open anomalies
POST /marketing-ai/budget-recommendations          — body: { projectId, campaignId? }
POST /marketing-ai/nl-query                        — body: { projectId, question, sessionId }
GET  /marketing-ai/nl-query/session/:sessionId     — get session history

All routes: validateJWT + aiOperationsLimiter for compute-intensive endpoints.

5. Frontend Surface Points

Project Overview (/marketing-projects/[id]):

• "Weekly Performance Digest" card showing the most recent weekly_summary insight report
• "Active Alerts" card listing unresolved anomalies with severity badges

Campaign Detail → AI Analysis tab (/marketing-projects/[id]/campaigns/[cid]):

• "Get Budget Recommendations" button
• Recommendation side panel (3 structured suggestions)
• "Apply" button updates campaign channel budget allocation

Marketing Hub (/marketing-projects/[id]/marketing):

• "Ask your data..." NL Query chat panel (collapsible, right side of page)
• Chat history shown in the panel for the current session

Data Connectivity (/marketing-projects/[id]/data-sources):

• UTM Builder tool accessible from Data Connectivity sidebar sub-item

6. UTM Builder Page (frontend/pages/marketing-projects/[projectid]/data-sources/utm-builder.vue)

<template>
    <div class="grid grid-cols-1 lg:grid-cols-2 gap-8">
        <!-- Left: Input form -->
        <div>
            <h2>UTM Builder</h2>
            <form>
                <input v-model="destinationUrl" placeholder="https://yoursite.com/landing-page" />
                <input v-model="source" placeholder="Campaign Source (e.g. google)" />
                <input v-model="medium" placeholder="Campaign Medium (e.g. cpc)" />
                <input v-model="name" placeholder="Campaign Name (e.g. spring-2026)" />
                <input v-model="term" placeholder="Campaign Term (optional)" />
                <input v-model="content" placeholder="Campaign Content (optional)" />
            </form>
        </div>
        <!-- Right: Live preview + saved presets -->
        <div>
            <div class="bg-gray-50 p-4 rounded font-mono text-sm break-all">
                {{ generatedUrl }}
            </div>
            <button @click="copyUrl">
                <font-awesome-icon :icon="['fas', 'copy']" /> Copy URL
            </button>
            <button @click="savePreset">
                <font-awesome-icon :icon="['fas', 'bookmark']" /> Save as Preset
            </button>
            <div v-if="savedPresets.length">
                <h3>Saved Presets</h3>
                <button v-for="preset in savedPresets" @click="applyPreset(preset)">
                    {{ preset.name }}
                </button>
            </div>
        </div>
    </div>
</template>

---

New Notification Type

Add 'marketing_anomaly' to dra_notifications_type_enum enum via a new migration following the AddNewNotificationTypes pattern.

Email Preferences

Add marketing_summaries boolean column to dra_email_preferences:

ALTER TABLE dra_email_preferences ADD COLUMN marketing_summaries BOOLEAN DEFAULT TRUE;

---

Files to Create

File	Purpose
backend/src/services/MarketingAIService.ts	Core AI service
backend/src/jobs/WeeklySummaryJob.ts	Weekly cron job
backend/src/jobs/AnomalyDetectionJob.ts	Daily anomaly detection cron
backend/src/routes/marketingAI.ts	Marketing AI routes
backend/src/migrations/[timestamp]-CreateMarketingAnomaliesTable.ts	DB migration
backend/src/migrations/[timestamp]-AddMarketingAnomalyNotificationType.ts	Extend notifications enum
backend/src/migrations/[timestamp]-AddMarketingSummariesEmailPreference.ts	Email preferences column
frontend/pages/marketing-projects/[projectid]/data-sources/utm-builder.vue	UTM Builder page
frontend/components/budget-recommendations-panel.vue	Recommendations side panel
frontend/components/nl-query-chat.vue	Natural language query chat
frontend/components/anomaly-alerts-card.vue	Summary of open anomalies
frontend/components/weekly-digest-card.vue	Weekly summary digest card

Files to Modify

File	Change
backend/src/server.ts	Register marketingAI routes; start scheduled jobs
backend/src/datasources/PostgresDSMigrations.ts	Add anomalies table entity
frontend/pages/marketing-projects/[projectid]/index.vue	Add Weekly Digest + Anomaly Alerts cards
frontend/pages/marketing-projects/[projectid]/marketing/index.vue	Add NL Query chat panel
frontend/pages/marketing-projects/[projectid]/campaigns/[campaignid]/index.vue	Fill AI Analysis tab with Budget Recommendations
frontend/components/marketing-project-sidebar.vue	Add red anomaly badge to Marketing Hub sidebar item

---

Dependencies

• Requires: Issues 01, 02 (Campaign entity for budget recommendations), 06 (MarketingReportProcessor — data source for all AI features)
• Enhanced by: Issues 09-11 (richer data = better AI summaries and anomaly detection)
• GeminiService, RedisAISessionService, SchedulerService, QueueService, NotificationHelperService are all available and require no changes

Testing Requirements

• detectAnomalies() correctly identifies a 25% CPL increase as severity = 'warning' and a 45% drop as severity = 'critical'
• detectAnomalies() does not create duplicate anomaly records if the same deviation persists across multiple daily runs (check is_resolved = false exists for the same metric+channel before inserting)
• generateWeeklySummary() does not throw when a project has no data sources (returns "No data available for this period" message)
• generateBudgetRecommendations() response is rate-limited to aiOperationsLimiter (20/hour)
• UTM Builder copyUrl guarded with import.meta.client (no SSR crash)
• UTM Builder correctly URL-encodes spaces in campaign name parameter
• savePreset persists to localStorage and loads correctly on page revisit
• NL Query session persists for 24 hours in Redis and disappears after TTL expires
• Weekly summary email is NOT sent if email_preferences.marketing_summaries = false
• npm run validate:ssr passes

[mustafaneguib]

@preettrank53 would you be interested in working on this ticket?