Steps to build newsletter ad tracking with Google Forms and Apps Script
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 playbook
What you’re building
An automated system where filling out one Google Form creates organized Drive folders, generates campaign documents, populates a master tracking sheet, and pulls in Google Analytics performance data — all without manual data entry.
What you need
- Tools: Google Workspace (Forms, Sheets, Drive, Apps Script), Google Analytics
- Time: 4–6 hours for setup
- Also needed: Google Gemini or ChatGPT (for generating Apps Script code), UTM tracking on your newsletter ad links
Setup steps
1. Create the Google Form
Build a new Google Form with these fields:
| Field | Type | Purpose |
| Send date | Date | When the ad ran |
| Newsletter name | Short text | Which newsletter |
| Invoice number | Short text | For billing tracking |
| Cost | Short text | Ad spend |
| Ad placement type | Multiple choice or dropdown | Position in newsletter (top, middle, sponsored section) |
| Ad copy | Paragraph | The actual ad text |
| Creative file | File upload | The ad image or design |
2. Open Apps Script from Google Forms
In your Google Form, click the three-dot menu in the top right and select “Script editor” (or find “Apps Script” in the menu). This opens the code editor where your automation will live.
Screenshot note: Show the Google Forms menu with “Apps Script” highlighted
3. Generate the automation script with AI
Open Google Gemini (or ChatGPT) and ask it to write the code. Use a prompt like:
Automation script
Write a Google Apps Script that:
- Triggers when a Google Form is submitted
- Creates a new Google Drive folder named “[Newsletter Name] — [Send Date]”
- Inside that folder, creates a Google Doc with all form response data including tracking URLs
- Creates a subfolder called “Creative” and moves the uploaded file there
- Appends all form data to a master Google Sheet (Sheet ID: [YOUR_SHEET_ID])
Copy the generated script into the Apps Script editor and save.
Screenshot note: Show the Apps Script editor with code pasted in, similar to the post’s screenshot of Gemini-generated code
4. Set up the form submission trigger
In Apps Script:
- Click the clock icon (Triggers) in the left sidebar
- Click “Add Trigger”
- Set the function to run on “From form” > “On form submit”
- Save and authorize the script when prompted
Screenshot note: Show the trigger configuration dialog
5. Create the master tracking sheet
Create a new Google Sheet with these columns:
| Column | Source | Content |
| A: Send Date | Form | When the ad ran |
| B: Newsletter Name | Form | Which newsletter |
| C: Invoice Number | Form | Billing reference |
| D: Cost | Form | Ad spend |
| E: Ad Placement Type | Form | Position in newsletter |
| F: Ad Copy | Form | The ad text |
| G: Creative Link | Form | Link to uploaded file |
| H: Tracking URL | Form/Script | UTM-tagged link |
| I: Impressions | Analytics | Auto-pulled |
| J: Clicks | Analytics | Auto-pulled |
| K: Google Value | Analytics | Auto-pulled |
Columns A‑H populate from form submissions. Columns I‑K will be filled by the analytics script in the next step.
Screenshot note: Show the tracking sheet with column headers and a few sample rows, similar to Goodwillie’s tracking sheet in the post
6. Connect Google Analytics
Generate a second Apps Script to pull performance data:
Pull performance data
Write a Google Apps Script that:
- Connects to Google Analytics (Property ID: [YOUR_PROPERTY_ID])
- For each row in my tracking sheet, pulls UTM campaign data: impressions, clicks, and a “Google value” metric
- Matches data to the correct row using the campaign UTM parameters
- Writes the data to columns I, J, and K
- Runs daily on a timer trigger
Paste this into a new Apps Script project (or add it to the existing one). Set a daily time-driven trigger so it runs automatically.
7. Add lead quality scoring (optional)
Ask ChatGPT to help you define scoring criteria — what attributes indicate a high-potential lead? Customize your intake forms so responses are assigned numerical values. These scores feed back into the tracking sheet and Google Analytics, giving you richer insights into ad quality, not just volume.
Screenshot note: Show the lead scoring criteria table, similar to Goodwillie’s scoring screenshot
8. Set up client sharing
Each campaign now has its own Google Drive folder with all assets and data. To share results with a client or stakeholder, just share the specific folder — they’ll see the campaign doc, creative files, and performance data without accessing your other internal files.
Test it
- Submit a test form entry with sample data
- Verify: Drive folder created? Google Doc generated? Data in tracking sheet?
- Wait for the daily analytics pull (or run the script manually)
- Check columns I‑K — does the performance data match what you see in Google Analytics?
Troubleshooting
- Script doesn’t trigger on form submission: Check that the trigger is set to “On form submit” (not “On edit” or “On open”). Re-authorize if needed.
- Analytics data not pulling: Verify your Google Analytics property ID is correct and the UTM parameters in your tracking URLs match what Analytics is recording.
- Drive folders not creating: Check the script has permission to create files in Drive. The first run may prompt for authorization.
- AI-generated code has errors: Paste the error message back into Gemini/ChatGPT and ask it to fix the issue. Include the full error log from Apps Script.
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.