## Introduction
In the Data & Analytics department of any startup or growing company, tracking user engagement and system usage is critical for decision-making. One common metric is the rolling 30-day usage, which helps identify trends, spikes, or declines in daily usage over a recent month. Manually compiling these metrics involves tedious extraction, aggregation, and charting steps across multiple platforms, delaying insights.
This tutorial explains how to fully automate building rolling 30-day usage charts using n8n, a powerful open-source workflow automation tool. By integrating your data source (e.g., a database or Google Sheets), transforming the data, and pushing the results to visualization tools like Google Sheets or Slack, you will create a scalable, low-maintenance workflow that delivers up-to-date usage metrics daily.
—
## What problem does this automation solve?
– **Problem:** Manually updating rolling usage charts is error-prone, repetitive, and slow. Teams lack real-time access to recent usage trends.
– **Who benefits:** Data engineers, analysts, product managers, and executives benefit from timely, automatic insights.
—
## Tools & Services Integrated
– **n8n:** The automation platform orchestrating the workflow.
– **Data source:** Could be a PostgreSQL/MySQL database, Google Sheets, or an API providing usage records.
– **Google Sheets:** For chart creation and sharing.
– **Slack (optional):** To distribute usage charts or alerts.
—
## Overview of the Workflow
1. **Trigger:** Scheduled execution daily (e.g., every morning).
2. **Extract Data:** Query database or fetch usage data for the past 30 days.
3. **Transform Data:** Process raw records to calculate daily aggregates.
4. **Load Data:** Upload aggregated data into Google Sheets.
5. **Generate Chart:** Use Sheets to visually represent the rolling 30-day usage.
6. **Notify (optional):** Post summary or chart snapshot to Slack.
—
## Step-By-Step Technical Tutorial
### Step 1: Set up n8n Environment
– Ensure n8n is installed and accessible (locally, on cloud, or via n8n.cloud).
– Access the n8n editor UI.
### Step 2: Create a New Workflow and Add a Cron Trigger
– Add the **Cron** node.
– Configure it to run once daily, e.g., at 6:00 AM.
### Step 3: Configure Data Extraction
– If your usage data resides in a database:
– Add the **PostgreSQL/MySQL** node.
– Set connection parameters (host, username, password, database).
– Write a SQL query to retrieve usage counts per day for the last 30 days:
“`sql
SELECT
DATE(usage_timestamp) as usage_date,
COUNT(*) as daily_usage
FROM user_usage_table
WHERE usage_timestamp >= CURRENT_DATE – INTERVAL ’30 days’
GROUP BY usage_date
ORDER BY usage_date ASC;
“`
– If your data is in Google Sheets:
– Add the **Google Sheets** node.
– Use the ‘Get Rows’ operation filtering for the last 30 days.
– Alternatively, fetch data via API if applicable.
### Step 4: Process and Ensure a Complete 30-Day Dataset
– Add a **Function** node after the extraction to:
– Create a date array covering the past 30 days.
– Map retrieved data to this array, filling in zeros for missing dates.
Example code snippet for the Function node:
“`javascript
const DAYS = 30;
let results = [];
let today = new Date();
for (let i = DAYS – 1; i >= 0; i–) {
let day = new Date(today);
day.setDate(today.getDate() – i);
let dateStr = day.toISOString().slice(0, 10);
let usageRecord = items.find(item => item.json.usage_date === dateStr);
results.push({json: {usage_date: dateStr, daily_usage: usageRecord ? usageRecord.json.daily_usage : 0}});
}
return results;
“`
### Step 5: Update Google Sheets with Aggregated Data
– Add another **Google Sheets** node.
– Use ‘Clear Range’ operation to remove outdated data from the usage data sheet.
– Use ‘Append’ or ‘Update’ rows to input the processed 30-day usage data.
– Layout recommendation:
– Column A: Date
– Column B: Daily Usage
### Step 6: Generate the Chart in Google Sheets
– Pre-create a line chart in Google Sheets linked to the data range so it updates automatically when data changes.
### Step 7: Optional – Notify via Slack
– Add a **Slack** node.
– Set up a channel to send daily updates.
– Message can include:
– A summary text (total usage, peak day).
– A link to the Google Sheet.
– Or an image attachment of the chart (if you export it first).
### Step 8: Save and Test Workflow
– Save your workflow.
– Run it manually to verify each step completes successfully and data appears in Google Sheets.
—
## Common Errors and Robustness Tips
– **Database connection failures:** Implement retry logic via n8n’s retry options.
– **Missing dates in data:** Handle gaps in Function node to ensure consistent 30-day periods.
– **API rate limits:** Space out calls or cache data where possible.
– **Google Sheets API limits:** Batch update rows, avoid unnecessary calls.
– **Time zone mismatch:** Normalize all dates to UTC or your business timezone.
Make heavy use of n8n’s built-in error handling and conditional nodes to add fallback paths.
—
## Scaling and Adaptations
– **Multiple usage types:** Expand queries to include different event categories and plot multi-series charts.
– **Additional metrics:** Integrate average session length, new users, churn rates.
– **Alternate output:** Use BI tools like Tableau or Power BI by exporting CSVs or pushing to databases.
– **Real-time alerts:** Trigger Slack notifications or emails on abnormal dips or spikes.
– **User segmentation:** Build similar workflows per user cohort.
—
## Summary
This guide detailed building a robust, automated rolling 30-day usage chart generator using n8n. By streamlining data extraction, transformation, and visualization, Data & Analytics teams gain timely insights with minimal manual effort. Leveraging n8n’s flexible platform and integrations like Google Sheets and Slack enables a scalable and adaptable solution that grows with your data needs.
—
## Bonus Tip: Version Control Your Workflow
Export and maintain your n8n workflow JSON in version control (e.g., Git). This practice aids collaboration and rollback if you make future changes—critical in complex data automation pipelines.
—
For further reference, consult n8n documentation on Google Sheets and database integrations to tailor this workflow to your infrastructure.