Average time between past orders

[gbkra]

Hello,

I am looking for a way to get average time between past 3 orders placed using email. Seems easiest way would be to use SQL query. Anybody have idea how to achieve it? Thank you in advance.

 

GBJ

[knacky]

Hi
Why do you think SQL would be good and can you imagine how complex it is?

Could you please specify your assignment?

If I give you an example, are you able to adjust it yourself?

Quote

orders placed using email

 

[knacky]

E.g:

SELECT 
	DATEDIFF(
        MAX(date_add), 
        MIN(date_add)) / (COUNT(date_add) - 1
    ) as dateDiff
FROM ps_orders 
ORDER BY date_add DESC
LIMIT 3;

 

[gbkra]

9 hours ago, knacky said:

Hi
Why do you think SQL would be good and can you imagine how complex it is?

Could you please specify your assignment?

If I give you an example, are you able to adjust it yourself?

 

Hello,

 

thank you for reply. I have seen few years back in some CRM that it was done by sql command. Not so difficult if you are not interacting with many millions records.

 

Basically I would like to monitor if customer placed order in his average order time +5 days. It is very effective. So basically I do need

1. Get dates (time can be ignored) for all order placed by specific email (no matter if account or guest - key is the email).
2. For emails that has 2 or more orders calculate average count of days between orders (I think get count of dates from first to last order divided by order counts will do the job)
3. Show output email,average days between orders, count of orders

Could this be done? Thank you in advance. 

[lordignus]

This calculates average time between ALL orders (not just the last 3) for all customers who've placed 3 or more orders. Hope you find it useful.

 

SELECT c.id_customer, c.email, COUNT(o.id_order) AS total_orders, ROUND(AVG(DATEDIFF(o.date_add, o2.date_add)),0) AS avg_days_between_orders
FROM ps_customer c
LEFT JOIN ps_orders o ON c.id_customer = o.id_customer
LEFT JOIN ps_orders o2 ON c.id_customer = o2.id_customer
WHERE o.id_order > o2.id_order
GROUP BY c.id_customer
HAVING total_orders > 2
ORDER BY avg_days_between_orders asc