How to Easily Sync Data from SendGrid to BigQuery?

Unlock the full potential of your SendGrid data by integrating it seamlessly with BigQuery. With Hevo’s automated pipeline, get data flowing effortlessly—watch our 1-minute demo below to see it in action!

Does your company use SendGrid to manage its marketing email campaigns? Are you trying to move the data generated by SendGrid to BigQuery for deeper analysis? If any of this applies to you, then you will find this blog beneficial. This post will outline two methods of moving your data from SendGrid to BigQuery.

Introduction to SendGrid

SendGrid is a cloud-based email delivery platform that helps you effortlessly send emails to customers without having to maintain email servers. It allows you to send promotional emails, password resets, shipping notifications, and email newsletters. SendGrid manages time-consuming tasks such as scaling the infrastructure, reputation monitoring, ISP outreach.

Here are a few salient features of SendGrid:

• Scalability: SendGrid’s cloud-based infrastructure can scale easily to meet your growing demands. SendGrid allows you to send emails without having to worry about maintaining and managing your own email infrastructure. You can deliver all emails via their distributed cloud-based email platform to ensure reliability to your customers.
• Better Customer Experience: With SendGrid you can oversee all emails through global statistics and activity while creating a seamless experience with uniform template designs. You can also manage recipient preferences in a single location thus making it easier to follow anti-spam regulations.
• Seamless Collaboration: SendGrid allows you to build an email program that supports cross-team collaboration from troubleshooting and integration, to engagement tracking and design. You can also track the Return On Investment (ROI) for emails sent via marketing campaigns or email API.

Introduction to BigQuery

Google BigQuery is an enterprise data warehouse offered by the Google Cloud Platform. It is cost-effective, scalable, and enables super-fast analysis of large amounts of data. It has many features and capabilities such as the ability to run machine learning models, data management, data querying, data integration, and access control. It works in conjunction with Google cloud storage. Here are a few salient features of BigQuery:

• Built-in AI and ML Integrations: Google BigQuery integrates with TensorFlow and Vertex AI that allows you to train and execute powerful models on structured data in no time flat. All it takes is SQL.
• Natural Language Processing: With Data QnA, you can access the data insights they need through NLP while maintaining security and governance controls. Based on Analyza, Data QnA allows you to analyze petabytes of data through Google BigQuery. It can be embedded where users work, places like spreadsheets, chatbots, BI platforms like Looker, or custom-built User Interfaces.
• Serverless: Google BigQuery’s serverless Data Warehouse carries out resource provisioning behind the scenes which allows you to focus on analysis and data instead of worrying about securing, upgrading, or managing the infrastructure.

Understanding the Methods to Connect SendGrid to BigQuery

Here are the 2 methods you can use to connect:

Method 1: Using Hevo Data to Connect SendGrid to BigQuery

Hevo Data, a No-code Data Pipeline, helps you directly transfer data from Sendgrid (a Free Source) and 150+ other data sources to Data Warehouses such as Google BigQuery, Databases, BI tools, or a destination of your choice in a completely hassle-free & automated manner. Hevo instantaneously detects the schema of the data flowing from SendGrid and maps it to the relevant BigQuery table automatically. With Hevo, you can achieve data migration in two simple steps.

Step 1: Set up and configure your SendGrid platform by entering the Pipeline name and the API Key to move data from Hevo Data to SendGrid.

Step 2: Load data from SendGrid to BigQuery by providing your BigQuery database credentials such as your authorized Google BigQuery account, along with a name for your database, dataset id, GCS bucket, sanitize table/column names, destination, and project Id.

More Reasons to Try Hevo:

Simplify your Data Analysis with Hevo today! 

• Wide Range of Connectors – Instantly connect and read data from 150+ sources including SaaS apps and databases, and precisely control pipeline schedules down to the minute.
• In-built Transformations – Format your data on the fly with Hevo’s preload transformations using either the drag-and-drop interface, or our nifty python interface. Generate analysis-ready data in your warehouse using Hevo’s Postload Transformation 
• Near Real-Time Replication – Get access to near real-time replication for all database sources with log based replication. For SaaS applications, near real time replication is subject to API limits.   
• Auto-Schema Management – Correcting improper schema after the data is loaded into your warehouse is challenging. Hevo automatically maps source schema with destination warehouse so that you don’t face the pain of schema errors.
• Transparent Pricing – Say goodbye to complex and hidden pricing models. Hevo’s Transparent Pricing brings complete visibility to your ELT spend. Choose a plan based on your business needs. Stay in control with spend alerts and configurable credit limits for unforeseen spikes in data flow.
• 24×7 Customer Support – With Hevo you get more than just a platform, you get a partner for your pipelines. Discover peace with round the clock “Live Chat” within the platform. What’s more, you get 24×7 support even during the 14-dayfree trial.
  Security – Discover peace with end-to-end encryption and compliance with all major security certifications including HIPAA, GDPR, SOC-2.

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

Method 2: Using Custom ETL Scripts to Connect SendGrid to BigQuery

Broadly, the steps involved in achieving this is as follows.

• Step 1: Extracting Data from SendGrid using Web APIs
• Step 2: Transforming SendGrid Data
• Step 3: Loading the Data to the Data Warehouse

Prerequisites

In order to successfully extract data from SendGrid to BigQuery, you would need to have:

1. An understanding of working with Web APIs.
2. A fully-functional SendGrid account. You can learn how to set up an account here.
3. A successful set-up BigQuery data warehouse.

Step 1: Extracting Data from SendGrid using Web APIs

There are two methods you could use to extract data from SendGrid. Using Web APIs and SMTP. Two types of Webhooks are supported: The Event Webhook POSTs and the Inbound Email Parse Webhook. Event Webhook POSTs occur when an email event occurs such as unsubscribe and bounce rate. Inbound email parse webhook receives and posts parameters of an email such as the subject, body, and attachments.  

Below is a sample API call.

https://api.sendgrid.com/api/bounces.get.json?api_user=your_sendgrid_username&api_key=your_sendgrid_password&date=1

The response will be JSON data showing a list of bounced emails. 

[
  {
    "status": "4.0.0",
    "created": "2011-09-16 22:02:19",
    "reason": "Unable to resolve MX host sendgrid.ne",
    "email": "example@example.com"
  },
  {
    "status": "4.0.0",
    "created": "2011-09-19 17:47:15",
    "reason": "Connection timed out",
    "email": "example@example.com"
  },
  {
    "status": "5.1.1",
    "created": "2011-10-17 11:07:10",
    "reason": "550 5.1.1 The email account that you tried to reach does not exist. Please try double-checking the recipient's email address for typos or unnecessary spaces. Learn more at http://mail.google.com/support/bin/answer.py?answer=6596 z10si838244anz.171 ",
    "email": "example@example.com"
  },
  {
    "status": "5.7.1",
    "created": "2011-10-26 23:06:22",
    "reason": "550 5.7.1 <example@example.com>... Relaying denied. Proper authentication required. ",
    "reason": "550 5.7.1 <info@bnl.co.uk>... Relaying denied. Proper authentication required. ",
    "email": "example@example.com"
  },
  {
    "status": "5.7.1",
    "created": "2011-12-01 01:45:55",
    "reason": "554 5.7.1 <example@example.com>: Relay access denied ",
    "reason": "554 5.7.1 <richard@hengseng.com>: Relay access denied ",
    "email": "example@example.com"
  },
  {
    "status": "550",
    "created": "2012-06-01 14:10:15",
    "reason": "550 support.sendgrid.net [74.63.202.100] is currently not permitted to relay through this server. Perhaps you have not logged into the pop/imap server in the last 30 minutes or do not have SMTP Authentication turned on in your email client. ",
    "email": "example@example.com"
  }
]

Source: https://sendgrid.com/docs/API_Reference/Web_API/bounces.html

Step 2: Transforming SendGrid Data

To store the retrieved data, create a data schema. Identify a predefined data type such as INTEGER, BOOLEAN, DATETIME, etc. for each value in the response data and build a table that will retrieve them. 

Some of the retrieved records may not be “flat”. Some objects could be lists. Therefore, create additional tables to capture any unpredictable cardinality in each of the records. 

Step 3: Loading the Data to the Data Warehouse

A helpful guide is provided by GCP (Google Cloud Platform) when loading data into BigQuery. The bq load command is used to upload data. The syntax for the bq command is available here.

You may supply a partition schema or table or use schema auto-detection for supported data types and iterate this process until you load all table data into BigQuery.

SendGrid to BigQuery using ETL: Challenges and Limitations

• Infrastructure Maintenance: SendGrid has many APIs and may be updated at any given time. Hence, you may need a team of engineers to continuously monitor and update the code.  
• Real-time Data: So far, you have created a program that loads data from SendGrid to BigQuery. However, the challenge of loading new and updated data still exists. One way to solve this problem is to replicate data each time a row is created. This method works but it is resource-intensive and slow. Hence, you will need to write additional code to solve this problem.
• Ability to Transform: The above approach works only with JSON data. However, if the existing data is in XML format, you will need to clean/transform the data before loading it to the warehouse. 

Learn More About:

Working with Sendgrid Webhook

Conclusion

This blog talks about the two methods you can use to set up a connection from SendGrid to BigQuery: using custom ETL Scripts or with the help of a third-party tool, Hevo.

Share your experience of understanding the process of moving data from SendGrid to BigQuery in the comment section below! We would love to hear your thoughts.