Category: UX advice / Research articles

Build your own website dashboard for free – tracking a user journey over time

Conversion funnels tracking how many users go through different stages of a website are very useful. The problem is that they’re generally just a single time snapshot of what is happening—certainly this is the case with Google Analytics funnels.

What I find more useful is something that allows you to see how that funnel is changing over time. It helps me to see if changes I make to the website are having a long term effect on conversion rates. It also means that if a problem occurs and conversion rate of a step occurs then it will stand out against historic trends.

I pull my Google Analytics data into a Google Spreadsheet via a free tool. There are a few benefits to having a dashboard like this:

Perhaps most importantly, you don’t need to be an Excel or spreadsheet wizard to get this to work.

Connect Analytics with a spreadsheet

What you’ll need:

Before anything, you’ll need to set yourself up with a Google Analytics (GA) account. So assuming you’ve got that sorted and you’re logged into that Google account, tackle the following steps…

  1. Create a new Google spreadsheet and from the Add-ons menu choose ‘Get Add-ons…’
  2. In the pop-up that appears search for ‘supermetrics’ in the top right.
  3. Click the blue button marked ‘FREE’ and allow it the permissions it needs.
  4. Now you should have a right side panel activated, choose GA from the options provided and allow the two to connect.
  5. Select the profile to build a dashboard for from the sites you track in GA.

You now have everything lined up to pull your data into the sheet and track it as you wish. The next section will explain what to consider before grabbing the numbers.

Consider your dashboard content

Dashboards can contain almost anything but the key things to show on this dashboard is a measure of success for each step of the user journey. Identify the journey your users need to take to reach the goal you want them to achieve.

For example if you are an ecommerce site you will likely follow the landing page > listings > product page > checkout > purchase template. If you sell a service your user flow might be landing page > pricing plan > details > sign up complete.

The main thing to track is pageviews, sessions, or users for each step of the flow—it doesn’t massively matter which as long as you are consistent and use the same metric across each step of the journey. I’ve used sessions when measuring the overall goal of purchases, as a user is only going to purchase once in a session whilst they can have many more pageviews, so it acts as a more appropriate relative guide. If it’s a sign up process where a user can only do it once then it makes sense to track users (or unique visitors).

Metrics on a dashboard spreadsheet

This ultimately is the core of what you need on your dashboard but there are a few other handy things to look at. An engagement metric per section helps gives some clues as to how users are behaving on the page. Average time on page will tell you if people are taking a long time or a short time to complete tasks or browse, whilst bounce rate will tell you how appealing landing pages are.

There’s a danger of getting carried away and putting too much data in there, better to keep it simple and answer some key questions that you regularly care about or it’s easy to get lost in a sea of unnecessary numbers. On a one-off basis you can always check the main GA interface to find out other information.

Populate data with Supermetrics

Once you’ve done the initial Supermetrics setup and have identified the pages you want to track to follow the user journey, now you just need to get the data in place. Here’s how to create a sheet like the above that displays a user journey over time.

Each change in data (e.g. specifying listings pages vs product pages) will require a new ‘query’ to be set up in the Supermetrics sidebar. If you have four steps in your flow you can do it with four queries.

The Supermetrics sidebar

Select dates

Here you can choose a variety of preset date options. If you want to create a current/live view of what is happening then a pro-tip is to put ‘yesterday’ or ‘today’ into the end field to make it dynamic.

You can also choose to compare your date range with a previous one. In the past I’ve found doing year on year (assuming you’ve got enough data in the past) a handy one to put current performance into context. This means for every column of data you get it will alternate between this year and last year. You can choose for it to show a percentage change in those metrics over time, the plus or minus actual number change or just the raw value.

Select metrics

Here’s where you select the metric you want to measure. And we want something to tell us the number of users on a page. You can pick one of users (formerly unique visitors), sessions (formerly visits) or pageviews for this—this link does a decent job of explaining the difference. Whichever you choose just make sure you use the same one throughout your sheet.

We also might track an engagement metric per page, so you can specify this here too, and it will pull them both in together.

Split by

I like to split it by date—either week or month. Choose ‘split to columns’ to get time going left to right across your doc. Tip: make sure you choose plenty of categories (I like 50 or 100) in order to be able to just come back and hit refresh and see your latest week’s data pop into the sheet without you having to do anything. If you only have 10 categories, it will only pull in 10 weeks worth of data.


This is the spot to specify your results as either being a specific type of traffic (direct, search, paid etc) or you can choose to see user behaviour from different devices (tablet, mobile or more specifically Android or iOS). It can be worth having the same journey shown on separate sheets for desktop, tablet and mobile.


Filtering is the bit where you declare which part of the overall metric you’re interested in. If you just choose a metric without a filter, you’ll see aggregated totals for the whole website.

We want things to be a bit more granular. For our purposes of mapping a user journey the filter we need is ‘page path’, which allows you to enter the url or use regex to specify the page of the website you want to track. How easy this is to define will depend on how clearly your website URLs are structured.

Editing/modifying queries

To go back and edit the content you’re pulling into a row you need select a cell with Supermetrics content in it. The Supermetrics panel on the right will show details of the query. Then simply hit the ‘Modify’ button under ‘Query actions’ which will then bring up that row/column’s settings in the sidebar.

Once you’re done making your edits click ‘Apply changes’ for the content to update and then make sure you click ‘Exit’ after that. This is important otherwise you can go around thinking you’re editing new cells but you’re actually changing the one you didn’t exit from.

Hiding cells

By default, if you’re splitting over time you get a date row with every metric you import in. It’s obviously handy for checking which date each piece of data refers to but it gets in the way if you repeat it all the way down your sheet. To turn it off, tick the checkbox ‘No header row’ under ‘Options’.

All done!

Now, when you want to see the latest content on your sheet, just open it up and go Add-ons > Supermetrics > Refresh All and it will automatically populate the latest live content.

Show the journey

Once you’ve got your metric for each page on the sheet, you can then display the relationship between each step. If you’ve got a four step process, you’ll want to see the conversion rate (or success rate) at getting users from step one to step two and through to step four.

To do this create some new rows showing the percentage change between users present on each step: the second divided by the first. You can then finish by showing an overall conversion metric of conversions divided by number of visitors to the site.

User journey conversion rate on a dashboard

This is your conversion rate for each step in your funnel and it will enable you to determine the success of each step. This is the main part of the dashboard to glance at and get an overview of your site performance. Watching the trends week to week (or month to month) will tell you whether it is performing consistently or whether something has changed.

If you release a change to the second step and you see its conversion drop compared to the past, you’ll know that something about it isn’t working. If it goes up but the third step’s conversion falls then you’ll know that despite improving step two, users are now dropping off further along—so time to focus your design efforts on that area.

If you want more ideas for how to dig into why things are happening on your website, take a look at my Evidence-Based UX Design Methods and the framework for using them.

Last updated on 28 October 2019
14 tools to gather evidence – guide

Articles on similar topics

An evidence-based framework for redesigning any website

Your website’s persona – how to quickly find out with Google Analytics data