Seminar #50 - Salesforce Data, Clean, Visualize, Analyze, & Dashboard

Slide 1

Slide 1 text

EXPLORATORY Online Seminar #50 X Get, Clean, Visualize, and Analyze Salesforce Data with Exploratory

Slide 2

Slide 2 text

Kan Nishida CEO/co-founder Exploratory Summary In Spring 2016, launched Exploratory, Inc. to democratize Data Science. Prior to Exploratory, Kan was a director of product development at Oracle leading teams to build various Data Science products in areas including Machine Learning, BI, Data Visualization, Mobile Analytics, Big Data, etc. While at Oracle, Kan also provided training and consulting services to help organizations transform with data. @KanAugust Speaker

Slide 3

Slide 3 text

3 Questions Communication Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) Data Science Workﬂow

Slide 4

Slide 4 text

4 Questions Communication (Dashboard, Note, Slides) Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) ExploratoryɹModern & Simple UI

Slide 5

Slide 5 text

EXPLORATORY Online Seminar #50 X Get, Clean, Visualize, and Analyze Salesforce Data with Exploratory

Slide 6

Slide 6 text

Agenda • Get Data • Clean and Transform Data • Visualize Data • Create, Publish, Share, and Schedule Dashboard • Parameterize Dashboard • Forecast Leads • Predict Win 6

Slide 7

Slide 7 text

Agenda • Get Data • Clean and Transform Data • Visualize Data • Create, Publish, Share, and Schedule Dashboard • Parameterize Dashboard • Forecast Leads • Predict Win 7

Slide 8

Slide 8 text

Supported Salesforce Editions 8 • Professional • Enterprise • Unlimited • Performance • Developer

Slide 9

Slide 9 text

UI SQL 2 Modes

Slide 10

Slide 10 text

UI SQL 2 Modes

Slide 11

Slide 11 text

No content

Slide 12

Slide 12 text

No content

Slide 13

Slide 13 text

13 Select a table you want to import data for.

Slide 14

Slide 14 text

14 Select columns.

Slide 15

Slide 15 text

Use Filter to limit the data. 15

Slide 16

Slide 16 text

Once the data is imported, it automatically generates the Summary view. 16

Slide 17

Slide 17 text

UI SQL 2 Modes

Slide 18

Slide 18 text

SQL (SOQL)

Slide 19

Slide 19 text

You can write SQL (SOQL) Queries to get data from Salesforce.

Slide 20

Slide 20 text

Agenda • Get Data • Clean and Transform Data • Visualize Data • Create, Publish, Share, and Schedule Dashboard • Parameterize Dashboard • Forecast Leads • Predict Win 20

Slide 21

Slide 21 text

Clean and Transform data from the column header menu.

Slide 22

Slide 22 text

You can update any of the existing data wrangling steps. 22

Slide 23

Slide 23 text

Click ‘Re-import’ button to get the latest data from Salesforce. 23

Slide 24

Slide 24 text

All the data wrangling steps will be automatically applied. 24

Slide 25

Slide 25 text

Agenda • Get Data • Clean and Transform Data • Visualize Data • Create, Publish, Share, and Schedule Dashboard • Parameterize Dashboard • Forecast Leads • Predict Win 25

Slide 26

Slide 26 text

Click the chart icon to quickly create a chart. 26

Slide 27

Slide 27 text

27 It will create a new chart under the Chart view.

Slide 28

Slide 28 text

28 You can customize the chart by updating the column assignments and the chart conﬁguration.

Slide 29

Slide 29 text

But, here is one thing…

Slide 30

Slide 30 text

30 The stage names are sorted alphabetically…

Slide 31

Slide 31 text

It’s better to sort them following the natural order of the stages. 31

Slide 32

Slide 32 text

Prospecting Conversion Hearing 32 Proposal This order is confusing… Stages for Opportunity

Slide 33

Slide 33 text

Prospecting Conversion Hearing 33 Proposal This is intuitive! Stages for Opportunity

Slide 34

Slide 34 text

1 3 4 34 Factor - Ordered Categories 2 Prospecting Conversion Hearing Proposal

Slide 35

Slide 35 text

Stage Hearing Conversion Proposal Prospecting Character (Categorical) 35 Stage Level Prospecting 1 Hearing 2 Proposal 3 Conversion 4 Factor (Ordinal)

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

A new data wrangling step is added to the Step pane. 38

Slide 39

Slide 39 text

39 The values are sorted following the level setting for the Factor data type columns.

Slide 40

Slide 40 text

Open the chart and move the Pin to the latest step. 40

Slide 41

Slide 41 text

Currently, the stages are ordered from the bottom to the top. 41

Slide 42

Slide 42 text

You can change the direction of the order. 42

Slide 43

Slide 43 text

Agenda • Get Data • Clean and Transform Data • Visualize Data • Create, Publish, Share, and Schedule Dashboard • Parameterize Dashboard • Forecast Leads • Predict Win 43

Slide 44

Slide 44 text

44 Here’s a typical Dashboard in Exploratory.

Slide 45

Slide 45 text

45 Numbers Analytics - Forecasting Analytics - Cohort Analysis Map Chart You can bring in various types of numbers and charts.

Slide 46

Slide 46 text

46 You can bring in various types of data.

Slide 47

Slide 47 text

47 And…

Slide 48

Slide 48 text

Since you can create charts with different steps of Data Wrangling…

Slide 49

Slide 49 text

49 You can bring in charts that are based on different steps.

Slide 50

Slide 50 text

50 Create a new Dashboard.

Slide 51

Slide 51 text

51 Add an existing chart.

Slide 52

Slide 52 text

Slide 53

Slide 53 text

53 Adjust the height of each row section by drag-and-drop.

Slide 54

Slide 54 text

54 Adjust the width of each column section by drag-and-drop.

Slide 55

Slide 55 text

55 Click ‘Run’ button to preview the output.

Slide 56

Slide 56 text

56 By clicking the ‘Re-Import Data’ button, you can query the latest data from Salesforce and apply all the data wrangling steps that are required to generate the charts.

Slide 57

Slide 57 text

Publish the Dashboard to: 1. Share 2. Interact 3. Schedule 57

Slide 58

Slide 58 text

Publish the Dashboard either in Private or Public mode.

Slide 59

Slide 59 text

59 Once it’s published the Dashboard will have its own unique URL.

Slide 60

Slide 60 text

60 Open the Dashboard in a web browser.

Slide 61

Slide 61 text

61 You can invite speciﬁc people to share your Dashboard securely.

Slide 62

Slide 62 text

62 Type their email addresses and write an inviting message.

Slide 63

Slide 63 text

63 They will receive an email like the below.

Slide 64

Slide 64 text

64 If they don’t have Exploratory accounts yet they can sign up for free ‘Viewer’ accounts.

Slide 65

Slide 65 text

Schedule your Dashboard so that the data inside the Dashboard is kept up-to-date automatically. 65

Slide 66

Slide 66 text

You can subscribe a notiﬁcation email every time the scheduling job is run. 66

Slide 67

Slide 67 text

67 Other invited members can also subscribe the notiﬁcation emails.

Slide 68

Slide 68 text

You will receive notiﬁcation emails with thumbnail images. 68 is updated. is updated.

Slide 69

Slide 69 text

69 Dashboard Dashboard Exploratory Desktop Exploratory Server Publish OAuth OAuth How the scheduling works.

Slide 70

Slide 70 text

70 Take a look at the past seminar for more details on the lifecycle of Dashboard!

Slide 71

Slide 71 text

Select ‘How To (Tutorials)’. 71

Slide 72

Slide 72 text

Click on ‘Dashboard’. 72

Slide 73

Slide 73 text

Click on ‘Dashboard’. 73

Slide 74

Slide 74 text

Agenda • Get Data • Clean and Transform Data • Visualize Data • Create, Publish, Share, and Schedule Dashboard • Parameterize Dashboard • Forecast Leads • Predict Win 74

Slide 75

Slide 75 text

75 Me Team Mates I want data for Europe. I want data for Africa. I want data for Asia. Exploratory Server Everybody has a different interest. Publish

Slide 76

Slide 76 text

76 By parameterizing the Dashboard, you can let them choose which part of the data they want to see.

Slide 77

Slide 77 text

• Data Source Filter • Step Filter • Chart Filter 3 Type of Filters

Slide 78

Slide 78 text

You can create Filters in the data import dialog so that you can limit the data you query from Salesforce. 78

Slide 79

Slide 79 text

79 You can parameterize this Filter!

Slide 80

Slide 80 text

80 You can create Filters on top of the imported data as data wrangling steps.

Slide 81

Slide 81 text

81 You can parameterize the Step Filter as well!

Slide 82

Slide 82 text

You can create Filters inside the Chart. This Chart Filter works only for the particular chart. 82

Slide 83

Slide 83 text

83 And, you can parameterize the Chart Filter as well!

Slide 84

Slide 84 text

84 Click the Parameter button.

Slide 85

Slide 85 text

85 All the parameters that are required to generate the chart data will automatically show up in the pane.

Slide 86

Slide 86 text

For the Dashboard, all the parameters that are required to generate data for all the charts inside the Dashboard are automatically added to the Parameter pane. 86

Slide 87

Slide 87 text

For the published Dashboard, you can open the Parameter pane and turn on the interactive mode to start using the parameters. 87

Slide 88

Slide 88 text

A bit more details on how the Parameter works.

Slide 89

Slide 89 text

89 1. Data Source 2. Create Calculation 3. Filter Created data wrangling steps and charts. Chart 2 Chart 1

Slide 90

Slide 90 text

90 Closed Date 1. Data Source 2. Create Calculation 3. Filter Created Filters. Probability Type Chart 2 Chart 1

Slide 91

Slide 91 text

91 Closed Date 1. Data Source 2. Create Calculation 3. Filter Created Dashboard with Chart 2. Dashboard 1 Probability Type Chart 2 Chart 1

Slide 92

Slide 92 text

92 Closed Date 1. Data Source 2. Create Calculation 3. Filter Dashboard 1 Probability When you open the Parameter pane you will see‘Closed Date’ and ‘Probability’ parameters automatically.

Slide 93

Slide 93 text

93 Closed Date 1. Data Source 2. Create Calculation 3. Filter Now, we added Chart 1 to the Dashboard. Dashboard 1 Probability Type

Slide 94

Slide 94 text

Dashboard 1 Type 94 Closed Date 1. Data Source 2. Create Calculation 3. Filter When you open the Parameter pane, the ‘Stage’ parameter is automatically added. Probability

Slide 95

Slide 95 text

Dashboard 1 Type 95 Closed Date 1. Data Source 2. Create Calculation 3. Filter Probability If we change the Probability parameter only the Chart 2 gets updated.

Slide 96

Slide 96 text

Dashboard 1 Type 96 Closed Date 1. Data Source 2. Create Calculation 3. Filter Probability If we change the Stage parameter only the Chart 1 gets updated.

Slide 97

Slide 97 text

Dashboard 1 Type 97 Closed Date 1. Data Source 2. Create Calculation Probability If we change the Closed Date parameter all the steps and the charts will be updated. 3. Filter

Slide 98

Slide 98 text

You can also parameterize the SQL queries.

Slide 99

Slide 99 text

Slide 100

Slide 100 text

100

Slide 101

Slide 101 text

101

Slide 102

Slide 102 text

102

Slide 103

Slide 103 text

Agenda • Get Data • Clean and Transform Data • Visualize Data • Create, Publish, Share, and Schedule Dashboard • Parameterize Dashboard • Forecast Leads • Predict Win 103

Slide 104

Slide 104 text

104 Can we forecast how the number of leads will look like in the next 12 months?

Slide 105

Slide 105 text

105 Want to forecast the number of leads in the next 12 months. Actual Forecasted

Slide 106

Slide 106 text

• Time interval between data has to be same throughout the data • Day with NA is not allowed • Seasonality with multiple periods (Week and Year) is hard to handle • Parameter tuning is hard and requires a forecasting expert level knowledge. Problems with Traditional Time Series Model

Slide 107

Slide 107 text

• A ‘curve ﬁtting’ algorithm to build time series forcasting models. • Designed for ease of use without expert knowledge on time series forecasting or statistics. • Built by Data Scientists (Sean J. Taylor & co.) at Facebook and open sourced. (https:// facebook.github.io/prophet) Prophet Sean J. Taylor @seanjtaylor

Slide 108

Slide 108 text

Build a model by ﬁnding a best smooth line which can be represented as sum of the following components. • Overall growth trend • Seasonality - Yearly, Weekly, Daily, etc. • Holiday effects - X’mas, New Year, July 4th, etc. • External Predictors Prophet - Additive Model

Slide 109

Slide 109 text

109 Lead Data

Slide 110

Slide 110 text

110

Slide 111

Slide 111 text

111

Slide 112

Slide 112 text

112

Slide 113

Slide 113 text

Agenda • Get Data • Clean and Transform Data • Visualize Data • Create, Publish, Share, and Schedule Dashboard • Parameterize Dashboard • Forecast Leads • Predict Win 113

Slide 114

Slide 114 text

114 Can we predict which opportunities we can win?

Slide 115

Slide 115 text

115 Algorithm Model Build a Prediction Model. Conversion Age Time Country Industry TRUE 60 120 Japan Ad FALSE 45 55 US Medical FALSE 52 20 US Media TRUE 48 140 Japan Ad TRUE 53 80 UK Bank FALSE 35 30 Japan Media

Slide 116

Slide 116 text

116 Predict Conversion Age Time Country Industry TRUE 25 120 Japan Ad FALSE 23 55 US Media FALSE 40 150 US Ad Conversion Age Time Country Industry ? 25 120 Japan Ad ? 23 55 US Media ? 40 150 US Ad Algorithm Model Conversion Age Time Country Industry TRUE 60 120 Japan Ad FALSE 45 55 US Medical FALSE 52 20 US Media TRUE 48 140 Japan Ad TRUE 53 80 UK Bank FALSE 35 30 Japan Media

Slide 117

Slide 117 text

117 A model is a deﬁnition of a pattern the algorithm has captured in the data. Algorithm Model Conversion Age Time Country Industry TRUE 60 120 Japan Ad FALSE 45 55 US Medical FALSE 52 20 US Media TRUE 48 140 Japan Ad TRUE 53 80 UK Bank FALSE 35 30 Japan Media

Slide 118

Slide 118 text

118 Not just for the prediction, we can also use it to learn a lot about the patterns in data. Insight • Which variables have stronger relationship with the target variable. • How are they related? • Are they signiﬁcant? • What is the quality if we used this model to predict? Algorithm Model

Slide 119

Slide 119 text

119 Opportunity Data

Slide 120

Slide 120 text

120 The StageName column indicates whether we have won a given opportunity or not.

Slide 121

Slide 121 text

121 What are the characteristics of these ‘Won’ opportunities?

Slide 122

Slide 122 text

In order to use the Correlation Mode and build a prediction model, we need to convert the StageName variable to Logical (True / False) data type. 122

Slide 123

Slide 123 text

Create a calculation that returns TRUE if a given value is ‘Closed Won’. 123

Slide 124

Slide 124 text

124

Slide 125

Slide 125 text

125 11.7% of the existing opportunities are ‘Closed Win’.

Slide 126

Slide 126 text

126 Click ‘Correlate’ button to open the Correlation mode.

Slide 127

Slide 127 text

Select ‘Is_Won’ variable as the target variable.

Slide 128

Slide 128 text

It shows the relationship between the Is_Win and all other variables with the charts along with metrics.

Slide 129

Slide 129 text

Sort the columns based on the AUC values, which indicate the strength of the relationship. 129

Slide 130

Slide 130 text

The ‘CallAmount’ has a strong relationship with the ‘Is_Won’. 130

Slide 131

Slide 131 text

We want to build a prediction model, but which one?

Slide 132

Slide 132 text

Numeric TRUE/FALSE TRUE/FALSE + Time Linear Regression Random Forest / XGBoost Statistical Learning Machine Learning Logistic Regression Cox Regression Survival Forest 132 Regression Model Classiﬁcation Model Survival Model Data Type Statistical Learning Machine Learning Statistical Learning Machine Learning Random Forest / XGBoost

Slide 133

Slide 133 text

Let’s try with Logistic Regression model.

Slide 134

Slide 134 text

Select multiple variables and select one of the prediction model algorithms. 134

Slide 135

Slide 135 text

135 A logistic regression model is created to predict the probability of winning.

Slide 136

Slide 136 text

Under the ‘Prediction’ tab, you can see how the probability of winning changes as the values in a given variable change. 136

Slide 137

Slide 137 text

137 Under the Importance tab, you can see which variables are more important to predict the probability of winning.

Slide 138

Slide 138 text

138 Under the Summary tab, you can see the quality of the prediction model.

Slide 139

Slide 139 text

139 Under the Data tab, you can see the predicted result for all the existing accounts.

Slide 140

Slide 140 text

140 This prediction result is for the existing data for which we know the answer already.

Slide 141

Slide 141 text

But, we want to predict the probability of winning for the future opportunities, not the past opportunities.

Slide 142

Slide 142 text

You can predict for the future opportunities by using the model we have just created.

Slide 143

Slide 143 text

143 Import a new opportunity data.

Slide 144

Slide 144 text

144

Slide 145

Slide 145 text

145 Select the model we have just created under the Analytics view.

Slide 146

Slide 146 text

146 A set of new columns is added as the prediction values.

Slide 147

Slide 147 text

That’s it for today!

Slide 148

Slide 148 text

Next Seminar

Slide 149

Slide 149 text

EXPLORATORY Online Seminar #52 7/7/2021 (Wed) 11AM PT Machine Learning - Variable Importance

Slide 150

Slide 150 text

No content

Slide 151

Slide 151 text

Information Email kan@exploratory.io Website https://exploratory.io Twitter @ExploratoryData Seminar https://exploratory.io/online-seminar