Campaign Responder Analysis

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 RateTest Spend per CustomerTest 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.