📊 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.
In case you have several order Sources (e.g. eShop & PoS) you need to group all sources into an orders master 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;