Student Absence Alerts & Attendance Tracking Automation

{
  "id": "2IRMZQsCHLw3s6RW",
  "meta": {
    "instanceId": "dd69efaf8212c74ad206700d104739d3329588a6f3f8381a46a481f34c9cc281",
    "templateCredsSetupCompleted": true
  },
  "name": "Student Absence Alerts & Attendance Tracking Automation",
  "tags": [],
  "nodes": [
    {
      "id": "50cc2f6f-e04f-4852-9b42-1247f72995ab",
      "name": "Daily Attendance Check - 10:30 AM",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        40,
        500
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 10,
              "triggerAtMinute": 30
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "9bb5b16b-c820-4c62-aec5-56588dc1fdb8",
      "name": "Read Today's Attendance",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        260,
        400
      ],
      "parameters": {
        "filters": {
          "fields": [
            {
              "value": "={{new Date().toISOString().split('T')[0]}}",
              "column": "Date"
            }
          ]
        },
        "resource": "worksheet",
        "workbook": {
          "__rl": true,
          "mode": "id",
          "value": "=attendance-workbook"
        }
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "64bec200-5b08-45be-ba98-0a810910dce5",
      "name": "Read Student Contacts",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        260,
        600
      ],
      "parameters": {
        "filters": {},
        "resource": "worksheet",
        "workbook": {
          "__rl": true,
          "mode": "id",
          "value": "=student-records-workbook"
        }
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "abfdb5ea-24d9-4f08-8150-0f8c7258f829",
      "name": "Process Absent Students",
      "type": "n8n-nodes-base.code",
      "position": [
        480,
        500
      ],
      "parameters": {
        "jsCode": "const attendanceRecords = $('Read Today\\'s Attendance').all();\nconst studentContacts = $('Read Student Contacts').all();\n\nconst absentStudents = [];\nconst today = new Date().toISOString().split('T')[0];\n\nfor (const record of attendanceRecords) {\n  const attendance = record.json;\n  \n  if (attendance.status === 'Absent' && !attendance.excused) {\n    const student = studentContacts.find(s => s.json.studentId === attendance.studentId);\n    \n    if (student) {\n      const studentData = student.json;\n      \n      // Calculate recent absence pattern (last 30 days)\n      const recentAbsences = attendanceRecords.filter(r => {\n        const recordDate = new Date(r.json.date);\n        const thirtyDaysAgo = new Date();\n        thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);\n        return r.json.studentId === attendance.studentId && \n               r.json.status === 'Absent' && \n               recordDate >= thirtyDaysAgo;\n      }).length;\n      \n      absentStudents.push({\n        studentId: attendance.studentId,\n        studentName: studentData.studentName,\n        className: studentData.className,\n        parentName: studentData.parentName,\n        parentEmail: studentData.parentEmail,\n        parentPhone: studentData.parentPhone,\n        absentDate: today,\n        recentAbsences: recentAbsences,\n        attendanceRate: Math.round(((30 - recentAbsences) / 30) * 100),\n        alertLevel: recentAbsences >= 5 ? 'High' : recentAbsences >= 3 ? 'Medium' : 'Low'\n      });\n    }\n  }\n}\n\nreturn absentStudents.map(item => ({ json: item }));"
      },
      "typeVersion": 2
    },
    {
      "id": "d4606ae7-8ac2-4648-bf59-caa601ac1b22",
      "name": "Prepare Absence Email",
      "type": "n8n-nodes-base.code",
      "position": [
        700,
        300
      ],
      "parameters": {
        "jsCode": "const studentData = $input.first().json;\n\nconst alertIcon = studentData.alertLevel === 'High' ? '🚨' : studentData.alertLevel === 'Medium' ? '⚠️' : 'ℹ️';\n\nconst emailSubject = `${alertIcon} Absence Alert: ${studentData.studentName} - ${studentData.absentDate}`;\nconst emailBody = `Dear ${studentData.parentName},\n\nWe are writing to inform you that ${studentData.studentName} was marked absent today (${studentData.absentDate}).\n\nAttendance Summary:\n- Student: ${studentData.studentName}\n- Class: ${studentData.className}\n- Absent Date: ${studentData.absentDate}\n- Recent Absences (30 days): ${studentData.recentAbsences}\n- Attendance Rate: ${studentData.attendanceRate}%\n- Alert Level: ${studentData.alertLevel}\n\n${studentData.alertLevel === 'High' ? \n  '⚠️ ATTENTION: Your child has exceeded the acceptable absence limit. Please contact the school office immediately to discuss this matter.' :\n  studentData.alertLevel === 'Medium' ? \n  '📋 NOTICE: Your child\\'s attendance needs attention. Please ensure regular attendance to avoid academic impact.' :\n  '📝 INFORMATION: This is a routine absence notification. Please ensure your child attends school regularly.'}\n\nIf this absence was due to illness or other valid reason, please contact the school office with appropriate documentation.\n\nFor attendance concerns, contact: [email protected] or (555) 123-4567\n\nBest regards,\nSchool Attendance Office`;\n\nreturn [{\n  json: {\n    to: studentData.parentEmail,\n    subject: emailSubject,\n    body: emailBody,\n    studentName: studentData.studentName,\n    parentName: studentData.parentName,\n    alertLevel: studentData.alertLevel\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "902d1502-2d1e-43d0-b41f-4b2ce68298ac",
      "name": "Prepare Absence SMS",
      "type": "n8n-nodes-base.code",
      "position": [
        700,
        700
      ],
      "parameters": {
        "jsCode": "const studentData = $input.first().json;\n\nconst alertEmoji = studentData.alertLevel === 'High' ? '🚨' : studentData.alertLevel === 'Medium' ? '⚠️' : '📋';\n\nconst whatsappMessage = `${alertEmoji} *Absence Alert*\\n\\nDear ${studentData.parentName},\\n\\n👤 Student: *${studentData.studentName}*\\n🏫 Class: ${studentData.className}\\n📅 Absent Date: ${studentData.absentDate}\\n\\n📊 *Attendance Summary:*\\n• Recent Absences (30 days): ${studentData.recentAbsences}\\n• Attendance Rate: ${studentData.attendanceRate}%\\n• Alert Level: *${studentData.alertLevel}*\\n\\n${studentData.alertLevel === 'High' ? \n  '🚨 *URGENT:* Please contact school office immediately regarding excessive absences.' :\n  studentData.alertLevel === 'Medium' ? \n  '⚠️ *ATTENTION:* Please ensure regular attendance to avoid academic impact.' :\n  '📝 *NOTICE:* Routine absence notification. Please ensure regular attendance.'}\n\nIf excused, please contact school office with documentation.\\n\\n📞 Contact: (555) 123-4567\\n📧 [email protected]\\n\\n_School Attendance Office_`;\n\nreturn [{\n  json: {\n    phone: studentData.parentPhone,\n    message: whatsappMessage,\n    studentName: studentData.studentName,\n    parentName: studentData.parentName,\n    alertLevel: studentData.alertLevel\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "14d0d2c1-9082-4ba6-ab6f-cccefb2b7b35",
      "name": "Send Absence Email",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        920,
        300
      ],
      "webhookId": "58f2f75e-edd7-48dc-b2d1-605f31c45e77",
      "parameters": {
        "text": "={{$json.body}}",
        "options": {},
        "subject": "={{$json.subject}}",
        "toEmail": "={{$json.to}}",
        "fromEmail": "[email protected]",
        "emailFormat": "text"
      },
      "credentials": {
        "smtp": {
          "id": "G1kyF8cSWTZ4vouN",
          "name": "SMTP -test"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "0fabc892-b164-4a89-bf2c-41474484e012",
      "name": "Send Absence WhatsApp",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        920,
        700
      ],
      "parameters": {
        "url": "https://graph.facebook.com/v17.0/FROM_PHONE_NUMBER_ID/messages",
        "options": {},
        "jsonBody": "={\n  \"messaging_product\": \"whatsapp\",\n  \"to\": \"{{ $json.phone }}\",\n  \"type\": \"text\",\n  \"text\": {\n    \"body\": \"{{ $json.message }}\"\n  }\n}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "headerParameters": {
          "parameters": [
            {
              "name": "Authorization",
              "value": "Bearer YOUR_ACCESS_TOKEN"
            },
            {
              "name": "Content-Type",
              "value": "application/json"
            }
          ]
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "d483ca47-cd98-423b-8a34-ef7360e18677",
      "name": "Generate Attendance Report",
      "type": "n8n-nodes-base.code",
      "position": [
        700,
        500
      ],
      "parameters": {
        "jsCode": "const absentStudents = $input.all();\nconst today = new Date().toISOString().split('T')[0];\n\nconst attendanceReport = {\n  date: today,\n  totalAbsences: absentStudents.length,\n  highAlerts: absentStudents.filter(s => s.json.alertLevel === 'High').length,\n  mediumAlerts: absentStudents.filter(s => s.json.alertLevel === 'Medium').length,\n  lowAlerts: absentStudents.filter(s => s.json.alertLevel === 'Low').length,\n  averageAttendanceRate: Math.round(\n    absentStudents.reduce((sum, s) => sum + s.json.attendanceRate, 0) / absentStudents.length\n  ),\n  studentsAtRisk: absentStudents.filter(s => s.json.recentAbsences >= 5).map(s => ({\n    studentName: s.json.studentName,\n    className: s.json.className,\n    recentAbsences: s.json.recentAbsences,\n    attendanceRate: s.json.attendanceRate\n  }))\n};\n\nreturn [{ json: attendanceReport }];"
      },
      "typeVersion": 2
    },
    {
      "id": "a1e55a21-3a65-4249-a613-703291679483",
      "name": "Save Attendance Report",
      "type": "n8n-nodes-base.microsoftExcel",
      "position": [
        920,
        500
      ],
      "parameters": {
        "options": {},
        "dataMode": "autoMap",
        "resource": "worksheet",
        "workbook": {
          "__rl": true,
          "mode": "id",
          "value": "=attendance-reports-workbook"
        },
        "operation": "append",
        "worksheet": {
          "__rl": true,
          "mode": "id",
          "value": "=w234t5yhyt43e"
        }
      },
      "credentials": {
        "microsoftExcelOAuth2Api": {
          "id": "jevPChvDpEJk6W9v",
          "name": "Microsoft Excel account - test"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "0e92d559-fcb7-4f32-83cb-645bdfd6c32e",
      "name": "Workflow Info",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -300,
        -40
      ],
      "parameters": {
        "color": 5,
        "width": 360,
        "height": 500,
        "content": "### **Absence Tracking Workflow**\n\n**🕙 Schedule:** Daily at 10:30 AM\n\n**📊 Process:**\n1. Check today's attendance records\n2. Identify absent students (unexcused)\n3. Calculate 30-day absence patterns\n4. Send alerts via Email & WhatsApp\n5. Generate daily attendance reports\n\n**🚦 Alert Levels:**\n- **High (🚨):** 5+ absences in 30 days\n- **Medium (⚠️):** 3-4 absences in 30 days  \n- **Low (📋):** 1-2 absences in 30 days\n\n**📈 Tracking:**\n- Attendance rates calculation\n- Pattern analysis\n- Risk identification\n- Historical reporting"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "36d284ba-f45d-484e-a7a3-cee26d0160c1",
  "connections": {
    "Prepare Absence SMS": {
      "main": [
        [
          {
            "node": "Send Absence WhatsApp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare Absence Email": {
      "main": [
        [
          {
            "node": "Send Absence Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Student Contacts": {
      "main": [
        [
          {
            "node": "Process Absent Students",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Process Absent Students": {
      "main": [
        [
          {
            "node": "Prepare Absence Email",
            "type": "main",
            "index": 0
          },
          {
            "node": "Prepare Absence SMS",
            "type": "main",
            "index": 0
          },
          {
            "node": "Generate Attendance Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Today's Attendance": {
      "main": [
        [
          {
            "node": "Process Absent Students",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Attendance Report": {
      "main": [
        [
          {
            "node": "Save Attendance Report",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Daily Attendance Check - 10:30 AM": {
      "main": [
        [
          {
            "node": "Read Today's Attendance",
            "type": "main",
            "index": 0
          },
          {
            "node": "Read Student Contacts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}