Part 5: Monthly SEO and AI Reporting - Turning Data Into Action

Target Audience

• Content Creators/Writers
• Design
• Web Dev/IT

Introduction

Set up a monthly SEO report in Excel to aggregate your data and provide month-over-month insights. Create tabs for the following reports:

5.1 Google Search Console

GSC_Queries

Follow these steps to pull the Queries and Pages reports from Google Search Console.  

1. Navigate to “Performance > Search results” in the left-hand menu.
2. Adjust your timeframe and click the checkboxes for “Average CTR” and “Average position”.
3. Click “Export > Download CSV”.
4. In the unzipped folder, retrieve the “Queries” CSV.

GSC_Pages

For this report, if you’ve followed the steps above, also retrieve the “Pages” CSV. You can move them both into one Excel workbook.

GSC_Technical

1. Navigate to “Indexing > Pages” in the left-hand menu.
2. Unlike the previous reports, there is no date to adjust, you are just looking at the Last Updated data (as shown in the top right corner.)
3. Click “Export > Download CSV” in the top right corner.
4. In the unzipped folder, retrieve the “Critical issues” CSV.
5. Combine it into the Excel workbook. Note the date it was last updated (could be in the tab itself, the tab name or elsewhere) so that you are consistent with this data.

5.2 Google Analytics 4

GA4_Landing

Follow these steps to pull the “Traffic acquisition” landing pages report from Google Analytics.

NOTE: You may want to do TWO versions of this report. If you create one without “Landing page” and a filter, it would help track overall organic traffic as a percent of the whole.

1. Create an “Exploration” with the following parameters:
   • Dimensions (Put in Rows): “Session source,” “Session medium,” “Landing page”
   • Metrics (Put in Values): “Sessions”
   • Filters: Filter so that “Session medium” exactly matches “organic”
2. Confirm the date range is correct.
3. Click “Export Data > CSV” in the top-right corner.
4. Combine into the Excel with the GSC reports.

AI_Traffic

Follow our Written Training on how to set up an AI Traffic report on Google Analytics.

1. Confirm the date range is correct.
2. Click “Export Data > CSV” in the top-right corner.
3. Combine into the Excel with the GSC reports.

5.3 What Can These Five Reports Tell Us About our SEO Performance?

Contact

• Rashmi Tenneti, Director of Analytics and Alignment, rtenneti@illinois.edu
• Maggie Evenson, Analytics Coordinator, evenson@illinois.edu