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

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”