Summary

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. 

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 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 only way to deal with this in Data Studio is to surround each value with a CASE  statement, like this:

leads =
CASE WHEN goal1 IS NULL THEN 0 ELSE goal1 END +
CASE WHEN goal2 IS NULL THEN 0 ELSE goal2 END +
CASE WHEN goal3 IS NULL THEN 0 ELSE goal3 END +
CASE WHEN goal4 IS NULL THEN 0 ELSE goal4 END

See https://support.google.com/datastudio/answer/7020724?hl=en for more on the CASE  statement and how to use it in Data Studio.

This is OK for pre-aggregation metrics, However if you want a post-aggregation metric, like a ratio, for example:

CPL = SUM(cost) / SUM(leads)

You can't just do this:

CPL = SUM(cost) / SUM(
CASE WHEN goal1 IS NULL THEN 0 ELSE goal1 END +
CASE WHEN goal2 IS NULL THEN 0 ELSE goal2 END +
CASE WHEN goal3 IS NULL THEN 0 ELSE goal3 END +
CASE WHEN goal4 IS NULL THEN 0 ELSE goal4 END)

as you can't put a CASE statement inside a SUM. You can't put a SUM inside a CASE either.
Instead, you will need to create a separate calculated field leads  and use that in your CPL  formula. The leads  field will need to have aggregation type 'Sum'. 

Set up metrics in Funnel to avoid null

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.

However 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(cost) / (SUM(goal1) + SUM(goal2) + SUM(goal3) + SUM(goal4))

This suffers from the same problem as before; if any of the values are null, the total will be null.

So what can we do? The answer is to split up your formula into two and use rule-based metrics. 

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(cost) / SUM(leads)

As leads is a rule-based metric in Funnel, null values will already have been taken care of, so you will not need to take any other action to get Data Studio to behave like Funnel.

Further reading

http://help.funnel.io/dimensions-and-metrics/metrics/how-are-custom-metrics-calculated
http://help.funnel.io/google-data-studio-connector/can-funnel-export-custom-metrics-to-data-studio
https://support.google.com/datastudio/answer/7020724?hl=en

Did this answer your question?