There are some scenarios when you wish Mautic would have just a little bit more brain. Sometimes you are one step away from great marketing automation, íbut you would need to parse another database first to make your idea work. I have a workaround: I call it the conversion table.

Let’s imagine the two following scenarios:

The Webshop
A webshop has 2000 products organised into 50 Subcategories. These subcategories can be organised into 5 interest groups. Marketing wants to set up automation based on these 5 interest groups.

The Local Service Provider
The local seller offers services for people from a certain area around him. He collects zip codes, and communicates with the potential leads based on the zip codes they have.

All of these cases require a simple workflow, where data comes in, a query is made (is this zipcode in my area OR which interest group does this product belong to), and a response value is saved in the profile of our contact. Based on this value further steps, decisions, segmentation can be made.

Use a conversion table

This method is super simple, you only require a couple lines of code and a csv file containing the information you want to use. There is no modification to Mautic, it will work even if Mautic is upgraded.

Let’s take our example above. A team of plumbers divides the city into 3 service areas. Based on the zipcode different experts will be alerted.

  1. Create a custom field called Area served
  2. Create a form that contains a zip code
  3. Contact fills out the form
  4. The form starts a campaign where a webhook performs the query by passing the email address and the zip code
  5. Script responds fills out the proper field with the matching info. (1, 2 or 3 as service area.)
  6. If someone fills out the contact form (or the Area served is not empty), a new campaign starts now with the notifying the right plumber and sending his contact infos to the client.

As you see the whole mechanism is based on: Is this value is this, then that value should be that. You could say, that Mautic already has this function, you can use conditions to achieve the same thing.
And you are theoretically right. But what if you have to check 20 conditions? And what about 10.000? You don’t want to create campaign monsters, not to mention that csv files are easy to edit and replace.

The script

So how would such a script work? I’ve got you covered, here is a detailed explanation, and you can also download the whole commented script if you are a member here. You can register any time, it’s free.

Create a csv with the corresponding values. Our plumbers would have a column with zip codes, and service area value:

90106,1
90107,1
90108,2
90109,3

Our webshop would use this table for saving the product group interest based on SKU codes:

3302,'girl toy'
3308,'boy toy'
3310,'unisex toy'

Read the csv and return the right value
The following script looks through your csv, and by finding the first result in the first column, it will return the value of the second column.

function processList($filename,$search){
        global $loginname;
        global $password;
        global $siteurl;
        // search for value in csv
        $f = fopen($filename, "r");
        $result = false;
        while ($row = fgetcsv($f)) {
        if ($row[0] == $search) {
                $result = $row[1];
                break;
                }
        }
        fclose($f);
        return($result);
}

Please capture the right fields with the POST or GET method (depending on what you want to use.)

$filename = $_POST['filename'];
$search = $_POST['search'];
$email = $_POST['email'];
$alias = $_POST['alias'];

Making the API call is super easy:

First we need to give values to login name, password and site url, since we will use simple authentication.

$GLOBALS['loginname'] = 'yourloginname';
$GLOBALS['password'] = 'yourpsw';
$GLOBALS['siteurl'] = 'mautic.yourwebsite.com';

Api calls are pretty simple: use your login info to pass a new value to a contact with the given email address.
We do it by using the alias or the field or custom field. Example:

'city' = 'San Francisco'

In our example, we will make the alias into a variable, and so the value we would like to pass.

$curl = curl_init();
// Set some options - we are passing in a user agent too here
   curl_setopt_array($curl, array(
   CURLOPT_RETURNTRANSFER => 1,
   CURLOPT_URL => "https://".$loginname.":".$password."@".$siteurl."/api/contacts/new",
   CURLOPT_USERAGENT => 'CSV Conversion Table',
   CURLOPT_POST => 1,
   CURLOPT_POSTFIELDS => array(
      'email' => $email,
      $alias => $value)
      )
   );
// Send the request & save response to $resp
$resp = curl_exec($curl);
// Close request to clear up some resources
curl_close($curl)

Our goal is to have just one function, which receives all the information needed, and modifies the right values. So why don’t we pass all the info. Let’s modify the function as follows:

function processList($filename,$search,$alias,$email,){

The above mentioned is just a blueprint, but you can download the whole functioning in your download area once you log in or register for free.

You don’t have to save your script in your Mautic instance, you can store it on any server, just make sure you can make calls to it from the internet.

The Mautic Part

Let’s take the plumber example. Let’s say we collect the zip code from our clients in a form, and would like to set up the automation based on the Service area belonging to that zip code. Zip code is filled out by the contact, we make a call to the conversion table, retrieve the right service area and continue the automation based on this info.

We need to make the form and the custom field for our service area variable. Make sure you make note of the alias of this custom field, we will need it later.

The campaign would look like this:

Note, that we stop for 2 minutes after the webhook. This is enough time for us to process the webhook itself. The webhook call would look like this:

The info will be retrieved by our script from the CSV and added to the contacts profile:

Use case for webshops

Let’s say you have a webshop and you would like to process data with the conversion table. Let’s assume, we have a toy store, where we sell girls toys, boys toys, educational toys, family games, and costumes. We are focusing on cross sell, so girl toy – buyers would receive information mostly related to girls toys updates while purchasing a board game would result in a flow of board game offers from us.

You can easily connect Mautic with Wootic, creating a separate form entry for every product purchased. Using the conversion table you can turn these form entries into tags in our contact’s profiles, creating personas ready to be targeted. In other words we will turn product SKU into tags.

I mentioned Wootic before, which is a free plugin for Woocommerce. It helps you to collect data in a form. Please note, that we can capture the product id:

Our CSV would look contain the product ID and the TAG to be attached to our contact.

3322,boardgame
3308,girl_toys
3310,boy_toys
3312,educational
3320,boardgame
3324,boardgame

Now watch the magic happen right here: we don’t need a webhook in this case. We can use the post to another form action in our product capture form! (This is why I love Mautic so much!)

The form will pass only email and product ID to our script as variables. We can either set filename and alias as an invisible fixed value in the form, or we can just hardcode them.

Set the filename as fixed value:

$f = fopen('productist.php', "r");

And we make sure the retrieved value is added as a tag in our API call:

CURLOPT_POSTFIELDS => array(
      'email' => $email,
      'tag' => $value)
      )

Tags are a good choice here, because adding a new tag will be just added vertically, and not overwrite the previous tags:

You can download the whole script by registering here (free), or by in to your download area.

If you want to know how to create dynamic emails based on tags or other custom fields, you should check out the Marketing Automation Show Part 3!

1 Comment

  • Dirk Spannaus

    says:

    Hi Joey,
    nice idea, I’m missing the conversion table too 😉 Thanks for the workaround!

    One little improvement for your campaign – instead of having a timeout (which may still fail due to unexpected delays in your script environment). Couldn’t you check for this service area field value to be “not empty” before proceeding to the the next step? Thus it doesn’t matter how long it takes.

    It would be cool, If we had some kind of “router” element for the campaign builder, where we check a field for a value and directly connect next steps to the results – instead of checking for each value with its own campaign element…

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *