Because we have run through the process a couple of times these posts on setting up the KPI spreadsheets start getting a little less repetitive and little more to the point.
I entered into my eval system as Lee Hogan and entered one Lead through the Insert Lead menu item. I refreshed the Visual Analyzer (VA) file and the Lead Dial now showed that Lee had 1 Lead in the database.
Seems simple enough, my next thought was what happens when I convert the Lead to a Contact in the database. I experimented around with my newly entered lead and used the Convert Qualified Lead wizard. After the wizard operation was complete, I refreshed VA and noticed the Lead dial for Lee went back down to 0. I checked into the Leads table a little and discovered that my contact indeed did not exist and was completely transformed over to the Contact table.
Next, I did a search and found the newly entered Contact. The Contact record had a History record written in the database with a description of “Lead Conversion Notes”. This provided the information I needed to draw the number of Leads a User enters into the system and also allows me to pull out the number of Leads that were also converted into Contacts.
For the client I am working with, I wrote the following SQL Script to help determine how many Leads had been converted to Contacts in the system.
SELECT COUNT(sysdba.CONTACT.CONTACTID) AS ConvertedLeads, sysdba.USERINFO.USERNAME, YEAR(sysdba.CONTACT.CREATEDATE) as Year
FROM sysdba.CONTACT INNER JOIN
sysdba.HISTORY ON sysdba.CONTACT.CONTACTID = sysdba.HISTORY.CONTACTID INNER JOIN
sysdba.USERINFO ON sysdba.HISTORY.USERID = sysdba.USERINFO.USERID
WHERE (sysdba.HISTORY.DESCRIPTION LIKE ‘%Lead Conversion Notes%’)
GROUP BY sysdba.USERINFO.USERNAME, sysdba.CONTACT.CREATEDATE
I then wrote this SQL Script to determine the number of Leads that the users had in the system by year.
SELECT sysdba.USERINFO.USERNAME, COUNT(sysdba.LEAD.LEADID) AS UnConvertedLeads, YEAR(sysdba.LEAD.CREATEDATE) AS [Year]
FROM sysdba.LEAD INNER JOIN
sysdba.USERINFO ON sysdba.LEAD.CREATEUSER = sysdba.USERINFO.USERID
GROUP BY sysdba.USERINFO.USERNAME, YEAR(sysdba.LEAD.CREATEDATE)
For each user, we looked at the number of Leads leads and the number of Leads converted to Contact for the last three years. The clients top user had 905 Leads in the system for 2007, 127 were converted into Contacts. This calculated out to 14% of the user entered as Leads had been converted to actual Contacts. Using this information the client set the goal for each user at 600 leads for 2008/2009 with the hope that there would be a 10% conversion of leads to Contacts with a further hope that more then 50% of those Contacts will become customers. This means 50 new Leads a month with 5 actually becoming Contacts and 2-3 becoming Customers while maintaining the Users current base.
We updated the Leads Spread Sheet as stated in previous posts. Just remember the spreadsheet name within the Microsoft Excel file is KPI (4). Also, remember the evaluation users (like Lee Hogan) will continue to display until all of the KPI Spread Sheets are updated with your User base.
Tickets KPI is up next.
