Custom reports

Waterfall chart with Google Data Studio basic features

Waterfall chart with Google Data Studio basic features

Waterfall charts are nice & efficient to visualize cumulative values of a metric and the related participation weight of each item, towards the final value.

In digital analytics fields, it can address insights about frictions on funnels, form filling, web performance, speed of reading with scroll depth, etc.


In this post, I describe how to built a waterfall chart inside Google Data Studio, applying tricks on stacked combo chart, without any custom visualization. Just basic features.

The chosen data set for this tutorial is just a pretext to apply the trick: I use the average time on checkout pages of a sales funnel from Google Analytics demo account. For information, I also apply a segment to isolate only sessions going through the complete funnel. It is just a way to get quickly enough consistent data.


So, this example is based only on positive & cumulated metric values. Waterfall charts can also display negative values. These kinds of use case are put aside, considering that it is not exactly the same initial need of follow-up, and quite more complicated to address, but possible. Maybe, I will cover it through another blog post with another analysis prism: metric variation and weight of each dimension value. Another story.


As usual, you can prepare any consistent sorted data thanks to custom tracking and/or data preparation to apply this waterfall recipe.


Ingredients of the waterfall

For this custom recipe, we will need to the following elements:

  • Your main data set from your data source with the metric value for each step. The step is identified by an order number. Here a calculated field to specify the order # of each step and the metric is the spent time by page.
  • Blended data tables with mentioned data set to get order number on one hand, cross joined with the same order numbers with the related metric values on the other hand
  • A stacked combo chart to display the final visualization
  • 2 chart calculated fields to calculate the current metric value and the accumulated value with the previous steps


1. Data set

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


Step order #
CASE 
 WHEN Page = "/basket.html" THEN 1
 WHEN Page = "/yourinfo.html" THEN 2
 WHEN Page = "/payment.html" THEN 3
 WHEN Page = "/revieworder.html" THEN 4
 ELSE NULL
 END

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


Data Studio chart filter with only checkout pages

2. Blended data

Now, It is time to self blend! We configure blended data “BD – Steps self blended” as described below for our example:

  • Left table – GA demo account
    • Table name: “Steps”
    • Dimension: “Step order #”
    • Metric: None
    • Segment: In my case, segment corresponding to session with order completed
    • Filter: “GA – Page – Checkout steps”
  • Right table – GA demo account
    • Table name: “Steps with time”
    • Dimension: “Step order #” renamed “Step order # with time”
    • Metric: “Avg. Time on Page”
    • Segment: Segment corresponding to session with order completed
    • Filter: “GA – Page – Checkout steps”
  • Join configuration
    • Operator: Cross join
    • No key

Data Studio blended data for waterfall


3. Chart configuration

it is time to insert in your report a stacked combo chart with the following configuration:

  • Char type: Stacked Combo chart
  • Setup tab
    • Data source: Your blended data “BD – Steps self blended”
    • Dimension: “Step order #”
    • Metrics:
      • A chart calculated field “Avg on previous step(s)” with the formula below
      • A chart calculated field “Avg. time on current step” with the formula below
    • Sort by “Step order #” ascending
  • Style tab
    • Series #1: Bar with the background color (white)
    • Series #2: Bar with a strong main color (blue), turning on the data labels
    • General: Stacked bar on right axis
    • Axis: Show axis
  • Adapt manually the size of the chart regarding the scale and the final rendering
  • Add an icon for each step and put them over the numbers of the x-axis, or add simple text elements with step names (you can keep the numeric value of the order if it is relevant)

Avg on previous step(s)
SUM(IF(Step order # with time < Step order # ,Avg. Time on Page , null))

Calculated field & trick Explanations
We sum also the metric values only if the step order number is lower than the current one, so only for the previous steps. This metric will be hidden thanks the use of the background color. Because it is a stacked bar, the next metric below will start graphically where the previous step ends.


Avg. time on current step
SUM(IF(Step order # = Step order # with time ,Avg. Time on Page,0))

Waterfall chart configuration

5. Configuration review

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



Mais je dois m’en aller