# Integration Specifications

## Integration Overview

Iterable is a cross-channel marketing platform that powers unified customer experiences and empowers businesses to create, optimize, and measure every interaction across the entire customer journey. Iterable leverages real-time data to personalize email campaigns, dynamically segmenting users and triggers with relevant messaging using Workflow Studio.

This document provides context on what kind of data is being gathered through this extractor, which endpoints that data is coming from, and how the extracted tables relate to each other.

## **Integration Availability**

This integration is available for:

* Enterprise

## API Endpoints

The Daasity Iterable extractor is built based on this [Iterable API documentation](https://api.iterable.com/api/docs).  The following endpoint is used by Daasity to replicate data from Iterable:

* [Campaigns](https://api.iterable.com/api/docs#campaigns_campaigns)
* [Channels](https://api.iterable.com/api/docs#channels_channels)
* [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* [Lists](https://api.iterable.com/api/docs#lists_getLists)
* [MessageTypes](https://api.iterable.com/api/docs#messageTypes_messageTypes)
* [Users](https://api.iterable.com/api/docs#users_getUser)

## Entity Relationship Diagram (ERD)

[Click here to view the ERD for the Daasity Pepperjam integration](https://lucid.app/documents/embedded/22b05e63-bb83-4e0c-a9d8-ac80410b7360) illustrating the different tables and keys to join across tables.

## Iterable Schema

The Daasity Iterable extractor creates these tables using the endpoints and replication methods listed. The data is mapped from source API endpoint to the table based on the mapping logic outlined in each table.

* [Campaigns](#campaigns)
* [Campaign Lists](#campaign-lists)
* [Campaign Suppression Lists](#campaign-suppression-lists)
* [Campaign Labels](#campaign-labels)
* [Lists](#lists)
* [Users](#users)
* [User Lists](#user-lists)
* [Email Subscribes](#email-subscribes)
* [Email Unsubscribes](#email-unsubscribes)
* [Email Unsubscribe Lists](#email-unsubscribe-lists)
* [Channels](#channels)
* [User Unsubscribed Channels](#user-unsubscribed-channels)
* [Email Unsubscribe Channels](#email-unsubscribed-channels)
* [Message Types](#message-types)
* [User Unsubscribed Message Types](#user-unsubscribed-message-types)
* [Email Sends](#email-sends)
* [Email Bounces](#email-bounces)
* [Email Complaints](#email-complaints)
* [Email Opens](#email-opens)
* [Email Clicks](#email-clicks)

### **Campaigns**

* Endpoint: [Campaigns](https://api.iterable.com/api/docs#campaigns_campaigns)
* Update Method: UPSERT
* Table Name: \[`iterable.campaigns`]

| JSON Element                           | Database Column |
| -------------------------------------- | --------------- |
| id                                     | campaign\_id    |
| createdAt                              | created\_at     |
| updatedAt                              | updated\_at     |
| startAt                                | started\_at     |
| endedAt                                | ended\_at       |
| name                                   | campaign\_name  |
| templateId                             | template\_id    |
| messageMedium                          | message\_medium |
| createdByUserId                        | created\_by     |
| campaignState                          | campaign\_state |
| workflowId                             | workflow\_id    |
| sendSize                               | send\_size      |
| type                                   | campaign\_type  |
| Daasity: source\_id                    | \_\_source\_id  |
| Daasity: account\_id                   | \_account\_id   |
| Daasity: MD5(source\_id + id)          | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB | \_\_synced\_at  |

### **Campaign Lists**

* Endpoint: [Campaigns](https://api.iterable.com/api/docs#campaigns_campaigns)
* Update Method: UPSERT
* Table Name: \[`iterable.campaign_lists`]

| JSON Element                                    | Database Column |
| ----------------------------------------------- | --------------- |
| id                                              | campaign\_id    |
| listIds::listId                                 | list\_id        |
| Daasity: source\_id                             | \_\_source\_id  |
| Daasity: account\_id                            | \_account\_id   |
| Daasity: MD5(source\_id + id + listIds::listId) | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB          | \_\_synced\_at  |

### **Campaign Suppression Lists**

* Endpoint: [Campaigns](https://api.iterable.com/api/docs#campaigns_campaigns)
* Update Method: UPSERT
* Table Name: \[`iterable.campaign_suppression_lists`]

| JSON Element                                               | Database Column |
| ---------------------------------------------------------- | --------------- |
| id                                                         | campaign\_id    |
| suppressionListIds::listId                                 | list\_id        |
| Daasity: source\_id                                        | \_\_source\_id  |
| Daasity: account\_id                                       | \_account\_id   |
| Daasity: MD5(source\_id + id + suppressionListIds::listId) | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB                     | \_\_synced\_at  |

### **Campaign Labels**

* Endpoint: [Campaigns](https://api.iterable.com/api/docs#campaigns_campaigns)
* Update Method: UPSERT
* Table Name: \[`iterable.campaign_labels`]

| JSON Element                                  | Database Column  |
| --------------------------------------------- | ---------------- |
| id                                            | campaign\_id     |
| labels::label                                 | campaign\_labels |
| Daasity: source\_id                           | \_\_source\_id   |
| Daasity: account\_id                          | \_account\_id    |
| Daasity: MD5(source\_id + id + labels::label) | \_\_sync\_key    |
| Daasity: timestamp when loaded into DB        | \_\_synced\_at   |

### **Lists**

* Endpoint: [Lists](https://api.iterable.com/api/docs#lists_getLists)
* Update Method: UPSERT
* Table Name: \[`iterable.lists`]

| JSON Element                           | Database Column |
| -------------------------------------- | --------------- |
| id                                     | list\_id        |
| name                                   | list\_name      |
| createdAt                              | created\_at     |
| listType                               | list\_type      |
| Daasity: source\_id                    | \_\_source\_id  |
| Daasity: account\_id                   | \_account\_id   |
| Daasity: MD5(source\_id + id)          | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB | \_\_synced\_at  |

### **Users**

* Endpoint: [Users](https://api.iterable.com/api/docs#users_getUser)
* Update Method: UPSERT
* Table Name: \[`iterable.users`]

| JSON Element                           | Database Column         |
| -------------------------------------- | ----------------------- |
| userId                                 | user\_id                |
| email                                  | email                   |
| signupDate                             | sign\_up\_date          |
| profileUpdatedAt                       | profile\_updated\_at    |
| publicIdString                         | public\_id\_string      |
| verified\_email                        | verified\_email         |
| accepts\_marketing                     | accepts\_marketing      |
| phoneNumber, limit 64                  | phone\_number           |
| email\_signup\_location                | email\_signup\_location |
| tags, limit: 4095                      | tags                    |
| signupSource                           | sign\_up\_source        |
| timeZone                               | user\_time\_zone        |
| Daasity: source\_id                    | \_\_source\_id          |
| Daasity: account\_id                   | \_account\_id           |
| Daasity: MD5(source\_id + userId)      | \_\_sync\_key           |
| Daasity: timestamp when loaded into DB | \_\_synced\_at          |

### **User Lists**

* Endpoint: [Users](https://api.iterable.com/api/docs#users_getUser)
* Update Method: UPSERT
* Table Name: \[`iterable.user_lists`]

| JSON Element                                         | Database Column |
| ---------------------------------------------------- | --------------- |
| userId                                               | user\_id        |
| emailListIds::id                                     | list\_id        |
| Daasity: source\_id                                  | \_\_source\_id  |
| Daasity: account\_id                                 | \_account\_id   |
| Daasity: MD5(source\_id + userId + emailListIds::id) | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB               | \_\_synced\_at  |

### **Email Subscribes**

* Endpoint: [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* Update Method: UPSERT
* Table Name: \[`iterable.email_subscribes`]

| JSON Element                                                 | Database Column     |
| ------------------------------------------------------------ | ------------------- |
| messageId                                                    | message\_id         |
| email                                                        | email               |
| templateId                                                   | template\_id        |
| camapaignId                                                  | campaign\_id        |
| userAgent                                                    | user\_Agent         |
| userAgentDevice                                              | user\_agent\_device |
| hrefIndex                                                    | href\_index         |
| url, limi: 4095                                              | url                 |
| createdAt                                                    | created\_at         |
| Daasity: source\_id                                          | \_\_source\_id      |
| Daasity: account\_id                                         | \_account\_id       |
| Daasity: MD5(source\_id + messageId + hrefIndex + createdAt) | \_\_sync\_key       |
| Daasity: timestamp when loaded into DB                       | \_\_synced\_at      |

### **Email Unsubscribes**

* Endpoint: [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* Update Method: UPSERT
* Table Name: \[`iterable.email_unsubscribes`]

| JSON Element                                 | Database Column |
| -------------------------------------------- | --------------- |
| email\_unsubscribes::email                   | email           |
| email\_unsubscribes::unsubSource             | unsub\_source   |
| email\_unsubscribes::campaignId              | campaign\_id    |
| email\_unsubscribes::workflowId              | workflow\_id    |
| email\_unsubscribes::templateId              | template\_id    |
| email\_unsubscribes::createdAt               | created\_at     |
| Daasity: source\_id                          | \_\_source\_id  |
| Daasity: account\_id                         | \_account\_id   |
| Daasity: MD5(source\_id + email + createdAt) | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB       | \_\_synced\_at  |

### **Email Unsubscribe Lists**

* Endpoint: [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* Update Method: UPSERT
* Table Name: \[`iterable.email_unsubscribe_lists`]

| JSON Element                                                    | Database Column |
| --------------------------------------------------------------- | --------------- |
| emailListIds::email                                             | email           |
| emailListIds::id                                                | list\_id        |
| emailListIds::createdAt                                         | created\_at     |
| Daasity: source\_id                                             | \_\_source\_id  |
| Daasity: account\_id                                            | \_account\_id   |
| Daasity: MD5(source\_id + email + createdAt + emailListIds::id) | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB                          | \_\_synced\_at  |

### **Channels**

* Endpoint: [Channels](https://api.iterable.com/api/docs#channels_channels)
* Update Method: UPSERT
* Table Name: \[`iterable.channels`]

| JSON Element                           | Database Column |
| -------------------------------------- | --------------- |
| id                                     | channel\_id     |
| name                                   | channel\_name   |
| channelType                            | channel\_type   |
| messageMedium                          | message\_medium |
| Daasity: source\_id                    | \_\_source\_id  |
| Daasity: account\_id                   | \_account\_id   |
| Daasity: MD5(source\_id + id)          | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB | \_\_synced\_at  |

### **User Unsubscribed Channels**

* Endpoint: [Users](https://api.iterable.com/api/docs#users_getUser)
* Update Method: UPSERT
* Table Name: \[`iterable.user_unsubscribed_channels`]

| JSON Element                                                                         | Database Column |
| ------------------------------------------------------------------------------------ | --------------- |
| unsubscribedChanelIds::userId                                                        | user\_id        |
| unsubscribedChanelIds::id                                                            | channel\_id     |
| Daasity: source\_id                                                                  | \_\_source\_id  |
| Daasity: account\_id                                                                 | \_account\_id   |
| Daasity: MD5(source\_id + unsubscribedChanelIds::userId + unsubscribedChanelIds::id) | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB                                               | \_\_synced\_at  |

### **Email Unsubscribed Channels**

* Endpoint: [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* Update Method: UPSERT
* Table Name: \[`iterable.email_unsubscribed_channels`]

| JSON Element                                                                          | Database Column |
| ------------------------------------------------------------------------------------- | --------------- |
| channelIds::email                                                                     | email           |
| channelIds::id                                                                        | channel\_id     |
| channelIds::createdAt                                                                 | created\_at     |
| Daasity: source\_id                                                                   | \_\_source\_id  |
| Daasity: account\_id                                                                  | \_account\_id   |
| Daasity: MD5(source\_id + channelIds::email + channelIds::createdAt + channelIds::id) | \_\_sync\_key   |
| Daasity: timestamp when loaded into DB                                                | \_\_synced\_at  |

### **Message Types**

* Endpoint: [MessageTypes](https://api.iterable.com/api/docs#messageTypes_messageTypes)
* Update Method: UPSERT
* Table Name: \[`iterable.message_types`]

| JSON Element                           | Database Column     |
| -------------------------------------- | ------------------- |
| id                                     | message\_type\_id   |
| name                                   | message\_type\_name |
| channelId                              | channel\_id         |
| Daasity: source\_id                    | \_\_source\_id      |
| Daasity: account\_id                   | \_account\_id       |
| Daasity: MD5(source\_id + id)          | \_\_sync\_key       |
| Daasity: timestamp when loaded into DB | \_\_synced\_at      |

### **User Unsubscribed Message Types**

* Endpoint: [Users](https://api.iterable.com/api/docs#users_getUser)
* Update Method: UPSERT
* Table Name: \[`iterable.user_unsubscribed_message_types`]

| JSON Element                                                             | Database Column   |
| ------------------------------------------------------------------------ | ----------------- |
| user::userId                                                             | user\_id          |
| unsubscribedMessageTypeIds::id                                           | message\_type\_id |
| Daasity: source\_id                                                      | \_\_source\_id    |
| Daasity: account\_id                                                     | \_account\_id     |
| Daasity: MD5(source\_id + user::userId + unsubscribedMessageTypeIds::id) | \_\_sync\_key     |
| Daasity: timestamp when loaded into DB                                   | \_\_synced\_at    |

### **Email Sends**

* Endpoint: [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* Update Method: UPSERT
* Table Name: \[`iterable.email_sends`]

| JSON Element                           | Database Column   |
| -------------------------------------- | ----------------- |
| messageId                              | message\_id       |
| email                                  | email             |
| messageBusId                           | message\_bus\_id  |
| templateId                             | template\_id      |
| campaignId                             | campaign\_id      |
| channelId                              | channel\_id       |
| messageTypeId                          | message\_type\_id |
| contentId                              | content\_id       |
| createdAt                              | created\_at       |
| Daasity: source\_id                    | \_\_source\_id    |
| Daasity: account\_id                   | \_account\_id     |
| Daasity: MD5(source\_id + messageId)   | \_\_sync\_key     |
| Daasity: timestamp when loaded into DB | \_\_synced\_at    |

### **Email Bounces**

* Endpoint: [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* Update Method: UPSERT
* Table Name: \[`iterable.email_bounces`]

| JSON Element                                     | Database Column  |
| ------------------------------------------------ | ---------------- |
| messageId                                        | message\_id      |
| email                                            | email            |
| templateId                                       | template\_id     |
| campaignId                                       | campaign\_id     |
| recipientState                                   | recipient\_state |
| status                                           | bounce\_status   |
| createdAt                                        | created\_at      |
| Daasity: source\_id                              | \_\_source\_id   |
| Daasity: account\_id                             | \_account\_id    |
| Daasity: MD5(source\_id + messageId + createdAt) | \_\_sync\_key    |
| Daasity: timestamp when loaded into DB           | \_\_synced\_at   |

### **Email Complaints**

* Endpoint: [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* Update Method: UPSERT
* Table Name: \[`iterable.email_complaints`]

| JSON Element                                     | Database Column  |
| ------------------------------------------------ | ---------------- |
| messageId                                        | message\_id      |
| email                                            | email            |
| templateId                                       | template\_id     |
| campaignId                                       | campaign\_id     |
| recipientState                                   | recipient\_state |
| createdAt                                        | created\_at      |
| Daasity: source\_id                              | \_\_source\_id   |
| Daasity: account\_id                             | \_account\_id    |
| Daasity: MD5(source\_id + messageId + createdAt) | \_\_sync\_key    |
| Daasity: timestamp when loaded into DB           | \_\_synced\_at   |

### **Email Opens**

* Endpoint: [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* Update Method: UPSERT
* Table Name: \[`iterable.email_opens`]

| JSON Element                                     | Database Column     |
| ------------------------------------------------ | ------------------- |
| messageId                                        | message\_id         |
| email                                            | email               |
| templateId                                       | template\_id        |
| campaignId                                       | campaign\_id        |
| ip                                               | ip\_address         |
| userAgent                                        | user\_agent         |
| userAgentDevice                                  | user\_agent\_device |
| createdAt                                        | created\_at         |
| Daasity: source\_id                              | \_\_source\_id      |
| Daasity: account\_id                             | \_account\_id       |
| Daasity: MD5(source\_id + messageId + createdAt) | \_\_sync\_key       |
| Daasity: timestamp when loaded into DB           | \_\_synced\_at      |

### **Email Clicks**

* Endpoint: [Export Data to JSON](https://api.iterable.com/api/docs#export_exportDataJson)
* Update Method: UPSERT
* Table Name: \[`iterable.email_clicks`]

| JSON Element                                                 | Database Column     |
| ------------------------------------------------------------ | ------------------- |
| messageId                                                    | message\_id         |
| email                                                        | email               |
| templateId                                                   | template\_id        |
| campaignId                                                   | campaign\_id        |
| userAgent                                                    | user\_agent         |
| userAgentDevice                                              | user\_agent\_device |
| hrefIndex                                                    | href\_index         |
| url, limit: 4095                                             | url                 |
| createdAt                                                    | created\_at         |
| Daasity: source\_id                                          | \_\_source\_id      |
| Daasity: account\_id                                         | \_account\_id       |
| Daasity: MD5(source\_id + messageId + hrefIndex + createdAt) | \_\_sync\_key       |
| Daasity: timestamp when loaded into DB                       | \_\_synced\_at      |