MailChimp Integration with FileMaker Pro – Part 2 of 3

UPDATE: 2011-10-10

It has been pointed out that this demo does not work on Windows machines. It seems that Internet Explorer, the application that FileMaker employs in its Web Viewer on Windows machines is not capable of recognizing the JSON files returned from MailChimp. There is currently no workaround, but I will post here if there is any news.

UPDATE: 2011-10-15 See the end of this blog entry for more suggestions for Windows users having the problem with JSON files.

UPDATE: 2011-11-26 Commentor Paul B has had some success employing MailChimp’s php syntax, instead of using the json format. See his comments at the bottom of Part I.

View walkthrough on Prezi.

Download Demo File here

In my previous post (Part 1, see also Part 3 ) about FileMaker MailChimp integration, I discussed the issues around using FileMaker as your main bulk email program, problems with WYSIWYG mail creation, getting hit with a spammer designation from your ISP, issues with using simple HTML to allow all email clients the benefit of reading the message correctly etc.

MailChimp is a web service that offers the ability to create campaigns, maintain email lists, provide stats and signup forms and somehow they are able to do all this AND dodge being blacklisted for spamming. All good things in email marketing campaigns.

The Data Islands Dilemma

The problem of working between two system (FileMaker and MailChimp), is the issue of data islands. As soon as you create another data table of your email addresses, the records can start to drift apart. Changes made directly to MailChimp may not show up in your main system and vice versa. Using the techniques in the sample database, we hope to display an approach to keeping the two ‘data islands’ a little closer together.

Keeping the records in FileMaker and MailChimp in sync is the object of this demo. We are not attempting to replicate the complete MailChimp API, which is extensive. Using the basic technique described here, you could create campaigns and start your emails right in FileMaker. We are not trying to do that, but rather, show a set of fairly straightforward techniques to allow you to keep your two data sets of email address in sync.

The MailChimp API Methods we have chosen to support in this demo include: Subscribe and Unsubscribe from a List; Add/Edit/Delete Groups; Get info on Lists; Get info on Groups. We also built an Export script to send larger batches of email addresses to MailChimp. Using a batch export/import routine is the best approach to dealing with large numbers of subscribers.

Download Demo File here

Using the Web Viewer/Parsing method we described in Part 1 of this article is very useful, but to be honest it is a tad slow. You will probably only want to employ the technique to add a few new subscribers at a time. The batch export/import is going to be the preferred mode to get set up. Then to do simple updates, adding people to groups, taking them out of the list, or adding a few new members, this can all be done using the WebViewer/Parsing techniques.

Launch the Demo File

First off, click away from the annoying splash screen, but wait…before you do, note the links here:

1) Clicking the HomeBase Software, this will take you to the website and blog of the developer. If you get stuck and need a hand with your solution…
2) Email the developer with questions and suggestions
3) Detailed documentation link. You are already reading this so, you probably don’t need the link, but on the other hand if you open the file six months from now and wonder what is going on, this could come in handy.

OK, now click the close button.

The list of email addresses

The main page is a fairly standard set of names and addresses (all bogus, generated by the fine folks at FakeNameGenerator.com)

Detail view of the People record

We will come back to this screen later, when we have done with the setup, I just wanted to let you know it was here. This is where most of the work will be done.

Groups list

These Groups correspond to Groups you will be creating on MailChimp. Adding Groups to a list give you the benefit of being able to send batch mail to sub-categories of your entire list.

The Group status field will show you if you have already added the Group to MailChimp. Don’t do anything in this layout quite yet, wait until you have completed the Settings steps which follow.

Settings layout

To get the MailChimp Global API from MailChimp,

In a web browser, open MailChimp, login, and go to the Global API page.

MailChimp login

Login to your account. If you don’t have one yet, set up a free demo account.

Go to API Keys and Info page

Copy the API key and switch back to FileMaker and paste it into the MailChimp Global API key field on the Settings layout of the demo file.

1) Paste in your MailChimp Global API key into the appropriate field
2) Click the Get List API button to get the Mailing List API key.

Note that in MailChimp, it is possible to have multiple mailing lists. We are assuming there is only one and we intend to employ Groups to handle sub-categories of People to send to, rather than having them in different Lists. If you want to do things different, you have to revise these scripts to handle multiple List names.

List API is retrieved into FileMaker

Once your API for the List is returned, you can move on and add your own Groups.

List in MailChimp

This web page shows the corresponding List in MailChimp. If you click the settings button, you can go to a screen that displays the List API.

Click on Groups in MailChimp to see the list of Groups created within this List.

MailChimp – List view – View Groups button

Click View Groups to view the Groups list.

Groups in MailChimp

Note that these groups already have members, because I did my import before. Your groups should not have any members at this point.

Groups in FileMaker

Clicking the middle yellow button (it turns blue to indicate that you are on the selected layout) will take you to a Groups list. The buttons here operate as you might expect, Adding, Deleting and Updating the Groups. It is good idea to create and tag People into Groups before uploading the full list to MailChimp.

People Layout – working with People and Group Tags

Find Groups and Tag Groups popup and buttons

Find Groups and Tag Groups

Find Groups
Click into the field to the right of the ‘Find Group’ text to see a popup list of the groups, with their ID number. If you have FileMaker 11, selecting one of these items will result in a search for that particular group. If you are in an earlier version, simply select a group and click the small ‘go’ button beside the field.

Tag Groups
The same approach works for Tag to Group. Find a selection of records and then select a group to tag your Found Set to. Click the Go button and it will loop through and add that Group tag to your list. Ideally this would have some error checking to prevent you from adding to a group twice, but, hey, this is just a demo right?

Mailing List Hygiene

MailChimp is fussy about what you send it in terms of email addresses. It is a good idea to clear duplicates and bad email addresses before running the Export for MailChimp routine.

Finding Bad Email Addresses

There is a Custom Function that checks for email validity. It doesn’t do a domain name search, but checks for top level domains. If it is causing false positives, please review the list in the Custom Function called “IsValidEmail”. This came from www.briandunning.com.

Export for MailChimp

So now you have your data ready to go. You have tagged people to Groups, cleaned up duplicate and bad addresses and you want to get rolling with MailChimp. Click the Export for MailChimp button.

First chance to bail out…

You are warned that this is a relatively slow process. The slowness is caused by turning the portal list of Groups into a comma delineated field for import into MailChimp.

Found Set or All?

In this case, for speed, I am selecting ‘Found Set’, but you will probably select ‘All’ for a full export.

Hygiene check runs

The Export script has screened for bad addresses, and notes that one address was rejected, meaning that only 24 out of my 25 records is going to be exported. If I had cleaned up before, this would not be happening, but then I wouldn’t be able to demonstrate this happening for you.

Processing…

If you have FileMaker 11, you will see a graph refreshing periodically to give the user an update on how things are progressing. If you have FM 10 or less, you will just see a flashing screen.

List Exported

File name given for the exported file, saved to your desktop. Details listed about dropped addresses.

FIle on Desktop

File Contents

File Export is a text file, ready to import into MailChimp. Note that the Groups are listed in the last field with commas separating them. This allows MailChimp to import them directly into your Groups in MailChimp. Note that these Groups were in a portal in FileMaker. That is what was slow about the export, moving them from an up-down format to a sideways format.

Back in FileMaker, MailChimp Status has been updated and the Groups Summary is filled in…

Over in MailChimp, import the list

Under ‘Lists’ tab, click ‘Import’ link on your List.

Select ‘Import from my computer’

Click the ‘Upload File’ button.

Click the ‘browse’ button to view a file on your computer

Select file from Desktop – click ‘OK’

Click ‘Import List’ button

Match up fields with the appropriate MailChimp field names

Click on each column head and select the field you are importing into, click ‘ok’ to move to the next field.

Click ‘all done’ to start import

Wait a while as MailChimp reviews the upload. Seriously, wait a while. If you thought my export was slow, wait until you try their import…

In the end, your group of email addresses should be imported, complete with Groups assigned.

Back in FileMaker again, Customer updates email address or Group info.

Now that you have your addresses in FileMaker and MailChimp, you can keep using FileMaker to do what it does well, keep track of your interactions with your customers and let MailChimp handle the bulk emailing chores.

If a customer tells you they have a new email address, click the Unsubscribe on the old address, enter the new address and the click Subscribe. Their address will be updated in MailChimp, without the need for a complete reupload of all the addresses.

Similarly, if you add them to a new Mail Group, you can Unsusbscribe and re-subscribe them to the list, with the new Groups listed.

Download Demo File

Download Demo File here

Give it a whirl and send me any comments or feedback.

Update: Part 3 of this topic covers using the Update method from MailChimp’s API to further refine the integration between the two systems.

Tagged: 'Freddie' the MailChimp, Data Islands, Email Marketing, FileMaker, MailChimp, Mass Emailing

Posted in: FileMaker 11, FileMaker Pro, Techniques