Hi All,
The title may be a bit confusing. I believe this needs some explanation, hehe. Thanks ahead of time to anyone that reads!
I have a recruitment system that I built with Code Igniter. Basically it is a database of insurance agent leads. We have several “Marketers” who add leads manually. I also built several email blasts that we send out to a list of insurance agents, the email blasts include a link, once the link is clicked it brings them to a page with a pop-up form to fill out for more information on the product. Right now, once that form is filled out I just have it send an email to me, and we manually disperse each lead to different marketers through the system manually.
My goal is to take the lead from the email form and insert it into my recruitment system database. The only confusing part, that I can’t seem to wrap my head around. Is how do I go about randomly selecting a marketer and assigning it to that person? I have a table called users and in there, there is a column for id. Is there a way, to select all these ID’s and assign each new lead that comes into the system to a different ID each time? I know I could accomplish this by randomly picking an ID that is in the system. But I don’t want to skip any of the marketers and if I’m not mistaken, randomly picking a marketer id could potentially assign a lead to that same marketer twice, rather than go through each marketer and assign a new lead to each one. I would like it to be as fair as possible if that makes any sense?
Sorry if this makes no sense. But I would appreciate any guidance. I’m not asking for the code, just some guidance as I am lost myself!
What about when a new lead comes in, select a list of marketers from the DB, along with a field for their current lead count in ascending order, and pick the first from the list? This way the leads should be distributed equally. If you also sorted on another field (ID, perhaps) once all marketers have been assigned a lead, it should come full circle and give you the first marketer again. That seems like it would ensure a fair distribution.
Great idea! Do you know of any code examples where a process similar to this has been ran? I just haven’t created something like this. Although I believe I know how and where to start, a little push in the right direction can’t hurt.
Going by what I saw of your DB structure in another post, this query should do it I think:
SELECT users.id FROM users, lead_details
WHERE users.id = lead_details.marketer_id
GROUP BY lead_details.marketer_id
ORDER BY count(lead_id), users.id
LIMIT 1
Perfect. Now at the moment actual lead counts are not the same for each marketer. Some marketer may have 5,000 leads and another may have 200. Would it be best for me to just create a new column in the users table, titled something like ‘lead_count’ and just set it at 1 for each marketer? Because the way I see it, I won’t use this lead_count column for anything other than this lead distribution?
I wouldn’t go by a count. You’ve discovered why, and adding a new count would work temporarily but consider what happens there when you add a new user. Plus other issues can interfere such as removing leads or who knows what you’ll want in the future.
If you want to be fair in the dispersing of leads, you’ll want an ordered list of users and a way to know where you are in the list. You do already have an ordered list of users in your users table and I think creating a separate list would not be needed for this task.
So now you just need a way to know who gets the next one. One way would be to have a boolean column such as last_lead or maybe next_lead where only one user at any time would have that set to 1. There are some “gotchas” with this method but you can plan for them. It feels a little dirty to add such a column but maybe it’s fine.
You could also look at the leads to determine who got the last one and go from there, but in the case where leads gets removed, that user gets two in a row. But maybe that’s not an issue for you.
Good points QMonkey, I hadn’t thought through those scenarios. In that case, what about adding a datetime field to the user for ‘last_lead_assigned’ or similar… if you sort the field by oldest first and work through, that should be a fair distribution. Any new marketers would get a zero value (0000-00-00 00:00:00) which would put them next in line for a lead when one comes in.
Thanks a lot for these ideas. I’m going to go with the datetime field and I’ve added a column titled last_lead_assigned to the users table. Now by default everyone is at 0000-00-00 which makes sense. But in my PHP, I’m a bit confused as to how I would start this process of the lead distribution in regards to selecting a marketer to assign this to, since everyone at the moment is at 0000-00-00 I can’t really ORDER BY anything particular? Or can I and am just having a brain fart here? I guess my question now is, how do I get the ball rolling?
SELECT id FROM users
ORDER BY last_lead_assigned
LIMIT 1
You can still order by last_lead_assigned, even though the value will be the same across all rows. As soon as you start assigning leads, those rows will drop to the bottom of the list.
After running with this code. Data is being inserted, however I am for some reason not getting a marketer_id from that sql query. So the marketer_id field has been left empty. Going to keep trying though, thanks everyone for your help!
Well I have it selecting the marketer_id just fine now and it inserts the lead just fine, with a marketer_id however it breaks when the script gets to the UDPATE section. Apparently my syntax is wrong but I can’t seem to spot it. Am I doing something wrong here with the UPDATE? Here is the error followed by my code:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘12:18:29 WHERE id=3’ at line 1
<?php
require_once 'admin/includes/config.php';
if($_SERVER['REQUEST_METHOD'] == 'POST') {
$firstname = $_POST['txtFirstName'];
$lastname = $_POST['txtLastName'];
$email = $_POST['EmailName'];
$telephone1 = $_POST['telephone1'];
$telephone2 = $_POST['telephone2'];
$telephone3 = $_POST['telephone3'];
$date = date("m-d-Y H:i:s");
$lead_type = 46;
$contracted = "Not Called";
$telephone = $telephone1."-".$telephone2."-".$telephone3;
$con = mysql_connect("localhost",$dbuser,$dbpass); //database connection
$sql = "
SELECT id FROM users
ORDER BY last_lead_assigned
LIMIT 1
";
$findMarketer = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array( $findMarketer ))
{
$marketer = $row['id'];
}
// Select the database
mysql_select_db($dbname,$con);
// Define the query
$query = "INSERT INTO `lead_details` (
lead_type,
first_name,
last_name,
phone,
email,
date_created,
marketer_id,
contracted,
last_inserted
)
VALUES (
'$lead_type',
'$firstname',
'$lastname',
'$telephone',
'$email',
'$date',
'$marketer',
'$contracted',
'$date'
)";
$last_lead = "
UPDATE `users` SET last_lead_assigned=".$date."
WHERE id=$marketer";
// Go
$result = mysql_query($query) or die(mysql_error());
$lead_lead = mysql_query($last_lead) or die(mysql_error());
if($result && $lead_lead) {
// If lead was added to database, send notification of lead to marketer.
$to = '';
$subject = 'New Lead - AutoForm';
$headers .= "Reply-To: Free Sales System <>\\r\
";
$headers .= "Return-Path: Free Sales System <>\\r\
";
$headers .= "From: Free Sales System <lead@freesalessystem.com>\\r\
";
$headers .= "Organization: Sender Organization\\r\
";
$headers .= "MIME-Version: 1.0\\r\
";
$headers .= "Content-type: text/plain; charset=iso-8859-1\\r\
";
$headers .= "X-Priority: 3\\r\
";
$headers .= "X-Mailer: PHP". phpversion() ."\\r\
";
/* BCC LIST */
$headers .= 'Bcc: ' . "\\r\
";
/* CONTENT OF MESSAGE */
$body = "Name: ".$_POST["txtFirstName"]." ".$_POST["txtLastName"]."\
";
$body .= "Email: ".$_POST["EmailName"]."\
";
$body .= "Phone: ".$telephone."\
";
mail($to, $subject, $body, $headers) or die ("Failure");
// Redirect to the page, and let lead have access.
header('Location: ./');
// Close the database connection.
mysql_close();
}
else {
echo("Insert Failed");
}
// End Database Insert & Sending of Email
}
?>
I’ve changed up the date variable to no longer include the time and just date and that seemed to allow the script to run. But nothing is happening with the users table. I don’t see the last_lead_assigned column being updated?
Yeah, that is what I had just changed. But it still seems to run through the script just fine, inserts the lead, but doesn’t seem to update the users table. Very odd.