Advanced Coupon Code Analysis

Understanding the coupon performance of your business is an interesting way to segment your orders and also better understand your customers. This topic walks you through the steps to create analyses to understand which customers you acquire by using coupons, how they perform and track general coupon usage.

This analysis contains advanced calculated columns.

Getting Started

As a first step, you need to ensure that the following columns are synced to your Data Warehouse. If they are not, go ahead and track them, by navigating to Manage Data > Data Warehouse, and syncing the following:

• sales_flat_order table
• coupon_code
• base_discount_amount

Calculated Columns

Columns to create regardless of guest orders policy:

• sales\_flat\_order table

• Order has coupon applied?

  • Column type: Same Table => CALCULATION

  • Inputs:

    • A: coupon\_code

  • Datatype: String

  • Calculation: case when A is null then No coupon else Coupon end

• [INPUT] customer_id - coupon code

  • Column type: Same Table => CALCULATION

  • Inputs:

    • A: customer\_id
    • B: coupon\_code

  • Datatype String

  • Calculation: concat(A,' - ',B)

• Number of orders with this coupon

  • Column type: Same Table => EVENT\_NUMBER
  • Event Owner:INPUT customer_id - coupon code
  • Event Rank: created\_at
  • Filters: Orders we count filter set

Additional columns to create if guest orders NOT supported:

• customer\_entity table

  • Customer’s first order included a coupon? (Coupon/No coupon)

  • Column type: Many to One => MAX

  • Path: sales\_flat\_order.customer\_id = customer\_entity.entity\_id

  • Select a column: Order has coupon applied? (Coupon/No coupon)

  • Filters:

    • A: Orders we count
    • B: Customer's order number = 1

  • Customer’s first order’s coupon

    • Column type: Many to One => MAX
    • Path: sales\_flat\_order.customer\_id = customer\_entity.entity\_id
    • Select a column: coupon\_code
    • Filter:
      • A: Orders we count
      • B: Customer's order number = 1

  • Customer’s lifetime number of coupons used

    • Column type: Many to One => COUNT
    • Path: sales\_flat\_order.customer\_id = customer\_entity.entity\_id
    • Filter:
      • A: Orders we count
      • B: Order has coupon applied? (Coupon/No coupon) = Coupon

  • Coupon acquisition customer or Non-coupon acquisition customer

    • Column type: Same Table => CALCULATION

    • Inputs:

      • A: Customer's first order included a coupon? (Coupon/No coupon)

    • Datatype: String

    • Calculation: case when A=‘Coupon’ then ‘Coupon acquisition customer’ else ‘Non-coupon acquisition customer’ end

  • Percent of customer’s orders with coupon

    • Column type: Same Table => CALCULATION

    • Inputs:

      • A: User's lifetime number of coupons used
      • B: User's lifetime number of orders

    • Datatype: Decimal

    • Calculation: case when A is null or B is null or B=0 then null else A/B end

  • Customer’s coupon usage

    • Column type: Same Table => Calculation

    • Inputs:

      • A: Percent of customer's orders with coupon

    • Datatype: String

    • Calculation: case when A is null then null when A=0 then ‘Never used coupon’ when A<0.5 then ‘Mostly full price’ when A=0.5 then ‘50/50’ when A=1 then ‘Coupons only’ when A>0.5 then ‘Mostly coupon’ else ‘Undefined’ end

• sales\_flat\_order table

  • Customer’s first order included coupon? (Coupon/No coupon)

    • Column type: One to Many => JOINED\_COLUMN
    • Path: sales\_flat\_order.customer\_id = customer\_entity.entity\_id
    • Select a column: Customer's first order included a coupon? (Coupon/No coupon)
      ^

  • Customer’s first order’s coupon

    • Column type: One to Many => JOINED\_COLUMN
    • Path: sales\_flat\_order.customer\_id = customer\_entity.entity\_id
    • Select a column: Customer's first order coupon?

Additional columns to create if guest orders NOT supported:

• sales\_flat\_order table

  • Customer’s first order included a coupon? (Coupon/No coupon) - created by analyst as part of your [COUPON ANALYSIS] ticket
  • Customer’s first order’s coupon {::}- created by analyst as part of your [COUPON ANALYSIS] ticket

• Customer’s lifetime number of coupons used {::}- created by analyst as part of your [COUPON ANALYSIS] ticket

• Coupon acquisition customer or Non-coupon acquisition customer

  • Column type: Same Table => CALCULATION

  • Inputs:

    • A: Customer's first order included a coupon? (Coupon/No coupon)

  • Datatype: String

  • Calculation: case when A=‘Coupon’ then ‘Coupon acquisition customer’ else ‘Non-coupon acquisition customer’ end

• Percent of customer’s orders with coupon

  • Column type: Same Table => CALCULATION

  • Inputs:

    • A: User's lifetime number of coupons used
    • B: User's lifetime number of orders

  • Datatype: Decimal

  • Calculation: case when A is null or B is null or B=0 then null else A/B end

• Customer’s coupon usage

  • Column type: Same Table => Calculation

  • Inputs:

    • A: Percent of customer's orders with coupon

  • Datatype: String

  • Calculation: case when A is null then null when A=0 then ‘Never used coupon’ when A<0.5 then ‘Mostly full price’ when A=0.5 then ‘50/50’ when A=1 then ‘Coupons only’ when A>0.5 then ‘Mostly coupon’ else ‘Undefined’ end

Metrics

• Coupon discount amount

  • Orders we count
  • Order has coupon applied? (Coupon/No coupon)= Coupon

• In the sales\_flat\_order table

• This metric performs a Sum

• On the discount\_amount column

• Ordered by the created\_at timestamp

• Filter:

• Number of coupons used

  • Orders we count
  • Order has coupon applied? (Coupon/No coupon)= Coupon

• In the sales\_flat\_order table

• This metric performs a Count

• On the entity\_id column

• Ordered by the created\_at timestamp

• Filter:

Reports

• % of coupon-acquired and non-coupon-acquired customers

  • Metric: New customers

• Metric A: Coupon acquisitions

• Time period: All time

• Interval: None

• Group by: Coupon acquisitions customer or Non coupon acquisition customer

• Chart type: Pie

• Number of coupon-acquired and non-coupon-acquired customers

  • Metric: New customers

• Metric A: Coupon acquisitions

• Time period: All time

• Interval: By Month

• Group by: Coupon acquisitions customer or Non coupon acquisition customer

• Chart type: Stacked column

• Average lifetime revenue: Coupon Acq. (90+ days age)

  • Metric: Average lifetime revenue
  • Filter:
    • Customer’s first order included a coupon (Coupon/No Coupon) = Coupon

• Metric A: Average lifetime revenue (at least 3 months age)

• Time period: X years ago to 90 days ago

• Interval: None

• Chart type: Scalar

• Average lifetime revenue: Non-coupon Acq. (90+ days age)

  • Metric: Average lifetime revenue
  • Filter:
    • Customer’s first order included a coupon (Coupon/No Coupon) = No coupon

• Metric A: Average lifetime revenue (at least 3 months age)

• Time period: X years ago to 90 days ago

• Interval: None

• Chart type: Scalar

• Average lifetime revenue by first order coupon

  • Metric: Average lifetime revenue

• Metric A: Average lifetime revenue

• Time period: All time

• Interval: None

• Group by: Customer's first order's coupon

• Chart type: Column

• Repeat order probablility: Coupon acquisitions

  • Metric: Number of orders

  • Filter:

    • Customer’s first order included a coupon (Coupon/No Coupon) = Coupon

  • Metric: Number of orders

  • Filter:

    • Customer’s first order included a coupon (Coupon/No Coupon) = Coupon
    • Is customer’s last order? = No

  • Formula: B/A

  • Format: Percentage %

  • Select statistically significant number from Customer's by lifetime orders chart. When looking at the chart, as a good rule is to look for order numbers with 30 or more customers in the bucket. Depending on your data set, this may be a large number so feel free to add 1-10.

• Metric A: Number of orders

• Metric B: Number of non last orders

• Formula: Repeat order probability

• Time period: All time

• Interval: None

• Group by: Customer's order number

• Chart type: Bar chart

• Repeat order probability: Non-coupon acquisitions

  • Metric: Number of orders

  • Filter:

    • Customer’s first order included a coupon (Coupon/No Coupon) = No Coupon

  • Metric: Number of orders

  • Filter:

    • Customer’s first order included a coupon (Coupon/No Coupon) = No Coupon
    • Is customer’s last order? = No

  • Formula: B/A

  • Format: Percentage %

  • Select statistically significant number from Customer's by lifetime orders chart or 1-5.

• Metric A: Number of orders

• Metric B: Number of non last orders

• Formula: Repeat order probability

• Time period: All time

• Interval: None

• Group by: Customer's order number

• Chart type: Bar chart

• Coupon-acquired customers’ coupon usage rate (repeat orders)

  • Metric: New customers

  • Filter:

    • Coupon acquisition customer or Non-coupon acquisition customer = Coupon acquisition

  • Metric: Number of orders

  • Filter:

    • Customer’s order number > 1
    • Customer’s first order included a coupon? (Coupon/No coupon) = Coupon

  • Metric:Number of orders

  • Filter:

    • Customer’s order number > 1
    • Customer’s first order included a coupon? (Coupon/No coupon) = Coupon
    • Order has coupon applied? (Coupon/No coupon) = Coupon

  • Formula: C/B

  • Format: Percentage %

• Metric A: Coupon-acquired customers

• Metric B: Number of repeat orders

• Metric C: Number of repeat orders with coupon

• Formula: % of repeat orders with coupon

• Time period: All time

• Interval: None

• Chart type: Table (can transpose this table for better visualization)

• Non-coupon-acquired customers’ coupon usage rate (repeat orders)

  • Metric: New customers

  • Filter:

    • Coupon acquisition customer or Non-coupon acquisition customer = Non-coupon acquisition

  • Metric: Number of orders

  • Filter:

    • Customer’s order number > 1
    • Customer’s first order included a coupon? (Coupon/No coupon) = No coupon

  • Metric: Number of orders

  • Filter:

    • Customer’s order number > 1
    • Customer’s first order included a coupon? (Coupon/No coupon) = No Coupon
    • Order has coupon applied? (Coupon/No coupon) = Coupon

  • Formula: C/B

  • Format: Percentage %

• Metric A: Non-coupon-acquired customers

• Metric B: Number of repeat orders

• Metric C: Number of repeat orders with coupon

• Formula: % of repeat orders with coupon

• Time period: All time

• Interval: None

• Chart type: Table (can transpose this table for better visualization)

• Coupon usage details (first time orders)

  • Metric: Number of orders

  • Filter:

    • Customer’s order number = 1
    • Number of orders with this coupon > 10

  • Metric: Revenue

  • Filter:

    • Customer’s order number = 1
    • Number of orders with this coupon > 10

  • Metric: Coupon discount amount

  • Filter:

    • Customer’s order number = 1
    • Number of orders with this coupon > 10

  • Formula: B-C (if C is negative); B+C (if C is positive)

  • Format: Currency

  • Metric: Average order value

  • Filter:

    • Customer’s order number = 1
    • Number of orders with this coupon > 10

• Metric A: First time orders (FTO)

• Metric B: Revenue from FTO

• Metric C: Discounts applied to FTO

• Formula: Gross revenue from FTO

• Metric E: Average order value for FTO

• Time period: All time

• Interval: None

• Group by: coupon code

• Chart type: Table

• Number of orders with coupon (all time)

  • Metric: Number of coupons used

• Metric A: Number or orders with coupon

• Time period: All time

• Interval: None

• Chart type: Scalar

• Net revenue from orders with coupons (all time)

  • Metric: Revenue
  • Filter:
    • Order has coupon applied? (Coupon/No coupon) = Coupon

• Metric A: Net revenue from orders with coupons

• Time period: All time

• Interval: None

• Chart type: Scalar

• Discounts from coupons (all time)

  • Metric: Number of coupons used

• Metric A: Coupon discount amount

• Time period: All time

• Interval: None

• Chart type: Scalar

• Number of orders with and without coupons

  • Metric: Number of orders

• Metric A: Number of orders

• Time period: Last 24 months

• Interval: None

• Group by: Order has coupon applied? (Coupon/No coupon)

• Chart type: Stacked column

• Coupon usage among repeat users

  • Metric: New customers
  • Filter:
    • Customer’s lifetime number of orders > 1

• Metric A: New customers

• Time period: All time

• Interval: None

• Group by: Customer's coupon usage

• Chart type: Pie

• Coupon usage details

  • Metric: Number of orders with coupon

  • Filter:

    • Number of orders with this coupon > 10

  • Metric: Revenue

  • Filter:

    • Number of orders with this coupon > 10

  • Metric: Coupon discount amount

  • Filter:

    • Number of orders with this coupon > 10

  • Formula: B-C (if C is negative); B+C (if C is positive)

  • Format: Currency

  • Formula: C/(B-C) (if C is negative); C/(B+C) (if C is positive)

  • Format: Percentage

  • Metric: Average order value

  • Filter:

    • Number of orders with this coupon > 10

  • Formula: C/A

  • Format: Currency

  • Metric: Distinct buyers

  • Filter:

    • Number of orders with this coupon > 10

• Metric A: Number of orders

• Metric B: Net revenue from orders

• Metric C: Total discounts applied

• Formula: Gross revenue

• Formula: % discounted

• Metric F: Average net order value

• Formula: Average order discount

• Metric H: Distinct buyers

• Time period: All time

• Interval: None

• Group by: coupon code

• Chart type: Table

After compiling all the reports, you can organize them on the dashboard as you desire. The result may look like the image at the top of the page.

If you run into any questions while building this analysis, or simply want to engage the Professional Services team, contact support.

Business.Adobe.com resources