Challenge
There is an inherent challenge when working with mautic and setting up unique discount codes. There are many instances where you might want to distribute a unique one time discount code.
We have found this necessary in a few instances for example:
- Signup to newsletter and get a unique discount code
- Single use, time limited, unique Abandoned Cart discount code
Solution
In order to do this we have taken an approach of a non-programmer and something that can be implemented by most tech savvy people.
We are using simple bash scripts along with mysql database and a webhook server.
Required
In order to implement this solution yourself you will need the following (and if not the ability to use Google)
- Basic understanding and navigation of Linux
- Basic understanding of mysql
- Access to your server (I have only implemented this on Ubuntu)
- Mautic Setup and working.
- Access to WooCommerce & Ability to install plugin
Setup Procedure
First thing is we want to create a separate database where we can track our unique discount codes.
From your shell terminal (where you have Mautic installed) lets start to create the database:
mysql -u root -p(your password if you require one)
create database DiscountSystem;
create table DiscountCodes (
id INT AUTO_INCREMENT PRIMARY KEY,
coupon_code VARCHAR(255),
lead_id VARCHAR(20) NOT NULL,
date_assigned DATE NOT NULL,
);
So what did we do above?
We created a new table with the following columns
ID which is auto incremented.
Coupon Code: This will be used to hold all of our unique discount codes that we generate (you will see later how to generate unique discount codes and import them into the table)
Lead_ID: This is the user ID of the user from Mautic which is held in the leads table.
Date Assigned: This will be the date that we distribute the discount code to the user.
Generate Unique Discount Codes
Ok now we have the database that will be able to track things for us, and what is needed now is to actually fill up the database with our unique discount codes.
You will need to generate a bunch of unique discount codes from whatever system you are using. We have gone ahead and done this through wordpress using WooCommerce.
There is a free plugin to generate bulk discount codes (Coupon Generator for WooCommerce).
Go ahead and create a bunch of unique discount codes here. Remember to make the codes only usable once and once per user.
Once you have a list of all the codes you have created export these to a csv file. Now transfer the file to your mautic instance.
Importing Unique Discount Codes to your DiscountSystem DB
Now there are a number of ways to do this, and I am sure there are easy ways than the way I am suggesting, but this is how I figured out how to do it:
Lets say your file name is unique_discount_codes.csv
In your terminal write the following command:
for x in `cat unique_discount_codes.csv`l do mysql -u root DiscountSystem -e “insert into DiscountCodes set coupon_code = ‘$x’ “; echo $x; done
The only reason I have added in the echo $x is so you can see things working.
What this will do is updated your database with all your discount codes.
In order to see this you can login to mysql and check this:
Mysql -u root DiscountSystem -p
Once inside: select * from DiscountCodes;
This will bring up a list of all your discount codes.
Installing WebHook Service
In order for our Mautic campaign to communicate with our new database and start to distribute unique discount codes I needed to find or write something that could accept webhooks.
I was lucky enough to stumble across webhook.d on github. This little tool is great, it will listen for webhooks and can then run scripts or do so many other things, so in order for this to work you need to go and download it from here.
In order to get it from your terminal do the following:
sudo curl -s https://raw.githubusercontent.com/ncarlier/webhookd/master/install.sh | bash
This will install the daemon in your root directory inside .local/bin
Follow the directions on the setup and you should be good to go. I would also recommend starting this as a service so if your server reboots it starts up automatically.
You should add some basic security (http basic authentication: htpasswd -B -c .htpasswd api) here and add username and password.
Once you have done this, you will need to go and generate an encoded version of username:password. This you can do by going to this link: https://mixedanalytics.com/knowledge-base/api-connector-encode-credentials-to-base-64/
You will get a string that looks similar to this: YXBpOmFwaTEyMw==
Preparing the Mautic Environment
I am going to focus on the example of an abandoned cart, but the principal is the same for any type of application you can think of.
I am assuming the following:
- That you already have a way of capturing an abandoned cart and it is being synched with your Mautic.
- You have a segment created capturing your users for abandoned carts today.
Go ahead and create the following custom fields:
abandoned_cart | Boolean | Yes|No |
discount_code | Text |
Lets also go ahead and setup the beginning of our Abandoned Cart Campaign.
First create a simple Template Mailer. Something like this (name of mailer = abandoned_cart_mailer-1):
You forgot something in your cart, here is a 10% discount to get things rolling.
{contactfield=discount_code}
Save that and publish it. This is only an example, there are so many other ways to approach abandoned cart content :-).
Go and setup a new campaign.
Start the campaign off with the segment of Abandoned_Carts_Segment.
Then add an action to “Send a Webhook” [we will deal with what we want to put in this a bit later.
Add an action to “Send Email” add in a 5 minute delay and choose the mailer you created (abandoned_cart_mailer-1)
The Technical’ish Stuff
We now need to find a way to capture information from Mautic and then do certain things to our database.
So what we want to happen is as soon as a user is flagged as an abandoned cart user they will enter your segment, and then enter the campaign, then this webhook thing is triggered and then the email is sent with the persons unique discount code.
Ok so back to terminal.
We now need to write a bash script that will understand how to tack the user id and assign the first free discount code and then send this discount code back to mautic and update the custom field we created discount_code.
First go into your root directory and do the following:
cd .local/bin
mkdir scripts
nano distribute_discount_codes.sh
#!/bin/bash
# This script will take care of assigning a unique distribution code to abandoned cart users
# First Get the last coupon_code that was assigned to a user.
current_id=`mysql -u root DiscountSystem -p -sN -e “select id from DiscountCodes where lead_id is null LIMIT 1″`
# The below line can be commented out, but i kept it in for testing
echo $current_id
# Set the date of today in the correct format
today_date=$(date ‘+%Y-%m-%d’)
echo $today_date
# Insert new ID into the DB and assign it to a coupon code and update the assigned_date
mysql -u root DiscountSystem -p -e “update DiscountCodes set lead_id = ${id}, date_assigned = ‘”$today_date”‘ where id = “$current_id” “
# Pass the coupon code back to the AMS
woo_discount_code=`mysql -u root DiscountSystem -p -sN -e “select coupon_code from DiscountCodes where lead_id = ${id} order by id desc limit 1″`
mysql -u root mautic -p -e “update leads set discount_code = ‘”$woo_discount_code”‘ where id = ${id}”
Save and exit the file. You now need to make this file executable so type in the following
chmod 755 distribute_discount_codes.sh
Time for a coffee before putting the final touches together and praying that it all works 😀
Lets go back to our campaign now and open the action “Send Webhook”
In the URL, type in the following: http://localhost:8080/distribute_discount_codes
In Method choose POST
In Headers click “Add a Value and add 2 values.
In the first value type in “Authorization” in the second tab.