Steps for automating your newsletter ad reporting and tracking
This resource is based on One form, one sheet, zero manual reporting, featuring James Goodwillie of Stack Influence, published on the AI Lab by ActiveCampaign.
Get the checklist
Before you start
Make sure you have:
- Google account with access to Google Forms, Sheets, Drive, and Apps Script
- Google Analytics set up with UTM tracking on your newsletter ad links
- Access to an AI tool — Google Gemini (used in the post) or ChatGPT for generating Apps Script code
- List your newsletter ad data points — what do you need to track? (send date, newsletter name, cost, placement type, copy, creative)
The workflow
Phase 1: Build the intake form (30–60 min)
- Create a Google Form with fields for: send date, newsletter name, invoice number, cost, ad placement type, ad copy, and creative file upload
- Test the form — submit a sample entry to make sure all fields capture correctly
- Share the form with your team — anyone running a newsletter ad should know to fill this out when a new ad goes live
Phase 2: Automate storage and organization (1–2 hours)
- Open Apps Script from the Google Forms menu (three dots > Script editor)
- Generate the automation script with AI — ask Gemini or ChatGPT: “Write a Google Apps Script that triggers on form submission and: (1) creates a new Google Drive folder labeled with the newsletter name and date, (2) generates a Google Doc inside that folder with all campaign details including tracking URLs, (3) creates a subfolder for ad images, (4) feeds campaign details into a master tracking sheet”
- Paste the script into Apps Script and save
- Set up the trigger — configure the script to run on form submission (Edit > Current project’s triggers > Add trigger > On form submit)
- Create the master tracking sheet — set up columns A‑H for form data (send date, newsletter name, invoice number, cost, placement type, ad copy, creative link, tracking URL)
- Test the full flow — submit a form entry and verify: Drive folder created, Google Doc generated, data appears in tracking sheet
Phase 3: Connect performance data (1–2 hours)
- Generate a Google Analytics connection script with AI — ask Gemini or ChatGPT: “Write a Google Apps Script that pulls UTM data from Google Analytics (impressions, clicks, Google value) and writes it to columns I‑K in my tracking sheet, matched by campaign UTM parameters”
- Paste and configure the script — add your Google Analytics property ID and sheet ID
- Set a daily trigger — configure the script to pull analytics data the day after each newsletter runs
- Test with a live campaign — verify that performance data auto-populates in the tracking sheet
Phase 4: Add lead quality scoring (1–2 hours)
- Define your lead scoring criteria — what makes a high-potential lead vs. a low-value one?
- Customize form responses with AI — ask ChatGPT to help assign values to form responses so high-potential leads score higher
- Feed scores back into the tracking sheet — add a column for lead quality score
- Connect scores to Google Analytics — use the values to get richer insights into whether an ad attracted the right kind of client, not just the cheapest lead
Quick reference
- Total time: 4–6 hours for initial setup
- Tools needed: Google Forms, Google Sheets, Google Drive, Google Apps Script, Google Analytics, Google Gemini, or ChatGPT
- Key output: A centralized tracking system where submitting one form auto-creates organized Drive folders, populates a master tracking sheet, pulls in analytics data, and scores lead quality, saving 5–10 hours per week
- No coding required: All scripts generated by AI (Gemini or ChatGPT)
Ready for the full story?
Read One form, one sheet, zero manual reporting, featuring James Goodwillie of Stack Influence, published on the AI Lab by ActiveCampaign.
Related
More data from the AI Lab.