API Fun with Quickbase and Mailchimp

Things have certainly changed in the past 2-3 years as business process automation technologies have evolved from primarily being on premise technology stacks by companies like IBM, Microsoft, and Oracle requiring on site system administrators to keep them running…to a new public cloud technology model from companies like Salesforce, Intuit, Google, and Amazon. These new public clouds are focused more on providing solutions rather than infrastructure. More and more often nowadays the job of creating business process automation solutions entails piecing together a smorgasbord of technologies from a variety of vendors most of whom are in the cloud.  Whereas in the past developing these solutions required developers to know a great deal about the underlying operating systems…today the process is more about creating solutions without having to know anything about the underlying platform running the solution. Automating systems is now often more about writing small amounts of code to connect different cloud platforms thus creating unique and innovative solutions to business problems.

In this vein I recently had the opportunity to develop an interface between Quickbase and Mailchimp. Quickbase is a cloud platform from Intuit for building custom database applications and Mailchimp is a cloud based email marketing service.

In this business scenario Quickbase is a customer relationship management (CRM) system where new leads are collected into a Leads table via direct input, mass import, and web forms from company websites. Mailchimp is used for email marketing campaigns and periodic newsletters.

The problem was to create an interface between Quickbase and Mailchimp so new leads collected in Quickbase could be automatically synced with mailing lists in Mailchimp.

I chose to create a PHP script with the Quickbase API to query a Leads table in Quickbase and sync the result set with a  subscription list in Mailchimp. On the Quickbase side I took advantage of a PHP wrapper developed by Joshua McGinnis, a Software Engineer  at Intuit, Inc.  based out of  Waltham, Massachusetts. On the Mailchimp side I was able to take advantage of  the Galahad MailChimp Synchronizer, a PHP wrapper provided by Chris Morrell.

My PHP script simply had to read the result query array from Quickbase and write the data elements into a new array in the format required by the Galahad Synchronizer.

Once completed I ftp’ed the script onto a LAMP hosting account, setup a Cron job to execute it hourly, and tested by adding new Lead records into the Quickbase table.  Then an hour later confirming the new Leads are added to the Mailchimp list as expected.

This system assumes the list in Quickbase is the master so once a lead is removed from Quickbase it is also removed from Mailchimp.

Here’s the code if you are interested:

<?php
require_once 'Galahad/MailChimp/Synchronizer/Array.php';
require_once 'MailChimp/MCAPI.class.php';
require_once 'quickbase/quickbase.php';
// =============================================================================
//  SET THESE
// =============================================================================
$apiKey = '9072e9b2028444af-us1'; // mailchimp api key
$list = 'c150f6eba1'; // mailchimp list ID
$quickbase = new QuickBase('userid', 'password',true, 'bgmk5dsfc');
$res=array();
// setup the query to quickbase > field id 112 equals yes
$queries = array(
array(
'fid' => '112',
'ev' => 'ex',
'cri' =>'yes')
);
// quickbase api query with field id list
$res = $quickbase->do_query($queries,'','','10.6.7.3.110.111.24.23','');
$email_results=array();
// write array elements from $res into a new array $users in the format required  by Galad Synchronizer
 $j=0;
 foreach($res->table->records->record as $record)
        {
            $i=0;
            foreach($record->f as $value)
            {
                $field_name[$i]=$value;
                $i=$i+1;
            }
$users[$j] =
array('EMAIL' => strval($field_name[0]), 'FNAME' => strval($field_name[1]), 'LNAME' =>
strval($field_name[2]), 'MERGE3' => strval($field_name[3]),
'MERGE4' =>  'bgmk5dsfc', 'MERGE5' => strval($field_name[4]), 'MERGE6' => strval($field_name[5]),
'MERGE7' => strval($field_name[6]),
'MERGE9' => strval($field_name[7]),
);
$j=$j+1;
         }
// =============================================================================
//  THAT'S EVERYTHING
// =============================================================================
// call the synchronizer
$Synchronizer = new Galahad_MailChimp_Synchronizer_Array($apiKey, $users);
$Synchronizer->sync($list);