image
SALES ANALYTICS VirtueMart Reports: Sales, Products, Orders, Customers, Locations, Manufacturers, Inventory, Taxes, Shipping Costs, ... everything under control on your online shop!! Read the Full Story
image
SALES ANALYTICS for MAGENTO Magento Reports: On-site reports for Sales, Products, Categories, Orders, Customers, Locations, Taxes, Shipping,... deep insight into your Magento online shop!! Read the Full Story
image
SOCIAL ANALYTICS JomSocial Reports: Now you can know about your user social activities in detail. Friends, groups, photos, videos, ... reports on all your user participations. Read the Full Story
image
WEB ANALYTICS Joomla! Reports: You can check how your website is evolving using all the relevant information at hand, and make it grow accordingly!! Read the Full Story
* * * *
image image image image
Welcome, Guest
Username Password: Remember me

Incorrect Multiplication of gross values
(1 viewing) (1) Guest
SALES ANALYTICS Installation & Configuration.

TOPIC: Incorrect Multiplication of gross values

Incorrect Multiplication of gross values 6 years, 10 months ago #1

  • quayfee
  • OFFLINE
  • Fresh Boarder
  • Posts: 6
  • Karma: 0
Hi,

Having installed last weekend (thanks again for the great support), I handed the isntallation over to my client and they have found some inconsistencies in the calculations.

It appears that things are being mulitplied incorrectly.

One example is in the 'Customers > Top Gross income' Report. The gross value is being muliplied by the number of items ordered. So one order is for £109.20 (5 products), but the gross income for that customer (who only placed that order) is shown as £546, not £109.20. This is for all orders an appears to be across all customer based reports.

Please let me know if you would like further information.

I should also point out the my client also said that he was impressed with the potential of the suite and was looking forward to using it for a long time.

Many Thanks

Keith

Re: Incorrect Multiplication of gross values 6 years, 10 months ago #2

I also encountered the same issue and look forward to resolving it. Thanks!

Re: Incorrect Multiplication of gross values 6 years, 9 months ago #3

  • latitudweb
  • OFFLINE
  • Administrator
  • Posts: 182
  • Karma: 1
Dear colleagues,

We've been having many difficuties when trying to replicate this behaviour in our systems (both test and production systems). I assume you're using the latest version (1.3.2).

The only question that comes to mind is whether these customers might have placed the order more than once and that the VirtueMart integration settings treats all these incomplete transactions as also successful transactions.

This is what we can do to work it out for once and for all. This is the SQL query that comes out of the Sales Analytics tool to build the 'Customers > Top Gross income' Report:
SELECT 
CONCAT_WS(' ', u.first_name, u.last_name) as user_name,
SUM(o.order_total) AS gross_income,
SUM(o.order_total - (o.order_tax+ o.order_shipping + o.order_shipping_tax)) AS net_income,
SUM(o.order_tax) AS taxes,
SUM(o.order_shipping) AS shipping,
SUM(o.order_shipping_tax) AS shipping_taxes,
COUNT(DISTINCT(o.order_id)) AS n_orders,
SUM(i.product_quantity) AS n_items,
SUM(o.coupon_discount) AS coupon_discount,
SUM(o.order_discount) AS order_discount
FROM `jos_vm_orders` AS o
LEFT JOIN `jos_sa_order_segmentation` AS t ON o.order_id = t.order_id
LEFT JOIN `jos_vm_order_item` AS i ON i.order_id = t.order_id
LEFT JOIN `jos_vm_user_info` AS u ON u.user_id = t.user_id
WHERE t.cdate>='2011-02-08' AND t.cdate<='2011-03-09 23:59:59' AND t.order_status IN ("C", "S")
GROUP BY u.user_id
ORDER BY gross_income DESC

Can you please try to run this SELECT SQL query on your database and check whether the DB response includes right data or wrong data? Maybe you need to change the table preffix, or the date or order_status values in this SQL query.

If the database response data is correct, is it the same that the Sales Analytics tool display for the 'Customers > Top Gross income' Report?

Kind Regards.

Re: Incorrect Multiplication of gross values 6 years, 9 months ago #4

  • latitudweb
  • OFFLINE
  • Administrator
  • Posts: 182
  • Karma: 1
Also, please bear in mind that when Sales Analytics is installed for the first time, the VirtueMart successful status that is set in the integration parameters is "Confirmed". This transaction status is allright for downloadable products (items that won't be delivered).

But if you run a deliverable goods store, the VirtueMart transaction status moves on from "Confirmed" to "Shipped". If this is your case, you should change this parameter in the Sales Analytics configuration.

Kind regards.

Re: Incorrect Multiplication of gross values 6 years, 5 months ago #5

  • cswikoff
  • OFFLINE
  • Fresh Boarder
  • Posts: 14
  • Karma: 1
Same problem. I have one customer that purchased 6 items for $49.82. In SA it shows that she has purchased 18 items. It then multiplies 18 items by $49.82 and shows that her purchase was $896.76. I do have a different option to the problem in SA. Actually, I would rather just have the sale of $896.76. So, if you can give me a way to do that I'll be delighted. analytictools.latitudweb.com/components/.../emoticons/smile.png

Re: Incorrect Multiplication of gross values 6 years, 5 months ago #6

  • hlbog
  • OFFLINE
  • Fresh Boarder
  • Posts: 6
  • Karma: 0
I have the same problem here.
Joomla 1.5.23 and VirtueMart 1.1.9. Sales Analytics 1.4.0.

I've tried your SQL query. But it throws an error:
<code>
SELECT
CONCAT_WS(' ', u.first_name, u.last_name) as user_name,
SUM(o.order_total) AS gross_income,
SUM(o.order_total - (o.order_tax+ o.order_shipping + o.order_shipping_tax)) AS net_income,
SUM(o.order_tax) AS taxes,
SUM(o.order_shipping) AS shipping,
SUM(o.order_shipping_tax) AS shipping_taxes,
COUNT(DISTINCT(o.order_id)) AS n_orders,
SUM(i.product_quantity) AS n_items,
SUM(o.coupon_discount) AS coupon_discount,
SUM(o.order_discount) AS order_discount
FROM `wbx_vm_orders` AS o
LEFT JOIN `wbx_sa_order_segmentation` AS t ON o.order_id = t.order_id
LEFT JOIN `wbx_vm_order_item` AS i ON i.order_id = t.order_id
LEFT JOIN `wbx_vm_user_info` AS u ON u.user_id = t.user_id
WHERE t.cdate>='2011-05-01' AND t.cdate<='2011-07-04 23:59:59' AND t.order_status IN ("C", "S")
GROUP BY u.user_id
ORDER BY gross_income DESC
</code>

Result:

#1054 - Unknown column 't.order_status' in 'where clause'
It seems field order_status does not exist on sa_order_segmentation table.

Re: Incorrect Multiplication of gross values 6 years, 5 months ago #7

  • cswikoff
  • OFFLINE
  • Fresh Boarder
  • Posts: 14
  • Karma: 1
Thanks hlblog. I hope this gets us closer to a solution. I really love this extension, but right now it is not usable. My client doesn't trust the data, which is understandable. If there are others out there that can give some details to help find the problem, or if someone found a solution, I'm sure everyone would appreciate it.

Re: Incorrect Multiplication of gross values 6 years, 5 months ago #8

  • hlbog
  • OFFLINE
  • Fresh Boarder
  • Posts: 6
  • Karma: 0
I guess I have figured out the problem. It is on the database queries.
I was debugging the Customer's reports only, but the other cases are similar.
This is original query that gather the data for the Customer's report (Top Gross) list.
 
SELECT CONCAT_WS(' ', u.first_name, u.last_name) AS user_name, SUM(o.order_total) AS gross_income, SUM(o.order_total - (o.order_tax+ o.order_shipping +o.order_shipping_tax)) AS net_income, SUM(o.order_tax) AS taxes, SUM(o.order_shipping) AS shipping, SUM(o.order_shipping_tax) AS shipping_taxes,COUNT(DISTINCT(o.order_id)) AS n_orders, COUNT(i.order_item_id) AS n_items, SUM(o.coupon_discount) AS coupon_discount, SUM(o.order_discount) AS order_discount
  FROM `wbx_vm_orders` AS o
  LEFT JOIN `wbx_sa_order_segmentation` AS t
  ON o.order_id = t.order_id
  LEFT JOIN `wbx_vm_order_item` AS i
  ON i.order_id = t.order_id
  LEFT JOIN `wbx_vm_user_info` AS u
  ON u.user_id = o.user_id
  WHERE t.cdate>='2011-05-01'
  AND t.cdate<='2011-07-04 23:59:59'
  AND o.order_status IN ("C", "S", "E")
  GROUP BY o.user_id
  ORDER BY gross_income DESC
 


The main problem is with the LEFT JOIN with the vm_order_item table. This creates additional rows that counts on the SUM.
After fixing this, I could see that customers that informed another name for the shipping address was also a problem.
Well, the final and working query is:
 
SELECT u.name AS user_name, SUM(o.order_total) AS gross_income, SUM(o.order_total - (o.order_tax+ o.order_shipping +o.order_shipping_tax)) AS net_income, SUM(o.order_tax) AS taxes, SUM(o.order_shipping) AS shipping, SUM(o.order_shipping_tax) AS shipping_taxes,COUNT(DISTINCT(o.order_id)) AS n_orders, (SELECT COUNT(order_item_id) FROM wbx_vm_order_item AS i WHERE i.order_id IN(SELECT order_id FROM wbx_vm_orders AS j WHERE j.user_id = o.user_id)) AS n_items, SUM(o.coupon_discount) AS coupon_discount, SUM(o.order_discount) AS order_discount
FROM `wbx_vm_orders` AS o
LEFT JOIN `wbx_sa_order_segmentation` AS t
ON o.order_id = t.order_id
LEFT JOIN `wbx_users` AS u
ON u.id = o.user_id
WHERE t.cdate>='2011-05-01'
AND t.cdate<='2011-07-04 23:59:59'
AND o.order_status IN ("C", "S", "E")
GROUP BY o.user_id
ORDER BY gross_income DESC
 


Remember this code is using a different table prefix.
So, the point now is where I change the component code to fix the queries. I'm going to dig it now, but a few help from the developers would be great
Dev Team, please...

Re: Incorrect Multiplication of gross values 6 years, 5 months ago #9

  • cswikoff
  • OFFLINE
  • Fresh Boarder
  • Posts: 14
  • Karma: 1
Wow,

Thanks for looking into that and posting your findings. I'm sure the developers appreciate it. I know I do. I am hoping it gets us really close to a final solution.

Thanks again.

Craig

Re: Incorrect Multiplication of gross values 6 years, 5 months ago #10

  • latitudweb
  • OFFLINE
  • Administrator
  • Posts: 182
  • Karma: 1
Hi Helder,

Thanks a lot for your help, we really appreciate it.

We'll check your query and we'll release an updated Sales Analytics version fixing this bug.

Again, thanks a lot.
Time to create page: 0.43 seconds

About Us

Latitud WEB is a trademark from Business Excellence through Information Technologies S.L. (BEIT). BEIT is an Information Technology company based in Valencia (Spain). Our aim is to put the IT advantages at the service of companies and organizations of any size.