ur based on your timezone requirements.
Step 2: Set Up Data Ingestion
Add a Google Sheets node. Configure it to read from your metrics sheet.
- Document ID: Your spreadsheet ID.
- Range:
A:E (or your specific data range).
- Authentication: Use OAuth2 credentials with read access.
Step 3: Implement KPI Logic
Add a Code node. This node replaces the manual math. It ingests the array of rows, computes totals, averages, and trends, and outputs a single JSON object containing the report payload.
// n8n Code Node: KPI Aggregator
// Input: Array of rows from Google Sheets
// Output: Single object with computed metrics
const inputData = items.map(item => item.json);
// Single-pass aggregation for efficiency
const aggregation = inputData.reduce((acc, row) => {
// Normalize revenue; handle missing or invalid values
const revenue = parseFloat(row.revenue_amount) || 0;
const isTransaction = revenue > 0;
acc.grossRevenue += revenue;
if (isTransaction) acc.transactionVolume++;
return acc;
}, { grossRevenue: 0, transactionVolume: 0 });
// Calculate Average Order Value
const avgTicketSize = aggregation.transactionVolume > 0
? (aggregation.grossRevenue / aggregation.transactionVolume).toFixed(2)
: '0.00';
// Week-over-Week Trend Calculation
// Assumes data is sorted chronologically; compares last entry to entry 7 days prior
const latestEntry = inputData[inputData.length - 1];
const baselineIndex = Math.max(0, inputData.length - 8);
const baselineEntry = inputData[baselineIndex];
const currentVal = parseFloat(latestEntry?.revenue_amount) || 0;
const baselineVal = parseFloat(baselineEntry?.revenue_amount) || 0;
const wowDelta = baselineVal > 0
? (((currentVal - baselineVal) / baselineVal) * 100).toFixed(1)
: 'N/A';
// Construct output payload
return [{
json: {
reportDate: new Date().toISOString().split('T')[0],
grossRevenue: aggregation.grossRevenue.toFixed(2),
volume: aggregation.transactionVolume,
avgTicket: avgTicketSize,
wowTrend: wowDelta
}
}];
Step 4: Configure Email Distribution
Add a Gmail node.
- To: Your executive email.
- Subject:
={{ '📊 Daily Report — ' + $json.reportDate }}
- Message: Use the following HTML template. It uses inline styles and table-based layout for maximum client compatibility.
<html>
<body style="font-family: system-ui, -apple-system, sans-serif; max-width: 600px; margin: 0 auto; padding: 20px; background-color: #f4f6f8;">
<div style="background: #ffffff; border-radius: 8px; padding: 24px; box-shadow: 0 2px 4px rgba(0,0,0,0.05);">
<h1 style="color: #1a202c; margin-top: 0;">📊 Daily Business Report</h1>
<p style="color: #718096; margin-bottom: 20px;">{{ $json.reportDate }}</p>
<table width="100%" cellpadding="12" cellspacing="0">
<tr>
<td style="background: #f7fafc; border-radius: 6px; text-align: center;">
<div style="font-size: 24px; font-weight: 700; color: #2f855a;">${{ $json.grossRevenue }}</div>
<div style="font-size: 12px; color: #718096; text-transform: uppercase; letter-spacing: 0.5px;">Revenue</div>
</td>
<td style="background: #f7fafc; border-radius: 6px; text-align: center;">
<div style="font-size: 24px; font-weight: 700; color: #2b6cb0;">{{ $json.volume }}</div>
<div style="font-size: 12px; color: #718096; text-transform: uppercase; letter-spacing: 0.5px;">Orders</div>
</td>
</tr>
<tr>
<td style="background: #f7fafc; border-radius: 6px; text-align: center;">
<div style="font-size: 24px; font-weight: 700; color: #6b46c1;">${{ $json.avgTicket }}</div>
<div style="font-size: 12px; color: #718096; text-transform: uppercase; letter-spacing: 0.5px;">Avg Order</div>
</td>
<td style="background: #f7fafc; border-radius: 6px; text-align: center;">
<div style="font-size: 24px; font-weight: 700; color: #c05621;">{{ $json.wowTrend }}%</div>
<div style="font-size: 12px; color: #718096; text-transform: uppercase; letter-spacing: 0.5px;">WoW Change</div>
</td>
</tr>
</table>
</div>
</body>
</html>
Step 5: Configure Slack Notification
Add a Slack node.
- Webhook URI: Your incoming webhook URL.
- Text:
={{ '📊 *Daily Report — ' + $json.reportDate + '*\n💰 Revenue: $' + $json.grossRevenue + '\n📦 Orders: ' + $json.volume + '\n📈 WoW: ' + $json.wowTrend + '%' }}
Step 6: Wire the Workflow
Connect the nodes sequentially:
Schedule Trigger → Google Sheets → Code Node → Gmail
Code Node → Slack
The connection from the Code node to both Gmail and Slack creates a fan-out pattern, ensuring both channels receive the report simultaneously.
Pitfall Guide
1. Timezone Drift in Cron Expressions
- Explanation: n8n executes cron expressions based on the server's system timezone. If your server is UTC and you expect 7 AM EST, the report will arrive at the wrong time.
- Fix: Set the
GENERIC_TIMEZONE environment variable in your n8n instance to your local timezone, or adjust the cron hour offset manually.
2. Implicit Type Coercion Errors
- Explanation: Google Sheets may return empty strings or text for numeric fields. Using
+row.value can result in NaN or string concatenation instead of addition.
- Fix: Always use
parseFloat(value) || 0 to safely coerce values and provide a fallback. The provided code node implements this pattern.
3. Hardcoded Sheet Ranges
- Explanation: Specifying a fixed range like
A:E may truncate data if you add columns or if rows extend beyond the expected bounds.
- Fix: Use a wider range like
A:Z or implement dynamic range detection in the Sheets node configuration to ensure all data is captured.
4. HTML Email Rendering Incompatibility
- Explanation: Modern CSS (Flexbox, Grid, external stylesheets) is poorly supported in email clients like Outlook and older Gmail versions.
- Fix: Use table-based layouts and inline styles exclusively. The provided HTML template adheres to these constraints for maximum compatibility.
5. Silent Failures in Fan-Out
- Explanation: If the Gmail node fails (e.g., quota exceeded), n8n may halt the execution, preventing the Slack notification from sending.
- Fix: Enable "Continue On Fail" in the Gmail node settings, or wrap the distribution nodes in error-handling sub-workflows to ensure at least one channel succeeds.
6. OAuth Token Expiration
- Explanation: Google and Slack OAuth tokens can expire or be revoked, causing the workflow to fail silently until the next manual check.
- Fix: Monitor n8n execution logs. Implement a separate workflow that tests credentials weekly and alerts you if authentication fails.
7. Data Volume Blowouts
- Explanation: Google Sheets has row limits and performance degrades with large datasets. A growing sheet will eventually slow down the ingestion node.
- Fix: Implement an archival strategy. Move data older than 90 days to a separate sheet or database, keeping the active sheet lean.
Production Bundle
Action Checklist
Decision Matrix
| Scenario | Recommended Approach | Why | Cost Impact |
|---|
| Solo Founder / Low Volume | Google Sheets + n8n | Lowest complexity; sheets are familiar and easy to maintain. | $0 (Self-hosted n8n) |
| High Volume / Real-Time Needs | Direct API (Stripe/Shopify) + n8n | Bypasses sheet limits; provides fresher data; handles scale. | API costs (usually negligible) |
| Multi-Client Agency | Loop over Client IDs + n8n | Single workflow generates reports for multiple entities efficiently. | $0 (Compute cost only) |
| Strict Compliance / Audit | Database + n8n | Immutable logs; better data governance; audit trails. | Database hosting costs |
Configuration Template
Use this JSON structure as a base for your workflow. Replace placeholders with your actual values.
{
"name": "Daily KPI Aggregator",
"nodes": [
{
"parameters": {
"rule": {
"interval": [{ "field": "cronExpression", "expression": "0 7 * * *" }]
}
},
"id": "trigger-node",
"name": "Daily Trigger",
"type": "n8n-nodes-base.scheduleTrigger",
"typeVersion": 1.2,
"position": [240, 300]
},
{
"parameters": {
"documentId": "{{SHEET_ID}}",
"sheetName": "Metrics",
"options": {
"rangeDefinition": "specifyRangeA1",
"range": "A:Z"
}
},
"id": "sheets-node",
"name": "Read Metrics",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.5,
"position": [460, 300]
},
{
"parameters": {
"jsCode": "// Paste the Code Node logic from Core Solution here"
},
"id": "code-node",
"name": "Compute KPIs",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [680, 300]
},
{
"parameters": {
"sendTo": "{{EXECUTIVE_EMAIL}}",
"subject": "={{ '📊 Daily Report — ' + $json.reportDate }}",
"emailType": "html",
"message": "// Paste HTML template from Core Solution here"
},
"id": "gmail-node",
"name": "Send Email",
"type": "n8n-nodes-base.gmail",
"typeVersion": 2.1,
"position": [900, 200]
},
{
"parameters": {
"webhookUri": "{{SLACK_WEBHOOK_URL}}",
"text": "={{ '📊 *Daily Report — ' + $json.reportDate + '*\\n💰 Revenue: $' + $json.grossRevenue + '\\n📦 Orders: ' + $json.volume + '\\n📈 WoW: ' + $json.wowTrend + '%' }}"
},
"id": "slack-node",
"name": "Post to Slack",
"type": "n8n-nodes-base.slack",
"typeVersion": 2.2,
"position": [900, 420]
}
],
"connections": {
"Daily Trigger": { "main": [[{ "node": "Read Metrics", "type": "main", "index": 0 }]] },
"Read Metrics": { "main": [[{ "node": "Compute KPIs", "type": "main", "index": 0 }]] },
"Compute KPIs": { "main": [[{ "node": "Send Email", "type": "main", "index": 0 }, { "node": "Post to Slack", "type": "main", "index": 0 }]] }
}
}
Quick Start Guide
- Prepare Data Source: Create a Google Sheet with columns
Date, Revenue, Orders. Populate with sample data.
- Import Workflow: In n8n, create a new workflow and import the JSON template.
- Configure Credentials: Connect your Google account and Slack workspace. Replace
{{SHEET_ID}}, {{EXECUTIVE_EMAIL}}, and {{SLACK_WEBHOOK_URL}} in the nodes.
- Execute Test: Click "Execute Workflow" to verify data flow and output formatting.
- Activate: Toggle the workflow to active. The pipeline will now run automatically at the scheduled time.
