Segmentaion Report: Coupon based orders

gistfile1.txt

SELECT ''                                       AS 'Promotion Used'
     , ''                                       AS 'Promotion Code'
     , ''                                       AS 'Times Used (Orders #)'
     , SUM(subtotal)                            AS 'Cumulative Price'
     , ''                                       AS 'Cumulative Paid with Coupon'
     , ''                                       AS 'Average Order Total (W/  Coupon)'
     , AVG(subtotal)                            AS 'Average Order Total (W/O Coupon)'
     , ''                                       AS 'Cumulative Savings'
     , ''                                       AS 'Cumulative Loss'
     , CONCAT(ABS(ROUND(
        ((COUNT(coupon_code) / (SELECT COUNT(*) FROM sales_flat_order s) * 100) - 100
       ), 1)), '%')                             AS 'Percentage'

FROM     foodhubsales_flat_order
WHERE    coupon_rule_name > ''

UNION ALL

SELECT coupon_rule_name                         AS 'Promotion Used'
     , coupon_code                              AS 'Promotion Code'
     , COUNT(coupon_code)                       AS 'Times Used (Orders #)'
     , SUM(subtotal)                            AS 'Cumulative Price'

     , SUM(total_paid)                          AS 'Cumulative Paid with Coupon'
     , AVG(total_paid)                          AS 'Average Order Total (W/  Coupon)'
     , AVG(subtotal)                            AS 'Average Order Total (W/O Coupon)'

     , ABS(SUM(discount_amount))                AS 'Cumulative Savings'

     , (
        SUM(discount_amount) - SUM(total_paid)
       )                                        AS 'Cumulative Loss'

     , CONCAT(ROUND((
        COUNT(coupon_code) / (SELECT COUNT(*) FROM sales_flat_order s)
       ) * 100, 1), '%')                        AS 'Percentage'

FROM     sales_flat_order
WHERE    coupon_code        IS NOT NULL
GROUP BY coupon_code
ORDER BY COUNT(coupon_code) DESC;


# MariaDB [ping_magento]> SELECT * FROM paid_vs_coupon;
# +----------------------------+----------------+------------+-----------+-----------------+--------------+---------------+------------+-----------+---------+
# | promotion_used             | promotion_code | times_used | cum_price | cum_paid_coupon | avg_w_coupon | avg_wo_coupon | cum_saving | cum_loss  | percent |
# +----------------------------+----------------+------------+-----------+-----------------+--------------+---------------+------------+-----------+---------+
# |                            |                |         10 | 3532.9900 |                 |              |  353.29900000 |            |           | 71.4%   |
# | $201 CREDIT - AMAZON LOCAL | A5NSHHOT44WL   |          1 |  299.9900 | 0.0000          | 0            |  299.99000000 | 299.9900   | -299.9900 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | C7556TPKSZG2   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | DM0XPEEIT26H   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | Looting Sale               | FIVE-FINGER    |          3 |  833.0000 | 0.0000          | 0            |  277.66666667 | 833.0000   | -833.0000 | 8.6%    |
# | $201 CREDIT - AMAZON LOCAL | G1CPBI3AM988   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | JC27EYY43058   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | WG3HI1BSYTRU   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | Y7N64X58QRRF   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# +----------------------------+----------------+------------+-----------+-----------------+--------------+---------------+------------+-----------+---------+
# 9 rows in set (0.00 sec)