Set My Data Free!

A nicely designed and well-built website will pay for itself many times over. Whether you already have one or whether you’re looking to get one, you will find many web designers extol the virtues of standards compliance, search engine optimisation and semantic mark-up.

After all, these are some vital things to consider with any website. On these subjects you will find much information and many guides but unfortunately these subjects are shouted with such ferver that often it’s done so at the near complete omission of some other vital things to consider for a truly successfully implemented website.

Whether you’re going to be selling online, or turning customer enquiries in to quotations, or building a marketing contact database or travelling the length and breadth of the country to meet potential clients, there is one very important thing in common: at some point the data in the website has to enter the physical world.

No man is an island
John Donne

Be it pick lists, packing sheets, delivery notes, address labels, satnav destinations, thank you letters, receipts, or a great many other things – at some point it’s more likely than not that you need the data in your website to be used for something which involves getting it out of the website. It is this pursuit where even the best standards-compliant, search-engine-friendly and semantically-correct websites come woefully undone. You may already have RSI from copy-paste operations all day, or have spent hours wrestling with Excel to stop it “fixing” your post code fields, or maybe you’ve lost the will to live trying to tame Word in to submission (word processors easily do to words what food processors do to food). If not, you’re one of the rare very lucky ones.

Websites should serve two masters: your end user customers; and your staff. The best website ever devised is incomplete if it excels at serving only customers but leaves you with nothing but hope and magic tricks to cope with your back-end operations. Unfortunately this is the situation that many people find themselves in when contracting to undertake a new website. All the focus is put on the front end and very little regard is made for how the website will actually make life easier and more productive for you.

You can’t have your cake and eat it

Old English proverb

But you can if you cut it in half! You should spend just as much time discussing your back-end system as you do finessing over every detail for the front-end. Such vital questions to ask any prospective web designer are things like:

  • What is the back-end database?

  • Which tables and fields can be exported?

  • Is the database schema in fifth normal form?

  • What data formats are supported?

  • Are these formats open standards?

  • How are internationalised characters managed?

  • What third party APIs are supported?

  • Can you bulk import data to the database?

  • Does the back-end database support ODBC or JDBC?

Depending on how much the web designer squirms you may gain a valuable insight in to whether you have found one suitably competent to meet your needs. No good web designer should be willing to trade-off a poor back-end administration system just for a visually pleasing front-end. The problem is that a great many do, amateurs and so-called professionals alike. However it will be you who pays the price in the long term for overlooking this crucial aspect of your website.

Why it matters

Consider the following scenario: There’s 4 weeks to go before the festive season. You’ve decided to reward your most valued customers with a thank you card and include a 10%-off voucher for their next order. This isn’t an exotic wish and it’s something your competitors can do quite easily. How are you going to go about doing this?

If your data is locked in to a system that you have to click through, screen by screen, then you’re soon going to become very familiar with Ctrl+C and Ctrl+V. But considering you need a title, first name, last name, address 1, address 2, address 3, town, county and post code field, that’s a whole lot of copy-pasta for each and every customer you wish to mail. Besides, you also need to first work out a way of knowing which of your several thousand customers are your most valued. Will you go through each and every record from A to Z to check? If you did, your mailing won’t be ready to go until after the new year.

If you’re lucky enough to have a rudimentary export feature to export a list of customer details, then you’re half way there. At least this year you won’t need a new keyboard from Santa. However it still means you’ve again got to identify which of your customers are the most valued who you wish to include on the offer. How are you going to go about this? Even if you can export a CSV list of all your order addresses and see which are the most popular, how do you find your best customers? Matching those addresses up against your customer list could require a lot of printouts spread out over the floor and bad knees for days to come.

So instead you decide to give your web designer a call and ask for the information you want. "No problem!" you’re told, and several hours later when your one-off data set arrives (which is already outdated by the time it does since customers were placing orders all afternoon), along with it will arrive an invoice for the pleasure of giving you access to your own data.

So if this isn’t a route you wish to go down each and every time you wish to do a card mailing, you instead decide to spend the money asking the web designer to create a feature that automatically exports your data at the click of a button. So you send off a very loose specification to the web designer and get back a quotation that it will cost 3 times as much as a one-off data extract, and take 3 weeks to implement. There’s no way your card mailing is going to make it if you can only start doing it in 3 weeks. What’s worse, the system will only give you access to customer data and not to all the data your website has collected. Hopes of being able to query to find your top-selling products soon go out of the window. You start to realise that the data you own is locked away so tightly there is almost a ransom to be paid to get at it.

Where there is no hope, it is incumbent on us to invent it

Albert Camus

A lot of lesser web design companies like to keep it secret that your new website will be hosted on a third party cheap-as-chips hosting provider. Often you will be charged over-the-odds for such a privilege and get to enjoy the lack of benefits provided by such a provider. However, if your website is hosted on a dedicated professional server like ours, then you get to take advantage of all the tools available to empower you to maximise the value of your data.

Do you know there is a fully featured and mature office suite which offers you everything that MS Office offers, yet is easier to use and is 100% legally free? It’s called OpenOffice and it’s in use by millions of companies around the world to run their business operations. It can work with all the common file formats, including MS Office file formats, and it can work directly with your website. It’s made by the same people who make the web-server that your website runs on, so they know their stuff.

If you don’t wish to get a new keyboard under your Yule tree, and would like your mailing to go out before the last post, then here are some simple steps you can follow to complete your mailing.

1. Download and install OpenOffice

Stage 01 Screenshot
Visit openoffice.org and download the appropriate language and version for your platform. Complete the installation process.

2. Start OpenOffice and select Database

Stage 02 Screenshot

OpenOffice comes as a fully featured office productivity tool, where Writer is like MS Word, Calc is like MS Excel, Presenter is like MS PowerPoint, Base is like MS Access, and so on. There are plenty of additional apps available by default and plenty you can install from the OpenOffice app store; however all apps are the same price as OpenOffice itself — completely free!

3. Choose the type of connection you wish to make

Stage 03 Screenshot

Whilst all ODBC and JDBC options should be supported, you probably want to use MySQL (or MariaDB if offered) here as it will set-up some default options for you. Most web-hosts use MySQL as their database server, although OpenOffice supports PostgreSQL, SQLite, NoSQL amongst many others should your web-host be more modern.

4. Choose your connection method

Stage 04 Screenshot

Depending on your preference and installed options, you can use Open Database Connectivity or Java Database Connectivity. ODBC comes as standard with nearly all operating systems, and JDBC comes comes as standard with most Linux, Mac and Android operating systems. Either should work, and the settings for both are very similar. In this example we use the default JDBC which comes installed with OpenOffice.

5. Enter your connection details

Stage 05 Screenshot

Your web designer should be able to provide these details for you. You may need to give your web host a static IP in order to be able to connect, although with the security in the new MySQL/MariaDB protocol this isn’t as necessary as it once was.

6. Enter your login details

Stage 06 Screenshot

Your web designer should again be able to provide these details for you. Great care should be taken to ensure nobody else knows what your username and password are. When you have entered these details, you can press a button to test the connection.

7. See your data!

Stage 07 Screenshot

That’s all you need to be able to see your database tables. Your web designer may have made it so that some or all or your tables are read-only. This means you can see their contents but can’t change their contents through this interface. There are many good reasons why this might be the case, and they are all to stop you accidentally making a mistake which can have devastating effects on your website data.

Doing the mail merge

With your data available you can really start to get value out of it. What’s best is that as your website data is changed by your users, these tables will update automatically in real-time. You no longer need to worry about saving versions of your data, the version now is always the latest.

You will probably find your tables are a bit more complex than this example but your web designer should be able to tell you which fields are used for what, if their name or contents aren’t descriptive enough.

8. Create your letter

Stage 08 Screenshot

Go to the File menu, choose New and select a text document. You can start writing your letter and insert fields from your tables by going to the Insert menu, choosing Fields and then Other.

When you’ve completed your letter, go to the Tools menu and choose Mail Merge Wizard.

9. Choose your document

Stage 09 Screenshot

It’s possible to reuse a document which you’ve already made but in this example we want to use the document that’s just been created. If you have trouble finding your fields in the Insert menu, you can come to the Wizard first and choose “Create a new document” and OpenOffice will guide you through the process of creating a new document with the fields you want.

10. Choose your data source

Stage 10 Screenshot

Going through the default options is quite sufficient, unless there is something you wish to change. When you get to "Insert address block" you can tell OpenOffice which database you want to use to power your mail-merge/e-mail merge/fax-merge. When selecting the address list, you should find that the database you accessed in the first few steps is automatically available (as will any other databases you have recently created). Pick this if it’s not already been automatically selected.

At this point you can be a little bit smarter by applying a filter. You might like to do a merge for just people in London, or for those customers you value the most. Using the "Filter" button can let you choose just which customers you want to include. You enter the criteria that records much be matched against and anything which doesn’t match isn’t included in this particular operation. You can save filters for future reuse.

11. Finishing touches

Stage 11 Screenshot

When you finish the Wizard you are returned to the document it’s generated and you can fine-tune it, see what it will look like with the fields replaced with actual data, and then choose to print them individually or as a batch job. You can even repeat the entire process easily to make mail-merged envelopes by going to the Tools menu and choosing Envelopes & Labels. OpenOffice even makes it easy for your printer to print the required envelope next to the letter that has to go in it, rather than doing all the letters and then all the envelopes and trying to match them up.

In conclusion

Regardless of what you want to do with your data, OpenOffice is one of many fantastic tools which are totally free and able to empower you to maximise the value of your digital information. Whether you want to mail-merge your customers or export your contact list as a KML file to send to your satnav, the possibilities of backend data integration are what separates a difficult website from a website that actually improves your business.

Refuse to settle for having your data locked away and inaccessible. Put as much thought in to the design of a good back-end system as you do in to the visual appearance of the front-end system and you will achieve a website which is far more balanced towards your needs and not just the needs of your customers. After all, your customers are the ones who can better benefit if you can use their data in useful ways.

Set My People Free!

Moses

If your current web-host can’t give you access to the data you own then you should really be asking whether they’re an appropriate partner to be able to support your business. If you’re currently looking for a new website and need a web host who knows their stuff then you’ve found the right place. We will work with you to create something truly special. Please contact us to see what we can do for you and your data.

Leave a reply

(won't be published)
Email us Web enquiry form