Regroupez automatiquement tous les retours, avis et réclamations. Analysez les tendances pour piloter l’amélioration continue et renforcer la relation client.
Regroupez automatiquement tous les retours, avis et réclamations. Analysez les tendances pour piloter l’amélioration continue et renforcer la relation client.
{
"meta": {
"instanceId": "32d80f55a35a7b57f8e47a2ac19558d9f5bcec983a5519d9c29ba713ff4f12c7"
},
"nodes": [
{
"id": "fdd55253-5cb6-4b1f-9c93-6915f254f700",
"name": "Schedule Trigger",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-60,
-240
],
"parameters": {
"rule": {
"interval": [
{
"field": "months",
"triggerAtDayOfMonth": 5
}
]
}
},
"typeVersion": 1.2
},
{
"id": "c8d6064a-3fd7-478d-891c-6ade336daa1f",
"name": "YTD vs Prevoius Month1",
"type": "n8n-nodes-base.mySql",
"onError": "continueRegularOutput",
"position": [
640,
0
],
"parameters": {
"query": "SELECTn -- budget_data.fiscal_year AS `Year`,n -- budget_data.cost_center AS `Cost Center`,n budget_data.budget_group AS `Budget Group`,n-- budget_data.sort_order AS `Sort Order`,nn -- YTD Totals up to previous month (up to dynamic month)n SUM(budget_data.budget_amount) AS `Budget YTD`,n SUM(COALESCE(actual_data.actual_amount, 0)) AS `Actual YTD`,n SUM(COALESCE(actual_data.actual_amount, 0)) - SUM(budget_data.budget_amount) AS `Variance YTD`,nn -- Previous Month Totals Onlyn SUM(CASE WHEN budget_data.budget_month = {{ $('PreviousMonth').item.json.previousMonth }} THEN budget_data.budget_amount ELSE 0 END) AS `Budget PM`,n SUM(CASE WHEN budget_data.budget_month = {{ $('PreviousMonth').item.json.previousMonth }} THEN COALESCE(actual_data.actual_amount, 0) ELSE 0 END) AS `Actual PM`,n SUM(CASE WHEN budget_data.budget_month = {{ $('PreviousMonth').item.json.previousMonth }} THEN COALESCE(actual_data.actual_amount, 0) ELSE 0 END) -n SUM(CASE WHEN budget_data.budget_month = {{ $('PreviousMonth').item.json.previousMonth }} THEN budget_data.budget_amount ELSE 0 END) AS `Variance PM`nnFROMn (n SELECTn bg.budget_group_name AS budget_group,n bg.sort_order,n bgd.fiscal_year,n bgd.budget_month,n bgd.cost_center,n CAST(bgd.budget_amount AS DECIMAL(18,6)) AS budget_amountn FROMn `tabBudget Group Detail` bgdn JOINn `tabBudget Group` bg ON bg.name = bgd.parentn WHEREn bgd.fiscal_year = {{ $('PreviousMonth').item.json.year }}n AND bgd.budget_month <= {{ $('PreviousMonth').item.json.previousMonth }}n AND bgd.cost_center = '{{ $json.CostCenter }}'n ) AS budget_datannLEFT JOIN (n SELECTn acc.budget_group AS budget_group,n YEAR(gl.posting_date) AS fiscal_year,n MONTH(gl.posting_date) AS budget_month,n gl.cost_center,n SUM(n CASE n WHEN acc.root_type = 'Income' THEN gl.credit - gl.debitn WHEN acc.root_type = 'Expense' THEN gl.debit - gl.creditn ELSE 0n ENDn ) AS actual_amountn FROMn `tabGL Entry` gln JOINn `tabAccount` acc ON gl.account = acc.namen WHEREn acc.budget_group IS NOT NULLn AND acc.root_type IN ('Income', 'Expense')n AND gl.docstatus = 1n AND YEAR(gl.posting_date) = {{ $('PreviousMonth').item.json.year }}n AND MONTH(gl.posting_date) <= {{ $('PreviousMonth').item.json.previousMonth }}n AND gl.cost_center = '{{ $('Filter').item.json['Cost Center'] }}'n GROUP BYn acc.budget_group,n YEAR(gl.posting_date),n MONTH(gl.posting_date),n gl.cost_centern) AS actual_datanONn budget_data.budget_group = actual_data.budget_group ANDn budget_data.fiscal_year = actual_data.fiscal_year ANDn budget_data.budget_month = actual_data.budget_month ANDn budget_data.cost_center = actual_data.cost_centernnGROUP BYn budget_data.fiscal_year,n budget_data.cost_center,n budget_data.budget_group,n budget_data.sort_ordernnORDER BYn budget_data.cost_center,n budget_data.sort_order,n budget_data.budget_group;n",
"options": {},
"operation": "executeQuery"
},
"retryOnFail": false,
"typeVersion": 2.4
},
{
"id": "13102b1c-8a06-4a23-8174-75254bf783ac",
"name": "Loop Over Items",
"type": "n8n-nodes-base.splitInBatches",
"position": [
-40,
200
],
"parameters": {
"options": {}
},
"typeVersion": 3
},
{
"id": "da2a0b30-3df4-430c-8cac-cd9d735ce759",
"name": "CostCentrs",
"type": "n8n-nodes-base.set",
"position": [
1100,
-240
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "ac6bcf14-13e3-464d-b9cd-4adee56018d7",
"name": "Cost Center",
"type": "string",
"value": "={{ $json['Cost Center'] }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "7891d71c-18f8-4e07-aa30-f50bec10cef6",
"name": "Date & Time",
"type": "n8n-nodes-base.dateTime",
"position": [
260,
-240
],
"parameters": {
"options": {}
},
"typeVersion": 2
},
{
"id": "3e69dc27-0850-4978-bf10-e81ff575ec60",
"name": "PreviousMonth",
"type": "n8n-nodes-base.code",
"position": [
520,
-240
],
"parameters": {
"jsCode": "// Get the input date from the previous nodenconst inputDateStr = $input.first().json.currentDate;nconst inputDate = new Date(inputDateStr);nn// Move to the first day of the current monthninputDate.setDate(1);nn// Step back one day to land in the previous monthninputDate.setDate(0);nn// Extract previous month and yearnconst previousMonth = inputDate.getMonth() + 1; // Months are 0-basednconst year = inputDate.getFullYear(); // This will reflect the correct year, even in Januarynnreturn [n {n json: {n previousMonth: previousMonth.toString().padStart(2, '0'), // e.g., "01", "12"n year: year.toString() // e.g., "2024"n }n }n];n"
},
"typeVersion": 2
},
{
"id": "f6776225-39d2-4746-a90f-b4d1b12a66ee",
"name": "Selected Cost Center",
"type": "n8n-nodes-base.set",
"position": [
260,
220
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "c4a6c71a-0df4-49df-9068-f039ddf7d507",
"name": "CostCenter",
"type": "string",
"value": "={{ $json['Cost Center'] }}"
},
{
"id": "ade95f85-baa2-4f5d-a125-7360b17cf99b",
"name": "previousMonth",
"type": "string",
"value": "={{ $('PreviousMonth').item.json.previousMonth }}"
},
{
"id": "36c1d772-5bb7-47a6-81f9-1b70208e558b",
"name": "year",
"type": "string",
"value": "={{ $('PreviousMonth').item.json.year }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "1e23d876-21be-4d90-b5e4-38f3543a0c3b",
"name": "Get Cost Centers with Budgets",
"type": "n8n-nodes-base.mySql",
"position": [
800,
-240
],
"parameters": {
"query": "SELECT DISTINCTn budget_data.cost_center AS `Cost Center`nFROMn(n SELECTn bgd.cost_center,n bgd.fiscal_year,n bgd.budget_monthn FROMn `tabBudget Group Detail` bgdn JOINn `tabBudget Group` bg ON bg.name = bgd.parentn WHEREn bgd.fiscal_year = {{ $json.year }}n AND bgd.budget_month <= {{ $json.previousMonth }}n) AS budget_datannINNER JOINn(n SELECT DISTINCTn gl.cost_center,n YEAR(gl.posting_date) AS fiscal_year,n MONTH(gl.posting_date) AS budget_monthn FROMn `tabGL Entry` gln JOINn `tabAccount` acc ON gl.account = acc.namen WHEREn acc.budget_group IS NOT NULLn AND acc.root_type IN ('Income', 'Expense')n AND gl.docstatus = 1n AND YEAR(gl.posting_date) = {{ $json.year }}n AND MONTH(gl.posting_date) `<th>${col}</th>`).join('');nn// Build rowsnlet bodyHtml = rows.map(row => {n return `<tr>${headers.map(col => `<td>${row.json[col]}</td>`).join('')}</tr>`;n}).join('');nn// Combine into one tablenconst tableHtml = `n<table border="1" cellpadding="6" cellspacing="0" style="border-collapse: collapse">n <thead><tr>${headerHtml}</tr></thead>n <tbody>${bodyHtml}</tbody>n</table>n`;nnreturn [{ json: { table: tableHtml } }];n"
},
"typeVersion": 2
},
{
"id": "9a8bdb09-f9d4-4c4b-b1d5-dadb3c6ee567",
"name": "Merge",
"type": "n8n-nodes-base.merge",
"position": [
1380,
220
],
"parameters": {
"numberInputs": 4
},
"typeVersion": 3.1
},
{
"id": "d310db4d-183d-4f99-9bd0-863320d2db73",
"name": "Code",
"type": "n8n-nodes-base.code",
"position": [
1420,
580
],
"parameters": {
"jsCode": "const table1 = $input.first().json.table; // From the first input nodenconst table2 = $items("verticalPL")[0].json.table; // From the node named 'verticalPL'nconst table3 = $items("WIP1")[0].json.table; // From the node named 'WIP1'nconst table4 = $items("Employees1")[0].json.table; // From the node named 'Employees1'nnconst htmlOutput = `nnnn n body { font-family: Arial, sans-serif; font-size: 14px; color: #333; }n h2 { margin-top: 30px; }n table { border-collapse: collapse; width: 100%; margin-top: 10px; }n th, td { border: 1px solid #ccc; padding: 8px; text-align: right; }n th:first-child, td:first-child { text-align: left; }n thead { background-color: #f0f0f0; }n nnn <h2>ud83dudcca Financial Overview u2013 YTD & PM Summary</h2>n ${table1}nn <h2>ud83dudcca Financial Overview u2013 Vertical Profit & Loss</h2>n ${table2}nn <h2>ud83dudcca Financial Overview u2013 WIP Summary</h2>n ${table3}nn <h2>ud83dudc65 Employees in the Business Unit</h2>n ${table4}nnn`;nnreturn [{ json: { html: htmlOutput } }];n"
},
"typeVersion": 2
},
{
"id": "ba5e60fb-d5cc-4a5f-9cb6-07808f7c7021",
"name": "Microsoft Outlook2",
"type": "n8n-nodes-base.microsoftOutlook",
"position": [
1240,
920
],
"webhookId": "0cdef86a-9910-49aa-bdd3-1beecb260035",
"parameters": {
"subject": "=Business Performance Syncbricks",
"bodyContent": "={{ $json['Email Output'] }}",
"toRecipients": "amjid@amjidali.com",
"additionalFields": {
"bodyContentType": "html"
}
},
"typeVersion": 2
},
{
"id": "c3cdf21d-417f-420b-98f9-dfca33119c5a",
"name": "CostCenter",
"type": "n8n-nodes-base.code",
"position": [
920,
0
],
"parameters": {
"jsCode": "const rows = items;nn// Get column names from the first rownconst headers = Object.keys(rows[0].json);nn// Build header HTMLnlet headerHtml = headers.map(col => `<th>${col}</th>`).join('');nn// Build rowsnlet bodyHtml = rows.map(row => {n return `<tr>${headers.map(col => `<td>${row.json[col]}</td>`).join('')}</tr>`;n}).join('');nn// Combine into one tablenconst tableHtml = `n<table border="1" cellpadding="6" cellspacing="0" style="border-collapse: collapse">n <thead><tr>${headerHtml}</tr></thead>n <tbody>${bodyHtml}</tbody>n</table>n`;nnreturn [{ json: { table: tableHtml } }];n"
},
"typeVersion": 2
},
{
"id": "9d9fb099-5fca-4777-a753-f6791f37fd37",
"name": "WIP1",
"type": "n8n-nodes-base.code",
"position": [
900,
400
],
"parameters": {
"jsCode": "const rows = items;nn// Get column names from the first rownconst headers = Object.keys(rows[0].json);nn// Build header HTMLnlet headerHtml = headers.map(col => `<th>${col}</th>`).join('');nn// Build rowsnlet bodyHtml = rows.map(row => {n return `<tr>${headers.map(col => `<td>${row.json[col]}</td>`).join('')}</tr>`;n}).join('');nn// Combine into one tablenconst tableHtml = `n<table border="1" cellpadding="6" cellspacing="0" style="border-collapse: collapse">n <thead><tr>${headerHtml}</tr></thead>n <tbody>${bodyHtml}</tbody>n</table>n`;nnreturn [{ json: { table: tableHtml } }];n"
},
"typeVersion": 2
},
{
"id": "5a6626ed-c841-4fd7-9111-f686fcacaa37",
"name": "Employees",
"type": "n8n-nodes-base.mySql",
"onError": "continueRegularOutput",
"position": [
640,
600
],
"parameters": {
"query": "SELECTn -- e.payroll_cost_center AS `Payroll Cost Center`,n COUNT(*) AS `Total Employees`,n COUNT(CASE WHEN YEAR(e.date_of_joining) = YEAR(CURDATE()) THEN 1 END) AS `Joined This Year`,n COUNT(CASE WHEN YEAR(e.date_of_joining) = YEAR(CURDATE()) AND MONTH(e.date_of_joining) = MONTH(CURDATE()) THEN 1 END) AS `Joined This Month`nFROMn `tabEmployee` enWHEREn e.status = 'Active'n AND e.payroll_cost_center = '{{ $json.CostCenter }}'nGROUP BYn e.payroll_cost_center;n",
"options": {},
"operation": "executeQuery"
},
"retryOnFail": false,
"typeVersion": 2.4
},
{
"id": "bbfd2c19-9538-4106-8931-f65f0261d43c",
"name": "Employees1",
"type": "n8n-nodes-base.code",
"position": [
900,
600
],
"parameters": {
"jsCode": "const rows = items;nn// Get column names from the first rownconst headers = Object.keys(rows[0].json);nn// Build header HTMLnlet headerHtml = headers.map(col => `<th>${col}</th>`).join('');nn// Build rowsnlet bodyHtml = rows.map(row => {n return `<tr>${headers.map(col => `<td>${row.json[col]}</td>`).join('')}</tr>`;n}).join('');nn// Combine into one tablenconst tableHtml = `n<table border="1" cellpadding="6" cellspacing="0" style="border-collapse: collapse">n <thead><tr>${headerHtml}</tr></thead>n <tbody>${bodyHtml}</tbody>n</table>n`;nnreturn [{ json: { table: tableHtml } }];n"
},
"typeVersion": 2
},
{
"id": "b425da91-2faa-4063-93dd-4d997f7cd7eb",
"name": "Wait",
"type": "n8n-nodes-base.wait",
"position": [
1480,
1180
],
"webhookId": "83d7ae9a-e309-4bac-a0b4-5ff651e3afe3",
"parameters": {
"unit": "minutes"
},
"typeVersion": 1.1
},
{
"id": "5deead0c-d386-4b51-9b96-bd58e85244c0",
"name": "Financial Performance",
"type": "n8n-nodes-base.code",
"position": [
700,
920
],
"parameters": {
"jsCode": "let html = $input.first().json.output || '';nn// Remove ```html at the start and ``` at the end (if present)nhtml = html.trim().replace(/^```html\s*/i, '').replace(/```$/i, '');nnreturn [{n json: {n cleaned_html: htmln }n}];n"
},
"typeVersion": 2
},
{
"id": "a472032f-42ba-4cb2-9bf7-55314083833e",
"name": "Email Data",
"type": "n8n-nodes-base.set",
"position": [
940,
920
],
"parameters": {
"options": {},
"assignments": {
"assignments": [
{
"id": "b227e25f-99ce-4147-b22d-c2a6cabfcafa",
"name": "CostCenter",
"type": "string",
"value": "={{ $('Selected Cost Center').first().json.CostCenter }}n"
},
{
"id": "94e5a360-cbff-4498-bd75-98cafe08557b",
"name": "Email Output",
"type": "string",
"value": "={{ $json.cleaned_html }}"
},
{
"id": "20cd6408-ab44-4632-8f0a-967604f16a1c",
"name": "For the Month",
"type": "string",
"value": "=Month : {{ $('PreviousMonth').first().json.previousMonth }} - {{ $('PreviousMonth').first().json.year }}"
}
]
}
},
"typeVersion": 3.4
},
{
"id": "b7c1ae4d-cbe3-47fb-84fa-f34295f8dfee",
"name": "Calculator",
"type": "@n8n/n8n-nodes-langchain.toolCalculator",
"position": [
620,
1220
],
"parameters": {},
"typeVersion": 1
},
{
"id": "2e59b7b8-d8f6-4433-98c5-f637f6b5eaf4",
"name": "Business Performance AI Agent (Analyst)",
"type": "@n8n/n8n-nodes-langchain.agent",
"onError": "continueRegularOutput",
"position": [
300,
920
],
"parameters": {
"text": "=You are a Business Performance Analyst Expert in Financial Management of Syncbricks LLC, you willbe given an input to Analyze the Financial Performance of syncbricks which is an AI and Automation Company.nThe sections Given to you will be belownn1. Financial Overview u2013 YTD & PM Summary (whis is income and expense of the Company, this data doesn't include the calculation of Gross Profit and Net Profit that you must do. This should be used together in one section only don't seperate them)n2. Financial Overview u2013 Vertical Profit & Loss (This containers the Verticials within the Cost Center, these are in fact the Sub Business Unit, you don't need to calculate anything but the information is for analysis to see how the Verticals within the Business Unit Perfored )n3. Financial Overview u2013 WIP Summary (This is the Projects Summary about how many projects are currently open and work is the WIP - Work in Process of the Project, the WIP figure should be added for Profit and Loss Statement Under Proejct Reveneu as expected WIP)n4. Employees in the Business Unit (These will be the number of Employees who are directly working in this BU, which means these employees are only serving the Company, other staff which are in back office support are not included in this. )n*** Your Role ***nnYour Role is to Prepare a comprehensive Report : n1. Executive Summary : Tell how was the overall performance of the Business Unit.n2. Analayze the Financial Overview - YTD together with PM Summary (Which is Previous Month) which is previous month Income and Expenses you must use Proper Strcuture of Financial Statement with Budget Vs Actuals (follow same input already given). nnYou must use the Section that will be shared with you in detail that says "Financial Overview u2013 YTD & PM Summary " and from there you must ensure to create sections based on best Practices of Performance Analysis that should include;nnSection: Revenuen-Projects - Revenuen-Trading - Revenuen-Service - RevenuenNet Sales (Total)nSection: Project Work in ProcessnWIP (Revenue to Book)nTotal: Gross SalenSection: Cost of SalenProjects - COGSnTrading - COGSnService - COGSnTotal Cost of SalesnGross ProfitnOther IncomenGross IncomennSection: Indirect Cost (here you must all the detail of expenses from "Financial Overview u2013 YTD & PM Summary"nThen you must calculatenProfit / (Loss) before taxnNP % to RevenuennUse the calculator tool to ensure precise calculations.nnn- Don't add decimals give format in currency but don't add symbol.nUse the standard P&L Format where you should first use Sale, then Cost of Sale and GP, then use Indirect Expenses and then Net Profile. Ensure to calculate the Perentages as well.n3. Provide the Summary of the Verticials Performance with their Profit and Loss and Percetage and Total of all Verticlalsn4. Current Project Progress.n5. Employees Summary, Number of Employees, How many joined in this Year and what is per Eployee Revenue and Gross Profit nnAnalyze Overall Performance of the Company and provide the Business Managers an Overview of what should be done next. Calculate per employee profit as well and suggest what is the performance overallnn**Tools**nUse calculator tool to do all calculations for accurate calculationsnn** Formatting and Output **nnGive output in html format fully responsive in with beauty added using CSS, ready to be shared with all possible tables, in hightly attracrtive format, headings, add colors green for good, red for bad, organge for acceptable and so on, also add remarks to show how the performance was, enusre to response as if you were as an Expert in Analyzing the Bususiness Performance. Don't add any other infomration or symbols which are not part of html. Don't give additional message saying ok, I will do and so on.nAlign Text in table on left and numbers on rightnNumber formatting : ##,###nnnHere is the datannMonth : {{ $('PreviousMonth').first().json.previousMonth }}nYear : {{ $('PreviousMonth').first().json.year }}nn{{ $json.html }}nn",
"options": {},
"promptType": "define"
},
"typeVersion": 1.8
},
{
"id": "3ac7b91e-ee61-4b40-9d2d-76d7916479ee",
"name": "Think",
"type": "@n8n/n8n-nodes-langchain.toolThink",
"position": [
460,
1220
],
"parameters": {},
"typeVersion": 1
},
{
"id": "97bac1cb-8271-4169-98cf-6ea5b06ef5db",
"name": "Google Gemini Chat Model",
"type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
"position": [
220,
1220
],
"parameters": {
"options": {},
"modelName": "models/gemini-2.5-pro-exp-03-25"
},
"typeVersion": 1
},
{
"id": "27f40dc7-61b9-4e09-9b6d-6bb9d16c38c3",
"name": "Departments ",
"type": "n8n-nodes-base.mySql",
"onError": "continueRegularOutput",
"position": [
640,
200
],
"parameters": {
"query": "SELECTn -- gl.vertical AS `Vertical`,nn -- Total Income (based on root_type)n ROUND(SUM(CASE WHEN acc.root_type = 'Income' THEN gl.credit - gl.debit ELSE 0 END), 0) AS `Total Income`,nn -- Total Expenses (based on root_type)n ROUND(SUM(CASE WHEN acc.root_type = 'Expense' THEN gl.debit - gl.credit ELSE 0 END), 0) AS `Total Expenses`,nn -- Profit or Loss = Income - Expensesn ROUND(SUM(n CASE n WHEN acc.root_type = 'Income' THEN gl.credit - gl.debitn WHEN acc.root_type = 'Expense' THEN -1 * (gl.debit - gl.credit)n ELSE 0n ENDn ), 0) AS `Profit or Loss`nnFROMn `tabGL Entry` glnJOINn `tabAccount` acc ON gl.account = acc.namennWHEREn acc.root_type IN ('Income', 'Expense')n AND gl.docstatus = 1n AND gl.cost_center = '{{ $("Selected Cost Center").item.json.CostCenter }}'n AND MONTH(gl.posting_date) = {{ $("Selected Cost Center").item.json.previousMonth }}n AND YEAR(gl.posting_date) = {{ $("Selected Cost Center").item.json.year }}nnGROUP BYn gl.verticaln",
"options": {},
"operation": "executeQuery"
},
"retryOnFail": false,
"typeVersion": 2.4
},
{
"id": "b2a3ec7d-d400-4d1c-877d-f49f67ad742f",
"name": "Projects",
"type": "n8n-nodes-base.mySql",
"onError": "continueRegularOutput",
"position": [
620,
380
],
"parameters": {
"query": "SELECTn -- p.cost_center AS `Cost Center`,n COUNT(DISTINCT p.name) AS `Projects`,nn FORMAT(SUM(p.contract_value), 0) AS `Contract Value`,n FORMAT(SUM(p.total_opening_revenue), 0) AS `Opening Revenue`,n FORMAT(SUM(p.total_opening_cost), 0) AS `Opening Cost`,nn -- New Revenue (GL)n IFNULL((n SELECT SUM(gl.credit - gl.debit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Income' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH)n ), 0) AS `New Revenue`,nn -- New Cost (GL)n IFNULL((n SELECT SUM(gl.debit - gl.credit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Expense' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH)n ), 0) AS `New Cost`,nn -- Actual Revenuen FORMAT(n SUM(p.total_opening_revenue) +n IFNULL((n SELECT SUM(gl.credit - gl.debit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Income' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH)n ), 0), 0n ) AS `Actual Revenue`,nn -- Actual Costn FORMAT(n SUM(p.total_opening_cost) +n IFNULL((n SELECT SUM(gl.debit - gl.credit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Expense' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH)n ), 0), 0n ) AS `Actual Cost`,nn -- Invoice %n CONCAT(ROUND((n (n SUM(p.total_opening_revenue) +n IFNULL((n SELECT SUM(gl.credit - gl.debit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Income' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH)n ), 0)n ) / NULLIF(SUM(p.contract_value), 0) * 100n ), 0), '%') AS `Invoice %`,nn -- Cost %n CONCAT(ROUND((n (n SUM(p.total_opening_cost) +n IFNULL((n SELECT SUM(gl.debit - gl.credit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Expense' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH)n ), 0)n ) / NULLIF(SUM(p.budgeted_project_cost), 0) * 100n ), 0), '%') AS `Cost %`,nn -- WIP Calculationn FORMAT(n CASEn WHEN SUM(p.budgeted_project_cost) = 0 THEN 0n WHEN (n SUM(p.total_opening_cost) +n IFNULL((n SELECT SUM(gl.debit - gl.credit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Expense' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date SUM(p.budgeted_project_cost)n THENn SUM(p.contract_value) -n (SUM(p.total_opening_revenue) +n IFNULL((n SELECT SUM(gl.credit - gl.debit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Income' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH)n ), 0))n ELSEn (SUM(p.contract_value) * (n (SUM(p.total_opening_cost) +n IFNULL((n SELECT SUM(gl.debit - gl.credit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Expense' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH)n ), 0)) / NULLIF(SUM(p.budgeted_project_cost), 0)n )) -n (SUM(p.total_opening_revenue) +n IFNULL((n SELECT SUM(gl.credit - gl.debit)n FROM `tabGL Entry` gln JOIN `tabAccount` acc ON acc.name = gl.accountn WHERE gl.docstatus = 1 AND acc.root_type = 'Income' AND acc.is_group = 0n AND gl.project IN (SELECT name FROM `tabProject` WHERE cost_center = p.cost_center AND status = 'Open')n AND gl.posting_date <= LAST_DAY(CURDATE() - INTERVAL 1 MONTH)n ), 0))n END,n 0) AS `WIP`nnFROMn `tabProject` pnWHEREn p.cost_center = '{{ $json.CostCenter }}'n AND p.status = 'Open'nGROUP BYn p.cost_center;n",
"options": {},
"operation": "executeQuery"
},
"retryOnFail": false,
"typeVersion": 2.4
},
{
"id": "a1c5f474-dc92-47e7-bf59-9cca756245cf",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1260,
-20
],
"parameters": {
"width": 1120,
"height": 1200,
"content": "## Key Sections of n8n Workflownn**ud83dudfe2 Schedule Trigger**n- Triggers the automation on the 5th of every month.n- Sets the cadence for monthly reporting.nn**ud83dudcc5 Date & Time + ud83euddee PreviousMonth**n- Captures the current date and derives the previous month/year for dynamic filtering.n- Used throughout all SQL queries for monthly slicing.nn**ud83dudcca Get Cost Centers with Budgets**n- Dynamically fetches all cost centers that have relevant budget and GL data for the selected period.n- Ensures only active, relevant divisions are processed.nn**ud83dudd0d Filter**n- Narrows the analysis to a specific Cost Center (e.g., "AI DEPARTMENT") during testing or preview.nn**ud83dudd01 Loop Over Items**n- Iterates over all fetched Cost Centers to individually generate reports for each business unit.n- Powers multi-division reporting automation.nn**ud83euddfe YTD vs Previous Month1**n- Performs detailed financial comparison between Year-To-Date and Previous Month.n- Outputs revenue, expenses, and variance figures.nn**ud83cudfe2 Departments + ud83euddf1 verticalPL**n- Analyzes financial performance by sub-divisions (Verticals) within the cost center.n- Data is transformed into an HTML table for final report.nn**ud83euddf1 Projects + ud83euddee WIP1**n- Gathers project status and WIP metrics.n- Calculates % invoiced, cost % used, and revenue recognition stats.nn**ud83dudc65 Employees + ud83dudcca Employees1**n- Counts total employees, joined this month/year for the selected cost center.n- Used to calculate per-employee KPIs.nn**ud83dudd17 Merge + ud83euddfe Code**n- Combines all HTML tables into a single responsive report with visual formatting.n- Clean, structured layout for email and review.nn**ud83dudca1 Business Performance AI Agent (Analyst)**n- Uses Google Gemini 2.5 Pro to analyze financial HTML output.n- Executes structured analysis and generates business insights, executive summary, profitability, and recommendations.nn**ud83dudce9 Email Data + Microsoft Outlook**n- Sends the final HTML report via email.n- Ready for business manager distribution with personalized message.nn**u23f3 Wait**n- Enables looping and batch control across multiple cost centers.n- Prevents overloading or email spamming.nn**ud83eudde0 Think + Calculator**n- Used by the AI Agent to "reason" and perform precise financial computations.n- Ensures accuracy in gross/net profit, percentages, and insights."
},
"typeVersion": 1
},
{
"id": "59500949-a8b9-4287-b6f7-be49e58e1842",
"name": "Sticky Note1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1960,
580
],
"parameters": {
"color": 4,
"width": 680,
"height": 560,
"content": "## SQL Query Nodes n(e.g., YTD vs Previous Month, Projects, Employees, Departments)nThese nodes fetch structured financial data (like revenue, expenses, WIP, employee count) directly from your database (e.g., ERPNext on MySQL).nnYou can use any database (MySQL, PostgreSQL, MSSQL, etc.) as long as the schema is mapped accordingly.nnTo generate these queries faster, use ChatGPT or any AI chat tool:nud83dudc49 Just describe what you want (e.g., u201cmonthly budget vs actual grouped by cost centeru201d), and it will write SQL for you.nnYou can also replace these SQL nodes with any other data source:nnExcel/CSV File (via Google Drive or n8n Read Binary File)nnGoogle SheetsnnAirtablennREST APIsnnInternal appsnnu2705 The key is to produce the same structured data format expected by the next steps in the workflow."
},
"typeVersion": 1
},
{
"id": "02f76680-0bbf-408f-826e-daef5e6e9b09",
"name": "Sticky Note11",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1960,
0
],
"parameters": {
"color": 4,
"width": 675,
"height": 536,
"content": "## Developed by Amjid AlinnThank you for using this workflow template. It has taken me countless hours of hard work, research, and dedication to develop, and I sincerely hope it adds value to your work.nnIf you find this template helpful, I kindly ask you to consider supporting my efforts. Your support will help me continue improving and creating more valuable resources.nnYou can contribute via PayPal here:nnhttp://paypal.me/pmptrainingnnFor Full Course about ERPNext or Automation using AI follow below linknnhttp://lms.syncbricks.comnnAdditionally, when sharing this template, I would greatly appreciate it if you include my original information to ensure proper credit is given.nnThank you for your generosity and support!nEmail : amjid@amjidali.comnhttps://linkedin.com/in/amjidalinhttps://syncbricks.comnhttps://youtube.com/@syncbricks"
},
"typeVersion": 1
}
],
"pinData": {},
"connections": {
"Code": {
"main": [
[
{
"node": "HTML",
"type": "main",
"index": 0
}
]
]
},
"HTML": {
"main": [
[
{
"node": "Business Performance AI Agent (Analyst)",
"type": "main",
"index": 0
}
]
]
},
"WIP1": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 2
}
]
]
},
"Wait": {
"main": [
[
{
"node": "Loop Over Items",
"type": "main",
"index": 0
}
]
]
},
"Merge": {
"main": [
[
{
"node": "Code",
"type": "main",
"index": 0
}
]
]
},
"Think": {
"ai_tool": [
[
{
"node": "Business Performance AI Agent (Analyst)",
"type": "ai_tool",
"index": 0
}
]
]
},
"Filter": {
"main": [
[
{
"node": "Loop Over Items",
"type": "main",
"index": 0
}
]
]
},
"Projects": {
"main": [
[
{
"node": "WIP1",
"type": "main",
"index": 0
}
]
]
},
"Employees": {
"main": [
[
{
"node": "Employees1",
"type": "main",
"index": 0
}
]
]
},
"Calculator": {
"ai_tool": [
[
{
"node": "Business Performance AI Agent (Analyst)",
"type": "ai_tool",
"index": 0
}
]
]
},
"CostCenter": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"CostCentrs": {
"main": [
[
{
"node": "Filter",
"type": "main",
"index": 0
}
]
]
},
"Email Data": {
"main": [
[
{
"node": "Microsoft Outlook2",
"type": "main",
"index": 0
}
]
]
},
"Employees1": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 3
}
]
]
},
"verticalPL": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 1
}
]
]
},
"Date & Time": {
"main": [
[
{
"node": "PreviousMonth",
"type": "main",
"index": 0
}
]
]
},
"Departments ": {
"main": [
[
{
"node": "verticalPL",
"type": "main",
"index": 0
}
]
]
},
"PreviousMonth": {
"main": [
[
{
"node": "Get Cost Centers with Budgets",
"type": "main",
"index": 0
}
]
]
},
"Loop Over Items": {
"main": [
[],
[
{
"node": "Selected Cost Center",
"type": "main",
"index": 0
}
]
]
},
"Schedule Trigger": {
"main": [
[
{
"node": "Date & Time",
"type": "main",
"index": 0
}
]
]
},
"Microsoft Outlook2": {
"main": [
[
{
"node": "Wait",
"type": "main",
"index": 0
}
]
]
},
"Selected Cost Center": {
"main": [
[
{
"node": "YTD vs Prevoius Month1",
"type": "main",
"index": 0
},
{
"node": "Departments ",
"type": "main",
"index": 0
},
{
"node": "Projects",
"type": "main",
"index": 0
},
{
"node": "Employees",
"type": "main",
"index": 0
}
]
]
},
"Financial Performance": {
"main": [
[
{
"node": "Email Data",
"type": "main",
"index": 0
}
]
]
},
"YTD vs Prevoius Month1": {
"main": [
[
{
"node": "CostCenter",
"type": "main",
"index": 0
}
]
]
},
"Google Gemini Chat Model": {
"ai_languageModel": [
[
{
"node": "Business Performance AI Agent (Analyst)",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"Get Cost Centers with Budgets": {
"main": [
[
{
"node": "CostCentrs",
"type": "main",
"index": 0
}
]
]
},
"Business Performance AI Agent (Analyst)": {
"main": [
[
{
"node": "Financial Performance",
"type": "main",
"index": 0
}
]
]
}
}
}