Calculate Salesforce Account Coverage with Treasure Data and SQL

You can check out the video of this integration.

Salesforce is the de facto cloud CRM tool for sales. But even though it’s ubiquitous in most sales organizations, there are still a few clear challenges to doing analytics with Salesforce: like limited visualization capabilities, problems sharing reports and difficult cross-object reporting.

Lucky for you, Treasure Data has a solution for each of these challenges!

Limited visualization capabilities? Treasure Data lets you get your data into a known format and then use one of the many available data visualization tools and templates already out there.

Problems sharing reports? Treasure Data lets you add accounts, so the right people can look at the same data.

Difficulties accessing historical data? With Treasure Data, you’d have no problem accessing nor querying data – whether it was collected a day, a week, or several years ago.

Difficult cross-object reporting? Let’s say you wanted to learn your account coverage – that is, the ratio of number of accounts with a contact to total number of accounts. To do this, you’d need to pull two separate reports (one from contacts, another from accounts), export them from Salesforce into something like Excel, and then manually merge them. We’ll demonstrate the solution to this problem below.

The Problem

We want to calculate our account coverage, which is the following ratio:

Coverage % = # of target accounts with a contact / # of target accounts

A target account means one of our sales staff has flagged the account for deeper follow up and targeted marketing efforts. A contact means we have someone we know on the inside.
We may want to run this calculation periodically and plot the result.

The solution

We’ll export both account and contact tables from SFDC to Treasure Data to two separate tables, join these tables, and then analyze them for further insight.

You can do this periodically to track trends.
Assuming you have Treasure Data toolbelt setup and a Treasure Data account, let’s look at one approach:

  1. Create .yml files for each data source you want to pull data from. Each will have a separate resulting table in Treasure Data:Contacts
    sfdc_contacts.yml:



    Accounts
    sfdc_accounts.yml:

  2. The very first time our command was run, we’d want to guess the schema and issue the data to the table, which we’re creating on the fly.  That command would look like this for our contact table:



    …and this for our account table:

    https://gist.github.com/Jammink2/5831f4ea28f403c1459dc4efc262f94c

  3. Next, you can use either the console or CLI to query the data.  Here’s the query we put together after extracting our data from SFDC and importing it to Treasure Data:



    Let’s break down what exactly this query does.

    1. In Treasure Data, we start with two tables:   account and contact;
    2. We create table t1 with account_id and region pulled from (differently named) fields on our account table.

      We’re only interested in those records where the boolean target_account__c = 1, meaning the record refers to a target account that our Sales team had flagged as such originally  in Salesforce.
    3. We create table t2 by selecting  accountid from our contact table, and renaming it to account_id;  we also count the total number of contacts in contact.
    4. We left join t1 with t2 where account_id’s are equivalent.

      We create table t3 by selecting the following values from our left joined table by t1.account_id,
      t1.region,
      t2.num_contacts
    5. Finally we calculate the ratio in line 33 from values in t3.

    When we ran these steps against our production Salesforce data, which we imported, we ended up with the following data set:

    Which, when plotted, looks as follows (1 = 100% coverage):

    What’s next?

    You can check out the video of this integration.

    Are you looking for a flexible, SQL-based way to manage your data and derive analytics from Salesforce?  Look no farther.

    And, if you’re trying us for the first time, why not  try our service free for 14 days?

Request a demo
John Hammink
John Hammink
John Hammink is Chief Evangelist for Treasure Data. An 18-year veteran of the technology and startup scene, he enjoys travel to unusual places, as well as creating digital art and world music.
In Case You Missed It