Jennifer asks, “Do you know how to use Google Data Studio to look at website traffic in a 10 minute window before and after a television commercial air time?”
The field you’ll be looking for in the API is ga:dateHourMinute, and it’s formatted in YYYYMMDDHHMM format. There are a couple of gotchas about how to use this data, including it crashing your browser, so watch the video for a full explanation.
Can’t see anything? Watch it on YouTube here.
Listen to the audio here:
- Got a question for You Ask, I’ll Answer? Submit it here!
- Subscribe to my weekly newsletter for more useful marketing tips.
- Find older episodes of You Ask, I Answer on my YouTube channel.
- Need help with your company’s data and analytics? Let me know!
- Join my free Slack group for marketers interested in analytics!
What follows is an AI-generated transcript. The transcript may contain errors and is not a substitute for watching the video.
In today’s episode, Jennifer asks, Do you know how to use Google Data Studio to look at website traffic in a 10 minute window before and after a television commercial time errs? So, interesting application, I don’t know that I would use data.
Well, data steel work for this.
Yes, there’s a way to do that Google Analytics keeps three different date parameters that keeps the year, the year, month and day.
That’s one parameter, the GA date parameter, does hour of day, and then it does minute.
There’s a consolidated text field called ga date hour minute, which you can find in in Google Analytics and Google Data Studio that you can then visualize it in some forums as as a chart so let’s look at how to switch over here to our application.
So here we are.
Google Data Studio.
Let’s go ahead and make sure obviously, that you’re you have a data source connected.
That is a Google Analytics account.
And just as a general best practice, always slap a little date browser there.
Now let’s go ahead and start with a table.
Slap this table in here.
And we have pageviews.
And we have medium.
So if I start typing date in here, you’ll see date, date and date, our minute date, our minute is a field we’re looking for.
Now, one thing you’ll notice though, is that date our minute is shows up as a text field.
And the reason for this is because it’s a large glued together dimension of those other three dimensions, which means that you can’t treat it as a date, which is somewhat problematic.
You can possibly switch it over here like this.
Ah, there we go.
So now we’ve converted the datatype to date hour a minute and that means that opens up the ability for us to look at this at a much more granular level.
level, it also means we can now use things like line charts to be able to visualize a little bit better.
There’s an incredible, incredibly dense chart.
So now we’ve got our pageviews.
The next thing we’re going to want to do is we’re going to want to provide some level of filtering.
By the way, if your computer is not up to the task of visualizing that much data, because you’re talking about 10s of thousands of lines, make sure that you don’t hit chart buttons you don’t mean to.
So we’ve got this The next thing we’re going to want to do is slap some kind of filtering on this for the period of time that you’re looking for, so that your computer again does not attempt to render this chart repeatedly and just crashed.
So if you know the specific timeframe that you’re looking for, specified as a custom time frame here, you can see even with a relatively new computer, this is my browser is struggling to keep up with just the sheer number of have lines on this chart.
So let’s just go down to seven days, days to remix.
And now you can get a little more granularity.
Once you flip over into view mode, you can then select the date range more thoroughly.
So let’s look at just yesterday.
Was it yesterday, it was two days ago.
And the chart should eventually rebuild itself.
So that’s how you get to this information.
If you know that the commercials are going to air only at a very specific time, I would say instead of using date, hour a minute, you might want to try other date fields.
If you know that it’s always going to be on at a certain hour of the day, you can swap in our instead.
You can look at the hours of the day.
Let’s switch that to a bar chart here.
And there’s the hours of the day and let’s change our sort ascending to see what time period what periods of time during the day.
You get people looking at your stuff, fella bigger and dimension last seven days, okay? You can also if you know it’s your television commercials on a very specific time, like 59 minutes past the hour, instead of doing our drop it in at the minute level, you’ll be able to start seeing how many minutes the level of minutes in your in your data.
So, that would be the way I would suggest tackling this problem if you know their specific period of time use that if you have to use the the date hour minute field, make sure that you convert it as we did to a date format so that you can use it with all these other visualizations.
Otherwise you can only use it in a table which is Not ideal, because then you have to apply all sorts of filters and stuff to it.
Here’s the other thing I would look at.
You may want to do a more advanced statistical calculation.
And you can’t do this in Data Studio you have to do this in something like R or Python or Tableau or something, you may want to look at the correlation between television commercial air times plus the 10 minutes on either side and website traffic to see Is there a mathematical relationship is a correlation between the airtime and the traffic by doing it that way, you can see if there is a mall if there is a relationship if the commercials are doing anything to drive traffic particularly, I would be looking at organic traffic’s from branded organic search or from organic search in general, because all unless your commercial has obvious, you know, calls to action What the URLs in them people are going to have to search for you by name have to remember the brand.
So that’s how I would look at this.
That, again, is outside of the scope of what Data Studio can do.
But it is, it is something that I think is worth doing.
If you have follow up questions, leave them in the comments box below below.
There is a lot to unpack in day two.
So definitely take the Data Studio course it’s available for free from Google over at a analytics Academy dot with google.com totally free because there’s so many different features that you can play with here.
So give this a shot.
Have some fun with it.
Let me know what you think.
As always, please subscribe to the YouTube channel on the newsletter.
I’ll talk to you soon take care.
One helps solving your company’s data analytics and digital marketing problems.
Visit Trust insights.ai today and let us know how we can help you
You might also enjoy:
- How to Set Your Public Speaking Fee
- You Ask, I Answer: Data Visualization Courses?
- How To Set Your Consulting Billing Rates and Fees
- Best Practices for Public Speaking Pages
Want to read more like this from Christopher Penn? Get updates here:
Get your copy of AI For Marketers