Data sources

Looker Studio Funnel Chart – How to adapt your data model to fit with the chart setup

October 3, 2024 - by Mehdi Oudjida

Last week, the Looker Studio team has released a new chart type: The funnel chart!

All the users community on the social networks was excited to discover this new welcomed feature, sharing examples. We were waiting for it for a while, we developed alternative solutions (custom configurations, community visualization…) and now a first native solution exists.

If you want to get an overview of this new native chart type, you can scan the official documentation and read the nice feature review written by Michele Kiss.

Beyond the excitement for the release of the funnel chart, I received also several questions about the required data to use this funnel chart, here 2 examples:

  • “Until now, I use a community visualization with a set of ordered metrics as input, how can I switch to this new chart?” 🤔
  • “My data source is a native connection to GA4 and I want to create an items funnel with the items views, items added to cart, items checked out, and items purchased. How can I do that?” 🤔


Indeed, the Looker Studio funnel chart requires to set up 2 fields as incomes:

  • A dimension corresponding to the step identifiers
  • A unique metric for the measure of each step, and also used for the calculation of derived metrics, like the rates (percentage of max or percentage of previous)


So let’s tackle this situation with the tutorial below, adapting the data model to fit with the native Looker Studio funnel chart.
As usual, an alternative would be to adapt the data source to be aligned with the requirements of the chart type. But at the same time, you will not prepare another set of reporting data for one single chart if the visualization layer can provide a quick-win solution.

Funnel Chart of Looker Studio - Adapt your data model to fit with the settings of the chart type

Ingredients

For this configuration, we will need the following elements:

  • Your current data set with aggregated metrics and optional dimensions
  • A static data source to list and sort the steps of the funnel
  • A data blending to link the good step with the good metric
  • The funnel chart sourced by the blended data
  • A chart-level calculated field to merge several metrics to one funnel measure

Your data set

Let’s consider that your data source provides a set of e-commerce interactions. For this exercise, my data source includes 2 dimensions (date and device type) and 4 aggregated metrics:

  • Sessions with Add to Cart
  • Sessions with Checkout
  • Sessions with Payment Method
  • Sessions with Purchase

Data source for the steps of the funnel chart

We create a simple data source with 2 fields : step name and step order. For this tutorial, I’ve decided to use a Google Sheets data source storing my four e-commerce steps with the related order:

  • Add to cart
  • Checkout
  • Payment Method
  • Purchase
Funnel chart from Looker Studio - Sheets for steps

Alternatively, you can build the same static data source with a csv file, an excel file or a BigQuery custom query (with a free sandbox or on your project) with an SQL query like the example below.

SELECT step_name, step_order FROM UNNEST([STRUCT('Add to Cart' AS step_name, 1 AS step_order), STRUCT('Checkout' AS step_name, 2 AS step_order), STRUCT('Payment Method' AS step_name, 3 AS step_order), STRUCT('Purchase' AS step_name, 4 AS step_order)]) ;

Blended data feeding the funnel chart

Now, we configure the blended data “BD – Steps for Funnel Chart” as described below for our example:

  • Left table – Google Sheets with the funnel steps
    • Dimensions: step_name, step_order
    • Metric: none
    • Filter: none
  • Right table – Your main data source
    • Dimensions: Date, Device Type
    • Metrics: “Sessions with Add to Cart”, “Sessions with Checkout”, “Sessions with Payment Method”, “Sessions with Purchase”
    • Filter: none
  • Join configuration
    • Operator: Cross join
    • No key
Funnel Chart of Looker Studio - Data blending to fit the chart data model

Chart configuration

At last, let’s add a funnel chart in our report page with the following configuration:

  • Char type: Funnel chart (stepped bar here)
  • Setup tab
    • Data source: Your blended data “BD – Steps for Funnel Chart”
    • Dimension: “step_name” renamed “Step”
    • Metric : A chart calculated field named “Sessions” and corresponding to formula below
    • Sort by: step_order ascending
  • Style tab
    • Customize which data you want to display and adapt the styles
Sessions
CASE step_name 
WHEN "Add to Cart" THEN Sessions with Add to Cart WHEN "Checkout" THEN Sessions with Checkout WHEN "Payment Method" THEN Sessions with Payment Method WHEN "Purchase" THEN Sessions with Purchase END

Configuration review

At last, let’s review the configuration just to be sure that you didn’t miss a…step…hoho.

Funnel thoughts

  • My example can be considered as simple because I don’t need more than 2 blending tables to adapt the data model, thanks to already calculated metrics. To get the same result with less easy contexts, again with blending capabilities, we could also imagine to mimic UNION operator with 5 blended tables. Each table would include a static step name and a metric with the required blending-table filters.
  • If you are looking for more examples of funnel custom solutions with Looker Studio, I wrote 2 other posts on the topic before the release of the native funnel chart but still relevant:

Like said, Josh Silverbauer on LinkedIn, here a lot of fun…nel!