Eating Our Own Dog Food: Fast Analytics for SalesForce.com
Problems with SalesForce.com Reporting Tools
Because SalesForce.com is essentially a large database, you might think it comes with reporting analysis tools that make it easy to answer business questions. While Salesforce.com has a wide array of packaged reports and the ability to create additional reports, they aren’t meant for in-depth analysis. Existing third party applications meant to add more flexibility to reporting for SalesForce fall into two buckets:
1. “Reporting Tools” whose primary output is text lists that are hard to author. The difficult authoring process and the dense textual results discourage users from doing much more than asking the simplest of questions. Even then, many users export reports to Excel and do what they can with Pivot-tables and other Excel analytics.
2. “Dashboard Gauges” that provide graphical displays of key metrics. These tools assume that managing a sales process is simple enough to be captured by a single speedometer with a bunch of gee-whiz effects. In reality, even sophisticated metrics can’t fully capture the stories that sales data can tell. It is the stories in the details of the data that guide intuition and make it possible to manage a sales pipeline effectively. Rich graphical summaries of the pipeline linked to visual “details-on-demand” provide the summaries and the stories.
Answering Questions with SalesForce Data and Tableau
SalesForce is a complete CRM system that can model many aspects of your company’s interactions with customers such as active opportunities, support calls, and marketing activities. At Tableau, we have focused primarily on using Tableau + SalesForce to understand our sales pipeline, our marketing efforts, and how to optimize our sales cycle.
What are my active opportunities?
The first area of our sales data we started looking at was open opportunities, asking questions like:
+ What deals are in the pipeline?
+ What stage is each of those deals at?
+ How much revenue have we booked this quarter?
+ How is each of the sales representatives doing?
What I find interesting is how the approaches to answering these questions can vary. A number of vendors have analytic offerings for SalesForce and most of them answer these questions with simple and low “data-density” graphics such as a single chart of “top 10 deals” or a gauge showing one or two metrics that capture “percentage to goal for the quarter”. That is useful – but very limited. I prefer graphics that leverage our perceptual system to present information in a manner that can be quickly scanned, understood, and acted on and thus don’t need to shy away from presenting a lot of detailed information. For example, we answer the first question by displaying all of the active deals in the pipeline as a horizontal bar chart:
Figure 1: A horizontal bar graph displaying active opportunities in the sales pipeline that are projected to close this quarter. Two metrics are displayed for each opportunity – the projected amount of the deal and the “weighted” amount of the deal (the projected amount times a weighting factor that captures the probability of that deal closing this quarter). The combination of these metrics highlights companies like “Eldie’s Boat Building” where, because the deal is close to closing, the expected amount exceeds that of larger deals. Color is used to communicate the stage of the deal using an ordered color palette that captures the relationship between stages. The darker green the bar the closer it is to closing. We’ve found this to be a great view for getting a feel for the pipeline’s viability for the quarter.
The graph in Figure 1 is built using the “Opportunities” data described in the latter section of this blog entry. A couple of things I really like about this view:
+ It includes all of the deals in the quarter, not just the top 10. Sorting is used to highlight the top deals but users can also quickly scan the whole list via scrolling to get a feel for the distribution in size and stage of all active deals.
+ It uses both color and a second axis to visually communicate the stage and probability of a deal.
+ It can be easily sorted and filtered to give different perspectives on the pipeline. Swapping the order of fields on the Rows shelf and dropping “Opportunity Owner” on color (2 drags!) shows us deals by stage and who owns them:
Figure 2: The graph from Figure 2 following two quick drags – one swapping the order of fields on Rows and the other dragging the field “Opportunity Owner” to the color shelf. Now the view emphasizes the deals that are in each stage of the pipeline and who owns them. This view can be a great tool for a Sales Manager about to do pipeline reviews with his team. Notice that John Locke is the only one with any deals in the “Negotiate” stage – that might be a troublesome warning sign late in the quarter.
Data dense graphics such as this can be an extremely effective analytics and monitoring tools. The masters of designing data-dense graphics are Edward Tufte and Stephen Few and I highly recommend their books as guidelines to creating great views of your data.
Using that same data source, we can also answer the questions about revenue-to-date. To display the same metrics that are often answered using a large “speedometer gauge” I like to use a simple bar chart combined with Tableau’s reference lines:
Figure 3: Revenue-to-date as a simple bar chart. The green bar is closed revenue and the grey region is expected revenue. A reference line is used to communicate the quarterly goal. The data here looks good if we are early in the quarter. There is clearly enough expected revenue but there is a lot of unclosed business in the pipeline.
This simple graphic is easy to read and understand and, because it has a small footprint, leaves room for detailed information such as per-salesperson revenue. Internally, we have a dashboard of sales metrics that displays total and per-sales people revenue together:
Figure 4: Revenue-to-date as a simple bar chart. The green bar is closed revenue and the grey region is expected revenue. A reference line is used to communicate the quota goal. We can see that James Ford has done a lot of work building a solid pipeline but he hasn’t yet driven them down the sales funnel. This could be a problem as end-of-quarter draws near.
These views are good ways of understanding the current state of the pipeline. We’ve also used Tableau to understand how our sales pipeline and customers have behaved historically.
What is my history with this account?
SalesForce stores complete opportunity and account history. This information can be valuable for many purposes from viewing an accounts purchasing history to understanding key metrics such as average order size or the length of sales cycles. When I see a deal in the pipeline, I often want to understand that account’s purchasing history – have they bought before? How often? Do they tend to large purchases or small incremental purchase? A view I like to use for this is a plot of events over time:
Figure 5: A graphical history of all of an account’s purchases over the last 18 months. The color of a dot communicates the product purchased whereas the size of the dot communicates the size of the sale. Hovering over a purchase provides details-on-demand, such as the description of the opportunity entered by the sales person. We can see that CyberDyne Systems has shown clear and steady progress buying regularly from us.
We’ve set up sheet links that allow us to use this view as “details-on-demand” for the earlier views. For example, we use the following drill-path in our workbooks which allows someone to quickly jump from an overview of the entire pipeline to the activities of a single account. Note that we can jump either to more details within Tableau or, using URL links, to the actual SalesForce interface for that account, opportunity, or contact:
Figure 6: A sheet link allows a user to jump from a bar summarizing expected deals to a list of those deals. Another sheet link can be used to jump from the name of an account to that account’s entire purchasing history and then a final link can jump to SalesForce.com.
What is our current and historic pipeline coverage?
In addition to historical events such as purchases or sales calls, SalesForce also stores a history of all changes to a record such as an Opportunity or Lead. We’ve leveraged the OpportunityHistory table to create views that show our “pipeline coverage” as it is and has evolved over time. The following chart is an example of this: We’ve plotted our “pipeline ratio” (which is the ratio of the total expected amount of deals in the pipe to the remaining quota) day-of-quarter for both the current quarter and historic quarters:
Figure 7: The OpportunityHistory table can be used to compute many valuable metrics such as pipeline ratio which is the ratio of the expected value of the pipeline to the remaining quota for the quarter. This view plots this metric versus day of quarter for the last five quarters. The current quarter is shown in red to draw attention to it whereas previous quarters are muted since they simply provide context. In this view, we can see that the current quarter is off to a much stronger start than historic quarters.
The different views and questions I’ve discussed above are a very small sample of the types of things we’ve been able to do with Tableau + SalesForce.com. We have had a lot of fun answering questions like:
+ What is our average order size and how has it changed over time?
+ What is the conversion rate for each stage of our pipeline?
+ Where do leads come from and what are the best sources?
+ Etc.
For more details about what can be done, I encourage you to contact one of our sales consultants. I am going to focus the remainder of this post on how to get Tableau connected to SalesForce and start asking questions.
How did we connect Tableau to SalesForce.com?
Tableau connects to a wide range of data sources from Excel files to large Netezza data warehouses. But it doesn’t provide native SalesForce.com connectivity – so how do we use Tableau to analyze SalesForce.com data? As recommended by SalesForce, we replicate the SalesForce data into a local data warehouse for analytics. We do this using an OLE DB driver written by a company called ForceAmp. I highly recommend this product – we had it installed and replicating SalesForce data to our local SQL Server 2005 instance within an hour or two of purchase. The ForceAmp product comes with a set of stored procedures which you set to replicate any number of tables from SalesForce at a frequency of your choosing. We have a SQL Server Job that runs the stored procedures hourly and replicates almost all of the SalesForce data giving us near real-time visibility into our CRM data.
The replicated SalesForce data has a lot of tables but you only need to join a couple to answer most questions. For example, their Sales data schema looks like the following:
Figure 8: The SalesForce.com schema for Sales objects from http://www.salesforce.com/us/developer/docs/api/index.htm
But to create all of our views of active opportunities, we simply set up the following joins in Tableau:
Other analysis, such as lead conversion or sales activity, require joining large sets of tables to start to include entities such as contacts, leads, and activities into the data model. The exact model is going to be dependent on the questions you are trying to answer and how you’ve set up Tableau. A couple pointers from our experience:
+ Almost all joins are based on the “Id” fields in the above schema. Primary keys are simply labeled “Id” and foreign keys are labeled as “EntityId” such as “AccountId”
+ Any custom fields you’ve created in SalesForce will show up in the data model but with a suffix of “__c” on the field name.
+ SalesForce has a lot of fields – to make things easier I suggest hiding all fields (in Tableau – just right click in the Data Window) except the relevant ones.
+ I recommend creating some views to simplify connecting to the replicated data. We created views for “Opportunities”, “Leads”, and “Activities”.
Subscribe to our blog
在您的收件箱中获取最新的 Tableau 更新。