Google Sheets AI Data Parser

🔴 The Problem

A marketing agency received weekly performance reports from all their clients in Google Sheets. Each client used their own naming conventions, column structures, and data formats — meaning every Monday, an analyst spent 3+ hours manually normalising, categorising, and reformatting data before it could be used in their reporting dashboards.

• 3+ hours every Monday on manual data normalisation
• 17 different client data formats — each slightly different
• Inconsistent category names (e.g., "Facebook Ads", "FB Advertising", "Meta Paid") treated as different categories
• Human error in categorisation causing inaccurate client reports

🟢 The Solution

I built a Google Apps Script + Make.com pipeline. When a new data file is uploaded to Google Drive, a trigger fires. Make.com reads the sheet, batches rows in groups of 20, sends each batch to OpenAI with a standardisation prompt, and writes clean structured data back to the master reporting sheet.

📋 What GPT-4 Normalises Per Row

• Channel standardisation: Maps any variation of ad channel name to a standard like "Meta Ads", "Google Search", "LinkedIn"
• Category assignment: Assigns each row to the correct campaign category from a predefined taxonomy
• Numeric cleaning: Removes currency symbols, commas, and percentage signs and converts to clean numbers
• Confidence score: Returns a 0–100 confidence for each categorisation decision

🛠️ Technical Architecture

📊 The Results

• 3 hours of weekly manual data work completely eliminated
• 1,000 rows processed and categorised in under 4 minutes
• 94%+ categorisation accuracy (exceeding human consistency)
• Low-confidence rows (under 70%) automatically flagged for human review — only ~6% of total rows
• Reporting now available Monday morning before the analyst even starts their shift

The analyst now spends their Monday reviewing 60 flagged rows instead of manually categorising 1,000.