Total Woocommerce Sales Cost of a Specific Coupon

Question

Problem:

I need a way to get all sales with a specific coupon code first. Then get the total revenue from all these sales (and, preferably, subtract any profit from these sales. To get the actual amount of income).

Idea for fulfillment

I use the woocommerce 2.6.8 and MySql database for this. I assume that somehow I will have to first calculate the number of sales made using a particular coupon with PHP and MySql. Then, for each unique order ID with a specific coupon, create a new request for the total amount.

Any suggestions on how PHP looks and queries are greatly appreciated :)

I must indicate that I do not want to receive the total discount amount from the coupon . I need to calculate the total price that was sold with a particular coupon (not a discount).

OK, so there is still no working solution. But this is exactly what I consider to be the basic structure of this. Getting the total amount is not so simple because the coupon is not directly related to the order. I believe the request should have been something on the line of this:

{TBLPRFX} _woocommerce_order_items

Step 1. GET order_id FOR order_item_name = {COUPON_NAME}

Step 2. GET order_item_id FOR order_item_type = line_item WHERE order_id EQUAL {RESULT FROM STEP 1}

| order_item_id | order_item_name | order_item_type | order_id |

| 40971 | {COUPON_NAME} | coupon | 001

| 40970 | VAT | tax | 001

| 40969 | {PRODUCT_NAME} | line_item | 001

-

{TBLPRFX} _woocommerce_order_itemmeta strong>

Step 3. SUM meta_value FROM meta_key = _line_tax AND meta_key = _line_total WHERE order_item_id = {RESULT FROM STEP 2}

| order_item_id | meta_key | meta_value |

| 40969 | _line_tax | {VALUE_TAX}

| 40969 | _line_total | {VALUE_TOTAL}

| 40969 | _product_id | {PRODUCT_ID}

-

These are the queries I need to help figure out :) Not quite sure how to ask for it in MySql and PHP. The idea is to do this foreach, where "order_item_name = {COUPON_NAME_VARIABLE}", so I can summarize the total amount of all sales in which this coupon was used (i.e. Not the value of the coupon discount).

Answer 1

So, after much testing, I came up with a working solution. It is not the most elegant, but it does its job.

If any SQL ninjas see this, it would be great if you could offer any more ordered queries :)

After creating the first working solution, I realized that there are several more steps that need to be checked to get a more accurate amount. Processing refunds, taxes, discounts, etc., and also make sure that I receive data only from completed orders (since they are canceled, suspended, pending, etc. They are not sold until they are completed).

So this is what I ended up with. As I said, I know that he needs some work, and perhaps this will be done better. But for now, it works.

• REVISED CODE FOR FUNCTIONS. PHP -

  // COUPON CHECK function couponcheck() { global $wpdb; $gtl = 0; // Grand Total $total_sales = 0; $cpn = $_GET['cpn']; // Get coupon name from URL string $tblprfx = '[YOUR_TABLE_PREFIX]'; // Table prefix $wpps = 'posts'; // Look for post_status $wppm = 'postmeta'; $wcoi = 'woocommerce_order_items'; $conversion_value = 1; $base_currency = '[YOUR_BASE_CURRENCY]'; $currency; $orders_made; // Check to make sure there is a couon name in string if(isset($cpn) && !empty($cpn)){ // Query chain $init_result = $wpdb->get_results("SELECT order_id FROM {$tblprfx}{$wcoi} WHERE order_item_name='$cpn'"); $orders_made = count($init_result); foreach($init_result as $ir){ $r1 = $ir->order_id; $completed_result = $wpdb->get_results( "SELECT ID FROM {$tblprfx}{$wpps} WHERE post_status='wc-completed' AND ID=$r1" ); foreach($completed_result as $post_rows) { $pr = $post_rows->ID; $completed_sales += 1; $currency_result = $wpdb->get_results( "SELECT meta_value FROM {$tblprfx}{$wppm} WHERE post_id=$pr AND meta_key='_order_currency'" ); foreach($currency_result as $cr) { $currency = $cr->meta_value; if($currency === 'EUR'){ $currency = 'EUR'; $currency_rate = $wpdb->get_results( "SELECT meta_value FROM {$tblprfx}{$wppm} WHERE post_id=$pr AND meta_key='_woocs_order_rate'" ); foreach($currency_rate as $rv) { $rate_value = $rv->meta_value; $conversion_value = $rate_value; } } } $data_result = $wpdb->get_results( "SELECT meta_value FROM {$tblprfx}{$wppm} WHERE post_id=$pr AND meta_key='_order_total'" ); foreach($data_result as $dr) { $d = $dr->meta_value; if($currency === 'EUR'){ $eur += $d; $d = $d/$conversion_value; }else{ $[YOUR_BASE_CURRENCY] += $d; } $gtl += $d; } } } // Total number of sales $refunded_orders = $orders_made-$completed_sales; $order_avg = $gtl/$completed_sales; echo '<p>Total <strong>completed, non-refunded, sales made (after discounts)</strong> with coupon <strong>' . strtoupper($cpn) . '</strong>: <br />(Number of refunded orders: <strong>' . $refunded_orders . ')</strong></p><h2>' . $completed_sales . '</h2><p>At a total <strong>sales value</strong> of:</p><h2>' . number_format($[YOUR_BASE_CURRENCY],2) . ' [YOUR_BASE_CURRENCY]</h2><h2>&euro;' . number_format($eur,2) . '</h2><p>Adding up to a <strong>sum total</strong> of:</p><h2>' . number_format($gtl,2) . ' [YOUR_BASE_CURRENCY]</h2><p>Creating an average order value of:<br /><strong>' . number_format($order_avg,2) . ' [YOUR_BASE_CURRENCY]</strong>'; } } add_shortcode('coupons', 'couponcheck'); 

A quick note is that I changed my actual base currency to [YOUR_BASE_CURRENCY]. Comments (constructive) are welcome :)