Automating Marketing Reports with n8n and Looker Studio

Every marketing team performs a monotonous ritual each Monday morning. They open Meta Ads Manager, Google Ads, and GA4, export three CSVs, paste them into a spreadsheet, fix the date formats, and rebuild the same charts they recreated the previous week. This process consumes an hour of effort, is prone to errors, and nobody enjoys it.

This guide outlines how to automate this entire pipeline using n8n (self-hosted workflow automation) and Looker Studio (free dashboards). The workflow involves n8n pulling spend and performance data from each ad platform on a schedule, normalizing it into one Google Sheet, and Looker Studio visualizes it automatically, perpetually refreshed.

Before diving into the specifics, it is essential to mention that this guide is based on the author's personal experience with building a n8n + Looker Studio automation system. The guide walks through the steps of pulling data from Meta Ads, Google Ads, and GA4, normalizing it using a Code node in n8n, and then writing the data into Google Sheets.

These data are then visualized in Looker Studio. While the guide provides detailed instructions for Meta Ads and GA4 data, it does not cover a live end-to-end test for Google Ads API due to authentication complexities. It is noteworthy that the Google Sheets are used as an intermediary step rather than direct integration with Looker Studio because Looker Studio's native ad-platform connectors have limited control over blending.

A self-owned Sheet ensures a consistent schema, timezone, and blended cross-platform metrics, which are computed before visualization.

The guide begins with pulling Meta Ads data using the HTTP Request node in n8n. Meta's Marketing API provides the necessary insights, and the GET request is made to https://graph.facebook.com/v21.0/act_AD_ACCOUNT_ID/insights with appropriate query parameters. The author highlights a critical issue regarding Meta's return format - actions are returned as an array of objects rather than flat columns. This necessitates flattening in the normalization step.

The guide then proceeds to pull data from GA4 using the Data API, which requires OAuth2 service accounts. After enabling the Google Analytics Data API in Google Cloud Console and setting up a service account with Viewer access, the data can be pulled using the Google Analytics node in n8n. The failure mode to anticipate in this step is a 403 error, which usually indicates missing the Viewer role in GA4 for the service account.

The third step involves Google Ads data, which is the most challenging due to Google Ads API requirements. The API needs a developer token, and Google approves such tokens only for accounts with a good standing. Although the n8n side is straightforward, the author has not completed a live pull against a production account due to the developer-token gate.

As an alternative, Google Ads scheduled email reports (CSV to inbox) followed by n8n's IMAP Email trigger can deliver the same data to a Sheet, though this is less elegant.

The fourth step involves normalizing the data from the three APIs into a single schema using a Code node in n8n. This node standardizes the data into a consistent format, accounting for different date formats, currencies, and structures returned by the APIs. It also handles the Google Ads cost in micro-units, which requires division by 1,000,000 to convert into readable units. Proper timezone normalization is critical to ensure accurate reporting.

Finally, the normalized data is written to Google Sheets using n8n's Google Sheets node with the Append operation. The Sheet is named 'raw', ensuring that all data is stored in one central location for Looker Studio to visualize. This automation process eliminates the repetitive, manual work that the marketing teams have been burdened with for years, providing an efficient, accurate, and time-saving solution.