# Sendgrid Export Integration 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 to, cc, 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.name, cc.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. ![](/assets/image2021-2-26_10-21-36.48cf8543ea916e9de32c8419beffeb54b46d97a5df60d8e5125507e84a28eb52.5a81bb91.png) 4. Type the credentials to authenticate. ![](/assets/image2021-2-10_16-45-55.e1a536fbffaa0dc27ef3d26407df0728ddbf80b8c38bdb16aa11b94cc42603c7.5a81bb91.png) ![](/assets/image2021-2-10_16-57-38.39afd2a33247d44a207719dcd20f02dd81dee400397970f7d3ec8fef0e2f0d97.5a81bb91.png) 1. Type a name for your connection. 2. Select **Done.** ### Define your Query 1. Complete the instructions in [Creating a Destination Integration](https://docs.treasuredata.com/display/PD/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. ![](https://docs.treasuredata.com/download/attachments/328524/image2020-12-18_13-44-6.png?version=1&modificationDate=1608327840458&api=v2) 1. 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: ![](https://docs.treasuredata.com/download/attachments/328524/image2023-5-17_14-42-52.png?version=1&modificationDate=1684348721512&api=v2) 1. Select **Done**. 2. Run your query. 3. Validate that your data moved to the destination you specified. ### Integration Parameters for Sendgrid ![](/assets/image2021-2-10_16-59-54.dab9bd594d863cd9effa5f4106ee562b5d06cb3623c2263f9ad929cfcb8f2d54.5a81bb91.png) ![](/assets/image2021-2-18_22-21-13.c2c0648aab16ece92af6b1aa79cd1ba53287911216f46f06538c58beee5805cb.5a81bb91.png) | Parameter | Req. or Optional | Description | | --- | --- | --- | | Action | Required | - Send Mail - Send Test Marketing Mail | | From Email | Required | Type an email value in the format: name@domain | | From Name | optional | | | Column Name to Send | Required | For Send Test Marketing Mail.Input the column name of the recipient from the exported query result | | Personalized Email Body | Required | 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: ```json {"personalizations" : [{"to": [{"email": "__email__"}], "subject": "subject test __email__", "substitutions": {"--name--": "meg"}} ]} ``` | | Content | Required | For example: ```json {"content": [ { "type": "text/plain", "value": "content root --name--" } ]} ``` | | Number of Recipients per API Call | Required | 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 | | Attachments | optional | JSON String | | Template ID | optional | Optional for Send Mail. If specified, this template ID will override the Substitutions in the Personalized email body. Required for Send Test Marketing Mail. | | Unsubscribe Group ID | optional | 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 Url | optional | | | Suppression Group ID | optional | The method used to specify an unsubscribe group. | | Mail Settings | optional | JSON string | | Tracking Settings | optional | JSON string | ### Example Query ```sql SELECT to_email as email FROM email_2_records ``` ### (Optional) Schedule Query Export Jobs You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify. Treasure Data's scheduler feature supports periodic query execution to achieve high availability. When two specifications provide conflicting schedule specifications, the specification requesting to execute more often is followed while the other schedule specification is ignored. For example, if the cron schedule is `'0 0 1 * 1'`, then the 'day of month' specification and 'day of week' are discordant because the former specification requires it to run every first day of each month at midnight (00:00), while the latter specification requires it to run every Monday at midnight (00:00). The latter specification is followed. #### Scheduling your Job Using TD Console 1. Navigate to **Data Workbench > Queries** 2. Create a new query or select an existing query. 3. Next to **Schedule**, select None. ![](/assets/image2021-1-15_17-28-51.f1b242f6ecc7666a0097fdf37edd1682786ec11ef80eff68c66f091bc405c371.0f87d8d4.png) 4. In the drop-down, select one of the following schedule options: ![](/assets/image2021-1-15_17-29-47.45289a1c99256f125f4d887e501e204ed61f02223fde0927af5f425a89ace0c0.0f87d8d4.png) | Drop-down Value | Description | | --- | --- | | Custom cron... | Review [Custom cron... details](#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. | | None | No schedule. | #### Custom cron... Details ![](/assets/image2021-1-15_17-30-23.0f94a8aa5f75ea03e3fec0c25b0640cd59ee48d1804a83701e5f2372deae466c.0f87d8d4.png) | **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 Value | Example | Example 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. | 1. (Optional) You can delay the start time of a query by enabling the Delay execution. ### 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 exported to the specified destination. Scheduled jobs that continuously fail due to configuration errors may be disabled on the system side after several notifications. (Optional) You can delay the start time of a query by enabling the Delay execution. ## Activate a Segment in Audience Studio You can also send segment data to the target platform by creating an activation in the Audience Studio. 1. Navigate to **Audience Studio**. 2. Select a parent segment. 3. Open the target segment, right-mouse click, and then select **Create Activation.** 4. In the **Details** panel, enter an Activation name and configure the activation according to the previous section on Configuration Parameters. 5. Customize the activation output in the **Output Mapping** panel. ![](/assets/ouput.b2c7f1d909c4f98ed10f5300df858a4b19f71a3b0834df952f5fb24018a5ea78.8ebdf569.png) - Attribute Columns - Select **Export All Columns** to export all columns without making any changes. - Select **+ Add Columns** to add specific columns for the export. The Output Column Name pre-populates with the same Source column name. You can update the Output Column Name. Continue to select **+ Add Columns**to add new columns for your activation output. - String Builder - **+ Add string** to create strings for export. Select from the following values: - String: Choose any value; use text to create a custom value. - Timestamp: The date and time of the export. - Segment Id: The segment ID number. - Segment Name: The segment name. - Audience Id: The parent segment number. 1. Set a **Schedule**. ![](/assets/snippet-output-connector-on-audience-studio-2024-08-28.a99525173709da1eb537f839019fa7876ffae95045154c8f2941b030022f792c.8ebdf569.png) - Select the values to define your schedule and optionally include email notifications. 1. Select **Create**. If you need to create an activation for a batch journey, review [Creating a Batch Journey Activation](/products/customer-data-platform/journey-orchestration/batch/creating-a-batch-journey-activation). ## 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](https://api-docs.treasuredata.com/en/tools/cli/api/#workflow-commands). ### Example Workflow for Sendgrid sendgrifd_marketing_test_mail.dig ```yaml _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 ```yaml    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 ```