Monday, August 31, 2009

Script: SugarCRM Click-Thru Target to Lead in Email Campaign

A quick post here about a little script I wrote for SugarCRM.

Update: I forgot to mention that I am working with SugarCRM version 5.2.0i here. Results may vary (read "not work") with other versions.

We were having trouble with the workflow process of running an Email Campaign in SugarCRM. Namely, we had like 100 some clickthru's in an email campaign, and I really wanted to just click a button to make all of those clickthru targets become leads. I would have even settled for the click-thru list being clickable links and doing it one-by-one (well, i would have had the marketing guys do that), but even that is not possible. They are not clickable links that redirect you to the target details view - instead, they are just a static list with only the related hyperlink being clickable.

Details of the problem here: http://www.sugarcrm.com/forums/showthread.php?p=181660#post181660

So, I wrote a php script to solve our dilemma:

<?php
/**
* A temporary method of generating GUIDs of the correct format for our DB.
* @return String contianing a GUID in the format: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
*
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
* All Rights Reserved.
* Contributor(s): ______________________________________..
*/
function create_guid()
{
$microTime = microtime();
list($a_dec, $a_sec) = explode(" ", $microTime);
$dec_hex = dechex($a_dec* 1000000);
$sec_hex = dechex($a_sec);
ensure_length($dec_hex, 5);
ensure_length($sec_hex, 6);
$guid = "";
$guid .= $dec_hex;
$guid .= create_guid_section(3);
$guid .= '-';
$guid .= create_guid_section(4);
$guid .= '-';
$guid .= create_guid_section(4);
$guid .= '-';
$guid .= create_guid_section(4);
$guid .= '-';
$guid .= $sec_hex;
$guid .= create_guid_section(6);
return $guid;
}
function create_guid_section($characters)
{
$return = "";
for($i=0; $i<$characters; $i++)
{
$return .= dechex(mt_rand(0,15));
}
return $return;
}
function ensure_length(&$string, $length)
{
$strlen = strlen($string);
if($strlen < $length)
{
$string = str_pad($string,$length,"0");
}
else if($strlen > $length)
{
$string = substr($string, 0, $length);
}
}
function microtime_diff($a, $b) {
list($a_dec, $a_sec) = explode(" ", $a);
list($b_dec, $b_sec) = explode(" ", $b);
return $b_sec - $a_sec + $b_dec - $a_dec;
}
?>
view raw create_guid.php hosted with ❤ by GitHub
<html>
<head>
<title>ProspectToLead - Campaign</title>
<style type="text/css">
body { font-family: Consolas, Monaco, Courier New, Courier }
.error { color: red; font-weight: bold }
.finished { color: green; font-size: larger }
</style>
</head>
<body style="width: 1000em">
<?php
if(!defined('sugarentry'))define('sugarentry', true);
require_once('create_guid.php');
// database
mysql_connect("localhost", "sugar_user_username", "sugar_password") or die(sterr(mysql_error()));
echo "connection to the server was successful!<br/>";
mysql_select_db("sugar_database") or die(sterr(mysql_error()));
echo "database was selected!<br/><br/>";
// you need to change these to match an existing campaign, and a user (probably yours)
$campaign_id = "314fe2d3-3004-e1d2-9b13-4a8ee361578e";
$user_id = "43188444-f11d-9b8f-46a5-460bd7368e24";
// Select Prospects from the campaign that have click-thrus and are not yet converted to leads, including number of hits
$qry = mysql_query("SELECT p.*,prospects_cstm.*, c.hits FROM campaign_log c
LEFT JOIN prospects p ON c.target_id = p.id
LEFT JOIN prospects_cstm ON p.id = prospects_cstm.id_c
WHERE c.campaign_id = '{$campaign_id}' AND c.target_type = 'Prospects'
AND c.activity_type = 'link' AND c.deleted=0
AND p.deleted=0 AND p.lead_id IS NULL") or die(sterr(mysql_error()));
echo "Prospects to Convert: " . mysql_num_rows($qry) . "<br /><br />";
// Process each Prospect
while($prospect = mysql_fetch_assoc($qry))
{
// pull email addresses for prospect
$qry_email = mysql_query("SELECT ea.*, ear.* FROM email_addresses ea
LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id
WHERE ear.bean_module = 'Prospects'
AND ear.bean_id = '{$prospect['id']}'
AND ear.deleted = 0
ORDER BY ear.reply_to_address, ear.primary_address DESC") or die(sterr(mysql_error()));
$lead_id = create_guid();
$now_str = date("Y-m-d H:i:s");
// Create LEAD from PROSPECT
$qry_lead_insert = mysql_query("INSERT INTO leads set id='{$lead_id}', date_entered='{$now_str}', date_modified='{$now_str}',
modified_user_id='{$user_id}', created_by='{$user_id}', deleted='0',
description=" . nov($prospect['description']) . ", assigned_user_id=null,
salutation=" . nov($prospect['salutation']) . ",
first_name=" . nov($prospect['first_name']) . ", last_name=" . nov($prospect['last_name']) . ",
title=" . nov($prospect['title']) . ",
department=" . nov($prospect['department']) . ", do_not_call='0',
phone_home=" . nov($prospect['phone_home']) . ",
phone_mobile=" . nov($prospect['phone_mobile']) . ",
phone_work=" . nov($prospect['phone_work']) . ",
phone_other=" . nov($prospect['phone_other']) . ",
phone_fax=" . nov($prospect['phone_fax']) . ",
primary_address_street=" . nov($prospect['primary_address_street']) . ",
primary_address_city=" . nov($prospect['primary_address_city']) . ",
primary_address_state=" . nov($prospect['primary_address_state']) . ",
primary_address_postalcode=" . nov($prospect['primary_address_postalcode']) . ",
primary_address_country=" . nov($prospect['primary_address_country']) . ",
alt_address_street=null, alt_address_city=null, alt_address_state=null, alt_address_postalcode=null, alt_address_country=null,
converted='0', refered_by=null, lead_source='Campaign', lead_source_description='{$prospect['hits']} ClickThru',
status='New', status_description=null,
account_name=" . nov($prospect['account_name']) . ",
contact_id=null, account_id=null, opportunity_id=null, campaign_id='{$campaign_id}';") or die(sterr(mysql_error()));
$website_c = nov($prospect['website_c']);
if ($website_c != "null") {
$qry_lead_cstm_insert = mysql_query("INSERT INTO leads_cstm set id_c='{$lead_id}', website_c={$website_c};") or die(sterr(mysql_error()));
}
while($email_address = mysql_fetch_assoc($qry_email))
{
// process each email address
$email_addr_bean_rel_id = create_guid();
$qry_email_addr_bean_rel_insert = mysql_query("INSERT INTO email_addr_bean_rel set id='{$email_addr_bean_rel_id}',
email_address_id='{$email_address['email_address_id']}', bean_id='{$lead_id}',
bean_module='Leads', primary_address='{$email_address['primary_address']}',
reply_to_address='{$email_address['reply_to_address']}',
date_created='{$now_str}', date_modified='{$now_str}', deleted='0'") or die(sterr(mysql_error()));
}
// Set the Lead_ID for the Prospect, so Prospect says it is "Converted to Lead" in the details
$qry_prospect_update = mysql_query("UPDATE prospects SET date_modified='{$now_str}', modified_user_id='{$user_id}', lead_id='{$lead_id}'
WHERE ID = '{$prospect['id']}'") or die(sterr(mysql_error()));
// get the target_tracker_key for this prospect
$qry_tracker_key = mysql_query("SELECT target_tracker_key FROM campaign_log
WHERE campaign_id='{$campaign_id}' AND target_type='Prospects' AND target_id='{$prospect['id']}' LIMIT 1") or die(sterr(mysql_error()));
$campaign_log_ttk = mysql_fetch_assoc($qry_tracker_key);
// Campaign Log Entry
$campaign_log_id = create_guid();
$qry_campaign_log_insert = mysql_query("INSERT INTO campaign_log SET id='{$campaign_log_id}', campaign_id='{$campaign_id}',
target_tracker_key='{$campaign_log_ttk['target_tracker_key']}', target_id='{$lead_id}', target_type='Leads', activity_type='lead',
activity_date='{$now_str}', related_id='{$prospect['id']}', related_type='Prospects', archived='0', hits=0, deleted='0', date_modified='{$now_str}'")
or die(sterr(mysql_error()));
echo "Converted {$prospect['last_name']},{$prospect['first_name']}...<br/>"; // next Prospect
}
echo "<br/><br/> <span class='finished'>Finished!</span>";
// utility functions
function nov($strval) {
if ($strval == "") return "null";
else return "'" . mysql_real_escape_string($strval) . "'";
}
function sterr($strval) {
return "<span class='error'>{$strval}</span>";
}
?>
</body>
</html>


Be sure to change the Database user/password/name to match your configuration, and to change the
$campaign_id and $user_id
variables at the top of the file.

4 comments:

ipad case said...

YES! That is extremely helpful , actually is really detailed. i will be using this as well. Thanks for sharing !

Abdur Razzaq said...
This comment has been removed by a blog administrator.
Simon Earl said...

Hi Adam,
Thanks for that, it seems our organisation is trying to do exactly what yours is....so thank's very much for the script, we're using 6.5.18 so I'll adapt the script if it needs it.....but what I'm wondering about is....are we both doing something SugarCRM is not designed to do ?
For me, (and presumably you as well) the business logic works....I'm just surprised that nobody else seems to have this issue ?
Again, thanks for the detailed post
Cheers
Simon

Unknown said...
This comment has been removed by a blog administrator.

Disqus for A Nofsinger's Blog