Get WooCommerce Product total sales amount

Question

I have created a shortcode that will returns all products in a loop. I am looking for a way to get the total sales for each product. Right now i'm still having hard time finding the solution.

This is my code:

    function loop_ffh_function() 
    {
        ?>

        <div class="row">
    <?php
    $args = array(
    'numberposts' => -1,
    'post_type' => 'product',
    'orderby' => 'date');
    $loop = new WP_Query($args);
    while ($loop->have_posts()) : $loop->the_post();
        global $product; ?>         
        
        <?php
        $productID = get_the_ID(); 
        
        
        ?>
        <div class="column">
            <a href="<?php echo get_permalink($loop->post->ID) ?>">
                <?php the_post_thumbnail('full'); ?>
            </a>
            <?php 
            $target = get_field( 'campaign_target_amount' );
            
            ?>
            
            <div class="progress" style="margin-top:20px;">
              <div class="progress-bar progress-bar-warning progress-bar-striped active" role="progressbar"
              aria-valuenow="40" aria-valuemin="0" aria-valuemax="100" style="width:40%">
                40%
              </div>
            </div>
            
            <div class="row">
                <div class="col-md-8" style="padding-top:7px;">
                    Target : <b style="font-size: 18px;">RM <?php echo $target;?></b>
                </div>
                <div class="col-md-4">
                    <a class="btn" href="<?php echo get_permalink($loop->post->ID) ?>">Donate Now</a>
                </div>
            </div>
        </div>  
    <?php endwhile; ?>
    <?php wp_reset_query(); ?>
      </div>

       <?php
    }
    add_shortcode('loop_ffh', 'loop_ffh_function');

How to get a sum amount of sales for each product and display it in the loop?

Answer 1

You can use the following custom functions that will get for each product, the net revenue or/and the gross revenue:

function get_product_net_revenue( $product_id ) {
    global $wpdb;

    return (float) $wpdb->get_var( $wpdb->prepare("
        SELECT SUM(product_net_revenue)
        FROM {$wpdb->prefix}wc_order_product_lookup
        WHERE product_id = %d
    ",$product_id ) );
}

function get_product_gross_revenue( $product_id ) {
    global $wpdb;

    return (float) $wpdb->get_var( $wpdb->prepare("
        SELECT SUM(product_gross_revenue)
        FROM {$wpdb->prefix}wc_order_product_lookup
        WHERE product_id = %d
    ",$product_id ) );
}

Code goes in functions.php file of the active child theme (or active theme).
Tested and works in WooCommerce 4 and above.

Example usage inside your loop:

<?php $amount = get_product_net_revenue( $loop->post->ID ); ?>

Then you can use this float amount variable where you need.

---

Addition:

To get the net revenue only for "completed" orders use:

function get_product_net_revenue( $product_id ) {
    global $wpdb;

    return (float) $wpdb->get_var( $wpdb->prepare("
        SELECT SUM(o.product_net_revenue) 
        FROM {$wpdb->prefix}wc_order_product_lookup o 
        INNER JOIN {$wpdb->prefix}posts p
            ON o.order_id = p.ID
        WHERE p.status = 'wc-completed'
            AND o.product_id = %d
    ",$product_id ) );
}

To target "processing" and "completed" orders replace:

WHERE p.status = 'wc-completed'

with

WHERE p.status IN ('wc-processing','wc-completed')