Home » Cloud » R and Salesforce

R and Salesforce

Introduction

R is widely used among scientists and statisticians to perform statistical analysis while Salesforce.com is one of the leading CRM software packages used for Marketing and Salesforce automation. Salesforce.com contains vital information regarding Leads, Customers, Contacts, Opportunities and Cases. Currently this data is mainly used for operational purposes by Sales and Marketing professionals. How about using this data for predictive analysis or forecasting?

Unfortunately Salesforce.com built on a multi-tenant architecture, limits system usage heavily. Only way to achieve this is to read Salesforce data using Java Web Services API provided by Salesforce and feeding that data to R.

One way is to export salesforce data to csv file and importing the data into R.
But this will be quite tedious and time consuming. It would be nice if one can query Salesforce.com data directly from R and create a data frame or matrix for performing analyses.

rJava

Fortunately Java code can be directly invoked from R using rJava package. Values returned by Java methods are available in R session memory and can be mapped to equivalent R data structures. So, theoretically it must be possible to invoke a java method from R which connects to Salesforce.com and returns the required data.

Let us consider a trivial use case where a java method accepts String argument containing SOQL (Salesforce.com equivalent of SQL) and execute that statement returning data as a list of lists such as vector of vectors.

The first step is to install rJava as follows:

Make sure that Java 1.6 or higher is installed and all the environment variables are pointing to correct directories or folders. (Such as CLASSPATH and PATH)

Start R

install.packages(“rJava”)

Once the installation is successful, try

.jnit().

This command will initialize Java.

myRObj=.jnew(“myJavaobj”) will crate a java object from your java class.

For detailed referece, please refer: http://cran.r-project.org/web/packages/rJava/rJava.pdf

Now, I am going to develop a java class say, SalesforceHelper.java having a method queryObject.

GetObject method will accept 1 String argument:SOQL statement. (Salesforce.com Equivalent of SQL) such
as Select Id, Name From Lead.

Force.com Web Service Connector (WSC)

Salesforce.com provides extensive java api to perform database query or updates. In order to develop SalesforceHelper java class mentioned above, I am going to use WSC-20.jar along with enterprise.wsdl.

To download enterprise.wsdl, login to your salesforce.com org and click on Setup.

Click on Develop->API.

Click on enterprise.wsdl.

You may download wsc-20.jar from the project url:

http://code.google.com/p/sfdc-wsc/downloads/list

Once you download wsc-20.jar, you need to create enterprise.jar as follows:

java -classpath wsc-20.jar com.sforce.ws.tools.wsdlc enterprise.wsdl enterprise.jar

Add wsc-20.jar and enterprise.jar files to your java CLASSPATH environment variable.

Let us write a java class to connect to Salesforce.com, perform SOQL and return a Vector containing Vectors of rows which in turn contain the selected field values.
Thus if our query is SELECT Name, Salary__c from Lead LIMIT 20, then our java method will return Vector containing 20 Vectors, each containing 2 objects of type String and Double.

/*
Copyright (c) 2012, Ardent Software, LLC.
All rights reserved.

Redistribution and use in source and binary forms, with or without modification,
are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice,
this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright notice,
this list of conditions and the following disclaimer in the documentation
and/or other materials provided with the distribution.
* Neither the name of the Ardent Software, LLC. nor the names of its contributors
may be used to endorse or promote products derived from this software
without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
OF THE POSSIBILITY OF SUCH DAMAGE.

****

Note that this code may require some work before you can deploy it to a standard org.
*/
import com.sforce.soap.enterprise.Connector;
import com.sforce.soap.enterprise.DeleteResult;
import com.sforce.soap.enterprise.EnterpriseConnection;
import com.sforce.soap.enterprise.Error;
import com.sforce.soap.enterprise.QueryResult;
import com.sforce.soap.enterprise.SaveResult;
import com.sforce.soap.enterprise.sobject.SObject;
import com.sforce.soap.enterprise.sobject.AggregateResult;
import com.sforce.ws.ConnectionException;
import com.sforce.ws.ConnectorConfig;
import java.util.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

public class SalesforceHelper {

public class Main {

  static final String USERNAME = "USER";
  static final String PASSWORD = "PASSWD+SEC TOKEN";
  static final String ENDPOINT = "https://login.salesforce.com/services/Soap/c/23.0";
  static EnterpriseConnection connection;

  public static void main(String[] args) {

  public static Vector queryObject(String qry) throws Exception {
    ConnectorConfig config = new ConnectorConfig();
    config.setAuthEndpoint(ENDPOINT);
    config.setUsername(USERNAME);
    config.setPassword(PASSWORD);

    Vector rowContainer = new Vector();
    String delims = "[ ,]+"; //Parse query tokens with these delimiters
    String[] tokens = qry.split(delims);
    String tableName=null;
    ArrayList colNames = new ArrayList();
    ListIterator listItr = colNames.listIterator();
    for (int i=1;i<tokens.length;i++){ //Start with 1 because 0 will be Select
        if (tokens[i].equalsIgnoreCase("from")){
            tableName=tokens[i+1].trim();
            break;
       }else{
            colNames.add(tokens[i].trim());
       }
   }
   Object[] cols = colNames.toArray();
   System.out.println("columns:"+cols+" Object:"+tableName);
   connection = Connector.newConnection(config);

   QueryResult queryResults = connection.query(qry);

   if (queryResults.getSize() > 0) {
      for (int i=0;i<queryResults.getRecords().length;i++) {
           Vector vrow = new Vector();
           String obj="com.sforce.soap.enterprise.sobject."+tableName;
           SObject  sfObject = (SObject)queryResults.getRecords()[i];
           Class<?> cl = Class.forName(obj);
          for (int j=0;j<cols.length;j++){
               String methodName="get"+(String)cols[j]; //Method Name will be get<colname> such as getName()
               Method m = cl.getMethod(methodName);
               vrow.add(m.invoke(sfObject)); //Invoke method to get the field value
               System.out.println("Row#"+i+": "+vrow);
          }
         // Add row to row container
         rowContainer.add(vrow); //Add Row to the container vector.
      }
  }

return rowContainer;
 }

}

Now we have to write a function in R which will accept one argument and return a data frame as follows:

getSFDataFrame<-function(arr){
arr<-arr$toArray()
rows<-arr$length
cols<-arr[[1]]$size()
df<-data.frame()
for(i in 1:rows){
  for (j in 1:cols){
    if (arr[[i]]$toArray()[[j]] %instanceof% "java/lang/String"){
        df[i,j]<-as.character(arr[[i]]$toArray()[[j]]$toString())
     }else if (arr[[i]]$toArray()[[j]] %instanceof% "java/lang/Double"){
        df[i,j]<-as.double(arr[[i]]$toArray()[[j]]$toString())
     }
  }
 }
return(df)
}

Currently this function is handling only String and Double object types. But it can be very easily extended to handle all the object types.
Also, this function is using java reflection to execute methods such as toArray() and toString().
Using reflection in this manner is very inefficient. Better way is to use Java Native Interface (JNI).
Please refer rJava reference as mentioned above.

Now let us get our Lead data as follows:

library(rJava)
.jnit()

sfObj=.jnew("SalesforceHelper")
vec=sfObj$queryObject("SELECT Name, Salary__c From Lead LIMIT 20")

LeadDataFrame<-getSFDataFrame(vec)

We now have Lead data frame in R which can be saved for further analysis.

Next Step

Performing some meaningful analysis on Lead data such as Regression Analysis to find out if any lead attributes are directly related to conversion.

About these ads

6 Comments

  1. Ajay Ohri says:

    This is very interesting. Have you thought of creating a R package for Salesforce?

  2. I have never ever imagined that studying can be so significantly exciting.

  3. Vinod Plakkal says:

    Looks very interesting ..truly helpful for core sales analysis .

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 46 other followers

%d bloggers like this: