[FIX?] Customer Stats

[Martin]

Not 100% sure of how this comes about but the problem is found in:
Statisics > Customer Stats > Revenue Per Customer (tab)

You sometimes get a slew of orders for a single customer who you just know hasn't ordered that much.

EDIT #2: I'm leaving this post as is, to help describe the logic processes I had to go through to find a fix... I wouldn't recommend applying any of the code fixes suggested in this post, but go with the SQL changes described in post #2 instead.


EDIT: Turns out it's not as simple as I thought because this approach doesn't deal with the issue of guest checkout records which do not have a customerid reference... What a way to run a railroad.. So this is actually quite a large bug as far as statistics go and looking at my stats it seems there's been a considerable number of transactions that didn't go through properly.

I suspect the fix for this is going to be a bit of code that runs at the beginning and that updates the ordbillemail with the customers email information (if available)... or something... Either way, this indicates something that needs fixing.



Original rational - use at your own discretion but bear in mind it's borked at present.

I'm pretty sure this is related to the problems with transactions that don't update properly due to a payment gateway timeout, etc.. but either way the errant bit of code is in this block of code (File: /admin/includes/classes/class.statistics.customers.php )

Code: Select all

			// Fetch the actual results for this page
			$query = sprintf("
				SELECT
					customerid,
					CONCAT(custconfirstname, ' ', custconlastname) AS name,
					custconemail,
					CONCAT(ordbillfirstname, ' ',  ordbilllastname) AS billname,
					ordbillemail,
					custdatejoined,
					COUNT(orderid) AS numorders,
					SUM(ordtotalamount) AS revenue
				FROM
					[|PREFIX|]orders
					LEFT JOIN [|PREFIX|]customers ON ordcustid = customerid
				WHERE
					ordstatus IN (".implode(',', GetPaidOrderStatusArray()).") AND
					orddate >= '%d' AND
					orddate <= '%d'
				GROUP BY
					ordbillemail

The critical bit is the "GROUP BY ordbillemail" which groups together a bunch of orders where the ordbillemail hasn't been set... Why hasn't it been set? probably because the order didn't complete properly and updating the status manually doesn't fix this? who knows, either way the fact that it's empty means that the Query result groups them all these orders with an empty ordbillemail field together and shoves them under the first customer if finds...

Fix is simple enough...

Just locate that block of code and change the

Code: Select all

				GROUP BY
					ordbillemail

to...

Code: Select all

				GROUP BY
					ordcustid

Job done...

[Martin]

Here's the fix I came up with in the end to help alleviate the confusion with Statistics...

Instead of modifying any of the stats code... you simply need to run this bit of SQL in phpmyadmin or whatever tool you use to admin your raw database code and it will copy the customer email address to fill any empty billing email address field. This then provides a unique ID that's associated with the customer and allows the stats code to group things properly.

Yes, it's a hack but it works so I'm not knocking it...

SQL:

Code: Select all

UPDATE isc_orders AS o INNER JOIN isc_customers AS c ON o.ordcustid = c.customerid SET o.ordbillemail = c.custconemail WHERE o.ordbillemail = ''

If you'd like to see just now many records would be affected and the customers concerned you can use this SQL to get a results table.

Code: Select all

SELECT customerid, CONCAT(c.custconfirstname, ' ', c.custconlastname) AS name, c.custconemail,
CONCAT(o.ordbillfirstname, ' ',  o.ordbilllastname) AS billname, c.custdatejoined, o.ordbillemail, o.orderid FROM isc_orders AS o INNER JOIN isc_customers AS c ON o.ordcustid = c.customerid WHERE o.ordbillemail = ''

Whatever you do, I'd strongly recommend taking a backup of your orders table before you do anything...