Join Rudolph Rosenberg for an in-depth discussion in this video New vs. returning customers, part of Financial Analysis: Analyzing the Top Line with Excel.
- View Offline
Another insightful analysis is about new versus returning customers. What is the proportion of business you're doing through returning customers, and what proportion through new customers? As we described earlier, your customer base is based on how often you expect your customers to buy from you. If it is every year, then all your customers that bought from you the past year are your current customers. And any new purchase from them will be considered as business from a returning customer.
And all the rest will be consider business from new customers. If we go back to our spreadsheet, you can see here that we have the revenue we have generated with each of our customers over the years 2013 and 2014. As you can see, some customers have business with us only in 2014 where other customers have business with us in both years. Customer 10, for example, has been buying from us $3,703 of business in 2014. That's a new customer. And Customer 11 has been buying from us in 2013 and 2014.
So this year, he's a returning customer. Now, it's very important for us to track the business we're doing with returning customers or new customers because the cost of doing business with new customers for example is much higher. Getting to start business with new customers is usually more expensive for the company. So you want to make sure that you maximize the business that you do with your current customer base. Now, what we want to do is put in front of each of those customers if it's a new customer or returning customer. To do so, let's select this cell that shows before our number star and select certain filter, and then filter. Now, we want to select here in 2013 only those that are blank which means they have no revenue whatsoever, and with which we have revenue in 2014. And we put new in front of each one of them to say those are our new customers. Then we want to re-select all of that, click okay, and this time, select only those customers that have business in both years.
So, here in 2013, we'll deselect the blank ones, the ones that has no business in 2013, click okay. And then in 2014, again, we'll unselect all those that have no revenue. Okay, here we have our customers that have business in both years and we put returning and just drag it down for all of them. Now, we reselect everything. And we should have all of our customers that are either new customers or returning customers.
And for the rest of our customers that are neither new or returning customers, let's call them current customers because they're in our customer base right now. We're still four months into the year, so they still have plenty of time to buy from us. To do so, let's just select those customers that have been doing business with us last year, by deselecting the blank ones. Click okay. And in 2014, let's deselect all of them and reselect only those that have no business with us, the blanks. Click okay. And those customers, we'll call them current, press enter, and then drag it down.
Once we have done that, we go to certain filter and clear, that will unselect all the selections we've made so far. And now we're done and have our customer base categorized between new customers, returning customers, and current customers. Now that we have done that, we want to be able to use that information in our pivot table so that it can be used in many different analytics. To do so, we'll go into our data page. And just before the customer ID number column, let's add a column.
We'll call that column new versus returning, and press enter. And here, we will use a vlookup function to take all the information we have prepared in the pivot tab and bring it back into this tab. If you want to have more information on the usage of lookup functions, there's a great course called Excel 2013 Advanced Formulas and Functions with Dennis Taylor. So, lets do it now.
So equal vlookup open parenthesis, then select the customer name, comma, in the pivot page. Select the columns A to E. And then press comma again. And then we'll select the fifth column. Put an extra comma, a closing parenthesis and press enter. As you can see Customer 37 is a current customer. And I will drag down this formula for all of our invoices. Now that this information is included in our data tab, what we need to do is first paste it in values, so that we can keep this information from changing.
To do so, we'll just select the entire column, click on copy. And then in the paste section, paste as values. The formula has been now removed. And only the result is kept in the cells. Let's go back now to the pivot page. And let's remove the information we have in column E. As we click now on the pivot table and hit refresh, you can see that the field new versus returning has appeared. We can now take that field, and bring it into our row section of the pivot table. And as we add it, we can see here that our customers are now sorted by the type of customers they are.
So you have your current customers right now, Customers 13 down to Customer 8, the new customers and our returning customers. You can even go a step further, and remove the customer name information, and start analyzing your business in terms of how much business you're doing with current customers, with new customers, and returning customers. Having new customers is great for your company. It means that your customer base is growing and that you therefore have a bigger pool from which to generate business. By knowing the proportion of revenue coming from new versus returning customers, you know how much your business relies on customer acquisition and on selling time and again to the same customers.
Both new and returning customers are important. It is more expensive to acquire new customers, but because every company loses customers now and then, it is important to ensure that you gain more customers than you lose, and that the customer base overall increases. Returning customers, on the other hand, already know you and are potentially satisfied with your product or services, and could be easier to sell to in the future. So having a lot of returning customers is a good sign.
It means that your customers are satisfied and that you can work to develop further your relationship with them, potentially selling them more product and services in the future. It is important though to strike a certain balance between new and returning customers.
Also check out the companion course, Financial Analysis: Analyzing the Bottom Line with Excel.
Lynda.com is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Preparing your revenue information
- Comparing past performance
- Analyzing customer data
- Analyzing product information
- Identifying exceptional revenue