SendGrid functional guide

Initiate a connectivity test to validate the credentials configured in the channel are valid.

Method: GET

Endpoint: https://api.sendgrid.com/api/profile.get.json?api_user=<user_name>&api_key=<sendgrid_password>

Request Body: None

Response Body:

[
  {
    "username": "<user_name>",
    "email": "<account_email>",
    "active": "true",
    "first_name": "",
    "last_name": "",
    "address": "34 Washington St",
    "address2": "",
    "city": "Wellesley",
    "state": "MA",
    "zip": "02481",
    "country": "US",
    "phone": "",
    "website": "",
    "website_access": "true"
  }
]

Method: GET

Endpoint: https://api.sendgrid.com/v3/api_keys

Request Body: N/A

Response Body:

{
  "result": [
    {
      "name": "redpoint_qa",
      "api_key_id": "TYWaXbJYSdm3fX_6xEuELg"
    }
  ]
}

If the connectivity fails, it will retry up to 10 attempts.

N/A

N/A

If the channel is configured to auto-suppress unsubscribes, RPI will query the suppression email table to identify and suppress any matching email addresses before generating the mail merge files.    

N/A

N/A

Use the Razor templating engine to render personalization and dynamic content to create the mail merge files.

N/A

N/A

Files are batched based on the unique sets of content across HTML, Test, and Subject Line assets.

N/A

N/A

Files are batched based on the unique sets of content across HTML, Test, and Subject Line assets.

AND

Each batch file includes up to 1000 recipients (including BCC), per the max allowed by SendGrid.

Using the V3 API may create additional batch files if each unique set of content includes more than 1000 recipients (including BCC, if configured).

N/A

N/A

RPI will loop through each of the files and initiate the send to SendGrid. 

N/A

Method: POST

Endpoint: https://api.sendgrid.com/v3/mail/send

Request Body: See attached file sendgrid_send_example.json

Response Body:  None

Once the sends are complete, the Interaction Log is updated with the total number of batch files that processed successfully/failed.  

For example, the attached file is from a log that sent 11K emails: Sendgrid_Interaction_Log.txt

N/A

N/A

Installation of the standard Callback Service

391

Upgrading the standard Callback Service

392

Installation of Web API Callback Service

393

Upgrading the Web API Callback Service

394

/PostEvents/{client id}

Method: POST

Request body: N/A

Response body: N/A

Purpose: Used by SendGrid Webhook where event data such as bounces, clicks, and opens are being captured and the files are delivered to the callback service web server (or ECP if configured).

/test

Method: GET

Request body: N/A

Response body: N/A

Purpose: Checks whether callback service is accessible or not. The endpoint returns OK if callback service can be accessed.

/status/{client id}

Method: GET

Request body: N/A

Response body: N/A

Purpose: Provides verbose details about the overall status of the callback service.

As part of SendGrid's webhook functionality, new event data is sent via an HTTP/HTTPS POST request to an IIS server where the Callback Service is running.

Upon arrival of the new event data posted by SendGrid webhook, the callback service is going to:

1. First look for any external content provider settings configured at SendGridWebAPIExternalContentProviderSettings on a cluster-level configuration, and the files will be posted to that respective location, or 

2. If no ECP is configured in the SendGridWebAPIExternalContentProviderSettings setting, the default email metrics folder path configured at emailMetricsFolderLocation setting in web.config file will be used (if using the Web API Callback Service).

3. However, if there is no folder path specified in emailMetricsFolderLocation setting, the callback service will try to look for a valid folder path configured at SendGridEmailMetricsFolderPath on a cluster-level configuration.

4. If none of these are configured, the Callback service application folder path will used. The folder path is configured either on external content provider or on a local server that serves as the root folder where event data is saved.

Typically, an external ECP such as S3 is configured using SendGridWebAPIExternalContentProviderSettings, if there's a security requirement to utilize a cloud provider for receiving and storing the SendGrid event files. This option can be used with any database, not just Redshift and Snowflake.   

Once the event data stream is available, it is read and stored as a JSON file either on:

• External content provider folder, or

• On a specific network folder location that can be accessed by RPI Execution Service

The JSON file has the following standard file naming: [GUID]_Events.json and is always written into the folder location [Root folder]/[Client ID]/. [Client ID] is an RPI client ID represented as GUID value, and this folder is automatically created from the root folder if it doesn't exist yet.

In the event of error, e.g. invalid root folder path or any folder permission related issues, the JSON file will be written into FailedEvents_[Client ID] folder under Callback service application root folder. This is to ensure that we are not losing any event data when an error has occurred. The FailedEvents_[Client ID] folder is automatically created if it doesn't exist yet. All JSON files stored in this folder can be used to reprocess the files. To reprocess, all JSON files must be manually copied and pasted into [Root folder]/[Client ID]/ folder. This process is applied either using an external content provider or local server folder locations. For external content provider, we recommend using any 3rd-party tool when copying files in bulk.

Channel Sync task is initiated, and it will first test connectivity to the Callback Service and the network share (if configured). If it fails, it will retry up to 10 times before the Channel Sync task is put into a failed state.   

The task will determine the source folder location of JSON files, based on what is configured within the SendGrid channel configuration settings:

• If "Email events shared folder" is configured, it will read the JSON files from the configured Network share, or

• If "Events external folder" is configured, the JSON files will be read and processed from the configured specific External content provider folder

• If "Email events shared folder" is configured on the channel, a batch of the JSON files are moved into Processing_Temp_[Guid] temporary folder on the network share. This is to ensure that no other clients with SendGrid channels can interrupt the processing operations of another client. After processing, the folder is deleted. 

• If "Events external folder" is configured, those JSON files in external content provider folder are going to be deleted once the records have been imported into SendGridEvents table successfully.

If the primary database is either Redshift or Snowflake, the recommendation is to use an S3 bucket to bulk load the records, for improved performance. If an S3 bucket is not configured, the process will only insert 200 records at a time.   

This can be enabled by updating the AWSPerformCopyfromS3 and AWSSourceS3BucketName settings in the tenant's system configuration.

This setting is not related to the SendGridWebAPIExternalContentProviderSettings setting in Server Workbench, which enables the option of having the SendGrid event files pushed directly to an S3 bucket, instead of a network share.      

Once those JSON files are available on specific folder location (either in "Events external folder" or "Email events shared folder"), these files are processed in batch, depending on the number of files set in Max import file count setting on SendGrid channel configurations. The default value of this setting is 200. This means 200 JSON files can be processed per batch per channel synchronization task execution. The value can be set to a higher value, but it may lead to poorer processing performance. Testing may be needed to find the ideal value for a particular environment.  

Processing JSON files involves de-serializing each file and extract the required information such as: email, timestamp, event, category, sg_event_id, channelId, channelExecutionId, and clientId. This information will be imported into the SendGridEvents table. The records from the JSON files are imported in batch, depending on the number of record count set in Event import batch size option on SendGrid channel configuration. The default value is 10,000, which means 10,000 records will be imported into SendGridEvents table per batch.

When Process older event files first option is selected on SendGrid channel configurations, it allows older JSON files to be processed first. In this case, we can still retrieve those previous event data that were not processed successfully. This option is not available on any RPI versions before v6.2.

Check if the SendGridEvents table exists on the database:

• If it does not exist, the process will create the table when there's at least one execution

• If the table exists, the process will insert the records from the temp table to the SendGridEvents table

The SendGridEvents table is used to store the original raw records from the JSON files.

If the SaveEmailEvents property is set to True on the channel configuration, the JSON files will be saved to the [Root folder]/[Client ID]/Processed/ folder on the network share. These saved files can be used for troubleshooting purposes.   

In the event of an error during import of a batch of JSON files into SendGridEvents table, a copy of JSON files is automatically generated and saved into the Failed folder within the root folder configured in Email events shared folder. These files from the Failed folder are commonly used for troubleshooting purposes and can be used re-importing to the SendGridEvents table. The SendGrid channel synchronization task will fail, and an error message will be reported back to user.

When JSON files have been processed and imported successfully into SendGridEvents table, each fulfillment activity (Channel Execution) generated by a specific SendGrid channel will be processed accordingly. It always processes the most recent fulfillment activity against the records from the SendGridEvents table that matches the following criteria:

• SendGrid channel ID should match ChannelID value

• Channel execution's client id should match ClientID value

• Channel execution's LastSynchronizationDate value must greater than or equal to EntryDate value

The channel execution's LastSynchronizationDate field is based on EntryDate value and is used to avoid calculating those records from the SendGridEvents table that were processed and imported already in offer_history_State table. The default value of this field is set to 1970-1-1 00:00:00 in UTC and is always updated with the recent EntryDate value that matches the record from SendGridEvents table. The records successfully matched will be inserted into offer_history_State table. This process is repeatedly executed until all fulfillment activities are processed.

Unsubscribes from the last 7 days are captured, and those collected records are written to the RPI email suppression table. The last [x] days frequency default value can also be modified through importUnsubFrequency parameter, which can be found in SendGrid channel synchronization task details XML value.

The records from offer_history_State table will be aggregated based on fulfillment state and channel execution Id to reflect each fulfillment state count in Offer activity result window.

If applicable, a user can re-subscribe a contact if they've previously unsubscribed and were added to the Global Suppression list in SendGrid. This is done via the Re-subscribe functionality in the Channel configuration (General tab).  

This functionality only supports the option to re-subscribe an email address if they've unsubscribed. It does NOT apply to email addresses that have hard bounced.   

There are 3 steps to this process:

1. Execute a GET on the Suppressions/Global endpoint to determine if the email address is currently on the list

2. If YES, then execute a DELETE on the Suppressions/Global endpoint to remove the email address from Global suppression list

3. Finally, delete the email address from the suppression_email table associated with the channel where the re-subscribe request is occurring

The API does not support the option to create “View as Web Page” links that mirror the email content

The ability to schedule a delivery is not supported by their API

Bypassing unsubscribes is not supported

Asynchronous processing is not supported

The use of social elements including Facebook Like Image and Facebook Like Share buttons are not supported

The “Forward-to-Friend” option is not supported

The use of content sharing for Facebook and Twitter is not supported

30/04/2021 3:12:33 pm
SendGrid Channel Trace HTTP Request
Request Endpoint: https://api.sendgrid.com/v3/suppression/unsubscribes?start_time=1614772955
Request Method: GET

Version :6.3.0.0
FileVersion :6.3.21118.1325
ProductVersion :6.3.21118.1325 -- RPI -- RPG-MACHINEX
DateTime :2021-04-30T07:12:33
TypeName :HttpClientLoggingHandler
TypeFullName :RedPoint.Plugins.Extensions.HttpClientLoggingHandler

Category: Plugin Trace
Priority: 10
Severity: Information

30/04/2021 3:12:34 pm
SendGrid Channel Trace HTTP Response
Request Endpoint: https://api.sendgrid.com/v3/suppression/unsubscribes?start_time=1614772955
Request Method: GET
Status: OK
Response Headers:
Transfer-Encoding: chunked
Connection: keep-alive
Access-Control-Allow-Methods: OPTIONS, HEAD, GET, POST, DELETE
Access-Control-Max-Age: 21600
Access-Control-Expose-Headers: Link, Location
Access-Control-Allow-Origin: *
Access-Control-Allow-Headers: AUTHORIZATION, Content-Type, On-behalf-of, x-sg-elas-acl, X-Recaptcha, X-Request-Source, Browser-Fingerprint
Content-Security-Policy: default-src https://api.sendgrid.com; frame-src 'none'; object-src 'none'
X-Content-Type-Options: nosniff
Strict-Transport-Security: max-age=31536000, max-age=600; includeSubDomains
X-Client-Ff: 1000
X-Ratelimit-Remaining: 598
X-Ratelimit-Limit: 600
X-Ratelimit-Reset: 1619766780
Powered-By: Mako
Date: Fri, 30 Apr 2021 07:12:34 GMT
Server: nginx

Raw Content:
[{"created":1615377755,"email":"ml@ml.emailtests.com"}]

Version :6.3.0.0
FileVersion :6.3.21118.1325
ProductVersion :6.3.21118.1325 -- RPI -- RPG-MACHINEX
DateTime :2021-04-30T07:12:34
TypeName :HttpClientLoggingHandler
TypeFullName :RedPoint.Plugins.Extensions.HttpClientLoggingHandler

Category: Plugin Trace
Priority: 10
Severity: Information

30/04/2021 3:06:45 pm
SendGrid Channel Trace HTTP Request
Request Endpoint: https://api.sendgrid.com/v3/api_keys
Request Method: GET

Version :6.3.0.0
FileVersion :6.3.21118.1325
ProductVersion :6.3.21118.1325 -- RPI -- RPG-MACHINEX
DateTime :2021-04-30T07:06:45
TypeName :HttpClientLoggingHandler
TypeFullName :RedPoint.Plugins.Extensions.HttpClientLoggingHandler

Category: Plugin Trace
Priority: 10
Severity: Information

30/04/2021 3:06:47 pm
SendGrid Channel Trace HTTP Response
Request Endpoint: https://api.sendgrid.com/v3/api_keys
Request Method: GET
Status: OK
Response Headers:
Connection: keep-alive
Link: <https://api.sendgrid.com/v3/api_keys?limit=10&offset=0>; rel="first"; title="1", <https://api.sendgrid.com/v3/api_keys?limit=10&offset=0>; rel="last"; title="1"
Access-Control-Allow-Methods: HEAD, GET, POST, OPTIONS
Access-Control-Max-Age: 21600
Access-Control-Expose-Headers: Link, Location
Access-Control-Allow-Origin: *
Access-Control-Allow-Headers: AUTHORIZATION, Content-Type, On-behalf-of, x-sg-elas-acl, X-Recaptcha, X-Request-Source, Browser-Fingerprint
Content-Security-Policy: default-src https://api.sendgrid.com; frame-src 'none'; object-src 'none'
X-Content-Type-Options: nosniff
Strict-Transport-Security: max-age=31536000, max-age=600; includeSubDomains
X-Client-Ff: 1000
X-Ratelimit-Remaining: 597
X-Ratelimit-Limit: 600
X-Ratelimit-Reset: 1619766420
Powered-By: Mako
Date: Fri, 30 Apr 2021 07:06:46 GMT
Server: nginx

Raw Content:
{"result": [{"name": "JB_Test2", "api_key_id": "PtMJzALmRuOmO6HkPjywYg"}, {"name": "JB_Test", "api_key_id": "9ei63O4VQaS68JhE43qAtA"}, {"name": "APIGener", "api_key_id": "QvVNY8lhRCmhVwTsKYNd_Q"}, {"name": "MinAccess", "api_key_id": "Sbux6bS5TD6ymaS-b7xDMA"}, {"name": "mktg04", "api_key_id": "YRJMRmRHRROKSMw_MY26cA"}, {"name": "Vhien", "api_key_id": "gNUMM07cRt6ih_QrSfPkuQ"}, {"name": "mktg03", "api_key_id": "YaSPk3wZTTyM8V35vGNwWA"}, {"name": "mktg02", "api_key_id": "PuTkLRx3R26iVCFri1K3Hw"}, {"name": "mktg01", "api_key_id": "cj1LNr6JTF6QHbhjY4s07Q"}]}

Version :6.3.0.0
FileVersion :6.3.21118.1325
ProductVersion :6.3.21118.1325 -- RPI -- RPG-MACHINEX
DateTime :2021-04-30T07:06:47
TypeName :HttpClientLoggingHandler
TypeFullName :RedPoint.Plugins.Extensions.HttpClientLoggingHandler

Category: Plugin Trace
Priority: 10
Severity: Information

30/04/2021 3:06:47 pm
SendGrid Channel Trace HTTP Request
Request Endpoint: https://local.rphelios.net/SendGridCallbackServiceWebAPI/test
Request Method: GET

Version :6.3.0.0
FileVersion :6.3.21118.1325
ProductVersion :6.3.21118.1325 -- RPI -- RPG-MACHINEX
DateTime :2021-04-30T07:06:47
TypeName :HttpClientLoggingHandler
TypeFullName :RedPoint.Plugins.Extensions.HttpClientLoggingHandler

Category: Plugin Trace
Priority: 10
Severity: Information

30/04/2021 3:06:47 pm
SendGrid Channel Trace HTTP Response
Request Endpoint: https://local.rphelios.net/SendGridCallbackServiceWebAPI/test
Request Method: GET
Status: OK
Response Headers:
Pragma: no-cache
Access-Control-Allow-Origin: *
Cache-Control: no-cache
Date: Fri, 30 Apr 2021 07:06:46 GMT
Server: Microsoft-IIS/10.0

Raw Content:
"OK!"

Version :6.3.0.0
FileVersion :6.3.21118.1325
ProductVersion :6.3.21118.1325 -- RPI -- RPG-MACHINEX
DateTime :2021-04-30T07:06:47
TypeName :HttpClientLoggingHandler
TypeFullName :RedPoint.Plugins.Extensions.HttpClientLoggingHandler

Category: Plugin Trace
Priority: 10
Severity: Information

30/04/2021 3:06:53 pm
SendGrid Channel Trace HTTP Response
Request Endpoint: https://api.sendgrid.com/v3/mail/send
Request Method: POST
Status: Accepted
Response Headers:
Connection: keep-alive
X-Message-Id: 3tWp9pZGTnqsKw6e9v0L7A
Access-Control-Allow-Origin: https://sendgrid.api-docs.io
Access-Control-Allow-Methods: POST
Access-Control-Allow-Headers: Authorization, Content-Type, On-behalf-of, x-sg-elas-acl
Access-Control-Max-Age: 600
X-No-CORS-Reason: https://sendgrid.com/docs/Classroom/Basics/API/cors.html
Strict-Transport-Security: max-age=600; includeSubDomains
Date: Fri, 30 Apr 2021 07:06:52 GMT
Server: nginx

Version :6.3.0.0
FileVersion :6.3.21118.1325
ProductVersion :6.3.21118.1325 -- RPI -- RPG-JGABITANAN
DateTime :2021-04-30T07:06:53
TypeName :HttpClientLoggingHandler
TypeFullName :RedPoint.Plugins.Extensions.HttpClientLoggingHandler

Category: Plugin Trace
Priority: 10
Severity: Information

Q

Which API version should we use to configure the channel?

A

Given that SendGrid will eventually deprecate the V2 APIs, the recommendation is to use the V3 API where possible. SendGrid has not yet provided timing for when they will deprecate the V2 APIs.

Q

Which Callback Service should we use to configure on the callback server - Standard or WebAPI? 

A

Both versions provide the same functionality for processing event data, but the WebAPI version has an additional Status endpoint that will provide the status of the service, including the database connections.   

Q

Is the OfferHistory_States table suitable for reporting?

A

No, it's not recommended to use OHS for any reporting, primarily for two reasons:

1. Querying the OHS table may potentially impact processing of the Channel Sync tasks

2. The data in the OHS table is also deduped at the rpcontactid, fulfillment state, and eventname level, which may not provide the full context when analyzing campaign performance

Please use the SendGridEvents table to support reporting.    

Q

We’re missing data in the OfferHistory_States table. Is there a way to retroactively load the missing event data?  

A

Yes, in the Interaction operational database, update the LastSynchronizationDate column in the op_channelExecution table for the channel executions that were impacted. The next time the channel sync task runs, it will use that new date to pick and process records from the SendGridEvents table.   

Q

Are there any SQL scripts available that could be used to manually load data to the OfferHistory_States table and update the operational table for the Offer Results counts?

A

Yes, the steps are covered in VSTS #12300 > https://redpointcmp.visualstudio.com/Interaction/_workitems/edit/12300

Q

Using standard SendGrid Callback service, the events files are being written into default directory: C:\temp\RedPoint\SendGrid\ not on the directory configured in SendGridEmailMetricsFolderPath setting. What is best place to start looking at this issue?

A

The best place to start troubleshooting this issue is to check whether the standard SendGrid callback service is able to communicate with the Pulse operational database.

Q

We’re experiencing a backlog in processing the event files. Are there any recommendations for improving overall performance when loading the files?

A

Yes, the following suggestions may help improve performance:

1. In the Channel config, increase the following thresholds:

   1. Max Import File Count to at least 20000, which will pick up and process up to 20000 files during each sync

   2. Event batch import size to 20000, which will process and load up to 20000 records during each sync

2. For Redshift and Snowflake integrations, ensure the system configuration is setup to use an S3 bucket for bulk loading the records

3. For customers using an EC2 instance for the callback server, they should consider having a minimum of 8GB of memory and storage type at the GP3 (or higher) tier, which supports higher IOPs (input/output operations per second)

4. In the SendGrid portal, uncheck any events that may not be necessary, such as Processed

Q

Can I get access to a sandbox account that can be used for demos or trying out the connector on my local instance?

A

Yes, reach out to support@redpointglobal.com, and the team will provide the credentials.

Q

Is configuring the SendGridWebAPIExternalContentProviderSettings setting required when using a Redshift or Snowflake database?

A

No, this setting can be used with any database. The only requirement for Redshift and Snowflake instances is to configure the AWSPerformCopyfromS3 and AWSSourceS3BucketName settings in the tenant's system configuration. This will improve performance by utilizing the COPY command to bulk load the data to the database.    

The SendGridWebAPIExternalContentProviderSettings setting is typically enabled when there's a security requirement to utilize a cloud provider for receiving and storing the SendGrid event files.