Email Marketing Automation with Insightly and Mailchimp

Posted in: Code, Marketing, Work on December 5th, 2014

Insightly to Mailchimp email marketing automation
Generic email blasting is a “throw shit against a wall and see what sticks” strategy. Instead, drip feeding potential customers personalized information that is relevant to their situation is a much better way to nurture and convert potential customers, and automating these emails frees up the sales team for more important tasks like hosting long lunches and playing Farmville.

A few months ago, one of my clients went Google, prompting a change from Microsoft Dynamics CRM to Insightly CRM. I won’t go into the differences between the two CRMs, but suffice to say, Microsoft Dynamics is too complex for a small business, while Insightly offers a more SMB friendly product – especially for businesses using Gmail.

Dynamics CRM offers an email template repository which could be utilized by the sales team to store and send marketing and sales emails. However, emails had to be sent manually, which meant the sales team had to set reminders and send the same emails repeatedly. I had spent some time trying to connect Dynamics with MailChimp to automate emails, but I found the Dynamics CRM API was too complex for this mere-mortal-marketer, and there was no budget for custom development. When we moved to Insightly, we lost email tempting and sending functionality, making the process even more laborious, with staff copying and pasting templates from a shared Google Doc. (Although I did write this Gmail Templates Chrome extension to make inserting email templates more efficient).

Anyway, I was a quite excited when I discovered Insightly’s REST api and easy to use PHP wrapper! MailChimp also has an excellent API with comprehensive documentation, and a user-friendly email automation feature. The lightbulbs started going off in my head and I decided I would connect Insightly and MailChimp using their respective APIs, then use MailChimp’s automation interface to send a series of sales emails, triggered by changes to the opportunity’s pipeline stage.

The Problem

  • The sales team was getting bogged down with repeatedly sending follow up, nurture and pipeline specific emails to potential customers.
  • Most of these emails are sent on a schedule, and could be automated.
  • Insightly CRM did not offer any email template repository or email automation. (Insightly’s MailChimp integration is manual and not very customizable).

The Solution

  • Create custom list fields in MailChimp that can map to Insightly fields.
  • Export customer data from Insightly using the Insightly API.
  • Import customer data to MailChimp using the MailChimp API.
  • Write a php script to handle the export/import API connection.
  • Set this php script to run automatically using a cron job.
  • Create custom segments in MailChimp to reflect specific customer personas.
  • Setup MailChimp to trigger a series of automated emails based on changes to pipeline stage data.

One downside to this solution is that MailChimp doesn’t offer the ability to personalize the from and reply-to fields using merge data – emails all just come from a single email address.  To mitigate this (slightly), I transferred the opportunity owner’s name and email from Insightly, and then merged this information somewhere in the email body.

Plan segments and prepare emails

I needed to know which data I required from Insightly to trigger and segment the automation workflows in MailChimp, so I planned the automated email campaigns in advance. I was fortunate that I already had the emails prepared (from Dynamics CRM), so I had a good idea of what segments I needed to make available for targeting. To choose target segments, I used a persona/pipeline matrix which identifies needs and pain points in the sales process, specific to each persona (this topic needs a post of its own!). I noted the specific data points I needed for the segments and triggers, and sauntered over to MailChimp…

Map Insightly fields to MailChimp list fields

MailChimp custom merge tags
MailChimp custom merge tags

Once I knew which Insightly fields we want to transfer, I could start creating custom data fields in MailChimp to ensure the data can map across. In this case, I opted to transfer more data than I needed to offer extended segmentation options in the future. Thus, I ended up mapping about 15 fields, as you can see in the script below.

MailChimp makes it easy to create new fields by going to Lists -> [select your list] -> Settings -> List fields and *|MERGE|* tags. Then just click the Add A Field button at the bottom and add away!

One important thing to note… The php script relies on the unique merge tag identifiers (the “put this tag in your content” column), so if you customize your tags, don’t go back and change them later without also editing the script. Alternatively, you can make your script more robust by using the numbered tags (eg, *|MERGE4|*) that MailChimp generates by default.

Setting up the php script

Starting the php Insightly/MailChimp script

Next I had to write the script, then upload the package to the server so I could run the script as needed. The folder structure of the production package is as below, and it is available on GitHub.

  • inc/ [includes folder]
    • MCAPI.class.php [MailChimp API wrapper]
    • config.inc.php [configuration file]
    • insightly.php [Insightly API wrapper]
  • insightly-mailchimp.php [the script that puts it all together and is run by the cron job]

A few caveats

This solution works perfectly for my situation and Insightly setup, but there are a few things to consider before trying to replicate this…

  • I’ll be the first to admit, the script is hacky and would probably make software engineers turn in their grave!
  • It is written for a specific use case with a specific set of custom fields. It is unlikely to work for other organizations, but should provide a a good starting point to hack from.
  • There is limited redundancy for fields that are missing – missing fields can prevent individual records from importing to MailChimp (this shouldn’t break the entire import though).
  • The server should be running php 5.5.x. The Insightly wrapper will throw parse errors in earlier php versions.
  • It uses the MailChimp API v1.3.2 wrapper which is no longer current, although it still works just fine.
  • This has been tested with an opportunity list size of around 1000. Larger accounts may encounter timeouts or other issues.
  • You must edit the config file before the script will work (as below).

Config file

I have added a sample config file to GitHub (config.inc.SAMPLE.php), as the file contains API keys and other private information. To use it, rename the file to config.inc.php, insert API keys and enter admin email addresses that will receive a notification when the script runs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<!--?php 
    // THIS FILE SHOULD BE UPDATED TO INCLUDE THE API KEYS AND LIST ID, AS BELOW. THEN RENAME AS config.inc.php and saved in the inc folder (current location). 
 
    //Mailchimp API Key - see http://admin.mailchimp.com/account/api
    $apikeyMC = 'YOUR-MAILCHIMP-API-KEY';
    
    // A List Id to run examples against. use lists() to view all
    $listId = 'YOUR-MAILCHIMP-LIST-ID';
    
    //just used in xml-rpc examples
    $apiUrl = 'http://api.mailchimp.com/1.3/';
    
    // insightly API key
    $apikeyIN = "YOUR-INSIGHTLY-API-KEY";
 
    // Email address to send completion email TO
    $completionToEmail = "TO-EMAIL";
 
    // Email address to send complettion email FROM
    $completionFromEmail = "FROM-EMAIL";
 
    // Email address to CC complettion email 
    $completionCCEmail = "CC-EMAIL";
 
    // Email address to reply to completion email 
    $completionReplyEmail = "REPLY-EMAIL";
?-->
<!--?php 
    // THIS FILE SHOULD BE UPDATED TO INCLUDE THE API KEYS AND LIST ID, AS BELOW. THEN RENAME AS config.inc.php and saved in the inc folder (current location). 

    //Mailchimp API Key - see http://admin.mailchimp.com/account/api
    $apikeyMC = 'YOUR-MAILCHIMP-API-KEY';
    
    // A List Id to run examples against. use lists() to view all
    $listId = 'YOUR-MAILCHIMP-LIST-ID';
    
    //just used in xml-rpc examples
    $apiUrl = 'http://api.mailchimp.com/1.3/';
    
    // insightly API key
    $apikeyIN = "YOUR-INSIGHTLY-API-KEY";

    // Email address to send completion email TO
    $completionToEmail = "TO-EMAIL";

    // Email address to send complettion email FROM
    $completionFromEmail = "FROM-EMAIL";

    // Email address to CC complettion email 
    $completionCCEmail = "CC-EMAIL";

    // Email address to reply to completion email 
    $completionReplyEmail = "REPLY-EMAIL";
?-->

Export customer data from Insightly using the php script

The full script below uses the Insightly PHP wrapper to make GET requests to the Insightly API. I have added comments throughout the script, which explain what is going on, but in a nutshell, here is what it does:

  • Get API keys from the config file.
  • Authenticate with Insightly.
  • Get all opportunities in JSON format.
  • Loop through all the opportunities and extract the required opportunity data.
  • Filter opportunity data by selected parameters. In this case we filter by role (STUDENT) and date updated (today).
  • Get pipeline stage ID, then loop through pipeline stages to get the matching pipeline stage name.
  • Get the responsible user’s ID, then get the responsible user’s (“who’s responsible” for the opportunity) name and email from Insightly.
  • Loop through custom fields and extract the data required.
  • Get the related contact person’s data using the contact ID and loop through to extract contact details.
  • Create an array ($batch) with all the extracted data, which is later imported to MailChimp.

Now the data is available in an array, it can potentially be imported in to any other marketing automation software that has a REST API. For my email automation needs, I imported the data to MailChimp…

Import customer data to MailChimp

The second part of the script was hacked together from code in the MCAPI v1.3.2 wrapper (download zip here). Although, this is no longer the current version it seems to be working just fine. Again, I have added comments in the script below, but here is an overview of what it does:

  • Authenticate with MailChimp.
  • Checks if there is data to import (if(isset($batch))).
  • If there is no new data, send an email confirming the script ran, but there was nothing to import.
  • If there is data to import, set import options and import the data to MailChimp. Then send a confirmation email to the nominated admin emails with any errors that may have occurred.

Setting up a cron job from CPanel's GUI

The full script, in all its glory

View in GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
<!--?php //Start insightly data
 
require_once 'inc/config.inc.php'; //contains api keys and email addresses for administrator notifications.
require_once 'inc/MCAPI.class.php'; // MailChimp wrapper
require_once 'inc/insightly.php'; // Insightly wrapper
 
$insightly = new Insightly($apikeyIN); // Connects to Insightly's API 
 
$opportunities = $insightly--->getOpportunities(); // This gets all opportunities from Insightly as JSON. You can limit the # of records retrived using parameter: array('top' => SOME_INTEGER)
 
$pipelineStages = $insightly->getPipelineStages(); // This gets all the pipeline stages from Insightly as JSON.
 
$today = date("Y-m-d"); // Sets today's date so we can filter by date.
 
// Get basic opportunity data by looping through the opportunities JSON and extract the data we want. 
foreach($opportunities as $opportunity) {
  $oppId = $opportunity->OPPORTUNITY_ID;
  $oppName = $opportunity->OPPORTUNITY_NAME;
  $oppState = $opportunity->OPPORTUNITY_STATE; // OPEN, CLOSED, ABANDONED, etc
  $oppUpdated = substr($opportunity->DATE_UPDATED_UTC, 0, -9); // Date opportunity was last updated in Y-m-d format. 
  $oppLinks = $opportunity->LINKS; // An array of all the linked items, eg, Contacts, Accounts, etc
  $oppCustomFields = $opportunity->CUSTOMFIELDS; // An array of all custom fields
 
  // Loop through the opportunities links to check the linked item's role.
  // We need to do this to ensure we are getting the appripriate contact person for the opportunity. 
  // In this case, we check to see if the linked item is a Student, but you could check for any type of linked role here. 
  // This relies on good naming conventions for linked entities.
  foreach($oppLinks as $oppLink){
 
    //Here we use a conditional to only select data for links where role is STUDENT & opportunity was updated today
    $oppLinkRole = strtoupper($oppLink->ROLE);
    
    if($oppLinkRole == "STUDENT" && $oppUpdated == $today) { 
      $contactID = $oppLink->CONTACT_ID;
 
      // Insightly requires another API call to get more detailed infomation for the opportunity. 
      // Here we use the opportunity ID ($oppId) and the getOpportunity method to query Insightly. 
      // This returns detailed opportunity information in JSON format. 
      $oppInfo = $insightly->getOpportunity($oppId);
 
      // Now we extract the pipeline stage ID and loop through the pipeline stages JSON data to match the stage ID with stage name.
      $oppPipelineStageID = $oppInfo->STAGE_ID;
       
      foreach($pipelineStages as $pipelineStage) {
        $pipelineStageName = $pipelineStage->STAGE_NAME;
        $pipelineStageID = $pipelineStage->STAGE_ID;
 
        if($oppPipelineStageID == $pipelineStageID) {
          $oppPipelineStageName = $pipelineStageName;
        }
      } 
 
      // We use the getUser(#ID) method to get complete responsible user information in JSON format.
      // Then we can extract the user's info such as name and email.
      $oppResponsibleUserID = $opportunity->RESPONSIBLE_USER_ID;
      $oppResponsibleUserInfo = $insightly->getUser($oppResponsibleUserID);
 
      if(isset($oppResponsibleUserInfo)){
        $oppResponsibleUserFName = $oppResponsibleUserInfo->FIRST_NAME;
        $oppResponsibleUserLName = $oppResponsibleUserInfo->LAST_NAME;
        $oppResponsibleUserName = $oppResponsibleUserFName . " " . $oppResponsibleUserLName;
        $oppResponsibleUserEmail = $oppResponsibleUserInfo->EMAIL_ADDRESS;
      }
 
      // In this case, there are a few custom fields we need to extract. 
      // We loop through the custom fields array and extract the data we require, using Insightly's numbered OPPORTUNITY_FIELD tags. 
      // To find these tags, edit an opportunity in Insightly, inspect element and look for input value="OPPORTUNITY_FIELD_##"
      foreach($oppCustomFields as $oppCustomField) {
 
        //Get Country of Citizenship custom field
        if(isset($oppCustomField->CUSTOM_FIELD_ID) && $oppCustomField->CUSTOM_FIELD_ID == "OPPORTUNITY_FIELD_5"){
          $oppCountry = $oppCustomField->FIELD_VALUE;
        } 
 
        //Get Source custom field
        if(isset($oppCustomField->CUSTOM_FIELD_ID) && $oppCustomField->CUSTOM_FIELD_ID == "OPPORTUNITY_FIELD_9"){
          $oppSource = $oppCustomField->FIELD_VALUE;
        }
 
        //Get Birthday custom field
        if(isset($oppCustomField->CUSTOM_FIELD_ID) && $oppCustomField->CUSTOM_FIELD_ID == "OPPORTUNITY_FIELD_12"){
          $oppBirthday = date("m/d", strtotime($oppCustomField->FIELD_VALUE));
        } 
      }
 
      // Use getContact(#ContactID) method to get contact record JSON.
      if(null !== $insightly->getContact($contactID)){
        $contact = $insightly->getContact($contactID);
 
        //Use default value of "student" if names not set. MailChimp may not accept the data without both names.
        if(isset($contact->FIRST_NAME)){
          $contactFName = $contact->FIRST_NAME;
        } else {
          $contactFName = "Student";
        }
        if(isset($contact->LAST_NAME)){
          $contactLName = $contact->LAST_NAME;
        } else {
          $contactLName = "Student";
        }
 
        $contactInfos = $contact->CONTACTINFOS;
 
        //Loop through contact details to get email and phone.
        foreach($contactInfos as $contactInfo){
          if(isset($contactInfo->TYPE) && $contactInfo->TYPE == "EMAIL"){
            $contactEmail = $contactInfo->DETAIL;
          } 
          if(isset($contactInfo->TYPE) && $contactInfo->TYPE == "PHONE"){
            $contactPhone = $contactInfo->DETAIL;
          } 
        }
      }
 
      //If contact info is set, create batch array in the format required by MailChimp
      if(isset($contactEmail)){
        // Creates batch[] array for Mailchimp import
        $batch[] = array('EMAIL'=>$contactEmail, 'FNAME'=>$contactFName, 'LNAME'=>$contactLName, 'MMERGE3'=>$oppCountry, 'MMERGE6'=>'International Student', 'MMERGE4'=>$contactPhone, 'CRMSTATE'=>$oppState, 'CRMOPPID'=>$oppId, 'MMERGE7'=>$oppSource, 'MMERGE8'=>$oppBirthday, 'CRMOPPOWNE'=>$oppResponsibleUserName, 'CRMOWNEMAI'=>$oppResponsibleUserEmail, 'CRMPIPELIN'=>$oppPipelineStageName);  
 
        // Used for checking the variables are correct by running insightly-mailchimp.php locally. Remove or comment out on production.
        // echo $oppId;
        // echo "<br?>Responsible User Name: " . $oppResponsibleUserName;
        // echo "
Responsible User Email: " . $oppResponsibleUserEmail;
        // echo "
Opportunity Name: " . $oppName;
        // echo "
Opportunity ID: " . $oppId;
        // echo "
Opportunity Souce: " . $oppSource;
        // echo "
Opportunity Country" . $oppCountry;
        // echo "
Opportunity State: " . $oppState;
        // echo "
Pipeline Stage Name: " . $oppPipelineStageName;
        // echo "
Linked Item Role: " . $oppLinkRole;
        // echo "
Opportunity Updated Date: " . $oppUpdated;
        // echo "
Contact ID: " . $contactID;
        // echo "
Birthday: " . $oppBirthday;
        // echo "
Contact First Name: " . $contactFName;
        // echo "
Contact Last Name: " . $contactLName;
        // echo "
Contact Email: " . $contactEmail;
        // echo "
Contact Phone Number" . $contactPhone; 
        // echo "
 
";  
 
      }
    } // End if statement to filter data
  } // End opp links loop
  
} //End opportunities loop
 
// END INSIGHTLY DATA
 
// START MAILCHIMP IMPORT
 
if(isset($batch)){ // Send confirmation email when data transferred.
  $api = new MCAPI($apikeyMC); // Connect with MailChimp
 
  // Set MailChimp options
  $optin = false; //no, don't send optin emails
  $up_exist = true; // yes, update currently subscribed users
  $replace_int = false; // no, add interest, don't replace
 
  // Use listBatchSubscribe method to import $batch to MailChimp
  $vals = $api->listBatchSubscribe($listId, $batch, $optin, $up_exist, $replace_int);
 
  // Report errors, if there are any
  if ($api->errorCode){
    
      echo "Batch Subscribe failed!\n";
    echo "code:".$api->errorCode."\n";
    echo "msg :".$api->errorMessage."\n";
  } else {
    echo "added:   ".$vals['add_count']."\n";
    echo "updated: ".$vals['update_count']."\n";
    echo "errors:  ".$vals['error_count']."\n";
    
    foreach($vals['errors'] as $val){
      echo $val['email_address']. " failed\n";
      echo "code:".$val['code']."\n";
      echo "msg :".$val['message']."\n";
      $errorList = $val['email_address'] . "Error Code:".$val['code']."\n Error Msg :".$val['message']."\n";
    }
 
  // Send email with Mailchimp Errors to see what was imported and where errors occurred.
    $to = $completionToEmail; // This is set in config.inc.php
    $subject = "Insightly to Mailchimp Transfer Completed [" . $today . "]. Errors:" . $vals['error_count']."\n; Added: ".$vals['add_count'] ."\n; Updated: ". $vals['update_count'] ."\n";
    if(isset($errorList)) {
      $body = "**Some errors occurred during import**\n" . $errorList;
    } else {
      $body = "Import done with no errors. Woo hoo!";
    }
 
    // Set headers for the the notification email - variables all set in config.inc.php
    $headers = "From: " . $completionFromEmail . "\r\n";
    $headers .= "Reply-To: " . $completionReplyEmail . "\r\n"; 
    $headers .= "Cc: " . $completionCCEmail . "\r\n";
 
    //Send email
    mail($to, $subject, $body, $headers);
 
  }
 
// If there are no new records to transfer, we still send a notification saying just that.
} else { 
  echo "No updated records to import";
  //Send email confirming completion, but with no new records.
  $to = $completionToEmail; 
  $subject = "Insightly to Mailchimp Transfer Completed [" . $today . "]. No updated records to import";
  $body = "Nothing new to update... Have a good day :)";
 
  // Set headers
  $headers = "From: " . $completionFromEmail . "\r\n";
  $headers .= "Reply-To: " . $completionReplyEmail . "\r\n"; 
  $headers .= "Cc: " . $completionCCEmail . "\r\n";
 
  //Send email
  mail($to, $subject, $body, $headers);
}
//END MAILCHIMP
 
?>
<!--?php //Start insightly data

require_once 'inc/config.inc.php'; //contains api keys and email addresses for administrator notifications.
require_once 'inc/MCAPI.class.php'; // MailChimp wrapper
require_once 'inc/insightly.php'; // Insightly wrapper

$insightly = new Insightly($apikeyIN); // Connects to Insightly's API 

$opportunities = $insightly--->getOpportunities(); // This gets all opportunities from Insightly as JSON. You can limit the # of records retrived using parameter: array('top' => SOME_INTEGER)

$pipelineStages = $insightly->getPipelineStages(); // This gets all the pipeline stages from Insightly as JSON.

$today = date("Y-m-d"); // Sets today's date so we can filter by date.

// Get basic opportunity data by looping through the opportunities JSON and extract the data we want. 
foreach($opportunities as $opportunity) {
	$oppId = $opportunity->OPPORTUNITY_ID;
	$oppName = $opportunity->OPPORTUNITY_NAME;
	$oppState = $opportunity->OPPORTUNITY_STATE; // OPEN, CLOSED, ABANDONED, etc
	$oppUpdated = substr($opportunity->DATE_UPDATED_UTC, 0, -9); // Date opportunity was last updated in Y-m-d format. 
	$oppLinks = $opportunity->LINKS; // An array of all the linked items, eg, Contacts, Accounts, etc
	$oppCustomFields = $opportunity->CUSTOMFIELDS; // An array of all custom fields

	// Loop through the opportunities links to check the linked item's role.
	// We need to do this to ensure we are getting the appripriate contact person for the opportunity. 
	// In this case, we check to see if the linked item is a Student, but you could check for any type of linked role here. 
	// This relies on good naming conventions for linked entities.
	foreach($oppLinks as $oppLink){

		//Here we use a conditional to only select data for links where role is STUDENT & opportunity was updated today
		$oppLinkRole = strtoupper($oppLink->ROLE);
		
		if($oppLinkRole == "STUDENT" && $oppUpdated == $today) { 
			$contactID = $oppLink->CONTACT_ID;

			// Insightly requires another API call to get more detailed infomation for the opportunity. 
			// Here we use the opportunity ID ($oppId) and the getOpportunity method to query Insightly. 
			// This returns detailed opportunity information in JSON format. 
			$oppInfo = $insightly->getOpportunity($oppId);

			// Now we extract the pipeline stage ID and loop through the pipeline stages JSON data to match the stage ID with stage name.
			$oppPipelineStageID = $oppInfo->STAGE_ID;
			 
			foreach($pipelineStages as $pipelineStage) {
				$pipelineStageName = $pipelineStage->STAGE_NAME;
				$pipelineStageID = $pipelineStage->STAGE_ID;

				if($oppPipelineStageID == $pipelineStageID) {
					$oppPipelineStageName = $pipelineStageName;
				}
			} 

			// We use the getUser(#ID) method to get complete responsible user information in JSON format.
			// Then we can extract the user's info such as name and email.
			$oppResponsibleUserID = $opportunity->RESPONSIBLE_USER_ID;
			$oppResponsibleUserInfo = $insightly->getUser($oppResponsibleUserID);

			if(isset($oppResponsibleUserInfo)){
				$oppResponsibleUserFName = $oppResponsibleUserInfo->FIRST_NAME;
				$oppResponsibleUserLName = $oppResponsibleUserInfo->LAST_NAME;
				$oppResponsibleUserName = $oppResponsibleUserFName . " " . $oppResponsibleUserLName;
				$oppResponsibleUserEmail = $oppResponsibleUserInfo->EMAIL_ADDRESS;
			}

			// In this case, there are a few custom fields we need to extract. 
			// We loop through the custom fields array and extract the data we require, using Insightly's numbered OPPORTUNITY_FIELD tags. 
			// To find these tags, edit an opportunity in Insightly, inspect element and look for input value="OPPORTUNITY_FIELD_##"
			foreach($oppCustomFields as $oppCustomField) {

				//Get Country of Citizenship custom field
				if(isset($oppCustomField->CUSTOM_FIELD_ID) && $oppCustomField->CUSTOM_FIELD_ID == "OPPORTUNITY_FIELD_5"){
					$oppCountry = $oppCustomField->FIELD_VALUE;
				} 

				//Get Source custom field
				if(isset($oppCustomField->CUSTOM_FIELD_ID) && $oppCustomField->CUSTOM_FIELD_ID == "OPPORTUNITY_FIELD_9"){
					$oppSource = $oppCustomField->FIELD_VALUE;
				}

				//Get Birthday custom field
				if(isset($oppCustomField->CUSTOM_FIELD_ID) && $oppCustomField->CUSTOM_FIELD_ID == "OPPORTUNITY_FIELD_12"){
					$oppBirthday = date("m/d", strtotime($oppCustomField->FIELD_VALUE));
				} 
			}

			// Use getContact(#ContactID) method to get contact record JSON.
			if(null !== $insightly->getContact($contactID)){
				$contact = $insightly->getContact($contactID);

				//Use default value of "student" if names not set. MailChimp may not accept the data without both names.
				if(isset($contact->FIRST_NAME)){
					$contactFName = $contact->FIRST_NAME;
				} else {
					$contactFName = "Student";
				}
				if(isset($contact->LAST_NAME)){
					$contactLName = $contact->LAST_NAME;
				} else {
					$contactLName = "Student";
				}

				$contactInfos = $contact->CONTACTINFOS;

				//Loop through contact details to get email and phone.
				foreach($contactInfos as $contactInfo){
					if(isset($contactInfo->TYPE) && $contactInfo->TYPE == "EMAIL"){
						$contactEmail = $contactInfo->DETAIL;
					} 
					if(isset($contactInfo->TYPE) && $contactInfo->TYPE == "PHONE"){
						$contactPhone = $contactInfo->DETAIL;
					} 
				}
			}

			//If contact info is set, create batch array in the format required by MailChimp
			if(isset($contactEmail)){
				// Creates batch[] array for Mailchimp import
				$batch[] = array('EMAIL'=>$contactEmail, 'FNAME'=>$contactFName, 'LNAME'=>$contactLName, 'MMERGE3'=>$oppCountry, 'MMERGE6'=>'International Student', 'MMERGE4'=>$contactPhone, 'CRMSTATE'=>$oppState, 'CRMOPPID'=>$oppId, 'MMERGE7'=>$oppSource, 'MMERGE8'=>$oppBirthday, 'CRMOPPOWNE'=>$oppResponsibleUserName, 'CRMOWNEMAI'=>$oppResponsibleUserEmail, 'CRMPIPELIN'=>$oppPipelineStageName); 	

				// Used for checking the variables are correct by running insightly-mailchimp.php locally. Remove or comment out on production.
				// echo $oppId;
				// echo "<br?>Responsible User Name: " . $oppResponsibleUserName;
				// echo "
Responsible User Email: " . $oppResponsibleUserEmail;
				// echo "
Opportunity Name: " . $oppName;
				// echo "
Opportunity ID: " . $oppId;
				// echo "
Opportunity Souce: " . $oppSource;
				// echo "
Opportunity Country" . $oppCountry;
				// echo "
Opportunity State: " . $oppState;
				// echo "
Pipeline Stage Name: " . $oppPipelineStageName;
				// echo "
Linked Item Role: " . $oppLinkRole;
				// echo "
Opportunity Updated Date: " . $oppUpdated;
				// echo "
Contact ID: " . $contactID;
				// echo "
Birthday: " . $oppBirthday;
				// echo "
Contact First Name: " . $contactFName;
				// echo "
Contact Last Name: " . $contactLName;
				// echo "
Contact Email: " . $contactEmail;
				// echo "
Contact Phone Number" . $contactPhone; 
				// echo "

";	

			}
		} // End if statement to filter data
	} // End opp links loop
	
} //End opportunities loop

// END INSIGHTLY DATA

// START MAILCHIMP IMPORT

if(isset($batch)){ // Send confirmation email when data transferred.
	$api = new MCAPI($apikeyMC); // Connect with MailChimp

	// Set MailChimp options
	$optin = false; //no, don't send optin emails
	$up_exist = true; // yes, update currently subscribed users
	$replace_int = false; // no, add interest, don't replace

	// Use listBatchSubscribe method to import $batch to MailChimp
	$vals = $api->listBatchSubscribe($listId, $batch, $optin, $up_exist, $replace_int);

	// Report errors, if there are any
	if ($api->errorCode){
		
	    echo "Batch Subscribe failed!\n";
		echo "code:".$api->errorCode."\n";
		echo "msg :".$api->errorMessage."\n";
	} else {
		echo "added:   ".$vals['add_count']."\n";
		echo "updated: ".$vals['update_count']."\n";
		echo "errors:  ".$vals['error_count']."\n";
		
		foreach($vals['errors'] as $val){
			echo $val['email_address']. " failed\n";
			echo "code:".$val['code']."\n";
			echo "msg :".$val['message']."\n";
			$errorList = $val['email_address'] . "Error Code:".$val['code']."\n Error Msg :".$val['message']."\n";
		}

	// Send email with Mailchimp Errors to see what was imported and where errors occurred.
		$to = $completionToEmail; // This is set in config.inc.php
		$subject = "Insightly to Mailchimp Transfer Completed [" . $today . "]. Errors:" . $vals['error_count']."\n; Added: ".$vals['add_count'] ."\n; Updated: ". $vals['update_count'] ."\n";
		if(isset($errorList)) {
			$body = "**Some errors occurred during import**\n" . $errorList;
		} else {
			$body = "Import done with no errors. Woo hoo!";
		}

		// Set headers for the the notification email - variables all set in config.inc.php
		$headers = "From: " . $completionFromEmail . "\r\n";
		$headers .= "Reply-To: " . $completionReplyEmail . "\r\n"; 
		$headers .= "Cc: " . $completionCCEmail . "\r\n";

		//Send email
		mail($to, $subject, $body, $headers);

	}

// If there are no new records to transfer, we still send a notification saying just that.
} else { 
	echo "No updated records to import";
	//Send email confirming completion, but with no new records.
	$to = $completionToEmail; 
	$subject = "Insightly to Mailchimp Transfer Completed [" . $today . "]. No updated records to import";
	$body = "Nothing new to update... Have a good day :)";

	// Set headers
	$headers = "From: " . $completionFromEmail . "\r\n";
	$headers .= "Reply-To: " . $completionReplyEmail . "\r\n"; 
	$headers .= "Cc: " . $completionCCEmail . "\r\n";

	//Send email
	mail($to, $subject, $body, $headers);
}
//END MAILCHIMP

?>

Now the script is ready, I need to get it to run automatically…

Setup a cron job to run the script automatically

Set timing for cron job
Set timing for cron job

First, I had to ponder the email campaigns that I had planned earlier. Did I need the emails to get out within an hour of the Insightly data being updated? Or was it ok to just transfer the data and trigger the emails every 24/48/n hours? In my case, the email automation was not ultra time sensitive, so transferring data once per day was sufficient. Other use cases may require data to be transferred more or less often, so just set the cron job to run hourly, twice-daily, or whatever floats your boat.

Setting up the cron job is easy with CPanel’s interface. Go to Cron Jobs (under the Advanced header) -> select the timing. Timing can be adjusted to suit your desired transfer schedule; I set mine to run at 11pm every night to ensure it picks up all changes made that day.

Then in the Command box, I entered the code below (being sure to add the direct path to the folder), then click Add New Cron Job.

1
/usr/local/bin/php -q /[DIRECT-PATH-TO-THE-FOLDER]/insightly-mailchimp/insightly-mailchimp.php
/usr/local/bin/php -q /[DIRECT-PATH-TO-THE-FOLDER]/insightly-mailchimp/insightly-mailchimp.php

Every server is different, so if you have trouble with your cron job, Google is your friend!

Finally, when I first setup the script, I did a preliminary run to get all the Insightly data transferred to MailChimp so I could create the segments.

Create custom segments

Custom segments in MailChimp
Custom segments in MailChimp

Now the technical stuff is out of the way, it’s time to make the magic happen! MailChimp’s list management is excellent, and offers a spunky interface for creating custom segments. To access segments in MailChimp, go to Lists -> Manage Subscribers -> Segments -> Create Segment, then add the conditions using the drop down menus. If you have super-intricate segments, you can use MailChimp’s power segmentation tool, Hairball, but I found the web interface more than adequate for my needs, so I went ahead and created the custom segments I had planned earlier.

Set automation workflows in MailChimp

Creating email automation workflow trigger in MailChimp
Selecting email automation workflow trigger in MailChimp
Setup automated emails in MailChimp
Setup automated emails in MailChimp

MailChimp’s knowledge base has a great article on setting up an automation workflow, so I would recommend reading that first.

To create a workflow I clicked Automation (left sidebar menu) -> Create Automation Workflow (top right) -> Select our list from the dropdown -> choose Custom workflow -> Configure settings (name, tracking, etc) -> click Next. This took me to the Configure Trigger screen.

I needed to trigger emails when a data field changed, so I selected when “Merge field value changes” from the dropdown menu, as in the screenshot above. Then I chose the merge field and selected the value. This must match the data exactly, so take casing in to account.

Next I chose the target segment (the dropdown includes the segments I created earlier), set the timing (hopefully you will already have an idea of the best time to send emails), then hit Next to go to the email creation screen.

I wanted to create each of the emails that I’d planned earlier, so I clicked Add Email and then hit Design Email to create each email in the series, making sure I set the delay for each email. MailChimp’s automation features allows the full power of templates and the WYSIWYG editor, so you can go nuts!

Finally, continue onto the Confirmation screen, double check everything is setup properly, fix any issues and hit “Start Workflow” to launch your campaign in to the stratosphere…

Phew!

That’s it! Lots to take in there. It took me a few days to write the script and get this all set up, but now we have completely automated email marketing using Insightly and MailChimp. I am able to automatically target pipeline stages and personas, giving our customers what they need in a timely manner, and hopefully increasing conversions! Time to pat myself on the back and do a little shake your booty dance!

Do you use Insightly? Are you automating marketing? Any other comments or questions? Let me know in the comments below or catch me on Twitter @ciaransm.

Share This:

4 Responses to “Email Marketing Automation with Insightly and Mailchimp”

  1. Dear Kiaran,
    thanks for the great post!
    We are also using insightly and mailchimp. I have a question, that you probably yould help me with:
    I am looking for a way to identify customers from insightly, that clicked on a link in a mailchimp message (and e.g. by this visited my website).
    So far I can only see, that x% opened the email and z% clicked on a link.
    But I do not know, WHO clicked on it.
    It would be great e.g. to contact those people, who actually clicked on a link.
    Is there any solution to that problem, that comes to your mind?
    Thanks so much,
    Matthias

    • Hi Matthias,

      You can probably get this data in Google Analytics with some hacking, but you need to be careful about tracking identifiable information. You could just transfer the opportunity ID across to MailChimp, then use this within a tracking url query string or UTM param. Then parse the query string and send custom data to your analytics software (no need to send custom data if you just use UTM params). If you want to track more detailed user information, you can use MixPanel which has a nice people tracking feature. I will do some testing and write a blog post once I sort it out.

      Cheers

  2. Dear Kiaran,
    Thanks a lot for this valuable insight.

    Have you tried out any 3rd party solutions to integrate MailChimp and Insightly?
    I’ve been looking for this sort of tools for a while and there are a few out there like Zapier (one of the most well known).

    I just signed up for Podbox -https://www.podbox.com/#!/MailChimp/Insightly- which provides integration for a wide range of cloud olutions.

    Have you got any feedback about this tool?

    Thank you,
    John

    • Hi John,

      I do use Zapier as well as my own solution. I haven’t tried Podbox. Maybe I’ll try it if I have the need, but I do like the flexibility and customization my build gives me.

      Thanks for stopping by!

      Cheers
      C

Leave a Reply to Matthias Möller