## Introduction
Analyzing campaign engagement by time of day is crucial for marketing teams who want to optimize message delivery and improve conversion rates. Understanding when your audience interacts most with your campaigns—whether via email opens, clicks, or social media engagements—enables smarter scheduling and tailored content strategies.
This guide targets marketing teams, automation engineers, and startup CTOs looking to automate the collection, aggregation, and visualization of campaign engagement metrics by time slots using an efficient, low-code approach.
We will build a workflow in **n8n**, a popular open-source automation tool, that integrates with **Gmail (or your email provider)**, **Google Sheets**, and **Slack** to automatically extract engagement data, analyze it by time of day, and notify stakeholders with actionable insights.
—
## Problem Statement
Marketers often struggle to pinpoint the exact time when their campaigns perform best because engagement data is scattered across multiple platforms and does not come pre-aggregated by time. Manually extracting and compiling this data is time-consuming and error-prone.
**Goal:** Automate the entire process of retrieving campaign engagement events, segmenting them by hour/time slots, and summarizing engagement metrics. This gives marketers a clear picture of peak engagement periods and helps optimize send times.
—
## Tools and Services Integrated
– **n8n:** Workflow automation platform
– **Gmail API:** To fetch email campaign engagement data (opens, clicks)
– **Google Sheets:** To store and analyze engagement data
– **Slack:** To send daily summary reports to the marketing team
*Note:* If your campaigns are managed through HubSpot, Mailchimp, or other marketing tools with APIs, you can replace Gmail API calls accordingly.
—
## How the Workflow Works: Trigger to Output
1. **Trigger:** Scheduled trigger runs once daily after campaign engagement period to collect data.
2. **Fetch Engagement Data:** Use Gmail API node to query recent campaign email open and click events.
3. **Extract Timestamp:** Parse engagement event timestamps.
4. **Aggregate by Time of Day:** Calculate the hour of day for each event and tally engagement counts.
5. **Store Data in Google Sheets:** Append or update rows with aggregated data.
6. **Analyze & Summarize:** Calculate trends, such as peak engagement hours.
7. **Notify via Slack:** Post a formatted message with engagement insights to your marketing channel.
—
## Step-by-Step Tutorial
### Prerequisites
– An n8n instance (cloud or self-hosted) with API access to Gmail, Google Sheets, and Slack configured.
– Access credentials for Gmail API with appropriate scopes (read email metadata).
– A Google Sheet setup with columns: Date, Hour of Day, Opens, Clicks.
– A Slack channel and Bot User OAuth token for message posting.
### Step 1: Create a Scheduled Trigger
– **Node:** Scheduled Trigger
– **Configuration:** Run daily at a fixed time (e.g., 10 PM) to aggregate data from the prior day.
This ensures the automation gathers complete engagement data after most recipients have had a chance to interact.
### Step 2: Fetch Campaign Email Engagement Data
– **Node:** HTTP Request or Gmail API
– **Action:** Query Gmail with filters to find campaign emails (e.g., label:”campaign-xyz”)
– **Parameters:** Use Gmail API’s `users.messages.list` endpoint filtered by date and label.
Because Gmail does not directly provide opens/clicks, this example assumes tracking pixels embed messages with specific inbound hits or use Gmail’s read receipt if enabled. For advanced tracking, connect your ESP’s API (e.g., Mailchimp) for click/open data.
**Tip:** For tracking pixel data, you might store tracking events in a separate database or Google Sheet and query those instead.
### Step 3: Extract Timestamps and Engagement Type
– **Node:** Function
– **Purpose:** Parse the JSON response to extract message engagement timestamps and type (open or click).
– **Sample script:**
“`javascript
return items.map(item => {
// Extract timestamp and event type
const event = {
timestamp: new Date(item.json.internalDate),
type: item.json.labelIds.includes(‘CLICK’) ? ‘Click’ : ‘Open’
};
return { json: event };
});
“`
Modify according to your actual data structure.
### Step 4: Aggregate Engagement Data by Hour
– **Node:** Function
– **Purpose:** Convert timestamps to hour of day (0-23) and count opens and clicks per hour
“`javascript
const aggregation = {};
items.forEach(({ json }) => {
const hour = new Date(json.timestamp).getHours();
if (!aggregation[hour]) {
aggregation[hour] = { opens: 0, clicks: 0 };
}
if(json.type === ‘Open’) {
aggregation[hour].opens++;
} else if(json.type === ‘Click’) {
aggregation[hour].clicks++;
}
});
return Object.entries(aggregation).map(([hour, counts]) => ({
json: {
hour: Number(hour),
opens: counts.opens,
clicks: counts.clicks
}
}));
“`
### Step 5: Store Aggregated Data in Google Sheets
– **Node:** Google Sheets
– **Action:** Append rows with columns: Date (previous day), Hour, Opens, Clicks
– **Configuration:** Connect to your spreadsheet and specify the target worksheet.
Use expressions like `{{$now.subtract(1, ‘day’).format(‘YYYY-MM-DD’)}}` for date.
### Step 6: Analyze Trend and Prepare Summary
– **Node:** Function
– **Purpose:** Read back the day’s aggregated data, find the peak engagement hour, and calculate totals.
Example:
“`javascript
let peakHour = null;
let maxEngagement = 0;
let totalOpens = 0;
let totalClicks = 0;
items.forEach(({ json }) => {
const engagement = json.opens + json.clicks;
totalOpens += json.opens;
totalClicks += json.clicks;
if (engagement > maxEngagement) {
maxEngagement = engagement;
peakHour = json.hour;
}
});
return [{ json: { peakHour, totalOpens, totalClicks } }];
“`
### Step 7: Notify Marketing Team on Slack
– **Node:** Slack
– **Action:** Send a message to your channel with the results.
Example message:
> *Campaign Engagement Summary for {{date}}*
>
> Total Opens: *{{totalOpens}}*
> Total Clicks: *{{totalClicks}}*
> Peak Engagement Hour: *{{peakHour}}:00 – {{peakHour + 1}}:00*
Configure the Slack node to replace placeholders with workflow variables.
—
## Common Errors and Tips
– **Insufficient API permissions:** Ensure Gmail API scopes allow reading message metadata.
– **Inconsistent timestamp formats:** Normalize timezone differences to your target marketing timezone.
– **Partial data availability:** Run the workflow with appropriate delays after campaign launch to capture late engagements.
– **Google Sheets rate limits:** Batch append rows or use caching to prevent hitting API quotas.
– **Slack message formatting:** Use markdown carefully; test message appearance in your channel.
Tip: Implement error handling nodes in n8n to capture and alert for API failures.
—
## Scaling and Adaptation
– Integrate with other ESP APIs like Mailchimp or HubSpot for more precise open/click data.
– Extend the workflow to segment by recipient demographics or device type by incorporating CRM data.
– Use Google Data Studio or Tableau connected to the Google Sheet for richer dashboard visualizations.
– Schedule the workflow more frequently (e.g., hourly) to refine send time strategies in near real-time.
—
## Summary
By automating campaign engagement analysis by time of day using n8n, Gmail API, Google Sheets, and Slack, marketing teams can stop guessing when their audience is most active and instead optimize campaign scheduling based on data-driven insights. This workflow reduces manual labor, centralizes engagement metrics, and facilitates collaborative decision-making through Slack notifications.
—
## Bonus Tip
For teams with more advanced requirements, enrich your workflow by adding machine learning prediction nodes or connecting to services like Amazon Forecast or Google Vertex AI to predict optimal send times based on historical engagement patterns. This turns your automation into a proactive marketing assistant.
—
*Happy automating!*