How to create 3 advanced SEO reports with Supermetrics for Google Sheets

Working with data is part of every marketer’s daily routine. In SEO we prepare reports for clients, analyze the effects of our work, and plan further optimization of websites. As one of Poland’s biggest SEO agencies, we use Google Search Console and other tools, but gathering data and creating interesting reports is a time-consuming process.

That is why we decided to try Supermetrics and see if there is any way to reduce this time and to dig deeper into some numbers. Now, we want to share our easy methods of how you can use Supermetrics for SEO reporting and SEO analytics.

We created three reports that can be useful for different stages of campaigns. In addition, some of them deliver data that is not available strictly from Google Search Console. The reports are especially useful when you need to evaluate your content in terms of SEO efficiency. They are a Content Efficiency Report, a Content Efficiency Report with Keywords, and a CTR Report Considering Position and Keyword Length.

1. Content efficiency report

Creating new content for SEO campaigns might be expensive, especially when you focus on quality. But do they always give you the expected results? In order to evaluate their effectiveness, we decided to analyze some data from Google Search Console, but in a nonstandard way. Of course, we could do that without using Supermetrics, but gathering the data manually would take much more time. We went for automation by using Supermetrics and adding some formulas in a Google spreadsheet.

How we generated data using Supermetrics?

We’ve chosen Google Search Console as a source, then a website which we wanted to analyze. In this case, it was our website widoczni.com. We were particularly interested in such metrics as clicks, CTR, impressions, and average position in Google search results. It was particularly important for us to split the data into months. However, we excluded data coming from branded keywords as they usually generate higher CTR.

Step by step guidelines to create this report:

• Data Source: choose GSC
• Select Metrics: choose clicks, impressions, CTR and average position
• Split by: split to rows – choose Full URL, split to columns – choose month
• Filter: exclude any traffic that is irrelevant, for example, blog traffic
• Options: in Brand keywords add your brand to exclude branded keywords

Important: In this report, we used manually created formulas to get some charts in the first few columns. Use a formula: SPARKLINE and add data range.

Why this report?

What GSC gives us by default is not enough. We would like to know the efficiency of our content in a certain time span in order to know if certain content influenced SEO results.

2. Content efficiency report with keywords

This is an extended version of the first report. This time we wanted to see a relation between a particular keyword and impressions, clicks, and position on the SERP.

How we generated data using Supermetrics?

We expanded the content efficiency report by using additional data – keywords used in the search engine.

Step by step guidelines to create this report:

• Data Source: choose GSC
• Select Metrics: choose clicks, impressions, CTR and average position
• Split by: split to rows – choose Full URL and Search query, split to columns – choose month
• Filter: exclude any traffic that is irrelevant, for example, blog traffic
• Options: in Brand keywords add your brand to exclude branded keywords

Important: In this report, we used manually created formulas to get some charts in the first few columns. Use a formula: SPARKLINE and add data range.

Why this report?

It helps us come to some vital conclusions and:

• Rate our content in terms of future results
• Plan new content
• Analyze the efficiency of our content

The report also helps to detect keywords cannibalization.

3. CTR report considering position and keyword length

Finally, a report that gives a chance to find data that is not available directly in GSC.

We wanted to compare CTR of every page of a website and see a correlation between the position in SERP and CTR. Additionally, we wanted to compare the length of each keyword and how it affected the position and CTR.

How we generated data using Supermetrics?

We’ve chosen to analyze metrics such as CTR, position in top 10 Google, and a number of words in each keyword (creating a range from 1 to +8 words).

Step by step report:

• Data Source: choose GSC
• Select Metrics: choose CTR
• Split by: split to rows – choose SERP position (rounded avg., paged), split to columns – choose Search query
• Filter: choose clicks greater and set 1
• Options: in Brand keywords add your brand to exclude branded keywords

Why this report?

This report lets us use GSC data in a nonstandard way, which is available only with Supermetrics. It is a valuable source of knowledge for our SEO experts. For example, when it comes to sales pages CTR for pages ranked number 7 and lower is nosediving. This means one should take care of optimization and fight for better results. Whereas with blog pages there is not much difference in CTR between 1st and 10th position. This lets us to the conclusion that fighting for better positions is useless. Of course, every time we recommend to make separate analysis, as every page, company, and business could be different.

Conclusion

We came up with 3 different reports for optimizing content and planning SEO campaigns, which minimized the time spent on research. When we did that manually, it took us a few days. With Supermetrics we could finish reporting in 3 hours. What is more, automation protects us against some human errors like misspelling or omitting some data. When it comes to the last report, we gain additional data that is not available directly in GSC.

About Cezary Urbanowicz

Cezary is SEO Manager at one of the biggest SEO agencies in Poland – widoczni.com. He is experienced in website optimization as well as e-commerce SEO. His latest passion is conversion rate optimization.