How to create SEO dashboard in Data Studio for SEO Analysis

23980
•
24-minute read
Author
Evgeniy Nosov
Evgeniy Nosov is an SEO with 7+ years of experience. He has been responsible for SEO strategy and quality control in one of the largest agencies in the CIS. Now he is engaged in large international projects...
Date
Feb 28, 2024

About the author

Evgeniy Nosov
Evgeniy Nosov is an SEO with 7+ years of experience. He has been responsible for SEO strategy and quality control in one of the largest SEO agencies in the CIS. Now he is engaged in large international projects as an SEO consultant.

As an SEO, one has to deal with a mass of data from different analytic tools. They help track and analyze all the necessary metrics that show your project's performance. The inconvenience is that they provide siloed data. And you may overlook important details, as you don't see a complete picture of your project.

Being an SEO myself, I've come to the necessity of having an SEO dashboard where I could have all the data I track in a single space. In my work, I use different tools, including Google Analytics, Google Search Console, and Rank Tracker.

Download Rank Tracker

 

I believed it could help me better assess the state of the project, its success or failure. It could also show potential growth opportunities and trouble spots, and help find possible reasons behind sudden ranking drops. Moreover, I needed to overcome the common Google Analytics limitation that involves hiding converting keywords behind the (not set) mark.

Contents

From this article, you will learn to integrate data from Rank Tracker, Google Search Console, and Google Analytics into Google Data Studio and aggregate it in a single SEO dashboard. You'll also see how to discover those (not set) converting keywords from Google Analytics.

Meanwhile, what is Google Data Studio

Google Data Studio is a reporting tool. Users can visualize the data through graphs and create tables with calculated fields. Data sources are added through various connectors. Apart from Google products (such as Ads, Analytics, Search Console, etc.), you may connect third-party tools and services as data sources. The tool also allows for combining data from different sources. Thus users may consolidate and analyze all the necessary data in a single space, choosing one of Google Data studio dashboard templates or creating a custom dashboard.

This enables to see a much clearer picture of a project. And helps define further steps and improvement tactics.

Let's try to create a working SEO dashboard in Data Studio, containing different pages with graphs, tables, charts, filters, etc.

Connecting data sources to Data Studio

To start using Data Studio, you first need to connect some data sources to it. In my example, I'll be using three different sources of SEO data to create the dashboard:

  • Google Search Console,
  • Google Analytics,
  • Rank Tracker.
Download Rank Tracker

 

Like mentioned before, there are various connectors in Data Studio through which data sources are added. I'll be using three of them. The corresponding connectors for Analytics and Search Console, and the File Upload for the data from Rank Tracker. (The latter will need some prearrangement, I'll come to it in due time.)

 
Connecting Google Search Console

Let's add Google Search console as the first data source for the future SEO dashboard.

  1. On the Data Studio homepage, click Create and choose Data Source in the dropdown menu.
  1. Then you need to select Search Console from the list of available connectors and authorize the tools' access to your Search Console account.
  2. Once done, you'll see the list of the websites you manage. Choose the one you're creating your dashboard for. Then select URL Impressions in the Tables column click Connect in the upper-right corner of the screen.
You'll see the available data fields. Sometimes, there's a necessity and possibility to add more fields. This time, we won't be adding anything, so click Done in the upper-right corner of the screen. The data source is connected.

 

 
 
Connecting Google Analytics

Let's get back to the homepage and add the second data source.

  1. This time, choose the connector for Google Analytics.
  2. Select the account you wish to connect, choose the website and the necessary view (if you have several) in the Property and View columns respectively.
  1. Click Connect, look through the list of available fields and click Done to finish this connection.
 
 
Connecting Rank Tracker
Now, let's get to adding data from Rank Tracker to Data Studio as the third data source for the particular SEO dashboard. Here the process will be a bit different.

 

I'm interested in comparing my website's ranks against those of my competitors'. The search Console module in Rank Tracker provides this information. But the available for analysis time range is limited to a month. In Rank Tracker, however, it's possible to save rank tracking history. It is handy If I want to track the ranks at runtime. Search Console itself doesn't share this information with Data Studio. Thus, I need the data integrated into Data Studio from Rank Tracker.

Apparently, there's no connector for Rank Tracker in Data Studio. So we'll apply an elegant technical solution :). I mean, we'll export the necessary data and add it to Data Studio through the File Upload connector.

1. Export Rank Tracker data

  • Open your project in Rank Tracker and go to File> Export to export your whole project's ranking history.
  • In windows that will successively appear, select:

Text CSV File as the export format
Comma as a delimiter
Export rankings historical data in Keyword Data Settings, (tick Include competitors if you need to export your competitors' data as well)

A little note. You may have already exported data from Rank Tracker. If so, at the first step you'll be asked to choose the export template from the list (you probably have some). In this case, click the Add button to create a new template.

  • At the Preview step, switch to Code.

Replace the contents of the screen with the sample below. (Don't ask howI got it. It was a cut-and-try method.) Then finish the export process.

<[DEFINE name="dateFormat" value="exportData.createDateFormat('M/d/YYYY')"/]>
<[DEFINE name="keywords" value="exportData.keywords"/]>
<[DEFINE name="searchEngines" value="exportData.searchEngines"/]>
<[DEFINE name="competitors" value="exportData.competitors"/]>
<[DEFINE name="ENTERED_VALUE" value="2147483647"/]>
<[DEFINE name="DROPPED_VALUE" value="-2147483646"/]>
<[DEFINE name="NOT_RANKED_VALUE" value="-2147483647"/]>
<[DEFINE name="NOT_CHECKED_VALUE" value="-2147483648"/]>
Keyword,SearchEngine,Check Date,Rank <[ECHO text="exportData.project.getShortName()"/]>,<[FOR_EACH name="competitors" id="competitor"]>Rank <[ECHO text="competitor.getShortName()"/]>,<[/FOR_EACH]>URL Found <[ECHO text="exportData.project.getShortName()"/]>, Landing Page <[ECHO text="exportData.project.getShortName()"/]>,<[FOR_EACH name="competitors" id="competitor"]>URL Found <[ECHO text="competitor.getShortName()"/]>,<[/FOR_EACH]>
<[FOR_EACH name="keywords" id="keyword"]>
<[FOR_EACH name="searchEngines" id="searchEngineType"]>
<[DEFINE name="history" value="keyword.getHistory(searchEngineType)"/]>
<[FOR_EACH name="exportData.reverse(history)" id="historyRecord"]>
<[ECHO text="keyword.query"/]>,<[ECHO text="searchEngineType.getName()"/]>,"<[ECHO text="dateFormat.format(historyRecord.checkDate)"/]>",<[DEFINE name="position" value="exportData.getPosition(historyRecord)"/]><[IF condition="position != null"]><[THEN]><[IF condition="position=='Not in top 50'"]><[THEN]><[ECHO text="100"/]><[/THEN]><[ELSE]><[ECHO text="position"/]><[/ELSE]><[/IF]><[/THEN]><[/IF]>,<[FOR_EACH name="competitors" id="competitor"]><[DEFINE name="competitorHistoryRecord" value="exportData.getKeywordPosition(competitor, keyword, searchEngineType, historyRecord)"/]><[IF condition="competitorHistoryRecord != null"]><[THEN]><[DEFINE name="position" value="exportData.getPosition(competitorHistoryRecord)"/]><[IF condition="position != null"]><[THEN]><[IF condition="position=='Not in top 50'"]><[THEN]><[ECHO text="100"/]><[/THEN]><[ELSE]><[ECHO text="position"/]><[/ELSE]><[/IF]><[/THEN]><[/IF]><[/THEN]><[/IF]>,<[/FOR_EACH]><[IF condition="historyRecord != null"]><[THEN]><[ECHO text="historyRecord.url"/]>,<[ECHO text="exportData.formatLandingPage(keyword.getLandingPage())" suppressEncoding="false" formatType="CSV" formatedTextType="URL"/]><[FOR_EACH name="competitors" id="competitor"]><[DEFINE name="competitorHistoryRecord" value="exportData.getKeywordPosition(competitor, keyword, searchEngineType, historyRecord)"/]>,<[IF condition="competitorHistoryRecord != null"]><[THEN]><[ECHO text="competitorHistoryRecord.url"/]><[/THEN]><[/IF]><[/FOR_EACH]><[/THEN]><[/IF]>
<[/FOR_EACH]>
<[/FOR_EACH]>
<[/FOR_EACH]>

 

  • Review the CSV file you got and prepare it for uploading to Data Studio. Normally, you'll get a file looking like this:

 

You might need to eliminate a couple of inconsistencies, in particular: Several cells may contain “Not in Top 100”, which means your website is out of the first hundred results. Change the text in such cells to a numeric value. I changed it to 100 Several cells in the Rank columns may contain values in brackets. This means that your website, apart from organic results, is shown in one of the search features (such as images, local pack, etc.). Just eliminate the value in brackets, and brackets, of course.

  • Finally, save the file as a CSV file with a comma as a delimiter. The export file is ready now, whew!

 

2. Select Upload File from the list of Data Studio connectors and upload the Rank Tracker export file.

3. Ensure that all the data fields were identified correctly.

  • Check date should be identified as Date. Otherwise, set the right data Type manually.
  • Rank should be identified as Number. If the Type is different, review your export file, some Rank cells might still contain text instead of numeric values(you'll then have to correct and re-upload the file).

 

4. For all the Rank strings, set the Aggregation as Average.

 

Merging data sources in a table

Having all the necessary data sources added to Data Studio, we can combine the data from these sources in a variety of analytic tables. We can base them either on the data from a single source or several sources combined. Data Studio allows for mixing up to five data sources in a single visualization.
To reach my goals, I'll need to analyze the data from different sources mixed in various combinations. So I will need to blend data.

There's a peculiarity you need to take into account. Blending data in Data Studio is a left outer join operation. It means that the table you create will be based on the records of the first data source you add to the blend. So depending on what metrics you need to analyze, consider placing the appropriate data source on the left in the blend row.

 
How to blend data
  1. On the homepage, click Create> Report. You'll have a new report page created.
  2. On the report page, select one of your data sources on the right sidebar.
  3. Go to Resources> Manage blended data, and on the appeared page click Add a Data View.

Add the necessary data sources. As an example, I'll add all three: Rank Tracker, Google Search Console, and Google Analytics.

  1. Choose the Landing page field as a key for Search Console and Rank Tracker and the ConcatURL field for Google Analytics.

Important note! As a rule, Landing pages in Google Analytics are shown as relative URLs, while absolute URLs are shown for the other data sources in my example.

 

URL Rank Tracker URL Search Console URL Google Analytics
https://site.com/page-1 https://site.com/page-1 /page-1
  • To do this, go to Resources> Manage added data sources and click Edit next to Google Analytics.
  • Click Add fields to create a new field, name it ConcatURL.
  • Add the following formula:
CONCAT('https://site.com',Landing Page)

We've created a calculated field that will be turning relative URLs to absolute. Thus all three keys will match.

  1. When done, click Save.
  2. Add the necessary metrics and dimensions to each data source.

When everything is configured correctly, Data Studio may be able to blend the data.

Let's take a closer look at all these Dimensions, Metrics, Filters, etc. needed for each data source.

Rank Tracker:

Keywords — target keywords from Rank Tracker
TypeOfKeyword — defines the type of keyword (I'll come to it a bit further)
HyperKeyword — makes a keyword a hyperlink. See how to do this below:

Knowing the SERP for a certain keyword might be quite helpful to define whether to target the keyword or what page to optimize for it. So let's make all the keywords hyperlinks to corresponding SERPs.

  1. On the report page, go to Resources> Manage added data sources and click Edit next to Rank Tracker.
  2. Click Add a field, name the field HyperKeyword and insert the following formula in the window below.
HYPERLINK(CONCAT("https://www.google.com/search?q=",Query,"&uule=w+CAIQICIfTmV3IFlvcmssTmV3IFlvcmssVW5pdGVkIFN0YXRlcw==&hl=en-US&cl=US"),Query)

Let's break it down:

HYPERLINK means you're creating a hyperlink.
CONCAT helps put together all the parts of the target URL.
Query — the query you'll see the SERP for.
&uule=w + introduces the tracking region code. (In my example, its the US, you may set up another region if necessary. Check out how to encode the region)
Query — the query will be used as the anchor text.

 
Google Search Console:

Landing Page — a page that had impressions in Google
Google Property — the type of search (web, images, etc.)
TypeOfQuery — defines the type of keyword

Google Analytics:

ConcatURL — a calculated absolute URL
Medium Filter — the filter that will cut off all results except for organic

 
 

Now, as we know how to blend data, we can start inhabiting the dashboard with a variety of analytic tables.

1. Analyze target keywords efficiency and find new keywords to target

To ensure an SEO campaign is effective, it's critical to regularly analyze the efficiency of your target keywords.

  • Are there any impressions and clicks for each of my target keywords?
  • Who are my competitors? And what are their positions for the target keywords?
  • What's the content competitors' ranking pages contain?
  • Are there keywords I don't target yet that bring me traffic?

This information will timely prevent me from wasting my time on inefficient keywords and help find out some great keyword ideas. Analyzing my competitors' content, I may find ways to improve my pages and thus be able to improve my ranks. For a comprehensive analysis, I need to gather all this data in a single place.

I will need the data on clicks, impressions, and CTR from Google Search Console. So I will base my keyword efficiency analysis table on the data from this tool. And I usually use Rank Tracker to track my positions for target keywords and those of my competitors. Thus, I'll blend the data from these sources in the first two tables I'm adding to my dashboard.

 
How to create a report and blend two data sources
  1. On the homepage, click Create> Report and select your initial data source from the list in the right sidebar.
  2. Go to Resource> Manage blended data, click Add a data view, and add Search Console as the first data source and Rank Tracker as the second.
  3. Choose Query and Keyword respectively as Join keys, and set up the necessary metrics and dimensions.
Search Console

Dimensions:

  • Landing Page
  • Query

Metrics:

  • Url Clicks
  • Impressions
  • URL CTR
Rank Tracker

Dimensions:

  • Landing page [my site name]
  • URL Found [competitor 1]
  • URL Found [competitor 2], etc. (Depending on how many competitors your export data contains.)

Metrics:

  • Rank [my site name]
  • Rank [competitor 1]
  • Rank [competitor 2], etc. (Depending on how many competitors your export data contains.)
  1. Click Save to finish the creation of the first data blend and Close the blend page. You'll be automatically sent back to your report page.
 
 
How to create a table
  1. On the report page, click Add a chart and select Table with heatmap form the list. Drag it to the page and place where you like it.
  2. Set up the metrics and dimensions for the table, as follows:

Dimensions:

  • Query
  • Landing Page
  • Landing page [my site name]
  • URL Found [competitor 1, 2, etc.]

Metrics:

  • Rank [my site name]
  • Rank [competitor 1, 2, etc.]
  • URL Clicks
  • Impressions
  • CTR

Mind that Average should be set up for every Rank metric, as well as for URL CTR. Click a small sign near the metric's name and choose the necessary setting. URL Clicks and Impressions should be marked as SUM.

How to add necessary table filters
  1. On the same page, click the Data range button on the upper toolbar, drag the data range box and place it where you like (above the table, for example).
  2. Click the Filter button, drag and place the filter box on the page.
  3. Choose Landing Page as a Dimension on the sidebar.
  4. Click Filter again. And drag the second filter box to the page. Set Landing page [my site] as a Dimension.
 

The first table is ready. It will give you a bunch of useful insights on the efficiency of the keywords you track.

The table may contain loads of data, so it would be much more convenient to analyze it page by page. Click on the Landing page filter box and click Only next to the page you want to analyze.

The URL MySite column on the screenshot shows which pages rank for certain queries. (Originally, the column was named Landing page [my site name], but I renamed it to shorten a bit.)

If you arrange the table by this column in ascending order, you may see the null value in the column cells. This means, you don't track these keywords, but your website ranks for them, and there were site impressions and clicks. Thus, if the keyword is relevant to your page, and there are many impressions for it, you may consider adding it to your campaign.

Based on the data from Google Search Console, the table has one drawback. It shows the keywords you track (and don't track) if your site appears in search for them.

But what if you target keywords nobody looks for? To see whether you have them, you need to rearrange the data sources used to create the table. This will give you a different view angle, as the table will base on the data from Rank Tracker, and show you all the target keywords with no regards to clicks or impressions. But as we want to see the whole picture, we better create another table.

 
How to
  1. On the page, click Add a chart and select Table with heatmap from the list. Drag-and-drop it below the first page.
  2. On the right sidebar, delete the data blend from the Data source module and click Blend data to create a new one.
  3. Add the same data sources as you did for the first table, but this time make Rank Tracker the first source (the one on the left). Set the same join keys, dimensions, and metrics.
  4. Set up the new table choosing Keyword and Landing page [my site name] as Dimensions. Choose URL Clicks, Impressions, CTR, and Rank [competitor 1, 2, etc.] for Metrics.
 

Click on the Impressions column heading to arrange the table results in ascending order. If you see null values for Impressions and Clicks, you probably target keywords that nobody searches.

Checking one of the keywords in Google Ads Keyword Planner, showed 10-100 estimated searches a month for it. However, as you can see, I had no site impressions for this keyword at all.

The page's low rank might have caused the lack of impressions. So try and track the keyword in progress. If there's no upward movement in impressions within a significant period, you're probably wasting your time on this keyword. (If only the keyword isn't seasonal.)

 

If your position for a keyword is high, but you still have no impressions, the keyword is doubtlessly useless.

2. Define keyword types automatically

Understanding what types of keywords your pages rank for can tell you whether you're putting efforts into the right things.

You know, that there are different intents behind keywords. So if someone types buy ice cream, it's definitely a transactional keyword that bears an intent to buy. In case it's what color is ice cream, the intent is informational. You got the point. Your specific pages must rank for the keywords of appropriate types.

It is obvious that for commercial websites, transactional queries at large generate the revenue. And the growth of branded requests will be evidence of successful advertising activities, online or offline. For a website having a blog, informational requests will also be of great interest.

 

So let's define what type of queries bring traffic to the website. We'll create another table and a chart that will help visualize the data better.

 
How to

First, we need to add a custom field to the data source.

  1. On the table page, go to Resources> Manage added data sources.
  2. Click Edit next to Google Search Console.
  3. Click Add a field and insert the following formula in a window that appeared. Let's name the field TypeOfQuery, for example.
CASE
WHEN REGEXP_MATCH(Query,"(.*can.*|.*how.*|.*is |.*what.*|.*when.*|.*why.*)") THEN "HTD"
WHEN REGEXP_MATCH(Query,"(.*brand_1.*|.*brand_2.*|.*brand_3.*)") THEN "brand"
WHEN REGEXP_MATCH(Query,"(.*prouct_1.*|.*prouct_2.*|.*prouct_3.*|.*prouct_4.*|.*prouct_5.*)") THEN "product"
ELSE "Other"
END

*Fill your brand name variations instead of the brand_1, etc.

*Fill in the types of products or services you provide, as well as all kinds of transactional modifiers, such as buy, near me, order, etc.

  1. Go back to your page and create another Table with heatmap. The table will be built on the data from the first data blend you created (Search Console-Rank Tracker), so change the source in the Data source module.
  2. Set TypeOfQuery as a Dimension. Choose URL Clicks, URL CTR, Impressions, and Landing page [my site name] for Metrics.
  3. Go to Add a chart and drag-and-drop a Pie chart on your page. Set TypeOfQuery as a Dimension. Add URL Clicks as a Metric and as a Sort.
 
 

Thus, we can analyze search queries of what type bring me traffic. In my example, the largest amount of traffic falls on queries related to the brand name. This means that I have a lot of work to do. Because I need to increase the amount of traffic I receive from transactional keywords – the traffic that greatly impacts revenue.

3. Track traffic growth and drops for each page/section of a website

It might be also interesting to group pages by their types and analyze the traffic different sections of your site receive. Moreover, it's necessary to track the traffic growth and drops at runtime. It would also be handy to know what type of queries caused the fluctuations and ranks of what type of pages drop. And knowing whether your competitors' average positions for such requests also changed may hint you at probable reasons for the change. Was it solely your website issue, or maybe there was another Google update that changed the landscape?

So, we'll need to create a table and a couple of charts to visualize this sort of necessary data.

Let's first group website pages by their types.

In general, there are different sections of a website, and each may include pages of the same type. For example, product pages, or services, blog pages, news pages, etc. The table will show how traffic splits up between different sectors of a website. Moreover, it will help analyze the effectiveness of your SEO efforts. You'll be able to see what types of keywords bring the largest part of traffic to your website, and what pages receive the largest part of the traffic. For example, if your product pages get the majority of traffic from, let's say brand queries, this may mean you're not reaching new users. This may be a signal to review your optimization tactics.

 
How to
  1. We need another custom field added to the Search Console data source. I called it TypeOfPage.

The process will be just the same as described previously. The formula for the field will be different, however.

CASE
WHEN REGEXP_MATCH(Landing Page,('https://site.com/product-page-1/|https://site.com/product-page-2/|https://site.com/product-page-3/')) THEN "product_pages"
WHEN REGEXP_MATCH(Landing Page,('https://site.com/blog/.*|https://site.com/blog/')) THEN "Blog"
WHEN REGEXP_MATCH(Landing Page,('https://site.com/compare-page-1/|https://site.com/compare-page-2/|https://site.com/compare-page-3/')) THEN "compare_pages"
WHEN REGEXP_MATCH(Landing Page,('https://site.com/reports/.*|https://site.com/reports/')) THEN "reports"
ELSE "Other"
END

*Make sure to enter your website pages instead of site.com examples.

Now you can see how your traffic splits between different types of pages.

  1. Create the same table as the previous one. But this time add TypeOfPage as the first dimension and TypeOfQuery as the second.
  2. Create another Pie chart, choosing TypeOfPage as a Dimension. Add URL Clicks as a Metric and as a Sort.
 
Analyzing traffic at runtime

The tables and charts above show the static data. But this view doesn't show the traffic at runtime. So let's create a couple of charts to compare the data within different periods. In this example, I will compare the data for July 2019 to July 2018.

 
How to
  1. On the report page, click Add a chart on the upper toolbar, and select a Time series chart out of the list. Drag the chart to any place of the page you like.
  2. The Dimension will be automatically set to Date. Hover on the little calendar sign next to Date. It will turn to a pencil sign you'll need to click to set up the date type.
  3. Choose Date and Time as the data type, and specify it as Year Month (YYYYMM).
  1. Click again the Date dimension to edit it. In the Show as field, choose Month to group the data by months.
  1. Select the TypeOfQuery field as the second dimension.
  2. In the Default data range module, click Custom and choose This year to date as the date range for our graph.
  3. Set Comparison Date Range as Previous year.
 

Now we can see the traffic changes at runtime for different types of pages and queries. In case your site experiences a traffic drop, you'll immediately know where performance has worsened.

Comparing a website's ranks against those of the competitors

We can also create a chart that shows how the website's average rank changed compared to the competitors.

 
How to
  1. Go to Add a chart and choose Line from the list.
  2. Repeat the process as described above, but choose Rank [site name] as a Metric.
 
 

I exported the data for a short period, so my graph lacks information. If you have saved historic data for a longer period, your chart will look much more interesting.

4. Measure the traffic/conversion potential of target keywords

Google Analytics doesn't provide information about keywords that lead to conversions. This data is hidden behind (not set)/(not provided) signs. This is very bad for you as an SEO, as you can't see which of your keywords bring not only traffic but also conversions. Obviously, the latter is more useful for an SEO campaign.

However, if we analyze the Analytics data combined with the data from Search Console, for example, we may suggest which keywords convert. Here's the data that, when mixed the right way, may help disclose a few secrets:

  • Search queries
  • Clicks on page
  • The date a click occurred
  • The date a conversion occurred

Let's merge this data into a single table. It will contain all search queries that had more than one click leading to a certain page. It will also include the data about conversions that occurred on a certain page the same day. This will let us very likely define what search query resulted in a conversion. (Yes, it's still a probability, but this method suits any project, and after all, it's free.)

 
Let's get started
  1. Add a new calculated field existQuery to the Search Console data source. This field will let us ensure there was a query on a certain date.

Use the following formula:

CASE
WHEN URL Clicks>0 THEN 1
ELSE 0
END
  1. Add a new calculated field SignUpGoals to the Search Console data source. This field will let us ensure there was a query on a certain date.

Use the following formula:

CASE
WHEN SignUPGoals >0 THEN 1 ELSE 0
END
  1. Create a new data blend for Google Analytics and Search Console .
  2. Select Date and ConcatURL as keys for Analytics.
  3. Select Date and Landing page as keys for Search Console.
  4. Add metrics:

For Google Analytics:

  • Different Goals you track
  • SignUpGoals
  • Goal Completions

For Google Search Console:

  • Query
  • TypeOfPage
  • HyperQuery
  • URL Clicks
  • URL CTR
  • Impressions
  • existQuery — to verify whether there was a click on a query

And set up a filter for Google Search Console:

  • Source Filter — to cut off the data apart from google / organic

Now, let's get to the table itself.

  1. Click Add a chart and select a Pivot table with heatmap from the list.
    Set up the necessary dimensions and metrics:

Row Dimensions

  • Date
  • HyperQuery
  • TypeOfQuery

Column Dimensions

  • ConcatURL

Metrics

  • URL Clicks
  • Impressions
  • SignUPGoals
  • ConversionRate — you need to create this metric:

Click Add metric> Create field. Name it ConversionRate and enter the following formula

SignUPGoals/URL Clicks+URL Clicks/SignUPGoals)/100*existGoalComplete
  • existQuery

Sorting Row #1

 

Thus, we can see the number of conversions for each keyword. Again, as the table comprises a huge amount of data, it's better to filter it and analyze the data on a page level.

 
To add filters
  1. On the table page, click the Filter sign on the upper toolbar, drag and place the filter box to any place on the page. We'll need to create two different filters: TypeOfQuery and ConcatURL.
  2. Choose the necessary filter dimensions on the right sidebar: TypeOfQuery and ConcatURL for the respective filters.
  3. For each filter, choose URL Clicks as Metrics.
 

If we add the third data source to the blend we use in this table, we'll see the average page's position for a keyword. This will also help assess keywords' conversion potential.

For example, find keywords that don't convert even though you rank very well and have lots of your page impressions for them. This might mean that either you target the wrong keywords, or your pages lack something that makes users convert. So you'll easily see the areas you need to improve.

 
How to
  1. Click the edit sign near the table data source on the right side-bar.
  2. Click Add next to the existing data sources and add Rank Tracker as the third source.
  3. Use Check Date and Landing Page as keys.
  4. Create a Pivot table with heatmap and set up all the necessary fields on the right sidebar:

Row Dimensions

  • HyperQuery
  • TypeOfQuery

Column Dimensions

  • ConcatURL

Metrics

  • URL Clicks
  • Impressions
  • URL CTR
  • AvgRankMysite
  • SignUPGoals
  • ConversionRate
  • existGoalComplete — the number of completed goals within a period when a click occurred.
  • existQuery

Sorting Row #1

  • ConversionRate

Filter

  • Plus, set up a filter that will cut off all the keywords that had no clicks, as follows:

Don't forget to add the necessary filters to be able to analyze the data more conveniently, page-by-page.

 

Let's take a closer look at the columns and what exactly they show. I intentionally provided the results for one page and filtered the data to simplify the explanation.

  • URL Clicks — the sum of clicks within a certain period.
  • Impressions — the sum of keyword impressions, in case there was at least one click on a keyword. So the data on the times there were no clicks is not taken into account.
  • URL CTR — the average keyword CTR, in case there was at least one click on a keyword.
  • AvgRankMySite — the average page's rank for all the keywords assigned to a certain page in Rank Tracker.
  • SignUPGoals — is a consolidated value of all the goals completed on a page in a period when clicks occurred.
  • ConversionRate — a conditional value that shows the keyword's conversion potential. It's calculated as (SignUPGoals/URL Clicks+URL Clicks/SignUPGoals)/100*existGoalComplete).
     
    A little sidenote. I know that there are other, easier and more correct ways of calculation. But due to the Data Studio limitations, they are not applicable here. You may read this piece for more details.
  • existGoalComplete — the number of completed goals within a period when a click occurred.
  • existQuery — the number of days when clicks occurred.

Analyzing this data set, I found out that the site's homepage receives 90% of traffic from branded keywords. And this traffic converts quite well.

Here's an example of another page analysis. It is a blog publication. And though the page appears in search for many keywords, none of them resulted in a conversion.

The surprise, however, came as I extended the period under review. I managed to find a couple of search queries that converted on this page. And they were quite unexpected.

So, the bigger the period you analyze, the more likely you'll find something useful.

Sharing your Data Studio dashboard with your team

Reporting is an important part of an SEO campaign. Keeping all the interests informed may require a lot of effort. So the possibility to easily share a single report with the involved team members would be of great help.

Sharing access to tables and dashboards in Data Studio is very similar to that in Google Docs. It can be done through shareable links or email invitations. You may grant different rights to team members making them able either to view or edit the dashboard's contents.

In all my examples, I used Rank Tracker, as one of the data sources. However, you may add the needed data from any other SEO PowerSuite tools to Data Studio dashboards.

Download SEO PowerSuite

Conclusion

What's so great about Data Studio? Despite several limitations, it's a great data visualization and analytics tool. Combining different data sets in different variations may help you see a bigger picture of your project and gives you lots of useful insights. It can save you the time you'd otherwise spent on analyzing data in different tools separately and creating separate reports for each. Moreover, it's free, and I believe it's going to develop more functionality in due time.

Article stats:
Linking websites N/A
Backlinks N/A
InLink Rank N/A
Data from Seo SpyGlass: try free backlink checker.
You may also like
Got questions or comments?
Join our community on Facebook!
Huge SEO PowerSuite sale is coming SEPTEMBER 24-26 SEP 24-26