Automate Your Monthly Website Traffic Reporting
One Google Apps script, created by Claude.
Monitoring your website traffic often means logging into Google Analytics for each one, pulling session data by channel, and comparing it to last month.
Specifically, I'm tracking traffic across multiple domains. I want the data in a spreadsheet that tracks a lot of other metrics for my business. It's not hard, but it's just tedious enough that I wasn't doing it consistently because it fell to the bottom of my "to-do" pile.
I knew nothing about Google Apps scripts, but Claude wrote it for me and then walked me through step-by-step how to set it up.
What This Automation Does
- A Google Apps script runs on the 1st of each month
- It pulls session data from all my GA4 properties
- It calculates channel breakdowns and month-over-month changes
- It writes everything to a spreadsheet
- It emails me a recap
Tools
- Google Analytics (GA4)
- Google Sheets*
- Google Apps Script
Step 1: Connect your GA4 properties to a Google Sheet
Tell Claude (or your AI tool of choice) that you want to set up a Google Apps Script inside a Google Sheet to pull data from GA4.
Claude will walk you through how to set this up, and you'll need to enable the Google Analytics Data API as an advanced service (which is basically granting permissions)
Step 2: Pull sessions by channel
For each property, the script pulls total sessions broken down by channel grouping — Direct, Organic Search, Referral, and Organic Social.
Step 3: Write the monthly data
The script writes one row per property per month to a "Monthly Data" tab, including raw session counts per channel and percentage-of-total formulas.
Step 4: Calculate month-over-month trends
A second tab compares the two most recent months per property, showing current vs. prior values and the signed percentage change for each channel.
Step 5: Email the recap
After each run, the script sends an email with total sessions per property and the month-over-month delta for key channels — so you get the headlines without opening the spreadsheet.
Step 6: Set the trigger
Add a time-based trigger in Apps Script to run the script monthly. To backfill past months, call a separate function with the target year and month.
