Running for Portage

When you have children, you hope and pray that everything will be ok. That they will be born healthy and grow up just like everyone else.

When our second daughter, Imogen, was born that’s exactly how we felt. But when Immy was about 1 year old we realised that she was having significant difficulty sitting up, walking and talking. Nagging doubts turned into fully blown concerns and soon we were being referred to paediatricians and attending medical appointments to see what might be going on.

We were worried sick. Would she ever walk and talk? Would she always need extra support in life? Is this because of something we’ve failed to do as parents? We’re not the most laid back of parents anyway… this stressed us out more than we ever thought possible.

But nobody can give you an answer. There was no obvious cause. All we could do was wait and see.

Amidst this anxiety, we started to be visited by “Su” from a charity called Portage. Su was amazing. She came every week for an hour and worked with Imogen to develop her abilities. While the doctors, physios and speech therapists could only visit occasionally. Su was there every week, without fail, to improve Imogen’s skills, give us advice and help reassure us that it would be ok.

That helped a lot.

Imogen, big sister Hannah, and Su from Portage

Imogen, big sister Hannah, and Su from Portage

Imogen is 3 now, she’s walking a lot more steadily and she’s starting to say things that almost sound like words you would recognise. She’s still very behind but she’s doing ok. She’s also one of the happiest and most affectionate children I’ve ever known. We have a laugh together, me and Imms.

Imogen Imogen & Dan

But I’d like give something back to Portage for helping us out as a family over the past year or two. The’ve helped us so much it’s the least I could do.

So I’ve decided to run the Bupa Great Birmingham Run this October to raise money for the National Portage Association. I’ve never run a half marathon before. Actually, I’ve never run more than 6k before. It’s going to be a bloody hard work! (You can follow my training here). This is what I looked like after that last run:

Dan after running

As you can see, I’ve a long way to go. But here’s the thing: if you sponsor me, you’ll not only be helping more families can get the kind of support that we’ve been so fortunate to receive, you’ll also motivate me to keep going!

Please help me to support this charity by visiting my Just Giving page and making a donation.

JustGiving - Sponsor me now!

Thank you.

I'd love to meet you on twitter here.

Most People’s Approach to Delegation is Wrong

In his biography of Steve Jobs, Walter Issacson tells the story of how Jobs used to go down to Jonny Ive’s design department most Wednesday afternoons to go over prototypes and review proposals. Most people hear that story and think, “Wow, Steve Jobs really focused on design and built the most valuable company in the world as a result, we should focus on design too”.

But, what I find fascinating about that story is not the apparent importance of design, it’s the fact that the CEO of a major international corporation was able to carve out so much of his week, on a regular basis, and the company kept running just fine. Most high level bosses I know spend all their time fire-fighting, and spending half a day a week on something like that seems out of the question. Steve Jobs must have been an excellent delegator.

Most training on delegation starts and ends with the Eisenhower Decision Matrix, which basically says that you should give your urgent but unimportant tasks to someone else. That may be true, but most of the over worked people I know are overloaded with tasks they consider urgent and important. What should they do?

Don’t Delegate, Systemize

When things get too busy, I think that’s a sign that you need to move one layer of abstraction up from where you are now. If your job is writing news articles, you need to become the person who edits news articles. If your job is coding websites, you need to become the person who manages the website coders, or the person who writes a content management system. If you cook meals, you need to become the person who designs the menus and trains the other chefs.

This is not easy. There’s no easy to remember system for doing this. But here are a few suggestions on how to approach it.

You’ll need to start by being really really good at what you do. Then you need to try and break down what you do into small components. Can you identify patterns in what you do? Can you write down your thought process and explain it to others? Can you automate some parts of the process? At first this would seem artificial and slow, “it’s quicker if I just do it myself” you might say. But only then can you start handing out parts of what you do to others and scaling up the whole operation.

You won’t be able to delegate everything. But what you’re left with is your personal USP (unique selling point). It’s the bit that you and only you can do. In Steve Job’s case it seems he kept hold of the one part of the creative process that he couldn’t systemize: Good Taste. He’s famous for destroying people publicly if they produced work he didn’t think was up to much. But while his methods may have been cruel and probably unnecessary, the systematization of Apple’s design process clearly worked because it produced good results across the board, with a steady flow of beautifully designed products coming out of Apple over the past 15 years.

I'd love to meet you on twitter here.

Founder Meetups in Birmingham

Startup founders can often become quite isolated. That they are required to appear optimistic and up beat to employees, investors, potential customers and the press. Meanwhile their family and friends probably have no experience of starting their own business and, as much as they may want to, they cannot relate to many of the challenges that founders go through. Which leaves startup founders with very few opportunities to connect with others they can relate to. Sometimes, you just want to admit that you have no idea what you’re doing and you’re not sure why you go into this anymore, without people freaking out.

Last week I had a number of conversations with some founder friends, many of whom said it would be great if there was a forum where they could be open about this sort of thing, relate to others in a similar situation, get some advice and maybe get some accountability too. Which got me thinking. What sort of format would fill this gap?

Here’s what I came up with…

Key Principles

  • Trust: members of a group must, at all times, feel that they can trust the other members of that group.
  • Equality: outward signs of success can often be misleading or temporary. Your apparent success or failure in business does not make your participation at the group any more or less valid.
  • Openness: if you want others to share, you have to lead by example. Sitting back and staying quiet while others talk openly is unfair.

The Rules

  • Group size should be between 4 and 8, if a group grows larger than that it should make arrangements to divide in two. Groups smaller than that should recruit new members or merge.
  • Membership is invite only
  • Members must hold at least 5% equity, or have been an original founding member, of a business that they still manage.
  • New members of a group will have a 6 meeting probation period. After which time they must be unanimously and anonymously approved as full members
  • Each group has a chairperson, they are responsible for arranging and facilitating the meeting
  • The position of chairperson should rotate every 6 months.
  • Each meeting will consist of members updating the group on what they’ve been doing, their thinking on key issues and their current state of mind. Members may ask questions and have a discussion in response to each person’s update. The chairperson will ensure everyone gives their update. The final 15 minutes will consist of each member stating what they intend to do before the next meeting. The chairperson will record this and send round an email summary. In the spirit of openness, each member must give an update and say what their objectives are at every meeting they attend.
  • No two members of the same group can be from the same company.
  • Everything discussed at the meeting is strictly confidential. Even the broad subject areas or themes.
  • If members feel they cannot continue to be in the same group as a particular individual they must raise it with the chairperson and the chairperson must discuss the matter individually with others in the group. If 1/3 or more of the group feels the same way then the chairperson must ask the individual in question to leave.

Not rules, just suggestions

  • Meet every two weeks, at a meeting room or cafe
  • If things go well we might invite the occasional guest speaker ­ someone who had founded their own company, obviously.
  • While equality is a key principle, when groups divide in two we might like to put people at similar stages of business in the same groups so that we don’t end up with massive differences in scale.

What Happened Next

I showed what I’d drafted to a number of the entrepreneurs who had given me the idea, and they all seemed keen on attending something along these lines. So next week, the first meeting will take place. We’ve already had eight people sign up so we’ll have to think about dividing into two groups pretty much straight away.

If you’d like to start a similar group with these rules then I encourage you to do so – let me know if you do and how it goes.

I'd love to meet you on twitter here.

Where Customer Service Goes to Die

“Sorry to bother you…”, the email from my client began. It was Monday morning, and I knew the company this person worked for always had conference calls with their customers on a Monday. No doubt she was prepping for a difficult one.

The email went on, “but could you help us with the query below?”

The email chain that followed contained a discussion between my client and one of their customers about an obscure feature on one of their enterprise software products. The customer didn’t like the way it worked and was clearly trying to paint this as a bug rather than a feature. I had recently done some unrelated work on that product at the request of that same customer, and I could tell they were gearing up to say, in effect, “We’re not going to pay for those changes because the product still has this ‘bug’ and we want you to ‘fix’ that too.”

This is a classic manoeuvre in enterprise software support. The customer identifies a broad or complex problem. They propose a small change to solve that problem. Then, once there are people working on it, they conveniently forget about the specific change they requested and insist more work be done because their broad problem is not solved yet.

This sort of thing happens frequently enough to convince me that it’s a deliberate ploy by many corporate managers. The only way to prevent doing work for free as a software vendor is to employ people to keep a very close eye on the scope of every change and argue back with the customer if they try this kind of thing.

A customer service expert might suggest spending more time talking to the customer about their broader problem, and seeking to solve it all from the beginning. Sometimes that works, sometimes that just looks like up-selling and the customer rejects it.

The real issue is that enterprise software exists mostly to join all sorts of disparate parts of an organisation together. It’s plumbing, and plumbing is messy. Plumbing needs continual patching up and tweaking. It’s an ever moving target. It’s also very political. It’s hard enough getting several departments to agree on how they’re going to work together, by the time they’ve agreed a process, getting that implemented in software is the easy bit!

So the companies that make enterprise software spend their days locked in conflict with their own customers trying to keep a lid on all the complexity arguing about who’s going to pay for all the changes they keep having to make. Enterprise software is where customer service goes to die.

And any potential way to improve the level of service you can give effectively amounts to, not selling enterprise software any more. This includes approaches like:

  • Simplifying what you offer
  • Focusing on doing one thing really really well
  • Trying not to get drawn into customers’ complex inter departmental problems
  • Trying to have more customers so you’re not so beholden to the few that you do have
  • Saying no when customers ask for complex things
  • Charging for your time rather than for the software

If your business is enterprise software then I feel bad for you. Fortunately there is hope. The oncoming tidal wave of SaaS applications is making enterprise IT more a matter of composing disparate services rather than building or buying large behemoth systems. Over time, most business functions are becoming productised and then owned by 3 or 4 dominant SaaS apps.

Need content management? Try WordPress, Joomla, Durpal or Umbraco. Need CRM? Try Salesforce, Sage or Highrise. Need to send lots of email? Try Mailchimp, Constant Contact, Sendgrid or Campaign Monitor. All of these products offer some customisation, but they differ from old style enterprise products in that they can be setup and configured by users in a browser. Somehow the vendor has hit that sweet spot of product positioning, sensible defaults and customisation capability and as a result they’ve carved out a market for themselves without armies of implementation experts and customisations.

If you’re selling enterprise software, my advice is to stop. Your industry will eventually be eaten by a few major SaaS vendors. So either carve out a niche and become one of those vendors, or switch to making a living gluing things together for a daily rate. Your customers will be happier. You’ll be happier, and you can stop wasting your Mondays arguing over edge cases and who’s funding the ongoing cost of tweaking things.

I'd love to meet you on twitter here.

The Fear that Drives Me On

I built a thing, but it didn’t work out. Plenty of people said the need was real, I got hundreds of people to come along and try it, quite a few of them said they loved it, but virtually no one continued to use it more than 48 hours after signing up, and pretty much nobody upgraded to a paid plan.

During the process, I talked to a lot of the users, and I listened for any potential crossover between what I’d built and an actual problem they needed solving.

After listening for a while, I formulated a plan. A pivot, if you will. Rather then building something that you had to import data into, I would take the interface that people said they liked and make it connect onto existing sources of data. I set about building that.

Step 1, fundraising. In the final months before version 1 came out I had been full time on the project, spurning any consulting work that might come my way. But the money was now running out and I needed a paying gig. Fortunately, when I phoned a former customer, they were happy to give me some more work.

Step 2, coding. This was familiar ground. I’d been here before, spending every spare moment trying to move the product forwards. It’s draining looking at code all evening after a full day at a customer’s office, but you believe in what you’re doing, so you press on. This time, you tell yourself, it’s going to work.

Step 3, shipping. This is where I’m at right now, and it’s hard. Not hard like A level maths, hard like getting dumped by your girlfriend. You start to put yourself out there. Telling people what your doing, looking for potential customers and refining the pitch. Inevitably though, only a few of those conversations bear fruit. My pitch is still a bit all over the place, and not everyone needs what I’m pushing. That’s when the doubt creeps in, and the pace slows. “Maybe I’ll just add this one extra feature before I ship” I think to myself. “Maybe I’ll avoid demonstrating the product to those people till it’s a bit more mature”. Because shipping could be the moment I find out I got it wrong. Again.

This week I’ve been feeling quite low and have been easily distracted. If I’m honest, the only thing driving me on right now is a fear that one day I’ll have to explain what I was doing during this time in an interview for a “proper job”, and while having a product that didn’t sell might make them feel pity, having shipped nothing would look worse.

I am now aspiring for pity from a pointy haired boss. Shit.

There isn’t really a moral to this story. I could pretend that everything’s going to plan, but I’m done with that.

Over the next few weeks I will knuckle down, fix bugs, write sales copy, pitch the product dozens of times and ship a finished version. I hope that people find it useful, if some do it will feel fantastic, if nobody does it will be hard going.

I'd love to meet you on twitter here.

Why Bitcoin is Worse Than Cigarettes and the Euro

Say you’re a gang leader and you control a territory. In order to pay your henchmen and enrich yourself, you decide to tax the local population and extract a proportion of all their wealth. Now, they might conduct most of their trade with each other using silver pieces, cigarettes, US Dollars, or bundles of pink wool. Whatever it is, the most effective way of working would be to conduct a valuation of everyone’s assets in the most standard form of currency there is and then demand payment of your slice in that standard currency. So, farmer Joe has 30 cows and 5 archers of land, which is valued at 15,000 cigarettes, therefore you demand 2000 cigarettes from Joe, or else the henchmen come round and rough him up a bit. Farmer Joe now needs to sell a few cows in exchange for cigarettes in order to pay you.

This is an effective way of working, however, you can run into issues. The threat of a good beating means there is a constant demand for cigarettes, and they are valued very highly. More so than they might otherwise be worth for what they are. As such, within your territory smoking becomes a show of wealth, a kind of status symbol. When it comes to buying products or services from people outside your territory, cigarettes might not be valued so highly. And if a neighbouring gang controlled a cigarette factory; they may soon realise that they can get your people to give them all sorts of things in exchange for a few cigarettes, which they can easily make more of any time they like. A massive transfer of assets may start to occur with all your people selling off their cows, chickens and family heirlooms to people in the nearby gang’s territory in exchange for cigarettes which they are making more of every day. Pretty soon your territory is awash with cigarettes and little else. Inflation kicks in and prices rise because thousands of cigarettes are chasing fewer and fewer products that people actually want or need.

What’s needed is a form of currency that only you control, not the gang next door. You decide to open your own cigarette factory making your own branded cigarettes. You pay these cigarettes to your henchmen and then demand protection money exclusively in the form of your own brand of cigarettes. The value of normal cigarettes collapses and the value of your own brand of cigarettes rockets. Things get back to normal as neighbouring gangs are not able to flood your economy with their cigarettes any more. In fact, neighbouring gangs actually value your brand of cigarettes quite highly because they know your people will work hard in exchange for them, so they can be used to buy stuff. For a while, all is plain sailing.

However, you can now make more cigarettes any time you like. The temptation is too great, you start to make more of them and use them to buy bespoke velvet suits and pimp up your Bentley Continental. Pretty soon there are too many out there relative to the number of things people want, and prices go up again. Neighbouring gangs loose interest in holding on to them too because their value is dropping fast. Congratulations, you have devalued your own currency.

You decide that want you need is a form of currency that neither you nor any other gang can make more of. Its a toss up between Bitcoin and the Euro, but you’ve never trusted computers so you opt for the Euro. You exchange your cigarette reserves for Euros and announce that protection money must now be in the form of Euros. Only the ECB can print more Euros and they look like a sensible bunch, so you put your trust in them. All is plain sailing once more.

Until one year, when a price bubble in chicken claw soup causes your entire population to invest in chickens and chicken farming equipment. You issue warnings or course, but they don’t listen. The price of chickens rockets with some going for as much as 20,000 EUR. People invest all their savings in frozen chicken claw soup, then one day, when people remember that they don’t like chicken claw soup, the price collapses and your people are destitute. They live off their chicken stockpiles for a while before eventually running out. They now have no Euros, no chickens, and they’re staving. What’s more, all your people know how to do is raise chickens and make soup. They flood into neighbouring areas offering their services, but there are only so many chickens that need raising and with so many people looking for work, wages fall. You cannot collect enough protection money any more and no amount of punishment beatings can change that. Your reserves are empty and your henchmen abandon you. Things are looking pretty bleak. If only you’d kept your cigarette factory going, now would be a good time to up production and keep things ticking over.

In a last ditch attempt to save your sorry ass, you go to the ECB and try to persuade them to make some more Euros to give to you. Your people are suffering you say, they can’t even buy food. They make you squirm a bit, they make you sell off some of your suits and your Bentley, but they eventually give in to your request and bail you out. You return to your gang den in with a suitcase full of Euros and your tail between your legs. As you hand out the last of the crisp 500 EUR bills to your henchmen, you think to yourself, what might have happened if I’d have chosen Bitcoins instead of Euros. “Phew” you say to yourself, at least I can reason with the ECB, if I’d have chosen Bitcoin people might have starved.

I'd love to meet you on twitter here.

Perfection Considered Harmful

People are always making mistakes. People send emails to the wrong person, or forget an attachment. They give you the wrong change at the checkout or bump into you in the street. Every day, people make little mistakes. For the most part we’re quite forgiving of each other. We understand that the other human being we’re looking at is doing their best and as long as they try to fix things quickly we cut them a bit of slack. But our forgiving nature is based on our ability to relate to the person who’s made the mistake as being like us, a fellow human being. We are notably less forgiving when someone is diving and needs to switch lanes before their junction. On the road, it is not a fellow human being who’s made a mistake, it’s “some idiot in a BMW”.

When it comes to software systems we are even less likely to relate to the person who’s made them. All software is designed, written and tested by people, but we don’t see it like that. We think of software as some alien entity that arrived on planet Earth fully formed. And like the evil BMW who wants to get in front of us, any mistakes that it makes are utterly unreasonable.

Within the software development world, we prevent mistakes by having people check things. Designs are reviewed, code is tested, the content of those tests are themselves reviewed, code is deployed to small groups of people first and monitored carefully, the list of things that went wrong is reviewed and fixes are made, tested, reviewed again, and then finally released to more people. If all goes well it will work perfectly and nobody who uses it will ever experience a problem.

All, never goes well.

Each layer of testing and checking is done by people, and people make mistakes. Each layer of checking just reduces the probability of a mistake getting through, but no amount of checking ever guarantees that the software is perfect. Playing the lottery every week will increase your chances of winning, but there’s no magic number of weeks you need to play before you’re guaranteed a jackpot. What is more, like playing the lottery, each time you get someone to check something it costs you money.

As a supplier of software to others, your job is to find the right balance when customers demand new features, developed quickly, with no bugs, on a budget. The cost of checking and re-checking each new bit of code that goes out the door must be traded off against the value of the new features you’re developing. If you had the new feature 3 weeks earlier, but had to refund 3 customers who found errors in it, would that be worth it? Or does the cost of having 3 angry customers outweigh the benefit of getting the feature sooner?

There is no easy answer here. Be assured; the site will not stay up 100% of the time, there will be bugs, occasionally a sale will be lost or a refund will have to be made. This is a natural consequence of having human beings do work for you. But the important thing to remember is; do not allow people to think its reasonable to expect perfection. Involve the customers/users in the trade off decision making process. Try to put a human face on the software you’ve created. Be present as much as possible. Smile. Talk in terms of probabilities.  Be humble. Say “sorry” early when things go wrong. And if you do that really well, you may actually go up in people’s minds when things go wrong, not down.

I'd love to meet you on twitter here.

Your Private Data is Too Important to Give to the Government

The United States Constitution gives citizens the right to bear arms as a last resort insurance policy against the government turning tyrannical and taking away their liberty. Guns are too effective, the argument goes, to be the preserve of government; the executive branch should not have a monopoly on their use.

I have often questioned the merits of that trade off for the everyday citizen. Having an armed citizenry has severe downsides, and there are other ways to stop a government turning tyrannical. But I’ve never questioned the basic logic of the 2nd Amendment – it would stop the government depriving people of their liberty.

But there now exists a weapon that is even more powerful than guns for controlling a population, and we’ve learned this week that the United States government is busy cornering the market on its use. That weapon is people’s private data, their emails, phone conversations and files.

“Don’t worry” they say, “if you haven’t done anything wrong you have nothing to fear”.


There isn’t a person on this planet you can’t embarrass, undermine or blackmail if you know enough about them. For politicians who have spent all their lives seeking power, the temptation will be too great. As Edward Snowdon says, having this weapon at your disposal turns the issue of who to use it against into a simple matter of policy.

Entrusting this data to a few corporations is not ideal, but I am reassured by the knowledge that these corporations exist to make money, and they do that by making things that people want. Even free services make money by keeping me engaged and serving me ads for things I actually want and will click on. Corporations don’t run police forces though, they can’t make laws and then lock me up for not following them. If Google or Microsoft ever tried to do that I’m sure we would be horrified. They are fundamentally different from governments, and the fact that you’ve entrusted your data to them should not be taken as justification for governments to copy and store it.

Democracy is a fragile thing, and it is more than simply having elections every so often. Free speech, the rule of law, an independent judiciary, all of these things are vitally important if you are to have a free society. It is a system of rules that prevent the people at the top having too much power. But in the long run, this system will not survive if the people at the top are allowed to wield a weapon as powerful as everyone’s private data.

I'd love to meet you on twitter here.

Behind the Scenes at QueryTree: The Anatomy of a Web Based Data Tool

QueryTree is a web based drag and drop tool for doing simple data analysis. Users drag and connect tools in a chain to first load in data, then manipulate that data (filter, group, sort etc.) and then visualise that data on a chart or map. I started building QueryTree by myself in between freelance projects about a year ago. More recently I’ve been able to devote more time to it and hire in another developer to help me. In this post I talk about some of the tools and technologies we used to build QueryTree and why.

The User Interface

QueryTree’s drag and drop interface was the first part of the tool to be developed, there was a time when I would do demos just off a single static html file with some JavaScript in – which involved a certain amount of smoke and mirrors! The tool is all about the interface and I wanted to see what people’s reaction to it was. Suffice to say that people like it and that encouraged me to continue working towards an MVP.

The UI is built using a HTML page and a few of JavaScript frameworks, these are:

  • RequireJS – this handles the modularisation of all the JavaScript and makes it easy to load new scripts asynchronously. Having modules for our stuff has been great, but getting other JavaScript frameworks integrated into Require has been a real pain. I’ve used a variety of tweaks to the require shim to set up dependencies, exported symbols and in some cases just wrapping third party libraries in require modules to keep them happy.
  • jQuery – naturally. I’m also using some jQuery UI elements such as dialog, draggable and button.
  • Knockout – this lets us template the HTML markup that we want and then bind those templates to JavaScript object models. There are “purer” frameworks such as Backbone or Angular but Knockout had a great tutorial which is what first got me into it and the framework has a minimal feel which suited my incremental development process. I started with some very simple HTML, I knockout-ified it, I added some more HTML, and somewhere along the way the UI QueryTree got built. I really like it and have never found it was holding me back.
  • Fabriq.js – this framework wraps the standard canvas API and gives you some extras like an object model for your shapes and SVG rendering. It does some extras like event handling but I’m not currently using those as the jQuery events were more familiar.
  • Flot – this is a JavaScript charting library. It uses the canvas element and I’m using it primarily because its fast. One of the things about QueryTree is that the user has complete flexibility to define whatever charts they like, which means they could do something stupid like try and plot 100,000 points. There are some charting libraries that I tried which have plenty of features, but which ground to a complete halt if the user did something bad.
  • Less – I’ve worked on so many projects were the CSS became a real mess and nobody dared refactor it for fear of never being able to get things looking right again. So for this project I took the decision to be a bit more organised from the start. Using less gave us a couple of weapons with which to beat back the CSS spaghetti: the nesting was most useful though, it enabled us to be specific about which elements we wanted to style, without adding lots of code.
  • Handsontable – The table tool uses this plugin to display an editable table to the user. Crucially, it has support for copying and pasting in from spreadsheets and HTML tables, which is fantastic.
  • Some free SVG icons
  • This reset.css script

The Middle Tier

Once the UI was mostly built and demoing well to interested parties I started on the middle tier. My language of choice these days is Python and as I’d been taking advantage of Google App Engine‘s free usage allowance for hosting my static HTML and JS it was totally natural (almost accidental) to start adding bits in python. As is standard on App Engine, we’re using Jinja for templating and WebApp2 for HTTP handlers.

First off, I started working on a JSON API that the UI could use to save the current state of the worksheet and to request the data for a particular tool. From the UI side I built objects (subsets of the Knockout model objects) then just stringified them before using jQuery to POST them to a URL. At the server end I started out using json.loads and json.dumps and have never looked back. It just works and it’s simple so I never felt the need to use a framework for my JSON API.

For user settings and worksheet setup I just used the Google App Engine Data Store. I felt nervous about locking us into App Engine but again, it’s so easy to get hooked. Although, if we had to, I’m sure we could replace it with something like MongoDB, it’s basically just a framework for persisting python objects so the GQL queries and the .put() methods are the only places where you really interact with it.

When it comes to data processing, well, we cheated. Each worksheet in QueryTree is allocated to a MySQL database and any data that you upload to a worksheet is stored in a special table for that tool. When you add chains of data manipulation tools to that worksheet and connect them to a data source the python code “compiles” that into SQL and runs it on the MySQL database. So, in effect, QueryTree is just an SQL development environment. When you click on a tool, the query is run and your data returned as JSON objects over HTTP, which the UI then renders in the results area at the bottom of the screen.

The Back End

As I said above, all the data ends up in a MySQL database somewhere. We have a system for allocating worksheets to a database and can grow the pool of databases as required. We use cloud hosted MySQL but could manage our own if we wanted, the web app just needs to know how to connect to the databases. We have workers which can clear down tables that are no longer being used to free up space too.

Keeping the data in this way does place an upper limit on the amount of data that QueryTree can handle on one worksheet to however much data a single MySQL database can hold. In practice though, that data has to come over HTTP to get into QueryTree, either as a file upload or web fetch, so the databases are not the limiting factor.

Other Bits

In no particular order, we’re using the following additional tools:

  • Joyride – Our product tour is built using Joyride, a JavaScript framework for scripting tour popups
  • Paymill – We take payments using Paymill.
  • Google Maps – The map tool renders a Google map in the results area.
  • Testing Framework – We do automated testing of the full stack using a testing framework that I wrote myself (along the lines of qUnit). So much of the functionality of QueryTree is spread out across the UI, middle tier and the SQL that is generated, or in how these layers interact, that simply testing one layer didn’t add enough value. I could have written 1000s of tests for the UI alone or for the python middle tier alone, but they would not have given me any comfort about deploying a new version to live. So I built a framework which drives the application from the UI and which exercises the whole stack. If those tests pass, I can have some confidence in the whole system being basically functional.
  • Bash – Whenever we want to deploy to live we type ./

The Future

Aside from adding more tools and  more options on the existing tools, there are three areas of the overall architecture that I’d like to improve:

  • Performance
    There’s a lot we can do to improve the performance of queries. We can automatically index tables (especially tables from uploaded files because the data isn’t changing) based on the columns that users’ queries are scanning a lot. We can also tune the queries that we generate, we’ve just made it work so far, we haven’t really thought about performance yet at all.
  • Interfaces
    We have the web data tool for pulling data in from URLs but for non-technical users I’d like to add tools that fetch data from specific services such as Twitter, ScraperWiki or These will be built on the same underlying tools as the web data tool but will be hard coded around the particular structure of the API in question. This will make it easier for users to pull meaningful data into their worksheets from third party sources.
  • Tool SDK
    Longer term I’d like to give people the ability to build their own tools and load them into QueryTree. The system is quite modular so coding up a new tool really just involves some JavaScript to define the tool at the client side, a HTML options dialog and a python object to define how the back end turns the settings into a SQL query. The only real challenge is productising it all, locking down the code that outsiders can upload and building the necessary development tools.

Looking back over this list, it struck me just how much I’m standing on the shoulders of giants here. QueryTree is a thin layer on top of the lot of third party components and my job has been simply to glue them all together in a way that is hopefully useful to others.


I'd love to meet you on twitter here.