airtable | Successful Software

Eating your own dogfood There is a story that a president of a pet food company ate some of his own dog food, to show how good it was. I’m not sure how tasty dog food really needs to be, given that dogs are happy to lick their own backsides. But his commitment is admirable. The least we can do as software developers is to use our own software as much as possible. After all, if you don’t use it, how can you expect anyone else to?

In that spirit I have been using my new Easy Data Transform product as much as possible. The biggest project so far has been merging two databases, for a charity that I volunteer at. I created an Airtable database for the charity. But volunteer information was already in a separate CRM. I imported relevant CRM data into Airtable, but the CRM system remained in use for emailing volunteers for a couple of years while I concentrated on Airtable and other tasks. In that time the Airtable database has become a roaring success for the charity. So we eventually decided to retire the CRM system and also use Airtable as our CRM.

Consequently I had to merge the latest CRM data into Airtable. I exported the relevant data from each as a CSV and then proceeded to merge the mailing list tags from the CRM into a new column in Airtable. I also created tables of discrepancies for the charity staff to work through. For example, where the telephone numbers or emails had been added or updated in one database, but not the other.

When I had initially imported the CRM data into Airtable, I had imported the CRM ID record. So those records were easy to match between Airtable and the CRM using a simple join on the ID. However any records added subsequently to Airtable or the CRM did not have matching IDs. So I had to match those by first name + last name or email address. The data was quite ‘dirty’, as is invariably the case with real world data. A phone number may be “0123 456 789” on one system and “01 23456789” on another. A volunteer might be “Chris” in one database and “Christopher” in another. Also some contacts had multiple entries in the CRM system. So this was not a trivial problem.

You can get an idea of what was involved from the screenshot above. The two pink input nodes are the 2 databases exported as CSV files, the blue nodes are various transforms (joining, filtering, removing spaces etc) and the green nodes are the outputs (e.g. lists of telephone and email differences, lists of people in one database, but not the other etc). Quite a lot of the transforms are just column renames (in future I should probably support renaming multiple columns in one transform).

I think this would have been a horrific task using Excel, SQL, Beyond Compare or any of the other tools I had to hand, amazing tools as they may be for other tasks. But Easy Data Transform performed brilliantly, even if I do say so myself. It was particularly helpful that you could see the whole process step-by-step and backtrack or branch at any point without losing previous changes.

While eating my own dogfood, I found one bug (related to carriage returns inside CSV records) and quite a few minor annoyances. These have now been fixed in the latest release. I also added a new ‘Compare Columns’ transform, which was really useful for this sort of work. So it was a very useful experience and I really recommend ‘eating your own dogfood’ as much as you can, along with usability testing.

Have you got some data that needs cleaning, merging, de-duping or filtering? Analytics, log files, emailing lists, databases? Of course you do! Why not give Easy Data Transform a try. It is free while it is in beta. Let me know how you get on.

There is a lot to be said for running a small software business (just me, with my wife doing some of the admin). For a start it gives me a great deal of flexibility, which I used to spend 2 months travelling abroad with my family last year. It is also low in stress, as I don’t have any employees to manage (my wife manages herself!). But even with some consulting work, going to the occasional conference and running some face-to-face training courses, I was starting to feel a little bit isolated after 13 years working mostly on my own. At that time the news was full of heart-rending stories of the suffering of refugees trying to flee war and repression. I don’t like the way the world is heading at present and wanted to do something to help these people in whatever small way I could. So I started volunteering at a charity for refugees and asylum seekers in my home town.

I initially tried to avoid doing computer things for the charity. But it quickly became clear that my IT skills were much more useful to them than anything else I could offer. Consequently I have been sorting out various IT issues, teaching people basic IT skills and have built them a simple CRM and reporting system, based on Airtable. Replacing the previous paper system with an electronic one is saving the staff and volunteers a lot of busy-work, which frees up their time to do more useful things. It is also giving the charity a lot more insight into how they are doing. And it has got me out from office and meeting some really great people who I wouldn’t have met otherwise. Win-win. Sometimes I feel spread a bit thin with my various work, charity and personal commitments (which is partly why the blog has been a bit quiet recently) but overall I am very glad I started volunteering.

So, if you are feeling a bit isolated, consider volunteering for a local charity. I suspect many small charities are desperate for volunteers with IT skills. Even if you are a programmer with quite specialist skills (like me), it is easy to forget that you are still an IT systems god compared with 99% of the population.