Python tool that pulls SEO data from Moz and Google Analytics (GA4) for your sites and writes the results into a Google Sheet. Designed to run on a schedule in the cloud (e.g. monthly) so report data is updated without manual copying.

• dallasbuilders.com
• dallasbuilders.org
• greenbuilttexas.com
• registry.greenbuilttexas.com
• mcsamawards.com
• paradeofhomesdallas.com

Use this email when sharing the Google Sheet and when adding a user in GA4:

seo-report-automation@seo-automation-tool-489619.iam.gserviceaccount.com

• Option A (recommended): API token

  • Go to Moz API dashboard and create an API token.
  • Set MOZ_API_TOKEN in your environment (or .env).

• Option B (legacy): Access ID + Secret Key

  • From your Moz account, use “Show legacy credentials” if available.
  • Set MOZ_ACCESS_ID and MOZ_SECRET_KEY in your environment.

• A Google Cloud project with:
  • Google Sheets API enabled
  • Google Analytics Data API enabled
  • Google Search Console API enabled (for the ORG Search Visibility tab)
• A service account with a JSON key file:
  • Used for Sheets, GA4, and (optionally) Search Console.
  • Share your Google Sheet with the service account email (Editor).
  • For GA4: add the service account as a user on the GA4 property (Viewer is enough).
  • For ORG Search Visibility: in Search Console, add the service account as a user (Full or Restrict) for the property (e.g. https://dallasbuilders.org/ or sc-domain:dallasbuilders.org).
• GA4 property ID(s):
  • In GA4: Admin → Property Settings → Property ID (numeric, e.g. 123456789).
  • Either one property per site in config.yaml under ga4.property_ids, or a single property for all sites under ga4.single_property_id.

• Copy .env.example to .env and fill in:
  • MOZ_API_TOKEN (or legacy Moz credentials)
  • GOOGLE_APPLICATION_CREDENTIALS = path to your service account JSON
• Edit config.yaml:
  • spreadsheet_id: your Sheet ID (already set to the DBA SEO Report Sheet).
  • sheet.moz.tab / sheet.ga4.tab: tab names that match your Sheet (defaults: "Moz Metrics", "GA Metrics").
  • sheet.moz.range / sheet.ga4.range: optional; data is written from row 1 with headers.
  • ga4.property_ids: map each site to its GA4 property ID, or set ga4.single_property_id if one property covers all sites.
  • date_range: last_month (default) or explicit dates.

To test with a fresh sheet (then switch to it and archive the old one):

1. Create the sheet
   In Google Sheets, create a new blank spreadsheet. Name it (e.g. "DBA SEO Report – Test" or "DBA SEO Report 2026").

2. Share it with the service account
   Click Share, add this email as Editor:
   seo-report-automation@seo-automation-tool-489619.iam.gserviceaccount.com
   (Uncheck "Notify people" if you prefer.)

3. Get the spreadsheet ID
   From the browser URL:
   https://docs.google.com/spreadsheets/d/XXXXXXXXXX/edit
   The spreadsheet ID is XXXXXXXXXX (the long string between /d/ and /edit).

4. Point the app at the new sheet
   In config.yaml, set:
   spreadsheet_id: "XXXXXXXXXX"
   (Replace with your new ID.) No other config changes are needed; the script creates the tabs it needs.

5. Run the report
   Run python main.py (or use the web app). The new sheet will get Moz Metrics, GA Metrics, and all optional tabs (Users x day, Users x month Test, etc.) written automatically.

6. Switch and archive
   When you’re happy with the new sheet, keep using it. Optionally rename or move the old sheet to an "Archive" folder in Google Drive, or add "(Archived)" to its name.

To map the old sheet into the new one (copy every tab and its cell data in one go):

1. Share the old sheet with the service account as Editor:
   seo-report-automation@seo-automation-tool-489619.iam.gserviceaccount.com

2. Set the source in config
   In config.yaml, set (or leave as-is if already set):

   • spreadsheet_id: your new sheet ID (where the report writes)
   • source_spreadsheet_id: the old sheet ID (e.g. 1V8QXFC7PaDee0Hc5GRWCjAWVDPkz4YwqULq6_-nl2EI)

3. Run the copy script

   python copy_sheet.py

   This creates any missing tabs in the new sheet and copies all cell values from the old sheet. Charts and formatting are not copied; running python main.py afterward will recreate charts where the report defines them.

4. Optional
   After migration, you can remove or comment out source_spreadsheet_id in config.yaml so it’s clear the one-off copy is done.

cd seo-report-automation
python3 -m venv .venv
source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt
cp .env.example .env
# Edit .env with your credentials and config.yaml with your sheet tabs / GA4 IDs
python main.py

A simple password-protected site lets you run the report by clicking a button.

1. In .env, set:
   • REPORT_PASSWORD – password to log in (required).
   • FLASK_SECRET_KEY – optional; a random string for session security (e.g. openssl rand -hex 24).
2. Install dependencies and start the app:

   pip install -r requirements.txt
   python app.py

3. Open http://localhost:5000 (or the URL shown). Log in with REPORT_PASSWORD, then click Run report now. The script runs in the background; when it finishes, the page shows success or error and the script output.

The app listens on all interfaces (0.0.0.0) so you can use it from another device on your network.

To host it on a public URL (so anyone with the link and password can run the report), see HOSTING.md. It covers Google Cloud Run, Railway, and Render: you get an HTTPS URL, set the password and secrets in the platform, and share the URL with your client or team.

Heroku and Slack /seo-report are documented in HEROKU.md. Google Slides placeholders (deck automation) are in docs/SLIDES.md.

This will:

1. Pull Moz URL metrics (Domain Authority, linking root domains, total links, spam score) for each site.
2. Pull GA4 metrics (sessions, users, bounce rate, avg session duration, conversions) for the configured date range.
3. Write two blocks to your Sheet: one for Moz data, one for GA4 data (headers + one row per site, plus report date).

• Moz tab: Site, Domain Authority, Linking Root Domains, Total Links, Spam Score, Report Date.
• GA tab: Site, Sessions, Users, Bounce Rate, Avg Session Duration (sec), Conversions, Report Date.

If your existing Sheet uses different tab names, set them in config.yaml under sheet.moz.tab and sheet.ga4.tab. You can add new tabs (e.g. "Moz Metrics" and "GA Metrics") and the script will write there.

See DEPLOYMENT.md for:

• Running the script as a Cloud Function or Cloud Run job.
• Using Cloud Scheduler to trigger it monthly (e.g. first day of the month).
• Storing Moz credentials in Secret Manager and using the same service account for Sheets and GA4.

No changes to the script are required; only deployment configuration and secrets.

Use as needed for your reports.