Blog

For many businesses, understanding revenue per hour is as important as revenue per day. It changes throughout the day with the number of people engaging with the product.

One powerful way to track how today is trending vs. the past few days is to overlay the hourly data of each day:

By overlaying each day as its own line, we can easily see which hours get the most revenue and how each day compares the the last.

And unlike a simple daily revenue bar chart, it’s easy to understand the current day’s progress before the day is over.

Building Hourly Revenue

To make this hourly trend chart, we’ll start with daily revenue:

select [created_at:pst:date], sum(price) from transactions where [created_at=5days] group by 1 order by 1

Which looks like this:

The square-bracket syntax above is a feature of Sisense for Cloud Data Teams. These two examples make it easier to work with time zones since the charts need to reflect PST but the database is in UTC. This database is PostgreSQL, so here’s what runs in their place:

Date Aggregation

[created_at:pst:date] converts created_at from UTC timestamp to a PST date:

The additional square bracket **`[created_at:pst:hour_of_day]`** extracts the hour from the PST-adjusted timestamp. It translates to:

extract(hour from ((created_at + interval '-7 hour'))::timestamp)

date_trunc( 'day', ((created_at + interval '-7 hour'))::timestamp )::date

Date Restriction [created_at=5days] restricts created_at to the last 5 days in PST and maintains the sargability of the column’s index by keeping all the math on the right-hand side:

created_at >= ( ((now() + interval '-7 hour')::date + interval '7 hour') - interval '4 day' ) and created_at < (now() + interval '-7 hour')::date + interval '31 hour'

Next, we split each day by hour to get the data we need for the lines:

select [created_at:pst:date], [created_at:pst:hour_of_day], sum(price) from transactions where [created_at=5days] group by 1, 2 order by 1, 2

Which looks like this:

The additional square bracket **`[created_at:pst:hour_of_day]`** extracts the hour from the PST-adjusted timestamp. It translates to:

extract(hour from ((created_at + interval '-7 hour'))::timestamp)

Rotating to Hourly Revenue by Day

The query is almost there! We want the hours to be the X-axis and the days the labels. Just switch the first two columns (or change the segmentation column) and make it a line chart:

select [created_at:pst:hour_of_day], [created_at:pst:date], sum(price) from transactions where [created_at=5days] group by 1, 2 order by 1, 2

That’s it! The full query with Sisense for Cloud Data Teams syntax expanded becomes:

select extract(hour from ( (created_at + interval '-7 hour'))::timestamp ), date_trunc('day', ( (created_at + interval '-7 hour'))::timestamp )::date, sum(price) from transactions where created_at >= ( ((now() + interval '-7 hour')::date + interval '7 hour') - interval '4 day') and created_at < ( now() + interval '-7 hour' )::date + interval '31 hour' group by 1, 2 order by 1, 2

A great companion chart to this one is the cumulative version of the same data. It shows how the the days stack up against each other and makes it obvious when the current day under or over-performing:

The same method for building the hourly charts works for other intervals too! Track daily trends by month or monthly trends by year, or any interval that makes sense for your business!