Recording who clicks which link when on Sendy eMailer

For many, Mailchimp is a reliable mailer for sending newsletters and other automated email campaigns. And for some it might even be cost effective but unless you are sending very few emails I’d suggest not. Whatever your views on costs it is very easy. However, if you’re already using Amazon’s AWS then there are other services that, with a little effort, might well be cheaper. These take advantage of the fact that you get a generous free sending limit with your account.

Sendy is one such service which does much of what Mailchimp does. It can send nicely formatted emails in html and plain text, run multiple lists, help you to be GDPR compliant, manage unsubscribes and much more. If you are willing to go through the setup and, perhaps, host it yourself it is a good option.

While Sendy is feature packed it has always surprised me that it is missing something that I consider to be important – the ability to see when someone clicked a link. To be clear you can get a list of who clicks on a link in a campaign but you have no idea when. This makes any follow up actions, such as creating an activity record in your CRM for that time (which is what I needed to do) impossible. So I decided to try and fix this deficiency.

The issue, to my mind, is how Sendy stores the clicks. This is done as a comma separated list of subscriber IDs in a longtext column in the links table. The latest click of a link just sees the latest subscriber ID added to the end of the list. What I really needed was a table that held the individual clicks along with WHEN the person actually clicked. So I created a table for this:

 CREATE TABLE campaign_click (
    id INT NOT NULL AUTO_INCREMENT,
    subscriber_id INT NOT NULL,
    clicked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    link_id INT,
    primary key (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then I created a trigger that fires every time the links table is updated and the clicks column has changed:

DELIMITER #
CREATE TRIGGER tr_links AFTER UPDATE ON links
FOR EACH ROW
BEGIN
IF (old.clicks != new.clicks) THEN
INSERT INTO campaign_click(subscriber_id, link_id)
VALUES (SUBSTRING_INDEX(new.clicks, ',', -1), new.id);
END IF;
END#
DELIMITER ;

What this does is whenever the links table is updated and the click column has changed details of the click will be inserted into the campaign_click table. Bingo! We finally have a record of who click which link when.

Now you can do things such as list all subscribers that clicked a link in the last hour:

SELECT c.`clicked_at`, l.`link`, a.`title`, s.`email`
FROM `campaign_click` c, `links` l, .`ares_emails` a, `subscribers` s
WHERE c.`clicked_at` >= DATE_SUB(NOW(),INTERVAL 1 HOUR)
AND c.`link_id` = l.`id`
AND l.`ares_emails_id` = a.`id`
AND c.`subscriber_id` = s.`id`

You can make this more sophisticated should you require but you get the idea.

I should say that Ben, the author of Sendy, may well not approve of this hack and any update that is released in future may way overwrite this but I suspect that it will be ok. Use with caution, you have been warned!

Kablecard – The Swiss Army Utility Card for Charging on the go!

Kickstarter has been a bit hit and miss for me but occasionally it does turn up a real gem and Kablecard is one of those.

I have to say that when I saw the video and spec list (multi-head charging! sim tool! kick stand! back light! memory storage! credit card sized!) it did seem a bit unbelievable and I wondered if it was a scam. But no, I did end up with one and it really can do all the things that it claimed.

Inside the Kablecard

Included inside a quite thin case is a main cable which is USB-C at both ends. You can then change the tips of the cable with the included micro-USB, lightning and USB-A heads. As you will see from the video below this allows you to do things such as connect the USB-A and lightning heads and attach it to a battery to charge your iPhone. 

Tucked away to the side is also a microSD card slot which means you can whack a card in there and then connect it to you laptop for a bit of external storage. This might save you having to carry both the card and a USB stick but then who does that these days?

As you can see in the video it all works well but is a bit fiddly. My only gripe is the back plate is pretty thin and the hinge at the top only opens to the angle to allow it to work as a stand. This isn’t immediately obvious and would be possible to snap it off if you didn’t know.

However, all in all, it is a good addition to the kit bag and easy enough to slip into the pocket along with the AirPods ensuring you always have the right cable with you.

Why I Dropped Edit for Notes on iOS

So, this is me…

I wrote recently about dropping Drafts for the Edit app on iOS. In this post I said, and I quote:

Edit is a universal app (do we still use that phrase?) and the only feature I would like is sharing of text between the iPhone and the iPad otherwise I am set.

Of course there is already an app that does just that and what’s more it also syncs to the Mac as well. So what is this app? It’s Apple’s own Notes app of course!

I’m not sure why I missed this but, in my defence, when I first started on iOS/OSX Notes wasn’t what it is today and so I had hidden it away. In fact on my iPad it wasn’t even installed.

However, it is now reinstalled and I am now effectively using it as a scratchpad for simple text notes, just as I did with Drafts (and Edit).

Oh, and did I mention that Notes is free?

Here’s a Question that Pains me… Is Evernote Dying?

It really pains me to say this but I am worried about the longevity of Evernote, a tool that I have been using for many, many years. So long in fact that I now have well over 30,000 notes stored in it.

Evernote co-founder Phil Libin always said that his aim was to create a “100-year startup” meaning that it’s “a company that’s around in 100 years, which means Evernote’s product needs to be durable.” Right now I cannot see them achieving that aim. Of course that aim could have been ditched when Libin left in 2016.

Why have I reached this conclusion? Well over the last year there have been increasing issues with Evernote’s products. For me this started when Skitch started playing up after the last OSX upgrade and after months of waiting for an update I got fed up of waiting and moved to Monosnap.

At around the same time the performance on my Evernote desktop client on my Mac got bad. Simple operations such as creating a new note took forever. Six months later I am still discussing with support what the underlying issue is. I’m told that they are working on a fix.

In the meantime I switched to the Evernote web client which has improved considerably since the last time I used it and it is almost , *almost*, comparable to the desktop app but not quite. Then a few days ago it stopped load. All I get is a white screen. If I open Chrome’s developer tools I can see an error being thrown which is the likely culprit so I have raised that too with support.

To add insult to injury the web clipper has stopped working too although I guess that could be related to the web app issue, maybe, maybe not.

So right now I am only able to access Evernote on my iPad which is ok but far from ideal.

The people on support have been great but that does not make the product work reliably, or hasn’t so far. I’d move to something else but I really don’t fancy trying to migrate 30,000 notes. And who’s to say I wouldn’t encounter the same issues? Who’s behind Bear and what’s their longevity likely to be? I could move to Microsoft’s OneNote but how likely is it that they get bored of it and drop support for it (although to be fair they have stuck with it for quite a long time!)

So I feel stuck and left with my fingers crossed that things pick up with Evernote and that they get back on track for the sake of my 37,775 notes if nothing else!

Why I Dropped Drafts for Edit on iOS

I have been a happy Drafts user on both my iPhone and iPad for many years. When version 5 of Drafts was launch it moved from being a one-off payment to a subscription model which made me stop and think about whether I wanted to signing up to yet another app subscription. I knew I didn’t.

For me there wasn’t a real reason to upgrade as Greg, the developer behind the app, had said that he would continue to support the old version, but it did make me stop and think whether I was getting the most out of Drafts. I decided I wasn’t. I was literally using it as a scratch pad to enter text notes which I would then use in WordPress to start a blog post or maybe send to Evernote. Hardly making the most of a powerful app.

So I had two choices: make better use of Drafts or find an alternative. As it happened as I was considering this I happened upon an article on a new app called Edit. This looks just like Drafts but without all the bells and whistles. All you can do is enter text and when you are done either copy it to the clipboard or share it – the only two actions I ever do in Drafts.

Turns out that simplicity is the key here for me and so I was happy to swap Drafts for Edit and to date I haven’t missed it at all. It might be that you make use of the additional features in Drafts, such as TextExpander support, Markdown and the like but I never did so it was an easy choice.

Edit is a universal app (do we still use that phrase?) and the only feature I would like is sharing of text between the iPhone and the iPad otherwise I am set.

Oh, and did I mention that Edit is free?

Edit

Edit

Drafts

Drafts

Custom HTML Widget Changes in WordPress

Last week I was asked by a client if I could help them put the Mailchimp newsletter signup form on their site. They had been trying for a while and they couldn’t get the form to appear at all. “No problem” I said, think that this would be a doddle. A couple of hours later I was pulling my hair out as I tried everything I could to get it to work.

Finally I did the inevitable Google search and discovered the at some point recently the custom HTML widget had been changed and that “some HTML tags like script, iframe, form, input, and style are not available.” This referred to WordPress.com installations but I am guessing that it applies to WordPress.org installs too as that was my situation.

Another search didn’t yield any quick solution for me and so I rolled up my sleeves and wrote a custom plugin to get round it. This was incredibly simple to do and the code is below – this needs to go into the wp-content/plugins folder. Remember to change the ‘Your raw HTML here’ bit!

<?php
/*
Plugin Name: Output Raw HTML
Plugin URI: https://www.spokenlikeageek.com
Description: Spits out raw HTML 
Version: 1.0
Author: Neil Thompson
Author URI: http://nei.lt
*/


add_shortcode( 'slagrh', 'slag_raw_html' );

function slag_raw_html( $atts ) {

  $output = 'Your raw HTML here';
  return $output;

}

To use use put [slagrh] wherever you want the raw HTML to appear and WordPress will oblige.

Quite why this should be necessary I do not know but there you go. Hopefully it will save someone else a load of time or, if you have a more elegant solution, please let me know in the comments.

iOS (finally) supports QR codes!

QR codes are a more modern equivalent of the ubiquitous bar code but hold both more data and more intelligent information – if you can read them. Until this week, if you had an iPhone and wanted to “read” a QR code you needed a third party app but with the introduction of iOS11 the camera now, finally, supports them natively.

The animated gif below shows this in action and how easy it is to use:

Of course you might wonder why this is important given that you don’t see QR codes that often. Well I would counter that you don’t see QR codes very often precisely because the iPhone doesn’t support them and this support may very well open the flood gates.

Create your own QR codes

So what happens if you want to create a QR code of your own? Well it is incredibly easy if you use Google Charts. All you need do is copy the link below and replace the Spoken Like a Geek address with whatever address you want and bingo! Google will create you a QR code you can save and use.

https://chart.googleapis.com/chart?chs=300×300&cht=qr&chl=https://www.spokenlikeageek.com/

Got iOS11 already? Give it a go using the QR code at the header!

Let’s Play Spot the Difference

Can you tell the difference between these two seemingly identical bills I received by email from Virgin Media?

 

I have to admit that initially I couldn’t. It was only when I saw the bill total of £498.52 and did a double take that I took a closer look. When you do that it becomes clear that:

  • my account details are missing
  • the email address, while valid, isn’t correct
  • when hovering over the View Bill button it wouldn’t have taken me to the Virgin Media website.

I consider myself to be pretty internet savvy but I was shocked at how well this was put together and how I very nearly clicked that button annoyed that Virgin were going to charge me 400 quid for my broadband!

The EvoDX Action Camera (Cheap GoPro Clone)

For quite a while I have had a hankering for a GoPro without really having a reason for spending that sort of money. We are on a road trip soon and I like the idea of being able to take some video from the dashboard as we drive around and so a GoPro would be ideal. Even so I still don’t think I would get my money’s worth with a GoPro so when an offer came up for a EvoDX at only £20 I had to give it a go.

You get a lot of value for twenty quid. It’s not just the camera that you are getting but also a load of accessories too (see below) which includes various mounts and even a waterproof case.

All of this seemed too good to be true and looking at some online comments there was a suggestion that the output from the camera left a little to be desired. However, the proof of the pudding is in the eating, as they say, and so I did a test.

The short video below was taken with the camera mounted on the dashboard of my car. It is on the second setting of 720p at 30fps (frames per second). This is not the highest quality that it can achieve which is 1080p at 15fps but I think this is a good compromise.

I think you’ll agree that this is pretty good and certainly good enough for what I want it for. One thing that is clear though – the camera does have a slight fish eye effect to it, particularly at the edges where you can see the lampposts have a bit of curvature. An issue I’m willing to forgive given the price.

The EvoDX will also do still photographs but frankly why would you bother? The quality is pretty poor when compared to a dedicate camera or even a phone so I’ll stick with video. Of course, I could do all this with my phone but it’s hard to be recording video while also using it as a satnav so I need something dedicated.

As I alluded to at the outset I’m not willing to spend 20x the amount I paid for the EvoDX just to get a GoPro. Particularly as I’m not looking to do any action sports, where I suspect the GoPro would shine. However, if you are looking for a cheap introduction to action cameras then I don’t think you can go far wrong with the EvoDX.

Going Secure via Cloudflare (and a Warning)

Forced to be Secure by Google

The more observant amongst you may have noticed that the site is now “secure”. Quite why a blog needs to be so I am not sure but Google is starting to insist on such things so I am in the process of converting all my sites to load via https.

To be honest it has been a bit of a trial, partly because this site runs on a WordPress multi-site installation and that has thrown up a few peculiarities. Anyway, the change from Google has forced me to look at all my sites and try and get them “secure”.

SSL with Cloudflare

I’ve chosen to do this via the Cloudflare service because this is reasonably frictionless although, as you will see, it does require you to do a database update… Setting Cloudflare is simple enough – go to the right domain, select Crypto from the menu, make sure you have a SSL cert setup and then select “Always use HTTPS”. Click on the image below to see all the settings for this domain:

Cloudflare SSL settings

However, that, for me, wasn’t sufficient as I started to get a “mixed content” message and Google still was showing the site as insecure. Mixed content means that some resources, usually images, are still being loaded over a non-secure link (i.e. http). Now there is a setting in Cloudflare called “Automatic HTTPS Rewrites” which should resolve these issues but in my experience this hasn’t worked and has caused multiple redirect issues. Of course, your mileage may vary, so give it a go and see but don’t be surprised …

Updating the Database

If you find that the automatic rewrites aren’t working for you then you are going to have to roll your sleeves up and update the database. I cannot stress this enough but make sure you BACK UP THE DATABASE before starting this. I made a couple of mistakes and was glad that I had the database to roll back to.

So the command that you need to run is the following. You will need to remember to change the “x” in “wp_x_posts” to the correct site instance and the from and to website address:

UPDATE wp_x_posts SET `post_content` = REPLACE (`post_content`, 'http://www.yourblog.com/', 'https://www.yourblog.com/');

And that should be sufficient to get you up and running on SSL.

And a Warning

The issue with Cloudflare is that, of course, it could go bust or their business model may change and what is currently generously provided for free may not in the future. So you are placing your faith in them being around for the duration but if anything changes I’ll be in the same boat!