acho_studio_metric_rcr_visualization
acho studio metric rcr visualization

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
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.
acho_studio_metric_rcr_import_stripe_data
acho studio metric rcr import stripe data

💡 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.

acho_studio_metric_rcr_filter
acho studio metric rcr filter

💡 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.

acho_studio_rcr_date_cut_string
acho studio metric rcr date cut string

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.

acho_studio_metric_rcr_pivot_table_total_customers
acho studio metric rcr pivot table total customers

The following is the result table:

acho_studio_metric_rcr_results_total_customers
acho studio metric rcr results total customers

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.

acho_studio_metric_rcr_create_table_new_customers
acho studio metric rcr create table new customers

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.

acho_studio_metric_rcr_replace_date
acho studio metric rcr replace date

Next, change the data type to integers. Now, the system considers the date columns as numbers.

acho_studio_metric_rcr_change_data_type
acho studio metric rcr change data type

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.

acho_studio_metric_rcr_pivot_table_first_purchase
acho studio metric rcr pivot table first purchase

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:

acho_studio_metric_rcr_results_new_customers
acho studio metric rcr results new customers

7. Join two tables

Now, we can go back to the 📋 Total Customers table and click the 🔘 Join button to merge two tables.

acho_studio_metric_rcr_join_table
acho studio metric rcr join table

8. Calculate the number of repeat customers and RCR

Click the 🔘 Formula button to calculate the two metrics.

acho_studio_metric_rcr_formula
acho studio metric rcr formula

Here is the result.

acho_studio_metric_final_results
acho studio metric rcr final results

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.

acho_studio_metric_visualization_2
acho studio metric rcr visualization 2

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.