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 .

  1. What were the best performing campaigns in a particular duration?
  2. Which campaigns helped the most in making the single frequency customers to visit again?
  3. 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 ParamDescription
Org_IDEnter the org_ID for which you want to perform the campaign analysis.
Start_DateEnter the start date of the duration for which you want to perform the analysis. Format: yyyy-mm-dd.
End_DateEnter the end date of the duration for which you want to perform the analysis. Format: yyyy-mm-dd.
Campaigns to be excludedEnter 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 ParamDescription
OU_IDYou 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 ParamDescription
DatabaseEnter the database name in which the segments table is created.
User segments tableEnter 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 segmentEnter the column name, which has the segment labels for each user ID.

Notebook links

Open your cluster-specific link provided for the Notebook.

NotebooksCluster links
Campaign Responder AnalysisIndia    SEA
EMEA
Campaign Responder Analysis- Adding OUIndia    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 ParamDescription
Campaign IDThe unique ID of the campaign.
RecencyThe recency of customers before the campaign start date.
FrequencyThe frequency of customers before the campaign start date.
Test contactedThe total number of customers contacted out of each recency and frequency buckets for a particular campaign.
Control contactedThe total number of control group customers contacted out of each recency and frequency buckets for a particular campaign.
Test respondedThe total number of customers responded from each recency and frequency buckets for a particular campaign and were in the test group.
Control respondedThe total number of control group customers responded from each recency and frequency buckets for a particular campaign.
Test responder salesThe total sales generated by the test group recipients during a particular campaign.
Control responder salesThe total sales generated by control group customers during a particular campaign.
Test billsTransaction of the test group members for a particular campaign.
Control billsTransaction 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 ParamFormulas
Test Hit Rate

Formula for calculating the test hit rate is TestResponders/TestContacted.

Control Hit RateFormula for calculating the test hit rate is ControlResponders/ControlContacted.
Incremental Hit RateFormula 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 SalesFormula 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.