Discount sensitivity

📊 Business Context

What is it about?

Discount sensitivity (or Promophily) assess how much a customer is sensitive to discounts.

By creating a customer segmentation based on the promophily score, you can focus on or exclude customers sensitive to discounts.

What are the main use cases?

Push new offers > Adjust your marketing pressure and your message depending on discount sensitivity.

Control your margin > Exclude from campaigns customers that buy only discounts offers.

What business impact can I expect?

Impact on: News sells, Retention, Acquisition

Level of impact: ⭐️⭐️️⭐️️

↔️ Inputs & Outputs

Data input

You will need

a dataset “Contacts” with at least the following attributes:

contact_id: unique identifier of the customer.

a dataset “Orders” with at least the following attributes:

contact_id: unique identifier of the customer.
order_id: unique identifier of the transaction.
order_date: date of the transaction.
total_amount: total amount of the transaction.
discount_amount: total amount of the transaction.

User Settings

The user can :

Adjust the exclusion parameter of customers with less than N orders (default is 1).

Adjust the ponderation between the weight of following two parameters in the scoring :

discount_value_percent (default is 80%)
discount_count_percent (default is 20%)

Our approach

Step 1

For each customer we compute:

discount_count_percent = Number of orders with a discount / Total number of orders

discount_value_percent = Total amount of discount / Total value of orders

Step 2

We compute a weighted_discount_percent attribute that represents the overall customer sensitivity to discount offers.

This attribute is computed as a weighted average of discount_count_percent and discount_value_percent (see default ponderation in User settings).

Step 3

In order to facilitate segmentation, we compute a last attribute called discount_sensitivity which splits your customer base into deciles based on the weighted_discount_percent value.

So a customer who reaches a discount_score of 10, it means that he belongs to the 10% of customers that are the more sensitive to discounts.

Expected output

The output of this recipe is a new Datasset with 2 output columns :

discount_count_percent : share of orders including a discount offer (from 0 to 100).
discount_score : decile of customer sensitivity to discount offers (from 1 to 10).

You can then make a join between your new Discount Dataset and your existing Contacts Dataset to map your discount_score for instance.

🖥️ Implementation in Octolis

1. Prepare your input data

Make sure that you have built a Dataset containing all your orders.

👉 Create a dataset

In case you have several order Sources (e.g. eShop & PoS) you need to group all sources into an orders master Dataset.

👉 Add a source to an existing dataset

2. Apply the SQL Template “Discount Sensibility”

Create a new Dataset using SQL expert mode.

Copy / past the following SQL template.

After pasting the template, make sure to modify all variables with the names of your input columns.

Add your Discount Sensibility Dataset as a source of your Contacts Dataset, map the needed fields to import them into your Contacts Dataset.

sql
/* Discount Sensibility Segmentation SQL Template */

WITH discount_percents AS (SELECT "contact_id",
                                  COUNT(*) AS "orders_count",
                                  (COUNT(*) FILTER (WHERE "discount_amount" <> 0 AND "discount_amount" IS DISTINCT FROM NULL))::FLOAT / COUNT(*)  AS "discount_count_percent",
                                  (CASE
                                       WHEN SUM("total_amount") <> 0 THEN SUM("discount_amount") / SUM("total_amount")
                                       ELSE 0 END) AS "discount_value_percent"
                           FROM raw.orders
                           WHERE
                               /* Optional filters depending on your dataset */
                               "total_amount" > 0
                             AND "completed" IS TRUE
                           GROUP BY "contact_id"),

     weighted_percent_average AS (SELECT "contact_id",
                                         "discount_count_percent",
                                         ("discount_value_percent" * 0.8) + ("discount_count_percent" * 0.2) AS "weighted_discount_percent"
                                  FROM discount_percents
                                  WHERE "orders_count" > 1
     )

SELECT "contact_id", "discount_count_percent", 0 AS "discount_score"
FROM weighted_percent_average
WHERE "discount_count_percent" = 0
UNION
SELECT "contact_id",
       "discount_count_percent" * 100 AS "discount_count_percent",
       NTILE(10) OVER (ORDER BY "weighted_discount_percent", "contact_id") AS "discount_score"
FROM weighted_percent_average
WHERE "discount_count_percent" <> 0;