How to know purchase cycle of customers?

7Apr - by OnoHaruyo - 0 - In Visualize existing customers

Purchase cycle analysis

Increasing repeat customers means to increase people who will regularly purchase products from your shop.

When considering repeat strategy, it’s good to better understand that the number of customers and the number of times they purchase your products in a certain period of time. Purchase cycle analysis is a way to know it. The increase in the number of purchase and when the interval of purchase shortens, the more profit you take.

The analysis will allow you to know and visualize the situation of your existing customers in terms of the number of purchase and the duration of purchase.

Purchase cycle analysis:
Analyzing how many existing customers and how many times they purchased products during a certain period of time.

The way of analysis:
Performing cross analysis on two axes, “Purchase times” and “Purchase interval”.

  • Purchase times – The times of purchase from the date when the customers first purchased products.
  • Purchase interval – The interval of purchase from the date the customers purchased products previously, to the current date they purchased products.

Example:
This table is made of the two axes using the purchase database during recent a year.

Purchase cycle analysis

You can see the horizontal axis of the table first. There were 11 people who purchased products twice within 10 days. Like this, you can see that there were 13 people who purchased products twice within 20 days.

The horizontal axis shows the following :

  • 67% of customers purchased twice within 50 days.
  • The most frequent third purchase was within 20 days after second purchase.
  • 78% of customers purchased thrice within 50 days.
  • The number of purchase decreased drastically after the 8th purchase.

Next, you can look at the vertical axis:

  • The most frequent purchase cycle was 30 days cycle, and it was 27%.
  • 72% of the purchase cycle was within 50 days.

The action plans you may consider based on the data :

  • Consider how to make the second purchase within 50 days for the first-time customers, since it decreases drastically if the date of the second purchase is over 50 days.
  • Consider how to make customers do more than three purchases within the 30 days purchase cycle.

How to make the table.

1.First, you need to make the table by including the following.

purchase database

Japanese words mean this:

Date of first-time purchase Date of last-time purchase Number of purchase Purchase interval 1 (Rough interval) Purchase interval 2 (Actual date of interval)

You need to convert the actual date of purchase interval (Purchase interval 2) to rough interval like within 10 days, within 20 days etc.

Excel formula:
You set this function to the cell of the “Purchase interval 1” to convert the data of “Purchase interval 2” to the rough interval.

=IF(G2<=10,"10日未満",IF(AND(G2>10,G2<=20),"20日未満",IF(AND(G2>20,G2<=30),"30日未満",IF(AND(G2>30,G2<=40),"40日未満",IF(AND(G2>40,G2<=50),"50日未満",IF(AND(G2>50,G2<=60),"60日未満",IF(AND(G2>60,G2<=70),"70日未満",IF(AND(G2>70,G2<=80),"80日未満",IF(AND(G2>80,G2<=90),"90日未満",IF(AND(G2>90,G2<=100),"100日未満",IF(AND(G2>100,G2<=110),"110日未満",IF(AND(G2>110,G2<=120),"120日未満",IF(AND(G2>120,G2<=130),"130日未満",IF(AND(G2>130,G2<=140),"140日未満",IF(AND(G2>140,G2<=150),"150日未満",IF(AND(G2>150,G2<=160),"160日未満",IF(AND(G2>160,G2<=170),"170日未満",IF(AND(G2>170,G2<=180),"180日未満","180日以上"))))))))))))))))))

Note:
G2: You should change this name based on your cell name of “Purchase interval 2”
日未満: within x days
日以上: over x days

2.Convert the table to pivot table.

Convert the table to pivot table

3.Set the pivot table.

Set the Number of purchase as Columns and set the Purchase interval 1 as Rows of the pivot table.

Set the pivot table

Also, you should set the Number of purchase as Values. Then, you click it and “Value field setting…” so that you change the figure of Values from Sum to Count.

Value field setting

Sum shows the total amount of the number of purchases. If there are 2 customers who purchase products for 13 times, the Sum shows “26”. But we want to know how many people purchased products for 13 times. Count function can show the number of data, in this case, it’s the number of customers “2”.

Basically, the table doesn’t show the Row sorted from new date to old date, so, you need to sort it this way to easily understand the data. Click the down arrow of the “Row Labels”, then click “More Sort Options…” and then set to “Manual” as sort options.

More Sort Options

Now, you can drag & drop the rows to rearrange them from short-interval to long-interval.

rearrange cells

Finally, you can assign colors to each cell to know which cell has low or high data very easily. Select the range of the table, and click “Conditional Formatting” and then set the New Rule.

assign colors to each cell

Select “3-Color Scale”.

assign colors to each cell

Finished.

Purchase cycle analysis

If you have the data that includes whether it’s regularly purchased, then, set the element to Filters so that you can see the table separated from regularly purchased or not.

regularly purchased

What kinds of things can affect the purchase cycle?

First, you should consider when the best interval of purchase cycle in terms of the volume or durability of your products.

If you provide supplements to customers, how long does it take them to consume all of their supplements? Also, if you provide reform service for houses when will the customer consider to reform of their home again?

First of all, purchase cycle depends on products or services. However, what is important is the fact that the consumer’s memory is the same regardless of short-interval or long-interval products they purchased.

If you deal with only long-interval products, it’s good to consider to have more variety of short-interval products to keep existing customers. This is the business level matters.

Next, you should consider many marketing activities to shorten the purchase cycle into the most frequent cycle. In this case, it’s 30 days. Without the marketing activities like sending e-mail magazines with some events or campaigns, existing customers will forget your shop. This is the marketing level matters.