Skip to content

AI Assistant Agent: Chat Statistics

This workflow automatically collects and analyzes chat statistics from an AI assistant system, generating daily reports that track conversation metrics, user engagement patterns, and topic distribution. It processes chat session data from a PostgreSQL database and updates Google Sheets dashboards with aggregated statistics and detailed chat histories.

Purpose

No business context provided yet — add a context.md to enrich this documentation.

This workflow serves as an analytics and reporting system for AI assistant interactions. It enables stakeholders to:

  • Monitor daily chat volume and user engagement
  • Track conversation topics and intent distribution
  • Analyze user message patterns and behavior
  • Generate automated reports for performance review
  • Maintain historical records of chat interactions

The system is designed for product managers, customer success teams, and data analysts who need insights into AI assistant usage and effectiveness.

How It Works

  1. Scheduled Trigger: The workflow runs daily at 4 AM to process the previous day's chat data
  2. Date Range Setup: Calculates the date range for data collection (previous day)
  3. Date Processing: Generates individual date entries for batch processing
  4. Data Retrieval: Queries PostgreSQL to fetch chat sessions for each date
  5. Empty Check: Verifies if data exists for the given date
  6. Session Analysis: For each session, retrieves detailed chat history
  7. Data Preparation: Formats and structures the chat data
  8. Statistics Generation: Aggregates metrics by intent/topic using the Summarize node
  9. Report Creation: Updates Google Sheets with conversation statistics
  10. Chat History Export: Filters human messages and exports detailed chat logs
  11. Rate Limiting: Implements delays to respect API limits during batch processing

Workflow Diagram

graph TD
    A[Schedule Trigger] --> B[Date Range]
    B --> C[Code - Generate Dates]
    C --> D[Loop Over Items]
    D --> E[Postgres - Query Sessions]
    E --> F[If Empty Check]
    F -->|No Data| G[Edit Fields]
    F -->|Has Data| H[Get Session Chats]
    H --> I[Edit Fields1]
    I --> J[Summarize by Intent]
    I --> K[If Human Message]
    J --> G
    G --> L[Google Sheets - Statistics]
    K --> M[Loop Over Items1]
    M --> N[Wait 20s]
    N --> O[Chat History Sheet]
    O --> P[Wait1 1min]
    P --> M
    L --> D
    Q[Webhook - Disabled] -.-> B

Trigger

Primary Trigger: Schedule Trigger - Runs daily at 4:00 AM - Processes previous day's chat data automatically

Secondary Trigger: Webhook (Currently Disabled) - Path: /webhook/396718a3-595f-4e27-a561-0a4943ce9a7f - Can be used for manual/on-demand execution

Nodes Used

Node Type Node Name Purpose
Schedule Trigger Schedule Trigger Initiates daily execution at 4 AM
Set Date Range Calculates start/end dates for data collection
Code Code Generates date array for batch processing
Split In Batches Loop Over Items Processes dates in batches
Postgres Postgres Queries chat_sessions table for session data
If If Empty Checks if query returned data
Postgres Get Session Chats Retrieves detailed chat history per session
Set Edit Fields1 Formats chat data with session metadata
Summarize Summarize Aggregates statistics by intent/topic
Set Edit Fields Prepares final statistics for export
Google Sheets Google Sheets Updates main statistics dashboard
If If Filters for human messages only
Split In Batches Loop Over Items1 Batches chat history for export
Wait Wait 20-second delay for rate limiting
Google Sheets Chat History Exports detailed chat logs
Wait Wait1 1-minute delay between batches

External Services & Credentials Required

PostgreSQL Database - Credential: "Postgres Waringa" - Tables accessed: chat_sessions, n8n_chat_histories - Required permissions: SELECT

Google Sheets - Credential: "Google Service Account Waringa" - Document: "Educate Chatbot Stats" (ID: 1n_ABeaVh_OsovfJOX9y0gSCn31mkHhgAhMn9Ye6M23k) - Sheets: "Sheet1" (statistics), "Chat History" (detailed logs) - Required permissions: Read/Write access to spreadsheet

Environment Variables

No specific environment variables are required. All configuration is handled through: - Node parameters (date calculations, queries) - Credential references - Hard-coded Google Sheets document IDs

Data Flow

Input Data: - Chat session records from PostgreSQL - Date range (previous day by default) - Session metadata (session_id, created_at, intent) - Chat message details (type, content, timestamps)

Processing: - Aggregates sessions by intent/topic - Counts unique sessions and total messages - Filters human messages for detailed export - Applies rate limiting for API calls

Output Data: - Statistics Sheet: Date, Topic, Number of Conversations, Total Messages - Chat History Sheet: ConversationID, Date, Program, Sender, Message Content - Timestamped metrics for trend analysis

Error Handling

Database Connection Issues: - Postgres nodes have alwaysOutputData: true to continue on empty results - If Empty node handles cases where no data exists for a date

Google Sheets API Limits: - Wait nodes implement rate limiting (20 seconds between chat exports) - Chat History node has onError: continueErrorOutput to handle API failures - Retry configuration: retryOnFail: true with 5-second intervals

Batch Processing: - Split In Batches nodes ensure large datasets are processed incrementally - Loop mechanisms allow workflow to continue even if individual items fail

Known Limitations

  • Processes only the previous day's data by default
  • Rate limiting may cause longer execution times for large datasets
  • Webhook trigger is currently disabled
  • Hard-coded Google Sheets document IDs require manual updates if sheets change
  • No data validation or duplicate detection for exported records

No related workflows specified in the current context.

Setup Instructions

  1. Import Workflow

    • Import the JSON workflow into your n8n instance
    • Ensure all required node types are available

  2. Configure PostgreSQL Credential

    • Create a new PostgreSQL credential named "Postgres Waringa"
    • Configure connection to database containing chat_sessions and n8n_chat_histories tables
    • Test connection and verify table access

  3. Configure Google Sheets Credential

    • Create a Google Service Account credential named "Google Service Account Waringa"
    • Share target Google Sheets document with the service account email
    • Verify the document ID matches: 1n_ABeaVh_OsovfJOX9y0gSCn31mkHhgAhMn9Ye6M23k

  4. Verify Database Schema

    • Ensure chat_sessions table has columns: session_id, created_at, intent
    • Ensure n8n_chat_histories table has columns: id, session_id, message
    • Verify message column contains JSON with type and content fields

  5. Test Execution

    • Run the workflow manually to verify data flow
    • Check that both Google Sheets are updated correctly
    • Monitor execution time and adjust wait intervals if needed

  6. Enable Webhook (Optional)

    • Enable the Webhook node if on-demand execution is required
    • Note the webhook URL for manual triggering

  7. Schedule Configuration

    • Verify the Schedule Trigger is set to your preferred time
    • Ensure timezone settings match your requirements
    • Activate the workflow for automatic daily execution