How a Marketing Agency Slashed 10 Hours of Weekly Reporting with Real-Time Automation

How a Marketing Agency Slashed 10 Hours of Weekly Reporting with Real-Time Automation

A marketing agency managing 50+ campaigns across 15 clients eliminated 10 hours of manual reporting—every week. No more copy-pasting. No stale data. No budget surprises. Just real-time dashboards, automated alerts, and client-ready PDFs—powered by Google Sheets and Apps Script.

The Reporting Nightmare

Every Monday, the ops team burned 8-10 hours pulling data from five ad platforms. They manually updated 15 client sheets, each with unique formats. By Tuesday, the numbers were already outdated.

One LinkedIn campaign blew past 150% of its daily budget—unnoticed for days. Clients paid the price. ROI analysis? Impossible. Agency-wide performance? Invisible.

When a campaign crashed Thursday, the team didn’t know until next week’s report. The client was already furious.

The Solution: Automated Campaign Engine

We built a self-running system: The Automated Campaign Engine. Google Sheets is the hub. Apps Script pulls spend, conversions, and ROI hourly from Google Ads, Meta, LinkedIn, TikTok, and email platforms—via APIs. It updates dashboards, triggers budget alerts, and generates PDF reports—zero manual input.

System Architecture

Apps Script runs the workflow. Credentials are secure in PropertiesService. The sheet holds only data and formulas—no tokens, no risk.

How to Build It

Setup Requirements

OAuth Scopes (add in Apps Script Project Settings):

• https://www.googleapis.com/auth/script.external_request
• https://www.googleapis.com/auth/spreadsheets
• https://www.googleapis.com/auth/script.send_mail

Permissions: Run under a service account with viewer access to all ad accounts. Store API tokens in PropertiesService.getScriptProperties()—never in cells.

Config Sheet: Create a Clients tab with columns: clientId, apiToken, dailyBudget, alertEmail, lastSync. The script reads this to sync data.

Core Code

Syncs campaign data hourly, respects quotas, and triggers budget alerts. Uses batch operations and time checks to avoid the 6-minute execution limit Google Apps Script release notes.

// Pseudo code for the application 
FUNCTION syncCampaignData
  START Timer
  GET Sheets [Clients, RawData]
  
  GET Client Config [ID, Token, Budget, Email]
  
  FOR EACH Client:
    IF Time > Limit THEN BREAK
    
    GET API Token
    TRY
      FETCH Campaign Data (Retry Logic)
      IF No Data THEN CONTINUE
      
      PREPARE Rows [Date, Client, Campaign, Spend, Clicks, Conv, CTR, CPC]
      WRITE to RawData Sheet (Batch)
      
      CALCULATE TotalSpend
      IF TotalSpend > 90% of Budget:
        CALL sendBudgetAlert(Client, Spend, Budget, Email)
        
    CATCH Error
      LOG Error
      NOTIFY Admin
  
  RETURN Status
END FUNCTION
 
FUNCTION fetchWithRetry(clientId, token)
  ATTEMPT 3 Times (Exponential Backoff):
    CHECK Daily Quota
    FETCH API Use Token
    IF Success THEN RETURN Campaigns
    
  RETURN Null
END FUNCTION
 
FUNCTION sendBudgetAlert(clientId, spend, budget, email)
  SEND Email "Budget Alert: {Client} spent {Spend}"
  SEND Webhook to Chat (If Configured)
END FUNCTION

Deployment Steps

1. Create master sheet: Add tabs Clients, RawData, Dashboard, Alerts. Populate Clients with client config.

2. Store tokens securely: Run this helper in Apps Script:

// Pseudo code for the application FUNCTION storeToken(clientId, token) STORE Token in Script Properties END FUNCTION

3. Set hourly trigger: In Apps Script, go to Triggers → Add Trigger. Select syncCampaignData, time-driven, every hour. Stays under 60 executions/minute limit Google Apps Script quotas.

4. Build dashboard: In Dashboard, use:

   =QUERY(RawData!A:H, "select B, sum(D), sum(F) where A > datetime '"&TEXT(NOW()-7,"yyyy-mm-dd")&"' group by B")

   Shows 7-day spend and conversions per client.

5. Adjust alert threshold: Change 0.9 to 0.95 in budget check to trigger at 95%.

6. Add weekly reports: Create sendWeeklyReports() to export PDF and email. Schedule for Monday 7 AM.

7. Test first: Comment out the loop, hardcode one clientId, and verify data flow.

Troubleshooting

"Authorization required" error: You used a simple trigger. Use an installable trigger via the Triggers menu—full OAuth access.

Script times out: With 15+ clients, split workload. Create syncClientBatch(startIdx, endIdx) and chain triggers using ScriptApp.newTrigger().after(5).create().

Hitting 20,000 URL fetch quota: At 15 clients × 5 platforms, you’re at ~1,800 calls/day—safe. For 100+ clients, use CacheService to cache API responses for 30 minutes—cuts calls by 50%.

Need this for your team? Book a 30-minute call. We’ll deploy the Automated Campaign Engine in your workspace by Friday—with a 30-day code warranty. If it breaks, we fix it free.