All Collections
Google Looker Studio Connector
Troubleshooting
How do I avoid null values when exporting calculated fields to Looker Studio?
How do I avoid null values when exporting calculated fields to Looker Studio?

Looker Studio and Funnel behave differently when it comes to handling null values in calculated fields.

Mark Tombs avatar
Written by Mark Tombs
Updated over a week ago

Summary

To avoid problems with null values in metrics exported from Funnel to Looker 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. 

Example

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 http://help.funnel.io/dimensions-and-metrics/metrics/creating-a-custom-metric for more on creating custom metrics.

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

How does Looker Studio handle null values?

Looker 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 Looker Studio

The best way to deal with this in Looker 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 Looker Studio: IFNULL Help for more on the IFNULL statement and how to use it in Looker 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 Looker Studio, Funnel will export the formula, rather than the values. The formula is translated to Looker 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 Looker Studio as

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

Further reading

Did this answer your question?