Saturday, January 10, 2015

Lead Scoring using SQL Server Analysis Services and Excel

Sales and Marketing departments at many companies use lead scoring to determine sales readiness of leads. At a high level, lead scoring involves the following steps:

  • Identify key factors that influence customer’s purchasing decision
  • Determine relative impact of each factor on the purchasing decision
  • Formulate lead scoring rules using the above information
  • Determine a score threshold required for a lead to be identified as sales ready
Key factors that may influence a lead’s purchasing decision depend on the product or service being offered and may include any of the following:
  • Demographic attributes such as gender, marital status, home ownership, income, number of children, location, etc.
  • Online behavior such as web inquiries, number of visits to a web page, clicks in response to an email campaign, etc.
  • Lead source including search engine, marketing campaign, trade show, etc.
Leads above the pre-determined lead score threshold are converted into qualified opportunities and pursued further. Also, depending on the score, a lead may be assigned to appropriate sales resources. For example leads with the score above 500 may be assigned to a senior salesperson whereas leads with the score between 200 and 300 may be assigned to telemarketing. Leads between the score of 100 and 200 may be included in a nurture campaign.
Often, sales and marketing departments collaborate to develop a lead scoring model based on their past experience with customers’ purchasing behavior and industry knowledge.
If you have sufficient historical data related to past wins and losses and customers’ demographics, lead source and online behavior, you can mine this data to determine scoring rules and score threshold.
Microsoft offers an Excel based toolset called Table Analysis Tools for Excel which includes Prediction Calculator.
To use the Table Analysis Tools for Excel, you must first create a connection to an instance of Analysis Services installed in Multidimensional server mode. This connection gives you access to the Microsoft data mining algorithms that are used to analyze your data. (Note: The tools will not work with Tabular server mode).

The following screenshots illustrate the sample input data included with the toolset and the output produced by the Prediction Calculator. 
Sample Input Data:


The table in the screenshot above includes demographic data of bike buyers and whether they purchased a bike or not (last column).
Based on this data, the tool produces the following report.

The suggested threshold to maximize profit is based on the numbers you input for:
False Positive Cost: Cost incurred on marketing, sales and other activities on a lead when the positive prediction by the model is wrong i.e. opportunity is lost.
False Negative Cost: Cost of missed opportunity when the negative prediction by the model is wrong.
True Positive Profit: Profit from correctly predicting a positive result i.e. opportunity is won.
True Negative Profit: Profit (or cost saving) from correctly predicting a negative result.
The Score Breakdown shows the list of influencing factors with their relative impact. For example, a relative weight of 47 is used if a prospective buyer is single and 0 if married. You can use these weights to create a scoring formula. The tool provides a worksheet to calculate the score based on the input for each factor and the prediction (win or loss).
If you capture the lead data for influencing factors in Microsoft Dynamics CRM, you can configure a workflow to automatically calculate a lead score based on this data and take appropriate actions such as qualifying a lead, assigning it to an appropriate user or team, sending follow up emails and setting up follow up tasks or phone call activities.