Bob Graham

The Bob Graham 24 Hour Club

How the Data Lists came to be

For the first forty years or so of the Club’s existance prospective members submitted the details of their round on paper. In the very early days even this was somewhat “ad hoc” and the submissions might be on anything from computer code listing paper to Basildon Bond writing paper! Eventually Fred Rogerson decided that a standard form was required. Originally he was only going to have 100 copies printed but was persuaded to go for 200. The form was definitely of its time - there’s nowhere to indicate gender or nationality for example, but it recorded most of what we still do today - we no longer ask for a physical address or Date of Birth though. Incidentally, the reverse side of that shown was for anticlockwise rounds.

original Bob Graham ratification form

Over the years those forms (along with the associated reports) built up until there were eleven arch lever folders stuffed to the gunwhales with them. Sadly the only time they ever saw the light of day was at the biennial Club dinner when members old and new could look through them.

An apparently “innocent” question on the FRA forums exposed the difficulty of extracting such information from that format. Even once such a list (and others) had been generated then ongoing maintainence would be a nightmare.

The following year (2012) saw a change as the Club began to move away from the paper records and into the electronic age. That transition took a few years to get to the current version (2.14) of the ratification form. Most of those changes were either to ease the workload at our end or in reponse to user error/confusion. Initially the forms were kept separate in the same way as the paper ones were but it soon became desirable to have at least some of the information in them collated in some manner. To this end an off-line database was created. Nothing complex but it would allow simple extraction and filtering of the data. The database is off-line primarily for security purposes.

But the data still wasn’t accessible...

The Covid pandemic and subsequent lockdowns provided an opportunity to digitise the old paper records, or rather just the ratification forms, all 1700 of them. That task led to the realisation that there was quite a bit of data missing. Some parts of or even entire forms had gone AWOL, others were incompletely filled out, some were illegible. As a result of this the missing data page was created. The data list behind that page being generated from the off-line database in a format that could be used by Javascript on the public site. Having more eyes on the problem has reduced that list from an initial figure of around 170 to under 90.

It was then realised that the code could be used and extended as a way of presenting both individual member’s data and having self-maintaining record lists. Generally the trick is to try and have the fewest possible data points from which you can construct as much information as possible. The table below shows the subset of data fields used on the site (Joss Naylor has been used as the example):

Field Value Notes
Given Name Joss
Family Name Naylor
Gender M
Age 34
Time 23:37
Date 1971-06-26
Week number 25
Direction Clockwise
Membership number 5
Original number 0 Used for second rounds
Season Summer Summer, winter or mid-winter
Route Option HR->SM
Record? 0 Set to one if this was a record at the time
Ladies' record? 0 Same as for the men's
Deceased 0
Nationality British
Club Kendal Athletics Club
Previous Attempts 0
Postcode LA12
Notes Anything extra that doesn't justify its own field in the database

The above data fields allow sorting and filtering on multiple criteria: “What are the fastest rounds?”, “Who are the youngest women?”, “get the fastest rounds done in July”, “How many women over 40 have done a sub 20hr round?” and so on.

Following an internet “discussion” it was decided to record details of who paced whom. While extending the database to consist of multiple tables was relatively easy, the data entry took several months of work cross referencing ratification forms and trying to figure out if this “John Smith” was the same as that “John Smith” or indeed “J. Smith” (apologies to any John Smiths reading this). Boyd Millen is a case in point: there were references to “Bill Millen”, was this the same person? A hundred or so forms later there appeared “William Boyd Millen” which almost proved things. Asking around, the eventual response was that folk at the time couldn’t remember a “Bill”. All that was for someone with a somewhat rare and unusual name.

There was a lot of jumping back and forth between forms when a name appeared and was similar to one seen before or was from the same club or era. This often happened when an early form had W. Smythe and a couple of hundred forms later there would be Walter Smythe allowing us fill in the blanks as it were.

That data allowed the generation of a second data file showing how individuals assisted others.

Field Value Notes
Name Joss Naylor
Membership number 5
Leg 1 count 3 These do not differentiate between clockwise and anticlockwise
Leg 2 count 11
Leg 3 count 9
Leg 4 count 9
Leg 5 count 2
Road support 0
Total 23 Not the sum of individual legs
Assisted 4 6 7 ... 1277 A list of membership numbers

While the database records which legs an individual helped on for any given round this is one piece of information that is not reflected in the data used on the site.

As of 2023, time of writing, there’s nearly twelve thousand names in the list of pacers and over twenty nine thousand data points linking those to the two thousand eight hundred members. There are 35 individual pieces of information for each member and four for each pacer. All told there was about 400 hours of work spread over eighteen months to get the historical data into the database.

The above mentioned “internet discussion” actually resulted in the table below showing how many members have assisted others. The data is split into blocks of 100 by membership number.

Membership # Total assists
0 - 99 92
100 - 199 87
200 - 299 87
300 - 399 92
400 - 499 81
500 - 599 80
600 - 699 85
700 - 799 79
800 - 899 73
900 - 999 79
1000 - 1099 69
1100 - 1199 76
1200 - 1299 85
1300 - 1399 82
1400 - 1499 88
1500 - 1599 80
1600 - 1699 82
1700 - 1799 80
1800 - 1899 77
1900 - 1999 81
2000 - 2099 81
2100 - 2199 78
2200 - 2299 77
2300 - 2399 75
2400 - 2499 55
2500 - 2599 86
2600 - 2699 59

The data is correct (without regard for those members for whom data is missing) at the time of writing (August 2023). While it may be thought that the level of support has fallen slightly it should be remembered that newer members (#2000 is from 2016 and #2500 is from 2021) haven’t had the same amount of time to offer assistance as earlier members. Overall the figure is 80%.

If the above has piqued an interest and you wish to do more data analysis of your own then there’s a more technical discussion on the API that lets you use the same data.