Magento Business Intelligence Help Center

Essentials: Coupon Code Analysis

Understanding the coupon performance of your business is an interesting way to segment your orders and better understand customer habits.

We have documented the steps required to create this analysis to understand how coupon-acquired customers perform, see trends, and track individual coupon code usage.

coupon_analysis_dash_720.png

First, a note about how Magento tracks coupon codes. If a customer applied a coupon to an order, three things happen:

    • A discount is reflected in the `base_grand_total` amount (your Revenue metric in MBI)
    • The coupon code is stored in the `coupon_code` field. If this field is NULL (empty) the order does not have a coupon associated with it.
    • The discounted amount is stored in `base_discount_amount`. Depending on your configuration, this value may appear negative or positive.
The first step will be to construct a new metric with the following steps:
  • Navigate to Manage Data > Metrics > Create New Metric.
  • Select the `sales_order` (if your store is on Magento 2) or `sales_flat_order` (if your store is on Magento 1) table.
  • This metric performs a Sum on the base_discount_amount column, ordered by created_at.
    • FILTERS:
      • Add the Orders we count (Saved Filter Set)
      • Add the following:
        • `coupon_code` IS NOT `[NULL]`
      • Give the metric a name, such as "Coupon discount amount".
  • Once the metric has been created:
    • Navigate to Dashboards > Dashboard Options > Create New Dashboard.
    • Give the dashboard a name such as "Coupon Analysis".
  • This will be where we create and add all the reports.
  • New Reports:
        • If you haven’t already, check out this video about using the Visual Report Builder to build charts, tables, and scalar values.
        • Note on Time Period: The time period for each report is listed as “All-time”. Please feel free to alter this to suit your analysis needs. We recommend all reports on this dashboard cover the same time period, such as “All time”, “Year-to-date”, or “Last 365 days”.
  • Orders with coupons
    • Metric: Orders
      • Add filter:
        • [A] `coupon_code` IS NOT `[NULL]`
    • Time period: All time
    • Interval: None
    • Chart Type: Number (scalar)
  • Orders without coupons
    • Metric: Orders
      • Add filter:
        • [A] `coupon_code` IS `[NULL]`
    • Time period: All time
    • Interval: None
    • Chart Type: Number (scalar)
  • Net revenue from orders with coupons
    • Metric: Revenue
      • Add filter:
        • [A] `coupon_code` IS NOT `[NULL]`
    • Time period: All time
    • Interval: None
    • Chart Type: Number (scalar)
  • Discounts from coupons
    • Metric: Coupon discount amount
    • Time period: All time
    • Interval: None
    • Chart Type: Number (scalar)
  • Average lifetime revenue: Coupon acquired customers
    • Metric: Avg lifetime revenue
      • Add filter:
        • [A] `Customer’s first order’s coupon_code` IS NOT `[NULL]`
    • Time period: All time
    • Interval: None
    • Chart Type: Number (scalar)
  • Average lifetime revenue: Non-coupon acquired customers
    • Metric: Avg lifetime revenue
      • Add filter:
        • [A] ` Customer’s first order’s coupon_code` IS `[NULL]`
    • Time period: All time
    • Interval: None
    • Chart Type: Number (scalar)
  • Coupon usage details (first time orders)
    • Metric 1: Orders
      • Add filter:
        • [A] `coupon_code` IS NOT `[NULL]`
        • [B] `Customer’s order number` Equal to `1`
    • Metric 2: Revenue
      • Add filter:
        • [A] `coupon_code` IS NOT `[NULL]`
        • [B] `Customer’s order number` Equal to `1`
      • Rename: Net revenue
    • Metric 3: Coupon discount amount
      • Add filter:
        • [A] `coupon_code` IS NOT `[NULL]`
        • [B] `Customer’s order number` Equal to `1`
    • Create new formula: Gross revenue
      • Formula: (B – C)
      • Format: Currency
    • Create new formula: % discounted
      • Formula: (C / (B - C))
      • Format: Percentage
    • Create new formula: Average order discount
      • Formula: (C / A)
      • Format: Percentage
    • Time period: All time
    • Interval: None
    • Chart Type: Table
  • Average lifetime revenue by first order coupon
    • Metric: Avg lifetime revenue
      • Add filter:
        • [A] `coupon_code` IS `[NULL]`
    • Time period: All time
    • Interval: None
    • Chart Type: Number (scalar)
  • Coupon usage details (first time orders)
    • Metric: Avg lifetime revenue
      • Add filter:
        • [A] `Customer’s first order’s coupon_code` IS NOT `[NULL]`
    • Time period: All time
    • Interval: None
    • Group by: Customer’s first order’s coupon_code
    • Chart Type: Column
  • New customers by coupon / non-coupon acquisition
    • Metric 1: New customers
      • Add filter:
        • [A] `Customer’s first order’s coupon_code` IS NOT `[NULL]`
      • Rename: Coupon acquisition customer
    • Metric 2: New customers
      • Add filter:
        • [A] `coupon_code` IS `[NULL]`
      • Rename: Non-coupon acquisition customer
    • Time period: All time
    • Interval: By Month
    • Chart Type: Stacked Column

 

After building the reports, you can organize them on the dashboard as you desire, we recommend the layout of the image at the top of this article!

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