Magento Business Intelligence Help Center

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 article will walk you through the steps to create analyses to understand which customers you acquire through the use of coupons, how they perform, and track general coupon usage.

 

This analysis contains advanced calculated columns that can only be created by a Magento BI analyst. Once you've completed the Getting Started section, file a support request with the subject [COUPON ANALYSIS], before moving to the next section.

  • sales_flat_order table
    • coupon_code
    • base_discount_amount

Columns to create

  • sales_flat_order table
    • Order has coupon applied? will be created by an analyst as part of your [COUPON ANALYSIS] ticket
    • [INPUT] customer_id - coupon code will be created by an analyst as part of your [COUPON ANALYSIS] ticket
    • Number of orders with this coupon will be created by an analyst as part of your [COUPON ANALYSIS] ticket
  • customer_entity table
    • Customer's first order included coupon? (Coupon/No coupon)
      • Select a definition: Max
      • Select table: sales_flat_order
      • Select column: Order has coupon applied? (Coupon/No coupon)
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
      • Filter:
        • Orders we count
        • Customer's order number = 1
    • Customer's first order's coupon
      • Select a definition: Max
      • Select table: sales_flat_order
      • Select column: coupon_code
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
      • Filter:
        • Orders we count
        • Customer's order number = 1
    • Customer's lifetime number of coupons used
      • Select a definition: Count
      • Select table: sales_flat_order
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
      • Filter:
        • Orders we count
        • Order has coupon applied? (Coupon/No coupon) = Coupon
    • Coupon acquisition customer or Non coupon acquisition customer will be created by an analyst as part of your [COUPON ANALYSIS] ticket
    • Percent of customer's orders with coupon will be created by an analyst as part of your [COUPON ANALYSIS] ticket
    • Customer's coupon usage will be created by an analyst as part of your [COUPON ANALYSIS] ticket
  • sales_flat_order table
    • Customer's first order included coupon? (Coupon/No coupon)
      • Select a definition: Joined Column
      • Select table: customer_entity
      • Select column: Customer's first order included a coupon? (Coupon/No coupon)
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
    • Customer's first order's coupon
      • Select a definition: Joined Column
      • Select table: customer_entity
      • Select column: Customer's first order coupon?
      • Path: sales_flat_order.customer_id = customer_entity.entity_id
  • Coupon discount amount
    • In the sales_flat_order table
    • This metric performs a Sum
    • On the discount_amount column
    • Ordered by the created_at timestamp
    • Filter:
      • Orders we count
      • Order has coupon applied? (Coupon/No coupon)= Coupon
  • Number of coupons used
    • In the sales_flat_order table
    • This metric performs a Count
    • On the entity_id column
    • Ordered by the created_at timestamp
    • Filter:
      • Orders we count
      • Order has coupon applied? (Coupon/No coupon)= Coupon

Note: Make sure to add all new columns as dimensions to metrics before building new reports.

  • % of coupon-acquired and non-coupon-acquired customers
    • Metric A: Coupon acquisitions
      • Metric: New customers
    • 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 A: Coupon acquisitions
      • Metric: New customers
    • 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 A: Average lifetime revenue (at least 3 months age)
      • Metric: Average lifetime revenue
      • Filter:
        • Customer's first order included a coupon (Coupon/No Coupon) = Coupon
    • Time period: X years ago to 90 days ago
    • Interval: None
    • Chart Type: Scalar
  • Average lifetime revenue: Non-coupon Acq. (90+ days age)
    • Metric A: Average lifetime revenue (at least 3 months age)
      • Metric: Average lifetime revenue
      • Filter:
        • Customer's first order included a coupon (Coupon/No Coupon) = No coupon
    • Time period: X years ago to 90 days ago
    • Interval: None
    • Chart Type: Scalar
  • Average lifetime revenue by first order coupon
    • Metric A: Average lifetime revenue
      • Metric: Average lifetime revenue
    • Time period: All time
    • Interval: None
    • Group by: Customer's first order's coupon
    • Chart Type: Column
    • NOTE: If you have a large number of coupon codes, as many of our clients do, you will want to apply a Top/Bottom such as Top 10 sorted by Avg lifetime revenue
  • Repeat order probablility: Coupon acquisitions
    • Metric A: Number of orders
      • Metric: Number of orders
      • Filter:
        • Customer's first order included a coupon (Coupon/No Coupon) = Coupon
    • Metric B: Number of non last orders
      • Metric: Number of orders
      • Filter:
        • Customer's first order included a coupon (Coupon/No Coupon) = Coupon
        • Is customer's last order? = No
    • Formula: Repeat order probability
      • Formula: B/A
      • Format: Percentage %
    • Time period: All time
    • Interval: None
    • Group by: Customer's order number
      • Select statistically significant number from "Customer's by lifetime orders" chart. When looking at the chart, as a good rule we typically 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.
    • Chart Type: Bar chart
  • Repeat order probablility: Non-coupon acquisitions
    • Metric A: Number of orders
      • Metric: Number of orders
      • Filter:
        • Customer's first order included a coupon (Coupon/No Coupon) = No Coupon
    • Metric B: Number of non last orders
      • Metric: Number of orders
      • Filter:
        • Customer's first order included a coupon (Coupon/No Coupon) = No Coupon
        • Is customer's last order? = No
    • Formula: Repeat order probability
      • Formula: B/A
      • Format: Percentage %
    • Time period: All time
    • Interval: None
    • Group by: Customer's order number
      • Select statistically significant number from "Customer's by lifetime orders" chart or 1-5
    • Chart Type: Bar chart
  • Coupon-acquired customers' coupon usage rate (repeat orders)
    • Metric A: Coupon-acquired customers
      • Metric: New customers
      • Filter:
        • Coupon acquisition customer or Non coupon acquisition customer = Coupon acquisition
    • Metric B: Number of repeat orders
      • Metric: Number of orders
      • Filter:
        • Customer's order number > 1
        • Customer's first order included a coupon? (Coupon/No coupon) = Coupon
    • Metric C: Number of repeat orders with 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: % of repeat orders with coupon
      • Formula: C/B
      • Format: Percentage %
    • 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 A: Non-coupon-acquired customers
      • Metric: New customers
      • Filter:
        • Coupon acquisition customer or Non coupon acquisition customer = Non coupon acquisition
    • Metric B: Number of repeat orders
      • Metric: Number of orders
      • Filter:
        • Customer's order number > 1
        • Customer's first order included a coupon? (Coupon/No coupon) = No coupon
    • Metric C: Number of repeat orders with 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: % of repeat orders with coupon
      • Formula: C/B
      • Format: Percentage %
    • Time period: All time
    • Interval: None
    • Chart Type: Table (can transpose this table for better visualization)
  • Coupon usage details (first time orders)
    • Metric A: First time orders (FTO)
      • Metric: Number of orders
      • Filter:
        • Customer's order number = 1
        • Number of orders with this coupon > 10
    • Metric B: Revenue from FTO
      • Metric: Revenue
      • Filter:
        • Customer's order number = 1
        • Number of orders with this coupon > 10
    • Metric C: Discounts applied to FTO
      • Metric: Coupon discount amount
      • Filter:
        • Customer's order number = 1
        • Number of orders with this coupon > 10
    • Formula: Gross revenue from FTO
      • Formula: B-C (if C is negative); B+C (if C is positive)
      • Format: Currency
    • Metric E: Average order value for FTO
      • Metric: Average order value
      • Filter:
        • Customer's order number = 1
        • Number of orders with this coupon > 10
    • Time period: All time
    • Interval: None
    • Group by: coupon code
    • Chart Type: Table
    • Note: The quantity of 10 for "Number of orders with this coupon" is arbitrary. Feel free to use the most appropriate quantity for this filter.
  • Number of orders with coupon (all time)
    • Metric A: Number or orders with coupon
      • Metric: Number of coupons used
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Net revenue from orders with coupons (all time)
    • Metric A: Net revenue from orders with coupons
      • Metric: Revenue
      • Filter:
        • Order has coupon applied? (Coupon/No coupon) = Coupon
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Discounts from coupons (all time)
    • Metric A: Coupon discount amount
      • Metric: Number of coupons used
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
  • Number of orders with and without coupons
    • Metric A: Number of orders
      • Metric: 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 A: New customers
      • Metric: New customers
      • Filter:
        • Customer's lifetime number of orders > 1
    • Time period: All time
    • Interval: None
    • Group by: Customer's coupon usage
    • Chart Type: Pie
  • Coupon usage details
    • Metric A: Number of orders
      • Metric: Number of orders with coupon
      • Filter:
        • Number of orders with this coupon > 10
    • Metric B: Net revenue from orders
      • Metric: Revenue
      • Filter:
        • Number of orders with this coupon > 10
    • Metric C: Total discounts applied
      • Metric: Coupon discount amount
      • Filter:
        • Number of orders with this coupon > 10
    • Formula: Gross revenue
      • Formula: B-C (if C is negative); B+C (if C is positive)
      • Format: Currency
    • Formula: % discounted
      • Formula: C/(B-C) (if C is negative); C/(B+C) (if C is positive)
      • Format: Percentage
    • Metric F: Average net order value
      • Metric: Average order value
      • Filter:
        • Number of orders with this coupon > 10
    • Formula: Average order discount
      • Formula: C/A
      • Format: Currency
    • Metric H: Distinct buyers
      • Metric: Distinct buyers
      • Filter:
        • Number of orders with this coupon > 10
    • Time period: All time
    • Interval: None
    • Group by: coupon code
    • Chart Type: Table
    • Note: The quantity of 10 for "Number of orders with this coupon" is arbitrary. Feel free to use the most appropriate quantity for this filter.

After compiling all the reports, you can organize them on the dashboard as you desire. The end 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 our professional services team to build the analysis for you, simply open a support ticket here.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk