Making a database usable online with Caspio

Anyone using MS Excel Access on a regular basis will have wondered how easy it might be to move the data online. This might be with a view to enabling a client to view a database, or to allow working from another computer.

I have not tested the latest online offering of MS Office/Access so can't comment on this. I am stuck with the older version of Office which does not easily 'go online'. To solve the problem I recently took a trial with Caspio - a tool to take data online.

The brief - Take Access online

Some background....this was in response to a specific query from a client asking for access to a database I use to create a monthly report. This seemed like a good idea. They can do a bit of slicin'-and-dicin' of their own, gaining some up-to-date added value from it.

And heres the problem. The Access database on which everything sits is not online accessible and even if it were, Access is not the most intuitive platform to run queries on the fly. There is no escaping it; Access is not particularly user friendly. What was needed was a way of creating a GUI or client view which they could use without tuition.

This is the heart of the existing Access database. It kind of makes sense to me if no one else!

Caspio - The Solution?

Caspio is a bit like Access in that it runs on tables and uses queries, forms and reports. It easily deploys to your own website, literally appearing as a box where you put the code. I am using a Weebly website and it was little hassle to install. After loading my latest table of data, the rest of the time was spent configuring what the clients sees and how they manipulate the results.

A number of video tutorial do help getting to grips with Caspio. Although I have explored only a narrow selection of its capabilities it seems logical and well laid out. I would have liked to have seen more of those little pop-up explanation boxes which expand on what an option means, as there are an awful lot of options.

Once some sort of data table has been added to Caspio, the next stage is getting a table online. This can either be used to collect responses (contact form, directory, etc), or adding responses to a form. Alternatively, as in this case, I have all the data and want to allow a client to sort and filter through them.

Anyway, in either instance you need to go to the DataPages, and in this instance select Report and Tabular style. 

From here there are about 8 stages left including selecting what the fields to show, creating what the page which the client will use to select their options. Then specify what the initial and detailed results pages will look like.  

Once the wizard has gone through the various options and stages, the final thing is to deploy the code to a site. Copy the chunk of code at the end of the process and paste it into a webpage. In my experience I then spent quite a few hours refining the look and way a user selects the options. My final(ish) version looks like this:

I would predict you will spend the majority of your time on this specific page of the wizard:

This is where to decide how the fields look and operate. I wanted a Date From and Date To option which was set up as a double criteria on the same field. I was also keen to allow my client a 'Select All' option which required a bit of extra configuring. There is no coding and it is all done by working through the wizard and seeing what the impact is on a preview form.

I would recommend a newbie to look at a few video and read a couple of how-to's. Then try adding a table and creating a deploy to a site. You can't really brake it.

Regrettably this level of functionality is not free, although there is a trial with the 'enterprise' version for a week or so. I did read around to see what the others were offering. While there are cheaper ones, with something like this there needs to be a level of dependability and support, both things which the reviews suggest Caspio excel at.  

Thank you for viewing the article and I very much hope you found it interesting. Please don't hesitate to offer a comment, particularly if I get things wrong!  It would also be great if you wanted to subscribe to future blog updates.

Media Evaluation Techniques (Part 2)

In Part 1 of this discussion on media evaluation techniques there was a look at the processes involved in collecting the cuttings, creating a media list and using packages like MS Excel and MS Access to collate and sample the media coverage. 

Although this 2-parter is an exploration of media evaluation techniques, deciding on a strategy is no less important. To be clear on this it is necessary to set clear campaign or period PR objectives. These objectives need to be measurable; thereby enabling the evaluator to hang the relevant techniques off of these, and thereby addressing these goals.

One of the points to emphasise is the non-prescriptive nature of this advice. As is often said in media evaluation circles, there is no one-size-fits-all solution. To paraphrase Philip Sheldrake 'Every organisation is unique, generating unique exposure, demanding a unique set of evaluation metrics'.

Once the media list has been collated and the chosen fields queried the most interesting stage to an evaluation project can start. My chosen query tool is an Excel Pivot Table, in my experience one of the most flexible and little used tools in the entire MS Office suite.

After extracting a listing featuring all the needed fields from MS Access, (see Part 1) paste into a blank Excel sheet. Add an extra column at the far left titled 'Count' and fill it with '1's down to the last entry. This will be used to calculate volume figures. 

Then from the Insert tab on the top line select Pivot Table which will then create a new tab. 

The list of fields on the right should match the column on the your listing. The first thing to do is select a metric with which to generate results, which if you want to use volume is what the 'Count' column is for. Drag it to that box unless you want to use another metric like cumulative audience. Be aware that if selecting another metric make sure it says 'Sum of....'. If it says 'Count of...' click the drop down to the right of it and select Value Field Settings and select Sum.

Pivot tables are a very flexible tool and its worth spending time dragging the fields around between the boxes and seeing what the impact is on the grid on the left. It is quite hard to break a pivot table. When you start dragging values around and doing cross-references, it is tempting to add a column or alter the source listing in some way. That is fine but delete the pivot table tab and insert a new one as it won't recognise the changes made to the listing. 

The sort of useful results which can be generated using this tool is multiple cross-referencing of fields. For example, finding out how many proactive clips there are for different subject or product areas or tracked competitors. As long as it is on the listing you should be able to query it.

The next stage is to parse these results into some form of report or presentation. Though this might make good content for a later post it is likely you have a very particular application in mind. 

I hope you have found this of interest and I would welcome any comments or thoughts you might wish to share.  

Media Evaluation Techniques (Part 1)

Although everyone has a different way of doing an evaluation of a project or periods media exposure, there are certain common processes worth highlighting. From a strategic perspective the starting point has to be the objectives. on the other hand, if you are more interested in evaluation techniques, getting the media clips seem like the logical beginning point. 

While there is much to be said about PR and campaign objectives at the strategic level, this mini-guide concentrates on the execution techniques. More often than not these media clips are electronically circulated press and internet cuttings from the various specialist monitoring outfits like Gorkana or Kantar. Personally I can't speak for the latter in any detail but for the former there is also the option to create an Excel media list, which we'll talk about in a bit.

Having worked with these sources for a number of years it is my experience of the UK media that most of the coverage found tends to be either printed or online coverage. It may be a symptom of my clients interests or the keywords checked but there is seldom coverage from broadcast sources.

These Excel media lists can be filtered to only the coverage for a set period; helpful if doing monthly evaluation reports. Often these listings will also have an indication of circulation or audiences (and the dreaded AVE figures!). Unless my own  master media listing file is missing a title I tend to ignore these figures. Once this Excel listing has been formated I Paste Append into the clients MS Access file.

This is when to find out how well the listing has been formatted. All the Excel columns have to match up with the Access file. Also this will draw on a separate master media data file, and so the media titles have to match. This is often a problem as my master media titles do not start with 'The', instead leaving it at the end. For example 'Daily Telegraph, The' is our chosen format but the cuttings agencies don't always do it that way.   Once the data has been added I run a query to highlight those media titles it can't find.

After 'cleaning' the entries the next stage is to work through the cuttings, sampling them for the relevant tracked fields. It is always a good idea to sample for as many fields as you might think could be relevant. This often goes beyond the brief, but you never know what might be of interest or where coverage might take you. 

Core requirements tend to be things like favourability towards the brand and presence of key messages. Then there are additional things like the tracking of subject or product/service areas, corporate/brand descriptors, pro-activity, scale of cutting. Below is an example of a form with fields (excluding messages).

That is the first part of a two-parter. In the next section we will take a look at some techniques on how to interrogate the data, filtering, sorting and sampling. Then there is report writing, mixing graphs and tables with narrative and choosing formats, all to address the original objectives.

If you have thoughts on this or additions it would be great to hear from you.