Reason for this need: We have retail sales agents that use $0 coupon codes to track their sales (this just applies the coupon as a tag to the order with no actual discount) submitted threw our site in cases that the store is out of stock and they still get their commission for the sale when submitting the order through our magento store.
Here is the query I found and am using to pull orders that use a coupon:
SELECT coupon_rule_name AS 'Promotion Used'
, coupon_code AS 'Code Used'
, COUNT(coupon_code) AS 'Times Used / Number of 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
Pulls different totals and averages but I also need the Order Numbers associated with each order.
Appreciate any help!