Looker Studio SEO dashboard. 2. Universal Google Analytics

I am back with a new chapter of SEO report in Looker Studio! Just to remind you, in the previous article we have connected some data sources like Google Analytics (GA4 and UA) and Google Search Console, reviewed filters and segments.

In this chapter I’m going to create a SEO report using Universal analytics.

Enjoy the video review that I have recorded:

Report’s KPIs

Usually, one of the first sections of my reports is KPIs. This way I present what we are going to deal with in the report. To complete the KPI section I use score boxes.

I create a score box with Sessions, Bounce Rate, Average Session Duration, Pages / Session, Goal Completions and Goal Conversion Rate with organic traffic segments and comparison to the previous period. 

Creating a blended data source

To compare organic traffic to all website data, I will use data blending. I select the score box →Data sources in the Setup menu → + Blend data.

The first table is our Google Analytics UA. I want to add a second table from the same data source, so it is Google Analytics UA. The dimension of both the tables is Date. The join – Left outer join. Field to join – Date. After this, we add all the metrics: Sessions, Bounce, Session Duration, Pageviews, Goal Completions. Consequently, I will remove the segment and rename all the fields like this: All Sessions, All Bounces, All Session Duration, All Pageviews, All Goal Completions. 

You need to Add the same metrics to the second table and also add an Organic traffic segment here. Later, we rename this blended data source to UA + UA Organic and it is ready to use!

Comparing organic traffic to all website data

Using our blended data source, we can create some custom formulas that basically allow us to compare the data. Here are the formulas that I’ve used:

% of all sessions: Sum (Sessions) / Sum (All Sessions). 

Type – Percentage

All Bounce rate: Sum (All bounces) / Sum (All sessions)

Difference bounce rate: Sum (Bounces) / Sum (Sessions) – Sum (All bounces) / Sum (All sessions)

Difference session duration:  Sum (All session duration) / Sum (All sessions)

Type – Duration

Difference pages/sessions: Sum (Pageviews) / Sum (Sessions) – Sum (All ageviews) / Sum (All Sessions)

% of goals completion: Sum (Goal completion) / Sum (All Conv.)

Type – Percentage

Difference conversion rate: Sum (Goal Completions) / Sum (Sessions) – Sum (All Conversions) / Sum (All Sessions) 

Sessions in dynamic and popular pages

To see sessions in dynamic I use a time series chart. The setting for the chart are following:

Dimension: Date

Breakdown Dimension: Source

Metric: Sessions

Segment: Organic traffic

I created a simple table to show Organic traffic popular pages. Don’t forget the Organic Traffic segment. To create this table I add all our main KPI metrics to split them by pages and I enable the summary row. Analyzing this table lets us see what page performs better. 

To see the pages in dynamic, I create a pivot table with a heatmap that has Sessions as a metric and Month of the Year as a column dimension. 

If you are curious about Looker Studio and you want to get a profound knowledge about this data visualization tool, I invite you to my Looker Studio course! Read more about it.

Hope this article was interesting and educational for you! Tell me your impressions in the comments section! Read more articles in my Medium!