Get to know your AOV using histograms
AOV as a metric is very useless. It tells you the average of your customer values. What it doesn't tell you is what is influencing it. This is where histograms can be a valuable visual. Let's dig into histograms and how you can create your own.
What is a histogram?
Histograms look like bar charts.
However, the values are placed into value buckets.
For a metric like average order value (AOV), these buckets could be increments of £5, £10 etc.
You choose the buckets to represent the data you have.
Why should I use a histogram chart?
As histograms are there to bucket values, it's a useful way to analyse/visualise data that may be covered by a single top value.
In this instance we're talking AOV (average order value).
As a top line metric, it tells you the average order values of your customer orders.
What it doesn't tell you is whether you have lot's of low value orders but a few high value which skew the data. It could be the other way too. Or your order values could all be very similar.
Using a histogram chart to visualise this allows you to understand the most common values your customers generate.
You may find 70% of your customers spend under your AOV.
Just taking a further look into this I popped in a quick example below.
What this shows is just two orders with a higher value, increased the overall AOV by +32%. That is huge.
If you took that figure into your morning trade everyone would be like WOW. However, you lose a few of those orders the next week, it's sad times again.
Histogram charts are also very simple charts to create.
There are a few simple ways which I'll dig into below.
3 ways to quickly create histogram charts in excel/google sheets
We all have access to Google sheets so I'll start with a simple how to here.
Why do I use Google Sheets?
It's a great platform with powerful capabilities. It's accessible to all and has small benefits like version history for when you inevitably mess up.
Now, Google sheets has limitations, but for top line insights it's very useful. I much prefer it compared to Looker due to it's flexibility.
However, looker is useful for client facing documents.
If you want a looker version, drop me a message on LinkedIn and I'll take a look.
1Using SUMIFS
Using SUMIFS is my preferred way to do this in Google Sheets.
What I like with SUMIFS, is it's simple to create views with as many filters as you wish.
What are SUMIFS?
SUMIFS is a function that is used to sum ranges based on conditional tests.
The formula is very simple
=SUMIFS( [Range to sum] , First condition range, First condition check)
So if we take this for our task in hand we get the following:
=SUMIFS(customerOrders,orderDate,">="&Date,orderDate,"<"&Date)
The above is saying sum customer orders where order date is >= inserted date and less than other inserted date.
I'm not going to dig too much further into the formula structure as there are better educational resources you should use. First is Ben Collins SUMIFS and the second being Google's SUMIFS page. Hey, you could even ask our upcoming overlords, ChatGPT.
Using this method I usually create a table which uses the basic SUMIFS mentioned above.
The sumifs is the easy part.
What makes this method my preferred version is the next formula.
Before I dig into this formula, I want to explain what it does.
The purpose of this formula is to take the incremental value and the max value and find all the increments.
So, if you have 10 increment and 100 max, you get 10,20,30,40,50,60,70,80,90 and 100.
This means your buckets are dynamic. WIN.
This is the formula:
IF(ROW(E2:E)<=FLOOR(dashboard!G4/dashboard!F4,1),IF(ROW(E2:E)=1,E2,E2+E2*(ROW(E2:E)1)),)
This is a quick breakdown of each step in the formula.
 The formula is checking the row numbers of a range of cells (E2 to E) and comparing them to a value calculated using the "dashboard" sheet.
 If the row number is less than or equal to a certain value, it performs some calculations. Otherwise, it returns an empty result.
 If the row number is equal to 1, it returns the value in cell E2. Otherwise, it calculates a result based on the value in cell E2 and the row number.
 The formula repeats these calculations for each row in the range, resulting in a set of values or an empty result.
Overall, this method is the hardest of the two but I think the better version.
2Using Helpers and Pivots
This is the easier option of the two. The main reason why is because to get the SUMIFS version working and visually pleasing, I tend to use another formula which adds the buckets dynamically.
For that reason, if you aren't deep into formulas, I'd say start with this method.
This version uses the concept of helper columns which you can then use within a pivot table.
What is a helper column?
Helper columns are columns within your data that output values where you can then use within other formulas or formats like pivot tables.
In this instance, the helper columns are the following:
1  Order bucket values (column H). For example, if the order value is £18, I want it to show £20.
2  Date filter (column I). For example I may want to filter to week, month or quarter.
Using these helper columns enables dynamic pivot tables without having to change it within the pivot table options.
So, like with SUMIFS version, you can then change the filters and the data will change. WOOP!
However, there are a few downsides with this method.
One of the biggest for me is those helper columns. For each filter you want to add on, you will need an additional helper column or have the ability to add it to a current helper where it's relevant.
This can become quite heavy which will slow your sheet down as your data grows.
To get started we create an IF formula within column H.
Instead of myself trying to explain, I have asked chatGPT.
The formula in question is:
IF($G$2:$G<=dashboard!$G$4,CEILING($G$2:$G/dashboard!$F$4)*dashboard!$F$4,dashboard!$G$4+1)
This is the explanation.

IF(condition, value_if_true, value_if_false): This is a special function that checks if a certain condition is true or false. If it's true, it gives a specific value; otherwise, it gives a different value.

$G$2:$G: This refers to a range of cells in column G. It could be a group of numbers or values.

<=: This symbol means "less than or equal to." It's used to compare two values and check if the first value is smaller or equal to the second value.

dashboard!$G$4: This refers to a specific cell in a sheet called "dashboard." The cell being referenced is in column G and row 4.

CEILING(value): This is a mathematical function that rounds up a number to the nearest whole number or to a specified multiple. It takes a value as input.

$G$2:$G/dashboard!$F$4: This refers to a range of cells in column G divided by a cell in a sheet called "dashboard." The cell being referenced is in column F and is used as a divisor.

dashboard!$F$4: This refers to a specific cell in the "dashboard" sheet. The cell being referenced is in column F and row 4.
Now, let's put it all together:
The formula is checking a condition: whether the values in the range $G$2:$G are less than or equal to a specific value in the "dashboard" sheet (cell G4). If that condition is true, it performs some calculations. Otherwise, it gives a different value.
If the condition is true, the formula uses the CEILING function to round up the values in the range $G$2:$G divided by a specific value in the "dashboard" sheet (cell F4). Then, it multiplies that roundedup value by the same specific value (cell F4). This gives a result that is a multiple of the specific value.
If the condition is false, the formula adds 1 to the specific value in the "dashboard" sheet (cell G4).
Liam back now.
Overall, it looks at the value and rounds it up to the nearest incremental value. If 10 is inserted in the dashboard, then it will find the ceiling to the nearest 10. This means that £18 turns into 20.
This helper is important to bucket the order values.
The next is time frame.
Once again, this is an IF formula. The difference here is we just want to check if a date is >= AND < a certain date range.
If the outcome is TRUE, then we return Yes, else No.
That formula looks like this:
if(AND($A2
=cheat!$C$3),"Yes","No")
cheat in this instance is a cell reference which includes a date.
All of these references are in the name of dynamic reports. However, you don't need to worry too much about that for now.
Doing both of those helpers and then selecting pivot table from the top menu, you can then start creating your table.
3Just select data and choose histogram
This is the super easy way.
The method is just selecting data, choosing create chart and then choosing histogram (see image below).
You could add a pivot table in the middle like I have. You also get to use the time filter helper I created.
Within the histogram chart choice you get an option to select bucket size. You do this by going into the chart settings (double click or 3 dots on the chart to access) and then going to the "Histogram" tab.
Like with option 2, the biggest problem with this is adding dynamic behaviour.
With option 1, I can adjust the view from bucket size to country, dates etc with ease.
Option 2 and 3 are very reliant on helper columns or going into the graph and selecting the value. This is more of a problem if you share your reports. If you only use it and you don't mind having to go into the settings, this will be a great options..
It can work well and you may prefer that method.
That is the joy of it, there are methods to suit the needs.
Just give me the doc
You know what, you don't need this page.
Let me make this easy for you and share the doc.
Just fill in the form below and you will get it into your inbox.
 Category
 data analytics
 growth marketing