Smart Invoice Followup System

{
  "id": "krCtlHDssldDicnv",
  "meta": {
    "instanceId": "d7661a849ead114a9aa6d9ceaf4160465aeb79532a35bde62160c840ffba9fc8",
    "templateCredsSetupCompleted": true
  },
  "name": "Smart Invoice Followup System",
  "tags": [],
  "nodes": [
    {
      "id": "sticky-note-1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        180,
        -100
      ],
      "parameters": {
        "width": 350,
        "height": 180,
        "content": "## 📊 STEP 1: Invoice Data Collection\n\nThis workflow monitors overdue invoices from Google Sheets:\n\n1. **Get Rows**: Pulls invoice data from your tracking spreadsheet\n2. **Edit Fields**: Calculates days since invoice was sent using date difference\n3. **Filter**: Only processes invoices that are 7, 14, 21, or 28 days overdue\n\n**Setup**: Your Google Sheet needs columns for Date Sent, Client Name, Email, and Invoice ID"
      },
      "typeVersion": 1
    },
    {
      "id": "sticky-note-2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        850,
        -150
      ],
      "parameters": {
        "width": 380,
        "height": 200,
        "content": "## 🔍 STEP 2: Email History Intelligence\n\nPrevents sending follow-ups at inappropriate times:\n\n1. **Loop Over Items**: Processes each overdue invoice individually\n2. **Gmail Get Messages**: Retrieves recent email history with each client\n3. **Aggregate**: Combines email threads for AI analysis\n4. **Edit Fields**: Formats conversation data with timestamps\n\n**Key**: This step ensures you don't send follow-ups too soon after recent communication"
      },
      "typeVersion": 1
    },
    {
      "id": "sticky-note-3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1500,
        -170
      ],
      "parameters": {
        "width": 400,
        "height": 220,
        "content": "## 🧠 STEP 3: AI-Powered Follow-up Generation\n\nCreates intelligent, context-aware follow-up emails:\n\n**OpenAI Processing:**\n• Analyzes conversation history to determine if follow-up is appropriate\n• Selects template based on days overdue (7, 14, 21, 28-day intervals)\n• Customizes message tone from gentle reminders to more urgent requests\n• Returns verdict (true/false) and personalized email content\n\n**Templates**: 4 escalating follow-up templates with increasing urgency"
      },
      "typeVersion": 1
    },
    {
      "id": "sticky-note-4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2100,
        -150
      ],
      "parameters": {
        "width": 380,
        "height": 200,
        "content": "## 📧 STEP 4: Automated Email Delivery\n\nSends personalized follow-ups automatically:\n\n1. **Filter**: Only proceeds with emails that AI approved (verdict = true)\n2. **Gmail Draft/Send**: Creates professional follow-up emails with:\n   • Personalized subject lines\n   • Context-aware messaging\n   • Appropriate urgency level\n   • Professional but friendly tone\n\n**Result**: Clients receive timely, intelligent follow-ups that feel personally written"
      },
      "typeVersion": 1
    },
    {
      "id": "dd5aa5ef-6970-448a-9a2e-3c2851eb0da7",
      "name": "When clicking ‘Execute workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -40,
        20
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "949f0ab9-a966-4120-abe9-1d9ff44fa2b9",
      "name": "Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        220,
        150
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1oPx-jSljqW1YVpojZ9zPa10H7MoiQERIdrFESS6lUqo/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1oPx-jSljqW1YVpojZ9zPa10H7MoiQERIdrFESS6lUqo",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1oPx-jSljqW1YVpojZ9zPa10H7MoiQERIdrFESS6lUqo/edit?usp=drivesdk",
          "cachedResultName": "Smart Invoice System Example"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "EOibXIc4U8wcXyRR",
          "name": "YouTube"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "2de0297d-3ab6-432a-a096-1e028b8bbf4c",
      "name": "Edit Fields",
      "type": "n8n-nodes-base.set",
      "position": [
        440,
        150
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "c09a2d8d-29ed-45c9-a8fa-00bfcb913378",
              "name": "daysSinceSent",
              "type": "number",
              "value": "={{ $now.diffTo($json['Date Sent'], 'days').round() }}"
            },
            {
              "id": "07206597-825a-4b32-b37e-a7d801985f07",
              "name": "email",
              "type": "string",
              "value": "={{ $json.Email }}"
            },
            {
              "id": "66deeae3-de18-4b47-8cb0-48f6b0b7aa49",
              "name": "firstName",
              "type": "string",
              "value": "={{ $json[\"Client Name\"].split(\" \")[0] }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "974084da-f66e-4d79-953e-7eef1a00aace",
      "name": "Filter",
      "type": "n8n-nodes-base.filter",
      "position": [
        660,
        150
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "or",
          "conditions": [
            {
              "id": "d181237f-cfbe-458a-8d55-979db05e73f4",
              "operator": {
                "type": "number",
                "operation": "equals"
              },
              "leftValue": "={{ $json.daysSinceSent }}",
              "rightValue": 7
            },
            {
              "id": "97436f9c-bd1d-4b55-a13f-0db6d6413ac0",
              "operator": {
                "type": "number",
                "operation": "equals"
              },
              "leftValue": "={{ $json.daysSinceSent }}",
              "rightValue": 14
            },
            {
              "id": "2547e0b3-5774-4595-ab7c-11014e9326f4",
              "operator": {
                "type": "number",
                "operation": "equals"
              },
              "leftValue": "={{ $json.daysSinceSent }}",
              "rightValue": 21
            },
            {
              "id": "4e3f0df8-bb44-4c1f-a24d-4f6a1b6164d5",
              "operator": {
                "type": "number",
                "operation": "equals"
              },
              "leftValue": "={{ $json.daysSinceSent }}",
              "rightValue": 28
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "00175045-bccf-4f26-bf07-296d3d73e219",
      "name": "Loop Over Items",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        880,
        150
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "ed7af6f0-3b03-4a0e-bfe8-b5550a5a8b28",
      "name": "Gmail",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1100,
        75
      ],
      "webhookId": "aaf3c7f9-6ebb-49e3-8ca7-220c051cb6cc",
      "parameters": {
        "limit": 10,
        "simple": false,
        "filters": {
          "q": "=from:{{ $json.email }} OR to:{{ $json.email }}"
        },
        "options": {},
        "operation": "getAll"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "fhD8H5s1MrHGhcsj",
          "name": "Gmail account 4"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "e432f02f-7135-4133-bc2d-b3f6ca3cc11e",
      "name": "Aggregate",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        1320,
        75
      ],
      "parameters": {
        "options": {
          "mergeLists": true
        },
        "fieldsToAggregate": {
          "fieldToAggregate": [
            {
              "fieldToAggregate": "text"
            },
            {
              "fieldToAggregate": "headers.date"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "3ec2726d-4165-4960-8241-2b783bb98303",
      "name": "OpenAI",
      "type": "@n8n/n8n-nodes-langchain.openAi",
      "position": [
        1760,
        80
      ],
      "parameters": {
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1",
          "cachedResultName": "GPT-4.1"
        },
        "options": {
          "temperature": 0.7
        },
        "messages": {
          "values": [
            {
              "role": "system",
              "content": "You're a helpful, intelligent administrative assistant. You help me follow up with emails."
            },
            {
              "content": "=I am following up with overdue invoices by sending a series of templated emails. Your task is to read through a conversation history between me and the prospect and determine whether I can send the email as-is, or if there are some changes I need to make to the template.\n\nHere is the template:\n\n`\n{{ \n  $if(\n    $('Loop Over Items').item.json.daysSinceSent === 7, $json.followUpTemplateArray[0],\n    $if(\n      $('Loop Over Items').item.json.daysSinceSent === 14, $json.followUpTemplateArray[1],\n      $if(\n        $('Loop Over Items').item.json.daysSinceSent === 21, $json.followUpTemplateArray[2],\n        $if(\n          $('Loop Over Items').item.json.daysSinceSent === 28, $json.followUpTemplateArray[3],\n          null\n        )\n      )\n    )\n  ) \n}}`\n\nYou'll return your answer in JSON using this format:\n\n{\"verdict\":\"true or false\",\"emailTemplate\":\"modified email template with \\n in place of newlines\"}\n\nRules:\n- If we sent or received an email discussing the invoice in the last 72 hours, do not follow up. This would be annoying. If this occurs, just skip and return \"false\" for verdict.\n- If we did not send an email discussing the invoice in the last 72 hours, return \"true\" for verdict.\n- If we've discussed something that materially makes the followup seem strange (i.e repeats the same information, or says something out of context), modify the template to accommodate for this. Do not modify the template unless it's necessary to maintain a cohesive conversation.\n\nCurrent date:\n{{ $now }}\n\nConversation history:\n{{ $json.textWithDate }}"
            }
          ]
        },
        "jsonOutput": true
      },
      "credentials": {
        "openAiApi": {
          "id": "kRhXLl2JwVGc05AE",
          "name": "YouTube_Feb 4"
        }
      },
      "typeVersion": 1.8
    },
    {
      "id": "18090468-75ce-45ee-94d3-4c2630bfeea8",
      "name": "Edit Fields1",
      "type": "n8n-nodes-base.set",
      "position": [
        1540,
        75
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "b55b50fb-0d38-456d-b19c-0d8b3953487a",
              "name": "textWithDate",
              "type": "string",
              "value": "={{ $json.text.map((item,index) => $json.date[index] + '\\n' + item ) }}"
            },
            {
              "id": "84ec4118-bbec-4417-bf5a-c994fad40f3b",
              "name": "followUpTemplateArray",
              "type": "array",
              "value": "=[\"Hey {{ $('Loop Over Items').item.json.firstName }},\\n\\nHope you're well. Just checking in on that invoice I sent you earlier. Let me know if you need anything.\\n\\nThanks,\\nNick\",\"Hi {{ $('Loop Over Items').item.json.firstName }},\\n\\nFollowing up about the invoice. Am here to help if anything is unclear—shout anytime.\\n\\nThanks,\\nNick\",\"Hey {{ $('Loop Over Items').item.json.firstName }},\\n\\nThought I'd send another ping re: my last invoice. Let me know!\\n\\nBest,\\nNick\",\"Hi {{ $('Loop Over Items').item.json.firstName }},\\n\\nI know it's been a while since that last invoice, and it's not my intention to bother—just want to follow up and make sure I can close this out. Let me know if there's anything I can do to help.\\n\\nBest,\\nNick\"]"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "97fc0856-b9f9-4017-90bc-0d697261b997",
      "name": "Filter1",
      "type": "n8n-nodes-base.filter",
      "position": [
        2136,
        75
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "404354e4-5fb9-4cda-a033-176cf6d1ce45",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.message.content.verdict }}",
              "rightValue": "true"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "835c5f4c-19cd-4942-81bd-9f1c3bd4396a",
      "name": "Gmail1",
      "type": "n8n-nodes-base.gmail",
      "position": [
        2356,
        150
      ],
      "webhookId": "fc132765-0368-44f3-8b83-fbb7bb517396",
      "parameters": {
        "message": "={{ $json.message.content.emailTemplate }}",
        "options": {
          "sendTo": "={{ $('Loop Over Items').item.json.email }}"
        },
        "subject": "=Re: Invoice for {{ $('Loop Over Items').item.json.firstName }}",
        "resource": "draft"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "fhD8H5s1MrHGhcsj",
          "name": "Gmail account 4"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "9a82e86c-1988-4423-b2f7-8e42c447defb",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -40,
        320
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1.2
    }
  ],
  "active": false,
  "pinData": {
    "When clicking ‘Execute workflow’": [
      {
        "json": {}
      }
    ]
  },
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "a947a103-56ce-43e6-a647-1eeab4c72a58",
  "connections": {
    "Gmail": {
      "main": [
        [
          {
            "node": "Aggregate",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter": {
      "main": [
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Gmail1": {
      "main": [
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI": {
      "main": [
        [
          {
            "node": "Filter1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter1": {
      "main": [
        [
          {
            "node": "Gmail1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Aggregate": {
      "main": [
        [
          {
            "node": "Edit Fields1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Edit Fields": {
      "main": [
        [
          {
            "node": "Filter",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Edit Fields1": {
      "main": [
        [
          {
            "node": "OpenAI",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Google Sheets": {
      "main": [
        [
          {
            "node": "Edit Fields",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Loop Over Items": {
      "main": [
        [],
        [
          {
            "node": "Gmail",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Google Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking ‘Execute workflow’": {
      "main": [
        [
          {
            "node": "Google Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}