A start in SEO.

A long time since I posted here. I have to admit my attention has been grabbed by the necesity of finding things to do that may secure me future contracts.
My latest project has been UpSpainNews which is a directory of start-ups from Spain. One thing puzzled me about this site - why was my website not in the first few pages of Google search? It is (so far as I know) the only web directory specifically on Spanish start-ups in the English language. Of course you can come across some of those entries if they are listed in Crunchbase or in AngelLists, but my listing is fairly unique. Yet Google doesn't recognise this if you search for "Spanish start-ups".
So the last week or two I've been looking into the murky world of SEO. The first thing I realised I didn't do, was to put relevant meta-tags on my web-site. I've now entered a line in my html code as follows:

<meta name="description" content="A directory of Spanish start-ups, the best way to read up on those start-ups in English" />

With luck, the Google robots will pick this description up in the next week or two. It means that when my page is listed on a google search, the description will be shown as above ie "A directory of Spanish start-ups, the best way to read up on those start-ups in English". Instead of the current text which is "About this site · Directory of Startups · Company entries; General Up news; Contact. Startup of the week. Screenshot. Adtriboo - source talent! Internet jobs" and is obviously a mish-mash from the site text.
That in itself won't put me on page 1. I also have to get more backlinks to the site, and (perhaps more importantly) get articles published on other sites that link back. I have made a start by donating articles to EUStartUps.com and hope to continue along this line.
I hope to edit this article in a week or two with better news on my Google page ranking.

EDIT 5 Jan. The google bots have updated their records, and my site now makes it onto Page 2 for the search "Spanish startups". Also, I come in top of Page 1 when I search on "Spanish startups directory". :) In order keep this good position, I will now try to build up links on high ranking sites, and perhaps continue to write articles for other sites that link to my site. I've made a start with this entry Spanish start-ups to look out for in 2012. Happy reading!

Running cron jobs for Drupal on a shared host.

Ok, this isn't normally the hardest thing in the world to set up, but as I didn't have shell access I didn't get around to doing this (and ran the cron jobs manually).
In fact, setting up automatic cron jobs via Cpanel is quite easy (and you can read more about this - here
On Cpanel, in the "Advanced Section" you can click on cronjobs. There are fields you can enter to determine how often the cron job is run. For example, if you enter 15 in the Minute box, the job can be run at 15 minutes past the hour. The top box allows common settings eg once a week, or once a hour, so you don't need to enter criteria directly into every box. The most important task though is to enter your command line correctly. If you have lynx enabled on your Linux server, you may wish to use that, but the wget command is probably more common on nix boxes. So for my job at www.mygreatsite.com I put in the following command:

wget -O - -q -t 1 http://www.mygreatsite.com/cron.php



The -t 1 switch merely states try again once upon failure.
Anyway after pressing the "Add Cron job" button, that's all that needs to be done. The crontab program will perform your regular updates from now until the program is modified, or the server blows up!
If only other IT tasks were so easy to set up and then leave alone.

How to replicate a "GROUP BY" clause in Excel

As a Data analyst, I often use the “group by” clause in sql to aggregate records against a key field. For instance, to bring back records where a field has greater than 10 entries I’d use a statement like “select city, count(*) from city_sales group by city having count(*) > 10”
However I’m currently in a role where I can’t use a database :( So, can Excel do the business? Well yes, pivot tables can provide a workaround. It’s a bit more involved than using a decent database (and obviously you’d need that database if you’re dealing with hundreds of thousands of rows) but at least it’s an option.
Here’s a short example. Supposing I have various sales records for different cities (see data below). I’ve just listed a few records, but there could be 100s or 1000s of records. Supposing I wanted to see the total sales figure, for those cities with greater than 3 sales?









What I’d do is create a couple of pivot tables. The first one involves putting the city in the row label, and then putting the inv_num field in the data values area. Note that I have to change the “Value field settings” here to show the count (and not the sum). It’s then easy enough to change the sort by to a descending sort on the count of inv_num.














I now create a similar pivot table, but this time I put the Amount field into the Data Value, and this time keep the formula as “Sum of”.

It’s now a simple matter to copy the relevant data, and then use VLOOKUP to bring back the sum of sales for those cities you’ve brought back.













Remember I can also put a field in the Report filter (equivalent to a Where clause in sql) so I could (for example) select only cities from a specified area.
I still prefer sql by far for this type of calculation, but if Excel is your only tool, at least we have this option of the pivot tables.
Hope this is of use to someone out there!
Mark

Getting Wordpress to use stylesheet on new theme

Sometimes you miss the b***** obvious. I'd started making a theme from scratch in Wordpress, put the correct reference to the link in the header file, and made sure the css file was correctly named and in the correct theme folder. But no matter how many times I reloaded the page, the css rules were not imported.
It turns out I hadn't called the header php page from index.php . I should have put this <?php get_header(); > in the index.php file. All that time wasted due to a simple oversight! It is an occupational hazard when programming, but it's so frustrating. :(

How to use a graphic to link elsewhere in WordPress

I wanted to change a graphic image into a clickable link, on a Wordpress site (La Liga referee statistics )I've been developing and hosting for a Spanish colleague.

The first step was to enter the code for the text link on the page

<a href="http://www.losarbitroscuentan.com/index.php?page_id=8790" title="Los Arbitros Cuentan - Primera - Click!" id="test-logo">Primera Division</a>

You can see that the id is specified as "test-logo" The page_id (so you go to the correct link) is obtained within Wordpress editor, looking at the pages and pressing right-click, to copy the link location, and then pasting into Notepad - or merely view the URL in your browser.

Now to allocate that link-text to a graphic, we change the relevant css file. This is normally the style.css but this may change. Also, if you are using a theme, you will need the edit the css file in that theme's folder.

#test-logo {
background-image:url(/wp-content/uploads/2010/10/graphicname.jpg);
display:block;
height:37px;
width:190px;
}

So far so good, and clicking on the image will take you to your desired screen. Well, each time you save changes to a css file you should reload the webpage, so it replaces the css info in the cache with the updated data.
Looks promising, but the only problem now is we have both text and graphic conflicting with each other!
There is an awesome technique we can use in css/html - "text-indent" to take the text completely off the screen ie to hide the text

#test-logo {
background-image:url(/wp-content/uploads/2010/10/graphicname.jpg);
display:block;
height:37px;
text-indent:-9999px;
width:190px;
}

Pretty cool hey? Many thanks to this technical site for pointing me in the right direction to accomplish this!

Back in town.

I've been busy. Moved house (now live in central London). And set up these two websites for a Spanish friend Blinds in Spain and Referee stats in La Liga First one was put together in Drupal and the second one in WordPress.

Writing a count distinct query in MS Access

I had to do this recently in my new job, and was annoyed to find that Access doesn't support the code

select columnA, count (distinct columnB) from table group by columnA

I did a bit of googling around, but wasn't too happy with the workarounds given. Some discussion, with possible solutions are given here:
discussion on stackoverflow
Well to cut a long story short, I devised my own workaround:

select Job,sum(pp) as number_distinct_fruits

from

(select Job, Fruit, 1 as pp

from Jobtable group by Job, Fruit) t

group by Job

This was based on data like the following
JobA Apple

JobA Apple

JobB Apple

JobB Pear

JobB Plum

JobC Apple

JobC Plum

JobC Plum

JobD Plum

JobD Banana

JobD Plum

JobD Cherry

The results came out as expected:

Job Number_distinct_fruits
==== ======================

JobA 1

JobB 3

JobC 2

JobD 3

This worked ok on my test data, but there are two things to watch out for. Firstly, the code will produce a grouped record for nul/blank entries in the Fruit field. If you don't want this as part of your count, insert a Where clause (Where Fruit is not null) in the inner select. I also found that the Access editor kept chamging the parenthesis for square brackets, so you may have to keep editing this when opening the query. Even so, it's quite an effective workaround I feel.