Data Destination Schema

Space Monkey securely extracts Mailchimp data and provisions structured BigQuery tables automatically. Below is the exact schema definition for each destination table.

Connecting an AI assistant? See the MCP Server integration.

Enterprise API Schema URL:https://spacemonkey.igods.com/_api/public/v1/enterprise/schema

Implementation Notes

Auto-populated timestamps: The _synced_at field is automatically appended and populated by Space Monkey during every successful sync batch, representing the exact extraction time.
Complex JSON structures: To ensure maximum schema flexibility and prevent breaking Data Studio (formerly Looker Studio) connectors, nested Mailchimp objects (e.g., settings, tracking, stats) are stringified and stored as the STRING type. These can be parsed natively in BigQuery using JSON_EXTRACT or JSON_VALUE.
Idempotency: All tables employ a MERGE (upsert) strategy based on their primary keys. Safe re-runs will update existing records without duplication.

campaigns#

Mailchimp email campaigns and their core settings.

Field Name	Type	Mode
campaign_id	STRING	REQUIRED
web_id	INTEGER	NULLABLE
type	STRING	NULLABLE
create_time	TIMESTAMP	NULLABLE
archive_url	STRING	NULLABLE
long_archive_url	STRING	NULLABLE
status	STRING	NULLABLE
emails_sent	INTEGER	NULLABLE
send_time	TIMESTAMP	NULLABLE
content_type	STRING	NULLABLE
settings	STRING	NULLABLE
tracking	STRING	NULLABLE
report_summary	STRING	NULLABLE
variate_settings	STRING	NULLABLE
_synced_at	TIMESTAMP	REQUIRED

lists#

Mailchimp audiences (lists) and high-level list stats.

Field Name	Type	Mode
list_id	STRING	REQUIRED
web_id	INTEGER	NULLABLE
name	STRING	NULLABLE
contact	STRING	NULLABLE
campaign_defaults	STRING	NULLABLE
date_created	TIMESTAMP	NULLABLE
stats	STRING	NULLABLE
_synced_at	TIMESTAMP	REQUIRED

members#

Subscribers (members) within your audiences, including merge fields, status, and member stats.

Field Name	Type	Mode
member_id	STRING	REQUIRED
list_id	STRING	NULLABLE
email_address	STRING	NULLABLE
unique_email_id	STRING	NULLABLE
web_id	INTEGER	NULLABLE
email_type	STRING	NULLABLE
status	STRING	NULLABLE
merge_fields	STRING	NULLABLE
stats	STRING	NULLABLE
ip_signup	STRING	NULLABLE
timestamp_signup	TIMESTAMP	NULLABLE
ip_opt	STRING	NULLABLE
timestamp_opt	TIMESTAMP	NULLABLE
member_rating	INTEGER	NULLABLE
last_changed	TIMESTAMP	NULLABLE
language	STRING	NULLABLE
vip	BOOLEAN	NULLABLE
location	STRING	NULLABLE
tags_count	INTEGER	NULLABLE
tags	STRING	NULLABLE
_synced_at	TIMESTAMP	REQUIRED

campaign_reports#

Aggregated performance metrics (opens, clicks, bounces) for each sent campaign.

Field Name	Type	Mode
campaign_id	STRING	REQUIRED
type	STRING	NULLABLE
list_id	STRING	NULLABLE
list_name	STRING	NULLABLE
subject_line	STRING	NULLABLE
preview_text	STRING	NULLABLE
emails_sent	INTEGER	NULLABLE
abuse_reports	INTEGER	NULLABLE
unsubscribed	INTEGER	NULLABLE
send_time	TIMESTAMP	NULLABLE
bounces	STRING	NULLABLE
forwards	STRING	NULLABLE
opens	STRING	NULLABLE
clicks	STRING	NULLABLE
_synced_at	TIMESTAMP	REQUIRED

automations#

Classic automations and customer journey workflows.

Field Name	Type	Mode
automation_id	STRING	REQUIRED
create_time	TIMESTAMP	NULLABLE
start_time	TIMESTAMP	NULLABLE
status	STRING	NULLABLE
emails_sent	INTEGER	NULLABLE
recipients	STRING	NULLABLE
settings	STRING	NULLABLE
tracking	STRING	NULLABLE
trigger_settings	STRING	NULLABLE
report_summary	STRING	NULLABLE
_synced_at	TIMESTAMP	REQUIRED

segments#

Saved segments within your audiences.

Field Name	Type	Mode
segment_id	STRING	REQUIRED
list_id	STRING	NULLABLE
name	STRING	NULLABLE
member_count	INTEGER	NULLABLE
type	STRING	NULLABLE
created_at	TIMESTAMP	NULLABLE
updated_at	TIMESTAMP	NULLABLE
options	STRING	NULLABLE
_synced_at	TIMESTAMP	REQUIRED

tags#

Tags created and applied to audience members.

Field Name	Type	Mode
tag_id	STRING	REQUIRED
list_id	STRING	NULLABLE
name	STRING	NULLABLE
_synced_at	TIMESTAMP	REQUIRED

member_activities#

Data extracted from Mailchimp.

Field Name	Type	Mode
activity_id	STRING	REQUIRED
member_id	STRING	NULLABLE
list_id	STRING	NULLABLE
email_address	STRING	NULLABLE
activity_type	STRING	NULLABLE
campaign_id	STRING	NULLABLE
title	STRING	NULLABLE
url	STRING	NULLABLE
timestamp	TIMESTAMP	NULLABLE
_synced_at	TIMESTAMP	REQUIRED

email_activities#

Data extracted from Mailchimp.

Field Name	Type	Mode
activity_id	STRING	REQUIRED
campaign_id	STRING	NULLABLE
list_id	STRING	NULLABLE
email_id	STRING	NULLABLE
email_address	STRING	NULLABLE
action	STRING	NULLABLE
timestamp	TIMESTAMP	NULLABLE
url	STRING	NULLABLE
_synced_at	TIMESTAMP	REQUIRED

BigQuery Views

In addition to base tables, Space Monkey auto-provisions analytical views directly into your dataset. These views aggregate and transform the base tables into ready-to-use insights and are recreated automatically on each sync. Access to views is determined by your subscription tier.

Basic Tier Views

Core performance and summary metrics.

v_audience_summary#

High-level aggregate count of members grouped by their status.

Column Name	Type	Description
status	STRING	Subscriber status (subscribed, unsubscribed, etc.)
member_count	INT64	Number of members with this status

v_list_overview#

Performance and health overview of all lists/audiences.

Column Name	Type	Description
list_id	STRING	List ID
name	STRING	List name
member_count	INT64	Total subscribed members
unsubscribe_count	INT64	Total unsubscribed members
cleaned_count	INT64	Total cleaned members
open_rate	FLOAT64	List average open rate
click_rate	FLOAT64	List average click rate

v_campaign_performance#

Detailed performance metrics for each campaign with >= 50 sends.

Column Name	Type	Description
campaign_id	STRING	Campaign ID
subject_line	STRING	Subject line
send_time	TIMESTAMP	Send time
emails_sent	INT64	Number of emails sent
unsubscribed	INT64	Unsubscribe count
total_bounces	INT64	Hard + soft bounces
opens_total	INT64	Total opens
unique_opens	INT64	Unique opens (proxy excluded)
clicks_total	INT64	Total clicks
unique_clicks	INT64	Unique clicks
deliveries	INT64	Successful deliveries
bounce_rate	FLOAT64	Bounce rate
delivery_rate	FLOAT64	Delivery rate
unsubscribe_rate	FLOAT64	Unsubscribe rate
open_rate	FLOAT64	Open rate
click_rate	FLOAT64	Click rate
ctor	FLOAT64	Click-to-open rate

v_campaign_averages#

Global averages across all campaigns.

Column Name	Type	Description
total_campaigns	INT64	Total campaigns sent
avg_emails_sent	FLOAT64	Average emails sent
avg_open_rate	FLOAT64	Average open rate
avg_click_rate	FLOAT64	Average click rate
avg_bounce_rate	FLOAT64	Average bounce rate
avg_delivery_rate	FLOAT64	Average delivery rate
avg_ctor	FLOAT64	Average click-to-open rate
total_emails_sent	INT64	Total emails sent overall
total_opens	INT64	Total opens overall
total_clicks	INT64	Total clicks overall

Advanced Tier Views

Detailed behavioral and segmentation trends.

v_subscriber_growth#

Daily breakdown of new subscriber opt-ins by status.

Column Name	Type	Description
opt_date	DATE	Date of last change
status	STRING	Subscriber status
new_members	INT64	Number of new members on this date

v_engagement_distribution#

Distribution of members across engagement ratings.

Column Name	Type	Description
member_rating	INT64	Member rating (1-5)
member_count	INT64	Count of members with this rating
last_synced_at	TIMESTAMP	Latest sync time for this rating group

v_top_campaigns#

Top performing campaigns highlighting key engagement metrics.

Column Name	Type	Description
campaign_id	STRING	Campaign ID
subject_line	STRING	Subject line
emails_sent	INT64	Number of emails sent
send_time	TIMESTAMP	Send time
unique_opens	INT64	Unique opens
unique_clicks	INT64	Unique clicks
deliveries	INT64	Deliveries
open_rate	FLOAT64	Open rate
click_rate	FLOAT64	Click rate
ctor	FLOAT64	Click-to-open rate

v_tag_summary#

Aggregated count of members associated with each tag.

Column Name	Type	Description
tag_id	STRING	Tag ID
name	STRING	Tag name
list_id	STRING	List ID
member_count	INT64	Number of members with this tag
_synced_at	TIMESTAMP	Sync time

v_segment_summary#

Overview of all saved segments.

Column Name	Type	Description
segment_id	STRING	Segment ID
list_id	STRING	List ID
name	STRING	Segment name
member_count	INT64	Member count
type	STRING	Segment type
created_at	TIMESTAMP	Creation time
updated_at	TIMESTAMP	Update time
options	STRING	Segment options JSON
_synced_at	TIMESTAMP	Sync time

Expert Tier Views

In-depth analytics including fatigue analysis, timing optimization, and sentiment.

v_campaign_fatigue#

Quarterly trends in campaign sends and engagement to monitor list fatigue.

Column Name	Type	Description
quarter	STRING	Quarter (YYYY-QN)
campaigns_sent	INT64	Campaigns sent in quarter
avg_open_rate	FLOAT64	Average open rate
avg_click_rate	FLOAT64	Average click rate
avg_ctor	FLOAT64	Average click-to-open rate

v_subject_line_keywords#

Analysis of keywords used in subject lines and their impact on engagement.

Column Name	Type	Description
keyword	STRING	Keyword from subject line
campaign_count	INT64	Campaigns using this keyword
avg_open_rate	FLOAT64	Average open rate
avg_click_rate	FLOAT64	Average click rate

v_send_time_heatmap#

Aggregated performance grouped by day of week and hour of day.

Column Name	Type	Description
day_of_week	INT64	Day of week (1=Sunday, 7=Saturday)
hour_of_day	INT64	Hour of day (0-23)
campaign_count	INT64	Number of campaigns sent
avg_open_rate	FLOAT64	Average open rate
avg_click_rate	FLOAT64	Average click rate

v_monthly_performance_trend#

Quarterly performance trend across campaigns.

Column Name	Type	Description
year_quarter	STRING	Quarter (YYYY-QN)
campaign_count	INT64	Number of campaigns sent
avg_open_rate	FLOAT64	Average open rate
avg_click_rate	FLOAT64	Average click rate
avg_ctor	FLOAT64	Average click-to-open rate

v_audience_health#

Overall audience health metrics including engagement distributions.

Column Name	Type	Description
total_members	INT64	Total members
subscribed_count	INT64	Subscribed members
unsubscribed_count	INT64	Unsubscribed members
cleaned_count	INT64	Cleaned members
high_engagement_count	INT64	Members with rating >= 4
low_engagement_count	INT64	Members with rating <= 2
avg_member_rating	FLOAT64	Average member rating