Monday
Jul292013
SugarCRM, SQL query to determine Sales Cycle From New Account --> Customer
Monday, July 29, 2013 at 05:28PM
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”
tagged SugarCRM\
Reader Comments