Looker Studio: Creating specific time and date buckets with GA data

[sam8]

Say that you have event data in Google Analytics. You want to create specific buckets to display event counts for, such as "April 3rd: 7am to 9am" and "April 10th: 2pm to 4pm". How do you create these in Looker Studio?

At a high level, we will create a custom group for these buckets. To allow us the granularity required to create buckets, we will use the UNIX_DATE function to convert the dates into numbers. We will follow these steps:

1. Create a unix date field
2. Convert your buckets to unix dates
3. Create a grouping field on the unix date field

Create a unix date field

First, create a new unix date field to group on. Use a formula like this: 

UNIX_DATE(Date + hour (YYYYMMDDHH)) + (HOUR(Date + hour (YYYYMMDDHH))/100)

Note the following:

• We are using the "Date + hour (YYYYMMDDHH)" field included in the GA source.
• We add the hour separately as a decimal value. This will allow us to bucket on the specific hour ranges. 

Convert your buckets to unix dates

Next, convert your specific date and hour start and endpoints to unix numbers. 

The Unix date is the number of days, in integers, between your date and January 1,1970. You can use a tool like a unix converter to get this date, or use the UNIX_DATE function in Looker Studio to see some examples.

To add in the hour part, the formula in the previous section divided the hour of day by 100. So add a 0.01 for 1AM, 0.13 for 1PM, etc.

The following table shows some examples:

Date and time	Unix date	Unix date with hour
April 3, 2024 7:00AM	19816	19816.07
April 3, 2024 9:00AM	19816	19816.09
April 10, 2024 2:00PM	19823	19823.14
April 10, 2024 4:00PM	19823	19823.16

Create a grouping field on the unix date field

Third, create a custom group on the unix date field from the first section. Use the endpoints that you calculated in the second section.

Finally, include this group as a dimension in a chart to bucket your data as desired!