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.

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

  1. 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?

  2. 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”?


  3. Fantastic! Thank you for the formula.

Leave a comment

Your email address will not be published.


This site uses Akismet to reduce spam. Learn how your comment data is processed.