Monitor Lead Response Time SLA Breaches with Google Sheets & Telegram Alerts

This n8n workflow helps sales teams and businesses monitor lead response times to ensure Service Level Agreements (SLAs) are met. It periodically checks a Google Sheet for leads that have breached their defined response time SLA and sends instant alerts to a Telegram chat.

What it does

1. Schedules Checks: Runs on a predefined schedule (e.g., every 15 minutes) to perform regular SLA checks.
2. Reads Lead Data: Retrieves lead information from a specified Google Sheet.
3. Calculates SLA Breaches: Uses a Code node to calculate the time elapsed since each lead's creation and determines if it has breached a predefined SLA (e.g., 15 minutes).
4. Filters Breached Leads: Identifies only those leads that have exceeded the SLA.
5. Sends Telegram Alerts: For each breached lead, it sends a detailed alert message to a designated Telegram chat, notifying the team about the overdue response.

Prerequisites/Requirements

• n8n Instance: A running n8n instance.
• Google Sheets Account: A Google account with access to Google Sheets.
  • Google Sheets Credential: An n8n credential configured for Google Sheets.
  • Lead Data Spreadsheet: A Google Sheet containing lead data with at least the following columns (or similar, adaptable in the Code node):
    • Lead Name
    • Lead Email
    • Creation Time (timestamp when the lead was created, e.g., YYYY-MM-DD HH:MM:SS)
• Telegram Account: A Telegram account.
  • Telegram Credential: An n8n credential configured for Telegram (Bot Token).
  • Telegram Chat ID: The chat ID of the group or individual where alerts should be sent.

Setup/Usage

1. Import the Workflow:
   • Download the provided JSON file.
   • In your n8n instance, go to "Workflows" and click "New".
   • Click the "Import from JSON" button and paste the workflow JSON or upload the file.
2. Configure Credentials:
   • Google Sheets:
     • Click on the "Google Sheets" node.
     • Select or create a new "Google Sheets API" credential. Follow the n8n documentation for setting up Google Sheets OAuth credentials.
   • Telegram:
     • Click on the "Telegram" node.
     • Select or create a new "Telegram API" credential. You'll need a bot token from BotFather.
3. Customize Google Sheets Node:
   • In the "Google Sheets" node, select the "Spreadsheet ID" of your lead data sheet.
   • Specify the "Sheet Name" (e.g., "Sheet1").
4. Adjust the Code Node:
   • Open the "Code" node.
   • Review the JavaScript code. This is where the SLA logic is defined.
   • SLA Threshold: Modify the slaThresholdMinutes variable to set your desired SLA (e.g., 15 for 15 minutes).
   • Column Names: Ensure the code correctly references the column names from your Google Sheet (e.g., item.json.creationTime, item.json.leadName, item.json.leadEmail). Adjust these if your column names are different.
5. Configure the "If" Node:
   • The "If" node checks the isSlaBreached property created by the Code node. No changes are typically needed here unless you modify the output of the Code node.
6. Customize Telegram Node:
   • In the "Telegram" node:
     • Set the "Chat ID" to the ID of the Telegram chat where you want to receive alerts.
     • Customize the "Text" message to include relevant lead details. The current message uses expressions like {{ $json.leadName }}, {{ $json.leadEmail }}, and {{ $json.timeSinceCreation }}.
7. Set the Schedule (Cron Node):
   • Click on the "Cron" node.
   • Configure the desired interval for the workflow to run (e.g., every 15 minutes, every hour).
8. Activate the Workflow:
   • Once all configurations are complete, save the workflow and activate it by toggling the "Active" switch in the top right corner of the n8n editor.

The workflow will now automatically monitor your Google Sheet for SLA breaches and send Telegram alerts.