SugarCRM, SQL query to determine Sales Cycle From New Account –> Customer

sugarcrmdevelopers —  August 7, 2013

SQL query below runs against SugarCRM Audit tables to determine how long it took an Account record to become a “Customer”.  It looks at the audit tables to calculate when the field was changed.

 

SELECT accounts.name, accounts.industry, accounts_audit.before_value_string, accounts_audit.after_value_string, 
accounts.date_entered,accounts_audit.date_created AS “Became Customer”, TIMESTAMPDIFF(DAY, accounts.date_entered, accounts_audit.date_created) AS “How Long”
FROM accounts_audit
JOIN accounts ON accounts_audit.parent_id = accounts.id
JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
WHERE after_value_string = “Customer” AND accounts.deleted = “0”

SELECT accounts.name, accounts.industry, accounts_audit.before_value_string, accounts_audit.after_value_string, accounts.date_entered,accounts_audit.date_created AS “Became Customer”, TIMESTAMPDIFF(DAY, accounts.date_entered, accounts_audit.date_created) AS “How Long”FROM accounts_auditJOIN accounts ON accounts_audit.parent_id = accounts.idJOIN accounts_cstm ON accounts.id = accounts_cstm.id_cWHERE after_value_string = “Customer” AND accounts.deleted = “0”