n8n Automated Business KPI Insights: GPT-4-Powered MSSQL & Multi-Channel Daily Reports

Daily auto-aggregate MSSQL, Google Analytics, Sheets data → calculate ROAS/CAC → AI-generated summary → WhatsApp/email push

Who

Founders, ops leads, or small teams who need daily visibility on revenue, CAC, and marketing efficiency

You get

Receive concise daily notifications with key metrics, risk alerts, and action recommendations without manual dashboards

You need

n8n · google-sheets · email · whatsapp

Steps

1. Configure data source nodes

   Set up four parallel data collection nodes in n8n: 1) Yesterday's Revenue (Microsoft SQL node) connects to your MSSQL database—replace the default query SELECT * from yourtable with your actual table and fields for yesterday's revenue; 2) Yesterday's Registered Users (Microsoft SQL node) similarly replace the query for new sign-ups; 3) Yesterday's Report (Google Analytics node) set propertyId to your own GA4 property ID with dateRange: yesterday; 4) Yesterday's Marketing Expenses (Google Sheets node) authenticate via service account and replace documentId and sheetName with your actual spend sheet. All nodes require pre-configured credentials in n8n's credential manager.

2. Add data validation and error alerts

   Add IF nodes after each data source (Check data, If, Condition for google report, Condition for marketing reports) to verify upstream data exists (using exists condition). If any source is missing, route to the Send Error Email node with the pre-filled error message. Replace toEmail/fromEmail with your actual operations email.

   Execution prompt · Source text。Send Error Email node text field

   Copy prompt

   Dear User,
   
   Daily business intelligence workflow failed due to missing input data.
   One or more required sources did not return valid results.
   Action: Review individual data source logs and resolve before next run.

3. Normalize and merge data

   Use Summarize/Set nodes to extract key fields: Get only Total Revenue Amount (sum TotalAmount), Get only Total Users (count email), Get Only Total Users (extract totalUsers), Get only Total Spend Amount (sum Ad Spend). Then merge all four branches with Merge all result, followed by Normalize the data (Code node) to unify into a single JSON object: {revenue, registeredUsers, totalUsers, adSpend}.

   Execution prompt · Source text。Normalize the data node jsCode field

   Copy prompt

   const result = {
     revenue: 0,
     registeredUsers: 0,
     totalUsers: 0,
     adSpend: 0
   };
   
   for (const item of items) {
     if (item.json.sum_TotalAmount !== undefined) {
       result.revenue = item.json.sum_TotalAmount;
     }
   
     if (item.json.count_email !== undefined) {
       result.registeredUsers = item.json.count_email;
     }
   
     if (item.json["Total Users"] !== undefined) {
       result.totalUsers = item.json["Total Users"];
     }
   
     if (item.json.sum_Ad_Spend !== undefined) {
       result.adSpend = item.json.sum_Ad_Spend;
     }
   }
   
   return [{ json: result }];

4. Calculate KPIs and apply business rules

   Connect to KPI Calculator (Code node) to compute ROAS (revenue/adSpend) and CAC (adSpend/registeredUsers), rounded to 2 decimals. Then connect to Business Decision Logic (Code node) to tag status: ROAS < 1 → high risk (marketing not generating returns); ROAS ≥ 3 → strong performance; CAC > 100 → medium risk (high acquisition cost). Output includes agentStatus, agentPriority, and insights array.

   Execution prompt · Source text。KPI Calculator node jsCode field

   Copy prompt

   const revenue = Number($json.revenue) || 0;
   const adSpend = Number($json.adSpend) || 0;
   const users = Number($json.registeredUsers) || 0;
   
   // Optional: yesterday values (if available later)
   const yesterdayRevenue = Number($json.yesterdayRevenue) || 0;
   const yesterdayUsers = Number($json.yesterdayUsers) || 0;
   
   // KPI calculations
   const ROAS = adSpend > 0 ? revenue / adSpend : 0;
   const CAC = users > 0 ? adSpend / users : 0;
   
   // Growth calculations
   const revenueGrowthPct =
     yesterdayRevenue > 0
       ? ((revenue - yesterdayRevenue) / yesterdayRevenue) * 100
       : 0;
   
   const userGrowthPct =
     yesterdayUsers > 0
       ? ((users - yesterdayUsers) / yesterdayUsers) * 100
       : 0;
   
   return [
     {
       json: {
         ...$json,
         ROAS: Number(ROAS.toFixed(2)),
         CAC: Number(CAC.toFixed(2)),
         //revenueGrowthPct: Number(revenueGrowthPct.toFixed(2)),
         //userGrowthPct: Number(userGrowthPct.toFixed(2))
       }
     }
   ];

   Execution prompt · Source text。Business Decision Logic node jsCode field

   Copy prompt

   const kpi = $json;
   
   let status = "normal";
   let priority = "low";
   let insights = [];
   
   if(kpi.ROAS < 1){
     status = "risk";
     priority = "high";
     insights.push("Marketing spend is not generating returns");
   }
   
   if(kpi.ROAS >= 3){
     insights.push("Marketing campaigns are perfoming very well");
   }
   
   if(kpi.CAC > 100){
     status = "risk";
     priority = "medium";
     insights.push("Customer acquision cost is high");
   }
   
   return [{
     json: {
       ...kpi,
       agentStatus: status,
       agentPriority: priority,
       insights
     }
   }];

5. Generate AI business summary

   Configure the AI Agent node with promptType: define. Connect the OpenAI Chat Model node (default gpt-4.1-mini, swappable to gpt-4.1 or gpt-4) as the language model, and Calculator as an optional tool. The AI Agent user prompt receives the upstream KPI JSON; the systemMessage sets the role as a founder's assistant, requiring output of: one-line summary, one risk (if any), one opportunity, one recommended action.

   Execution prompt · Source text。AI Agent node text field (user prompt)

   Copy prompt

   =Business KPIs:
   {{ JSON.stringify($json) }}
   
   Write:
   1. One-line business summary
   2. One risk (if any)
   3. One opportunity
   4. One recommended action

   Execution prompt · Source text。AI Agent node options.systemMessage field

   Copy prompt

   You are an AI business agent working for a company founder.
   
   Your job:
   - Analyze business KPIs
   - Explain what is happening
   - Decide if a notification is required
   - Keep the message short, clear, and actionable
   - Do not use technical terms
   - Do not exaggerate

6. Format and push multi-channel notifications

   Feed AI Agent output into a Merge node combined with raw KPI data, then distribute simultaneously to: 1) Send message (WhatsApp node) with textBody containing the AI summary plus four metrics; 2) Send Final Email (Email node) with subject Yesterday's Revenue Report. Both body templates are pre-filled—replace recipient contacts. The source also mentions extensibility to Slack/Telegram (add nodes manually).

   Execution prompt · Source text。Send message (WhatsApp) node textBody field

   Copy prompt

   ={{ $json.output }}
   
   Revenue :- {{ $json.revenue }}
   New Users :- {{ $json.registeredUsers }}
   Total Users :- {{ $json.totalUsers }}
   Total Ad Spend :- {{ $json.adSpend }}

   Execution prompt · Source text。Send Final Email node text field

   Copy prompt

   =Dear User,
   
   {{ $json.output }}
   
   Revenue :- {{ $json.revenue }}
   New Users :- {{ $json.registeredUsers }}
   Total Users :- {{ $json.totalUsers }}
   Total Ad Spend :- {{ $json.adSpend }}

You are setting up an n8n automated business KPI daily report workflow. Please confirm in this order: 1) n8n instance and six credentials ready (MSSQL, Google Analytics OAuth2, Google API service account, SMTP, OpenAI API, WhatsApp API); 2) all placeholder queries and document IDs replaced; 3) Schedule Trigger daily execution time set; 4) error notification email configured; 5) single-node data return tested. Recommend testing with sample data through the Normalize the data node before enabling the AI Agent.