Adding a Connection to SendGrid

To add a connection to SendGrid:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the SendGrid icon is not available, click the Add More icon to download and install the SendGrid connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

You must use an API key to authenticate through the SendGrid Sync App.

Obtaining an API Key

To obtain an API key, follow the steps below:

1. Log into SendGrid.
2. Click Settings.
3. Click API keys.
4. Click Create API Key.
5. Choose the Permissions the API Key should have, either Full Access or Restricted Access. The API key is then displayed.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

This section details a selection of advanced features of the SendGrid Sync App.

User Defined Views

The Sync App supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to SendGrid and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Sync App uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The Sync App models SendGrid APIs as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files.

However, the Recipients table is dynamic: The Sync App connects to SendGrid and gets the metadata for this table by calling the appropriate Web services. Any changes you make to the custom filters you define for Recipients are available when you connect; you can use these custom filters as columns in the WHERE clause. These custom filters are surfaced in the CustomFields table.

The Sync App uses the SendGrid API to process supported filters. The Sync App processes other filters client-side within the Sync App.

New Marketing Campaigns Data Model

See New Marketing Campaigns Data Model for the available entities in the New Marketing Campaigns API.

Legacy Data Model

See Legacy Data Model for the available entities in the Legacy API.

The CData Sync App models the New Marketing Campaigns API as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files. The Sync App uses the SendGrid API to process supported filters. The Sync App processes other filters client-side within the Sync App.

To use the New Marketing Campaigns Data Model, simply set Schema to NMC.

Tables

Tables describes the available tables that are easy to query and update.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to Sendgrid. They can be used to search, update, and modify information.

The Sync App models the data in SendGrid as a list of tables in a relational database that can be queried using standard SQL statements.

SendGrid Connector for CData Sync Tables

Name	Description
Alerts	Create, update, delete, and query Alerts in SendGrid.
ApiKeys	Query, Create, Update and Delete the available ApiKeys in SendGrid.
Blocks	Query and Delete the available Blocks in SendGrid.
Bounces	Query and Delete the available Bounces in SendGrid.
CancelScheduledSends	Update, delete, and query Cancel Scheduled Sends in SendGrid.
GlobalUnsubscribes	Query Global Unsubscribes in SendGrid.
InvalidEmails	Query and Delete invalid emails in SendGrid.
NMCCustomFields	Create, delete, and query New Marketing Campaign Custom Fields in SendGrid.
NMCLists	Create, update, delete, and query the available New Marketing Campaign Lists in SendGrid.
NMCSegments	Create, update, delete, and query New Marketing Campaign Segments in SendGrid.
NMCSenders	Create, update, delete, and query New Marketing Campaign Senders in SendGrid.
NMCSingleSends	Create, update, delete, and query the available New Marketing Campaign Single Sends in SendGrid.
Spam	Query and delete Spam reports in SendGrid.
Teammates	Query Teammates in SendGrid.
UnsubscribeGroups	Create, update, delete, and query Unsubscribe Groups in SendGrid.
Users	Query the available Users in SendGrid.

Create, update, delete, and query Alerts in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM Alerts
SELECT * FROM Alerts WHERE Id = '148825'

Insert

The following columns are required in an insert for Stats Notification: Type, EmailTo, Frequency.

INSERT INTO Alerts (Type, EmailTo, Frequency) VALUES ('stats_notification', '[email protected]', 'daily')

The following columns are required in an insert for Usage Limit: Type, EmailTo, Percentage.

INSERT INTO Alerts (Type, EmailTo, Percentage) VALUES ('usage_limit', '[email protected]', 20)

Update

You can only update EmailTo by specifying an Id.

UPDATE Alerts SET EmailTo = '[email protected]' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM Alerts WHERE Id = 1310789

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the alert.
CreatedAt	Datetime	True		Date when the alert was created.
UpdatedAt	Datetime	True		Date when the alert was updated.
EmailTo	String	False		The email address that the alert will be sent to.
Percentage	Integer	False		The email address that the alert will be sent to.
Type	String	False		The type of alert. Allowed values (usage_limit, stats_notification).
Frequency	String	False		If the alert is of type stats_notification, this indicates how frequently the stats notifications will be sent.(daily, weekly, monthly).

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query, Create, Update and Delete the available ApiKeys in SendGrid.

Table-Specific Information

Select

To get all the Api Keys

SELECT * FROM ApiKeys

To get information about a specific Api Key

SELECT * FROM Apikeys WHERE ApiKeyId = 'Dqdvdvdvdv4yLeuVQqO3rFxN5ktUxA'

Insert

The following columns are required in an insert: Name. VALUES for Scopes are: alerts.create, alerts.read, alerts.update, alerts.delete, asm.groups.create, asm.groups.read, asm.groups.update, asm.groups.delete, ips.pools.ips.read, mail.send, mail_settings.bcc.read, mail_settings.bcc.update, mail_settings.address_whitelist.read, mail_settings.address_whitelist.update, mail_settings.footer.read, mail_settings.footer.update, mail_settings.forward_spam.read, mail_settings.forward_spam.update, mail_settings.plain_content.read, mail_settings.plain_content.update, mail_settings.spam_check.read, mail_settings.spam_check.update, mail_settings.bounce_purge.read, mail_settings.bounce_purge.update, mail_settings.forward_bounce.read, mail_settings.forward_bounce.update, partner_settings.new_relic.read, partner_settings.new_relic.update, partner_settings.sendwithus.read, partner_settings.sendwithus.update, tracking_settings.click.read, tracking_settings.click.update, tracking_settings.subscription.read, tracking_settings.subscription.update, tracking_settings.open.read, tracking_settings.open.update, tracking_settings.google_analytics.read, tracking_settings.google_analytics.update, user.webhooks.event.settings.read, user.webhooks.event.settings.update, user.webhooks.event.test.create, user.webhooks.event.test.read, user.webhooks.event.test.update, user.webhooks.parse.settings.create, user.webhooks.parse.settings.read, user.webhooks.parse.settings.update, user.webhooks.parse.settings.delete, stats.read, stats.global.read, categories.stats.read, categories.stats.sums.read, devices.stats.read, clients.stats.read, clients.phone.stats.read, clients.tablet.stats.read, clients.webmail.stats.read, clients.desktop.stats.read, geo.stats.read, mailbox_providers.stats.read, browsers.stats.read, user.webhooks.parse.stats.read, templates.create, templates.read, templates.update, templates.delete, templates.versions.create, templates.versions.read, templates.versions.update, templates.versions.delete, templates.versions.activate.create, user.timezone.read, user.timezone.update, user.settings.enforced_tls.read, user.settings.enforced_tls.update, api_keys.create, api_keys.read, api_keys.update, api_keys.delete, email_activity.read, categories.create, categories.read, categories.update, categories.delete, mail_settings.template.read, mail_settings.template.update, marketing_campaigns.create, marketing_campaigns.read, marketing_campaigns.update, marketing_campaigns.delete, mail.batch.create, mail.batch.read, mail.batch.update, mail.batch.delete, user.scheduled_sends.create, user.scheduled_sends.read, user.scheduled_sends.update, user.scheduled_sends.delete, access_settings.whitelist.create, access_settings.whitelist.read, access_settings.whitelist.update, access_settings.whitelist.delete, access_settings.activity.read, whitelabel.create, whitelabel.read, whitelabel.update, whitelabel.delete, suppression.create, suppression.read, suppression.update, suppression.delete, messages.read

INSERT INTO Apikeys (Name) VALUES ('apiname')

INSERT INTO Apikeys (Name, Scopes, Sample) VALUES ('apiname', 'alerts.create,alerts.read', 'test')

Update

You can update ApiKeys by specifying an ApiKeyid.You need to specify both the name and the scopes during the UPDATE even if you don't want to update it.

UPDATE Apikeys SET Name = 'apiname2', Scopes = 'alerts.create,alerts.read' WHERE apikeyid = 'Dqdvdvdvdv4yLeuVQqO3rFxN5kscsctUxA-P_Q'

Delete

You can Delete ApiKeys by specifying an ApiKeyid.

DELETE FROM ApiKeys WHERE apikeyid = 'CMp4C8klscscscsQ0GxqvxtK2-P_Q'

Columns

Name	Type	ReadOnly	References	Description
ApiKeyId [KEY]	String	False		The ID of your API Key.
Name	String	False		The name of your API Key.
Scopes	String	False		The permissions this API Key has access to. This is populated only when Id is specified.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query and Delete the available Blocks in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Blocks WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Blocks by specifying an Email.

DELETE FROM Blocks WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the block was created.
Reason	String	False		The reason for the block.
Email [KEY]	String	False		The email that blocked.
Status	String	False		The enhanced SMTP block response.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query and Delete the available Bounces in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Bounces WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Bounces by specifying an Email.

DELETE FROM Bounces WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the bounce was created.
Reason	String	False		The reason for the bounce.
Email [KEY]	String	False		The email that bounced.
Status	String	False		The enhanced SMTP bounce response.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Update, delete, and query Cancel Scheduled Sends in SendGrid.

Table-Specific Information

Select

This table only supports filtering by BatchId. The following are the only possible SELECT queries:

SELECT * FROM CancelScheduledSends
SELECT * FROM CancelScheduledSends WHERE BatchId = 148825

Update

You can update CancelScheduledSends by specifying an BatchId.

Allowed Values : cancel, pause

UPDATE CancelScheduledSends SET Status = 'cancel'  WHERE BatchId = 1310789

Delete

You can only delete from CancelScheduledSends by specifying an BatchId.

DELETE FROM CancelScheduledSends WHERE BatchId = 1310789

Columns

Name	Type	ReadOnly	References	Description
BatchId [KEY]	String	False		The Id of the batch.
Status	String	False		The status of the scheduled send.Allowed Values: cancel, pause

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query Global Unsubscribes in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. However this filter is optional. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalUnsubscribes WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

INSERT

Performing an Insert operation in this table you need to specify an email which will be added to the GlobalUnsubscribes list. For example:

INSERT INTO GlobalUnsubscribes (Email) VALUES ('[email protected]')

DELETE

Performing a Delete operation in this table you need to specify an email which will be removed from the GlobalUnsubscribes list. For example:

DELETE FROM GlobalUnsubscribes WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
Email [KEY]	String	False		Email of the unsubscriber.
CreatedAt	Datetime	True		The time the unsubscriber was created.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query and Delete invalid emails in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM InvalidEmails
SELECT * FROM InvalidEmails WHERE Email = '[email protected]'

Delete

You can only delete from InvalidEmails by specifying an Email.

DELETE FROM InvalidEmails WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
Created	Datetime	True		The time the when the email address was added to the invalid emails list.
Reason	String	False		A reason explaining why the email address was marked as invalid..
Email [KEY]	String	False		The email address that was marked as invalid.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Create, delete, and query New Marketing Campaign Custom Fields in SendGrid.

Table Specific Information

New Marketing Campaign Custom fields are filters for a Contact that are defined by the user. NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the NMCCustomFields or filter them by Id:

SELECT * FROM NMCCustomFields WHERE Id = '1'

Use quotations to filter on NMC Custom Fields regardless of type.

Insert

To insert on NMCCustomFields specify the Name of the field and its Type. The Type can be text, date, or number.

INSERT INTO NMCCustomFields (name, type) VALUES ('age', 'number') 

Update

UPDATE NMCCustomFields SET name = 'updated name' WHERE Id = '123' 

Delete

You can only delete from NMCCustomFields by specifying an Id:

DELETE FROM NMCCustomFields WHERE Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the custom field.
Name	String	False		The name of the custom field.
Type	String	False		The type of the custom field. Allowed values are number, text, or date.

Create, update, delete, and query the available New Marketing Campaign Lists in SendGrid.

Table-Specific Information

NMC prefix stands for New Marketing Campaign.

Select

The NMCLists table can only filtered by its Id.

SELECT * FROM NMCLists WHERE Id = '123'

Insert

Only the Name for the NMCLists is required to insert into this table.

INSERT INTO NMCLists (Name) VALUES ('TestList')

Update

You can only update from NMCLists by specifying an Id.

UPDATE NMCLists SET Name = 'New Name' WHERE Id = '123'

Delete

You can only delete from NMCLists by specifying an Id.

DELETE FROM NMCLists WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the list.
Name	String	False		The name of the list.
ContactCount	Integer	True		The recipient count of the list.
ItemUrl	String	True		The URL of the List Item.

Create, update, delete, and query New Marketing Campaign Segments in SendGrid.

Table-Specific Information

NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the segments or filter them by Id.

SELECT * FROM NMCSegments WHERE Id = '1'

Insert

A Segment divides NMCContacts by certain criteria. To specify these criteria use the QueryDSL filter.

INSERT INTO NMCSegments (Name, QueryDSL) VALUES ("New Segment", "Segment Cdata", "first_name LIKE '%test%'"))

The statement above creates NMCSegments containing NMCContacts with FirstName containing the value "test".

Update

You can update the name or the conditions. Since the whole set will be overwritten, specify a full set.

UPDATE NMCSegments SET Name = "Name Updated", QueryDSL = "first_name LIKE '%test update%'" WHERE Id = '123'

Delete

You can only delete NMCSegments by Id:

DELETE FROM NMCSegments WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the segment.
Name	String	False		The name of the segment.
ParentListId	String	False		The Id of the list associated with the segment.
ContactsCount	Integer	True		The recipient count of the segment.
CreatedAt	Datetime	True		Field name for the first condition.
SampleUpdatedAt	Datetime	False		Field name for the first condition.
UpdatedAt	Datetime	True		Field name for the first condition.
QueryDSL	String	False		Field name for the first condition.
NextSampleUpdate	Datetime	True		The last updated samples date.
ItemUrl	String	True		The URL of the Segment item.
ContactSampleAggregate	String	True		The aggregate of the contact sample.

Create, update, delete, and query New Marketing Campaign Senders in SendGrid.

Table-Specific Information

NMC stands for New Marketing Campaign.

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM NMCSenders
SELECT * FROM NMCSenders WHERE Id = '148825'

Insert

The following columns are required in an insert: Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country.

INSERT INTO NMCSenders (Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country) VALUES ('Nickname', 'FromEmail', 'FromName', 'ReplytoEmail', 'Address', 'City', 'Country')

Update

You can update NMCSenders by specifying an Id.

UPDATE NMCSenders SET Nickname = 'Nickname', FromEmail = 'FromEmail', FromName = 'FromName', ReplytoEmail = 'ReplytoEmail', Address = 'Address', City = 'City', Country = 'Country'  WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM NMCSenders WHERE Id = 1310789

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The unique identifier of the sender identity.
Nickname	String	False		A nickname for the sender identity. Not used for sending..
FromEmail	String	False		This is where the email will appear to originate from for your recipient.
FromName	String	False		This is the name appended to the from email field. IE - Your name or company name.
ReplytoEmail	String	False		This is the email that your recipient will reply to.
ReplytoName	String	False		This is the name appended to the reply to email field. IE - Your name or company name.
Address	String	False		The physical address of the sender identity.
Address2	String	False		Additional sender identity address information.
City	String	False		The city of the sender identity.
State	String	False		The state of the sender identity.
Zip	String	False		The zipcode of the sender identity.
Locked	String	True		Indicator if the sender is locked.
VerifiedStatus	String	True		Verified status of the sender.
VerifiedReason	String	True		Verified reason of the sender.
Country	String	False		The country of the sender identity.
CreatedAt	Datetime	True		The end of the time range when the bounce was created.
UpdatedAt	Datetime	True		The end of the time range when the bounce was created.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Create, update, delete, and query the available New Marketing Campaign Single Sends in SendGrid.

Table-Specific Information

NMC stands for New Marketing Campaign.

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM NMCSingleSends
SELECT * FROM NMCSingleSends WHERE Id = '148825'

Insert

The following columns are required in an insert: Name.

INSERT INTO NMCSingleSends (Name) VALUES ('SingleSend Name')

Update

You can update NMCSingleSends by specifying an Id.

UPDATE NMCSingleSends SET Name = 'SingleSend Updated' WHERE Id = 1310789

Delete

You can only delete from NMCSingleSends by specifying an Id.

DELETE FROM NMCSingleSends WHERE Id = 1310789

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the single send.
Name	String	False		The name of the single send.
Categories	String	False		The name of the single send.
Status	String	False		The status of the single send.
CreatedAt	String	True		The created timestamp of the single send.
UpdatedAt	String	True		The updated timestamp of the single send.
IsABTest	Boolean	False		Indicator showing if the single send is an AB Test.
SendAt	Datetime	False		The ISO 8601 time at which to send the single send.
EmailConfigCustomUnsubscribeUrl	String	False		The URL allowing recipients to unsubscribe.
EmailConfigEditor	String	False		The editor, design or code, used to modify the Single Send's design in the Marketing Campaigns application user interface. This is populated only when Id is specified.
EmailConfigGeneratePlainContent	Boolean	False		If this property is set to true, plain_content is always generated from html_content. If it's set to false, plain_content is not altered. This is populated only when Id is specified.
EmailConfigHtmlContent	String	False		The HTML content of the Single Send. This property is not used when a design_id value is set. This is populated only when Id is specified.
EmailConfigIpPool	String	False		The name of the IP Pool from which the Single Send emails are sent. This is populated only when Id is specified.
EmailConfigPlainContent	String	False		The plain text content of the Single Send. This is populated only when Id is specified.
EmailConfigSenderId	Integer	False		The ID of the verified sender from whom the Single Send will be sent. This is populated only when Id is specified.
EmailConfigSubject	String	False		The subject line of the Single Send. This is populated only when Id is specified.
EmailConfigSuppressionGroupId	Integer	False		The ID of the Suppression Group to allow recipients to unsubscribe. This is populated only when Id is specified.
SendToListIds	String	False		The IDs of each contact list to which the Single Send will be sent. This is populated only when Id is specified.
SendToSegmentIds	String	False		The IDs of each segment to which the Single Send will be sent. This is populated only when Id is specified.
SendToAll	Boolean	False		If this property is set to true, the Single Send will be sent to all of your contacts. This is populated only when Id is specified.
ItemUrl	String	True		The URL of the Single Send Item.

Query and delete Spam reports in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM Spam
SELECT * FROM Spam WHERE Email = '[email protected]'

Delete

Delete from Spam 

You can delete from Spam by specifying an Email.

DELETE FROM Spam WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time when the spam report was made.
Email [KEY]	String	False		The email address of the recipient who marked your message as spam.
Ip	String	False		The IP address that the message was sent on.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query Teammates in SendGrid.

View-Specific Information

Select

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

• Username supports the '=,IN' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Teammates WHERE Username = 'test'

SELECT * FROM Teammates WHERE Username IN ('test', 'test2')

Insert

The following columns are required in an insert: Email and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

The IsAdmin is optional and by default is accepted as false from the API.

INSERT INTO Teammates (Email, Scopes) VALUES ('[email protected]', 'user.profile.read,user.profile.update')

INSERT INTO Teammates (Email, Scopes, IsAdmin) VALUES ('[email protected]', 'user.profile.read,user.profile.update', 'true')

Update

The following columns are required in an update: Username and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

UPDATE Teammates SET Scopes = 'user.profile.read' WHERE Username = 'testuser'

Delete

Only the username column is required to delete a record from Teammates table. For example:

DELETE FROM Teammates WHERE Username = 'testuser'

Columns

Name	Type	ReadOnly	References	Description
Username [KEY]	String	False		The username of the teammate.
Email	String	False		Email of the teammate.
FirstName	String	False		First name of the teammate.
LastName	String	False		Last name of the teammate.
UserType	String	False		User type of the teammate.
IsAdmin	Boolean	False		Indicator if the teammate is admin.
Phone	String	False		Phone of the teammate.
Website	String	False		Website of the teammate.
Company	String	False		Company of the teammate.
Address	String	False		Address of the teammate.
Address2	String	False		Address 2 of the teammate.
City	String	False		City of the teammate.
State	String	False		State of the teammate.
Country	String	False		Country of the teammate.
Zip	String	False		Zip of the teammate.
Scopes	String	False		Scope of the permissions for the new teammate. Multiple values are allowed and should be split by a comma symbol. Possible values are: user.account.read,user.credits.read,user.email.create,user.email.read,user.email.update,user.email.delete,user.profile.create,user.profile.read,user.profile.update,user.profile.delete
IsPartnerSSO	Boolean	False		Indicator if the teammate partner is SSO.
IsSSO	Boolean	False		Indicator if the teammate is SSO.
IsUnified	Boolean	False		Indicator if the teammate is unified.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Create, update, delete, and query Unsubscribe Groups in SendGrid.

Table-Specific Information

Select

UnsubscribeGroups can only be filtered by Id.

SELECT * FROM UnsubscribeGroups WHERE Id = '1'

Insert

Provide a Name and a Description to insert into UnsubscribeGroups:

INSERT INTO UnsubscribeGroups (Name, Description, IsDefault) VALUES ('Unsubscribe1', 'General Unsubscribe', true)

Update

You can only update UnsubscribeGroups by specifying an Id.

UPDATE UnsubscribeGroups SET Name = 'New Name', Description = 'New Description' WHERE Id = '1'

Delete

You can only delete from UnsubscribeGroups by specifying an Id.

DELETE FROM UnsubscribeGroups WHERE Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id of the unsubscribe group.
Name	String	False		The name of the unsubscribe group.
Description	String	False		The description of the unsubscribe group.
LastEmailSentAt	Datetime	True		The last time an email was sent to the unsubscribe group.
IsDefault	Boolean	False		If the unsubscribe group is the default group or not.
Unsubscribes	Integer	True		The number of unsubscribes from this group.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query the available Users in SendGrid.

View-Specific Information

Select

Query this table to retrieve your current profile details.

SELECT * FROM Users 

Update

You can update the all the columns marked as readonly false.

UPDATE Users Set Address='Milpitas', Address2='North Carolina' WHERE id='38713966'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the user.
Address	String	False		The address of the user.
Address2	String	False		The secondary address field for the user.
City	String	False		The city of the user.
Company	String	False		The company of the user.
Country	String	False		The country of the user.
FirstName	String	False		The first name of the user.
LastName	String	False		The last name of the user.
Phone	String	False		The phone number of the user.
State	String	False		The state the user is located in.
Website	String	False		The Web site URL of the user.
Zip	String	False		The ZIP code of the user.
Email	String	True		The Email Address of the user.
Type	String	True		The type of the user.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

SendGrid Connector for CData Sync Views

Name	Description
AutomationStats	Get All Automation Stats
Categories	Query the available Categories in SendGrid.
CategoryStats	Query the available Category Stats in SendGrid.
Designs	List Designs
DynamicTemplates	Query Teammates in SendGrid.
GlobalStats	Query the available Global Stats in SendGrid.
GroupSuppressions	Query group suppressions in SendGrid.
IPAccessManagement	Query Global Unsubscribes in SendGrid.
MailSettings	Query all the mail settings in SendGrid.
MessageEvents	List of events related to email message in SendGrid.
Messages	Query Email Activities in SendGrid.
NMCContacts	Query New Marketing Campaign Contacts in SendGrid.
NMCReservedFields	Query the available New Marketing Campaign Reserved Fields in SendGrid.
NMCSegmentsContacts	Query New Marketing Campaign Segments Contacts sample in SendGrid.
PartnerSettings	Query all the partner settings in SendGrid.
SingleSendClickTrackingStats	Retrieves Single Send click tracking statistics by Id.
SingleSendsStats	Get all Single Sends statistics.
StatsByBrowser	Query Browser Statistics in SendGrid.
StatsByClient	Query Client Statistics in SendGrid.
StatsByDevice	Query Device Statistics in SendGrid.
StatsByLocation	Query Location Statistics in SendGrid.
StatsByMailboxProvider	Query Mailbox Provider Statistics in SendGrid.
Subusers	Query all the subusers in sendgrid.
SubuserStats	Query Subuser Statistics in SendGrid.
Templates	Retrieve paged transactional templates

Get All Automation Stats

View-Specific Information

Select

The following is an example to query the AutomationStats view:

 
SELECT * FROM AutomationStats

Columns

Name	Type	References	Description
Id	String		This is the ID of the Automation you are requesting stats for.
Aggregation	String		This describes the time unit to which the stat is rolled up. It is based on the aggregated_by parameter included in the request. It can be 'total' or the date (in YYYY-MM-DD format) the stats are for.
StepId	String		This is the ID of the step if the stats were requested to be grouped by step_id.
BounceDrops	Integer		BounceDrops
Bounces	Integer		Bounces
Clicks	Integer		Clicks
Delivered	Integer		Delivered
InvalidEmails	Integer		InvalidEmails
Opens	Integer		Opens
Requests	Integer		Requests
SpamReportDrops	Integer		SpamReportDrops
SpamReports	Integer		SpamReports
UniqueClicks	Integer		UniqueClicks
UniqueOpens	Integer		UniqueOpens
Unsubscribes	Integer		Unsubscribes

Query the available Categories in SendGrid.

View-Specific Information

Select

Query this table to get available categories in SendGrid.

SELECT * FROM Categories

Columns

Name	Type	References	Description
Name	String		The name of the categories.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Query the available Category Stats in SendGrid.

View-Specific Information

Select

The following is an example to query the CategoryStats view:

 
SELECT * FROM CategoryStats WHERE Name = 'news'

You can also filter by Date. If you do not specify one, a default span of the past 1 year is used. The operators =, >=, <=, <, and > are supported for this column.

SELECT * FROM CategoryStats WHERE Name = 'news' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the category.
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails in this category dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

List Designs

View-Specific Information

Select

The following is an example to query the Designs view:

 
SELECT * FROM Designs
SELECT * FROM Designs WHERE ID = 'd0ceee19-e773-403b-a72c-8d7c58ec6065'

Columns

Name	Type	References	Description
Id [KEY]	String		ID of the Design.
UpdatedAt	String		Datetime that Design was last updated.
CreatedAt	String		Datetime that Design was created.
ThumbnailURL	String		A Thumbnail preview of the template's html content.
Name	String		The name of the new design.
Editor	String		The editor used in the UI.
ItemUrl	String		The URL of the Design Item.
Subject	String		The subject of the Design Item.
GeneratePlainContent	Boolean		Indicates whether plain content is to be generated.

Query Teammates in SendGrid.

Columns

Name	Type	References	Description
Id [KEY]	String		The ID of the template.
Name	String		Name of the template.
Generation	String		Generation of the template. (dynamic)
UpdatedAt	Datetime		The last time the template was updated.

Query the available Global Stats in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalStats 

SELECT * FROM GlobalStats WHERE Date > '2016-01-01' AND Date < '2016-03-30'

Columns

Name	Type	References	Description
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
BounceDrops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.
UnsubscribeDrops	Integer		The number of emails globally dropped by SendGrid because the recipient unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Query group suppressions in SendGrid.

Table-Specific Information

Select

The following are examples to query the GroupSuppressions table:

SELECT * FROM GroupSuppressions WHERE GroupId = 391

SELECT * FROM GroupSuppressions WHERE GroupId IN (391, 392, 393)

Columns

Name	Type	References	Description
GroupId [KEY]	Integer		Email of the unsubscriber.
Emails	String		The time the unsubscriber was created.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Query Global Unsubscribes in SendGrid.

View-Specific Information

Select

The Sync App will use the SendGrid API to retrieve information about the IP addresses which have accessed the account. However there is no filtering supported server-side for this view.

Only the following query is processed server side:

SQL

SELECT * FROM IPAccessManagement

Columns

Name	Type	References	Description
IP	String		IP Address.
Allowed	Boolean		Indicator if this IP is allowed for access.
AuthMethod	String		Authentication method.
FirstAt	Timestamp		First time when IP has accessed the account.
LastAt	Timestamp		Last time when IP has accessed the account.
Location	String		Location of the IP.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Query all the mail settings in SendGrid.

Table-Specific Information

Select

You can retrieve all the mail settings.

SELECT * FROM MailSettings

Columns

Name	Type	References	Description
Title	String		The title of the mail setting.
Enabled	String		Indicates if this mail setting is currently enabled.
Name	String		The name of the mail setting.
Description	String		A description of the mail setting.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

List of events related to email message in SendGrid.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

• MessageId supports the '=' comparisons.

SQL

SELECT * FROM MessageEvents WHERE MessageId = '1234'

NOTE: The query below will return message events only for the first 1000 message IDs retrieved from the messages endpoint.

SELECT * FROM MessageEvents;

Columns

Name	Type	References	Description
MessageId	String		The Id of the message.
EventName	String		Name of event. The allowed values are bounced, opened, clicked, processed, dropped, delivered, deferred, spam_report, unsubscribe, group_unsubscribe, group_resubscribe.
Processed	Datetime		The date when the event was processed.
Reason	String		Explanation of what caused the message to be 'bounced', 'deferred', or 'blocked'.
AttemptNum	Integer		Used with 'deferred' events to indicate the attempt number out of 10.
Url	String		Used with 'clicked' event to indicate which url the user clicked.
BounceType	String		Use to distinguish between types of bounces. The allowed values are soft, hard.
HttpUserAgent	String		Client recipient used to click or open message.
MXServer	String		The MX server that received the email.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
Categories	String		Categories users associated to the message.
UniqueArgs	String		JSON hash of key-value pairs associated with the message.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.

Query Email Activities in SendGrid.

NOTE: If you want access to Email activities, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client side within the Sync App.

Note: The API will return only 1000 records. Please choose from the available filters to restrict the search.

• Id supports the '=,IN' comparisons.
• Status supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Subject supports the '=,!=,LIKE,NOT LIKE' comparisons.
• FromEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
• ToEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Clicks supports the '>,<,<=,>=,=,!=' comparisons.
• LastEventTime supports the '>=, <=, BETWEEN' comparisons.
• ApiKeyId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• AsmGroupId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• MarketingCampaignId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• MarketingCampaignName supports the '=,!=,LIKE,NOT LIKE' comparisons.
• OriginatingIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
• OutboundIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Teammate supports the '=,!=,LIKE,NOT LIKE' comparisons.
• TemplateId supports the '=,!=,LIKE,NOT LIKE' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Messages WHERE Id = '1234'

SELECT * FROM Messages WHERE Id IN ('1234', '4567')

SELECT * FROM Messages WHERE Status = 'not_delivered'

SELECT * FROM Messages WHERE LastEventTime BETWEEN '2020-01-01' AND '2020-01-15'

NOTE: Pagination is not available on this view. Any query—whether filtered or not—will return a maximum of 1000 records. If your filtered query matches more than 1000 records, only the first 1000 will be returned.

Columns

Name	Type	References	Description
Id [KEY]	String		The Id of the message.
Status	String		Status of the email.
Subject	String		Subject of the email.
FromEmail	String		The email of the sender.
ToEmail	String		The email of the receiver.
ClicksCount	Integer		The number of clicks.
OpensCount	Integer		The number of times that the email was opened.
LastEventTime	Datetime		The datetime of the event.
ApiKeyId	String		Id of the API Key.
AsmGroupId	String		ASM Group Id.
MarketingCampaignId	String		The id of the marketing campaign.
MarketingCampaignName	String		The name of the marketing campaign.
MarketingCampaignSplitId	String		Split id of the campaign.
MarketingCampaignVersion	String		Version of the campaign.
OriginatingIp	String		This is the IP address of the person sending the message
OutboundIp	String		This is the SendGrid dedicated IP address used to send the email
OutboundIpType	String		The type of the outbound IP.
Teammate	String		Username of the teammate.
TemplateId	String		Template ID of the email.
Events	String		List of events related to email message in SendGrid.

Query New Marketing Campaign Contacts in SendGrid.

View-Specific Information

New Marketing Campaign contacts are recepients that are managed by the user. NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the NMCContacts or filter them by Id, ListId or SegmentId:

SELECT * FROM NMCCustomFields WHERE Id = 'pe37d765-ba38-46d9-be55-1e45a1ee8c84'
SELECT * FROM NMCCustomFields WHERE Id IN ('pe37d765-ba38-46d9-be55-1e45a1ee8c84', 'fe37d765-ba38-46d9-be55-1e45a1ee8c84')
SELECT * FROM NMCCustomFields WHERE ListId = 't6d51a34-3981-4fa6-bd3f-abdd4de8ff9f'
SELECT * FROM NMCCustomFields WHERE ListId IN ('j6d51a34-3981-4fa6-bd3f-abdd4de8ff9f', 'f6d51b34-3981-4fa6-bd3f-abdd4de8ff9f')
SELECT * FROM NMCCustomFields WHERE SegmentId = '82aeee2d-2870-41e4-b43c-0dcf63481589'
SELECT * FROM NMCCustomFields WHERE SegmentId IN ('82aeee2d-2870-41e4-b43c-0dcf63481589', 'j9d51a34-3981-4fa6-bd3f-abdd4de8ff9f')

Columns

Name	Type	References	Description
Id [KEY]	String		This column is dynamic and maps to the corresponding field in the API.
AddressLine1	String		This column is dynamic and maps to the corresponding field in the API.
AddressLine2	String		This column is dynamic and maps to the corresponding field in the API.
City	String		This column is dynamic and maps to the corresponding field in the API.
Country	String		This column is dynamic and maps to the corresponding field in the API.
PostalCode	String		This column is dynamic and maps to the corresponding field in the API.
StateProvinceRegion	String		This column is dynamic and maps to the corresponding field in the API.
Line	String		This column is dynamic and maps to the corresponding field in the API.
Facebook	String		This column is dynamic and maps to the corresponding field in the API.
PhoneNumber	String		This column is dynamic and maps to the corresponding field in the API.
UniqueName	String		This column is dynamic and maps to the corresponding field in the API.
WhatsApp	String		This column is dynamic and maps to the corresponding field in the API.
Email	String		This column is dynamic and maps to the corresponding field in the API.
FirstName	String		This column is dynamic and maps to the corresponding field in the API.
LastName	String		This column is dynamic and maps to the corresponding field in the API.
CreatedAt	Datetime		This column is dynamic and maps to the corresponding field in the API.
UpdatedAt	Datetime		This column is dynamic and maps to the corresponding field in the API.
ItemUrl	String		This column is dynamic and maps to the corresponding field in the API.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
SegmentId	String		This column is dynamic and maps to the corresponding field in the API.
ListId	Uuid		This column is dynamic and maps to the corresponding field in the API.

Query the available New Marketing Campaign Reserved Fields in SendGrid.

View-Specific Information

Select

This table does not support filtering. NMC prefix stands for New Marketing Campaign.

SELECT * FROM NMCReservedFields

Columns

Name	Type	References	Description
Id	String		The Id of the reserved field.
Name	String		The name of the reserved field.
Type	String		The type of the reserved field.

Query New Marketing Campaign Segments Contacts sample in SendGrid.

Columns

Name	Type	References	Description
Id [KEY]	String		The Id of the contact sample.
Email	String		The email of the contact sample.
FirstName	String		The first name of the contact sample.
LastName	String		The last name of the contact sample.
NMCSegmentId	String	NMCSegments.Id	Segment id of the contacts table.

Query all the partner settings in SendGrid.

Table-Specific Information

Select

You can retrieve all the partner settings.

SELECT * FROM PartnerSettings

Columns

Name	Type	References	Description
Title	String		The title of the partner.
Enabled	String		Indicates if this partner setting has been enabled.
Name	String		The name of the partner setting.
Description	String		A description of this partner setting.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Retrieves Single Send click tracking statistics by Id.

View-Specific Information

Select

Get Single Send Click Tracking statistics. To access the view, you need to provide the Single Send ID.

 
SELECT * FROM SingleSendClickTrackingStats where SingleSendId = '74550809-66a2-11ef-ad7f-460f539623db'

Columns

Name	Type	References	Description
SingleSendId	String	NMCSingleSends.Id	The Id of Single Send for which you want to retrieve click tracking statistics.
Url	String		The URL of the link clicked. If {{custom_fields}} are part of the URL, they are included.
UrlLocation	Integer		The location of the link clicked in each Single Send A/B variation, or if there are no variations, the Single Send itself. Links are numbered from the top down; the topmost link is index 0.
ABvariation	String		The A/B variation of the Single Send statistic returned. It is set to 'all' if the ab_variation query parameter was not set in the request and group_by does not contain ab_variation.
ABphase	String		The A/B phase of the Single Send statistic returned. If the ab_phase query parameter was not provided, it returns 'all'.
Clicks	Integer		The number of clicks on this particular link.

Get all Single Sends statistics.

View-Specific Information

Select

The Sync App uses the SendGrid API to process search criteria that refer to the Id column, while other filters are processed client side within the Sync App.

Get all Single Sends statistics:

SELECT * FROM SingleSendsStats 

Get Single Send statistics by ID:

SELECT * FROM SingleSendsStats where id = '25723e10-669e-11ef-b50f-0e4b6974e9ad'

Columns

Name	Type	References	Description
Id [KEY]	String		The ID of the Single Send you require stats for.
ABvariation	String		The A/B variation of the Single Send statistic returned. If the group_by parameter doesn't include ab_variation in the request, then the value is 'all'.
ABphase	String		This is the A/B phase of the Single Send statistic returned. If the group_by parameter doesn't include ab_phase in the request, then the value is 'all'.
Aggregation	String		The time unit to which the statistic is rolled up. It is based on the aggregated_by parameter included in the request. It can be 'total' or the date (in YYYY-MM-DD format) the statistics are for.
BounceDrops	Integer		The number of dropped messages due to previous bounces.
Bounces	Integer		The number of email messages that bounced (not delivered to recipients).
Clicks	Integer		The total number of times links in the email were clicked.
Delivered	Integer		The total number of messages successfully delivered to recipient inboxes.
InvalidEmails	Integer		The number of email addresses that were invalid or did not exist.
Opens	Integer		The total number of times the email was opened.
Requests	Integer		The total number of email send requests made for the Single Send campaign.
SpamReportDrops	Integer		The number of messages not sent because recipients previously marked your emails as spam.
SpamReports	Integer		The number of times recipients reported the email as spam.
UniqueClicks	Integer		The number of unique recipients who clicked at least one link in the email.
UniqueOpens	Integer		The number of unique recipients who opened the email at least once.
Unsubscribes	Integer		The number of recipients who unsubscribed using the unsubscribe link in the email.

Query Browser Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByBrowser WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Browser Statistics by name.

SELECT * FROM StatsByBrowser WHERE Name = 'Firefox'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Clicks	Integer		The number of links clicked in your emails.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Query Client Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByClient WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Client Statistics by name.

SELECT * FROM StatsByClient WHERE Name = 'Other'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Query Device Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByDevice WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Device Statistics by name.

SELECT * FROM StatsByDevice WHERE Name = 'Phone'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Query Location Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByLocation WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Location Statistics by name.

SELECT * FROM StatsByLocation WHERE Name = 'US'

To query Location Statistics by type.

SELECT * FROM StatsByLocation WHERE Type = 'country'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Clicks	Integer		The number of links clicked in your emails.
Opens	Integer		The number of times your emails were opened by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Query Mailbox Provider Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByMailboxProvider WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Mailbox Provider Statistics by name.

SELECT * FROM StatsByMailboxProvider WHERE Name = 'Microsoft Outlook Live'

Columns

Name	Type	References	Description
Type	String		The type of the statistic.
Name	String		The name of the statistic.
Date	Date		The time the global statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped by SendGrid because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links clicked in your emails.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails confirmed delivered.
Opens	Integer		The number of times your emails were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid.
Requests	Integer		The number of emails you requested to send via SendGrid.
SpamReports	Integer		The number of emails marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked links in your emails.
UniqueOpens	Integer		The number of unique recipients who opened your emails.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String		The ID of the account.
SubuserUsername	String		The username of the subuser.

Query all the subusers in sendgrid.

View-Specific Information

Select

Query this table to retrieve all subusers.

SELECT * FROM Subusers

You can filter by Username to retrieve a specific subuser.

SELECT * FROM Subusers WHERE Username = 'Example'

Columns

Name	Type	References	Description
Id	Integer		The Id of the subuser.
Email	String		The email of the subuser.
Username	String		The username of the subuser.
Disabled	Boolean		Indicates whether the subuser is enabled or disabled.

Query Subuser Statistics in SendGrid.

View-Specific Information

Select

You can filter by SubuserName to retrieve stats of a specific subuser.

SELECT * FROM SubuserStats WHERE SubuserName = 'Example'

You can also filter by Date. Operators =, >=, <=, <, and > are supported for this column. If you do not specify one, a default span of the past 1 year is used.

SELECT * FROM SubuserStats WHERE SubuserName = 'News' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns

Name	Type	References	Description
Date	Date		The time the subuser statistic was created.
Blocks	Integer		The number of emails that were not allowed to be delivered by ISPs.
Drops	Integer		The number of emails automatically dropped because they appear on one of the suppression lists.
Bounces	Integer		The number of emails that bounced instead of being delivered.
Clicks	Integer		The number of links that were clicked in the emails from this subuser.
Deferred	Integer		The number of emails temporarily refused by the receiving server.
Delivered	Integer		The number of emails from this subsuer that were confirmed delivered.
InvalidEmails	Integer		The number of emails with a malformed address or reported as invalid by the receiving server.
Opens	Integer		The number of times emails from this subuser were opened by recipients.
Processed	Integer		The number of emails pushed to SendGrid by this subuser.
Requests	Integer		The number of emails that you requested to send via SendGrid from this subuser.
SpamReportDrops	Integer		The number of emails automatically dropped because the emails were marked as spam by the recipients.
SpamReports	Integer		The number of emails from this subuser that were marked as spam by recipients.
UniqueClicks	Integer		The number of unique recipients who clicked on links in your emails from this subuser.
UniqueOpens	Integer		The number of unique recipients who opened your emails from this subuser.
UnsubscribeDrops	Integer		The number of emails from this subuser dropped by SendGrid because the recipients unsubscribed from your emails.
Unsubscribes	Integer		The number of recipients who unsubscribed from your emails from this subuser.
SubuserName	String		The name of the statistic.
Type	String		The type of the statistic.

Retrieve paged transactional templates

View-Specific Information

Select

The following is an example to query the Templates view:

 
SELECT * FROM Templates

The Sync App will use the SendGrid API to process WHERE clause conditions built with the following column and operator.

• Generation supports the '='.

 
SELECT * FROM Templates WHERE Generation = 'dynamic'

Columns

Name	Type	References	Description
ID [KEY]	String		The ID of the transactional template.
Generation	String		Defines the generation of the template. By default, we will get data for both dynamic and legacy. The allowed values are dynamic, legacy.
Name	String		The name for the transactional template.
UpdatedAt	String		The date and time that this transactional template version was updated.
Versions	String		The different versions of this transactional template.

Stored procedures are function-like interfaces that extend the functionality of the Sync App beyond simple SELECT/INSERT/UPDATE/DELETE operations with SendGrid.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from SendGrid, along with an indication of whether the procedure succeeded or failed.

SendGrid Connector for CData Sync Stored Procedures

Name	Description
AddRecipientToList	Adds a recipient to a list.
GetImportContactsStatus	Gets the Import contacts status
GetScopes	Returns a list of all scopes that this user has access to.
ImportMarketingContacts	Import contacts to sendgrid account from CSV file.
SendMail	Sends a mail.
SendMail2	Sends a mail. This Stored Procedure is used to handle aggregates.
SendTestCampaign	Sends a test campaign.

Adds a recipient to a list.

Input

Name	Type	Required	Description
ListId	String	True	The unique identifier for the list.
RecipientId	String	True	The Id of the recipient.
AccountId	String	False	The ID of the account.
SubuserUsername	String	False	The username of the subuser.

Result Set Columns

Name	Type	Description
Success	String	Whether or not the recipient was successfully added to the list.

Download Marketing Campaign Stats for Automations

Stored Procedure Specific Information

The DownloadCampaignAutomationStats Stored Procedure requires CampaignID and the DownloadTo parameters to download the campaign automation stats in specific folder.

For Example:

EXEC DownloadCampaignAutomationStats Campaignid= 3091688, DownloadTo= 'C:\dev\tests\v20\ProviderSendGrid\'

Input

Name	Type	Required	Description
CampaignID	String	True	Comma seperated list of campaignIds.
Timezone	String	False	TimeZone of the location.
DownloadTo	String	False	Where to store the file.
Encoding	String	False	The FileData input encoding type. The allowed values are NONE, BASE64. The default value is BASE64.

Result Set Columns

Name	Type	Description
Success	String	Whether or not the campaign was downloaded successfully.
FileData	String	If the DownloadTo and FileStream are not provided, this contains the content of the file.

Gets the Import contacts status

Stored Procedure Specific Information

The GetImportContactsStatus Stored Procedure requires JobId of the Import Contacts, which we can get by executing ImportMarketingContacts stored procedure.

Example:

EXEC GetImportContactsStatus JobId= '1234'

Input

Name	Type	Required	Description
JobId	String	True	The Id of the Import Contacts Job

Result Set Columns

Name	Type	Description
Success	String	The Staus of the corresponding Import Contacts Job.
RequestedCount	String	The Number of contacts requested to modify through API.
CreatedCount	String	The Number of contacts created.
UpdatedCount	String	The Number of Updated contacts.
DeletedCount	String	The Number of Deleted contacts.

Returns a list of all scopes that this user has access to.

Input

Name	Type	Required	Description
AccountId	String	False	The Id of the account on whose behalf the call is made.
SubuserUsername	String	False	The username of the Subuser on whose behalf the call is made.

Result Set Columns

Name	Type	Description
Scopes	String	List of all scopes.

Import contacts to sendgrid account from CSV file.

Stored Procedure Specific Information

The ImportMarketingContacts Stored Procedure requires Field Mapping Ids and path of the File which contains contacts data.

We can Use custom_field ids or reserved_field ids for Field Mapping and We can get these ids using "Get All Field Definitions" endpoint.

NOTE:

1. Email field mapping is mandatory to import contacts from file.

2. As of now only csv file format is supported for this operation.

Example:

EXEC ImportMarketingContacts FileType= 'csv' , FieldMappings= '_rf2_T,_rf9_T' , FilePath = 'C:\Dev\Tests\v20\ProviderSendgrid\example.csv'

Input

Name	Type	Required	Description
FileType	String	False	The type of the file The allowed values are csv.
FieldMappings	String	True	Comma separated list of field_definition IDs
ListIds	String	False	Comma separated list of List id's
FilePath	String	False	The absolute path of the File to import contacts

Result Set Columns

Name	Type	Description
JobId	String	The Id of the Import Contacts Job

Sends a mail.

Procedure-Specific Information

The DynamicTemplateData input requires information about dynamic data of the template. Value for the different variables put into templates.

Example: {"fullname":"Test","paymenttotal":"Test","description":"Test"}

NOTE: The TemplateId column is required when using Dynamic Templates.

Process of adding Persanalizations object to SendMail Stored Procedure

SendMail Requires atleast one personalizations object in the request body and it can be added using the aggregate function.

For Example:

Using TEMP Tables.

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'abc')

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'xyz')

INSERT INTO To#TEMP (ToEmail) VALUES ('[email protected]')

INSERT INTO ToCC#TEMP (ToCCName, ToCCEmail) VALUES ('[email protected]', 'xyz')

INSERT INTO ToBCC#TEMP (ToBCCEmail, ToBCCName) VALUES ('[email protected]', 'abc')

INSERT INTO Content#TEMP (ContentValue, ContentType) VALUES ('[email protected]', 'text/plain')

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2', 'To#TEMP', 'ToCC#TEMP', 'ToBCC#TEMP')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content = 'Content#TEMP' 

Using Aggregate values.

Send Mail to single Mail Address.

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2',
         '[
		   {
              "ToEmail": "[email protected]",
              "ToName": "abc"
           }
		  ]',
		  '[
            {
              "ToCCName": "xyz",
              "ToCCEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToBCCEmail": "[email protected]",
              "ToBCCName": "abcd"
            }
          ]')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content =
'{
  "ContentValue":"content",
  "ContentType":"text/plain"
}'

Send Mail to Multiple Mail Addresses.

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2',
          '[
		    {
              "ToEmail": "[email protected]",
              "ToName": "abc"
            },
            {
              "ToEmail": "[email protected]",
              "ToName": "xyz"
            },
            {
              "ToEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToCCName": "sac",
              "ToCCEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToBCCEmail": "xyz",
              "ToBCCName": "[email protected]"
            }
          ]')
		  
EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content =
'{
  "ContentValue":"content",
  "ContentType":"text/html"
}'

Send Mail using attachment.

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'abc')

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'xyz')

INSERT INTO To#TEMP (ToEmail) VALUES ('[email protected]')

INSERT INTO ToCC#TEMP (ToCCName, ToCCEmail) VALUES ('[email protected]', 'xyz')

INSERT INTO ToBCC#TEMP (ToBCCEmail, ToBCCName) VALUES ('[email protected]', 'abc')

INSERT INTO Content#TEMP (ContentValue, ContentType) VALUES ('[email protected]', 'text/plain')

INSERT INTO Attachments#TEMP (AttachmentContent, AttachmentFilename) VALUES ('abcdefghijklmn', 'Test2.png')

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2', 'To#TEMP', 'ToCC#TEMP', 'ToBCC#TEMP')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content = 'Content#TEMP', Attachments = 'Attachments#TEMP'

Input

Name	Type	Required	Description
Subject	String	True	The subject of the mail to be sent.
ContentValue	String	False	The content of the mail to be sent.
ContentType	String	False	The type of content to be sent. supported values are text/plain and text/html
Content	String	True	The aggregate for Content object
SendAt	String	False	The date and time when you want your email to be delivered. Scheduling more than 72 hours in advance is forbidden.
FromEmail	String	True	The email address of the sender.
FromName	String	False	The name of the sender.
ToEmail	String	False	Email address to send the mail to
ToName	String	False	Email's respective names to send the mail to.
To	String	False	The aggregate for To recipients
ToCCEmail	String	False	Email address of the recipients who will receive a copy of your email
ToCCName	String	False	Name of the recipients who will receive a copy of your email.
ToCC	String	False	The aggregate for ToCC recipients.
ToBCCEmail	String	False	Email address of the recipients who will receive a blind carbon copy of your email.
ToBCCName	String	False	Name of the recipients who will receive a blind carbon copy of your email.
ToBCC	String	False	The aggregate for ToBCC recipients.
ReplyToEmail	String	False	The email address of the person to whom you are replying to.
ReplyToName	String	False	The name of the person to whom you are replying to.
AttachmentPaths	String	False	Path of the file to attach to the mail to be sent
TemplateId	String	False	The Id of the template that you may want to send
DynamicTemplateData	String	False	The data of the dynamic template.
Personalizations	String	True	An array of messages and their metadata. Each object within personalizations can be thought of as an envelope - it defines who should receive an individual message and how that message should be handled.
AttachmentFilename	String	False	The attachment's filename. Specify only when you don't specify the AttachmentPaths.
Attachments	String	False	An array of objects where you can specify any attachments you want to include. Specify only when you don't specify the AttachmentPaths.

Result Set Columns

Name	Type	Description
Success	String	Whether or not the mail was sent successfully.

Sends a mail. This Stored Procedure is used to handle aggregates.

Procedure-Specific Information

SendMail2 Stored Procedure is specifically made for handling the aggregates. User has to provide the whole json structure in order to execute this Stored Procedure.

For Example:

EXECUTE SendMail2 Personalizations = '[{"to": [{"email": "[email protected]", "name": "John Doe"}, {"email": "[email protected]", "name": "Julia Doe"}], "cc": [{"email": "[email protected]", "name": "Jane Doe"}], "bcc": [{"email": "[email protected]", "name": "Jim Doe"}]}, {"from": {"email": "[email protected]", "name": "Example Sales Team"}, "to": [{"email": "[email protected]", "name": "Janice Doe"}], "bcc": [{"email": "[email protected]", "name": "Jordan Doe"}]}]', FromEmail = '[email protected]', FromName = 'Example Order Confirmation', Subject = 'Test Subject', Content = '[{"type": "text/html", "value": "test value"}]', Categories = '["cake", "pie"]'

Input

Name	Type	Required	Description
Personalizations	String	True	An array of messages and their metadata. Each object within personalizations can be thought of as an envelope - it defines who should receive an individual message and how that message should be handled.
FromEmail	String	True	The email address of the sender.
FromName	String	True	The name of the sender.
ReplyToEmail	String	False	The email address of the person to whom you are replying to.
ReplyToName	String	False	The name of the person to whom you are replying to.
Subject	String	True	The global or 'message level' subject of your email.
Content	String	True	An array where you can specify the content of your email.
Attachments	String	False	An array of objects where you can specify any attachments you want to include.
Categories	String	False	An array of category names for this message. Each category name may not exceed 255 characters.
SendAt	String	False	A unix timestamp allowing you to specify when you want your email to be delivered.
BatchId	String	False	An ID representing a batch of emails to be sent at the same time.
AsmGroupId	String	False	The unsubscribe group to associate with this email.
AsmGroupsToDisplay	String	False	An array containing the unsubscribe groups that you would like to be displayed on the unsubscribe preferences page.
IpPoolName	String	False	The IP Pool that you would like to send this email from.
MailSettingsByPassListManagementEnable	String	False	A collection of different mail settings that you can use to specify how you would like this email to be handled. Indicates if this setting is enabled.
MailSettingsFooterEnable	String	False	The default footer that you would like included on every email. Indicates if this setting is enabled.
MailSettingsSandboxModeEnable	String	False	Sandbox Mode allows you to send a test email to ensure that your request body is valid and formatted correctly. Indicates if this setting is enabled.
TrackingSettingsClickTrackingEnable	String	False	Allows you to track if a recipient clicked a link in your email. Indicates if this setting is enabled.
TrackingSettingsClickTrackingEnableText	String	False	Allows you to track if a recipient clicked a link in your email. Indicates if this setting should be included in the text/plain portion of your email.
TrackingSettingsOpenTrackingEnable	String	False	Allows you to track if the email was opened by including a single pixel image in the body of the content. When the pixel is loaded, Twilio SendGrid can log that the email was opened. Indicates if this setting is enabled.
TrackingSettingsOpenTrackingSubstitutionTag	String	False	Allows you to track if the email was opened by including a single pixel image in the body of the content. When the pixel is loaded, Twilio SendGrid can log that the email was opened. Allows you to specify a substitution tag that you can insert in the body of your email at a location that you desire. This tag will be replaced by the open tracking pixel.
TrackingSettingsSubscriptionTrackingEnable	String	False	Allows you to insert a subscription management link at the bottom of the text and HTML bodies of your email. Indicates if this setting is enabled.
TemplateId	String	False	The Id of the template that you may want to send

Result Set Columns

Name	Type	Description
Success	String	Whether or not the mail was sent successfully.

Sends a test campaign.

Input

Name	Type	Required	Description
To	String	True	The email address to send the campaign to.
Campaign_ID	String	True	The Id of the campaign to send.
AccountId	String	False	The ID of the account.
SubuserUsername	String	False	The username of the subuser.

Result Set Columns

Name	Type	Description
Success	String	Whether or not the campaign was sent successfully.

The CData Sync App models the Legacy API as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files.

However, the Recipients table is dynamic: The Sync App connects to SendGrid and gets the metadata for this table by calling the appropriate Web services. Any changes you make to the custom filters you define for Recipients are available when you connect; you can use these custom filters as columns in the WHERE clause. These custom filters are surfaced in the CustomFields table.

The Sync App uses the SendGrid API to process supported filters. The Sync App processes other filters client-side within the Sync App.

To use the Legacy Data Model, simply set Schema to Legacy.

Tables

Tables describes the available tables that are easy to query and update.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to Sendgrid. They can be used to search, update, and modify information.

The Sync App models the data in SendGrid as a list of tables in a relational database that can be queried using standard SQL statements.

SendGrid Connector for CData Sync Tables

Name	Description
Alerts	Create, update, delete, and query Alerts in SendGrid.
ApiKeys	Query, Create, Update and Delete the available ApiKeys in SendGrid.
Blocks	Query and Delete the available Blocks in SendGrid.
Bounces	Query and Delete the available Bounces in SendGrid.
CancelScheduledSends	Update, delete, and query Cancel Scheduled Sends in SendGrid.
CustomFields	Create, delete, and query Custom Fields in SendGrid.
GlobalUnsubscribes	Query Global Unsubscribes in SendGrid.
InvalidEmails	Query and Delete invalid emails in SendGrid.
Lists	Create, update, delete, and query the available Lists in SendGrid.
MarketingCampaigns	Create, update, delete, and query Marketing Campaigns in SendGrid.
Recipients	The SendGrid table Recipients.
Schedules	Create, update, delete, and query Campaign Schedules in SendGrid.
Segments	Create, update, delete, and query Segments in SendGrid.
SenderIdentities	Create, update, delete, and query Sender Identities in SendGrid.
Spam	Query and delete Spam reports in SendGrid.
Teammates	Query Teammates in SendGrid.
UnsubscribeGroups	Create, update, delete, and query Unsubscribe Groups in SendGrid.
Users	Query the available Users in SendGrid.

Create, update, delete, and query Alerts in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM Alerts WHERE Id = '148825'

Insert

The following columns are required in an insert for Stats Notification: Type, EmailTo, Frequency.

INSERT INTO Alerts (Type, EmailTo, Frequency) VALUES ('stats_notification', '[email protected]', 'daily')

The following columns are required in an insert for Usage Limit: Type, EmailTo, Percentage.

INSERT INTO Alerts (Type, EmailTo, Percentage) VALUES ('usage_limit', '[email protected]', 20)

Update

You can only update EmailTo by specifying an Id.

UPDATE Alerts SET EmailTo = '[email protected]' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM Alerts WHERE Id = 1310789

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the alert.
CreatedAt	Datetime	True		Date when the alert was created.
UpdatedAt	Datetime	True		Date when the alert was updated.
EmailTo	String	False		The email address that the alert will be sent to.
Percentage	Integer	False		The email address that the alert will be sent to.
Type	String	False		The type of alert. Allowed values (usage_limit, stats_notification).
Frequency	String	False		If the alert is of type stats_notification, this indicates how frequently the stats notifications will be sent.(daily, weekly, monthly).

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query, Create, Update and Delete the available ApiKeys in SendGrid.

Table-Specific Information

Select

To get all the Api Keys

SELECT * FROM ApiKeys

To get information about a specific Api Key

SELECT * FROM Apikeys WHERE ApiKeyId = 'Dqdvdvdvdv4yLeuVQqO3rFxN5ktUxA'

Insert

The following columns are required in an insert: Name. VALUES for Scopes are: alerts.create, alerts.read, alerts.update, alerts.delete, asm.groups.create, asm.groups.read, asm.groups.update, asm.groups.delete, ips.pools.ips.read, mail.send, mail_settings.bcc.read, mail_settings.bcc.update, mail_settings.address_whitelist.read, mail_settings.address_whitelist.update, mail_settings.footer.read, mail_settings.footer.update, mail_settings.forward_spam.read, mail_settings.forward_spam.update, mail_settings.plain_content.read, mail_settings.plain_content.update, mail_settings.spam_check.read, mail_settings.spam_check.update, mail_settings.bounce_purge.read, mail_settings.bounce_purge.update, mail_settings.forward_bounce.read, mail_settings.forward_bounce.update, partner_settings.new_relic.read, partner_settings.new_relic.update, partner_settings.sendwithus.read, partner_settings.sendwithus.update, tracking_settings.click.read, tracking_settings.click.update, tracking_settings.subscription.read, tracking_settings.subscription.update, tracking_settings.open.read, tracking_settings.open.update, tracking_settings.google_analytics.read, tracking_settings.google_analytics.update, user.webhooks.event.settings.read, user.webhooks.event.settings.update, user.webhooks.event.test.create, user.webhooks.event.test.read, user.webhooks.event.test.update, user.webhooks.parse.settings.create, user.webhooks.parse.settings.read, user.webhooks.parse.settings.update, user.webhooks.parse.settings.delete, stats.read, stats.global.read, categories.stats.read, categories.stats.sums.read, devices.stats.read, clients.stats.read, clients.phone.stats.read, clients.tablet.stats.read, clients.webmail.stats.read, clients.desktop.stats.read, geo.stats.read, mailbox_providers.stats.read, browsers.stats.read, user.webhooks.parse.stats.read, templates.create, templates.read, templates.update, templates.delete, templates.versions.create, templates.versions.read, templates.versions.update, templates.versions.delete, templates.versions.activate.create, user.timezone.read, user.timezone.update, user.settings.enforced_tls.read, user.settings.enforced_tls.update, api_keys.create, api_keys.read, api_keys.update, api_keys.delete, email_activity.read, categories.create, categories.read, categories.update, categories.delete, mail_settings.template.read, mail_settings.template.update, marketing_campaigns.create, marketing_campaigns.read, marketing_campaigns.update, marketing_campaigns.delete, mail.batch.create, mail.batch.read, mail.batch.update, mail.batch.delete, user.scheduled_sends.create, user.scheduled_sends.read, user.scheduled_sends.update, user.scheduled_sends.delete, access_settings.whitelist.create, access_settings.whitelist.read, access_settings.whitelist.update, access_settings.whitelist.delete, access_settings.activity.read, whitelabel.create, whitelabel.read, whitelabel.update, whitelabel.delete, suppression.create, suppression.read, suppression.update, suppression.delete, messages.read

INSERT INTO Apikeys (Name) VALUES ('apiname')

INSERT INTO Apikeys (Name, Scopes, Sample) VALUES ('apiname', 'alerts.create,alerts.read', 'test')

Update

You can update ApiKeys by specifying an ApiKeyid.You need to specify both the name and the scopes during the UPDATE even if you don't want to update it.

UPDATE Apikeys SET Name = 'apiname2', Scopes = 'alerts.create,alerts.read' WHERE apikeyid = 'Dqdvdvdvdv4yLeuVQqO3rFxN5kscsctUxA-P_Q'

Delete

You can Delete ApiKeys by specifying an ApiKeyid.

DELETE FROM ApiKeys WHERE apikeyid = 'CMp4C8klscscscsQ0GxqvxtK2-P_Q'

Columns

Name	Type	ReadOnly	References	Description
ApiKeyId [KEY]	String	False		The ID of your API Key.
Name	String	False		The name of your API Key.
Scopes	String	False		The permissions this API Key has access to. This is populated only when Id is specified.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query and Delete the available Blocks in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Blocks WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Blocks by specifying an Email.

DELETE FROM Blocks WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the block was created.
Reason	String	False		The reason for the block.
Email [KEY]	String	False		The email that blocked.
Status	String	False		The enhanced SMTP block response.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query and Delete the available Bounces in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Bounces WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Bounces by specifying an Email.

DELETE FROM Bounces WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
CreatedAt	Datetime	True		The time the bounce was created.
Reason	String	False		The reason for the bounce.
Email [KEY]	String	False		The email that bounced.
Status	String	False		The enhanced SMTP bounce response.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Update, delete, and query Cancel Scheduled Sends in SendGrid.

Table-Specific Information

Select

This table only supports filtering by BatchId. The following are the only possible SELECT queries:

SELECT * FROM CancelScheduledSends
SELECT * FROM CancelScheduledSends WHERE BatchId = 148825

Update

You can update CancelScheduledSends by specifying an BatchId.

Allowed Values : cancel, pause

UPDATE CancelScheduledSends SET Status = 'cancel' WHERE BatchId = 1310789

Delete

You can only delete from CancelScheduledSends by specifying an BatchId.

DELETE FROM CancelScheduledSends WHERE BatchId = 1310789

Columns

Name	Type	ReadOnly	References	Description
BatchId [KEY]	String	False		The Id of the batch.
Status	String	False		The status of the scheduled send.Allowed Values: cancel, pause

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Create, delete, and query Custom Fields in SendGrid.

Table Specific Information

Custom fields are filters for a Recipient that are defined by the user.

Select

You can retrieve all the CustomFields or filter them by Id:

SELECT * FROM CustomFields WHERE Id = '1'

Use quotations to filter on Custom Fields regardless of type.

Insert

To insert on CustomFields specify the Name of the field and its Type. The Type can be text, date, or number.

INSERT INTO CustomFields (name, type) VALUES ('age', 'number') 

Update

This table does not support updating.

Delete

You can only delete from CustomFields by specifying an Id:

DELETE FROM CustomFields WHERE Id = '1'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	False		The Id of the custom field.
Name	String	False		The name of the custom field.
Type	String	False		The type of the custom field. Allowed values are number, text, or date.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query Global Unsubscribes in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. However this filter is optional. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalUnsubscribes WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

INSERT

Performing an Insert operation in this table you need to specify an email which will be added to the GlobalUnsubscribes list. For example:

INSERT INTO GlobalUnsubscribes (Email) VALUES ('[email protected]')

DELETE

Performing a Delete operation in this table you need to specify an email which will be removed from the GlobalUnsubscribes list. For example:

DELETE FROM GlobalUnsubscribes WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
Email [KEY]	String	False		Email of the unsubscriber.
CreatedAt	Datetime	True		The time the unsubscriber was created.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Query and Delete invalid emails in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM InvalidEmails
SELECT * FROM InvalidEmails WHERE Email = '[email protected]'

Delete

You can only delete from InvalidEmails by specifying an Email.

DELETE FROM InvalidEmails WHERE Email = '[email protected]'

Columns

Name	Type	ReadOnly	References	Description
Created	Datetime	True		The time the when the email address was added to the invalid emails list.
Reason	String	False		A reason explaining why the email address was marked as invalid..
Email [KEY]	String	False		The email address that was marked as invalid.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Create, update, delete, and query the available Lists in SendGrid.

Table-Specific Information

Select

The Lists table can only filtered by its Id.

SELECT * FROM Lists WHERE Id = '123'

Insert

Only the Name for the List is required to insert into this table.

INSERT INTO Lists (Name) VALUES ('TestList')

Update

You can only update from Lists by specifying an Id.

UPDATE Lists SET Name = 'New Name' WHERE Id = '123'

Delete

You can only delete from Lists by specifying an Id.

DELETE FROM Lists WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The Id of the list.
Name	String	False		The name of the list.
RecipientCount	Integer	True		The recipient count of the list.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.

Create, update, delete, and query Marketing Campaigns in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM MarketingCampaigns
SELECT * FROM MarketingCampaigns WHERE Id = '1'

Insert

The following columns are required in an insert: Title, Subject, SenderId, ListIds, SegmentIds, Categories, SuppressionGroupId, HtmlContent, and PlainContent.

Note that a CustomUnsubscribeUrl can be used in place of a SuppressionGroupId. However, SendGrid highly recommends to use SuppressionGroupId.

Use the check points below to find the SenderId and SuppressionGroupId inputs:

• To find the SenderId, navigate to Marketing -> MarketingCampaigns -> Senders. Create a new sender if you do not have one already. Then click the gear on the Action column and select Edit. The Id is displayed on the navigation bar after /senders/id.
• To find the SuppressionGroupId, insert into the UnsubscribeGroups table; the created Id is the SuppressionGroupId.

INSERT INTO MarketingCampaigns (Title, Subject, SenderId, ListIds, Categories, SuppressionGroupId, HtmlContent, PlainContent) VALUES ('Test Campaign', 'March Newsletter', '123', '1,2', 'fruits, health', '12', '<b>Eat Healthy</b>', 'Eat healthy')

Update

You can only update MarketingCampaigns by specifying an Id.

UPDATE MarketingCampaigns SET PlainContent = 'Eat healthier' WHERE Id = '123'

Delete

You can only delete from MarketingCampaigns by specifying an Id.

DELETE FROM MarketingCampaigns WHERE Id = '123'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The Id of the marketing campaign.
Title	String	False		The title of the marketing campaign.
Subject	String	False		The subject of the marketing campaign.
SenderId	Integer	False		The sender Ids of the marketing campaign, separated by a comma.
ListIds	String	False		The list of list Ids of the marketing campaign, separated by a comma.
SegmentIds	String	False		The list of segment Ids of the marketing campaign, separated by a comma.
Categories	String	False		The categories of the marketing campaign.
SuppressionGroupId	Integer	False		The suppression group Id of the marketing campaign.
CustomUnsubscribeUrl	String	False		The custom unsubscribe URL of the marketing campaign.
IpPool	String	False		The IP pool of the marketing campaign.
HtmlContent	String	False		The HTML content of the marketing campaign.
PlainContent	String	False		The content of the marketing campaign.
Status	String	True		The status of the marketing campaign.
Editor	String	False		The editor used in the UI. The allowed values are code, design.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
AccountId	String	The ID of the account.
SubuserUsername	String	The username of the subuser.