The Winback Trend Analysis Notebook is used to understand what different factors/characteristics that are responsible for the winback of Lapsed and Inactive users for a particular period.

What is Winback Analysis?

To understand this analysis, first, we need to understand what winback users. Winback users are users who were lapsed users earlier and then came back again to shop with our brand after the brand’s lapsed period. The visit they made after being lapsed/inactive will be considered a Winback visit and the said user will be called Winback users. These users can be either Lapsed winback or Inactive winback users.

  • Lapsed Winback: Users who were lapsed earlier.
  • Inactive Winback: Users who were inactive earlier.

For eg: A brand X has a lapsation period of 250 days and an inactive period of 450 days. A user whose recency was more than 250 until their last visit are called Lapsed Winback users and if their recency was more than 450 until their last visit are Inactive Winback users.

With this analysis, we will try to understand different factors that can trigger a winback visit for a user.

Here we will create a customer single view for the winback visit of the user. We will capture the following information here:

  • User level KPIs: User id
  • Transaction level KPIs:  bill_id, bill year, bill month, sales, quantity purchased, bill discount, last bill date before winback visit, winback visit bill date
  • Geographical KPIs: Purchased store location (City, zone, region)
  • Product KPI’s: Purchased product category

Using the above KPI’s we will find our active, winback lapsed and winback inactive users based on their recency. After this, we will continue our analysis on the lapsed and inactive winback customers.

Here, we are considering four factors that affect our Winback customers.

  1. Seasonality
    1. In this part, we are calculating monthly Winback lapsed and inactive customers. Using this we will get to know if any particular month/quarter/year is increasing our Winback rate.
    2. We will also check the year-on-year trend of Winback rate for both lapsed and inactive users.
  2. ATV and  Frequency level
    1. In this section, we will analyze our Winback customers based on their Average Transaction Value (ATV) and total visits made by them (Frequency) before their winback visit.
    2. We will divide our user base into high, mid and low based on their ATV and Visits. This exploration will lead us to know users of which segment has maximum winback customers. With this information we can target those specific customers with proper engagement/communication to make sure they are actively purchasing from the brand.
  3. Geographical Level:
    1. Here, we will take geographical level information into account to check which of our geography has maximum Winback customers. 
    2. This geographical level can be zone, region, city, area, etc. (Any 2 from these are explored in this analysis).
    3. We will get our top 10 geographies with maximum Winback customers. We can then focus on these geographies to increase our user engagement.
  4. Product Level
    1. In this section, we will analyze the product category/categories purchased by our Winback users in their winback visit. This will include a combination of categories if our user has bought more than one product category in their last visit. 
    2. Using this data, we can get our top categories or combination categories that were purchased by our Winback users. We can then know if there is any particular product category that is triggering winback of users.
    3. This exploration is done for both Lapsed & Inactive and one-time users & repeat users separately.

Understanding the Output

The first part of point 1 is simply showing how the Winback rate for lapsed and Inactive users has changed in comparison to the previous year. The remaining graphs of 1 part are a percentage distribution of users on a yearly and monthly basis, giving a yearly comparison. 

Sample Output of yearly winback rate for an apparel brand:

For point 2, cmd 31 has the excel sheet for all the users' KPIs and their respective ATV and Frequency tag. You can download this sheet to get user ids. Cmd 33 is a visual representation of the percentage distribution of users in each tag respectively.

Sample output of ATV and Frequency wise distribution of users:

For point 3, cmd 38,39,42,43 are graphs showing geographical level 1 and  2 wise winback lapsed and inactive one-timers and repeater users based on the geography. These graphs will only show us the top 10 of each Geographical level based on maximum lapsed users present.

Sample output of Top 10 Store state-wise winback inactive and lapsed, repeat users:

For point 4, cmd 48 will have  Top 7 product category combination and tag wise count of users, their sales, and average discount given to them. This is based on the products purchased by users in their win back visit. Cmd 50 to 61 is a visual representation of the users, sales and average discount for the Lapsed One timer and Repeater users and Inactive One timer and Repeater users.

Sample output of Winback Categories purchased by repeat users:

Running notebook

The required inputs which need to be entered before running the notebook:

  • Org_id of the brand
  • Start Date and End Date - Duration for which the Lapsation notebook is to be run. Format: yyyy-mm-dd 
  • Inventory table database name and Inventory table name for product category wise analysis. 
  • Primary Key Column name to join it with bill_summary to get product details.
  • Main Category Column name - Column name of the main category of the product from the Inventory table
  • Lapsed Period - Lapsed Period of the brand in terms of days
  • Inactive Period -  Inactive Period of the brand in terms of days
  • Geographical level 1 and 2 - Column names from zone_tills table for geographical analysis (For eg: state,store,city,zone,region,area,etc)

Steps to run the notebook

Notebook links: India Cluster | APAC Cluster | EMEA Cluster

  1. Input the Org ID of the brand
  2. Start date and End date for your analysis
  3. Provide lapsed period and Inactive Period days of your brand
  4. Input the column name of geographical level 1 and geographical 2 from zone_tills table of your brand.
    This analysis will return us which are top 10 geographical level 1 and geographical level 2 (i.e city, region, state, zone) that have the most Win Back Lapsed and Inactive one-timers and repeaters users.
  5. In command 29 change ATV low & mid using the outputs from cmd 26 and for frequency low and mid use output from cmd 27.
    Run the notebook again after making these changes for final outputs.
  6. For product-wise analysis, provide the database and table name of your product master.
  7. Input the primary key column name to join with bill_lineitems item_code
  8. Input the column name for the main category from the product table.
    This column should not contain any duplicate values. For eg if your column has 'Gold' and 'GOLD' in the main category then kindly change it to either one before running this notebook otherwise we will get duplicate categories in our analysis.
  9. Make necessary changes in the SQL code to add any other filters for the main category tag in cmd 10 line 51 and cmd 46 line 56. 
  10.    The sample code has '-' and 'null' values tagged as others. You can change it according to your brand's data.
  11. Change the insights and observations below each graph according to your output.