To avoid problems with null values in metrics exported from Funnel to Google Data Studio, follow these guidelines when creating custom metrics in Funnel:

  • If your custom metric does not contain a 'divide', create it as a rule-based metric.

  • If your custom metric contains divide, first create the component parts as rule-based metrics, then create the metric as a formula metric. 


Let's say you want to create 'Cost per lead' (CPL) based on a number of different Google Analytics goal completions. The custom metric formula could be:

CPL = cost / (goal1 + goal2 + goal3)

Best practice is to first create a rule-based metric called leads that contains

leads = goal1 + goal2 + goal3

and use that in Cost per lead:

 CPL = cost / leads

See for more on creating custom metrics.

The rest of this article explains how Data Studio and Funnel handle null values in calculated fields, and why the guidelines above are necessary.

How does Data Studio handle null values?

Data Studio calculated fields behave like many other tools out there : X + null = null , no matter what X is. For example if you have a formula that adds up multiple Google Analytics goals, like this:

 leads = goal1 + goal2 + goal3 + goal4

If any one of the goals is null, leads will be null, so 

100 + 23 + null + 7 = null

How to avoid nulls in Data Studio

The best way to deal with this in Data Studio is to surround each value with an IFNULL  statement, like this:

leads =
IFNULL(goal1, 0) +
IFNULL(goal2, 0) +
IFNULL(goal3, 0) +
IFNULL(goal4, 0)

See Data Studio: IFNULL Help for more on the IFNULL statement and how to use it in Data Studio.

For a post-aggregation metric, like a ratio, for example:

CPL = SUM(cost) / SUM(leads)

You must perform IFNULL before the SUM :

CPL = SUM(IFNULL(cost, 0)) / SUM(
IFNULL(goal1, 0) + IFNULL(goal2, 0) + IFNULL(goal3, 0) + IFNULL(goal4, 0))

Metrics in Funnel

Funnel automatically handles null values inside formulas, so there is no need to add the CASE statements in your Funnel metrics when you use them inside Funnel.

When calculated metrics are exported to Data Studio, Funnel will export the formula, rather than the values. The formula is translated to Data Studio format, so your Funnel formula

 CPL = cost / (goal1 + goal2 + goal3 + goal4)

Will be translated to

CPL = SUM(IFNULL(cost, 0)) / (SUM(IFNULL(goal1, 0)) + SUM(IFNULL(goal2, 0)) + SUM(IFNULL(goal3, 0)) + SUM(IFNULL(goal4, 0)))

While this works, it is recommended that you split up your formula into two and use rule-based metrics, keeping the formulas simple.

This shows how the leads  metric could be configured in Funnel:

The formula in Funnel would then be

CPL = cost / leads

Which exports to Data Studio as

CPL = SUM(IFNULL(cost, 0)) / SUM(IFNULL(leads, 0))

Further reading

Did this answer your question?