Here is how you can determine when users are most likely to visit your website in BigQuery.
Step-1: Convert ‘event_timestamp’ into a date object. The formatted ‘event_timestamp’ may look like the one below: 2024-11-23 07:05:30.985585 UTC
Step-2: Once the ‘event_timestamp’ is formatted, segment the converted values into ‘Morning’, ‘Afternoon’, ‘Evening’ and ‘Night’.
Following is my definition:
Night is defined as the time between 12:00 AM and 6:00 AM.
Morning is defined as the time between 6:01 AM and 12:00 PM.
Afternoon is defined as the time between 12:01 PM and 6:00 PM.
Evening is defined as the time between 6:01 PM and 12:00 AM.
Extract the time part from each ‘event_timestamp’ and then put them in the following buckets: ‘Morning’, ‘Afternoon’, ‘Evening’ and ‘Night’.
Step-3: Count the total number of pageviews for ‘Morning’, ‘Afternoon’, ‘Evening’ and ‘Night’ to determine when users are most likely to visit your website.
Once you understand the logic, creating a text prompt becomes easy.
Use the following SQL (automatically generated via a text prompt in ChatGPT) to track peak time for Google Analytics 4 visits in BigQuery: