How to create an interactive funnel report with a bar chart from Looker Studio

---

Last year, I wrote an article describing how to build a simple funnel chart with a single table chart. A way to demonstrate in Looker Studio (ex Google Data Studio…should I continue to specify it) that we don’t need 20 chart elements to build this kind of visualisation with steps, absolute values, drop-offs, rates…

This time, I propose to inject interactivity as part of the design concerns of funnel report creation. Let’s include 2 controls:

• Type of value to display
  • Absolute values fo data volume, and so context
  • Vs
  • Rates for steps comparison and performance
• Type of drop-off to display
  • Step drop-off regarding the previous step
  • Vs
  • Funnel drop-off, so the cumulated exits from the funnel for each step

The 4 combinations of these options can provide insights. Choosing to display one at the time could help for reading and exploration.

---

Ingredients of the interactive funnel report

For this custom recipe, we will need the following elements to build the funnel report above:

• Your data set from your data source with dimension step orders – for my example it is a calculated field – and a metric
• 2 parameters as lists of text values, the 2 related data source calculated fields for blended data wrapping, and 2 controls
• Blended data tables with mentioned data to self cross join our data set and get access to all rows for each display rows
• A bar chart to display the final visualization
• Some chart calculated fields, for the metrics to display regarding the parameter values selection

---

---

---

1. Data set

For my example, I use Google Analytics 4 demo account, selecting 5 events corresponding to the ecommerce funnel steps and I define their order number thanks to the calculated field below on the data source level.

---

Funnel step order

CASE 
 WHEN Event name = "add_to_cart" THEN 1
 WHEN Event name = "add_shipping_info" THEN 2
 WHEN Event name = "begin_checkout" THEN 3
 WHEN Event name = "add_payment_info" THEN 4
 WHEN Event name = "purchase" THEN 5
 ELSE NULL
 END

---

Then, I create a filter to keep only wished events and I call it “GA4 – Checkout steps”. This filter will be applied on the configuration of blended data tables.

It is quite simple, taking advantage of the null value returned by the calculated field above when the event name is out of scope for our funnel report.

---

---

2. Parameters and related elements

Create 2 parameters of list of text values:

• “Drop-off type” with “Step drop-off” and “Funnel drop-off” values (and label)
• “Values type” with “Absolute values” and “Rates” values (and label)

---

---

---

Add 2 data-source calculated fields to get access to the selected values through the data blending we will configure later (blended data cannot access to parameter values without a calculated field of wrapping).

---

Drop-off type dim

Drop-off type

---

Values type dim

Values type

---

Last step before blending data: Add 2 fix-sized list controls (or dropdown) in your report page and use the parameters as control fields.

---

3. Blended data

Now, we configure blended data “BD – Self blended funnel steps” as described below for our example:

• Left table – GA4 demo account
  • Table name: “Displayed data”
  • Dimensions: “Funnel step order”, Drop-off type dim, Values type dim
  • Metric: “Total users”
  • Filter: “GA4 – Checkout steps”
• Right table – GA4 demo account
  • Table name: “Reference data”
  • Dimension: “Funnel step order” renamed “Ref Funnel step order”
  • Metric: “Total users” renamed “Ref Total users”
  • Filter: “GA4 – Checkout steps”
• Join configuration
  • Operator: Cross join
  • No key

---

---

---

4. Chart configuration

Let’s create the single bar chart of the funnel report:

• Chart type: Bar chart
• Setup tab
  • Data source: Your blended data “BD – Self blended funnel steps”
  • Dimensions:
    • “Funnel step order”
  • Metrics:
    • A chart calculated field named “Abs users” corresponding to formula below with AVG aggregation
    • A chart calculated field named “Abs exited users” corresponding to formula below with AVG aggregation
    • A chart calculated field named “% active” corresponding to formula below with AVG aggregation
    • A chart calculated field named “% drop-off” corresponding to formula below with AVG aggregation
  • Sort by “Funnel step order” ascending
  • Uncheck all chart interactions option
• Style tab
  • Horizontal bar chart
  • Stacked bars
  • Show data labels
  • Data label: Metric value / Compact numbers / 1 as decimal precision / Bar label at the left
  • Select 2 same series of 2 colors for active and drop-offs (we need to do it twice to cover the 2 first metrics fo absolute values, then 2 next metrics for rates)
  • Hide axes
  • Adapt the group bar width regarding the wished spaces between step (100% in my example)
  • Adapt the label font size
  • Turn off the legend
  • Do not show the chart header

At last, add at the left icon representing each step or add texts elements.

---

Abs users

IF(
 Values type dim = "Absolute values"
 ,
 Total users
 ,
 null
 )

---

Abs exited users

IF(
 Values type dim = "Absolute values"
 ,
 IF
 (
 Drop-off type dim = 'Funnel drop-off'
 ,
 IF(Ref Funnel step order = 1 AND Funnel step order != 1, ( Ref Total users-Total users ),null)
 ,
 IF(Ref Funnel step order = Funnel step order - 1, ( Ref Total users-Total users ),null)
 )
 ,
 null
 )

---

Previous step dropoff

IF( Values type dim = "Absolute values",
 IF
 (
 Drop-off type dim = 'Funnel drop-off'
 ,
 IF(Ref Funnel step order = 1 AND Funnel step order != 1, ( Ref Total users-Total users ),null)
 ,
 IF(Ref Funnel step order = Funnel step order - 1, ( Ref Total users-Total users ),null)
 )
 , null)

---

% active

IF(
 Values type dim != "Absolute values"
 ,
 IF
 (
 Drop-off type dim = 'Funnel drop-off'
 ,
 CASE 
     WHEN Funnel step order = 1 THEN 1
     WHEN Ref Funnel step order = 1 AND Funnel step order != 1 THEN (Total users )/Ref Total users
     ELSE NULL
 END    
 ,
 CASE 
     WHEN Funnel step order = 1 THEN 1
     WHEN Ref Funnel step order = Funnel step order - 1 THEN (Total users )/Ref Total users
     ELSE NULL
 END    
 )
 ,
 null
 )

---

% drop-off

IF(
 Values type dim != "Absolute values"
 ,
 IF
 (
 Drop-off type dim = 'Funnel drop-off'
 ,
 IF(Ref Funnel step order = 1 AND Funnel step order != 1, ( Ref Total users-Total users )/Ref Total users,null)
 ,
 IF(Ref Funnel step order = Funnel step order - 1, ( Ref Total users-Total users )/Ref Total users,null)
 )
 ,
 null
 )

---

Small graphical limitation

Because of the way to display rates independently to the value of each step, If you select “Step drop-off” and “Rates” at the same time, the bar chart will draw a visualisation that will not look like a funnel. But at the same time, afterthought, the result is more efficient visually to spot the steps with the more drop-off rates (the 2nd and the 5th step in the example).

---

5. Configuration review

At last, let’s review the full configuration with this small video.

---

---

Voilà.