How to create a funnel report with a single Google Data Studio table chart

---

We create funnel reports for a while inside Google Data Studio. There are a lot of solutions, templates, articles describing how to do. Most of these resources are nice and address the visualisation need. Technically speaking, they are based on scorecards with dedicated blended data to calculate each step-over-step rate or the related dropoff.

In these same examples, the steps with absolute metric values are often designed with a bar chart or with other scorecards.

So the full funnel report requires a lot of elements (charts, blended data, filters, shapes, and sometimes segments…so also multiple specific data loading as the consequence of all these elements).

We know how any additional element can degrade the performance of the report loading for readers, and even more for report editors. So, we need to keep in mind what happens behind the scenes to display queried data regarding our design choices.

---

As a solution to take care of performance and to go back to simple things, in this post, I describe how to built a funnel report – with the same data described above – BUT with a single table chart and the fewest configuration elements.

For the example, I choose to use a Google Analytics 4 data set, based on e-commerce event names and related users. But as usual, you can use any funnel data from any source (pages, stages, journey steps…).

---

---

Ingredients of the funnel report

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

• Your data set from your data source with dimension step names, dimension step orders – for my example it is a calculated field – , and a metric
• Blended data tables with mentioned data to self cross join our data set and get access to all rows for each display rows
• A table chart to display the final customized visualization with a lot of features available on this type of chart
• Chart calculated fields, to calculate the dropoff rate of each step and secondary metrics

---

---

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. Blended data

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

• Left table – GA4 demo account
  • Table name: “Displayed data”
  • Dimensions: “Funnel step order #”, Event name
  • 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

---

---

---

3. Chart configuration

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

• Char type: Table chart
• Setup tab
  • Data source: Your blended data “BD4 – Self blended funnel steps”
  • Dimensions:
    • “Funnel step order #” renamed “#”
    • “Event name” renamed “Step”
  • Metrics:
    • A chart calculated field named “-” corresponding to formula below with AVG aggregation (left bars)
    • “Total users” with AVG aggregation, renamed “Users” (Values)
    • “Total users” (again) with AVG aggregation, renamed “-” (right bars)
    • A chart calculated field named “Previous step dropoff” corresponding to formula below, percent type
    • “Total users” (at last) with AVG aggregation, % type, “Percent of max” comparison calculation, renamed “% funnel entrances”
  • Sort by “Funnel step order #” ascending
• Style tab
  • Uncheck “Show pagination”
  • Missing data: Show “-“
  • Dimensions: Right align (2 columns)
  • Metrics
    • column #1: Bar, wished color
    • column #2: Number, center, compact numbers
    • column #3: Bar, same color that column #1
    • column #4: Number, left align, 0 as decimal precision
    • column #5: Number, right align, 0 as decimal precision
  • Chart Header: Do not show
  • Optional : Add a conditional formatting rule to display the column of previous step dropoff with a specific color
• Adapt manually the chart size and any other style options
• Apply the same width to metric columns with bars to get the same scale, so looking like a symmetrical funnel at both sides of the value

---

-

0-Total users

---

Previous step dropoff

IF(Ref Funnel step order # = Funnel step order # - 1, ( Total users- Ref Total users)/Ref Total users,null)

---

5. Configuration review

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

---

---

Des fois, Ca coûte pas plus cher de bien manger !