Category Archives: Software

MailChimp: Data mining your subscriber lists.

MailChimp Logo

To find out more about your MailChimp lists, create a segment.

I’m not sure why it took me so long to figure this out, (just dumb, I guess..) but MailChimp actually has a pretty good built-in querying ability directly from the management interface.  It involves the segmenting function, where you create subsets of your list.  MailChimp calls these subsets segments, and the classic use for this is to break up a large list so that you can test different segments by using different subject lines, or mailing times.

From a database perspective, it looks like this:

MailChimp vs. Database
create a segment = create a query
segment = query results, aka a “cursor”
segmenting options = query criteria, aka  an SQL WHERE clause
saved segment = saved query results

In SQL, this would be the equivalent of:

SELECT * FROM <my eMail list> WHERE <my criteria> INTO <my segment>;

The available criteria are fixed, but there are a lot of useful ones. You can combine up to five criteria in a single segment request.  For example, let’s say you want to see how your list is performing. You can query how many subscribers opened:

  • all of your last five campaigns
  • one or more of the last campaigns
  • none of your last campaigns

The criteria are chosen from a convenient drop-down list.

Mailchimp Segment Drop-Down

Mailchimp Segment Drop-Down

To see the results of this query,  click on the  “Preview Segment” button at the bottom of the dialog box.

MailChimp - Segment Results

MailChimp – Segment Results

One thing you may note in the listing above, is a field called “Grade Level”.   We include this field on our MailChimp sign-up form. It will be populated only if we acquired the user through that form and if they choose to give us that information. We also ask for zip code.

The “Contact Rating” field, with the stars, rates the quality of the contact based on their campaign activity and the length of time that they have been on the list. Oddly enough, new acquisitions start out with two stars. If they fail to respond to several campaigns, then they are demoted to one star. These stars are the basis of determining how to pare down your list; eventually you might consider removing 1-star contacts altogether, or sending them a “re-engagement” eMail beforehand. This is well documented on the MailChimp web site. To cut to the chase…  4 and 5 star members are engaged, 3 star members either have low activity, or haven’t been on the list long enough to earn a higher rating.

 

HeidiSQL+pLink+putty=Joy

Logo for HeidiSQL, a slick GUI front-end for mySQL

After manually changing a hundred blog posts imported with another theme from “published” to “draft”, I figured it was time to actually look at my WordPress database, since we may wish to do some global link updates,  once we get all of the media imported from another blog.  One of the best tools for this on Windows is the wonderful HeidiSQL program.

My Ubuntu server which hosts mySQL wants an SSL connection to accomplish this, so SSL must be used with HeidiSQL. This is done by using a intermediate program called plink which sits between HeidiSQL and Putty (the terminal program for accessing the Linux command line).

I found an explanation of how to use pLink with HeidiSQL.  However, if you can reach the command line using Putty and an SSL connection on port 22,  then you don’t have to do the first part of the instructions, because you already have the server’s certificate installed on your machine. It was cool to be able to verify this in the Windows registry by looking at the registry key.  And then, I was in.

heidisqlscreen

 

 

FileMaker 15

filemaker

FileMaker has updated to version 15 for all platforms. This version includes a ton of bug fixes, heightened security, and some internal changes, as opposed to visual changes. In fact it almost looks and acts the same as version 14.

There is a fair amount of grousing going on in the FileMaker forums about the paucity of new features and (yet another) increase in the effective price. I’m afraid that FileMaker is pricing themselves completely out of the low-end market, although a single copy can be had for education or nonprofits for $197 on Amazon. (Regular price $329).  This is a perfectly respectable deal, and you might want to consider at least one copy for end-user database needs especially if you use Macs, or loath Microsoft Access. FileMaker is my database of choice for front-ends for mySQL (via ODBC), managing eMail lists, and creating tables and inspecting data of all kinds.

FileMaker comes with some pre-built database applications called “Starter Solutions”. Some of these  have been updated for version 15.  I did my expense reporting for a recent trip in the Expense solution, and it makes an attractive listing sorted by categories of all your trip expenses.  Here’s the data entry screen.

Screenshot_051716_035903_PM

Here’s the report:

Expense Report

The application can be hosted on FileMaker server and accessed in a web browser, or run within a standalone copy of FileMaker on a Mac or PC, or run on an iPhone or iPad using the free FileMaker Go app.

There is provision for storing an image of each receipt.  If you run the application on an iPad, you can snap a picture of a paper receipt, or enter a bar-code.  Pretty slick!

 

WordPress: Fix the File Upload Size Limit

On our WordPress site we want to allow the site manager the ability to upload PDF files which can then be downloaded by our blog viewers. While working out this process we ran into a problem with the file size; any file larger than 2 megabytes was not allowed to be uploaded to the site.

Rather than having this setting located somewhere within WordPress, it turns out that the setting is set by the PHP installation.  (PHP is the language that WordPress is written in, and WordPress the application is written as a series of PHP files).

As I was working through this issue, the first embarrassment was that I couldn’t even find the WordPress installation on my Linux server. Usually WordPress is supposed to be installed somewhere relatively transparent, like the /var/html/wordpress folder. Instead mine was buried under  /srv/www/li999-999.members.linode.com/public_html; somehow related to my virtual host from Linode

As my plumber says, when looking at the latest plumbing problem in our basement, “I wonder why they did that?”  Whatever. The way I finally found this out was to search for the one file that is in every WordPress site; wp-config.php.  I did this by firing up the FileZilla FTP program, and doing a search for remote files.

SearchRemoe

Having found the root directory of my WP installation,  I now needed to find the location of the PHP configuration file. I used a similar search in FileZilla and came up with two php.ini files.  Which was the “real” one?

Actually, I didn’t need to do that. Instead, I needed to get to have a script that calls the phpinfo() function which displays a nice list of all the php configuration parameters. Placed in the root directory of my web server, and then called from a web browser, this script displays all of the inner workings of the php configuration of my site.  Here’s the script:

<?php
// Show all information, defaults to INFO_ALL
phpinfo();

?>

This script is saved as GetPHPInfo.php and copied back to the root directory of the web server.

http://mywebsite/GetPHPInfo.php

Call this script in the web browser and it produces  the following:

PHP1

The above is just the tip of the iceberg. Scrolling waaaay down, I find the following parameter in the “core” section of the page:

php2

That is the size restriction, and it is this parameter that I need to change.  I’m thinking that 12 megs should be plenty.  Looking back up top in the 6th line, I see the “Loaded Configuration File” is located at:  /etc/php5/apache2/php.ini  This is the file I’m going to change. So, its back to FileZilla to find it, download it and edit it in Notepad++

PHPini

I downloaded the wp-config.php file to my local machine using FileZilla.  Then I edited the file using Notepad++. to change the  2M to 12M.  Then I uploaded the file via FileZilla back to the web server.

At this point I needed to reboot the web server, Apache2. Depending on the your installation, there are a couple ways to do this at the command line:

$ sudo /etc/init.d/apache2 restart

Or,  in the case of the lazy systems administrator, I just rebooted the whole server. This takes less than two minutes.

The result is now we can upload files that are a maximum of 12 megabytes in size.

I get to do this on another WordPress server too.  Oh joy!

Slack for Non-Profits – EMail is Obsolete!

Back in February I wrote about using Slack for our non-profit organization.  I’ve since introduced this to another organization that I’m a board member for, and it appears to have really taken off for this other group. One thing I hadn’t mentioned last time was the Slack for Non-Profits program, which provides all of the benefits of a paid Slack account to a qualifying non-profit for free. These include:

  • A fully searchable archive with unlimited messages
  • Unlimited external integrations
  • Simple usage statistics
  • Custom message retention policies
  • Guest access
  • Premium support
The first point I should emphasize is that even if you don’t qualify for the non-profit program, Slack is still highly useful. In particular, we were looking for the ability to invite users to our Slack board, without having those users be able to see the entire list of channels. For example,  here is our channel list.

You’ll note that this full list includes the standard default channels, “general”, and “random”.  All of the other channels are related either to committees, or for planning of upcoming events.  The committees include:

  • board
  • fundraising
  • publicity
The planning channels include
  • 2015_fall_concert
  • 50th_anniversary 
  • auditions_2015fall 
You may have gathered that our group is a music group.  We’re actually a semi-professional choral group of 36 acapella singers that sing five centuries of choral music.  
The calendar channel is a special channel. This is an integration which displays calendar events that are entered into our organization’s Google Calendar. You’ll note that the non-profit plan includes “unlimited integrations”. (The free plans include 10 integrations). Integrations are a whole separate discussion, but briefly, they allow events and information from other applications to appear (be copied to) a Slack channel and vice-versa. I’ve used this especially for integrating Trello project management boards with Slack channels. So, for example, I may have a Trello board for a particular grant application, and have additions and changes in the Trello board appear within the fundraising channel. 
Ok.  Back to the other enhancements. An advantage of the non-profit plan is that we’ll be able to create a channel for, say the Concert Committee, and invite all the members of that committee to the channel. Those members can be restricted so that they can’t see the board channel, or indeed any other committees that they aren’t a member of. This is great for us…as we’ll probably end up having channels for each committee, as well as an AllMembers channel for everyone who sings with us.  
The other major advantage of non-profit status is that you can use the enhancements for no cost. I was interested to see that the Slack crew said if we had an actual paid account, we’d be spending US$640.00 per year, and that is just for our current subset of our full membership, (basically the board of directors). By the time we add our committee channels and the rest of our members, we’ll be getting the equivalent of at least double that amount. Not bad for a few minutes of filling out the application.  
What are you waiting for?  Apply for a Non-Profit Slack account now.  You’ll need a 501c(3) letter, testifying that you are granted non-profit status.

And I’d love to hear how you are using Slack in your non-profit; send a note or leave comments. 

FileZilla

I have taken over our webmaster’s job, following the departure of that esteemed and highly valued person a week or so ago. We are going to miss him in ways that we haven’t even figured out yet.

I’m scrambling to find out everything that the webmaster does…and finding that his tool set was basic in the extreme; roughly a text editor and and the superb open-source FTP program called FileZilla. With these two tools, he maintained two major web sites, and several minor mini-sites, built on-demand web pages for special projects, ran our DNS, managed our open-source survey system called LimeSurvey, did our analytics and search-engine-optimization (SEO), slung PHP code like a master, and managed a series of third-party advertising tiles and several mailing lists.  Whew!  

A Database for Grant Research

I put together a grants database screen (click to view full size) to consolidate information for funding sources, and to track dates and interactions.

It is definitely an evolving project, but contains the basic information need to contact the funder, the deadline dates involved, the funder’s areas of interest, and the typical range of a grant award.

So far, I’ve been concentrating on foundation funding. Many foundations typically ask for a letter of interest before you put together a full proposal. So, I’ve included multiple date fields, a deadline for a letter of interest, a deadline for a full proposal, and a date when they announce their award.

Originally I thought that this database would be mostly for research, but after working with the online grants database, Grantstation, I think I will reserve this database for funders that I really expect to submit to. Some ideas for future enhancements include:

  • Links to standard “boilerplate” paragraphs that are used in an application. 
  • Links to edit the proposal or letter directly in Word. 
  • Links to the PDFs of the proposal. 
  • Reports that create a grants calendar. 
Before anyone comments that “you should really use X software” for this purpose, I just want to say that I’ve used several in the past, including DonorPerfect and Blackbaud, and evaluated many others. Right now, I’m in the process of rethinking my entire workflow automation from the ground up, and this very lightweight approach is just what I’m looking for. Plus its in FileMaker, so I can run it on my Windows machines at work, or my Macs at home.