Running SOQL queries on Salesforce objects in R

A classic in B2B marketing: an end-to-end funnel that gives a complete overview of the cost-per-acquisition or even the return-on-investment. Given the fact that Salesforce is one of the most popular CRMs in the world, creating an end-to-end funnel analysis might require you to interact with Salesforce. Let’s do it in R.

The easiest way to get data out of Salesforce, whether classic or the Lightning platform, is via SOQL queries. SOQL is an acronym for Salesforce Object Query Language. It is a simplified structured query language. You can find some of the limitations in the documentation.

The go-to-library to interact with Salesforce from R is salesforcer. It’s a pretty advanced library with many features and is built on another relevant package, RForcecom.

First, you can get authorized via OAuth using the sf_auth function. It will open a browser window where you can log into Salesforce. You can also login through your company’s Salesforce domain and via Single Sign-on.

sf_auth()

For many, this will be the most important part of this blog post. If you receive an error in both your R console and your browser, you need to install the httpuv package, a building-block of the salesforcer package.

R: httpuv not installed, defaulting to out-of-band authentication

Browser: error=redirect_uri_mismatch&error_description=redirect_uri%20must%20match%20configuration

Querying data is straightforward if you know some SQL. In the following example, I query all email adresses from the Lead object. If you want to filter on a date, don’t forget to use the DAY_ONLY function.

df <- sf_query(
	soql = 'SELECT Email FROM Lead WHERE DAY_ONLY(CreatedDate) > 2020-09-01 NULLS FIRST'
)

By the way, if you’re having trouble understanding some of the code and concepts, I can highly recommend “An Introduction to Statistical Learning: with Applications in R”, which is the must-have data science bible. If you simply need an introduction into R, and less into the Data Science part, I can absolutely recommend this book by Richard Cotton. Hope it helps!