Send SendGrid Data to BigQuery
How to Send SendGrid's email engagement data to Google BigQuery
Capturing data from multiple sources is the key to creating a rich data warehouse. And it becomes even more crucial when it comes to online businesses as the data is scattered across a plethora of platforms used to run the business.
SendGrid is one such marketing platform that delivers transactional and marketing emails to help keep users informed and engaged.
We will go over different methods to store SendGrid email data to our data warehouse tool.
SendGrid Webhooks
SendGrid provides several ways to export its data out. One such way is to use SendGrid webhooks. Webhooks are automated messages sent from apps when something happens. So whenever SendGrid sends an email a webhook is trigger containing information about the email that was sent.
SendGrid event webhooks are available for the following email delivery and engagement events
So SendGrid triggers a webhook for all of the above events and passes the information along with it. Following is an example of information that it sends on an open event.
This information is called payload. See here for example payloads for other SendGrid events.
Data Pipeline
The blend of SendGrid’s webhooks and Google Cloud Function can help to capture all the data needed in BigQuery.
Steps
there are three steps involved in setting up the data flow.
- Create a BigQuery Table with a schema based on the data you want to store from SendGrid. Example email, event, timestamp, category.
- Set up a cloud function that can receive a webhook call from SendGrid, extract the information required, and passed it to BigQuery.
- Configure webhook in SendGrid.
And Done!
Now let’s work to actually implement all the above-mentioned steps.
Note it requires quite a bit of technical and programming background. So I will advise asking a technical person in your team to implement the solution.
Data Pipeline Implementation
1- BigQuery Table
We first have to create a table in BigQuery that can store the SendGrid data. Enter the column name as shown in the below image.
Name the table as sendgrid_events.
2- Cloud Function
Next, we will have to create a cloud function that has two responsibilities. The first is to get the data passed by SendGrid. The second is to process and store that data in the BigQuery table we created above.
We will be using Node.js to program our cloud function. Here is the configuration we will be using for it.
Once configured add the below code in index.js to parse the data as needed.
Replace my_project, my_database according to your GCP project. The above code parses the data send from SendGrid and then appends it to BigQuery
Also, add BigQuery dependency in package.json.
Copy the Cloud function triggering URL and we are good to go to the final step.
3- Webhook Configuration
In SendGrid UI goto Settings > Mail Settings. From there click edit to enable webhooks.
A window will open. Enter the cloud function URL and check all the events to be posted as depicted below.
Click the ‘Test Your Integration’ button to send test events to the cloud function for you to see how those are stored in BigQuery. Here is how it looks
Did you note we are also storing event_payload as it is? The reason being is that we can extract more information out from the payload for any event if needed.
Hit the save button in the webhook configuration window and we are done.
Following the above steps will get you all the SendGrid data you need in near-realtime in BigQuery to be processed and worked upon.
If the implementation is too technical for you and wants to learn more yourself then here is an excellent course to start data engineering in GCP.
