Connecting to CustomerIO

Using API Key Authentication

To obtain your Customer.io App API Key, navigate to the Customer.io UI under Data & Integrations > Integrations > Customer.io API and generate your API key.

After setting the following connection properties, you are ready to connect:

AuthScheme: Set this to APIKey.
APIKey: Set this to your Customer.io App API Key.

Example Connection String

Profile=C:\profiles\CustomerIO.apip;AuthScheme=APIKey;ProfileSettings="APIKey=your_api_key";

Connecting to CustomerIO

Once the authentication is configured, you can connect to CustomerIO and read, create, update, and delete data across the available tables such as Customers, Campaigns, Segments, and Collections.

Available Tables

The CustomerIO API Profile provides access to comprehensive customer engagement and marketing automation data across 27 specialized tables:

Customers - Access customer profile data with email addresses and Customer.io identifiers.
Activities - Query customer activity tracking data including email and SMS activities.
Campaigns - Access campaign definitions and configuration from Customer.io campaigns.
Broadcasts - Retrieve broadcast campaign data with scheduling and targeting information.
Segments - Create and query customer segment definitions; supports INSERT and DELETE.
Collections - Create, update, and delete data collections and schema definitions; supports INSERT, UPDATE, and DELETE.
Exports - Query data export operations and status tracking.
TransactionalMessages - Access transactional email and message data.
Workspaces - Query workspace configuration and settings.
CustomerAttributes - Access detailed customer attributes and metadata.
CustomerMessages - Query customer-specific message history and delivery data.
BroadcastMessages - Access broadcast message details and delivery status.
CampaignMessages - Query campaign message data with performance metrics.
CustomerActivities - Access customer-specific activity data and engagement metrics.
SenderIdentities - Query email sender identity configurations.
Snippets - Create, update, and delete reusable content snippets; supports INSERT, UPDATE, and DELETE.
Imports - Initiate and query data import operations; supports INSERT.
SubscriptionTopics - Access subscription topic definitions and preferences.
CustomerSegments - Query customer segment membership data.
CustomerSubscriptionPreferences - Access customer subscription preference settings.
ESPSuppressions - Query email service provider suppression lists.
BroadcastActions - Query and update broadcast action details and configuration; supports UPDATE.
CampaignActions - Query and update campaign action details and configuration; supports UPDATE.
BroadcastTriggers - Access broadcast trigger definitions and conditions.
TransactionalMessageVariants - Query and update transactional message content variants; supports UPDATE.
ESPSuppressionsByType - Access email suppressions organized by suppression type.

Important Notes

Several tables require a parent identifier as a filter in the WHERE clause (e.g. CustomerId for CustomerAttributes, CustomerMessages, CustomerActivities, and CustomerSegments; BroadcastId for BroadcastActions and BroadcastTriggers; CampaignId for CampaignActions and CampaignMessages; TransactionalMessageId for TransactionalMessageVariants).
When inserting into the Imports table, ObjectTypeId is required if the Type is object. For all other import types (people, events, relationships), Identifier is required instead.
Snippet names are unique. When updating a snippet, if the snippet name does not exist, a new snippet is created. If the name already exists, the existing snippet is updated.

API Documentation

For more information about the Customer.io API, see the official Customer.io API documentation.

Usage Examples

Query customers by email:

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

Query customer activities by type:

SELECT * FROM Activities WHERE Type = 'sent_email'

Query all campaigns:

SELECT * FROM Campaigns

Query all broadcasts:

SELECT * FROM Broadcasts

Query all segments:

SELECT * FROM Segments

INSERT INTO Segments (Name, Description) VALUES ('Active Users', 'Customers who were active in the last 30 days')

DELETE FROM Segments WHERE Id = 123

Query all collections:

SELECT * FROM Collections

INSERT INTO Collections (Name, Url) VALUES ('Product Catalog', 'https://example.com/data/products.json')

UPDATE Collections SET Name = 'Updated Product Catalog', Url = 'https://example.com/data/products_v2.json' WHERE Id = 'coll-abc123'

DELETE FROM Collections WHERE Id = 'coll-abc123'

Query exports by status:

SELECT * FROM Exports WHERE Status = 'done'

Query all transactional messages:

SELECT * FROM TransactionalMessages

Query all workspaces:

SELECT * FROM Workspaces

Query customer attributes for specific customer:

SELECT * FROM CustomerAttributes WHERE CustomerId = '12345'

Query customer messages for specific customer:

SELECT * FROM CustomerMessages WHERE CustomerId = '12345'

Query broadcast messages for specific broadcast:

SELECT * FROM BroadcastMessages WHERE BroadcastId = 123

Query all campaign messages:

SELECT * FROM CampaignMessages WHERE CampaignId = 456

Query customer activities by type:

SELECT * FROM CustomerActivities WHERE CustomerId = '12345'

Query all sender identities:

SELECT * FROM SenderIdentities

Query all snippets:

SELECT * FROM Snippets

INSERT INTO Snippets (Name, Value) VALUES ('company_name', 'Acme Corporation')

UPDATE Snippets SET Value = 'Acme Corp' WHERE Name = 'company_name'

DELETE FROM Snippets WHERE Name = 'company_name'

Query imports by import ID:

SELECT * FROM Imports WHERE Id = 123

INSERT INTO Imports (Name, Type, DataFileUrl, Description, DataToProcess) VALUES ('Customer Import Q3', 'people', 'https://example.com/data/customers.csv', 'Q3 customer data import', 'all')

Query all subscription topics:

SELECT * FROM SubscriptionTopics

Query customer segments for specific customer:

SELECT * FROM CustomerSegments WHERE CustomerId = '12345'

Query customer subscription preferences:

SELECT * FROM CustomerSubscriptionPreferences WHERE CustomerId = '12345'

Query all ESP suppressions:

SELECT * FROM ESPSuppressions

Query broadcast actions for specific broadcast:

SELECT * FROM BroadcastActions WHERE BroadcastId = 123

UPDATE BroadcastActions SET FromId = 5, ReplyToId = 3, Subject = 'Summer Campaign', SendingState = 'automatic' WHERE BroadcastId = 123 AND Id = 456

Query campaign actions for specific campaign:

SELECT * FROM CampaignActions WHERE CampaignId = 456

UPDATE CampaignActions SET FromId = 5, ReplyToId = 3, Subject = 'Welcome Email', SendingState = 'draft' WHERE CampaignId = 456 AND Id = 789

Query all broadcast triggers:

SELECT * FROM BroadcastTriggers WHERE BroadcastId = 123

Query all transactional message variants:

SELECT * FROM TransactionalMessageVariants WHERE TransactionalMessageId = 123

UPDATE TransactionalMessageVariants SET FromId = 5, ReplyToId = 3, Subject = 'Your Order Confirmation', Body = 'Thank you for your order!' WHERE TransactionalMessageId = 123 AND Id = 456

Query ESP suppressions by type:

SELECT * FROM ESPSuppressionsByType WHERE SuppressionType = 'bounce'

Connection Properties

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider.

Property	Description
APIKey	Your Customer.io App API Key.
AuthScheme	The scheme used for authentication. Accepted entries are APIKey, or None. Allowed values are: BASIC, NONE, NTLM, OAUTH, APIKEY, OAUTH_CLIENT, OAUTH_PASSWORD