This notebook aims to understand the responses to a campaign from different recency and frequency segments of the customers.
The following are the main insights which can be drawn from the output of this notebook .
- What were the best performing campaigns in a particular duration?
- Which campaigns helped the most in making the single frequency customers to visit again?
Which campaigns enabled the most winbacks, that is getting the lapsed customers?
Input parameters
You need to enter the following details to run the responder analysis notebook.
Input Param | Description |
Org_ID | Enter the org_ID for which you want to perform the campaign analysis. |
Start_Date | Enter the start date of the duration for which you want to perform the analysis. Format: yyyy-mm-dd. |
End_Date | Enter the end date of the duration for which you want to perform the analysis. Format: yyyy-mm-dd. |
Campaigns to be excluded | Enter the campaign IDs to exclude the campaigns from the analysis. Leave the field blank if there are no campaigns to exclude. |
The following parameter is only required if you want to run the OU version of this notebook.
Input Param | Description |
OU_ID | You can also see the results by specific OUs. To view the results by OU, use the OU version of this notebook. |
The following parameters are only required if the end-user also wants to view the output by any other segments created for the org/brand.
Input Param | Description |
Database | Enter the database name in which the segments table is created. |
User segments table | Enter the name of the user segment table. |
Field to identify user ID | Enter the column name, which has the capillary's system-generated user ID in the user segments table. |
Field to identify segment | Enter the column name, which has the segment labels for each user ID. |
Notebook links
Open your cluster-specific link provided for the Notebook.
Notebooks | Cluster links |
Campaign Responder Analysis | India SEA EMEA |
Campaign Responder Analysis- Adding OU | India SEA EMEA |
Campaign Responder Analysis - Part 2 (adding Advanced Segments) | India SEA EMEA |
Output parameters
The final output is a data frame with the following columns.
Output Param | Description |
Campaign ID | The unique ID of the campaign. |
Recency | The recency of customers before the campaign start date. |
Frequency | The frequency of customers before the campaign start date. |
Test contacted | The total number of customers contacted out of each recency and frequency buckets for a particular campaign. |
Control contacted | The total number of control group customers contacted out of each recency and frequency buckets for a particular campaign. |
Test responded | The total number of customers responded from each recency and frequency buckets for a particular campaign and were in the test group. |
Control responded | The total number of control group customers responded from each recency and frequency buckets for a particular campaign. |
Test responder sales | The total sales generated by the test group recipients during a particular campaign. |
Control responder sales | The total sales generated by control group customers during a particular campaign. |
Test bills | Transaction of the test group members for a particular campaign. |
Control bills | Transaction of the control group members for a particular campaign. |
Understanding the Output
The end user will have to extract the table in the form of a csv file to the local system. The following is the output.
The following are the fields displayed in the image.
- Recency: Values in the recency field represent the recency bucket of customers to which they belonged before they were targeted for the respective campaign. For example, the value “0to3” means that customers’ recency was within 0 to 3 months.
- Frequency: Values in the frequency field represent the frequency bucket of customers to which they belonged before they were targeted for the respective campaign. For example, the value “1” means that the customer had only visited the store once before being targeted for the respective campaign. Similarly, the value “2to3” contains all the customers who had visited more than once but less than or equal to 3 times before they were targeted for the respective campaign.
The following are the extra columns that you can add in the table.
Output Param | Formulas |
Test Hit Rate | Formula for calculating the test hit rate is TestResponders/TestContacted. |
Control Hit Rate | Formula for calculating the test hit rate is ControlResponders/ControlContacted. |
Incremental Hit Rate | Formula for calculating the incremental hit rate is Test Hit Rate - Control Hit Rate (if Test Hit Rate > Control Hit Rate, otherwise 0). |
Test Spend per Customer | Formula for calculating the test spend per customer is Test Responder Sales/Test Responders. |
Incremental Sales | Formula for calculating the the incremental sales is Incremental Hit Rate*Test Spend per Customer*Test Contacted. |
Creating the Pivot
You can create a pivot table after selecting the entire data set to get some insights for the responder analysis. Following are few examples of pivot tables.
- Campaign Overview: You can select the entire data set and insert a pivot table in a new sheet to create a campaign overview. Put the campaign_id on the row level, and all the different KPIs in values. This way user can get the overall campaign performance at a campaign level.
- One Timer to Repeat Conversion: For finding out which campaigns have helped in converting the one timers to repeaters, you can put Frequency field at the rows, campaign_id at columns and Test hit rate in values. Campaigns with comparatively higher hit rates for the frequency value as one are the ones that have helped in repeat conversions the most.
- Lapsed Win Back: For finding out which campaigns have helped in winning back the customers from lapsed recency buckets, follow the same process for One Timer to Repeat Conversion but with Recency at row levels. Campaigns which have higher hit rates for the higher recency buckets, that is for the ones which are greater than one year then those campaigns have helped in winning back the lapsed customers.
The KPIs used here for comparison are set as Test Hit Rate. You can also use any other parameter to analyze the campaign. |