Real-time mail tracking in PowerBI with SendGrid and Azure

Hello,

Mail marketing are more and more important now in companies.  Marketers want to have a lot of data in order to take the best decision for the company they work for.

SendGrid is a cloud platform that help you to monitor a lot of information, such as open, click on link, delivered etc from your mails.

It provides you out of the box reports, but you will definitely want to go beyond and have your own reports.

In this blog post, I will explain you how to setup in PowerBI a real-time mail tracking, so you can make your own reports.

What do you need to perform this setup?

  • Azure Subscription
  • PowerBI license/access
  • SendGrid Account/Access
  • Visual Studio

For this post I assume that you know how to:

  • Deploy Web App in Azure from Visual Studio
  • Use C# to perform query on database
  • Create Database Schema
  • Optionally, you know ASP.NET MVC
  • Use PowerBI to connect and retrieve information from Database in Azure

The Setup

    1. Create a SendGrid account, it can be a free subscription. You can create it via your Azure tenant or via the website https://sendgrid.com/
    2. Log into Azure and create a Web App (https://portal.azure.com -> new -> search for web app -> select Web App and create it
    3. Retrieve and open the Visual Studio solution from here: https://github.com/thinkingserious/SendGridEventWebhookAzureASPNETCSharp

      As you can see in the model class SendGridEvents.cs, you can retrieve multiple information from your mail activity.

      public string email { get; set; }
      
      public int timestamp { get; set; }
      
      public int uid { get; set; }
      
      public int id { get; set; }
      
      [JsonProperty("sg_event_id")]
      public string sendgrid_event_id { get; set; }
      
      [JsonProperty("smtp-id")] // switched to underscore for consistancy
      public string smtp_id { get; set; }
      
      public string sg_message_id { get; set; }
      
      [JsonProperty("event")] // event is a protected keyword
      public string sendgrid_event { get; set; }
      
      public string type { get; set; }
      
      public IList<string> category { get; set; }
      
      public string reason { get; set; }
      
      public string status { get; set; }
      
      public string url { get; set; }
      
      public string useragent { get; set; }
      
      public string ip { get; set; }
      
      public string marketing_campaign_name { get; set; }
      
      public int marketing_campaign_id { get; set; }
      
    4. Based on the information that you’re interested to, create in Azure an SQL Database with your wanted schema. (https://azure.microsoft.com/en-us/documentation/articles/sql-database-get-started/ )
    5. In the Visual Studio of the point 3, use Entity Framework or … any framework you like, to connect and perform change into your Database.
    6. In the HomeController.cs class at line 38, this is where you have to persist your content based on the retrieved model.
    7. When done, deploy your solution to your Azure Web App created at the point 2
    8. Connect to your SendGrid account
    9. Go to the Event Notification menu, Settings -> Mail Settings -> Event Notificationsnip_20160612130104
    10. There you will have to turn on the Event Notification service
    11. In the HTTP Post URL, you need to add the URL of your Azure Web App created in point 2 and add /api/SendGrid at the endExample:   http://[MYWEBAPPNAME].azurewebsites.net/api/SendGrid
    12. Select the actions you want to monitor and then save the configuration
      After that configuration, automatically when a mail activity is performed/monitored by SendGrid, it will appear into the Mail Activity menu of SendGrid, but it will also make a POST request to your Azure Web App with all information.
    13. Now connect with PowerBI to your Database and start making nice report 🙂

 

Advertisements
This entry was posted in Azure, C# Solutions, Office 365 and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s