Email Campaigning
Have you ever received an email and opened it, only to receive another email that is tailored more toward what you viewed? This is email targeted campaigning, ands can be done in several ways. A campaign email system allows you to track several points of interaction with an email to gain the most insights from a users. The basics are, is the email sent, did the user open the email, and what links did the user click, along with how many times the email was opened or clicked by the user. Tracking who is sent an email can be easily handled with our code, if the email does not error out it was sent. Tracking a link click is easy too, each link inside the email can be formed to include a unique identifier and user information from the campaign system the interaction.. But what happens if you don't click a link how does the company know you were interested? There must be something in the email to do this?
Simple, an image. These images are usually referred to as 0 pixel images which are image tags that reference a URL instead of an image. So when the email loads the image it is actually logging the interaction with the email.
In this post, we are going to create an email campaign system in 5 minutes.
Creating an Email Campaign in 5 Minutes
Setup SQL
The first thing we need to do to setup our email campaign is create a database to capture our campaign details. I will be using Azure SQL to do this. This is pretty straight forward, choose your plan and follow the steps. Once done, make sure to add your IP to the firewall. You will need at least one SQL database to add our tables.
For this post, we will be setting up 3 simple tables, one for creating a campaign, one for tracking if an email is sent, and one for tracking if an email is opened. Thee commands can be run from SQL Server management studio or the Query Editor Preview (at the time of this writing) feature in the Azure SQL section .
CREATE TABLE Campaigns(CampaignId nchar(36) NOT NULL PRIMARY KEY, Subject nvarchar(255) NOT NULL)
CREATE TABLE sent_tracking(SentID nchar(36) NOT NULL PRIMARY KEY, Email nvarchar(255) NOT NULL, Sent bit NOT NULL, Campaign nchar(36) NOT NULL FOREIGN KEY REFERENCES Campaigns(CampaignId))
CREATE TABLE campaign_tracking(TrackingId nchar(36) NOT NULL PRIMARY KEY, Email nvarchar(255) NOT NULL, OpenDate datetime NOT NULL, Campaign nchar(36) NOT NULL FOREIGN KEY REFERENCES Campaigns(CampaignId))
Setup Azure Function
With our SQL database setup, we can now create an Azure function for tracking our emails that our opened. I will be using a VS code extension for creating the Azure Function this will allow me to create and deploy my function to Azure. This extension is aptly named "Azure Functions".
With our add-in installed, we can create and setup our Azure Function. If prompted, sign in to your Azure account. once you see your Azure subscription, click the lightning bolt with a plus next to it to create the function.
You will be asked a series of questions, the first is the language to create the function, this post I will use C#.
Next, VS Code will ask what type of function I want. I want the function to execute every time it is called, so HTTPTrigger is the project type.
The function needs a name, for the purposes of this blog I will name it EmailTrackingFunction. A namespace will be required as well.
Finally, Azure wants to know how to secure the function, no credentials will be used so it will be anonymous for this example.
Our Azure function will capture the user's email address and what time they opened the email. Every time the email image is loaded, our system will make an entry into our SQL database.
using System;
using System.Collections.Generic;
using System.Net;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Http;
using Microsoft.Extensions.Logging;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;
public static class EmailTrackingFunction
{
[Function("EmailTrackingFunction")]
public static HttpResponseData Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post")] HttpRequestData req,
FunctionContext executionContext)
{
var logger = executionContext.GetLogger("EmailTrackingFunction");
// create connection to local settings
var config = new ConfigurationBuilder()
.AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables()
.Build();
string cs = config.GetConnectionString("EmailTrackingDB");
//connect to sql db
using(SqlConnection connection = new SqlConnection(cs))
{
//insert our tracking data into our database
var queryStrings = System.Web.HttpUtility.ParseQueryString(req.Url.Query);
connection.Open();
using(SqlCommand cmd = new SqlCommand("INSERT INTO campaign_tracking (TrackingId, Email, OpenDate, Campaign) Values(@TrackingId, @Email, @OpenDate, @Campaign)", connection))
{
cmd.Parameters.AddWithValue("@TrackingId", Guid.NewGuid());
cmd.Parameters.AddWithValue("@Email", queryStrings.Get("Email").ToString());//email that opened link
cmd.Parameters.AddWithValue("@OpenDate",DateTime.Now); //time email was opened
cmd.Parameters.AddWithValue("@Campaign", queryStrings.Get("Campaign").ToString()); //ID linked to another table with campaign details
cmd.ExecuteNonQuery();
//log that email entry was made
logger.LogInformation("Logged {0}", queryStrings.Get("Email").ToString());
}
connection.Close();
}
//create a successful response to return in the function
var response = req.CreateResponse(HttpStatusCode.OK);
return response;
}
In order to connect to the SQL database, we must reference our connection string. When running locally, we can setup our local.settings.json file to read the connection string. This file does not deploy to Azure, but will allow you to setup a config to mimic the configuration settings in Azure.
{
"IsEncrypted": false,
"Values": {
"AzureWebJobsStorage": "",
"FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated"
},
"ConnectionStrings" : {
"EmailTrackingDB": "{sql connection string}"
}
}
With the function created, the final step is to deploy it to Azure. To do this, click the Up arrow in our add-in. If this is the first deployment, it will ask to create the Azure Function or select an existing one.
Now when you log back into Azure, you should see your newly created function under app services. The final thing we must do is configure the connection string in Azure, since the local.settings.json file will not deploy to our function. Inside the configuration of our function, there is a section for connection strings. All that needs to be done is copying and pasting the connection string here and save the updated configuration.
Create Console App for sending emails
The email tracking function is now setup, but in order to test the function an email job must be created. To do this, I will just create a console application that sends emails through Gmail. Gmail allows us to create a SMTP connection and send out emails using that email address. The trick is to allow the app to send emails by allowing less secure connections.
With Gmail configured, we can create our app. I am using SMTP to send the email. Along with creating the entries for our campaign so that we can track how many people open the email.
For our example to work we need to create a campaign with a unique id and then send and email. We will just do a simple insert into SQL to create our campaign, then we will take our unique ID and append it to a URL for our image to call back to the Azure function.
Before creating the function, the project will need references to SQL Client and Mail Client
using System;
using System.Net.Mail;
using System.Data.SqlClient;
Once the reference to the clients are added, a function for inserting data into the campaign will be needed.
static string CreateCampaign(string Subject)
{
//Replace with your sql connection string
string cs = "Server=tcp:sqlconnection,1433;Initial Catalog=trackingdata;Persist Security Info=False;User ID={sql user};Password={sql password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
string campaignId = Guid.NewGuid().ToString();
using(SqlConnection connection = new SqlConnection(cs))
{
//insert our data into our campaing table to track what campaigns are sent.
connection.Open();
using(SqlCommand cmd = new SqlCommand("INSERT INTO Campaigns (CampaignId, Subject) Values(@CampaingId, @Subject)", connection))
{
cmd.Parameters.AddWithValue("@CampaingId", campaignId);
cmd.Parameters.AddWithValue("@Subject", Subject);//email that opened link
cmd.ExecuteNonQuery();
}
connection.Close();
}
//return our Unique campaign id
return campaignId;
}
Inside the main function, I will call the SQL function that returns a unique id and append it to the URL. Then to test I will send the URL to 1 email address.
static void Main(string[] args)
{
string subject = "Testing Pixel";
string fromEmail = "{from email}"; //replace with who is sending email
string toEmail = "{to email}"; //test, this would usually be a list of users
string campaignId = CreateCampaign(subject); //creates the campaign and returns guid
string baseUrl = "https://{azure function}/api/EmailTrackingFunction"; //our azure function url
string imgUrl = $"{baseUrl}?Email={toEmail}&Campaign={campaignId}";// our url for our image
//connect to gmail
using(SmtpClient client = new SmtpClient("smtp.gmail.com"))
{
client.Credentials = new System.Net.NetworkCredential(fromEmail, "from email password");
client.Port =587;
client.EnableSsl = true;
//create our email
using(MailMessage msg = new MailMessage(fromEmail, toEmail))
{
msg.Subject = subject;
msg.Body = $"Mass mailout
";
msg.IsBodyHtml = true;
client.Send(msg);
//would log time/date email was sent in a database here
}
}
That's it, we now have a successful email campaign system for sending and tracking mass mailouts.
Clone the project
You can find the full project on my GitHub site here https://github.com/fiveminutecoder/blogs/tree/master/EmailTracker
You can find the full project on my GitHub site here https://github.com/fiveminutecoder/blogs/tree/master/EmailTracker