Perform a customer purchase cycle analysis (how often you buy the same product) to help improve your repeat rate. The more purchases you make and the shorter the purchase interval, the more profit you make, so it’s important to understand how many customers buy your product over a specific period of time.
What is Purchase Cycle Analysis?
Analyze how many existing customers purchase your product how often and in a particular period of time.
How to analyze the purchase cycle:
Cross-analysis is performed on the two axes of the number of purchases and the purchase interval.
- Number of purchases – Number of purchases from the first purchase date
- Purchase Interval – Days from the last purchase date to the current purchase date
Example: The table below is a two-axis cross-analysis of purchase count and purchase interval over the last year.
First, let’s look at the horizontal axis (number of purchases). Eleven people made their second purchase within 10 days. Similarly, 13 people made their second purchase within 20 days. Confirm like this.
What can be read from the horizontal axis (number of purchases):
- 67% of customers make a second purchase within 50 days.
- The most common third purchase is within 20 days after the second purchase.
- 78% of customers make a third purchase within 50 days of the second purchase.
- Purchases from the 8th time onward have decreased significantly.
Next, check the vertical axis (purchase period):
- The most common purchase cycle is 30 days, which is 27% of the total.
- 72% of the purchase cycle is within 50 days.
Possible action plans based on purchase cycle data:
- Consider how to get the first purchaser to make a second purchase within 50 days. (The number of purchases will decrease significantly after 50 days)
- Consider a method for making the third and subsequent purchases within a purchase cycle of 30 days or less.
How to create a purchase cycle table
1. Create a table containing the following elements.
Converts the actual purchase interval (purchase interval 2) to an approximate purchase interval (within 10 days, within 20 days, etc.).
Set the function below to the cell with purchase interval 1 to convert the data with purchase interval 2.
= IF (G2)<= 10, “less than 10 days”, IF (AND (G2)>10, G2<= 20), “less than 20 days”, IF (AND (G2)>20, G2<= 30), “less than 30 days”, IF (AND (G2)>30,G2<= 40), “less than 40 days”, IF (AND (G2)>40, G2<= 50), “less than 50 days”, IF (AND (G2)>50, G2<= 60), “less than 60 days”, IF (AND (G2)>60, G2<= 70), “less than 70 days”, IF (AND (G2)>70, G2<= 80), “less than 80 days”, IF (AND (G2)>80, G2<= 90), “less than 90 days”, IF (AND (G2)>90, G2<= 100), “less than 100 days”, IF (AND (G2)>100, G2<= 110), “less than 110 days”, IF (AND (G2)>110, G2<= 120), “less than 120 days”, IF (AND (G2)>120, G2<= 130), “less than 130 days”, IF (AND (G2)>130, G2<= 140), “less than 140 days”, IF (AND (G2)>140, G2<= 150), “less than 150 days”, IF (AND (G2)>150, G2<= 160), “less than 160 days”, IF (AND (G2)>160, G2<= 170), “less than 170 days”, IF (AND (G2)>170, G2<= 180), “less than 180 days”, “more than 180 days”)))))))))))))))))
G2: Rename the cell with purchase interval 2.
2. Convert to a pivot table
3. Set the pivot table
Set the number of orders to “Column” and the purchase interval 1 to “Row”.
Also, set the number of orders to Value, click on it, select Set Value Field, and change the value from Total to Quantity of Data.
“Total” displays the total number of orders. For example, if two customers make 13 purchases, the “total” will display “26”. What we want to know is not the total, but how many customers have purchased 13 times. “Number of data” counts the number of data, and in this case, the number of customers “2” is displayed.
Second, basically the pivot table isn’t sorted from new to old, so sort the rows in the following way: Click the down arrow for Row Labels, select Sort Options, and set it to Manual.
Then, you can move the rows by dragging and dropping, so swap the rows so that they are arranged in order from the shortest period to the longest period.
Finally, color the data to make it easier to visually understand the weighting. Select the range you want to color, then home> Select New Rule from Conditional Formatting.
Select “3-color scale”.
If you include the data of subscribers or regular buyers in the database, you can set it in the filter to display the purchase cycle separately.
Factors that influence the purchase cycle
First, consider how best the cycle is based on the quantity and durability of the products we handle. If the product is a supplement, how many days will it take to consume it all? Also, if you are offering a home remodeling service, when will your customers reconsider their home remodeling? As a general premise, the purchase cycle depends on the products and services you are dealing with.
However, people’s memory is the same whether it is a product with a short purchase cycle or a product with a long purchase cycle. If you’re only dealing with products with a long purchase cycle, it’s a good idea to consider dealing with products with a shorter cycle to retain existing customers. Next, consider marketing measures aimed at reducing the long purchase cycle to the most purchase cycle (many users repeat the product in this cycle). In this case, it is within 30 days. Without positive action from us, such as events and campaigns, existing customers will forget about the store.