How to Create Monthly SEO Reports

How to Create Monthly SEO Reports

Documents Needed:

Monthly reports are important to me for two reasons. First, they provide much-needed communication to stakeholders about the SEO health of a website. Second, they help identify opportunities for growth and next actions that need to be taken to capture that growth.

This monthly report Data Studio template uses data from Google Analytics, Google Search Console, and a crawl from Screaming Frog to highlight the previous month’s SEO performance.

Of course, just providing raw data to stakeholders is useless. So included in this template is the ability to add a written analysis and summary of the data presented.

How to Build a Monthly SEO Report with Data Studio

The end result of this process will be two documents:

  • A Google Sheets file that will contain your crawl data from Screaming Frog.
  • A Data Studio document containing your monthly SEO report and analysis

The Data Studio template is a remix of a report created by Webris. You can check out their version here.

Step 1: Make a copy of the Google Sheets template

How to Create Monthly SEO Reports

Step 2: Crawl Your Website with Screaming Frog

  • In Screaming Frog, perform a full crawl of your website
How to Create Monthly SEO Reports

Step 3: Export Your Site Crawl

  • Within the “Internal” tab of Screaming Frog, set the filter drop down to “All” and Export to CSV
How to Create Monthly SEO Reports

Step 4: Import Your Site Crawl CSV to the Google Sheets Doc

  • Go to the “Site Crawl Import” tab
  • Go to File >> Import
  • Find your Screaming Frog CSV export
  • In the “Import” dialog box, select “Replace current sheet”
How to Create Monthly SEO Reports

Step 5: Identify Your Website’s Sitemap(s)

There are two types of sitemaps:

  • Normal sitemap: contains actual website URLs
    • Example: http://www.cherrybombsalon.com/page-sitemap.xml
  • Sitemap Index: a list of sitemaps
    • Example: http://www.cherrybombsalon.com/sitemap_index.xml

Search for a sitemap index first. If there is no index, then search for a normal sitemap.

Step 6: Crawl Your Website’s Sitemap(s)

Be sure that you are using version 10 of Screaming Frog. If not, update Screaming Frog.

  • Open Screaming Frog. Go to Mode > List
  • Click on the Upload dropdown and select the appropriate sitemap option.
    • If you have a sitemap index, select “Download Sitemap Index” and enter the index URL.
    • If you have a normal sitemap, select “Download Sitemap” and enter the sitemap URL.
How to Create Monthly SEO Reports

Step 7: Export All Sitemap Crawl Data

  • Within the “Internal” tab of Screaming Frog, set the filter drop down to “All” and Export to CSV

Step 8: Import Sitemap Crawl Data to Google Sheets Doc

  • Go to the “Sitemap Crawl Import” tab
  • Go to File >> Import
  • Find your Screaming Frog CSV export
  • In the “Import” dialog box, select “Replace current sheet”
How to Create Monthly SEO Reports

Step 9: Make a Copy of the Monthly Report Data Studio Template

  • Open the Monthly Report Data Studio template
  • In the upper-right corner, click the “Copy” icon
How to Create Monthly SEO Reports

Step 10: Set up Data Connectors

You will be presented with a prompt to set up data connectors for Google Analytics, Google Search Console, and your Google Sheet Crawl Data Doc. For each connector, click the drop-down arrow for “Select a Data Source.”

If this is your first time using Data Studio, then you’ll need to “Create New Data Source” for Google Analytics and Google Search Console. Then, create a new data source for the following worksheets (tabs) within your Google Sheets doc:

  • 4xx/5xx pages
  • Crawl Metrics
  • Sitemap Metric
How to Create Monthly SEO Reports

How to Create Monthly SEO Reports

How to Create Monthly SEO Reports

How to Create Monthly SEO Reports

How to Create Monthly SEO Reports

Learn more about Data Studio Connectors with these resources:

Step 11: Analyze Data

Now that you have connected all your data, the last step is to analyze the data for insights, opportunities, and next actions. The first page of the Data Studio Template has an example report you can create for your stakeholder.

Feel free to reach out to me at dwayne@dwaynehogan.com or connect with me on LinkedIn if you have any questions about this template.

Leave a Reply

Your email address will not be published. Required fields are marked *