CRM

Time

How

Probability of Closing

Deal Size

Sales Data

Probability of Closing

Deals Closed by Rep

ggplot(leads2) + geom_bar(aes(x=Status, fill=Status, color=Status)) + 
    facet_wrap(~Rep) + scale_fill_brewer(palette='Paired') + 
    scale_color_brewer(palette='Paired') + theme(legend.position='none')

Deals Closed by Level

ggplot(leads2) + geom_bar(aes(x=Status, fill=Status, color=Status)) + 
    facet_wrap(~Level) + scale_fill_brewer(palette='Paired') + 
    scale_color_brewer(palette='Paired') + theme(legend.position='none')

Deals Closed by Service

ggplot(leads2) + geom_bar(aes(x=Status, fill=Status, color=Status)) + 
    facet_wrap(~Service) + scale_fill_brewer(palette='Paired') + 
    scale_color_brewer(palette='Paired') + theme(legend.position='none')

Deals Closed by Source

ggplot(leads2) + geom_bar(aes(x=Status, fill=Status, color=Status)) + 
    facet_wrap(~Source) + scale_fill_brewer(palette='Paired') + 
    scale_color_brewer(palette='Paired') + theme(legend.position='none')

Probability of Closing

\[ p(y_i=1) = \text{logit}^{-1}(\boldsymbol{X}_i\boldsymbol{\beta}) \]

\[ \text{logit}^{-1}(x) = \frac{e^x}{1+e^x} = \frac{1}{1+e^{-x}} \]

Binary Regression

win1 <- glm(factor(Status) ~ Rep + Level + Source + Service, data=leads, family=binomial)
term estimate std.error statistic p.value
(Intercept) -10.18 0.23 -43.69 0
RepGeorge Aaronow -4.25 0.13 -33.29 0
RepJohn Williamson 2.73 0.10 26.38 0
RepRicky Roma 4.45 0.12 37.24 0
RepShelley Levene -5.61 0.14 -38.80 0
LevelDirector 8.99 0.21 43.20 0
LevelExecutive 10.43 0.22 47.05 0
LevelManager 4.62 0.17 27.57 0
LevelPartner 11.49 0.23 48.89 0
SourcePartner 5.11 0.13 38.99 0
SourceReferral 4.23 0.12 34.50 0
SourceSearch -2.94 0.11 -27.80 0
ServiceSoftware -2.74 0.08 -35.59 0

Visualize

coefplot(win1, sort='mag')

By Rep

By Level

Deal Size

Deal Size by Rep

ggplot(leadsWon) + ggridges::geom_density_ridges2(aes(x=Amount, y=Rep)) + 
    scale_x_continuous(label=scales::dollar) + 
    theme(axis.text.x=element_text(angle=40, hjust=1))

Deal Size by Rep and City

ggplot(leadsWon) + geom_violin(aes(x=Rep, y=Amount)) + 
    scale_y_continuous(label=scales::dollar) + 
    theme(axis.text.x=element_text(angle=40, hjust=1)) + facet_wrap(~City)

Deal Size

\[ \boldsymbol{Y} = \boldsymbol{X}\boldsymbol{\beta} + \boldsymbol{\epsilon} \]

\[ \begin{bmatrix} Y_1 \\ Y_2 \\ Y_3 \\ \vdots \\ Y_n \end{bmatrix} = \begin{bmatrix} 1 & X_{11} & X_{12} & \dots & X_{1p} \\ 1 & X_{21} & X_{22} & \dots & X_{2p} \\ \vdots & \vdots & \vdots & \ddots & \vdots \\ 1 & X_{n1} & X_{n2} & \dots & X_{np} \end{bmatrix} \begin{bmatrix} \beta_0 \\ \beta_1 \\ \beta_2 \\ \vdots \\ \beta_p \end{bmatrix} + \begin{bmatrix} \epsilon_1 \\ \epsilon_2 \\ \epsilon_3 \\ \vdots \\ \epsilon_n \end{bmatrix} \]

Linear Regression

size3 <- glm(Amount ~ Rep + Source + Service, data=leadsWon)
term estimate std.error statistic p.value
(Intercept) 74481.94 731.65 101.80 0.00
RepGeorge Aaronow 24586.63 817.03 30.09 0.00
RepDave Moss 74247.30 715.68 103.74 0.00
RepJohn Williamson 174968.33 703.11 248.85 0.00
RepRicky Roma 274727.47 699.81 392.57 0.00
SourcePartner 883.76 356.33 2.48 0.01
SourceReferral 781.87 361.48 2.16 0.03
SourceSearch 673.77 527.98 1.28 0.20
ServiceSoftware 487.85 260.50 1.87 0.06

Visualize

coefplot(size3, sort='mag')

Score the Lead

\[ \text{Score} = \text{Amount}*\text{Probability} \]

Score

scores <- bind_cols(newLeads, 
                    tibble(Score=predict(win1, newdata=newLeads, type='response') * 
                             predict(size3, newdata=newLeads))) %>% 
  group_by(Rep) %>% arrange(desc(Score)) %>% slice(1:3) %>% 
  ungroup() %>% arrange(desc(Score))

Score

Rep City Level Source Service Score
Ricky Roma Philadelphia Partner Partner Software 350476.9
Ricky Roma San Jose Partner Partner Software 350476.9
Ricky Roma Philadelphia Partner Partner Software 350476.9
John Williamson San Francisco Partner Partner Software 250404.4
John Williamson Dallas Partner Partner Software 250404.4
John Williamson Dallas Partner Partner Software 250404.4
Dave Moss San Francisco Partner Partner Consulting 149367.3
Dave Moss Palo Alto Partner Partner Consulting 149367.3
Dave Moss Dallas Partner Referral Consulting 148920.7

For the Future

  • Wallet Estimation via Quantile Regression

Scoring Method

  1. Calculate Expected Value
    • Predict Deal Value
    • Predict Probability of Closing
  2. Sort by Score
  3. ABC

Thank You

Jared P. Lander

Packages

[1] "coefplot" "dplyr"    "DT"       "ggplot2"  "here"     "knitr"   
[7] "magrittr" "readr"    "tibble"

The Tools