For a better reading experience, the following is the reference to different objects on Acho:
🔤: Variable | 🔘: Button | 📋: Table
Businesses sometimes struggle to allocate resources between acquiring new customers and retaining existing customers. Acquiring new customers can expand the existing customer base and create a new stream of cash flow, but it inevitably generates higher costs in marketing. On the other hand, retaining existing customers helps a business keep recurring revenue and build a long-term relationship with their customers, but it may slow down the speed of user growth. Both of these paths to revenue have some pros and cons, so the best way is to know where you are as a business and find the most optimal way of allocating your precious resources. The million dollar question is how much effort should a company put into each side? Should these 2 paths be treated equally? If you have no idea how to decide your marketing budget, we want to introduce a metric, repeat customer rate, to help you make decisions.
What is the repeat customer rate?
Repeat customer rate means the proportion of customers who have purchased or subscribed before. In other words, it shows the percentage of customers that is not a first time buyer. If the repeat customer rate is too high, like exceeding around 50%, then the company should consider spending more efforts on growth marketing to get new clients. However, if the proportion is too low (below 20%), there are two possible reasons. First, your current growth strategies work very well, so you get lots of new customers. Second, low repeat customer rate may also indicate that the customer churn rate is so high that the proportion of existing customers is too low. No matter which reason, the company should adjust its strategy and figure out some other ways to retain customers.
In sum, the repeat customer rate can answer the following questions:
- How well does the company retain customers?
- How to allocate the marketing budget between acquiring new users and retaining existing customers?
How to calculate repeat customer rate(RCR)?
Since businesses and customer behaviors are dynamic, you may need to change strategies based on important findings. It's very helpful to have an analytical pipeline to calculate and support this RCR metric automatically. Here we use Stripe data as an example to demonstrate how to build a real-time RCR pipeline to monitor your marketing strategies.
1. Import data from Stripe and keep necessary columns
First, Let’s import invoice data from Stripe. Each row represents an invoice and related information, such as who paid for it, when the customer paid. To calculate, we just need the following three columns:
- 🔤 customer_id: a unique id for each customer.
- 🔤 date: refers to when an invoice happens.
- 🔤 paid: indicates whether a payment is successful.
💡 Want to know how to set up a connection to Stripe? Check out here.
2. Set filters to remove invalid payments
First, we have to ensure all customers did make a purchase, so we can set 🔤 paid as True to ensure all transactions are valid in the data.
Second, some payments were made by internal testing, and they should be excluded. Let’s set another filter to exclude these test 🔤 customer ids.
💡 If you often need this group of filters, you can save it as your favorite and then you can use it next time.
3. Create a month column
In this example, the business offers a monthly subscription service, and it’s better to evaluate metrics by month. In order to calculate values by month, we need to transform the 🔤 date variable into months by using the "cut string" function in the "Cleanse" button.
4. Calculate the total number of customers
To calculate how many customers we have in each month, we need to use the 🔘 Pivot Table action. First drag 🔤 date in Vertical Dimensions and 🔤 customer_id in Values, and specify aggregation as Count Distinct. Through aggregating as Count Distinct, it can avoid counting the same customer repeatedly.
The following is the result table:
5. Create a new table to calculate the total number of the new customers
Now, we are going to create another table to calculate the total number of the new customers. All we need to do is to import the same data from Stripe again and do the same thing in the first three steps.
To calculate the number of new users, we first need to find out the earliest purchase date for each customer. However, the date variable is string and the computer cannot determine which string is larger or smaller. Thus, we have to transform strings into numbers. To do so, we replace dash to empty string first.
Next, change the data type to integers. Now, the system considers the date columns as numbers.
6. Pivot the table two times to calculate the total number of new customers
Then, we have to pivot the table two times. The first time is to find out the first purchase date for each customer. Put 🔤 customer_id in the vertical dimension block, and then drag the 🔤 date column into values and choose the aggregation as Min.
The second time is to calculate how many new customers there are each month. This time, we change the position of 🔤 date and 🔤 customer_id, date in Vertical Dimensions and 🔤 customer_id in Values.
After hiding the unnecessary columns, here is the result:
7. Join two tables
Now, we can go back to the 📋 Total Customers table and click the 🔘 Join button to merge two tables.
8. Calculate the number of repeat customers and RCR
Click the 🔘 Formula button to calculate the two metrics.
Here is the result.
9. Export to Visual Panel to visualize the results
The last step is to produce a dashboard with your table. Once your table is set to update from the Stripe connection, you should set a timely schedule for this dashboard. Acho offers a built-in tool called Visual Panel for this. You may also connect your Acho table to your favorite BI tools such as Tableau and Metabase.
Based on the above charts, RCR shows an increasing trend from last October and exceeds 50% in this month.The number of new users obviously declines after January. Although the numbers in March still have some uncertainty (since the data is only collected until 3/15), the company probably needs to plan some campaigns to acquire new clients.