AI Assistant Agent: Chat Statistics

This workflow automatically collects and analyzes chat statistics from an AI assistant system, generating daily reports on conversation topics, message volumes, and user interactions. It runs on a scheduled basis to provide insights into chatbot usage patterns and popular discussion topics.

Purpose

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

How It Works

1. Daily Schedule: The workflow triggers automatically at 4 AM each day
2. Date Range Setup: Calculates the previous day's date range for data collection
3. Date Processing: Generates individual date objects for the analysis period
4. Session Retrieval: Queries the database for chat sessions within the specified timeframe
5. Data Validation: Checks if session data exists before proceeding
6. Chat History Collection: For each session, retrieves detailed chat messages from the database
7. Message Filtering: Identifies and processes only human messages from conversations
8. Statistical Analysis: Summarizes conversation data by intent/topic, counting unique sessions and total messages
9. Report Generation: Exports summary statistics to Google Sheets for reporting
10. Detailed Logging: Records individual chat messages to a separate sheet for detailed analysis
11. Rate Limiting: Implements delays between API calls to prevent service overload

Workflow Diagram

graph TD
    A[Schedule Trigger] --> B[Date Range]
    B --> C[Code]
    C --> D[Loop Over Items]
    D --> E[Postgres]
    E --> F[If Empty]
    F --> G[Edit Fields]
    F --> H[Get Session Chats]
    H --> I[Edit Fields1]
    I --> J[Summarize]
    I --> K[If]
    J --> G
    K --> L[Loop Over Items1]
    L --> M[Wait]
    M --> N[Chat History]
    N --> L
    N --> O[Wait1]
    O --> L
    G --> P[Google Sheets]
    P --> D
    Q[Webhook] -.-> B

Trigger

• Primary: Schedule Trigger - Runs daily at 4:00 AM
• Secondary: Webhook (currently disabled) - Manual trigger option available

Nodes Used

Node Type	Purpose
Schedule Trigger	Initiates daily execution at 4 AM
Set (Date Range)	Calculates previous day's start and end timestamps
Code	Generates array of dates for processing
Split in Batches	Processes dates and chat messages in batches
Postgres	Queries chat sessions and message history
If	Conditional logic for empty data and message filtering
Summarize	Aggregates conversation statistics by intent
Google Sheets	Exports data to spreadsheet reports
Wait	Implements rate limiting between API calls
Webhook	Alternative manual trigger (disabled)

External Services & Credentials Required

PostgreSQL Database

• Credential: "Postgres account"
• Purpose: Stores chat sessions and message history
• Tables: chat_sessions, n8n_chat_histories

Google Sheets

• Credential: "Google Service Account account"
• Purpose: Report generation and data export
• Document: "Educate Chatbot Stats" spreadsheet
• Sheets: "Sheet1" (summary stats), "Chat History" (detailed messages)

Environment Variables

No specific environment variables are configured in this workflow. All configuration is handled through node parameters and credentials.

Data Flow

Input

• Trigger: Scheduled time-based execution
• Database: Chat session records with session_id, created_at, intent fields
• Database: Chat history records with message content, type, and metadata

Processing

• Date range calculation for previous day
• SQL queries to extract relevant chat data
• Message filtering to identify human interactions
• Statistical aggregation by conversation intent/topic

Output

• Google Sheets Summary: Date, Topic, Number of Conversations, Total Messages
• Google Sheets Detail: ConversationID, Date, Program, Sender, Message Content

Error Handling

• Empty Data Check: If no chat sessions exist for a date, the workflow creates empty summary records
• Retry Logic: Google Sheets operations include retry on failure with 5-second delays
• Continue on Error: Chat History node continues processing even if individual records fail
• Always Output Data: Critical nodes are configured to output data even when no results are found

Known Limitations

• Processes only the previous day's data in each run
• Rate limiting may slow execution for high-volume chat days
• Dependent on external database and Google Sheets availability
• Manual webhook trigger is currently disabled

Related Workflows

No related workflows specified in the current context.

Setup Instructions

1. Import Workflow: Import the JSON configuration into your n8n instance

2. Configure Database Credentials:

   • Create PostgreSQL credential named "Postgres account"
   • Ensure access to chat_sessions and n8n_chat_histories tables

3. Setup Google Sheets Integration:

   • Create Google Service Account credential
   • Share target spreadsheet with service account email
   • Verify sheet names match: "Sheet1" and "Chat History"

4. Database Schema Requirements:

   -- chat_sessions table should include:
   -- session_id, created_at, intent
   
   -- n8n_chat_histories table should include:
   -- id, session_id, message (JSON), created_at
   

5. Activate Workflow: Enable the schedule trigger for daily execution

6. Test Execution: Use the manual trigger to verify data flow and output formatting

7. Monitor Results: Check Google Sheets for populated data after first successful run