Having ‘Null’ (or empty) values in your Google Looker Studio dashboard can cause some problems. For instance: you can’t use mathematical functions with Null values. An example: suppose we want to show the total costs of our social advertisements of last month in a scorecard. Last month we made the costs below:
- Facebook Ads: $1.788,39
- LinkedIn Ads: Null
To create a scorecard with the costs summed up, you must create the following formula: Facebook Ads costs + LinkedIn Ads costs. But the problem here is that the source LinkedIn hasn’t made any costs that month. The connector then communicates the costs of LinkedIn as a Null value, which results in a scorecard with ‘No data’ in Google Looker Studio (see the image below).
Because of the Null value, the scorecard shows no data.
Replacing null to zero in Looker Studio
Option #1: Using the CASE statement
To replace null to zero you need to create a new calculated field in Google Looker Studio. Place the following code with the CASE statement in a calculated field and save it:
CASE
WHEN *METRIC* IS NULL THEN 0
ELSE *METRIC*
END
You only need to replace *METRIC* to the metric you need to use. This can be all kind of metrics, for example: pageviews, sessions, conversions etcetera.
The formula to create a metric in Looker Studio without NULL.
Another handy solution for a problem with Null
With Accuranker you can create very nice graphs with organic keyword rankings. But when we’re not ranking for a keyword it returns as 0 in Google Looker Studio. Not very handy, because some people could think that we’re ranking very well for that keyword, or that we’ve gained an organic featured snippet. See the image below for a visualisation of the situation before we used the calculated field.
Option #2: NARY_MAX function
The second option is the NARY_MAX function. Place the following code in a calculated field and save it:
NARY_MAX(*METRIC*,0)
You only need to replace *METRIC* to the metric you need to use. This can be all kind of metrics, for example: pageviews, sessions, conversions etcetera.
In the example above we’re not ranking in the search engines for Keyword 3. But the Google Data Studio connector of Accuranker says that we’re ranking at position 0.
We can fix this ‘problem’ with the following calculated field (the bold metric we are using is a metric of Accuranker):
CASE
WHEN
Rank [Keyword Rank] IS NULL THEN 100
ELSE Rank [Keyword Rank]
END
With this calculated field we’re telling Google Looker Studio to replace Null to 100. Because if we’re not ranking for a keyword, we want to see position 100.
When the calculated field is set up properly, we’re getting to see the graph below.
A nice visualisation of our organic keyword rankings in the search engines.