A Simple Equation
In a dynamic business environment, it’s often unclear what type of marketing activities achieve results or what new experiments to try. In times when the stock market changes by double digits in a month, common wisdom doesn’t always apply.
Innovative companies experiment with new approaches, but need to be able to measure the results of small micro-campaigns or use historic trends to justify investment. We ran into a situation where we wanted to better understand how periodic follow up affected revenue. While this data was already in Saleforce.com, there was no way to rank and sort leads based on the number of tasks. The goal was to create a score to focus on the ones that had gotten the least attention.
Score = Rating * (Age / Activities)
Leads that had fewest follow up activities per month (or any period) would get the highest score. Furthermore, the rating (on a 1-5 scale with 5 being the best fit) would be used to give a boost to the best leads. So I needed to figure our how many activities each lead had. Unfortunately, the reporting built into salesforce.com does not provide this in a sortable way and none of the apps on AppExchange seemed to do this without us buying significant integration work. As this was a small experimental campaign, it did not make sense to invest heavily until the approach shows positive results.
SQL Server Tricks
Once the data was in SQL server, it was very easy to analyze it using Izenda. The one trick we had to do was to truncate the foreign key as they seem to have additional characters on them which tell you what kind of object it links to. In order to work around this I had to use SUBSTRING to get part of the key:
[sql]CREATE VIEW LeadsAndTasks AS SELECT * FROM Leads JOIN Tasks ON Lead_ID = SUBSTRING(WhoID,0,16)[/sql]
With this view in place, it is now possible to query data from both tasks and leads. We can present this view to the user without requiring them to understand the nuances of joins or substrings.
