Skip to content

AI Assistant Agent: Chat Statistics

This workflow automatically generates daily statistics for AI chatbot interactions by analyzing chat sessions and messages from a PostgreSQL database, then exports the data to Google Sheets for reporting and analysis.

Purpose

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

How It Works

  1. Daily Trigger: The workflow runs automatically at 4 AM each day via a schedule trigger
  2. Date Range Setup: Creates a date range for the previous day (yesterday from start to end of day)
  3. Date Processing: Converts the date range into individual date items for processing
  4. Session Query: For each date, queries the PostgreSQL database to find all chat sessions created on that day, including session ID, creation timestamp, and intent/topic
  5. Empty Check: Verifies if any sessions exist for the given date
  6. Chat History Retrieval: For each session found, fetches all associated chat messages from the chat history table
  7. Data Preparation: Combines session metadata with individual chat messages, preserving session context
  8. Statistics Generation: Summarizes the data by intent/topic, counting total messages and unique conversations
  9. Summary Export: Writes aggregated statistics (topic, conversation count, total messages, date) to the main Google Sheets tab
  10. Message Filtering: Identifies human messages from the chat history for detailed logging
  11. Detail Export: Exports individual human messages to a separate "Chat History" sheet with conversation context
  12. Rate Limiting: Includes wait periods between Google Sheets operations to prevent API rate limiting

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
    G --> L[Google Sheets]
    K --> M[Loop Over Items1]
    M --> N[Wait]
    N --> O[Chat History]
    O --> P[Wait1]
    O --> M
    P --> M
    L --> D
    Q[Webhook] -.-> B

Trigger

  • Schedule Trigger: Runs daily at 4:00 AM
  • Webhook (disabled): Alternative manual trigger available at /webhook/396718a3-595f-4e27-a561-0a4943ce9a7f

Nodes Used

Node Type Node Name Purpose
Schedule Trigger Schedule Trigger Triggers workflow daily at 4 AM
Set Date Range Creates start/end date range for previous day
Code Code Converts date range into individual date items
Split in Batches Loop Over Items Processes each date individually
Postgres Postgres Queries chat_sessions table for session data
If If Empty Checks if any sessions exist for the date
Postgres Get Session Chats Retrieves chat messages for each session
Set Edit Fields1 Formats chat message data with session context
Summarize Summarize Aggregates statistics by intent/topic
Set Edit Fields Formats summary data for export
Google Sheets Google Sheets Exports summary statistics to main sheet
If If Filters for human messages only
Split in Batches Loop Over Items1 Processes individual messages for detail export
Wait Wait Rate limiting for Google Sheets API
Google Sheets Chat History Exports detailed chat history to separate sheet
Wait Wait1 Additional rate limiting for batch processing
Webhook Webhook Manual trigger (disabled)

External Services & Credentials Required

PostgreSQL Database

  • Credential: "Postgres account"
  • Tables Used:
    • chat_sessions (session_id, created_at, intent)
    • n8n_chat_histories (id, session_id, message, created_at)

Google Sheets

  • Credential: "Google Service Account account"
  • Document: "Educate Chatbot Stats" (ID: 1n_ABeaVh_OsovfJOX9y0gSCn31mkHhgAhMn9Ye6M23k)
  • Sheets:
    • Sheet1 (summary statistics)
    • Chat History (detailed message logs)

Environment Variables

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

Data Flow

Input

  • Triggered by schedule (no external input required)
  • Processes previous day's data automatically

Output

  • Summary Statistics (Google Sheets - Sheet1):
    • Date
    • Topic/Intent
    • Number of Conversations (unique sessions)
    • Total Messages
  • Detailed Chat History (Google Sheets - Chat History):
    • ConversationID
    • Date
    • Program/Intent
    • Sender (human/ai)
    • Message Content

Error Handling

  • Postgres nodes: Set to "Always Output Data" to handle empty results gracefully
  • If Empty node: Explicitly checks for empty database results and routes accordingly
  • Google Sheets nodes:
    • Retry on failure enabled with 5-second wait between attempts
    • Chat History node continues on error to prevent workflow failure
  • Wait nodes: Implement rate limiting to prevent API quota issues

Known Limitations

  • Processes only the previous day's data (not configurable date ranges)
  • Relies on specific database schema (chat_sessions and n8n_chat_histories tables)
  • Google Sheets API rate limiting may cause delays with large datasets
  • Manual webhook trigger is disabled in current configuration

No related workflows identified in the current configuration.

Setup Instructions

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

  2. Configure PostgreSQL Credential:

    • Create a new PostgreSQL credential named "Postgres account"
    • Provide connection details for your chat database
    • Ensure access to chat_sessions and n8n_chat_histories tables

  3. Configure Google Service Account:

    • Create a Google Service Account credential named "Google Service Account account"
    • Share the target Google Sheets document with the service account email
    • Ensure the document has "Sheet1" and "Chat History" tabs

  4. Verify Database Schema:

    • Confirm chat_sessions table has: session_id, created_at, intent columns
    • Confirm n8n_chat_histories table has: id, session_id, message, created_at columns

  5. Test Execution:

    • Enable the webhook node temporarily for manual testing
    • Run a test execution to verify data flow and Google Sheets integration
    • Disable webhook and ensure schedule trigger is active

  6. Monitor Performance:

    • Check execution logs for any rate limiting issues
    • Adjust wait times if experiencing Google Sheets API errors
    • Verify daily statistics are being generated correctly