Sync Meta Ads insights to Google Sheets with backfill and weekly ETL

Meta Ads Insights to Google Sheets (Backfill & Weekly Sync ETL) This workflow provides a structured way to extract Meta Ads performance data and store it in Google Sheets for reporting, dashboarding, or further analysis. It is designed as a lightweight, reliable ETL pipeline focused on stability, clarity, and ease of use, rather than building a full data warehouse solution. What This Workflow Does At a high level, the system: - Pulls Meta Ads Insights data via API - Supports both historical backfill and automated incremental sync - Splits large date ranges into manageable weekly chunks - Handles pagination and retries automatically - Filters out zero-spend records before storage - Stores clean, structured data in Google Sheets - Logs skipped or empty responses for traceability Architecture Overview Core Components - n8n - Meta Ads API - Google Sheets Primary Data Outputs - AccountA → Campaign-level data (weekly) - AccountB → Ad-level data (daily breakdown) - AccountALog / AccountBLog → Logging for skipped or empty responses End-to-End Flow A) Dual Entry Points The workflow supports two execution modes: 1. Historical Backfill (Manual Trigger) Used to populate past data. - Define startdate and enddate - Workflow generates 7-day chunks - Each chunk is processed sequentially 2. Incremental Sync (Scheduled Trigger) Runs automatically every 7 days. - Dynamically pulls last 7 days - No manual input required B) Period Chunking Large date ranges are split into weekly intervals. - Prevents API overload - Reduces risk of timeouts - Ensures consistent data retrieval C) Data Extraction (Per Account) Each period is processed for two separate data streams: Account A - Level: campaign - Granularity: weekly Account B - Level: ad - Granularity: daily (timeincrement=1) Both using pagination handling & fail-safe response handling D) Response Validation Each API response is validated: - Must contain a non-empty data array - Invalid or empty responses are redirected to logging This prevents corrupted or empty data from entering the dataset. E) Data Transformation API responses are: - Split into individual rows - Normalized (numeric fields converted properly) - Preserved in full structure (no schema trimming) F) Filtering Logic Only meaningful data is stored: - Records where spend != 0 are allowed - Zero-spend rows are discarded This keeps the dataset lean and relevant for reporting. G) Data Loading Valid records are appended into Google Sheets: - Account A → campaign-level table - Account B → ad-level table Each run adds new rows without overwriting previous data. H) Logging & Traceability If a period returns: - empty data - or API anomaly The workflow logs: - status - reason - account - date range - execution ID - timestamp This creates a lightweight audit trail for debugging and monitoring. Safeguards Built In - Pagination handling (auto-follow next page) - Fail-safe handling for unstable API responses - Execution-level traceability via logs - Separation between transformation and filtering logic Google Sheets Schema AccountA / AccountB Includes: - date range (start & stop) - account, campaign, adset, and ad identifiers - performance metrics (spend, impressions, clicks, etc.) - action arrays and ranking fields Log Sheets Columns: - status - reason - account - since - until - executionid - timestamp Limitations (By Design) - Append-only system (no deduplication) - Re-running the same period will create duplicate rows - No transactional guarantees (Google Sheets limitation) - No concurrency control for parallel executions - Not designed for real-time reporting These constraints are intentional to keep the workflow simple and portable. When This Design Works Well - Marketing reporting pipelines - Looker Studio / dashboard data sources - Small to medium datasets - Teams without a data warehouse - Lightweight ETL needs Setup Requirements - Meta Ads API access (adsread permission) - Google Sheets (with required tabs) - n8n instance (cloud or self-hoste