Monday, October 22, 2012

How to bulk update deals in Highrise HQ

One of the fun things about being a technology entrepreneurs is that occasionally I encounter business problems that give me a chance to touch code. In this case I had to update a bunch of Highrise HQ deals.

The business problem: At Telestax, we had a great first year, which we celebrated at the recent Mobicents Community Summit 2012 in Rio De Janeiro.  One of the decisions we made at the Summit was to grow the sales team. We use HighriseHQ and until now we've used it in a loosely organized fashion. The result of course was a messy pile of sales data.

Lesson learned. We've created a list of discovery questions, improved our data sheets, polished the offerings documents and created best practices for the sales process. Now we can pass on the knowledge to the new members of the sales team. But before we do that, we need to clean up the old mess. For example we need to organize deals in meaningful categories, remove outdated tasks, and establish proper visibility levels.

The technical challenge: HighriseHQ has a nice search UI, and intuitive create/update UI for individual entries, but it doesn't offer more advanced features such as bulk updates. At least I couldn't find them. This is probably by design as 37Signals is known for keeping things simple.

The solution: Fortunately there is a nice, well designed REST API that can be used with command line tools to get the job done. The task of updating a lot of leads takes a few command lines and a little scripting. In the example below, we are updating the visibility of all existing leads to a sales user group.

First let's get a list of all deals in an xml file:

curl -u 6006c09437e0e1fde:X https://telestax.highrisehq.com/deals.xml > all-deals.xml

Then list the groups so we can see the integer ID of the group we want to apply to all deals:

curl -u 6006c09437e0e1fde:X https://telestax.highrisehq.com/groups.xml

The result would have a section that looks similar to this:


  <group>
    <id type="integer">437336</id>
    <name>Sales Account Managers</name>

We now have enough information to process. The next step is to write a script that creates update commands. We will use a simple XSLT stylesheet for the job:


<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/deals">
      <xsl:for-each select="deal/id">
curl -u 6006c09437e0e1fde:X -X PUT  -H 'Content-Type: application/xml'  -d '<deal><visible-to>NamedGroup</visible-to><group-id type="integer">437361</group-id></deal>'   https://telestax.highrisehq.com/deals/<xsl:value-of select="text()" />.xml
      </xsl:for-each>
</xsl:template>
</xsl:stylesheet>


The stylesheet above simply iterates over all deals in an xml file, extracts each deal ID and places it in a curl command that updates the visibility of the given deal to the group ID of Sales Account Managers. Let's name the XSLT file deal-visibility-update.xslt and run it with the xsltproc command line tool found on most Linux and OS X machines.

xsltproc deal-visibility-update.xslt deals-all.xml > deals-update.sh

The output of the command goes to a shell script file that's almost ready to run. We just need to remove the first line, which is an xml heading printed by default by xsltproc.

<?xml version="1.0" encoding="UTF-8"?>

The rest of the deals-update.sh file is a long list of curl commands printed by the xslt script. Let's run it.

bash deals-update.sh

It takes a second or two for each deal to be updated, because each curl command is a remote http invokation to the HighriseHQ server. It would have been a lot more efficient if we could run a bulk update query on the HighriseHQ server. However I am not complaining because this is not a daily need and its a whole lot faster than using the UI to update each deal. In fact I started that route and after twenty minutes of clicking decided that its going to take hours before I can make significant progress.

So here we go, in a couple of hours total, I managed to solve a business problem, play with code and blog about it.

If you know of more efficient ways to get the same job accomplished, please share in the comments section. Thank you in advance!