0

I need to get the sales revenue per product using sales order.

I have been using product_collection to multiply FinalPrice with order_qty But as the FinalPrice changes, this is no good to work out the actual sales revenue.

I can get the revenue for all products but I cannot seem to filter by Product ID.

$revenue = Mage::getModel( 'sales/order' )->getCollection()
            ->addAttributeToSelect('grand_total')
            ->getColumnValues('grand_total');

$overall_profit = array_sum( $revenue );
$overall_profit = Mage::helper( 'core' )->currency( $overall_profit, true, false );

I am using this inside a loop so I have access to Product_ID, SKU etc

1
+50

Based on your own answer, you can do it using Magento objects as Julien suggested:

$collection = Mage::getResourceModel('sales/order_item_collection')
    ->addFieldToFilter('product_id', $prodId);

$collection->getSelect()
    ->from('sales_flat_order_item', 
         array('sum' => 'sum(sales_flat_order_item.row_total_incl_tax)'
    );

$productRevenue= 0;

foreach($collection as $result) {
     $productRevenue += $result->getSum();
}

$productRevenue = $productRevenue / 2;
0

Here is a custom function I wrote to calculate the bestselling products (top 5) for a particular store... you should be able to modify it to get the data you need.

/**
 * Create a collection of products based directly on the sales items
 *
 * @return Varien_Data_Collection
 */
protected function _directSalesCollection()
{
    $this->setData('direct', true);

    /* @var $read Varien_Db_Adapter_Interface */
    $read       = Mage::getSingleton('core/resource')->getConnection('core_read');
    $select     = new Varien_Db_Select($read);
    $collection = new Varien_Data_Collection();
    $result     = array();

    $select->from(
        array('i' => Mage::getModel('sales/order_item')->getResource()->getMainTable()),
        array(
            'product_id'    => 'i.product_id',
            'product_name'  => 'i.name',
            'total_sales'   => 'SUM(i.base_price)',
            'product_price' => 'AVG(i.base_price)',
            'qty_ordered'   => 'SUM(i.qty_ordered)',
        )
    )
    ->join(
        array('o' => Mage::getModel('sales/order')->getResource()->getMainTable()),
        'i.order_id = o.entity_id',
        array()
    )
    ->where('o.state NOT IN(?)', array('closed', 'canceled'))
    ->where('o.store_id = ?', Mage::app()->getStore()->getId())
    ->where('o.created_at >= (NOW() - INTERVAL 30 DAY)')
    ->group('i.product_id')
    ->order('total_sales '. Varien_Data_Collection::SORT_ORDER_DESC)
    ->limit(5);

    $rows = $read->fetchAll($select);

    foreach ($rows as $row) {
        $collection->addItem(new Varien_Object($row));
    }

    return $collection;
}

For that function, I was less considered about the total sales amount. If you want the actual sold price you can use i.base_row_total instead of i.base_price. Of course this does NOT include values for tax or shipping... if you need tax to be included use i.base_row_total_incl_tax but shipping cannot and should not be included for the product level report.

0

OK I managed to do it with this SQL Query:

$query = "SELECT sum(row_total_incl_tax) FROM sales_flat_order_item where product_id = $prod_id";

$result = $mysqli->query($query);   
$fetch = mysqli_fetch_array($result);
$product_revenue = array_sum($fetch);
$product_revenue = $product_revenue/2;

I had to divide the result by two and I got the correct figure, checked manually.

  • Please use Magento objects for manipulate collections rather than pure SQL and basic PHP functions to achieve what you need. – Julien Lachal Dec 1 '16 at 10:12
  • If it was possible I would have :) – Mike Tim Turner Dec 1 '16 at 10:21
  • How is it not possible? – Julien Lachal Dec 1 '16 at 12:40
  • With my limited knowledge of the subject. You could have demonstrated it and got the extra rep points but looks like someone beat you too it :) – Mike Tim Turner Dec 1 '16 at 12:58
  • No worries man, I was simply trying to avoid you some trouble, I don't really have an answer to your question, your use of pure SQL and basic functions just struck me that's all :) – Julien Lachal Dec 1 '16 at 13:58
0

@mike

you can try below query with groupby

$query = "SELECT sum(row_total_incl_tax) FROM sales_flat_order_item where product_id = $prod_id groupby product_id" ;

This will return you a total of that product so you don't have to use array_sum function this will return you a single row.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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