Jump to content

Statistics/Best Selling Products


Recommended Posts

This features shows best selling products since store creation.   That's 11 years for me & inventory has grown in recent years.  Most of my product is produced by myself.   To plan production & stocking better would it be possible to add date filters to this section so Best Selling Product between user defined dates can be used?

 

Thanks in advance.

Link to comment
Share on other sites

Nice idea!

In admin, Maintenance, Rebuild tab, there is a means to "Reset all product view counts". "Views" is one of the choices on what criteria to base a query for "Best Sellers" (aka Popular Products).

But resetting Views might be overkill for some.

I have in mind a text box next to Store Settings, Layout tab, "Source data for popular products" saying, "Within the past [###] days."

This won't be a date range, but will this suffice for your needs?

  • Like 1
Link to comment
Share on other sites

I found it to be easier to specify a specific date.

In /classes/gui.class.php:

Near lines 1299-1302, find:

            } else {
                $vars = 'none';
            }
            foreach ($GLOBALS['hooks']->load('class.gui.display_popular_products') as $hook) {

Change to:

            }
            foreach ($GLOBALS['hooks']->load('class.gui.display_popular_products') as $hook) {

Near lines 1269-1271, find:

            if ($products) {
                $vars = array();
                foreach ($products as $product) {

Change to:

            $vars = array();
            if ($products) {
                foreach ($products as $product) {

Near lines 1265-1266, find:

                default: // view-based
                    $where      = $GLOBALS['catalogue']->outOfStockWhere(array('status' => '1'));

Change to:
                default: // view-based
                    $where      = $GLOBALS['catalogue']->outOfStockWhere(array('status' => '1', 'date_added' => ">".$since));

Near line 1262, find:

                    $query  = "SELECT `oi`.`product_id`, `i`.`name`, `i`.`price`, `i`.`sale_price`, `i`.`tax_type`, `i`.`tax_inclusive`, SUM(`oi`.`quantity`) as `quantity` FROM `".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_order_inventory` as `oi` JOIN `".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory` as `i` WHERE `oi`.`product_id` = `i`.`product_id` AND `i`.`status` = 1 $whereStr GROUP BY `oi`.`product_id` ORDER BY `quantity` DESC LIMIT ".$limit.";";

Change to:

                    $query  = "SELECT `oi`.`product_id`, `i`.`name`, `i`.`price`, `i`.`sale_price`, `i`.`tax_type`, `i`.`tax_inclusive`, SUM(`oi`.`quantity`) as `quantity` FROM `".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_order_inventory` as `oi` JOIN `".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory` as `i` WHERE `oi`.`product_id` = `i`.`product_id` AND `i`.`status` = 1 AND `i`.`date_added` > '$since' $whereStr GROUP BY `oi`.`product_id` ORDER BY `quantity` DESC LIMIT ".$limit.";";

Near lines 1258-1259, find:

            $limit = (is_numeric($GLOBALS['config']->get('config', 'catalogue_popular_products_count'))) ? $GLOBALS['config']->get('config', 'catalogue_popular_products_count') : 10;
            switch ((int)$GLOBALS['config']->get('config', 'catalogue_popular_products_source')) {

Change to:

            $limit = (is_numeric($GLOBALS['config']->get('config', 'catalogue_popular_products_count'))) ? $GLOBALS['config']->get('config', 'catalogue_popular_products_count') : 10;
            $since = (!empty($GLOBALS['config']->get('config', 'catalogue_popular_products_since'))) ? $GLOBALS['config']->get('config', 'catalogue_popular_products_since') : "0000-00-00";
            switch ((int)$GLOBALS['config']->get('config', 'catalogue_popular_products_source')) {

In the admin template settings.index.php:

Near lines 249-251, find:

         <div><label for="catalogue_popular_products_source">{$LANG.settings.product_popular_source}</label><span><select name="config[catalogue_popular_products_source]" id="catalogue_popular_products_source" class="textbox">
            {foreach from=$OPT_CATALOGUE_POPULAR_PRODUCTS_SOURCE item=option}<option value="{$option.value}"{$option.selected}>{$option.title}</option>{/foreach}
            </select></span>

Change to:

         <div><label for="catalogue_popular_products_source">{$LANG.settings.product_popular_source}, since</label><span><select name="config[catalogue_popular_products_source]" id="catalogue_popular_products_source" class="textbox">
            {foreach from=$OPT_CATALOGUE_POPULAR_PRODUCTS_SOURCE item=option}<option value="{$option.value}"{$option.selected}>{$option.title}</option>{/foreach}
            </select><input name="config[catalogue_popular_products_since]" id="catalogue_popular_products_since" class="textbox number" value="{$CONFIG.catalogue_popular_products_since}" placeholder="YYYY-MM-DD"></span>

 

Link to comment
Share on other sites

You should see a simple text entry field just to the right of the drop-down selector for choosing where the data comes from to make the selection of Best Selling products in admin, Store Settings, Layout tab, Popular & Latest Products section, "Source data for popular products". This, then, affects what the customer sees in the Best Sellers box.

However, your not seeing the change in admin caused me to re-read this conversation. Now, it seems to me, that what you wanted was a way to set a focus on those product sales with specific criteria for the admin's benefit in admin, Statistics, Best Selling Products tab.

Is that the case?

 

Link to comment
Share on other sites

Hi  - yes, it was for my own benefit so I can make more stock of the most popular products over a defined time period..   That said the Popular Products on the home page is nice to have too!   It isn't working as expected.   If I set to start of this year it shows no popular products.  If I set it to start of 2020 it only shows 2 popular products.   They are not best sellers by a long way.  I have to set a date of June 1 2019 for 10 popular products to show.   The number of products declines the later I set the date.  

I have changed the popular products to show setting to 0 to disable it for now.

Link to comment
Share on other sites

"If I set to start of this year it shows no popular products."

Interesting. In admin, Store Settings, Advanced tab, enable Debug mode and enter your local IP address in the next field (www.showmyip.com).

Request the homepage.

Below the content there will be a grey debug area listing all of the SQL statements used. You may have to have the browser search the HTML for date_added. The query for Best Sellers based on sales will look something like:

SELECT `oi`.`product_id`, `i`.`name`, `i`.`price`, `i`.`sale_price`, `i`.`tax_type`, `i`.`tax_inclusive`,
SUM(`oi`.`quantity`) as `quantity` 
FROM `CubeCart_order_inventory` as `oi` 
JOIN `CubeCart_inventory` as `i` 
WHERE `oi`.`product_id` = `i`.`product_id` AND `i`.`status` = 1 AND `i`.`date_added` > '2020-01-01' 
GROUP BY `oi`.`product_id` ORDER BY `quantity` DESC LIMIT 4;

And based on views:

SELECT `name`, `product_id`, `quantity`, `price`, `sale_price`, `tax_type`, `tax_inclusive`, `popularity` 
FROM `CubeCart_inventory` 
WHERE CubeCart_inventory.status = '1' AND CubeCart_inventory.date_added > '2020-01-01' 
ORDER BY popularity DESC LIMIT 4;

In the post above, you wrote a date as June 1 2019. Hopefully, the date you specified in admin would have been 2019-06-01.

Link to comment
Share on other sites

  • 6 months later...

Hi Brian,

I have read through the above and wonder if on the admin side of the statistics page. https://www.harrisorganicwine.com.au/shop/admin_xxxxx.php?_g=statistics#stats_prod_sales There could be a date range as there is in the filter tab for the report filter. 

That way one could get a report for any period of all product sales.

Duncan

Ps I have tried to add a screen shot but failed.

Link to comment
Share on other sites

The files that have been modified to show Best Products Sold in a given time frame, and Best Customers in a given time frame are ready for testing.

I will try to attach the ZIP file to this post. But if the forum no longer allows it (I used to be able to do this), send me a PM with your email address and I will send them to you.

Well, the message said the upload failed. So, if interested, ....

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...