What is up, Measure friends? This is Eric from MeasureSchool.
Have you ever seen this in your reports before?
No data, plain zeros, unknowns, or other ones?
This is very common.
But if you don't handle missing values
properly, they can lead to incorrect conclusions.
The problem is that simply removing them
can sometimes create an even greater problem than the missing data itself.
This is why we look at how to deal
with them in Looker Studio to ensure that your analysis is as reliable as possible.
Cool? Let's get started.
Missing values like duplicates are known to be trouble makers for data analysis.
Now, they can come in different names, and here are some examples.
Typically, to handle them,
you will be going to your original data source to clean the data.
But there are reasons why you may want to do it within your visualization tool.
This can be if you have a small data set, if you don't want to deal with another
tool, or if you are tasked to only edit a report.
Now, let's jump into Looker Studio to start the process.
In Looker Studio, we need to first identify if we do have missing data or not.
A quick way to do this is to have a global view of your dashboard and try to find if
there is any information referring to missing data.
For example, we have our time series chart
on the left side, which breaks down our campaigns by their URLs.
If you pay attention to the legend,
you'll notice this null, which represents missing values.
So we know that we have missing values in this chart.
Now identifying missing values becomes more tricky with tables because they can
have different names in the same dimension.
What you can do to find them is to sort
the dimension or metric that you need by clicking on the header.
I'm interested in the age dimension,
and as you can see, we do have quite some data here.
What I'll do is click on age to sort it out.
Here we see that we have no data, which equates to missing values.
But this is not the full picture.
If I was to click on age a second time to sort it out again,
different types of missing values, such as unknown, will show up.
And this is why it is very important to understand the makeup of your data prior.
Now, after identifying our missing data, we need to decide what to do with it.
Should we remove it or should we keep it?
There are different rules of thumb for this.
Usually, it is safe to keep any missing
data that takes less than 20 or 30 % of your data set.
It is generally accepted that anything beyond that should be removed.
But just know that many businesses sometimes have more than 50 % of missing
data and still decide to keep it because you can still get good enough insights or
directions despite a large margin of error.
Anyway, in our case, should we keep it or remove it?
A good trick that I like to make this
decision is to use a chart to identify the proportion of missing data.
And what I like to do is to use a pie chart.
I'll select the Donut Chart.
Now, this Donut Chart is a reflection of our time series chart on the left.
If you take a close look at the legend, you find this big blue dot at the bottom
with no data associated with it, which represents our missing values.
Let's select this.
Here we see that our missing values take up to 15.8 or around 16 % of our data set.
This is less than the 20 or 30 % threshold.
Therefore, it is safe to keep these missing values for our analysis.
Typically, there are two ways to remove
null values in Looker Studio, one that involves a cosmetic solution
that will have no impact on your data and one that will.
For the first method, we'll just use a filter.
Select your chart, add a filter.
We'll rename our filter.
We'll select Exclude.
For the field, we'll select here it is the lending page dimension.
And for the condition equals to null.
Save it.
Perfect.
Null values are now removed from our chart and its legend, but our metrics such as
revenue and number of users remain the same.
Now, we look at the second method.
Let's pretend that we had more than 30 %
of missing data and that we wanted to remove them.
A quick way to do this could be to just
add a control and select the drop down list.
We have already built one here
with the landing page dimension to reflect our time series chart on the left.
Now, if I was to click here,
you'll see that our URLs are grouped in different categories and you can find
your null category, your null values and unselect it.
Now, see how our number of users and revenue has dropped.
However, please be cautious with this
common method because you may exclude other insightful data.
Let's see exactly how excluding null
values this way works and its implications.
Because we are using our data from Google Sheets,
let's just use it to understand what's going on in the background.
Now, here you'll notice that we have two
columns, one for gender and then one for the number of users.
This way we can know how many users we have per gender for male and female.
In the gender column, you'll see our null values here in red.
Now, using the method that we used earlier, you may believe that Looker Studio
will go and find these null values and then delete them this way.
However, this is not the case.
What is actually happening, Looker Studio is looking at all the rows
that are containing these null values and removes them all.
Now, this may not be an issue if you are using a variable like ID number,
for example, because ID numbers are not really useful for analysis.
You cannot use them for your calculations, for example.
Therefore, in this situation,
removing your null values will have no consequences.
Now, in the case of revenue, this would be problematic.
For example, here we can see our revenue per gender.
In the revenue column at the bottom, you'll see that we have a total of $3,000.
If we were to remove our null values this
way, our revenue will be significantly impacted.
Now, in Looker Studio,
let's bring back our null values so that we can see our complete numbers.
Now, for our revenue, we have around $500,000.
If I was to exclude our null values,
this number will drop to around 400,000, which is a decrease of approximately 16 %.
Now, when it comes to Web Analytics, some decrease is okay because analytics
platforms will never really give you an exact number as precise as those as you
will find in your CRM or your shopping cart.
Still, we do need thresholds when it comes
to revenue, and usually, anything that is less than 5 % is fine.
However, here we are way above this threshold with our 16 %.
Therefore, in this case, we will need to keep our null values.
So let's select them back.
Now, Looker Studio has a native way of dealing with missing values.
To give you an idea, if I was to go to Users and sort this out,
you'll notice that our missing values are shown as no data.
Now, it is usually best practice to have zeros, particularly for metrics.
Let's see how we can change that.
Let's go back to edit and select our table.
In the properties panel, go to style
and scroll down to the missing data section.
Right here.
You can choose how your missing values should show up.
And here we have set them as show no data.
Hence the reason why if you go to age, you can find again here no data,
and also in users, you'll find no data here as well.
We can change this into zero and I'll select Show Zero.
Now we have zero values instead.
When it comes to making decision to remove
missing values, consider those values that become irrelevant.
Let's have a look at our age dimension again and let's bring back our pie chart.
Add a chart.
Let's go with the pie chart this time.
All right, this
chart is linked to our age dimension, and we can find our null values by looking
at this blue icon which has no values beside it.
These are our null values.
If I was to select it,
we can see that we have close to 100 % of missing data for the age dimension,
and this is on top of the unknown values as well.
At this point, the age dimension is just
irrelevant and should be removed from your report.
I'll remove the pie chart and then remove the age dimension from our dashboard.
Age is removed.
Lastly, pay attention to data types when
it comes to null values, as this can lead to wrong conclusions or odd results.
We have here a scatter plot that looks
at the relationship between the number of sessions and total revenue.
This data is being pulled
from the spreadsheet where you can see our null values, our missing data highlighted
in blue with NAs for the total revenue column.
If I was to select our chart,
you'll notice that total revenue is assigned with the ABC icon,
which is problematic because ABC is supposed to be assigned to dimensions.
Metrics, like Session, for example, comes with the one two three icon.
We can change this by going to the data source and edit the data source.
Find your metric, Total Revenue, and change the type from text to number.
Go to Numeric, select Number, and now click on Done.
Now you probably noticed that changing data types does not result in immediate
changes, and sometimes your charts also might break.
What we need to do is to replace the old
value, which is the Total Revenue, with the new default value.
Select here in the default group,
go to Total Revenue, and you can see now that we have the one, two, three icon.
Now refresh your page and see the change with our scatter plot.
You can also just rebuild your chart if you prefer.
We come to the conclusion of this video where we have covered how to identify
missing values and also the different approaches to handle them.
If you find this video useful,
consider liking it and please share your thoughts with us.
Anyway, this was Eric from MeasureSchool and see you next time.