Calculate Average Transactions per Day in Google Data Studio

Data Studio Transactions per Day Dashboard example

A while ago an eCommerce store told me that their goal was to increase sales to Z. To translate that goal into a day to day KPI, the easiest explanation was this:

Today your Average Order Value is X. To reach your goal of Z, your number of Transactions per Day have to be Y.

So, when Google Data Studio finally became available in Norway, it was time to create the Average Transactions per Day KPI in Google Data Studio.

Calculate number of days in Data Studio using DATE_DIFF & TODATE

In this example I’m using data from Google Analytics, so if you are going to calculate this using a different data source, you may have to tweak the date part of my example.

To calculate number of days we have to use 2 functions, DATE_DIFF and TODATE. In Google Data Studio you find this example/help text about how to do this calculation:

The example above seems to confuse some people. They try to create a new metric using the calculation exactly as it is written, which isn’t possible, and an error is returned. The problem is that end_time and start_time in the calculation are just dummy text telling you to input your end time and start time.

If you are going to calculate number of days using Google Analytics as a Data Source, the example below will get you there.

If you wonder about the DEFAULT_DASH part, see TODATE input formats.

In my example I call this new metric for Date Diff.

Data Studio DATE_DIFF formula

Notice that I have chosen Count as Aggregation.

Calculate Average Transactions per Day

To calculate Average Transactions per Day, create a new metric using the calculation below. I have called the metric Transactions per Day.

Data Studio Transactions per Day formula

Job completed!

Now make the Average Transactions per Day KPI fit into your dashboard design in Google Data Studio.

Be the first to comment on "Calculate Average Transactions per Day in Google Data Studio"

Leave a comment

Your email address will not be published.


*