Quick overview
This workflow runs weekly to analyze Google Search Console query and page performance, uses OpenAI to draft FAQPage schema and content/metadata improvements for high-opportunity pages, sends an approval request via Gmail, and stores approved packages in Google Drive while logging and monitoring results in Google Sheets.
How it works
- Runs weekly on a schedule.
- Pulls up to 250 query-and-page performance rows from the Google Search Console Search Analytics API and normalizes them into one record per query and landing page.
- Reads the existing “SEO Change Log” tab from Google Sheets, merges it with performance signals, and scores each query/page for missed-click opportunity.
- For eligible opportunities, fetches the live page HTML, extracts observable on-page facts (like title, H1, and presence of JSON-LD), and uses OpenAI to produce a content gap analysis.
- Uses OpenAI to draft a JSON package containing FAQPage schema with three FAQ pairs plus title/meta alternatives and a brief/outline, then compiles the results into an approval digest.
- Sends the digest to an approver via Gmail and, if approved, uploads the approved JSON package to Google Drive and appends a record to the “SEO Change Log” in Google Sheets.
- Notifies the website team by Gmail, waits 14 days, and appends a post-change snapshot to the “SEO Monitoring” tab in Google Sheets.
Setup
- Add credentials for Google Search Console OAuth2, Google Sheets OAuth2, Google Drive OAuth2, Gmail OAuth2, and an OpenAI API key.
- Replace the placeholders for site URL, Search Console property (for example, sc-domain:yourdomain.com), Google Sheet ID, Drive folder ID, and approval email.
- Create a Google Sheet with two tabs named “SEO Change Log” and “SEO Monitoring” with the columns you want to append for logging and monitoring.
- Ensure the target Search Console property is accessible to the connected Google account and that the workflow can fetch your page URLs publicly.
- Confirm the Gmail “send and wait” approval flow works for your approver (responses must set the approval flag) and adjust the schedule/date range in the Search Console request if needed.