SendGrid is a cloud-based SMTP provider that allows you to send email without having to maintain email servers.  With the Treasure Data integration, you can easily export profile segments to Sendgrid for all of your marketing email campaigns.



Prerequisites

  • Basic Knowledge of Treasure Data.

  • Knowledge of Sendgrid JSON email template.


Requirements and Limitations

There are several rate limitations and restrictions when using the Sendgrid connector features: Mail Send and Send Test Marketing Mail.

  • The total size of your email, including attachments, must be less than 30MB.

  • The total number of recipients must no more than 1000. This includes all recipients defined within the tocc, and bcc parameters, across each object that you include in the personalizations array.

  • The total length of custom arguments must be less than 10000 bytes.

  • Unicode encoding is not supported for the from field.

  • The to.namecc.name, and bcc.name personalizations cannot include either the ; or , characters.

  • For substitution, NULL values are truncated to ““ (empty string). If the substitution is for a numeric column, it is converted to a string.

  • Because the messages are sent in a batch, we will not revert the whole session. The error messages are logged to the console, but the job will continue.

  • You MUST HAVE strong knowledge of the Sendgrid JSON email template.

  • In Mail Send operation:
    • Template ID overrides the substitution defined in the Personalization Body. 
    • Skip invalid records only applied for invalid personalization body.
  • In Send Test Marketing Mail operation:
    • Should only input Unsubscribe URL or Suppression group ID.
    • Skip invalid records applied for invalid emails.

Obtain the Sendgrid API Key

To establish the connection to the Sendgrid application you must have an API key.

Your application, mail client, or website can all use API (Application Programming Interface) keys to authenticate access to SendGrid services. We suggest that you use API keys for connecting to all of SendGrid’s services. They are the preferred alternative to using a username and password because an API key can be revoked at any time without having to change your username and password. 

Sendgrid API keys include the following:

  • Full Access allows the API key to access GET, PATCH, PUT, DELETE and POST endpoints for all parts of your account, excluding billing and Email Address Validation.
  • Restricted Access customizes levels of access for all parts of your account, excluding billing and Email Address Validation.
  • Billing Access allows the API key to access billing endpoints for the account.


Within Sendgrid:

  1. Navigate to Settings on the left navigation bar.
  2. Select API Keys.
  3. Copy or write down the value of the API key that you need to use for the connection to Treasure Data.


Use the TD Console to Create Your Connection

Create a New Connection

In Treasure Data, you must create and configure the data connection before running your query. As part of the data connection, you provide authentication to access the integration.

  1. Open TD Console.

  2. Navigate to Integrations Hub Catalog.

  3. Search for and select Sendgrid.

  4. Type the credentials to authenticate.

  5. Type a name for your connection. 

  6. Select Done.



Define your Query


  1. Complete the instructions in Creating a Destination Integration.
  2. Navigate to Data Workbench > Queries.

  3. Select a query for which you would like to export data.

  4. Run the query to validate the result set.

  5. Select Export Results.

  6. Select an existing integration authentication.
  7. Define any additional Export Results details. In your export integration content review the integration parameters.
    For example, your Export Results screen might be different, or you might not have additional details to fill out:
  8. Select Done.

  9. Run your query.

  10. Validate that your data moved to the destination you specified.


Integration Parameters for Sendgrid



ParameterReq. or OptionalDescription
ActionRequired
  • Send Mail
  • Send Test Marketing Mail
From EmailRequired

Type an email value in the format:

<name>@<domain>

From Nameoptional
Column Name to SendRequired

For Send Test Marketing Mail. 

Input the column name of the recipient from the exported query result
Personalized Email BodyRequired

Input configured email template. Use __column_name__ to mark a substitute column/alias. All Substitute columns/aliases must be in the exported data.

For example:

{"personalizations" : [{"to": [{"email": "__email__"}], "subject": "subject test __email__", "substitutions": {"--name--": "meg"}} ]}


ContentRequired

For example:

{"content": [
            {
                "type": "text/plain",
                "value": "content root --name--"
            }
        ]}

Number of Recipients per API CallRequired

For each request call to Sendgrid, the connector will build the personalizations body up to this number

Min: 1, Max: 1000, default: 1000

Example with Export SQL query return 50,000 records. And with default config 1000, the connector will send 50 requests to Sendgrid, with 1000 personalizations per request

AttachmentsoptionalJSON String
Template ID

optional

Optional for Send Mail.
If specified, this template ID will override the Substitutions in the Personalized email body.

RequiredRequired for Send Test Marketing Mail.
Unsubscribe Group ID

You can give your recipients more control over the types of emails they want to receive by letting them opt out of messages from a certain group. 

Unsubscribe Urloptional
Suppression Group IDoptionalThe method used to specify an unsubscribe group.
Mail SettingsoptionalJSON string
Tracking SettingsoptionalJSON string

Example Query

select to_email as email from email_2_records



Optionally Schedule the Query Export Jobs

You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify.



Navigate to Data Workbench > Queries.
Create a new query or select an existing query.
Next to Schedule, select None.

In the drop-down, select one of the following schedule options.

Drop-down ValueDescription
Custom cron...

Review Custom cron... details.

@daily (midnight)Run once a day at midnight (00:00 am) in the specified time zone.
@hourly (:00)Run every hour at 00 minutes.
NoneNo schedule.

Custom cron... Details

Cron Value

Description

0 * * * *

Run once an hour

0 0 * * *

Run once a day at midnight

0 0 1 * *

Run once a month at midnight on the morning of the first day of the month

""

Create a job that has no scheduled run time.

 *    *    *    *    *
 -    -    -    -    -
 |    |    |    |    |
 |    |    |    |    +----- day of week (0 - 6) (Sunday=0)
 |    |    |    +---------- month (1 - 12)
 |    |    +--------------- day of month (1 - 31)
 |    +-------------------- hour (0 - 23)
 +------------------------- min (0 - 59)

The following named entries can be used:

  • Day of Week: sun, mon, tue, wed, thu, fri, sat

  • Month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec

A single space is required between each field. The values for each field can be composed of:

Field ValueExampleExample Description

a single value, within the limits displayed above for each field.



a wildcard ‘*’ to indicate no restriction based on the field. 

‘0 0 1 * *’ configures the schedule to run at midnight (00:00) on the first day of each month.
a range ‘2-5’, indicating the range of accepted values for the field.‘0 0 1-10 * *’ configures the schedule to run at midnight (00:00) on the first 10 days of each month.
a list of comma-separated values ‘2,3,4,5’, indicating the list of accepted values for the field.

0 0 1,11,21 * *’


configures the schedule to run at midnight (00:00) every 1st, 11th, and 21st day of each month.
a periodicity indicator ‘*/5’ to express how often based on the field’s valid range of values a schedule is allowed to run.

‘30 */2 1 * *’


configures the schedule to run on the 1st of every month, every 2 hours starting at 00:30. ‘0 0 */5 * *’ configures the schedule to run at midnight (00:00) every 5 days starting on the 5th of each month.
a comma-separated list of any of the above except the ‘*’ wildcard is also supported ‘2,*/5,8-10’‘0 0 5,*/10,25 * *’configures the schedule to run at midnight (00:00) every 5th, 10th, 20th, and 25th day of each month.
 (Optional) If you enabled the Delay execution, you can delay the start time of a query.

Execute the Query

Save the query with a name and run, or just run the query. Upon successful completion of the query, the query result is automatically imported to the specified container destination.


Scheduled jobs that continuously fail due to configuration errors may be disabled on the system side after several notifications.


Optionally Configure Export Results in Workflow

Within Treasure Workflow, you can specify the use of this data connector to export data.

Learn more at Using Workflows to Export Data with the TD Toolbelt.

Example Workflow for Sendgrid

sendgrifd_marketing_test_mail.dig

_export:
  td:
    database: sendgrid_db

+sendgrid_send_mail_task:
  td>: marketing_test_mail.sql
  database: ${td.database}
  result_connection: meg_sendgrid_auth
  result_settings:
  	type: sendgrid
	action: send_mail
  	from_email: test@treasure-data.com
    from_name: lee 
  	personalized_template: |
        {
          "personalizations": [{
                                 "to": [{
                                          "email": "__email__"
                                        }],
                                 "subject": "Subject in personalization __email__",
                                 "substitutions": {
                                   "--name--": "substitution value"
                                 }
                               }]
        }
  	content: |
	    {"content": [
            {
                "type": "text/plain",
                "value": "Hello --name--"
            }
        ]}
    batch_size: 1000 	
    unsubscribe_group_id: null
    attachment: null 
    mail_settings: null 
    tracking_settings: null  

#for send_test_marketing_mail
    type: sendgrid
    action: send_test_marketing_mail
    from_email: meg+sendgrid@treasure-data.com
    from_name: lee
    receiver_column_name: email
    template_id_send_testing: d-d86cc2d8bxx4400e903adf052e53d55a
    unsubscribe_url: null
    suppression_group_id: null


















  • No labels