If you’re relatively new to crunching your company’s numbers, the phrase “Business Intelligence” might seem a bit daunting. But the truth is you don’t need massive databases of complex data to get really useful insights from business intelligence tools.
In fact, with the most basic sales data you can come up with really interesting insights.
Basic Sales Data
Let’s say we have a database or a spreadsheet of sales data with the following columns: Email, Date, and Total.
What’s hiding beneath the surface of this data is the ability to split out revenue from first time customers and revenue from returning customers. Before we get down to the nuts and bolts, let’s consider the insights you can glean from this
New vs returning customers
Revenue from returning customers helps you understand how well your product and service is received by your customers. Happy customers are returning customers. (Usually. You might be in an industry where that is not the case.) On the other hand, revenue from new customers lets you know how effective your marketing / sales efforts are, and how well your website converts visitors into customers.
Here’s the tricky part though: there’s no way to know what the “correct” ratio of new customer revenue to old customer revenue is. It’s going to depend on your particular market. But by plotting these values over time, you have relative measures of how you’re doing on each. For example, if your new customer revenue is consistently growing, but your returning customer revenue is flat, you’ve got a very leaky bucket and need to improve your product or service.
On the other hand, if your new customer business has been falling over the last few months, but your returning customer revenue is holding steady, something is probably wrong with your marketing / sales effort.
At MakeStickers, we recently made an improvement to the way we show live previews for some of our stickers. The (sanitized) chart below shows what happened. New growth in new customer revenue has outpaced growth in returning customer revenue. Over time, we expect returning customer revenue to catch up with new customer revenue.
Making the Report
Let’s walk through how to build this kind of report. We’ll assume you have an Orders data table with three columns: Email, Date, and Total. In order to split orders into orders from new customers and orders from returning customers, we need a calculated column on our data that tells us whether the order is the first order for the customer.
If you’re brand new to all this, go ahead and download Microsoft’s Power BI Desktop — it’s my go-to for crunching numbers. You can also use Google’s Data Studio. Many of the concepts are the same.
Our first step will be to make a calculated table that has one row for each customer. The formula for this table is just:
Customers = DISTINCT(Orders[Email])
Be sure to add a relationship between the Email column on the Customers table and the Email column on the Orders table.
Next, we add a calculated column on our new Customers table to list the date of the first order for each customer:
FirstOrderDate = CALCULATE(MIN('Orders'[Date]), RELATEDTABLE('Orders'))
At this point, we have created a brand new calculated table that lists each customer and his or her first order date.
We can use that table to help us calculate whether each order in the Orders table is a customer’s first order or not:
IsFirstOrder = RELATED('Customers'[FirstOrderDate]) = 'Order'[OrderDate])
If an order’s date is equal to the date of the first order for that customer, then it must be the first order for that customer.
Now you can use IsFirstOrder as your Legend on any charts you want to make!
Using the same basic sales data, you can plot the average order value for new and returning customers, and the number of orders for each group.
Get Started
Tools like Power BI and Google Data Studio are free, and there are plenty of great getting started resources available. If you have any kind of data — even simple data — chances are you can uncover valuable insights if you work at it a little bit.
