One of my clients runs an online forum to which people can subscribe. While the process of accepting payment and registering is mainly automated cancellations and refunds work a bit differently.
Let’s say Mary signs up on the 3rd of the month but after a few months decides to cancel. Her subscription will expire on the 3rd of the month too, but she may cancel on the 10th. Since she’s paid up till the 3rd we don’t want the automated system to cancel her immediately, but to allow her access until her sub runs out.
That’s where I come in. Apart from acting as a general subscription Helpdesk person one of my tasks is to cancel access to the forum on or after the correct date.
The whole process of monitoring subscribers involves email, a spreadsheet, a text editor and a web browser. In my case, that’s Mailplane, Numbers, BBEdit and Safari, along with Keyboard Maestro and Applescript.
The sign up subscription process
When Mary first subscribes I receive an email from Clickbank, the payment processor. I select a whole chunk from that email then use Keyboard Maestro (KM) to star and archive the message, send the chunk to BBEdit and run an Applescript which gathers just the details I need onto a single line separated by tabs, like this:
order number, date, name, email address, product and price.
Keyboard Maestro then copies this line and takes me to Numbers where I can paste it in. At that point I can see what I’ve pasted and check everything’s OK. I’ve been caught out before now by format changes in the email.
The cancellation process
When someone cancels their sub Clickbank sends me an email. I copy the subscriber’s email address from that email, which for some reason almost always includes a tab character before the address.
Then I paste that address into a Mailplane search to see what correspondence there was around it and to see when the sub payment last occurred. Their subscription expires a month after that last payment.
I also paste the address into my Numbers spreadsheet so I can record the cancellation against their name and enter the date for last access to the site. I regularly go through my (sorted) spreadsheet and manually do the cancellations on the site.
One quirk of Numbers is that the tab character at the start prevents me from finding the name so I have to delete the tab.
Another quirk of the process, and where the spreadsheet really proves its worth, is that many people operate under more than one email address, they may use firstname.lastname@example.org when they pay, email@example.com when they register on the site, firstname.lastname@example.org when they email me … I can record all these on my spreadsheet so I can identify individuals and see what’s what with their subs.
The third thing I often do is search for the subscriber’s email address in the forum to carry out the cancellation.
That’s a lot of clicking, copying, pasting and deleting, and finally today it occurred to me to have KM do the work for me. It took a bit of wrangling to get it to work correctly, mainly around using pauses or waits and encoding spaces as characters, but it was pretty straightforward and seems in limited testing to work well.
With the wonderful new Groups feature of KM v7 I was able to create sets of actions within one macro.
By the way: I duplicated my macro and changed some of the more sensitive bits of information to preserve my client’s privacy.
1] Tidy up the clipboard
I use a Filter Clipboard with Trim Whitespace action to get rid of the tab character that sometimes pushes its way ahead of the email address.
Display Clipboard action is in there to help check that the Tab character was removed while I was developing the macro.
I set a named clipboard to the email address so it’s easier for me to work with.
2] Search my spreadsheet
I take the cleaned up email address, call up a Find window in Numbers and search for the subscriber by pasting the address.
3] Search the Forum website
The Forum website has a search box at top right of the page. Unfortunately I couldn’t use KM 7’s web form feature because of how the website’s coded, but it works to click at a specific location. KM 7’s wonderful mouse location window made it incredibly easy to discover where to click.
One action I needed to add to make this work was to wait for Safari to finish loading the page before trying to paste in the email address to search for.
4] Search Mailplane
Mailplane has a nice feature where pages can have URLs of their own. That means, for example, that you can search for a particular term and then Copy the Link for that specific search from the Edit menu to use later. One trick to know about though is that certain characters are encoded.
I also always open the cancellation emails into a separate Mailplane window (Command click an email in the list) so the macro needs to bring the main list of emails to the front to call up the Search URL.
To create the search URL with the email address I have to search and replace the clipboard to encode the @ symbol and then ‘glue’ that address on the end of the search link. I put this action last because of changing the string on the clipboard.
The whole macro
One of the wonderful features about v7 of Keyboard Maestro is that you can now Group actions together and use the new Gear menu to Try that group, enable or disable it, color it, copy it as an image (most of these screenshots were made that way), add a Note or rename it. I love all of those features. The Rename feature means I no longer have to use Comments to help keep track of what I’m doing.
I made a Group of Groups for the following screenshot. It shows that you can collapse a Group but still see the name you gave it. It’s a wonderful way to get an overview of the macro.
Why did it take so long?
I’ve been handling the cancellations in this way for a long time now. There’s so much repetitive action in it: copy, paste, paste, paste, and it was so easy to create the macro. I really don’t understand why I didn’t create this macro a very long time ago. Closely examining v7.0 though gave me a big boost, and a big reminder to get in and use this fantastic app even more than I already do.
When I look through my email archive I see I was using Keyboard Maestro as far back as July 2002, when it seems it was at version 1. I seem to have more than 400 macros, some quite simple, others complex. I use some of those macros every single day, others rarely.
What I do know though, is that I wouldn’t be without it! I don’t have that much time I want to waste.