The Powerful Insights Hiding in Your Sales Data

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

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

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.

The light green line is revenue from first-time customers. The dark line is revenue from returning customers.

Making the Report

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

I love talking analytics. I’m on Twitter @adamfeil.

Educational Psychology Ph.D., business analytics nerd, computer scientist, President @MakeStickers