Calculate Average Transactions per Day in Google Data Studio

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:

DATE_DIFF(X, Y)
Summary
Returns the difference in days between X and Y (X - Y).
X
A datetime returned by the TODATE function.
Y
A datetime returned by the TODATE function.
Example
DATE_DIFF(TODATE(end_time, 'NANOS', '%Y-%m-%d'), TODATE(start_time, 'MICROS', '%Y%m%d'))

DATE_DIFF(X, Y)

Summary

Returns the difference in days between X and Y (X - Y).

A datetime returned by the TODATE function.

Example

DATE_DIFF(TODATE(end_time, 'NANOS', '%Y-%m-%d'), TODATE(start_time, 'MICROS', '%Y%m%d'))

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.

DATE_DIFF(TODATE(Date, 'DEFAULT_DASH', '%Y-%m-%d'), TODATE(Date, 'DEFAULT_DASH', '%Y-%m-%d'))

1	DATE_DIFF(TODATE(Date, 'DEFAULT_DASH', '%Y-%m-%d'), TODATE(Date, 'DEFAULT_DASH', '%Y-%m-%d'))

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

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

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.

Transactions / Date Diff

1	Transactions / Date Diff

Job completed!

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

5 Comments on "Calculate Average Transactions per Day in Google Data Studio"

eric | July 20, 2017 at 3:23 pm | Reply

Thanks for the formula, works great for getting the date range as a metric.
I’m having trouble with using it in a calculated metric though. I checked it’s output and it’s right, but when I divide users by date diff I’m getting discrepancies from the right answer up to 40% (ex users/date diff=avg users per day: 43979/30=2233 when the right answer is 1466). Any thoughts where this is going wrong?
Oscar Ochoa | July 25, 2017 at 5:29 am | Reply

Thank you! this is very helful, any chance you can provide a custom metric formula to calculate “current days of the month) for example if today is June 13 it will return “13” and also days to the end of the month (if the month has 30 days and there are 17 days left will return “17”?

Thanks!
Vignesh | July 9, 2019 at 8:23 am | Reply

Fantastic! Thank you for the formula.
Pieter | February 21, 2020 at 11:08 am | Reply

Is this function only intended for 2 static dates or can it be used between 1 static date and the current date?
- Eivind Savio | February 26, 2020 at 9:54 pm | Reply
  
  If I understand your question correctly, it can be used between 1 static date and the current date.
  
  Regards,
  Eivind