3

I have a site with 1000 coupons. All the coupons have a usage limit of one. Im using the code provided by Raunuk Gupta to export coupons directly from the SQL database.

How WooCommerce Coupons are stored in Database ?

Is it possible to retrieve order meta of the user that used the coupon? I would like to include in the report the user's name, email address and possibly some other custom fields.

Thank you.

  • We generate 1000 coupons and provide it to a company ( client ).
  • Each coupon has a usage limit of 1.
  • The company then distribute the coupons among their members.
  • At the end of the month, we need to send a report to the company telling them:
    1. How many of the 1000 coupons were used. ( this can be done with the code provided at the link above )
    2. Then from the used coupons, we need to tell them who used it, in other words the name and email address of the user that placed the order.

LINK TO SAMPLE EXCEL EXPORT -The Yellow fields are from the COUPONS -The Blue fields are from the ORDER

Schalk Joubert
  • 398
  • 3
  • 19
  • FIELDS REQUIRED: ID | coupon_code | coupon_description | discount_type | coupon_amount | product_ids | product_categories | usage_limit | total_usaged | order_id | billing_email | billing_first_name | billing_last_name | order_total – Schalk Joubert Oct 22 '18 at 08:14

1 Answers1

8

Below MySQL query will list all your coupon associated with Order.

SELECT pc.post_title AS coupon_name,
       pc.post_excerpt AS coupon_description,
       Max(CASE WHEN pmc.meta_key = 'discount_type'      AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS discount_type,
       Max(CASE WHEN pmc.meta_key = 'coupon_amount'      AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS coupon_amount,
       Max(CASE WHEN pmc.meta_key = 'product_ids'        AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS product_ids,
       Max(CASE WHEN pmc.meta_key = 'product_categories' AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS product_categories,
       Max(CASE WHEN pmc.meta_key = 'customer_email'     AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS customer_email,
       Max(CASE WHEN pmc.meta_key = 'usage_limit'        AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS usage_limit,
       Max(CASE WHEN pmc.meta_key = 'usage_count'        AND  pc.`ID` = pmc.`post_id` THEN pmc.`meta_value` END) AS total_usaged,
       po.ID AS order_id,
       MAX(CASE WHEN pmo.meta_key = '_billing_email'      AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_email,
       MAX(CASE WHEN pmo.meta_key = '_billing_first_name' AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_first_name,
       MAX(CASE WHEN pmo.meta_key = '_billing_last_name'  AND po.ID = pmo.post_id THEN pmo.meta_value END) AS billing_last_name,
       MAX(CASE WHEN pmo.meta_key = '_order_total'        AND po.ID = pmo.post_id THEN pmo.meta_value END) AS order_total
FROM `wp_posts` AS pc
INNER JOIN `wp_postmeta` AS pmc ON  pc.`ID` = pmc.`post_id`
INNER JOIN `wp_woocommerce_order_items` AS woi ON pc.post_title = woi.order_item_name
    AND woi.order_item_type = 'coupon'
INNER JOIN `wp_posts` AS po ON woi.order_id = po.ID
    AND po.post_type = 'shop_order'
    AND po.post_status IN ('wc-completed', 'wc-processing', 'wc-refunded') -- Added needed order status over here.
INNER JOIN `wp_postmeta` AS pmo ON po.ID = pmo.post_id
WHERE pc.post_type = 'shop_coupon'
GROUP BY po.ID
ORDER BY pc.ID DESC,
         po.ID DESC
LIMIT 0, 10 -- modify it accordingly.

Hope this helps!

Raunak Gupta
  • 10,412
  • 3
  • 58
  • 97
  • Thank you! This works to export coupons with the associated order info. However, I was hoping to get an export as the initial Coupon export code you provided, but just with the added column of the order detail, should there be any. – Schalk Joubert Oct 19 '18 at 10:34
  • I want to end up with the list exactly as before, but should the last field of "total_usaged" be 1, then i would like to see the name and email of the person that used that coupon. If the field "total_usaged" is NULL or 0, then the additional columns of name email will be empty. Is that possible? Than you – Schalk Joubert Oct 19 '18 at 10:38
  • @user1664798: I didn't get you, can you please update your question with an example and also list the column that you want in the export. – Raunak Gupta Oct 19 '18 at 12:22
  • Thank you, I have updated the question, added a sample excel file and also added the columns headings as a separate comment. Thank you so much. – Schalk Joubert Oct 22 '18 at 08:16
  • @user1664798: I have updated my answer. Please check this would fetch the desired result. – Raunak Gupta Oct 23 '18 at 17:28
  • This works but I need to ad even more detail like the oder date and order status. Could you please point me in the right direction? i have managed to add the paid_date. – Schalk Joubert May 05 '19 at 10:21
  • And also the selected payment method. – Schalk Joubert May 05 '19 at 13:43
  • @user1664798: `posts.post_date` is the order date, `posts.post_status` is the order date. If the answer was helpful then do not forget to [upvote it](https://meta.stackexchange.com/a/173400). :) – Raunak Gupta May 06 '19 at 05:45