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))