Videos

    Using the Email Query component in Matillion ETL for BigQuery

    Matillion uses the Extract-Load-Transform (ELT) approach to delivering quick results for a wide range of data processing purposes: everything from customer behaviour analytics, financial analysis, and even reducing the cost of synthesising DNA.

    The Email Query component in Matillion ETL for BigQuery presents an easy-to-use graphical interface, enabling you to pull data from your email server directly into BigQuery. Customers are use this to pull email data into BigQuery, say from a Marketing account, to combine with other data to measure email effectiveness.

    The connector is completely self-contained: no additional software installation is required. It’s within the scope of an ordinary Matillion license, so there is no additional cost for using the features.

    Video

    Watch our tutorial video for a demonstration on how to set up and use the Email Query component in Matillion ETL for BigQuery.

     

     

    Email Providers

    Matillion supports the below email providers out of the box:

    • AOL
    • Gmail
    • Outlook
    • Yahoo
    • Zoho

    “Other IMAP” is also available in the Provider drop down menu to allow you to pull data from other IMAP based servers.

     

    Using the Email Query component in Matillion ETL for BigQuery - Email Provider Gmail

     

    You must set up your email services to allow access to lower-security apps. Consult your provider for details on how to achieve this.

    We are going to pull some data from a test Gmail account:

     

    Using the Email Query component in Matillion ETL for BigQuery - Gmail

     

    User Account

    The first step in configuring the Email Query component is to provide the email address to pull the data from. This is populated in the User property:

     

    Using the Email Query component in Matillion ETL for BigQuery - User Account

     

    Account Password

    The password is the password you would use to log into the email account via the web interface.

    Leave the IMAP Server and IMAP Port as per the default, unless you are using the Other IMAP option. If you are using other options you will need to configure to your email providers specification.

    Data Source

    Next, choose the data you want to load into BigQuery from the Data Source drop down. This is a list of folders in your email account:

     

    Using the Email Query component in Matillion ETL for BigQuery - Data Source

     

    After choosing the data source, the next step is to choose the required fields from the emails in the Data Selection. This will form the new table which you will create in BigQuery.

     

    Using the Email Query component in Matillion ETL for BigQuery - Data Source Properties

     

    Data Source Filter

    You may wish to add a filter to the emails brought through into BigQuery using the Data Source Filter. Customers often filter the emails on date.

    Connection Options

    These are some additional parameters which are supported by the driver. No Connection Options are mandatory with the Email driver as sensible defaults are usually given. However further details on the options are available here.

    Running the Email Query component in Matillion ETL for BigQuery

    Before you can run the component, give the Target Table a name. This will be the name of a new table which you are creating so that you write the data into in BigQuery. Also, you will need to specify a Cloud Storage Staging Area. This is a Cloud Storage bucket used to temporarily store the results of the query before it is loaded into BigQuery.

    This component also has a Limit property feature which you can use to force an upper limit on the number of records returned. We recommend using either a limit or a filter to reduce the number of rows returned and to improve the speed of your job.

    You can run the Orchestration job, either manually or using the Scheduler, to query your data and bring it into BigQuery.

     

    Using the Email Query component in Matillion ETL for BigQuery - Job Successful

     

    Advanced mode

    The Email Query component offers an “Advanced” mode instead of the default “Basic” mode. In Advanced mode, you can write a SQL-like query over all the available fields in the data model. This is automatically translated into the correct API calls to retrieve the data requested.

     

    Using the Email Query component in Matillion ETL for BigQuery - Advanced Mode

     

    Using the Email Query component in Matillion ETL for BigQuery - Advanced Mode SQL

     

    Transforming the Data

    Once you have brought your required data into BigQuery from your email service provider, you can use it in a Transformation job, perhaps to enhance existing data:

     

    Using the Email Query component in Matillion ETL for BigQuery - Transformation Job

     

    In this way, you can build out the rest of your downstream transformations and analysis, taking advantage of BigQuery’s power and scalability.

    Useful Links

    Email Query component in Matillion ETL for BigQuery
    Component Data Model
    Integration information
    Video

    Want to try the Email Query component in Matillion ETL for BigQuery? Request a free demo now, or launch on the Google Cloud Launcher.