Technistas

As most of my readers know, I like to write about work-related items – a webinar I’m hosting, a review of an article that is relevant to my day-to-day work in the software business, or some such thing. But every now and then I need to remind myself of what got me into this business in the first place – a sheer love of figuring out how to program something.

Last week, the something was Salesforce.com, the leading customer relations management software product on the web. We use Salesforce at the company I work for (OpenMake Software, Inc.) to help track how we communicate with customers and prospects. One of the cool things that Salesforce lets you do is to send out an email to a large list – no, not spam, but legitimate communications to people who have agreed to hear from you. As anyone who has tried to send out a mass mailing knows, all email lists are dirty – meaning, there are addresses on the list that are no longer valid and that result in a bounced email. So my task was to figure out a way to programatically set the email ‘opt out’ bit on the associated record in Salesforce.

Salesforce has an excellent web services API that gives you complete access to the underlying data model. I decided to tackle the problem using my favorite Swiss-army bazooka, Perl, along with a Perl module that I found on the internet called ‘Salesforce’. Once I installed the Salesforce module, the code was pretty easy to write.

The program assumes that you have a text file that contains a list of the email addresses that have bounced, separated by a newline character. Here’s the code:

use Salesforce;
use Getopt::Long;

# Gather command line parms
my ($user, $password, $bounceFileName, $update);
my $result = GetOptions ("user=s" => \$user,
			"password=s" => \$password,
			"bounceFileName=s" => \$bounceFileName,
			"update" => \$update);

# Set up the SOAP service and login
my $service = new Salesforce::SforceService();
my $sf = $service->get_port_binding('Soap');
my $login_ok = $sf->login('username' => $user, 'password' => $password);
die "Bad login" unless $login_ok;
print "Login OK";

# Loop through the bounce file and process each bounced email address
open (BOUNCE, $bounceFileName) || die "Can't open file: $bounceFileName";
while (<BOUNCE>) {

	# Get the lead from SF
	chomp($_);
	my $leadEmail = $_;
	print "\nProcessing lead email: $leadEmail ";
	my $result = $sf->query('query' => "select id, hasoptedoutofemail from
                                             lead where email='$leadEmail'",
                                             'limit' => '1');
	my $leadID = $result->result->{'records'}->{'Id'}[0];
	my $hasOptedOut = $result->result->{'records'}->{'HasOptedOutOfEmail'};
	print "Lead id is: $leadID. Opt out status is: $hasOptedOut\n";

	if ($update) {
		my $lead = $result->result->{'records'};
		$lead->{'Id'} = "$leadID";
		$lead->{'HasOptedOutOfEmail'} = 'true';

		my $updateresult = $sf->update(%$lead);
		my $updreshash = $updateresult->result;
		print "Update status: $updreshash->{success}\n";
	}
}
close (BOUNCE);

1;

As you can see, the code is pretty simple. In the initial block, we use the standard getopt functionality to get the name of the bounce file, along with the Salesforce userid and password. One comment about the password, you need to append your Salesforce security token to your standard Salesforce password – this is a standard technique in Salesforce and in many other cloud computing environments. We also set an ‘update’ flag – if it is set, we will not only query the database for the existence of a bounced email record, but we will also attempt to update the opt out bit.

The next block sets up the Salesforce SOAP service and binds to it. Once this is done, you are ready to make queries and update the Salesforce database. Salesforce uses the SOQL query language to interact with its database. In the code, we first open up and then iterate over the bounce file. Each time through the loop, we perform a query to pull the record associated with the bounced email address from the Salesforce database. If the update flag is set, we then prepare the record (as a Perl hash) for update and use the ‘update’ method to push the change to Salesforce.

I found working with the Salesforce API to be fairly straightforward. The only hard part was figuring out the exact format of the records that come back from Salesforce – in some earlier versions of the code, I just used Data::Dumper to peek inside the SOAP envelopes to determine the structure. All in all, it was a few hours of learning the API and the data formats, with the end result being a functional and useful piece of code.

Hope this helps, and happy coding!

- Matt