Hey there.
Let's talk about advanced date selection in Data Studio.
We're going to cover how to create rolling
dates, super customized date ranges, and advanced comparison ranges.
If you're using Data Studio,
your most probably working in marketing, information tech in content,
or you're a business owner and you want to grow your company with the help of data.
Let's make a quick detour.
Imagine you're opening your email app and it only shows you emails from 2019.
Okay, those emails probably have value
since you kept them and you can get back to them at any time.
But you're most probably interested
in seeing your emails from the present moment, not from another era.
It's the same with your business data.
Looking at a fixed date range, whether it's from 2019 or from last month,
won't take you too far in your business decisions.
So what you want is to make use of Data Studio's Advanced Date Selection
feature and allow the reports you have built to dynamically update as days go by.
This concept is called Rolling Dates,
and you can get the same result in two ways by using the Date Range control
and by editing the date range at each charts level.
We'll take them one by one.
The Date Range Control this control can be
found in Edit mode in the secondary menu at the top of your dashboard right here.
So you go to add a control date Range
control and you position it anywhere on the page where you want.
We already have one, so I will delete this one for now.
Data Studio offers some predefined rolling dates options.
So if I go to view and I go
to my Date Range control and I click on this button here,
I will see that I have the last 7, 14, 28, 30 days this week to date or this entire
week, and the option to start the week on a Sunday or on a Monday.
Then this month, this quarter, this year, either the entire year or year to date.
But in my case,
I want to look at the number of new users and the total events for the past 21 days.
And I don't have this option here.
So how do we do that?
We go into Edit mode, we click on our little date range widget here.
We select just this one.
We go on the right side menu, we click on the drop down and we scroll to Advanced.
And then I will say that I want today minus one day.
You have the option here to select day, weeks, month, quarters, years.
And again the week starts on Sunday or Monday.
And then I said that I want the last 21 days.
So I will select 22 days here and this way I will have 21 days because today I have
incomplete data and I will apply and I have my desired date range.
So when I open the dashboard tomorrow, I will see the 7 June and the dates will
roll as I need them to. Group your control with your charts.
If you have multiple date ranges on one
page or leave them ungrouped and all the charts on the page will react to it.
It's important that you make sure that all the charts that you want to react
to the date range control have in this section here.
So scroll all the way to the default date
range and make sure you have the auto selected here.
So if you ever have a chart that is not
reacting to the control date filter, check this section here and set it to auto.
So it's the same with this one.
If we scroll we have the auto default date
range and this will react to our date range control.
Also pay attention that you can do
the same thing at the control Widget level but it will consider it's a one time only
need and it will reset every time you reopen the dashboard.
So what I mean by that is we will go back here and we will reset this to auto date
range and then I will show you what I mean.
So if you select the date range, you can apply directly here the advanced option.
So I will go and say one day here and 22 days here and I will apply.
So what happens is that if I restart and I
go back to the dashboard, the date range will reset.
It will consider it's a one time only need.
So if you want it permanently,
you will want to set it up again, select it and set it up on the right side
menu, otherwise it will go back to the default which is the last 28 days.
The auto date range editing the date range at each charts
level is exactly the same process, just that you're not using a control.
But let's say you want to look at monthly rolling data for this year minus
the current month on the same page with the other daily rolling data.
So we had this daily example
and on the same page you want to have this year minus the current month.
So we go back into edit mode and we select
our chart and we scroll to the default state range and we click custom.
And then if I want a fully dynamic solution.
I will say today minus one month.
So I don't want to take into consideration
this month because this month is incomplete and then today -13 months and I
will see the results from May 2021 to May 2022 and I can apply.
I might want to see the results
from the start of the year so I can go here back and I will say I want this start
date to be a fixed start date and I will select this year.
So we go to 2022 this year and January
and I will select the 1 January and then I will apply and I have the performance
starting with the start of the year and then up to last month.
But what about situations where you need
even more customization for your date ranges?
Let's take this case.
Our company reports on data from the 21st
of each month to the 20th of the next month.
Is there a way I can set an advanced date
range filter, one that I don't have to go and update each month manually?
The answer is yes.
There is a workaround.
While we're waiting and hoping
for Data Studio to implement this simple feature, we can be resourceful and use not
a control filter or a setting, but a Google Sheet and a blend.
So in this example, we want to extract the data between
the 21st of each month and the 20th of the next month.
Good. Let's start with the Google Sheet.
And this is an embed here. Okay.
And add them as a start date and an end date.
These are dynamic date values.
As you can see the formulas here built so that they will change every month.
So we have the month of the date that is
today minus one, and we want the 21st and the year of the date that is today.
And the same with the end date.
It's a similar formula, but it doesn't subtract one month from it.
So it will be this month.
Then with a simple array formula,
we can extract all the dates in our desired date range.
And if I scroll, you will see that okay,
I'm starting with the 21 May and I'm finishing with the 20 June.
So start date, end date.
By the way, we will make this resource
available in the notes below, so feel free to use it if you need to.
Then we can go to Data Studio, connect this spreadsheet as a data source,
and build a table with the date as a dimension and an important aspect.
I'll go into Edit mode to show you I
connected the spreadsheet as a data source.
I will select the table,
and what you will see is that this table doesn't have a date range dimension,
so the date is only added as a dimension here.
The next step is to create a table
with your regular data source and your desired metrics.
And for this one, make sure you have a date range dimension assigned.
In this case, we have a GA4 data
source, and we will have the default date range to auto.
Now go ahead, select both of your data sources and blend them.
The blend has to have the Google Sheet on the left.
So this is the blend.
I already created it.
I will go into Edit mode of the blend,
and you will see that the custom rolling date range is on the left side
with the date just as a dimension, not as a date range.
And the regular data source on the right
with all your desired metrics the date as a the dimension.
So on top, the date and the date range set
to auto the conditions through which they are blended is a left outer join based
on the date dimension that is present in both data sources.
What results is exactly what we needed
a custom rolling date range for the regular data source.
Our dates were starting on the 17th and were ending yesterday but with this
blend it will start on the 21st and it will end on the 20th as we define it next month
this will reset according to the new month values.
I chose to show dashes for the days
in which we don't have data yet but you can choose any of these from Style.
You go to Missing Data here and you can
show no data, zeros, dashes, nulls or blanks.
I personally recommend zeros, dashes or blanks because they're more clear.
Finally, we'll quickly go over the Advanced comparison date ranges.
These have similar steps to the advanced
date selection just that you'll have to set them at each charts level.
Unfortunately, you don't have the option
of choosing multiple comparison date ranges or add them to the interface as
a control filter as we did with the date selection.
The comparison settings are in the same section with the default date range.
Actually, they're just right below and you will see them here.
Comparison date Range we have some predefined ones the previous period
and the previous year and they are rather limiting.
I know around probably 80% of my clients
needs go into these two comparison categories but let's say you had a hell
of a good month and you want to show your stakeholders that you brought more revenue
and more conversions than the entire last six months together.
So we will select Advanced, we will go in this case to the comparison
date range and we will select here one month.
We want to take a look at the data up
to one month ago and six months here apply in the same process.
In this other scorecard we go
to comparison date range Advanced and we already have it set it up here.
So one month, six months and we'll see that we have 9% more revenue than
in the last six months combined and 10% more conversions.
But how do we make this more clear?
We go to Style.
We can also add additional text if we want but we can also go to Style and we can
uncheck this height comparison label that will tell you which is the date range
that we're comparing our current data with.
So going back here height comparison label and you will see that we have 10% more
conversions than the date range from the 1 December to the 31 May 2022.
You also have the option to show the absolute change.
So instead of showing the percentage by which you improve the performance you
can show the exact number of conversions that you improved the performance.
And you also have the option to define
the positive and negative colors depending on your metrics.
So, for example, the CPA will have these colors reversed.
I never miss a chance for a good shout out
to the Data Studio community by the time we're recording this.
Medieval Judah,
one of the top contributors in the GDS community, has talked about a proof
of concept for a custom date ranges comparison selector.
So you will be able to switch between the previous week, previous year and have
that advanced date selection for the comparison that we all need.
No pressure, but I can't wait to see how he managed to get these results.
All right, folks,
that was probably the longest video ever made on date selections in Data Studio.
Thanks for watching. See you in the next one.
All right, thanks Andreea, for this great information.
If this video helped you out in any way, then hit the like button.
And also check out this playlist right
over here where we have more interesting topics on Data Studio and data
visualization now from everybody here at MeasureSchool.
Happy measuring.