0

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!

1 Answer 1

1

Found a diff SQL query and modified to my needs:

SELECT  
        CONCAT(address.firstname,' ',
        address.lastname) AS Name,
        address.email AS Email,
        items.created_at AS Date,
        items.name AS Description,
        items.store_id AS Logon,
        items.name AS Category,
        items.store_id AS FeedbackDate,              
        items.sku AS ProductSearchcode,
        items.order_id AS Orderref,  
        orders.coupon_code AS CouponCodeUsed,
        orders.quote_id AS OrderId

FROM sales_flat_order AS orders
  JOIN sales_flat_order_item AS items 
  ON items.order_id = orders.entity_id 
  LEFT JOIN sales_flat_order_address AS address
  ON orders.entity_id = address.parent_id

WHERE    
items .created_at BETWEEN  '2016-01-01 00:00:00' AND '2016-10-19 00:00:00'
AND orders.status = 'complete'

Reason for this need: We have retail sales agents that use $0 coupon codes to track their sales 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.

Solution: The above query pulls order ids from both the sales_flat_order and sales_flat_order_item tables but these are not the order numbers that are shown in magento admin backend however they are unique to each order shown in the export. What i was able to use from the data pulled to track down orders was the Name and Date columns to find the order in admin panel, then use the rest of the data to confirm the order which has shown to be very accurate so far.

Hope this helps someone else :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.