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

Re: Incorrect Multiplication of gross values 7 years, 1 month ago #11

  • hlbog
  • OFFLINE
  • Fresh Boarder
  • Posts: 6
  • Karma: 0
Great to hear that!
If you need data to replicate the problem, I can send it to you. The main problems are:
- When an order has more than one kind of items. This creates several entries on order_item table, so the problem with the LEFT JOIN appears;
- When user post two (or more) orders, and one of them has a different shipping address with a different name and surname on this address from the billing address. This results in a row with the shipping's name as the customer. For example, one of my customers put the name and address of a neighbor because he had problems before with delivering items for his home. The query results with an entry for his neighbor as the customer. I saw this after fixing the LEFT JOIN problem, so I'm not sure if it will appear at first tests.

At last, I would ask you to take the opportunity to include a check to see if the site is using HTTPS and replace dynamically the URL's for the Google API.

Thanks and keep the good job, this extension is really very promising.
--
Helder Garcia

Re: Incorrect Multiplication of gross values 7 years, 1 month ago #12

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

After we performed our tests, we'll go with this query (beware of the table prefixes):

 
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 `jos_vm_order_item` AS o LEFT JOIN `jos_vm_orders` AS j ON o.order_id = j.order_id LEFT JOIN `jos_sa_order_segmentation` AS t ON o.order_id = t.order_id WHERE j.user_id = o.user_id AND t.cdate>='2011-05-01' AND t.cdate<='2011-07-04 23:59:59' AND o.order_status IN ("C", "S", "E")) 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_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
 


We've added some more clauses to the n_items inner query, and it also gets user information from the Joomla! users table (with registration data) instead of the VirtueMart users info table (with shipping data).

About HTTPS requests, we did not include them because the Google API did not accept them. But since our lastest release, it seems Google has improved the API, and it is now accepting HTTPS requests. If this is so, we'll include the feature.

As soon as everything is ready, we'll release an updated version.

Kind Regards.

Re: Incorrect Multiplication of gross values 7 years, 1 month ago #13

  • hlbog
  • OFFLINE
  • Fresh Boarder
  • Posts: 6
  • Karma: 0
Yes, it works perfectly!

Thanks for the support.
--
Helder Garcia

Re: Incorrect Multiplication of gross values 7 years, 1 month ago #14

  • latitudweb
  • OFFLINE
  • Administrator
  • Posts: 182
  • Karma: 1
Correction

After executing performance tests, finally this is the query to be used:

 
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,
SUM(i.n_items) 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 (SELECT o.order_id, SUM(o.product_quantity) AS n_items FROM `jos_vm_order_item` AS o LEFT JOIN `jos_sa_order_segmentation` AS t ON o.order_id = t.order_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.order_id) AS i ON i.order_id = o.order_id
LEFT JOIN `jos_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
 


Instead of using an inner select query in the query fields, a temporary (in memory) table is used in the LEFT JOIN's.

Regards.

Re: Incorrect Multiplication of gross values 7 years, 1 month ago #15

  • cswikoff
  • OFFLINE
  • Fresh Boarder
  • Posts: 14
  • Karma: 1
Thanks everybody. It looks like you are getting there. So, can I add this query somewhere to fix the problem now, or would it just be better to wait for an update? If I can add this how and where do I add it?

Re: Incorrect Multiplication of gross values 7 years, 1 month ago #16

  • latitudweb
  • OFFLINE
  • Administrator
  • Posts: 182
  • Karma: 1
We're testing some other query updates to improve performance, and we believe the new release will be ready very soon. But in the meantime, if you want to modify this query in your Sales Analytics installation, you can run this query in the database (for example, via phpmyadmin):

 
UPDATE `jos_sa_report_queries` SET report_query='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, SUM(i.n_items) 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 (SELECT o.order_id, SUM(o.product_quantity) AS n_items FROM `jos_vm_order_item` AS o LEFT JOIN `jos_sa_order_segmentation` AS t ON o.order_id = t.order_id WHERE #TFWC# AND #OSWC# GROUP BY o.order_id) AS i ON i.order_id = o.order_id LEFT JOIN `jos_users` AS u ON u.id = o.user_id WHERE #TFWC# AND #OSWC# GROUP BY o.user_id #ROC#', header_query='SELECT 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.n_items) 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 (SELECT o.order_id, SUM(o.product_quantity) AS n_items FROM `jos_vm_order_item` AS o LEFT JOIN `jos_sa_order_segmentation` AS t ON o.order_id = t.order_id WHERE #TFWC# AND #OSWC# GROUP BY o.order_id) AS i ON i.order_id = o.order_id LEFT JOIN `jos_users` AS u ON u.id = o.user_id WHERE #TFWC# AND #OSWC#' WHERE queries_id=8
 


Beware of the table prefixes!!!!

This query will update Sales Analytics query #8, the one used for top customer reports.

Regards.

Re: Incorrect Multiplication of gross values 7 years, 1 month ago #17

  • latitudweb
  • OFFLINE
  • Administrator
  • Posts: 182
  • Karma: 1
We've just released Sales Analytics v1.5 to solve the accuracy issue when joining data from the order items table.

All our report queries have been reviewed, also to improve accuracy.

You can download the latest version here: analytictools.latitudweb.com/downloads/d...sales-analytics.html

For further information about reports data and accuracy, please read analytictools.latitudweb.com/products/sa...tics/how-to-use.html

Thanks to everybody for your collaboration. Any feedback about this tool is highly appreciated as it helps us improve how it works.

Kind regards.

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

  • rcl
  • OFFLINE
  • Fresh Boarder
  • Posts: 3
  • Karma: 0
I have found that the program calculates wrong data about items sold.I run report Products>#Items and it shows 10 and 2 sold items instead 1. (Pls. look attachment.) In both orders was sold about 1 psc of product ,but I see items q-ty 10 and 2. I need a true data ,how can I solve this problem?
Attachments:

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

  • rcl
  • OFFLINE
  • Fresh Boarder
  • Posts: 3
  • Karma: 0
somebody help please ....

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

  • rcl
  • OFFLINE
  • Fresh Boarder
  • Posts: 3
  • Karma: 0
as i see, support for Sales analytics - absolute zero.....
Time to create page: 0.36 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.