Monday, May 17, 2021

Create an Email Tracking Campaign using Azure Functions

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


Microsoft, C#,C Sharp, Authentication, Azure Functions, SMTP, GMail, Azure App, application, SQL, SQL Server, Azure SQL, Transact-SQL, dot net, dot net core CMS, Content Management System

No comments:

Post a Comment

C#, C sharp, machine learning, ML.NET, dotnet core, dotnet, O365, Office 365, developer, development, Azure, Supervised Learning, Unsupervised Learning, NLP, Natural Language Programming, Microsoft, SharePoint, Teams, custom software development, sharepoint specialist, chat GPT,artificial intelligence, AI

Cookie Alert

This blog was created and hosted using Google's platform Blogspot (blogger.com). In accordance to privacy policy and GDPR please note the following: Third party vendors, including Google, use cookies to serve ads based on a user's prior visits to your website or other websites. Google's use of advertising cookies enables it and its partners to serve ads to your users based on their visit to your sites and/or other sites on the Internet. Users may opt out of personalized advertising by visiting Ads Settings. (Alternatively, you can opt out of a third-party vendor's use of cookies for personalized advertising by visiting www.aboutads.info.) Google analytics is also used, for more details please refer to Google Analytics privacy policy here: Google Analytics Privacy Policy Any information collected or given during sign up or sign is through Google's blogger platform and is stored by Google. The only Information collected outside of Google's platform is consent that the site uses cookies.