Home » Converting null to zero in Google Data Studio

Converting null to zero in Google Data Studio

by Luuk

Having Null values in your Google Data 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: $0

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 Data Studio (see the image below).

Because of the Null value, the scorecard shows no data.

Replacing null to zero in Google Data Studio

To replace null to zero you need to create a new calculated field in Google Data Studio. Place the following code in the calculated field and save it:


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.

Null to zero in Google Data Studio

The formula to create a metric in Google Data 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 Data 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.

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

Rank [Keyword Rank] IS NULL THEN 100
ELSE Rank [Keyword Rank]

With this calculated field we’re telling Google Data 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.

You may also like