O'Reilly Network    
 Published on O'Reilly Network (http://www.oreillynet.com/)
 See this if you're having trouble printing code examples


Using the Salesforce.com API

by Tony Stubblebine
12/04/2006

This is part two in a three-part series on how to build and distribute applications on Salesforce.com's AppExchange.

In "An Introduction to Salesforce.com's AppExchange," I gave an overview of the Salesforce.com platform and the tools available to developers. If you haven't already, you should go back to that article and make sure you have signed up for a free Salesforce developer account and that you know how to edit standard Salesforce objects.

In this article I'm going to turn a blog into a Salesforce lead generator by integrating the blog comments with Salesforce Leads using the Salesforce API, SOQL (a Salesforce-specific, SQL-like query language), and some Salesforce object customization.

If you hear the phrase "lead generation" and picture a car salesman, then you should probably step back and think about what you do when someone posts a comment to your blog. Do you try to find their blog? Google them? Go as far as emailing them? If you comment on my blog, you can be assured that I'm going to email you back.

Lead generation and tracking is a big part of any sales process and a major concept inside the Salesforce CRM. Many businesses are adding blogs to their sales and marketing activities and will want to integrate these into their existing sales process.

Our blog-to-lead application will run as two parts. We'll write a script that runs on the same server as our blog that will check for new comments, and then add the commenter into the Salesforce database using the Salesforce API. The script will be run hourly by the Unix cron program.

The second half of the application will live inside your Salesforce account and be accessible through the Salesforce website. This will be where you'll go to check for new leads and to track who has or hasn't been contacted.

Getting Started with the API

Before you can use the Salesforce API you need to enable it from within your Salesforce account. If you haven't already done this, go to Setup -> Integrate -> AppExchange API and click Download Enterprise WSDL. This will enable the API for your account. If your toolkit requires a WSDL, this is the one you should use. Otherwise you can ignore the actual file.

I'm going to use Perl for the examples because the toolkit maps well to the published API (and because it's a good fit for cron scripts). Practically every other language is represented on the Salesforce API toolkit page, and Java, PHP, or .Net would probably be more natural choice for most developers, since those toolkits are a little more robust than the Perl kit. We'll stick closely to the standard API actions so that you can follow along in any of the other toolkits.

To get going with Perl, you're going to have to install the WWW::Salesforce::Simple module. You can probably install it with the command:

sudo cpan -I WWW::Salesforce::Simple

I ran into a little trouble and had to install both the Crypt::SSLeay Perl module and then reinstall WWW::Salesforce::Simple with the force option. On Debian, you can install Crypt::SSLeay with:

sudo apt-get install libcrypt-ssleay-perl

Now that the library is installed, let's try writing a little bit of code to list the available Salesforce objects (essentially database tables), and then the field of the Lead and Task objects that we're going to be using later.

#!/usr/bin/perl -w
use strict;
use WWW::Salesforce::Simple;

# Authenticate with the Salesforce API based on command line parameters.
my $sforce = WWW::Salesforce::Simple->new(
        'username' => $ARGV[0],
        'password' => $ARGV[1]
);

print "[Available Objects]\n";
foreach my $table (@{$sforce->get_tables()}) {
  print "$table\n";
}

print "\n\n[Fields in the Lead Object]\n";
foreach my $field (@{$sforce->get_field_list("Lead")}) {
  print $field->{name} . "\n";
}

print "\n\n[Fields in the Task Object]\n";
foreach my $field (@{$sforce->get_field_list("Task")}) {
  print $field->{name} . "\n";
}

Perl line-noise aside, the code is doing three things: connecting to the API, listing the available tables, and listing the fields of the Lead and Task tables. These are basic actions provided in every Salesforce API toolkit. You'll need to know your Salesforce username and password, which you can pass in as command line parameters:

perl example.pl user@example.com password

Building a Blog-Comment Lead Generator

Now we're going to get going with the actual lead generation application. We'll start our application by finding comments that need to be added as leads. We'll do that by first checking the Salesforce database to find a timestamp for the last time we added a lead. Then we'll pull any comments from our blog that are more recent than that timestamp.

Of course, we'll want to make sure that our blog leads aren't confused with any other leads in the database. So let's add a new option to the Lead Source field of the Lead table so that we can differentiate blog leads from other leads. Do this from Setup -> Customize -> Leads -> Fields. Use the Edit Lead Source option to add a new source called "blog" (or, if you're planning to generate leads from multiple blogs, then you should enter the name of each blog).

Lead Source
Click for a larger view

Now let's start writing the script that will integrate our blog with our Salesforce account. We'll start by writing code to connect to our Salesforce account and to retrieve the timestamp of the most recently added lead.

#!/usr/bin/perl -w
use strict;
use DBI;
use WWW::Salesforce::Simple;
use Date::Parse;
use Time::Piece;

# Authenticate with the Salesforce API based on command line parameters.
my $sforce = WWW::Salesforce::Simple->new(
        'username' => $ARGV[0],
        'password' => $ARGV[1]
);

# Figure out the last time we updated Salesforce
my $result = $sforce->do_query("Select CreatedDate From Lead Where LeadSource = 'Blog'"); 
my $max_date = '1970-01-01';
foreach my $row (@$result) {
  $max_date = $row->{CreatedDate} if $row->{CreatedDate} gt $max_date;
}

The Salesforce API uses SOQL, an SQL-like query language, for retrieving records from the Salesforce database. It supports Select, From, and Where clauses. The Winter '07 release will introduce Joins. Most of the other features that you rely upon in SQL aren't available in SOQL.

In this case we really needed a MAX() function so we can select the most recent date in one statement. Since that's not available, we instead need to query the CreatedDate from every Lead record and then loop through them in code.

Many developers who run into the limits of SOQL end up building their own local convenience infrastructure. Now that you've seen the limits, you may want to keep track of the last update on the blog server rather than the Salesforce server. This works, and many developers opt for this approach, especially if there's only one update path.

One more thing to note--the Salesforce API returns query results in batches of 2,000 rows. If your application is affected by this, you'll have to call queryMore until you have all the rows. Some toolkits hide this from you as a convenience.

Now that we have a date we can search for new comments.

# Connect to the database for your blog.
# You'll need to know the name of the database, the username, and the password.
my $dbh = DBI->connect("dbi:mysql:$ARGV[2]", $ARGV[3], $ARGV[4]);
my $sth = $dbh->prepare(
"SELECT comment_author, 
        comment_email,  
        comment_url, 
        comment_text, 
        comment_created_on 
FROM  mt_comment
WHERE comment_created_on > ? 
      AND comment_junk_status != -1");
$sth->execute(Time::Piece->new(str2time($max_date))->strftime("%F %T"));

The SQL above is for MovableType blogs. If you're using different blog software, then change this section of code. Everything else that we're building is blog-software agnostic.

To get the code above to work, you'll also need to know the name of blog database, the database username, and the database password. At this point you should have a list of all new comments.

Now let's get ready to store the contact information in the comments as Salesforce Leads and to generate Tasks that remind us to follow up with each lead. Leads and Tasks are both standard Salesforce objects that play a prominent role in the CRM experience. There is one difference between the leads we're capturing in the blog and Salesforce Leads. A Salesforce Lead doesn't have a website URL. Let's add one ourselves.

We did this for the Contact object in the first article, and the steps are identical in this context. Choose Settings -> Customize -> Leads -> Fields. At the bottom of the list of Lead fields is a New button. This starts a four-step process for adding a field. Select the URL field type. Label and name the field "Website." Then go with the defaults for the last two steps.

Now we're going to write code to loop through each comment. We'll check to see if the comment lead already exists as a Salesforce Lead, and if it does, we'll assume the lead is being acted on and do no more than update the database. For each new lead, we'll create a Lead record and a corresponding Task record.

Here's how we start looping through the comments and checking for existing Lead records.

while (my $comment = $sth->fetchrow_hashref()) {
  next unless $comment->{'comment_email'} && $comment->{comment_author};
  my %lead;

  my $result = $sforce->do_query("select Id from Lead where Email = '$comment->{'comment_email'}'");
  $lead{id} = $result->[0]->{'Id'} if $result->[0];

This uses another SOQL query to find existing Lead records based on their email addresses. We've also started generating a hash (sometimes called a dictionary or an associative array) to hold lead data for our call to the Update or Create actions. If we're passing the hash to an Update call, then we need to include a value for the ID field. That's how Update knows which record to act on.

Let's fill in the rest of the fields based on the comment data.

$lead{"type"}       = "Lead";
  $lead{"Email"}      = $comment->{"comment_email"};
  $lead{"LeadSource"} = "blog";
  $lead{"Company"}    = "NA";
 $lead{"Website"}   = $comment->{"comment_url"};

  my ($first, $last) = split(/ /, $comment->{"comment_author"}, 2);
  unless ($last) {
    $last = $first; $first = "";
  }
  $lead{"FirstName"} = $first if $first;
  $lead{"LastName"}  = $last if $last;

The type field tells Salesforce which table to update. The default setup for Salesforce Leads makes Company and LastName required fields. Our comments never have company names and rarely have last names, so we have to improvise. I chose "NA" for company and to make any single-word names the Lead last name.

If we've seen this particular Lead before, all we want to do is update it. That's as simple as passing the hash to the API's Update method. The ID that we added into the hash earlier tells Salesforce which record to update.

  if ($lead{id}) {
    $result = $sforce->update(%lead);
    if ($result->result->{"success"} eq "false") {
        print $result->result->{errors}->{message} . "\n";
    }
  }

Now let's add in code to save new Leads. It works the same way that Update works--just pass in a hash with the required fields.

  else {
    $result = $sforce->create(%lead);
    if ($result->result->{"success"} eq "false") {
        print $result->result->{errors}->{message} . "\n";
    }

Both the create and the update actions return an object that you can check for success and for error messages. I've found the error messages very helpful in times when I didn't know about a required field.

If we managed to create a new Lead without any errors, then we also want to add a Task so that we'll remember to follow up. The result object returned after creating includes the ID of our new lead. We'll need to pass that as the Task's WhoId field to associate the two records. WhoId works like a database foreign key with a little bit of special behavior. The field only accepts Contact and Lead IDs, and is smart enough to handle each one correctly.

    else {
      my %task = ( type => 'Task',
                   WhoId => $result->result->{id},
                   Subject => "Contact blogger",
                   Description => $row->{comment_text}, );
      $sforce->create(%task);
    }
  }
}

As before, we need to create a hash that includes the table type and values for our fields. Then we pass that hash to the Create method. That's the end of our program.

We can run it from the command line by passing in our Salesforce username and password, and blog database name, username, and password.

$ perl sync_blog_leads.pl SF_USER SF_PASS BLOG_DB BLOG_USER BLOG_PASS

Or cron it to run every hour:

00 * * * * /path/to/sync_blog_leads.pl SF_USER SF_PASS BLOG_DB BLOG_USER BLOG_PASS

If you've followed along, you should be able to log in into your Salesforce account and see your new tasks underneath the Home tab.

Task List
Click for a larger view

Now head over to the Leads tab and select All Open Leads. You should see a list of all your new leads.

Lead List

That's all there is to it. You should now have a working Salesforce integration between your blog and your Salesforce account. In the next article, we'll beef up the Salesforce side of the application and then package it up for the AppExchange directory.

Tony Stubblebine is an Internet consultant and author of Regular Expression Pocket Reference.


Return to the O'Reilly Network.

Copyright © 2009 O'Reilly Media, Inc.