## Introduction
Retention curves are critical metrics in Data & Analytics departments, especially for customer success, product, and growth teams in startups and established businesses. These curves help visualize how often customers return or engage with your product over a defined period. Generating these charts manually can be time-consuming and error-prone. Automating this process not only enables timely insights but also ensures consistency and scalability.
In this guide, we’ll walk through how to build an automation workflow in n8n to generate retention curve charts automatically. This workflow extracts relevant user data, calculates retention metrics, generates the visualization, and shares the results via Slack or email. The tutorial assumes you have some familiarity with n8n, data sources, and basic data manipulation.
—
## Problem and Benefits
### Problem
Manually generating retention curves typically involves:
– Exporting user engagement data from databases or analytics tools
– Performing calculations in spreadsheets or BI tools
– Creating visualizations with charting software
– Distributing those charts internally
This manual workflow is repetitive, slow, and prone to human error.
### Benefits of Automation
– Consistent, up-to-date retention analysis without manual intervention
– Time saved for data analysts and engineers
– Easy distribution to stakeholders
– Scalability to multiple products, cohorts, or regions
—
## Tools and Services Integrated
– **n8n:** The automation platform for orchestrating the workflow
– **PostgreSQL (or your database):** Stores raw user engagement events
– **Google Sheets (optional):** For storing or backing up the retention data
– **Google Charts / QuickChart API / Chart.js:** To generate retention curve charts programmatically
– **Slack or Email:** Channel for delivering the final retention chart to stakeholders
—
## Overview of the Workflow
1. **Trigger:** Scheduled weekly/daily trigger to start retention calculation
2. **Data Extraction:** Query user engagement data for relevant cohorts from the database
3. **Data Transformation:** Process raw data to calculate retention rates for specified periods
4. **Chart Generation:** Use a chart generation API to create retention curve images
5. **Distribution:** Share the chart via Slack message or email attachment
—
## Step-By-Step Technical Tutorial
### Step 1: Set Up the Trigger Node
– Add an **n8n Cron node** to run the workflow on desired intervals (e.g., every Monday at 9 AM).
– Configure the schedule per your reporting cadence.
### Step 2: Extract Retention Data from Database
– Add a **Postgres node** (or the appropriate DB node).
– Write an SQL query that returns user cohort start dates, days since sign-up, and retention status (active/inactive).
Example SQL snippet:
“`sql
WITH cohorts AS (
SELECT user_id, MIN(date_trunc(‘day’, event_date)) AS signup_date
FROM user_events
GROUP BY user_id
),
retention AS (
SELECT
c.signup_date,
DATE_PART(‘day’, ue.event_date – c.signup_date) AS days_since_signup,
COUNT(DISTINCT ue.user_id) AS retained_users
FROM cohorts c
LEFT JOIN user_events ue ON ue.user_id = c.user_id
AND ue.event_date >= c.signup_date
AND ue.event_date < c.signup_date + INTERVAL '30 day'
GROUP BY c.signup_date, days_since_signup
)
SELECT signup_date, days_since_signup, retained_users
FROM retention
ORDER BY signup_date, days_since_signup;
```
- This query groups users into signup cohorts and calculates daily retention counts for the first 30 days.
### Step 3: Calculate Retention Rates
- Add a **Function node** after the database query.
- Use JavaScript to calculate retention rate = retained_users / cohort size.
- The function manipulates the data to be chart-ready, creating arrays for X-axis (days) and Y-axis (retention rate percentages).
Sample function logic:
```javascript
const data = items[0].json;
// Assume data is an array of objects [{signup_date, days_since_signup, retained_users}]
// Calculate cohort sizes (users at day 0) per signup date
const cohorts = {};
items.forEach(item => {
const signupDate = item.json.signup_date;
if (!cohorts[signupDate]) cohorts[signupDate] = { size: 0, retention: {} };
if (item.json.days_since_signup === 0) {
cohorts[signupDate].size = item.json.retained_users;
}
cohorts[signupDate].retention[item.json.days_since_signup] = item.json.retained_users;
});
// Build retention arrays
const result = [];
for (const [cohort, data] of Object.entries(cohorts)) {
const retentionRates = [];
for (let day = 0; day <= 30; day++) {
const retained = data.retention[day] || 0;
retentionRates.push((retained / data.size) * 100);
}
result.push({ cohort, retentionRates });
}
return result.map(r => ({ json: r }));
“`
### Step 4: Generate Retention Curve Chart
– Use a **HTTP Request node** to send a payload to a chart generation API like QuickChart (https://quickchart.io).
– Construct a Chart.js configuration representing multiple retention curves (one per cohort).
Sample Chart.js config for QuickChart:
“`json
{
type: ‘line’,
data: {
labels: [0,1,2,…,30],
datasets: [
{
label: ‘Cohort 2023-05-01’,
data: [100, 80, 70, …],
fill: false
},
{
label: ‘Cohort 2023-06-01′,
data: [100, 85, 75, …],
fill: false
}
]
},
options: {
title: {
display: true,
text: ’30-Day Retention Curve’
},
scales: {
yAxes: [{
ticks: { beginAtZero: true, max: 100, callback: v => v + ‘%’ }
}]
}
}
}
“`
– The HTTP Request node should POST this config encoded as JSON to `https://quickchart.io/chart/create` or compose an image URL directly.
### Step 5: Distribute the Chart
– Add a **Slack node** or **Email node** to deliver the chart.
– For Slack, post a message to a channel with the chart URL or upload the image.
– For Email, attach the image or include it inline.
Example Slack message text:
“`
Automated retention curve for the last 30 days is ready:
“`
—
## Common Errors and Tips
– **SQL Timezone Issues:** Ensure date truncations and intervals respect your timezone to align daily cohorts.
– **Data Gaps:** Handle missing days or cohorts by inserting zeros to keep chart data consistent.
– **API Rate Limits:** QuickChart is free with limits; consider caching charts or upgrading plans for heavy usage.
– **Large Cohorts:** Generating many cohorts can bloat the chart. Limit to recent cohorts or aggregate by week/month.
– **Error Handling:** Use n8n’s error workflows to retry failed HTTP requests or alert on data extraction failures.
—
## Scaling and Adaptation
– **Multiple Products:** Parameterize the SQL query and execution trigger to run per product or segment.
– **Different Retention Windows:** Modify intervals to 7-day, 90-day retention by adjusting SQL and chart x-axis.
– **Integrate BI Tools:** Instead of posting charts, push retention data to Google Sheets or Power BI.
– **Trigger on Demand:** Add a manual webhook to generate charts anytime.
—
## Summary
Automating retention curve generation in n8n empowers Data & Analytics teams to provide timely, accurate engagement insights without manual effort. By leveraging database queries for user events, scripting data transformations, and integrating with chart APIs and communication platforms, this workflow streamlines a vital reporting process.
### Bonus Tip
To make your retention analysis even richer, consider adding funnel analysis or cohort comparisons in the same workflow. You can use n8n to orchestrate multiple related reports, all generated and shared automatically, making your data team a strategic partner across your organization.