MCL

Monthly Report → Automation

An end-to-end automated reporting system that extracts backend data, processes key metrics, generates branded monthly performance decks, and delivers them to multiple partners through automated email workflows.

This system orchestrates backend data through BigQuery and Google Sheets to generate branded partner reports, delivered automatically via n8n workflows.

Project Summary

Automated extraction of backend data into BigQuery

Scheduled data sync to Google Sheets for visualization

Automated chart refresh and high-resolution export

Automated partner-specific report generation with custom branding

Monthly email distribution automated via n8n

Scales to multiple partners with minimal team effort

System Architecture

Backend Data

Source data systems

BigQuery

Data warehouse & processing

Google Sheets

Charts & visualization

Apps Script

Chart export and report assembly

n8n Workflows

Automated email distribution

Key Challenges & Solutions

Complex Data Processing

Backend data requires multi-stage processing across BigQuery and Google Sheets.

Solution:
An automated ETL pipeline with scheduled data pulls and centralized transformation logic.

Regular Data Sync

Frequent data extraction is required to keep reports accurate and up-to-date.

Solution:
Scheduled workflows to pull data from backend → BigQuery → Google Sheets on defined intervals.

Multiple Partners

Partners require individualized reports with their own branding and data filters.

Solution:
Template-based generation with dynamic placeholder replacement and partner- specific data filtering.

Limited Resources

Small marketing team lacks bandwidth to manually process data and create reports monthly.

Solution:
End-to-end automation from data extraction to email delivery, eliminating manual work.

API Rate Limits

Batch export tasks can trigger HTTP 429 rate-limit errors

Solution:
Implemented exponential back-off with configurable delays and retry limits.

Technical Implementation

1. Refreshing Chart Data

// Iterates through all presentations and refreshes embedded charts
function refreshChartsInAllPresentations() {
  SLIDE_URLS.forEach(url => {
    const presentation = SlidesApp.openById(extractId(url));
    presentation.getSlides().forEach(slide => {
      slide.getSheetsCharts().forEach(chart => chart.refresh());
    });
  });
}

2. Exporting Slides as Images

// Exports each slide as a high-resolution PNG
slides.forEach((slide, index) => {
  const thumbnail = slide.getThumbnail({
    contentUrl: 'PRESENTATION_ID',
    mimeType: 'image/png'
  });
  
  folder.createFile(thumbBlob)
    .setName(`slide_${index + 1}.png`);
});

3. Generating Partner Reports

// Copy template, replace placeholders, insert charts & logos
partners.forEach(partner => {
  const deck = template.makeCopy(partner.name);
  
  deck.replaceAllText('{{PARTNER_NAME}}', partner.name);
  deck.replaceAllText('{{MONTH}}', currentMonth);
  
  insertLogo(deck, partner.logoUrl);
  insertChartImages(deck, partner.chartFolder);
  
  deck.saveAndClose();
});

90%

Reduction in manual reporting time

400+

Charts processed each Cycle

Manual errors

30min

Automated runtime per month

Want to automate your workflows?

Let’s create an automation that saves you time and keeps your data secure.

Let's build something →