Routing Calls based on Area Code

[pete_chirpich]

I would like to route calls to certain agents based on the area code of the caller. I assume I will need a database file to reference the calling party number to. Then filter on the area code, and router accordingly. I have never made a script look into a database. I would use a separate SQL database server for this.

I can handle the routing part once I know if the number matches. I just need some tips on getting to the database correctly.

Am I heading in the wrong direction? Should it be an XML file instead? Any advice or sample scripts would be appreciated.

[JimSJ]

My advice is, don't do it. See this link to a previous discussion on doing this with 3.1, but it's the same idea for 3.5. The problem isn't the database dip, its routing to the specific agent. You could maybe just do Call Redirects, but it will be a problem if you want to distribute to specific agents with a Select Resource (and be able to do enterprise data/macros/accurate reporting/etc).

http://forum.cisco.com/eforum/servlet/NetProf?page=netprof&type=bookmarks&CommCmd=MB%3Fcmd%3Dpass_through%26location%3Doutline%40%5E1%40.eea1ffa

Based on the experience of the author of that post with developing the solution and my own with maintaining his solution, you really don't want to do it. (Note: not saying his solution was bad, just really difficult to maintain, given the requirement of area code routing).

I've heard rumors that IPCC 4.x due out this summer may address this scenario better, perhaps if a Cisco representative reads this, they may be able to address that.

Jim

[pete_chirpich]

I understand. However, I fortunately (or unfortunately) already have separate CSQ’s for each agent, used in another script. So I am not concerned about the management piece. Just getting the correct data from the db. Any other thoughts?

[joe.conger]

I've always just let the carrier do the area code routing for me. The carrier can send you different DNIS information based on the area code of the caller.

[JimSJ]

First, verify that you have a version of IPCC Express that will support database dips (Enhanced w/ CTI port option or Premium I believe). Setup a system DSN on the IPCC pointing to the appropriate database server. Add a datasource on the IPCC under subsystems/ database.

Verify that after the configuration, your database subsystem is in service. It will likely be out of service or in partial service if either the system DSN is incorrect or you have a bad username/pw under subsystems/database/datasource configuration. Once you do, startup the script editor, do a Get Call Contact Info step to get the calling number (caller ID). Do a Java method to get the first 3 digits if the total length of the callerID variable is 10 (in other words, if you get caller ID from the caller).

This will take two execute java methods. The public int length() will return the length of a string variable in an integer. Do the if against that integer variable. Then do an execute java method against the calling number string variable with method public String substring(int,int). Argument #0 should be 0, Argument #1 should be 3. Argument #0 indicates the starting position, Argument #1 indicates how many digits to pull, so you're telling it start at the first digit and only take the first 3 digits to assign to your strAreaCode variable.

Now you are ready to do the database dip. We assume that you already have a table setup with every area code and the corresponding agent assigned to each. Data type for the columns should probably be varchar unless there's some special reason for something different. Depending on the rest of the logic of your call routing, you may want to also have a column in the database for fallback CSQ in case agent for area code 000 is unavailable. For example, if they're in sales, you may want a column for sales if you also have a sales csq so they can be queued there if your designated person is unavailable.

Do a DB Read first with a statement like this:

SELECT * from areacodeTable where areaCode = $strAreaCode

Under the Successful branch, place a DB Get where you assign results of the query to variables. For example, you may want to assign the agent ID from the query to a variable called strAgentID. Then, let's say your individual queues are all in format CSQ_agentName. Do a set to add CSQ_ to the strAgentID (ie set CSQ = "CSQ_" + strAgentID)that you just pulled from the DB. Then do a select resource to queue it and you're good.

The following link is for 3.0(x) but the steps should be the same for developing scripts that access databases.

http://www.cisco.com/en/US/products/sw/custcosw/ps1846/products_programming_reference_guide_chapter09186a00800c49d5.html

Finally, make note of the maximum supported number of triggers per hardware platform. You can exceed them, but if you're over the cisco noted maximum, TAC support may become a concern.

Is this what you were looking for?

Jim

[pete_chirpich]

Jim, thank you for the message! This is what I was looking for. My one fear is the database side. We have database people here, so I am hoping they can tell me the correct way to setup the database, and the query part (or use yours). Then i should be set.

If there are any gotchas to setting up the SQL database, can you let me know? My thought was to just setup an area code file, and an associated rep name in another field. If the query finds the area code, then it can pull from that line the rep and always we go!

In response to the previous post to have the telco do it: I cannot do that as this is one toll free line used for other options on the main menu - redirect to sales person, accounting, etc. If it was a toll free for just customer service, then I really like that option. Thanks!

[pete_chirpich]

Jim, I got the script to work. I am having difficulty getting the strAgent to work with a database length of 10. Some agents names are 3 characters, some 10. I am trying the sql RTrim but not having luck. I'll get it. The part I stumbled on is the java section. Is it possible for you to show me how to trim that area code number off, with the code? Or is that not something people like to give out? I will be asking a co worker who supposedly knows java, but thought I would ask.

Right now, I just bypassed that part with a variable so I could test the db dip.

[JimSJ]

Sure. First, get the calling number into a string variable by creating a string variable, we'll call it strCallingNumber with the Get Call Contact Info. Now we have the caller ID (or null if their caller ID is blank). The next step is to see how long the string variable is. We want to see if it is 10 digits long so that we'll know if it is valid caller ID and whether we should try to route by area code. To find out if its 10 digits do this...

Put an Execute Java Method step from under the JAVA folder in the script. Go to properties of the step and under select variable, choose strCallingNumber. Then click the Explore Class Information tab. From the Method drop down menu, select public int length() . In the area at the bottom next to Assign to Variable, select an integer variable that we'll assume is called intLength.

Now the intLength variable contains the length of the calling number. So let's do an if statement and say if intLength == 10. If its true, we'll do area code routing, if its not, we'll just queue it in a general queue right? We only want to area code route if we have valid 10 digit caller ID w/ the area code included.

Now we need to find what the first three digits are if it is valid caller ID. To get these three digits, do this:

Assign another string variable and let's call it strAreaCode. Put another Execute Java Method step in the script. On the first screen, select strCallingNumber for Select Variable. Then go to the Explore Class Information tab. Under method, select public String substring(int,int). Double click argument #0 and put in 0 for the value. Next double click argument#1 and put in 3 for the value. What this will do is say "Take the caller ID which is stored in the strCallingNumber variable, grab a certain section of it, and assign this other section to another variable." The 0 and the 3 indicates what the section to assign will be. The 0 means to start at the first character of the string and the 3 means from the first character, select the next 3 characters. So for 8005551212, this will say start at 8 and select 3 characters, so it will end up as 800. Now after you've done that, go to Assign To Variable and choose the strAreaCode. Poof! We now have the area code in a form we can use.

Now throw the $strAreaCode into your select statement and you're good.

Also, there is a trim function built into the editor. See the public String trim() method under the drop down menu of Execute Java Method. Generally easier to do the trim there than in SQL.

Good luck,

Jim

[pete_chirpich]

Pretty darn cool! That wasn't so painfull! Everything works as stated. I just need to go through and make sure my bases are covered for error - typos in agent name, no area code, caller id block, etc. Thank you so much for showing me the way!

[JimSJ · Accepted solution]

No problem. Remember all the nifty tools in Execute Java Method later and I think you'll find it quite handy in many scenarios.

By the way, remember if you find a post is helpful or solves your problem, you should give it a rating :)

Thanks,

Jim

[pete_chirpich]

I updated the call with my vote. Thanks for the reminder. I am now stuck on a select resource issue. I enter my csq_names in the sql database. It matched the area code to the agent name fine. However, for testing I purposely mis-spelled an agent name. The script still runs, and tries to send the call the CSQ_Nam. That queue does not exist. (CSQ_Name does). So I get the system message “We are sorry, we are currently experiencing system problems”. I have tried the select resource/Selected/Connect/Failed routine. I tell it to dequeue, then set my csq to a known good queue name, a “general” queue. Still the same message. Anyone have advice on getting around this? I have set my “select resource” to connected yes and no, and it errors either way.

I could probably do something in the database to make sure they enter the correct name in the field. I would rather not lose calls due to some typo though!

[JimSJ]

I think the reason your attempt to catch the problem with a dequeue or under the connect/failed branch is that you're throwing the error immediately at the Select Resource step, so anything you do under there is not executed, the call is immediately sent to the system failure message.

One way you might be able to run a verification (although its not very elegant) is this...grab the agent from your database dip referencing the area code. Have a separate table in your database that just lists valid agents. Do another DB Read with something like

select * from agentTable where agentID = $strAgentID

Put a DB Get under the Successful branch. Don't do anything under DB get Successful (continue routing to the specific agent) but under the No Data branch, send the script to a general queue. In this way, if you get a match, it routes to that agent, but if what you pulled from the area code table doesn't match your list of valid agents in the other table, it gets routed to a general queue.

You could do all this, but I would say that you're probably better off just being careful about how you manage access to the area code table and verify that the entries are valid after any update.

Good luck,

Jim

[chris.mott]

I know this is late, but I just caught it looking for something else. You can also perform this task using a much easier way ... use an XML file! I did this for another customer who had inbound call routing to different queues based on area code, so we developed an XML file with the format and ... I then did a call to the XML file with the area code stripped from the ANI, got the queue info, then jumped there based upon the lookup ... no databases, no client issue with upgrading to a DB-capable version ... if there is interest on this, I'll post the specifics.

[pete_chirpich]

I would be interested in seeing the XML. I have a holiday lookup that checks an xml file, but not sure how a new one should be formatted. Thus far the db seems to be working. I need to find a way to change the data. Our reps change who is responsible for different area codes, so I need to then change each field in the db.

If you would like to post an XML I would love to try something simple!

[chris.mott]

Check this ...

-

-

HOB

KDB

RDC

-

HOB

KDB

RDC

-

CHF

MOF

CAS

The call in your script opens this file, "zipcodes.xml" and, depending on the ZIP code, retrieves the contents of the tags into a string, which you then leverage for call routing. I cannot access those files now, but I might can arrange it if you need the snippet.