Average Time of Order Accepting

[hogava]

hi,

in support overview page we can see Average Time Response for ticket,

how can i see Average Time between ordering and completing it?

thanks

[Kian]

No such feature exists. Moreover WHMCS doesn't record any date when the order completes meaning that if you want to get Average Time, first you'll need to store this date somewhere.

5 hours ago, hogava said:

how can i see Average Time between ordering and completing it?

What do you mean with the expression "completing"? Invoice paid? Order accepted?

[brian!]

On 26/05/2019 at 12:26, Kian said:

What do you mean with the expression "completing"? Invoice paid? Order accepted?

the answer to that question is important because WHMCS does log when an order is placed, when it's accepted and also when an invoice is paid - so a time calculation could be made between any two conditions and averaged out.

Edited May 28, 2019 by brian!

[hogava]

I mean when Order accepted, want to check how much time it takes for the service to be delivered after the customer has paid.

It seems can do this with Hook.

 

[Kian]

1 hour ago, brian! said:

the answer to that question is important because WHMCS does log when an order is placed, when it's accepted and also when an invoice is paid - so a time calculation could be made between any two conditions and averaged out. 

Really? Where? 🤔 Order placed and invoice paid are in tblorders.date and tblinvoices.datepaid but I have no idea where's the date for order accepted.

[brian!]

On 26/05/2019 at 14:55, Kian said:

Really? Where? 🤔 Order placed and invoice paid are in tblorders.date and tblinvoices.datepaid but I have no idea where's the date for order accepted.

aahh, I thought I had said where... my bad 🙄

it's not stored in a simple way as per tblorders or tblinvoices, but it will (should is probably safer to say!) be stored in the activity log, so tblactivitylog...

so the activitylog stores when an order is placed and when it's accepted... as you already know, you will more easily be able to get the date an order was placed from tblorders, then you'd have to query tblactivitylog to see if the order has been accepted (and not later returned to pending etc), and if so grab the date, do your calculation and then repeat for other orders, and finally average out the results... so not as straightforward as if the acceptance date was stored in the tblorders table, but with some effort, it should be possible to calculate an average time between placing and accepting. 🙂

[Kian]

I was suspecting that. Personally I would create a dedicated table/column somewhere else. Joining tblorders with tbltickets and then with tblactivitylog on "description" column (TEXT field with no index and thousand of records) is inefficient. But I understand that 95% of times there will be no problem at all.

[brian!]

1 minute ago, Kian said:

Personally I would create a dedicated table/column somewhere else.

I wouldn't argue with that - for a new user with a clean database, having a separate t/c would make sense... whereas, if you have years of existing data and then decide you want to know the average time between the two events, that's not as sensible (though still doable).

2 minutes ago, Kian said:

Joining tblorders with tbltickets and then with tblactivitylog on "description" column (TEXT field with no index and thousand of records) is inefficient.

I wasn't suggesting joining tblactivitylog into the query. 🙂