Skipper Jack's Tales of Yellowfin and Where to Fish!

Hello again shipmates. Thanks to all for reading my previous blogs, very much appreciated.

It’s come to my attention from my last blog that not everyone knows the definition of Business Intelligence. I’ve had a question from the East Devon Volunteer Services Agency (EDVSA) on this. Well, simply put, from my perspective, Business Intelligence allows me to bring together all the information I collect on my fishing trips and make sense of it. Creating clarity from clutter, so to speak. With this knowledge I can plan and make better decisions to improve my business. I hope this clears that up for you.

For those of you new to my blogs, I’m Skipper Jack and I’m the owner of a fishing trawler named the DSCallards. Me and my first mate, Gerald, work out of Fowey in Cornwall and my missus runs the business side of things on dry land while me and Gerald are out at sea.

The DSCallards has all manner of technology on board, but there’s one superb piece of tech that we can’t do without and that’s our Yellowfin system. Find out more at www.yellowfinbi.com.

Yellowfin is an amazing tool that allows us to work from our iPads while out at sea. We can perform such tasks as calculate profit margins based on what we’ve caught for example, before we reach port. 

But lately, whilst relaxing in our local pub, The Salty Dog, Gerald and me have been contemplating the most important question for all fishermen, and that’s ‘where to fish’.

We have sunk many a cider pondering this. 

One evening, Gerald had a brainwave. He called his idea ‘Location Intelligence’. Unfortunately, someone else had already thought of that, so he renamed it ‘Where To Fish’.

Location Intelligence is a new-fangled term used in Business Intelligence circles. We think ‘Where To Fish’ describes it perfectly.

So I thought I would share it with you all. Gerald reasoned that if we could visualise where our customers were on a map, we could fish nearer where they were. This could save us time and money we thought. So, after having another pint, we got to work.

But where do we start? Well, we found out that we needed to get the latitude and longitude to pinpoint an area on a map. And, if we had the postcodes of the restaurant’s that bought our fish, and work out the latitude and longitude of them postcodes, we could plot this on a map, like a Google Map for example.

We was very fortunate to find a web site that helped us loads.

http://www.freemaptools.com/download-uk-postcode-lat-lng.htm

This site has a free downloadable postcodes.csv file that contains the first element of every postcode in the country. For example ‘PL1’ is a postcode in the Plymouth area.

Gerald got to work in Yellowfin. But straight away we came up against a challenge. We couldn’t link this new information to our fishing database. ‘No problem’ said Gerald, ‘all we need to do is create a new Yellowfin View with this information in it, and then we can use a Yellowfin sub-query report to link the reports based on different views together’.

Gerald wrote the main report in Yellowfin showing the latitude and longitude information.

 

Then, using a sub-query from a different Yellowfin view, he wrote a report with our customer’s postcodes in it. 

 

Both reports needed linking together with a common field and this is quite straightforward to do in Yellowfin. The Outcode refers to the first part of the postcode. This was taken directly from the downloaded spread-sheet file. Gerald needed to create the same information from the postcode field in our fishing database.

He wrote some SQL to accommodate some of the data anomalies in our database. I’ll share it with you below. This was based on an SQL Server database.

CASE WHEN MID(customer.postcode,3,1) = ‘ ‘ OR LENGTH(customer.postcode)=5 THEN LEFT(customer.postcode,2) ELSE

CASE WHEN MID(customer.postcode,4,1) = ‘ ‘ THEN LEFT(customer.postcode,3) ELSE LEFT(customer.postcode,3) ELSE

LEFT(customer.postcode,4) END END

After he’d done this, he was able to link both reports together.

The end result is shown in the map below. This map shows where some of our customers are. Gerald used a Google Map chart in Yellowfin to do this. Clearly, in this example, fishing in the Plymouth area gets us nearer to our customers.

Until next time ...

Skipper Jack

For more information visit www.dscallards.com.