Creating your Conversion Funnel in Excel
17/01/2014 | Written by | Categories: Analysis and Insight

Conversion funnel diagrams are incredibly useful to understand how visitors go through your website to achieve a goal. Most Web Analytics tools provide reports to show the conversion rate between each key stage but they don’t often show exactly the information I need. Therefore, when I analyse a new website, I love drawing up my own funnels. It helps to understand how the website works and can provide additional insights:

  • Drawing up your conversion paths is useful to identify all stages
  • Analysing optional steps can be painful with standard funnel reports
  • Discovering quick wins to improve the customer journey becomes easier

1 – Making a draft with a pencil

To start, we only need a pencil and a piece of paper. There is no need to do anything beautiful at this stage. The aim is to represent required and optional steps to complete a goal. For example, to order online through a retail website visitors might go through the following stages:


Here two steps are optional: The Login and Billing Address pages.

2 – Representing the funnel in Excel

The next task consists of reproducing the draft in Excel. This can be achieved by many methods and there is no need to be an Excel Guru. For example I could use this type of template:


Each rectangle represents a stage. At the top, we can see the number of visits starting the stage and at the bottom, the number of visits completing it. The step completion rate which is the ratio between both metrics is shown at the left:


The checkout completion rate represents the overall performance of the checkout process. It’s the number of visits viewing the confirmation page divided by the number of visits starting the checkout process:


3 – Filling in the data

The data can be imported from the web analytics tool. A fast method is to simply manually enter data from the web analytics tool into the excel file (a longer term solution is to use the API or an Excel plugin).

Conversion Funnel

After a quick look at the drawing, I can see that the completion rate for the Login and Payment details are lower than the other ones. I should look at those two stages more into details to see if we can identify any quick wins to improve the checkout process.

Going further into the analysis

To understand why a step performs less than the other ones, I love comparing the data for a few key segments. For example, looking at the step completion rates by browser or device category is a good approach as we can quickly identify levers to improve the overall website conversion rate.

What you can end up with is a diagnostic data matrix and we will study this method more in details in the next blog post…

Finally, your turn – Have you ever felt the need to draw up your own funnel to have a better understanding of your website conversion paths? Would you go outside your standard Web Analytics reports to look at the performance of all optional steps? What are your favourite methods/tools to visualise funnels?

Leave a Reply

Your email address will not be published. Required fields are marked *

Looking to improve your digital analytics or conversion rate optimisation?
Contact us today on +44 (0)20 7148 5970

Alternatively, come visit us in London and we’ll buy the coffee!
Get in touch
Leave us a message
Use the form below to leave us a message.

Lindsey Street