{
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "Follow-up emails from a Google Sheet with reply tracking",
  "tags": [],
  "nodes": [
    {
      "id": "263d7a2a-0185-47b5-a58b-c00a0f756dcc",
      "name": "Daily Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -80,
        -485
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "cronExpression",
              "expression": "0 9 * * *"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "d5b54189-9981-4b23-afa1-d8c2007ce94d",
      "name": "Fetch Contact Data",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        140,
        -485
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "YOUR_SHEET_TAB_NAME"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "YOUR_GOOGLE_SHEET_ID"
        },
        "authentication": "oAuth2"
      },
      "typeVersion": 4.6
    },
    {
      "id": "f2525c0e-4f9b-4430-a4f1-d21c18977095",
      "name": "Iterate Contacts",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        360,
        -485
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "55ff15d6-15ed-4197-b2b4-98df8695f98c",
      "name": "Determine Follow-Up Stage",
      "type": "n8n-nodes-base.if",
      "onError": "continueErrorOutput",
      "position": [
        580,
        -560
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "8533f0cb-5664-4ba3-ac35-46587194560d",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.Reply }}",
              "rightValue": "no"
            }
          ]
        }
      },
      "typeVersion": 2.2,
      "alwaysOutputData": true
    },
    {
      "id": "11f2e1b6-0f21-4d5b-aced-a4c45e29f970",
      "name": "Route by Follow-Up Stage",
      "type": "n8n-nodes-base.switch",
      "position": [
        800,
        -560
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "bde52b5f-b028-4849-acd0-fd976bbae44d",
                    "operator": {
                      "type": "number",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json['Follow-up'] }}",
                    "rightValue": 6
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "1a69881c-21ce-4cb7-9c17-f5ed0e5dd6ac",
                    "operator": {
                      "type": "number",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json['Follow-up'] }}",
                    "rightValue": 7
                  }
                ]
              }
            }
          ]
        },
        "options": {}
      },
      "typeVersion": 3.2
    },
    {
      "id": "2dbd64d3-ec48-4992-a25e-f665d4f04001",
      "name": "Send Follow-Up Email 1",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1020,
        -660
      ],
      "parameters": {
        "sendTo": "={{ $json['User Email'] }}",
        "message": "YOUR_FOLLOW_UP_1_MESSAGE",
        "options": {
          "appendAttribution": false
        },
        "subject": "YOUR_FOLLOW_UP_1_SUBJECT",
        "emailType": "text",
        "authentication": "oAuth2"
      },
      "typeVersion": 2.1
    },
    {
      "id": "c2ed8d92-11c0-49df-87c2-191917a18a9b",
      "name": "Send Follow-Up Email 2",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1020,
        -460
      ],
      "parameters": {
        "sendTo": "={{ $json['User Email'] }}",
        "message": "YOUR_FOLLOW_UP_2_MESSAGE",
        "options": {
          "appendAttribution": false
        },
        "subject": "YOUR_FOLLOW_UP_2_SUBJECT",
        "emailType": "text",
        "authentication": "oAuth2"
      },
      "typeVersion": 2.1
    },
    {
      "id": "715c5558-6bdb-4b8b-afc1-ae62bbf175f0",
      "name": "Update Sheet with Follow-Up Status",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1240,
        -460
      ],
      "parameters": {
        "columns": {
          "value": {
            "Reply": "={{ $('Route by Follow-Up Stage').item.json.Reply }}",
            "Follow-up": "={{ $('Route by Follow-Up Stage').item.json['Follow-up'] + 1}}",
            "Send Mail": "=yes (Follow-up = {{ $('Route by Follow-Up Stage').item.json['Follow-up'] }}) ",
            "User Name": "={{ $('Route by Follow-Up Stage').item.json['User Name'] }}",
            "Inquiry ID": "={{ $('Route by Follow-Up Stage').item.json['Inquiry ID'] }}",
            "User Email": "={{ $('Route by Follow-Up Stage').item.json['User Email'] }}",
            "Follow-up Type": "Email",
            "Submission Date": "={{ new Date($now).toISOString().split('T')[0] }}",
            "Next Follow-up Date": "={{ new Date(Date.now() + 3 * 24 * 60 * 60 * 1000).toISOString().split('T')[0] }}"
          },
          "schema": [
            {
              "id": "User Name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "User Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "User Email",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "User Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Inquiry ID",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Inquiry ID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Reply",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Reply",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Follow-up",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Follow-up",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Send Mail",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Send Mail",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Follow-up Type",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Follow-up Type",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Communication Mode",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Communication Mode",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Status",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Comment",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Comment",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Requirement Doc",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Requirement Doc",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Workflow Diagram",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Workflow Diagram",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Test Cases",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Test Cases",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Call Schedule",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Call Schedule",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Submission Date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Submission Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Next Follow-up Date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Next Follow-up Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "row_number",
              "type": "string",
              "display": true,
              "removed": true,
              "readOnly": true,
              "required": false,
              "displayName": "row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "User Name"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "YOUR_SHEET_TAB_NAME"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "YOUR_GOOGLE_SHEET_ID"
        },
        "authentication": "oAuth2"
      },
      "typeVersion": 4.6
    },
    {
      "id": "009b187a-a8b6-4748-bc9a-2e7dcd69f233",
      "name": "Check Email Responses",
      "type": "n8n-nodes-base.emailReadImap",
      "position": [
        -80,
        -200
      ],
      "parameters": {
        "options": {
          "customEmailConfig": "[\"UNSEEN\", [\"SUBJECT\", \"YOUR_SUBJECT_KEYWORD\"]]"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "91e87156-bd84-4df5-ad9e-11089af50c02",
      "name": "Update Sheet with Response",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        140,
        -200
      ],
      "parameters": {
        "columns": {
          "value": {
            "reply": "=yes",
            "user email": "={{ $json.from }}"
          },
          "schema": [
            {
              "id": "user name",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "user name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "user email",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "user email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "reply",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "reply",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "followup",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "followup",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Send mail",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Send mail",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "row_number",
              "type": "string",
              "display": true,
              "removed": true,
              "readOnly": true,
              "required": false,
              "displayName": "row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "user email"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "YOUR_SHEET_TAB_NAME"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "YOUR_GOOGLE_SHEET_ID"
        },
        "authentication": "oAuth2"
      },
      "typeVersion": 4.6
    },
    {
      "id": "1dd2ad29-a4cb-43cf-b93d-274d1f74bc1c",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        -100,
        -960
      ],
      "parameters": {
        "color": 4,
        "width": 460,
        "height": 230,
        "content": "## 1. Daily follow-up flow\n\nAt 9 AM the sheet is read and its rows are processed one by one. A row continues only if Reply is exactly no and Follow-up is 6 or 7. Follow-up 6 gets email 1, Follow-up 7 gets email 2, then the row is updated.\n\nThis flow sends follow-ups only. It does not send a first email."
      }
    },
    {
      "id": "6455bef6-a901-45e2-8740-a0904c6f94d7",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        380,
        -960
      ],
      "parameters": {
        "color": 3,
        "width": 440,
        "height": 230,
        "content": "## 2. How the loop continues\n\nThe loop moves to the next row only after Update Sheet with Follow-Up Status. A row that fails the If or the Switch ends the run, and the rows after it are not processed."
      }
    },
    {
      "id": "1443ea57-efd8-43b4-875e-bb82c1ce39a1",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        -100,
        -120
      ],
      "parameters": {
        "color": 5,
        "width": 460,
        "height": 230,
        "content": "## 3. Reply tracking\n\nCheck Email Responses watches your mailbox over IMAP for unread mail whose subject contains your keyword. Update Sheet with Response then sets reply to yes for the sender. It runs on its own, not at 9 AM."
      }
    },
    {
      "id": "c8edf2e3-0312-4c64-8c60-159b0ad76da6",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        380,
        -120
      ],
      "parameters": {
        "color": 6,
        "width": 440,
        "height": 330,
        "content": "## Sheet headers used by the file\n\nUser Name, User Email, Inquiry ID, Reply, Follow-up, Send Mail, Follow-up Type, Submission Date, Next Follow-up Date.\n\nThe reply node uses lowercase user email and reply. Make sure your headers match what each node writes, including capitals.\n\nReply must contain no for a row to be followed up."
      }
    },
    {
      "id": "06f3d6c0-fd01-4924-a3ae-0e2ff1a45fce",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        -100,
        240
      ],
      "parameters": {
        "color": 7,
        "width": 460,
        "height": 330,
        "content": "## Before you run it\n\nReplace every YOUR_ value: sheet ID, tab name, both subjects, both messages and the subject keyword. Connect your own Google Sheets, Gmail and IMAP credentials.\n\nTest with rows that contain your own addresses. The workflow is saved inactive.\n\nThe messages are plain text and contain no unsubscribe line. Add one."
      }
    }
  ],
  "settings": {
    "executionOrder": "v1"
  },
  "connections": {
    "Daily Trigger": {
      "main": [
        [
          {
            "node": "Fetch Contact Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Iterate Contacts": {
      "main": [
        [],
        [
          {
            "node": "Determine Follow-Up Stage",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Contact Data": {
      "main": [
        [
          {
            "node": "Iterate Contacts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Email Responses": {
      "main": [
        [
          {
            "node": "Update Sheet with Response",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send Follow-Up Email 2": {
      "main": [
        [
          {
            "node": "Update Sheet with Follow-Up Status",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send Follow-Up Email 1": {
      "main": [
        [
          {
            "node": "Update Sheet with Follow-Up Status",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Route by Follow-Up Stage": {
      "main": [
        [
          {
            "node": "Send Follow-Up Email 1",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Send Follow-Up Email 2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Determine Follow-Up Stage": {
      "main": [
        [
          {
            "node": "Route by Follow-Up Stage",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Update Sheet with Follow-Up Status": {
      "main": [
        [
          {
            "node": "Iterate Contacts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}