Building a Custom Open Funnel Dashboard for Conversion and Abandonment Rates in Looker Studio

Hi, I have joined YUZURIHA a few months ago and also started my journey in learning marketing automation for our client's project. I've tried this and that to create comprehensive dashboards for some metrics that can help us identify issues, improve the service etc.

Therefore, for this article I will try to write about how to make a custom dashboard for conversion and abandonment rates in Looker Studio (former Google Data Studio).

:::note warn 
Looker Studio and Looker are two different tools. The documentation and functionality it's completely different so be careful when searching. 
:::

# Why are conversion and abandonment rate dashboards important?
If you already know the basics, [jump here](https://qiita.com/karina-yzrh/items/8cf719ceb494a1f53a8e#how-to-make-a-conversion-and-abandonment-rate-dashboard).

Monitoring conversion and abandonment rates is crucial for understanding user behavior and optimizing business performance.

### Conversion rate 
Conversion rate is a critical metric that represents the percentage of users who complete a specific goal out of the total number of visitors. For instance, if 100 users visit your site and 5 make a purchase, your conversion rate is 5%.

### Abandonment rate 
Measures the percentage of users who initiate a process on your website but leave before completing it. This metric is often used to assess the performance of shopping carts, forms, and other multi-step processes. 

For example, if 200 users add items to their cart but only 50 complete the purchase, the abandonment rate is 75%. 

High abandonment rates can signal problems such as complex checkout processes, lack of trust, or technical issues. Understanding and reducing abandonment rates can significantly improve user experience and increase the likelihood of conversions.

# Why use Looker Studio to create dashboards?
:::note info
TL;DR: Basically, it's more powerful, customizable and better looking than Google Analytics exploration dashboard. You can share it with your customers or shareholders in a clean comprehensible format that matches the style of the business or presentation ;)
::: 
<dl>
  <dt>Flexible Design</dt>
  <dd>Looker Studio provides a high degree of customization, allowing you to design dashboards that match your brand’s look and feel. You can customize colors, fonts, and layout to create visually appealing reports that are easy to read and interpret</dd>
  <dt>Multiple Data Sources</dt>
  <dd>Looker Studio can connect to a wide variety of data sources beyond Google Analytics, including databases, spreadsheets, and other third-party tools</dd>
  <dt>Interactive Elements</dt>
  <dd>Enhance user engagement with interactive elements such as filters, date range controls, and clickable charts. These features make it easier for users to explore the data and gain insights without needing to navigate away from the dashboard</dd>
  <dt>User-Friendly Interface</dt>
  <dd>Looker Studio’s intuitive drag-and-drop interface makes it accessible even for those with limited technical skills. You can quickly build and modify dashboards without needing to write code or have advanced knowledge of data analytics</dd>
</dl>

Looker Studio is free although it has a Pro version. Mostly you can create a descent dashboard with the free version but, in case you're curious check the official site.

Now let's jump to the good part.

# How to make a conversion and abandonment rate dashboard?
Here are the basic steps:

1. Identify the site event you want to measure as conversion
1. Identify the steps a user takes to complete a conversion
1. Set up your events and key events (former "conversions" in google analytics) in your site. Optionally with GTM
1. Create a Looker Studio account
1. Create a blank report and set your data source (choose Google Analytics)
1. Set up your charts and give them style (Here is the point where this article is about and I will elaborate further)

Many guides focus on shopping sites, discussing purchase conversion rates and cart abandonment. However, adapting these concepts to different types of sites, like membership sites, can be challenging. 
Here, I’ll share **my experience creating a dashboard for a membership site**. The steps for shopping does not differ too much though.

For my membership site, the key metrics to track are:

* Conversion Rate for New Member Registrations: This measures the percentage of visitors who complete the registration process
* Registration Process Abandonment Rate: This tracks the percentage of users who start but do not complete the registration

### Why Use an Open Funnel Dashboard?
In my case, I chose to create an open funnel dashboard for the abandonment rate due to the nature of our registration process, which includes email verification. Here’s why:

* Email Verification Step: The registration process on our site requires users to verify their email, which can interrupt the session. A closed funnel dashboard would inaccurately show a higher abandonment rate because it doesn’t account for users returning later to complete verification
* Multiple Device Use: Users often start the registration on one device (e.g., PC) and finish it on another (e.g., phone). A closed funnel might count these as separate sessions, leading to duplicated session counts and skewed data

:::note warn
Looker studio has not a funnel chart available. The two options are create your own custom funnel or use a 3rd party chart extension (currently known as Community Visualizations). 
I will create my own custom funnel chart without any external tool.
:::

https://support.google.com/looker-studio/answer/9206527#zippy=%2Cin-this-article



### Abandonment rate funnel steps
Here are the funnel steps I planned for my site
1. Number of sessions of my pre-registration page (people who visited the page)
1. Number of events of pre-registration micro-conversion (people who decided to proceed to the email verification)
1. Number of sessions of the registration form (after email verification)
1. Number of events of registration conversion (people who completed the registration process)

You can notice it's not possible to just create a bar chart with these data because the measure unit is not the same for each step. 
Therefore we need to do the following to create a custom open funnel dashboard:
1. **Create a score chart for each step metrics**
Step 1: Number of sessions of my pre-registration page
![step1_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/b5db307d-27f6-8499-1582-6d59e8a56828.png)
Step 2: Number of events of pre-registration micro-conversion (people who decided to proceed to the email verification)
![step2_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/06e5e35c-4d12-ea51-ea27-3e152b8b21a4.png)
Step 3: Number of sessions of the registration form (after email verification)
![step3_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/b12fc5f2-9005-ec06-2fa8-ca8c972812e6.png)
Step 4: Number of events of registration conversion (people who completed the registration process)
![step4_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/b9c4b680-e720-f943-45d4-069213486a76.png)

1. **Blend the steps data into 1 single custom data source**
Select all charts, right click and choose "blend data".
![blend steps_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/875a4884-d154-6149-a563-c35277e7a24e.png)
It will appear a score chart. At this points it does not tell us much so we will tweak it a little.
![blend steps2_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/12f10286-d3a5-9904-aff4-7015a76516f9.png)
1. **Create a bar chart from the blended data**
You can see the 4 steps blended data
![blended data_eng.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/2b533396-5275-960d-4097-db62df6a7c82.png)
Then we click on the blended data score chart and change the chart type to "bar" or "column".
It will throw an error but just change the dimension to the 1st column of your blended data and make it bigger. It should look like below.
![blend steps3_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/4a75ed5a-a3d3-76da-33b5-387ae8ab33cf.png)
Then, add the other 3 steps columns to the chart as metrics.
![blend steps4_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/2538c980-d415-93c9-17be-1caa7d4fcafd.png)
1. **Set the abandonment rate between steps**
Select step 2 and then step 1, right click and then choose "blend data".
![blend steps5_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/21fb4222-1162-ec16-d8b3-ba862a395a8e.png)
A new score chart will appear.  
![blend steps6_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/34ff29cd-0481-31cc-2583-b36c55cdc45b.png)
However, we need to edit the formula so it shows the data of the people who did not proceed to step 2.
![blend steps7_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/eb47d9da-5444-3b2c-9e66-8ca3a613e2a2.png)
Repeat the same process for the other steps. You can add some style custom formatting to make the score change color according a threshold.
![blend steps8_english.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/c32d2852-8677-b41e-00e1-c3cfaa85614f.png)

Now let's add the conversion rate.

### Conversion rate chart metric
Here is the formula 
```Conversion rate = Conversions / Total visitors x 100```

In my site's case it would be:
```the number of conversions / number of micro-conversions x 100```

In looker studio, it's pretty easy. Just select the conversions chart and then select the micro-conversions chart. Then blend the data and a new chart will appear. That's it. Give it a title to understand what the chart means. I added a small explanation of which steps I'm using for the conversion rate metric.

# Final result
Here is the complete conversion and abandonment rate dashboard after giving it a few aesthetic touches.
![conversion_abandonment_rate_dashboard_complete.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3801563/bebd3372-662d-e751-4ec2-1266bdd78da9.png)


### Share your dashboard
You can now share your dashboard with other team members, clients or shareholders.
Click share and set the access permissions. I would suggest to give "view only" as default unless you need other people to help you edit the dashboard.

And that's it all for this custom funnel chart in looker studio. Thank you for reading until the end. 

# End Note
The fun part is that there's is no single path or method with Google Analytics, Google Tag Manager and Looker Studio so use this guide as reference.
I'm still new on this topic so I would love to hear your suggestions on how you would create your dashboard.