Custom reports

Monitor your traffic with Google Data Studio after a redirection plan

July 27, 2021 - by Mehdi Oudjida

The preparation & the deployment of a URLs redirection plan require method, know-how, a lot of rigor of execution, involving various digital team members and expertises (from traffic management to IT services).

Because of its high weight of impacts, and also its high probability to not cover all cases of URLs, a project of redirections must involve:

  • Preventive actions to be sure to not break user experiences
  • Anticipating about palliative & patch solutions, and the related process, if problems occur after the deployment
  • And of course, ways to control how it goes with medium to communicate about the website health to your stakeholders and clients

Let’s take care about the last topic, creating a visual Data Studio report to control the traffic of our website after the release of a redirection plan and getting first insights.

Google Data Studio report to monitor your traffic after a URLs redirection plan

Table of contents

Your requirements

The tutorial below is just an example to get details about how to configure this redirection report using basic data collected by Google Analytics (GA) & Google Search Console (GSC).

But of course, for each business case, it should be adapted to meet the following requirements:

  • Include conversion (KPIs) & traffic metrics to balance the potential loss & errors with factual business impacts
  • Design simple & light visualisations, understandable by everybody, keeping in mind that it is not just a technical tool of monitoring but also a communication medium (“Everything is under control”)
  • Made with filters and interactive data controls for a first level of exploration
  • Reuse the already made redirection plan – this time – for monitoring needs. It was made for referencing and technical rewriting purposes, making it as a part of the reporting device is effortless and a new way to get a return of invested time on this mapping
  • Report built to be reusable for another future redirections plan for the same web application, for another site, for another client.

Example of use case & ingredients

In the dummy example below, redirections has been released the 6th of April and the redirection plan consists to rewrite 49 URLs with a simple 1-to-1 relation (1 new URL replacing 1 previous URL). The report is built with the following elements on 2 pages, one page for Google Analytics and another page for Google Search console:

  • A panel to filter data on main dimensions for simple exploration (previous or new version of the URL, date of migration, page category, URL search and channel for GA data)
  • Some metrics to summarize the context of displayed data. In your custom report, we can imagine to include in this section also some business KPIs. Feel free to adapt it following your needs
  • A stacked area chart to see the evolution or metric, after previous URLs have been replaced by the new ones
  • A table chart to get details about each pair of URLs and the related traffic metrics before & after the redirection release
  • At last, for GA data only, a 100% stacked bar chart with entrances by channel to control the traffic source recognition

To fill these charts, with need some components behind the scenes:

  • The URLs redirection plan, stored in Google Sheets with data prepared and optimized for Data Studio usage
  • Google Analytics (GA) data source
  • Google Search Console (GSC) data source with URLs statistics (not site ones)
  • 2 blended data sources mixing the redirection plan data with GA data, and the same for GSC data
  • Some calculated fields

The result

With the short video below, let’s see how Google Analytics data can help us to confirm that all URLs are correctly redirected and they still generate page views, without impact on the channels of traffic sources (landing & recognition).

According to Google Search Console data, how long does it take to get the first impressions for new URLs on Google search results page? Indexing problems with some URLs? Which ones?

Google Sheets data source with redirection plan data

First, to build this report, we need to prepare our redirection plan data before loading it as a data source inside Google Data Studio.

Regarding most of cases, it is a reference table with new defined URLs (or patterns of URLs) corresponding to existing legacy URLs, additionally decorated with some fields (columns) to qualify them, get additional info like categories, ids and migration date in the example below.

Simple redirection plan

For our reporting, we apply some changes to take in account how Google Data Studio can blend data across several temporal states of the application: The same page will not get the same URL value following the time but we need to display it as a trend continuity.

We will apply the following adaptations:

  • Keep qualification fields like Product id, Page category and Migration date
  • Duplicate rows with Old URL and New URL values in dedicated column
  • Create a “URL” and a “Old or new” new column. Fill them with consistency to get one row with the new URL and another with the old one as value of “URL” column

See below the result. Finally, we will get 2 rows by redirection and all data on each row: Product id, Old URL, New URL, URL (current row URL), Old or new (for the current row URL), Page category and Migration date

Data of the redirection plan, adapted to reporting needs

To do so, inside Google Sheets, I like to use QUERY function to request data from the input format of the original redirection plan & build the required format for Data Studio needs.
Below an example of Google Sheets formula to duplicate rows and apply simple adaptations (Union of 2 queries with hard-written value).

For performance purposes, when you’ve finished to prepare redirection plan data, copy the final result as values in a dedicated Google Sheet tab before using it in Data Studio un connect your data source.

Blended data

Data are ready to be used inside Data Studio. Now, add them as data sources of your report:

  • Google Analytics view
  • URLs data from your Google Search console property (Table : URL impression / Property parameter : web)
  • Redirection plan prepared data stored on Google Sheets

Then create the following blended data for Google Analytics report page:

  • As left data source
    • Data source: The redirection plan from Google Sheets file
    • Join keys: URL
    • Dimensions: Old URL, New URL, Migration date, Old or new
  • As right data source
    • Data source: Google Analytics view
    • Join keys: Page
    • Dimensions: Page, Date, Default Channel Grouping
    • Metrics: Pageviews, Entrances for this example
Blended data to mix redirection plan with Google Analytics

Let’s do the same for SEO purposes, blending data from Google Search Console & our redirection plan, stored in Google Sheets and prepared for Data Studio usage:

  • As left data source
    • Data source: The redirection plan from Google Sheets file
    • Join keys: URL
    • Dimensions: Old URL, New URL, Migration date, Old or new
  • As right data source
    • Data source: Google Analytics view
    • Join keys: Landing page path*
    • Dimensions: Landing page path, Date
    • Metrics: Impressions for this example
Blended data to mix redirection plan with Google Search Console data

* For the data blending above, you need to create a calculated field “Landing page path” using “Landing Page” provided field from the Search Console and remove the protocol and the hostname. Thanks to a recent released feature, you can create calculated fields directly for blend data.

Landing page path
REGEXP_EXTRACT(Landing Page,'^https://[^/]+(/.*)$')

Reporting design

Now, blended data are ready an we can design dashboard charts with statistics from Google Analytics for the first page, and Google Search console for the second one, both widened by data context & reconciliation, thanks to our redirection plan.

Reporting – 1. Panel of filter controls

Data Studio report - Filter controls

The top of page report is quite easy to configure. It is a set of fixed-sized lists of controls, using directly each data source and without requiring to use blended data but original data sources. The selected values will be cascaded to blended data using this original data sources.

As example, for Google Analytics page, I chose to filter by:

  • Old or new URL
  • Migration date
  • Page category
  • New URL
  • Default channel grouping (Google analytics)

Reporting – 2. Scorecards for data context

Data Studio report - Scorecards

As a first slice of monitored data, we create a scorecards section containing metrics to control for New and Old URLs:

  • Number of days before or after the redirection plan
  • Absolute number of URLs from the redirection plan
  • Number of URLs with traffic (for Google Analytics, and the number of URLs with impressions for Search Console)

Number of days before the redirection plan (For Google Analytics and Google Search Console, so using each blended data) – at the top left corner of the section 2 above

Create a table chart with one row, applying the configuration below:

  • Chart type: Table
  • Data tab
    • Dimension: the chart calculated field with the formula below
    • Row per page: 1
    • Sort: The calculated field below, descending
  • Style tab
    • Uncheck “Show header”
    • Chart header: Do not show
Number of days before the redirection plan
DATE_DIFF(Migration date,Date)

Number of days after the redirection plan (For GA and GSC, so using each blended data) – at the top right corner of the section 2 above

Create a table chart with one row, applying the configuration below:

  • Chart type: Table
  • Data tab
    • Dimension: the chart calculated field with the formula below
    • Row per page: 1
    • Sort: The calculated field below, descending
  • Style tab
    • Uncheck “Show header”
    • Chart header: Do not show
Number of days after the redirection plan
DATE_DIFF(Date, Migration date)-1

Number of URLs before the redirection plan (For GA and GSC, so using each blended data)

Create a scorecard chart, applying the configuration below:

  • Chart type: Scorecard
  • Data tab
    • Metric: the chart calculated field with the formula below
  • Style tab
    • Missing data: Show “0”
Number of URLs before the redirection plan
COUNT_DISTINCT(Old URL)

Number of URLs after the redirection plan (For GA and GSC, so using each blended data)

Create a scorecard chart, applying the configuration below:

  • Chart type: Scorecard
  • Data tab
    • Metric: the chart calculated field with the formula below
  • Style tab
    • Missing data: Show “0”
Number of URLs after the redirection plan
COUNT_DISTINCT(New URL)

Number of URLs with traffic before the redirection plan (For Google Analytics, so using GA blended data)

Scorecard chart configuration:

  • Chart type: Scorecard
  • Data tab
    • Data source: Blended data with redirection plan and GA data
    • Metric: the chart calculated field with the formula below
    • Scorecard filter: “Only old URL” (see below, common to GSC for the same scorecard)
  • Style tab
    • Missing data: Show “0”
Nb of URLs with traffic before the redirection plan
COUNT_DISTINCT(Page)

Number of URLs with impressions before the redirection plan (For Google Search Console, so using GSC blended data)

Scorecard chart configuration:

  • Chart type: Scorecard
  • Data tab
    • Data source: Blended data with redirection plan and GSC data
    • Metric: the chart calculated field with the formula below
    • Scorecard filter: “Only old URL” (see below, common to GA for the same scorecard)
  • Style tab
    • Missing data: Show “0”
Nb of URLs with impressions before the redirection plan
COUNT_DISTINCT(Landing page path)

Only old URL chart filter (to create for GA and GSC blended data source)

Data Studio report filter - Old URLs

Number of URLs with traffic after the redirection plan (For Google Analytics, so using GA blended data)

Scorecard chart configuration:

  • Chart type: Scorecard
  • Data tab
    • Data source: Blended data with redirection plan and GA data
    • Metric: the chart calculated field with the formula below
    • Scorecard filter: “Only old URL” (see below, common to GSC for the same scorecard)
  • Style tab
    • Missing data: Show “0”
Nb of URLs with traffic after the redirection plan
COUNT_DISTINCT(Page)

Number of URLs with impressions after the redirection plan (For Google Search Console, so using GSC blended data)

Scorecard chart configuration:

  • Chart type: Scorecard
  • Data tab
    • Data source: Blended data with redirection plan and GSC data
    • Metric: the chart calculated field with the formula below
    • Scorecard filter: “Only new URL” (see below, common to GA for the same scorecard)
  • Style tab
    • Missing data: Show “0”
Nb of URLs with impressions after the redirection plan
COUNT_DISTINCT(Landing page path)

Only new URL chart filter (to create for GA and GSC blended data source)

Data Studio report filter - New URLs

Reporting – 3. Continuous trend of URL metrics

Data Studio report - Line chart

Now, let’s build the chart I prefer because it is visual and show quickly the temporal trend situation. Like in the scorecards section, the blue depicts the old URLs and the orange is our concerns, the new URLs.

The chart configuration is quite similar between Google Analytics and Google Search Console.

Configuration for Google Analytics data

  • Chart type: Stacked area chart
  • Data tab
    • Data source: Blended data with redirection plan and GA data
    • Dimension: Date
    • Breakdown dimension: “Old or new”
    • Metric: “Pageviews”
    • Sorting: “Old or new” descending
    • Interaction: Check “Apply filter”
  • Style tab
    • Area chart: Check “Show Stack”
    • Color by; Dimension values (blue for “Old URL” and orange for “New URL”)
    • Missing data: line to zero

Configuration for Google Search Console data

  • Chart type: Stacked area chart
  • Data tab
    • Data source: Blended data with redirection plan and GSC data
    • Dimension: Date
    • Breakdown dimension: “Old or new”
    • Metric: “Impressions“
    • Sorting: “Old or new” descending
    • Interaction: Check “Apply filter”
  • Style tab
    • Area chart: Check “Show Stack”
    • Color by; Dimension values (blue for “Old URL” and orange for “New URL”)
    • Missing data: line to zero

Reporting – 4. Detailed table of URLs

Data Studio report - Table chart

Then, we add the table chart to get data with page granularity. Clicking on one specific row, we allow to filter the area chart & get the related trend.

Chart configuration for Google Analytics data

  • Chart type: Table chart
  • Data tab
    • Data source: Blended data with redirection plan and GA data
    • Dimensions: New URL, Old URL
    • Metrics (3 calculated fields – formulas below): Pageviews, Pageviews before redirection, Pageviews after redirection
    • Sorting: Pageviews descending
    • Interaction: Check “Apply filter”
  • Style tab
    • Adapt style options following your layout
    • You can also add conditional formatting for page without page views after the date of release of the redirection plan
Pageviews
IF(Pageviews IS NULL, 0,Pageviews)
Pageviews before redirection
IF(DATE_DIFF(Date,Migration date)<0,Pageviews,0)
Pageviews after redirection
IF(DATE_DIFF(Date,Migration date)>0,Pageviews,0)

Chart configuration for Google Search Console data

  • Chart type: Table chart
  • Data tab
    • Data source: Blended data with redirection plan and GSC data
    • Dimensions: New URL, Old URL
    • Metrics (3 calculated fields – formulas below): Impressions, Impressions before redirection, Impressions after redirection
    • Sorting: Impressions descending
    • Interaction: Check “Apply filter”
  • Style tab
    • –
Impressions
IF(Impressions IS NULL, 0,Impressions)
Impressions before redirection
IF(DATE_DIFF(Date,Migration date)<0,Impressions,0)
Impressions after redirection
IF(DATE_DIFF(Date,Migration date)>0,Impressions,0)

Reporting – 5. Entrances by channel (only for Google Analytics data)

Data Studio redirection plan report - Stacked bar chart

At last, only for Google Analytics, we will end the page with a way to check if the redirection plan has not affected the traffic source recognition or the landing of particular traffic acquisition channels.

  • Chart type: 100% stacked bar chart
  • Data tab
    • Data source: Blended data with redirection plan and GA data
    • Dimension: “Old or new”
    • Breakdown dimension: Default Channel Grouping
    • Metric: Entrances
    • Sorting: “Old or new” descending, then Entrances descending
    • Interaction: Check “Apply filter”
  • Style tab
    • Bar chart: 2 bars, 5 series or more regarding you channels mix, checking stacked bars and 100% stacking
    • Color by dimension values
    • Check “Show axes”
    • Display the legend

Configuration review

This tutorial is long, with a lot of steps, also quite simple but we definitely need to review the full configuration with these videos.

Let’s begin with Google Analytics page…

Then, here how the report configuration of Google Search console page looks like.

Final thoughts

  • The result of this tutorial is just an example with basic metrics, common to every context. It can be applied to other business and measurement use case
    • Feel free to include your own KPIs in addition
    • Transpose these techniques to any value mutation with temporal follow-up.
      Example: I improved the page experience and performance with consecutive actions (with the related scores), how they affect my search or site centric metrics?
  • For agencies or multi websites owners, this recipe can be easily enhanced to be used for several websites with some additions: GA/ GSC data control, filtering fields in the redirection plan…
  • Monitoring a redirection plan is also taking care about peak of page not found views (404). I’ve already written in other blog post concerning this subject and how to follow it through Data Studio. We can imagine to combine this redirection plan report, and 404 report in the same Data Studio file.

“Cruchot ! Finis les bêtises, tu retournes à la circulation ! Allez hop !”