What This Integration Does
This integration pulls campaign metadata, audience lists, user profiles, and email engagement activity from your Iterable account into the TwoCircles data warehouse (Redshift). It refreshes daily, giving you a consistent, queryable record of who received what campaigns, who opened them, and who clicked — enabling unified customer reporting and audience persona development.
Data Overview
Source: Iterable REST API
Destination: Redshift — schema: `iterable`
Refresh: Daily (can be increased to every 5 minutes on request)
Historical coverage: 2 years of history loaded on initial setup; ongoing from there
Environment: Sandbox during testing; Production after sign-off
Tables available
| Table | What it contains | Grain |
| iterable.campaigns | Campaign metadata including campaign name, send size, audience lists, labels, and campaign dates | One row per campaign |
| iterable.lists | Audience list metadata and list-level details | One row per list |
| iterable.user | User/contact profiles and attributes, keyed by email address | One row per user |
| iterable.emailsend | Records of every email sent, including recipient and associated campaign | One row per send event |
| iterable.emailopen | Records of every email open event | One row per open event |
| iterable.emailclick | Records of every link click within an email | One row per click event |
Note: Not included in Phase 1: SMS, push notifications, in-app events, real-time streaming data.
Use Cases
Use Case 1:
Campaign performance — who received what and how did they respond?
What it answers: For a given campaign, how many users received it, how many opened it, and how many clicked?
Where to look: Join `emailsend`, `emailopen`, and `emailclick` on `campaign_id` and `email`
sql
SELECT
s.campaignid,
COUNT(DISTINCT s.email) AS sends,
COUNT(DISTINCT o.email) AS opens,
COUNT(DISTINCT k.email) AS clicks,
ROUND(COUNT(DISTINCT o.email) * 100.0 / NULLIF(COUNT(DISTINCT s.email), 0), 2) AS open_rate_pct,
ROUND(COUNT(DISTINCT k.email) * 100.0 / NULLIF(COUNT(DISTINCT s.email), 0), 2) AS click_rate_pct
FROM iterable_marketing.emailsend s
LEFT JOIN iterable_marketing.emailopen o ON s.email = o.email AND s.campaignid = o.campaignid
LEFT JOIN iterable_marketing.emailclick k ON s.email = k.email AND s.campaignid = k.campaignid
WHERE s.createdat >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY 1
ORDER BY sends DESC;
Use Case 2:
User engagement history — what has a specific customer done?
What it answers: For a given email address, what campaigns have they received, opened, and clicked?
Where to look: Query `emailsend`, `emailopen`, `emailclick` filtered by email; join `campaigns` for names.
sql
SELECT
s.createdat AS sent_at,
s.campaignid,
CASE WHEN o.email IS NOT NULL THEN 'Yes' ELSE 'No' END AS opened,
CASE WHEN k.email IS NOT NULL THEN 'Yes' ELSE 'No' END AS clicked
FROM iterable_marketing.emailsend s
LEFT JOIN iterable_marketing.emailopen o ON s.email = o.email AND s.campaignid = o.campaignid
LEFT JOIN iterable_marketing.emailclick k ON s.email = k.email AND s.campaignid = k.campaignid
WHERE s.email = 'customer@example.com'
ORDER BY s.createdat DESC;Use Case 3:
Audience segmentation — identify highly engaged users
What it answers: Which users have opened or clicked campaigns in the last 90 days? Use as a basis for persona development or targeted segments.
Where to look: Combine `emailopen` / `emailclick` with `user` profiles
SELECT
o.email,
COUNT(DISTINCT o.campaignid) AS campaigns_opened,
COUNT(DISTINCT k.campaignid) AS campaigns_clicked,
MAX(o.createdat) AS last_open_date
FROM iterable_marketing.emailopen o
LEFT JOIN iterable_marketing.emailclick k ON o.email = k.email
WHERE o.createdat >= DATEADD(day, -90, CURRENT_DATE)
GROUP BY 1
ORDER BY campaigns_clicked DESC, campaigns_opened DESC;How to Access the Data
For analysts (Redshift):
Connect to your TwoCircles Redshift instance and query the `iterable` schema. Contact your TwoCircles account manager if you need credentials or connection details.
Recommended connection settings:
- Schema: `iterable`
- Warehouse/role: as provisioned by TwoCircles
For non-technical users:
Data from these tables is available via your TwoCircles-managed dashboard. If you don't have dashboard access set up yet, contact your TwoCircles account manager.
Validating the Data
On first access, run the following to confirm the integration loaded correctly:
```sql
-- Check row counts and date coverage for all tables
SELECT 'campaigns' AS tbl, COUNT(*) AS rows, MIN(created_at) AS earliest, MAX(created_at) AS latest FROM iterable.campaigns
UNION ALL
SELECT 'lists', COUNT(*), MIN(created_at), MAX(created_at) FROM iterable.lists
UNION ALL
SELECT 'user', COUNT(*), MIN(profile_updated_at), MAX(profile_updated_at) FROM iterable.user
UNION ALL
SELECT 'emailsend', COUNT(*), MIN(created_at), MAX(created_at) FROM iterable.emailsend
UNION ALL
SELECT 'emailopen', COUNT(*), MIN(created_at), MAX(created_at) FROM iterable.emailopen
UNION ALL
SELECT 'emailclick', COUNT(*), MIN(created_at), MAX(created_at) FROM iterable.emailclick;
```Expected outcome of the above query:
Rows present in all tables, dates spanning approximately 2 years of history through yesterday, all date fields in readable timestamp format (not large integers).
If row counts look wrong or dates are missing, contact your TwoCircles account manager.
Questions or Issues?
Contact your TwoCircles account manager or reach out to the data team directly.
Note: During the testing phase, data reflects the Sandbox environment. Production data will be available after go-live confirmation.