2

I want to run an SQL query to show orders by new customers and existing customers and group them by month.

EDIT:

I now have this script however it only compares the dates if they are exact to the minute, I now need to compare the dates by just the date rather than date and time.

SELECT * 
FROM (

SELECT e.grand_total, e.created_at, COUNT( e.increment_id ) AS grand_total1, o.entity_id, o.created_at AS  `last_order_date` 
FROM  `mg_sales_flat_order` AS  `e` 
LEFT JOIN  `mg_customer_entity` AS  `o` ON e.customer_id = o.entity_id
WHERE e.created_at = o.created_at
GROUP BY YEAR( e.created_at ) , MONTH( e.created_at ) 
ORDER BY e.created_at DESC
)sub_query

Edit: to clarify I want a table to display: Month - #New Customer Orders - #Existing Customer Orders - Total Customer Orders

A new customer would be a customer who came onto the site placed there first order in month x if they then came back and ordered in month a,b and c then those orders would be classed as existing customer orders

I have come across this script on this site but I'm not sure how that would help.

$LastOrder=Mage::getModel('sales/order')->load($orderId);
$LastOrdertime= $LastOrder->getCreatedAt();

$OrderCollection=Mage::getModel('sales/order')->getCollection()->addFieldToFilter('customer_email',$LastOrder->getData('customer_email'))
        ->setOrder('created_at','asc');
$fstoredetime= $OrderCollection->getFirstItem()->getCreatedAt();    

if($fstoredetime==$LastOrdertime):
// new customer
else:
//old customer
endif;  

I can get number of transactions by month with this code but I want to split it out into new and existing customers

<?php
            $result = mysqli_query($con,"SELECT count(*), created_at FROM sales_flat_order WHERE status NOT LIKE 'canceled' AND store_id BETWEEN 1 AND 2 GROUP BY YEAR(created_at), MONTH(created_at) ORDER BY created_at DESC");

            while($row = mysqli_fetch_array($result)) {

            $num = $row[0];

              echo "<tr>";

              echo "<td>";

              echo date("m/Y", strtotime($row['created_at']));

              echo "</td>";

              echo "<td>";

              echo $num;

              echo "</td>";

              echo "</tr>";
            }

            mysqli_close($con);
        ?>

I have also tried comparing created_at in both customer and order tables, has anyone done this before?

SOLUTION:

SELECT * 
FROM (
SELECT e.grand_total, e.created_at, COUNT( e.increment_id ) AS grand_total1, o.entity_id, o.created_at AS  `last_order_date` 
FROM  `mg_sales_flat_order` AS  `e` 
LEFT JOIN  `mg_customer_entity` AS  `o` ON e.customer_id = o.entity_id
WHERE DATE(e.created_at) = DATE(o.created_at)
GROUP BY YEAR( e.created_at ) , MONTH( e.created_at ) 
ORDER BY created_at DESC
)sub_query

I also found more information here http://www.webamondo.co.uk/blog/magento-sql-repeat-customers-vs-new-customers/

  • How exactly do you want to define a "new" customer, as opposed to an "existing" one? – Mike May 1 '15 at 13:47
  • @Mike a new customer would just be a customer that hadn't ordered before. – Will Wright May 1 '15 at 13:56
  • Before when? Do you mean that a new customer is any customer at all that has exactly one order, and any customer with more than one order is "existing"? – Mike May 1 '15 at 13:58
  • @Mike If an order is placed in April and the customer had previously made no other order then they would be a new customer, if the customer placed in april but had previously ordered before then they would be an existing customer. So I would end up with a table that had the headers Month, Total transactions, New Cust Trans, Existing Cust Trans. – Will Wright May 1 '15 at 14:02
  • 1
    Let us continue this discussion in chat. – Will Wright May 1 '15 at 14:21
2

The relevant tables and fields are

  • sales_flat_order - customer_id, updated_at
  • customer_entity - created_at

Just do a join of these two tables and then group as needed.

  • thanks david I have edited the script above which works sort of I just need WHERE e.created_at = o.created_at to just compare the date rather than date and time – Will Wright May 2 '15 at 8:36

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.