Generate weekly Brevo campaign analytics reports with Groq and Google Sheets

How it works:

This workflow fetches sent campaign data from Brevo on a schedule, calculates key metrics (open rate, click rate, bounce rate), and stores results in Google Sheets. It then filters to a rolling 7-day window, runs an AI analysis to generate an executive summary with performance highlights and recommendations, and delivers a structured HTML report by email to stakeholders.

Step-by-step:

• Data collection

• Schedule Trigger – Fires the workflow on your chosen day and time.

• HTTP Request (Brevo) – Fetches sent campaign data via the Brevo API.

• Transform Data – Calculates open rate, click rate, and bounce rate per campaign.

• Write to Google Sheets – Appends processed results to the historical database.

• Data preparation

• Read from Google Sheets – Retrieves the latest stored campaign records.

• Filter to 7-day window – Narrows data to the last 7 days for trend analysis.

• Aggregate Results – Summarises campaign performance across the window.

• AI analysis

• AI Summary Engine – Evaluates trends and generates an executive summary.

• LLM (Groq or alternative) – Provides the AI model for analysis.

• Format AI Output – Structures the summary for use in the report.

• Report delivery

• Build HTML Report – Compiles metrics, highlights, and recommendations into a styled email.

• Send via Gmail – Delivers the final report to stakeholder recipients.

Why use this?

• Automates the full reporting lifecycle from data pull to inbox delivery.
• Surfaces top-performing campaigns and list health issues without manual review.
• Keeps a growing historical database for long-term trend tracking.
• Easily adjustable reporting window, thresholds, and notification channels.
• Works with any AI provider — swap Groq for any compatible LLM.