Tag Archives: SQL

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.

 

Advertisements

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: A Quick and Dirty SQL Query Screen

How to create a quick and dirty SQL screen for a FileMaker database:
1. Create a data table. I call it SQLWB. Give this table two text fields: “Query” and “Result”. Both of these will be text fields.
 2. Modify the layout I call it SQL Workbench.
 3. Resize the two fields to show a larger amount of text. I add a scrollbar to the end of each field

4. Create a scripts to run the query.  I call it RunSQLQuery.

Set Field [SQLWB::Results; ExeuteSQL (SQLWB::Query;””;””)]


5. Create a script to clear the two text boxes.  I call this script ClearResults.

Set Field [SQLWB::Query;””]
Set Field [SQLWB::Results;””]

6. Add two buttons to the layout; one for each of the scripts.

The result will look something like this:








Note that this isn’t a full-blown SQL implementation, it will only respond to SELECT statements. (You can’t INSERT or DELETE, for example.   Also, if there any problems with your SQL syntax, the result box will only show a question mark.