Mailchimp connector

Set up the Mailchimp connector in Kaivo: authentication, configuration, the 12 BigQuery tables it syncs, and answers to common questions.

Written By Lauri Raivio

Last updated 16 days ago

Kaivo is a fully managed data platform that syncs your Mailchimp data into a Google BigQuery warehouse and keeps it up to date automatically. There is no pipeline to build and no infrastructure to run, so you can spend your time analysing your survey and email data instead of moving it.

What is the Mailchimp connector

Sync your Mailchimp campaigns, lists, and email activity into BigQuery with Kaivo to analyse email marketing alongside the rest of your data.

Category	Surveys & Email, Marketing & Sales
Authentication	API key
Setup	Self-service

Getting started with the Mailchimp connector

Sign up for Kaivo and create a workspace.
Connect your Mailchimp account.
Choose which tables to sync.
Wait for the initial sync to finish.
Query your data in BigQuery or your favourite AI or BI tool.

Authenticating Mailchimp

Authenticate with your API Key.

Field	Description
API Key	Mailchimp API Key. See the Mailchimp docs for information on how to generate this key.

Configuring the Mailchimp connector

When you set up the connector, you provide:

Field	Description
Start Date	Any data before this date will not be fetched.

Tables and columns synced from Mailchimp

Kaivo syncs 12 tables from Mailchimp into a dedicated dataset in your BigQuery warehouse. Click any table to see its columns and types.

automations (41 columns)

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`create_time`	`TIMESTAMP`	The timestamp when the automation was created
`emails_sent`	`FLOAT64`	The number of emails sent as part of the automation
`id`	`STRING`	The unique identifier for the automation
`recipients__list_id`	`STRING`	The ID of the recipient list
`recipients__list_is_active`	`BOOL`	Indicates if the recipient list is active
`recipients__list_name`	`STRING`	The name of the recipient list
`recipients__segment_opts__match`	`STRING`	Matching criteria for segmenting recipients
`recipients__segment_opts__saved_segment_id`	`FLOAT64`	The ID of the saved segment
`recipients__store_id`	`STRING`	The ID of the store associated with recipients
`report_summary__click_rate`	`FLOAT64`	The click-through rate for the automation
`report_summary__clicks`	`FLOAT64`	The total number of clicks generated
`report_summary__open_rate`	`FLOAT64`	The open rate for the automation
`report_summary__opens`	`FLOAT64`	The total number of opens generated
`report_summary__subscriber_clicks`	`FLOAT64`	Number of clicks per subscriber
`report_summary__unique_opens`	`FLOAT64`	Number of unique opens recorded
`settings__authenticate`	`BOOL`	Indicates if authentication is set
`settings__auto_footer`	`BOOL`	Automatically add footer to emails
`settings__from_name`	`STRING`	Sender's name
`settings__inline_css`	`BOOL`	Include inline CSS in emails
`settings__reply_to`	`STRING`	Email address for replies
`settings__title`	`STRING`	Title of the automation
`settings__to_name`	`STRING`	Recipient's name field
`settings__use_conversation`	`BOOL`	Enable conversation tracking
`start_time`	`TIMESTAMP`	The timestamp when the automation started
`status`	`STRING`	Current status of the automation
`tracking__capsule__notes`	`BOOL`	Additional notes for capsule tracking
`tracking__clicktale`	`STRING`	Clicktale tracking status
`tracking__ecomm360`	`BOOL`	Ecommerce tracking status
`tracking__goal_tracking`	`BOOL`	Goal tracking setup status
`tracking__google_analytics`	`STRING`	Google Analytics tracking status
`tracking__html_clicks`	`BOOL`	HTML click tracking status
`tracking__opens`	`BOOL`	Open tracking status
`tracking__salesforce__campaign`	`BOOL`	Salesforce campaign tracking status
`tracking__salesforce__notes`	`BOOL`	Additional notes for Salesforce tracking
`tracking__text_clicks`	`BOOL`	Text click tracking status
`trigger_settings__runtime__hours__type`	`STRING`	Type of hourly triggering
`trigger_settings__workflow_emails_count`	`FLOAT64`	Number of emails in the workflow
`trigger_settings__workflow_title`	`STRING`	Title of the workflow
`trigger_settings__workflow_type`	`STRING`	Type of workflow
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: automationsrecipientssegment_opts__conditions

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: automations__trigger_settingsruntimedays

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`value`	`STRING`
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

campaigns (99 columns), A summary of an individual campaign's settings and content.

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`ab_split_opts__from_name_a`	`STRING`	For campaigns split on 'From Name', the name for Group A.
`ab_split_opts__from_name_b`	`STRING`	For campaigns split on 'From Name', the name for Group B.
`ab_split_opts__pick_winner`	`STRING`	How we should evaluate a winner. Based on 'opens', 'clicks', or 'manual'.
`ab_split_opts__reply_email_a`	`STRING`	For campaigns split on 'From Name', the reply-to address for Group A.
`ab_split_opts__reply_email_b`	`STRING`	For campaigns split on 'From Name', the reply-to address for Group B.
`ab_split_opts__send_time_a`	`TIMESTAMP`	The send time for Group A.
`ab_split_opts__send_time_b`	`TIMESTAMP`	The send time for Group B.
`ab_split_opts__send_time_winner`	`STRING`	The send time for the winning version.
`ab_split_opts__split_size`	`INT64`	The size of the split groups. Campaigns split based on 'schedule' are forced to have a 50/50 split. Valid split integers are between 1-50.
`ab_split_opts__split_test`	`STRING`	The type of AB split to run.
`ab_split_opts__subject_a`	`STRING`	For campaigns split on 'Subject Line', the subject line for Group A.
`ab_split_opts__subject_b`	`STRING`	For campaigns split on 'Subject Line', the subject line for Group B.
`ab_split_opts__wait_time`	`INT64`	The amount of time to wait before picking a winner. This cannot be changed after a campaign is sent.
`ab_split_opts__wait_units`	`STRING`	How unit of time for measuring the winner ('hours' or 'days'). This cannot be changed after a campaign is sent.
`archive_url`	`STRING`	The link to the campaign's archive version in ISO 8601 format.
`content_type`	`STRING`	How the campaign's content is put together.
`create_time`	`TIMESTAMP`	The date and time the campaign was created in ISO 8601 format.
`delivery_status__can_cancel`	`BOOL`	Whether a campaign send can be canceled.
`delivery_status__emails_canceled`	`INT64`	The total number of emails canceled for this campaign.
`delivery_status__emails_sent`	`INT64`	The total number of emails confirmed sent for this campaign so far.
`delivery_status__enabled`	`BOOL`	Whether Campaign Delivery Status is enabled for this account and campaign.
`delivery_status__status`	`STRING`	The current state of a campaign delivery.
`emails_sent`	`INT64`	The total number of emails sent for this campaign.
`id`	`STRING`	A string that uniquely identifies this campaign.
`long_archive_url`	`STRING`	The original link to the campaign's archive version.
`needs_block_refresh`	`BOOL`	Determines if the campaign needs its blocks refreshed by opening the web-based campaign editor. Deprecated and will always return false.
`parent_campaign_id`	`STRING`	If this campaign is the child of another campaign, this identifies the parent campaign. For Example, for RSS or Automation children.
`recipients__list_id`	`STRING`	The unique list id.
`recipients__list_is_active`	`BOOL`	The status of the list used, namely if it's deleted or disabled.
`recipients__list_name`	`STRING`	The name of the list.
`recipients__recipient_count`	`INT64`	Count of the recipients on the associated list. Formatted as an integer.
`recipients__segment_opts__match`	`STRING`	Segment match type.
`recipients__segment_opts__prebuilt_segment_id`	`STRING`	The prebuilt segment id, if a prebuilt segment has been designated for this campaign.
`recipients__segment_opts__saved_segment_id`	`INT64`	The id for an existing saved segment.
`recipients__segment_text`	`STRING`	A description of the [segment](https://mailchimp.com/help/create-and-send-to-a-segment/) used for the campaign. Formatted as a string marked up with HTML.
`report_summary__click_rate`	`FLOAT64`	The number of unique clicks divided by the total number of successful deliveries.
`report_summary__clicks`	`INT64`	The total number of clicks for an campaign.
`report_summary__ecommerce__total_orders`	`INT64`	The total orders for a campaign.
`report_summary__ecommerce__total_revenue`	`FLOAT64`	The total revenue for a campaign. Calculated as the sum of all order totals minus shipping and tax totals.
`report_summary__ecommerce__total_spent`	`FLOAT64`	The total spent for a campaign. Calculated as the sum of all order totals with no deductions.
`report_summary__open_rate`	`FLOAT64`	The number of unique opens divided by the total number of successful deliveries.
`report_summary__opens`	`INT64`	The total number of opens for a campaign.
`report_summary__subscriber_clicks`	`INT64`	The number of unique clicks.
`report_summary__unique_opens`	`INT64`	The number of unique opens.
`resendable`	`BOOL`	Determines if the campaign qualifies to be resent to non-openers.
`rss_opts__constrain_rss_img`	`BOOL`	Whether to add CSS to images in the RSS feed to constrain their width in campaigns.
`rss_opts__feed_url`	`STRING`	The URL for the RSS feed.
`rss_opts__frequency`	`STRING`	The frequency of the RSS Campaign.
`rss_opts__last_sent`	`TIMESTAMP`	The date the campaign was last sent.
`rss_opts__schedule__daily_send__friday`	`BOOL`	Sends the daily RSS Campaign on Fridays.
`rss_opts__schedule__daily_send__monday`	`BOOL`	Sends the daily RSS Campaign on Mondays.
`rss_opts__schedule__daily_send__saturday`	`BOOL`	Sends the daily RSS Campaign on Saturdays.
`rss_opts__schedule__daily_send__sunday`	`BOOL`	Sends the daily RSS Campaign on Sundays.
`rss_opts__schedule__daily_send__thursday`	`BOOL`	Sends the daily RSS Campaign on Thursdays.
`rss_opts__schedule__daily_send__tuesday`	`BOOL`	Sends the daily RSS Campaign on Tuesdays.
`rss_opts__schedule__daily_send__wednesday`	`BOOL`	Sends the daily RSS Campaign on Wednesdays.
`rss_opts__schedule__hour`	`INT64`	The hour to send the campaign in local time. Acceptable hours are 0-23. For example, '4' would be 4am in [your account's default time zone](https://mailchimp.com/help/set-account-defaults/).
`rss_opts__schedule__monthly_send_date`	`FLOAT64`	The day of the month to send a monthly RSS Campaign. Acceptable days are 0-31, where '0' is always the last day of a month. Months with fewer than the selected number of days will not have an RSS campaign sent out that day. For example, RSS Campaigns set to send on the 30th will not go out in February.
`rss_opts__schedule__weekly_send_day`	`STRING`	The day of the week to send a weekly RSS Campaign.
`send_time`	`TIMESTAMP`	The date and time a campaign was sent.
`settings__authenticate`	`BOOL`	Whether Mailchimp [authenticated](https://mailchimp.com/help/about-email-authentication/) the campaign. Defaults to `true`.
`settings__auto_footer`	`BOOL`	Automatically append Mailchimp's [default footer](https://mailchimp.com/help/about-campaign-footers/) to the campaign.
`settings__auto_tweet`	`BOOL`	Automatically tweet a link to the [campaign archive](https://mailchimp.com/help/about-email-campaign-archives-and-pages/) page when the campaign is sent.
`settings__drag_and_drop`	`BOOL`	Whether the campaign uses the drag-and-drop editor.
`settings__fb_comments`	`BOOL`	Allows Facebook comments on the campaign (also force-enables the Campaign Archive toolbar). Defaults to `true`.
`settings__folder_id`	`STRING`	If the campaign is listed in a folder, the id for that folder.
`settings__from_name`	`STRING`	The 'from' name on the campaign (not an email address).
`settings__inline_css`	`BOOL`	Automatically inline the CSS included with the campaign content.
`settings__preview_text`	`STRING`	The preview text for the campaign.
`settings__reply_to`	`STRING`	The reply-to email address for the campaign.
`settings__subject_line`	`STRING`	The subject line for the campaign.
`settings__template_id`	`INT64`	The id for the template used in this campaign.
`settings__timewarp`	`BOOL`	Send this campaign using [Timewarp](https://mailchimp.com/help/use-timewarp/).
`settings__title`	`STRING`	The title of the campaign.
`settings__to_name`	`STRING`	The campaign's custom 'To' name. Typically the first name [merge field](https://mailchimp.com/help/getting-started-with-merge-tags/).
`settings__use_conversation`	`BOOL`	Use Mailchimp Conversation feature to manage out-of-office replies.
`social_card__description`	`STRING`	A short summary of the campaign to display.
`social_card__image_url`	`STRING`	The url for the header image for the card.
`social_card__title`	`STRING`	The title for the card. Typically the subject line of the campaign.
`status`	`STRING`	The current status of the campaign.
`tracking__capsule__notes`	`BOOL`	Update contact notes for a campaign based on subscriber email addresses.
`tracking__clicktale`	`STRING`	The custom slug for [ClickTale](https://mailchimp.com/help/additional-tracking-options-for-campaigns/) tracking (max of 50 bytes).
`tracking__ecomm360`	`BOOL`	Whether to enable [eCommerce360](https://mailchimp.com/help/connect-your-online-store-to-mailchimp/) tracking.
`tracking__goal_tracking`	`BOOL`	Whether to enable [Goal](https://mailchimp.com/help/about-connected-sites/) tracking.
`tracking__google_analytics`	`STRING`	The custom slug for [Google Analytics](https://mailchimp.com/help/integrate-google-analytics-with-mailchimp/) tracking (max of 50 bytes).
`tracking__html_clicks`	`BOOL`	Whether to [track clicks](https://mailchimp.com/help/enable-and-view-click-tracking/) in the HTML version of the campaign. Defaults to `true`. Cannot be set to false for variate campaigns.
`tracking__opens`	`BOOL`	Whether to [track opens](https://mailchimp.com/help/about-open-tracking/). Defaults to `true`. Cannot be set to false for variate campaigns.
`tracking__salesforce__campaign`	`BOOL`	Create a campaign in a connected Salesforce account.
`tracking__salesforce__notes`	`BOOL`	Update contact notes for a campaign based on subscriber email addresses.
`tracking__text_clicks`	`BOOL`	Whether to [track clicks](https://mailchimp.com/help/enable-and-view-click-tracking/) in the plain-text version of the campaign. Defaults to `true`. Cannot be set to false for variate campaigns.
`type`	`STRING`	There are four types of [campaigns](https://mailchimp.com/help/getting-started-with-campaigns/) you can create in Mailchimp. A/B Split campaigns have been deprecated and variate campaigns should be used instead.
`variate_settings__test_size`	`INT64`	The percentage of recipients to send the test combinations to, must be a value between 10 and 100.
`variate_settings__wait_time`	`INT64`	The number of minutes to wait before choosing the winning campaign. The value of wait_time must be greater than 0 and in whole hours, specified in minutes.
`variate_settings__winner_criteria`	`STRING`	The combination that performs the best. This may be determined automatically by click rate, open rate, or total revenue -- or you may choose manually based on the reporting data you find the most valuable. For Multivariate Campaigns testing send_time, winner_criteria is ignored. For Multivariate Campaigns with 'manual' as the winner_criteria, the winner must be chosen in the Mailchimp web application.
`variate_settings__winning_campaign_id`	`STRING`	ID of the campaign that was sent to the remaining recipients based on the winning combination.
`variate_settings__winning_combination_id`	`STRING`	ID for the winning combination.
`web_id`	`INT64`	The ID used in the Mailchimp web application. View this campaign in your Mailchimp account at `https://{dc}.admin.mailchimp.com/campaigns/show/?id={web_id}`.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: campaignsrecipientssegment_opts__conditions

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`value`	`JSON`
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: campaignssettingsauto_fb_post

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`value`	`STRING`
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: campaigns__variate_settings__combinations

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`content_description`	`INT64`	The index of `variate_settings.contents` used.
`from_name`	`INT64`	The index of `variate_settings.from_names` used.
`id`	`STRING`	Unique ID for the combination.
`recipients`	`INT64`	The number of recipients for this combination.
`reply_to`	`INT64`	The index of `variate_settings.reply_to_addresses` used.
`send_time`	`INT64`	The index of `variate_settings.send_times` used.
`subject_line`	`INT64`	The index of `variate_settings.subject_lines` used.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: campaigns__variate_settings__contents

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`value`	`STRING`
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: campaigns__variate_settings__from_names

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`value`	`STRING`
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: campaigns__variate_settings__reply_to_addresses

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`value`	`STRING`
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: campaigns__variate_settings__send_times

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`value`	`TIMESTAMP`
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: campaigns__variate_settings__subject_lines

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`value`	`STRING`
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

email_activity (12 columns), A list of member's subscriber activity in a specific campaign.

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`campaign_id`	`STRING`	The unique id for the campaign.
`list_id`	`STRING`	The unique id for the list.
`list_is_active`	`BOOL`	The status of the list used, namely if it's deleted or disabled.
`email_id`	`STRING`	The MD5 hash of the lowercase version of the list member's email address.
`email_address`	`STRING`	Email address for a subscriber.
`action`	`STRING`	One of the following actions: 'open', 'click', or 'bounce'
`type`	`STRING`	If the action is a 'bounce', the type of bounce received: 'hard', 'soft'.
`timestamp`	`TIMESTAMP`	The date and time recorded for the action in ISO 8601 format.
`url`	`STRING`	If the action is a 'click', the URL on which the member clicked.
`ip`	`STRING`	The IP address recorded for the action.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

interest_categories (7 columns)

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`list_id`	`STRING`	The ID of the list to which this interest category belongs.
`id`	`STRING`	The unique identifier for the interest category.
`title`	`STRING`	The title or name of the interest category.
`display_order`	`INT64`	The order in which this interest category should be displayed in the UI.
`type`	`STRING`	The type of interest category, e.g., 'checkboxes', 'hidden', 'dropdown'.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

interests (8 columns)

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`category_id`	`STRING`	Unique identifier for the category to which this interest belongs.
`list_id`	`STRING`	Unique identifier for the list associated with this interest.
`id`	`STRING`	Unique identifier for this specific interest.
`name`	`STRING`	Name or label of the interest.
`subscriber_count`	`STRING`	Number of subscribers who have selected this interest.
`display_order`	`INT64`	Numeric value representing the display order of this interest within its category.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

list_members (43 columns)

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`consents_to_one_to_one_messaging`	`BOOL`	Indicates if the member has consented to receive one-to-one messaging
`contact_id`	`STRING`	The unique identifier for the contact associated with the member
`email_address`	`STRING`	The email address of the member
`email_client`	`STRING`	The email client used by the member
`email_type`	`STRING`	The type of email address (e.g., html, text)
`full_name`	`STRING`	The full name of the member
`id`	`STRING`	The unique identifier for the member
`ip_opt`	`STRING`	The IP address where the member opted in
`ip_signup`	`STRING`	The IP address where the member signed up
`language`	`STRING`	The preferred language of the member
`last_changed`	`TIMESTAMP`	The date and time when the member was last changed
`last_note__created_at`	`TIMESTAMP`	The timestamp when the note was created
`last_note__created_by`	`STRING`	The user who created the note
`last_note__note`	`STRING`	The content of the note
`last_note__note_id`	`INT64`	The unique identifier for the note
`list_id`	`STRING`	The unique identifier for the list to which the member belongs
`location__country_code`	`STRING`	The two-letter country code of the member's location
`location__dstoff`	`INT64`	Daylight saving time offset in seconds
`location__gmtoff`	`INT64`	GMT offset in seconds
`location__latitude`	`FLOAT64`	The latitude of the member's location
`location__longitude`	`FLOAT64`	The longitude of the member's location
`location__region`	`STRING`	The region or area of the member's location
`location__timezone`	`STRING`	The timezone of the member's location
`marketing_permissions__enabled`	`BOOL`	Indicates if marketing permissions are enabled
`marketing_permissions__marketing_permission_id`	`STRING`	The unique identifier for the marketing permission
`marketing_permissions__text`	`STRING`	The text of the marketing permission
`member_rating`	`INT64`	The rating score assigned to the member
`source`	`STRING`	The source from which the member was added
`stats__avg_click_rate`	`FLOAT64`	Average click rate of the member
`stats__avg_open_rate`	`FLOAT64`	Average open rate of the member
`stats__ecommerce_data__currency_code`	`STRING`	The currency code used for transactions
`stats__ecommerce_data__number_of_orders`	`FLOAT64`	Total number of orders placed by the member
`stats__ecommerce_data__total_revenue`	`FLOAT64`	Total revenue generated by the member
`status`	`STRING`	The subscription status of the member
`tags_count`	`INT64`	The count of tags associated with the member
`timestamp_opt`	`TIMESTAMP`	The timestamp when the member opted in
`timestamp_signup`	`TIMESTAMP`	The timestamp when the member signed up
`unique_email_id`	`STRING`	The unique email identifier for the member
`unsubscribe_reason`	`STRING`	Reason provided by the member for unsubscribing
`vip`	`BOOL`	Indicates if the member is a VIP
`web_id`	`INT64`	The unique identifier for the member on the web
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: list_members__tags

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`id`	`INT64`	The unique identifier for the tag
`name`	`STRING`	The name of the tag
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

lists (48 columns), Information about a specific list.

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`beamer_address`	`STRING`	The list's Email Beamer address.
`campaign_defaults__from_email`	`STRING`	The default from email for campaigns sent to this list.
`campaign_defaults__from_name`	`STRING`	The default from name for campaigns sent to this list.
`campaign_defaults__language`	`STRING`	The default language for this list's forms.
`campaign_defaults__subject`	`STRING`	The default subject line for campaigns sent to this list.
`contact__address1`	`STRING`	The street address for the list contact.
`contact__address2`	`STRING`	The street address line 2 for the list contact.
`contact__city`	`STRING`	The city for the list contact.
`contact__company`	`STRING`	The company name for the list.
`contact__country`	`STRING`	A two-character ISO3166 country code. Defaults to US if invalid.
`contact__phone`	`STRING`	The phone number for the list contact.
`contact__state`	`STRING`	The state for the list contact.
`contact__zip`	`STRING`	The postal or zip code for the list contact.
`date_created`	`TIMESTAMP`	The date and time that this list was created in ISO 8601 format.
`double_optin`	`BOOL`	Whether or not to require the subscriber to confirm subscription via email.
`email_type_option`	`BOOL`	Whether the list supports multiple formats for emails. When set to `true`, subscribers can choose whether they want to receive HTML or plain-text emails. When set to `false`, subscribers will receive HTML emails, with a plain-text alternative backup.
`has_welcome`	`BOOL`	Whether or not this list has a welcome automation connected.
`id`	`STRING`	A string that uniquely identifies this list.
`list_rating`	`INT64`	An auto-generated activity score for the list (0-5).
`marketing_permissions`	`BOOL`	Whether or not the list has marketing permissions (eg. GDPR) enabled.
`name`	`STRING`	The name of the list.
`notify_on_subscribe`	`STRING`	The email address to send subscribe notifications to.
`notify_on_unsubscribe`	`STRING`	The email address to send unsubscribe notifications to.
`permission_reminder`	`STRING`	The permission reminder for the list.
`stats__avg_sub_rate`	`FLOAT64`	The average number of subscriptions per month for the list (not returned if we haven't calculated it yet).
`stats__avg_unsub_rate`	`FLOAT64`	The average number of unsubscriptions per month for the list (not returned if we haven't calculated it yet).
`stats__campaign_count`	`INT64`	The number of campaigns in any status that use this list.
`stats__campaign_last_sent`	`TIMESTAMP`	The date and time the last campaign was sent to this list in ISO 8601 format. This is updated when a campaign is sent to 10 or more recipients.
`stats__cleaned_count`	`INT64`	The number of members cleaned from the list.
`stats__cleaned_count_since_send`	`INT64`	The number of members cleaned from the list since the last campaign was sent.
`stats__click_rate`	`FLOAT64`	The average click rate (a percentage represented as a number between 0 and 100) per campaign for the list (not returned if we haven't calculated it yet).
`stats__last_sub_date`	`TIMESTAMP`	The date and time of the last time someone subscribed to this list in ISO 8601 format.
`stats__last_unsub_date`	`TIMESTAMP`	The date and time of the last time someone unsubscribed from this list in ISO 8601 format.
`stats__member_count`	`INT64`	The number of active members in the list.
`stats__member_count_since_send`	`INT64`	The number of active members in the list since the last campaign was sent.
`stats__merge_field_count`	`INT64`	The number of merge vars for this list (not EMAIL, which is required).
`stats__open_rate`	`FLOAT64`	The average open rate (a percentage represented as a number between 0 and 100) per campaign for the list (not returned if we haven't calculated it yet).
`stats__target_sub_rate`	`FLOAT64`	The target number of subscriptions per month for the list to keep it growing (not returned if we haven't calculated it yet).
`stats__total_contacts`	`INT64`	The number of contacts in the list, including subscribed, unsubscribed, pending, cleaned, deleted, transactional, and those that need to be reconfirmed.
`stats__unsubscribe_count`	`INT64`	The number of members who have unsubscribed from the list.
`stats__unsubscribe_count_since_send`	`INT64`	The number of members who have unsubscribed since the last campaign was sent.
`subscribe_url_long`	`STRING`	The full version of this list's subscribe form (host will vary).
`subscribe_url_short`	`STRING`	Our EepURL shortened version of this list's subscribe form.
`use_archive_bar`	`BOOL`	Whether campaigns for this list use the Archive Bar in archives by default.
`visibility`	`STRING`	Whether this list is public or private.
`web_id`	`INT64`	The ID used in the Mailchimp web application. View this list in your Mailchimp account at `https://{dc}.admin.mailchimp.com/lists/members/?id={web_id}`.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: lists__modules

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`value`	`STRING`
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

reports (72 columns), A list of reports containing campaigns marked as Sent.

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`id`	`STRING`	A string that uniquely identifies this campaign.
`campaign_title`	`STRING`	The title of the campaign.
`type`	`STRING`	The type of campaign (regular, plain-text, ab_split, rss, automation, variate, or auto).
`list_id`	`STRING`	The unique list id.
`list_is_active`	`BOOL`	The status of the list used, namely if it's deleted or disabled.
`list_name`	`STRING`	The name of the list.
`subject_line`	`STRING`	The subject line for the campaign.
`preview_text`	`STRING`	The preview text for the campaign.
`emails_sent`	`INT64`	The total number of emails sent for this campaign.
`abuse_reports`	`INT64`	The number of abuse reports generated for this campaign.
`unsubscribed`	`INT64`	The total number of unsubscribed members for this campaign.
`send_time`	`TIMESTAMP`	The date and time a campaign was sent in ISO 8601 format.
`rss_last_send`	`TIMESTAMP`	For RSS campaigns, the date and time of the last send in ISO 8601 format.
`bounces__hard_bounces`	`INT64`	The total number of hard bounced email addresses.
`bounces__soft_bounces`	`INT64`	The total number of soft bounced email addresses.
`bounces__syntax_errors`	`INT64`	The total number of addresses that were syntax-related bounces.
`forwards__forwards_count`	`INT64`	How many times the campaign has been forwarded.
`forwards__forwards_opens`	`INT64`	How many times the forwarded campaign has been opened.
`opens__opens_total`	`INT64`	The total number of opens for a campaign.
`opens__unique_opens`	`INT64`	The total number of unique opens.
`opens__open_rate`	`FLOAT64`	The number of unique opens divided by the total number of successful deliveries.
`opens__last_open`	`TIMESTAMP`	The date and time of the last recorded open in ISO 8601 format.
`clicks__clicks_total`	`INT64`	The total number of clicks for the campaign.
`clicks__unique_clicks`	`INT64`	The total number of unique clicks for links across a campaign.
`clicks__unique_subscriber_clicks`	`INT64`	The total number of subscribers who clicked on a campaign.
`clicks__click_rate`	`FLOAT64`	The number of unique clicks divided by the total number of successful deliveries.
`clicks__last_click`	`TIMESTAMP`	The date and time of the last recorded click for the campaign in ISO 8601 format.
`facebook_likes__recipient_likes`	`INT64`	The number of recipients who liked the campaign on Facebook.
`facebook_likes__unique_likes`	`INT64`	The number of unique likes.
`facebook_likes__facebook_likes`	`INT64`	The number of Facebook likes for the campaign.
`industry_stats__type`	`STRING`	The type of business industry associated with your account. For example: retail, education, etc.
`industry_stats__open_rate`	`FLOAT64`	The industry open rate.
`industry_stats__click_rate`	`FLOAT64`	The industry click rate.
`industry_stats__bounce_rate`	`FLOAT64`	The industry bounce rate.
`industry_stats__unopen_rate`	`FLOAT64`	The industry unopened rate.
`industry_stats__unsub_rate`	`FLOAT64`	The industry unsubscribe rate.
`industry_stats__abuse_rate`	`FLOAT64`	The industry abuse rate.
`list_stats__sub_rate`	`FLOAT64`	The average number of subscriptions per month for the list.
`list_stats__unsub_rate`	`FLOAT64`	The average number of unsubscriptions per month for the list.
`list_stats__open_rate`	`FLOAT64`	The average open rate (a percentage represented as a number between 0 and 100) per campaign for the list.
`list_stats__click_rate`	`FLOAT64`	The average click rate (a percentage represented as a number between 0 and 100) per campaign for the list.
`ab_split__a__bounces`	`INT64`	Bounces for Campaign A.
`ab_split__a__abuse_reports`	`INT64`	Abuse reports for Campaign A.
`ab_split__a__unsubs`	`INT64`	Unsubscribes for Campaign A.
`ab_split__a__recipient_clicks`	`INT64`	Recipient Clicks for Campaign A.
`ab_split__a__forwards`	`INT64`	Forwards for Campaign A.
`ab_split__a__forwards_opens`	`INT64`	Opens from forwards for Campaign A.
`ab_split__a__opens`	`INT64`	Opens for Campaign A.
`ab_split__a__last_open`	`TIMESTAMP`	The last open for Campaign A.
`ab_split__a__unique_opens`	`INT64`	Unique opens for Campaign A.
`ab_split__b__bounces`	`INT64`	Bounces for Campaign B.
`ab_split__b__abuse_reports`	`INT64`	Abuse reports for Campaign B.
`ab_split__b__unsubs`	`INT64`	Unsubscribes for Campaign B.
`ab_split__b__recipient_clicks`	`INT64`	Recipients clicks for Campaign B.
`ab_split__b__forwards`	`INT64`	Forwards for Campaign B.
`ab_split__b__forwards_opens`	`INT64`	Opens for forwards from Campaign B.
`ab_split__b__opens`	`INT64`	Opens for Campaign B.
`ab_split__b__last_open`	`TIMESTAMP`	The last open for Campaign B.
`ab_split__b__unique_opens`	`INT64`	Unique opens for Campaign B.
`share_report__share_url`	`STRING`	The URL for the VIP report.
`share_report__share_password`	`STRING`	If password protected, the password for the VIP report.
`ecommerce__total_orders`	`INT64`	The total orders for a campaign.
`ecommerce__total_spent`	`FLOAT64`	The total spent for a campaign. Calculated as the sum of all order totals with no deductions.
`ecommerce__total_revenue`	`FLOAT64`	The total revenue for a campaign. Calculated as the sum of all order totals minus shipping and tax totals.
`ecommerce__currency_code`	`STRING`	The currency code used for the campaign.
`delivery_status__enabled`	`BOOL`	Whether Campaign Delivery Status is enabled for this account and campaign.
`delivery_status__can_cancel`	`BOOL`	Whether a campaign send can be canceled.
`delivery_status__status`	`STRING`	The current state of a campaign delivery.
`delivery_status__emails_sent`	`INT64`	The total number of emails confirmed sent for this campaign so far.
`delivery_status__emails_canceled`	`INT64`	The total number of emails canceled for this campaign.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: reports__timewarp

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`gmt_offset`	`INT64`	For campaigns sent with timewarp, the time zone group the member is part of.
`opens`	`INT64`	The number of opens.
`last_open`	`TIMESTAMP`	The date and time of the last open in ISO 8601 format.
`unique_opens`	`INT64`	The number of unique opens.
`clicks`	`INT64`	The number of clicks.
`last_click`	`TIMESTAMP`	The date and time of the last click in ISO 8601 format.
`unique_clicks`	`INT64`	The number of unique clicks.
`bounces`	`INT64`	The number of bounces.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: reports__timeseries

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`timestamp`	`TIMESTAMP`	The date and time for the series in ISO 8601 format.
`emails_sent`	`INT64`	The number of emails sent in the timeseries.
`unique_opens`	`INT64`	The number of unique opens in the timeseries.
`recipients_clicks`	`INT64`	The number of clicks in the timeseries.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

segment_members (30 columns)

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`id`	`STRING`	The unique identifier of the segment member.
`email_address`	`STRING`	The email address of the segment member.
`unique_email_id`	`STRING`	The unique identifier related to the email address.
`email_type`	`STRING`	The type of email the segment member receives.
`status`	`STRING`	The subscription status of the segment member.
`stats__avg_open_rate`	`FLOAT64`	The average open rate of the segment member.
`stats__avg_click_rate`	`FLOAT64`	The average click-through rate of the segment member.
`ip_signup`	`STRING`	The IP address where the segment member signed up.
`timestamp_signup`	`TIMESTAMP`	The date and time when the segment member signed up.
`ip_opt`	`STRING`	The IP address where the segment member opted in.
`timestamp_opt`	`TIMESTAMP`	The date and time when the segment member opted in.
`member_rating`	`INT64`	The rating assigned to the segment member.
`last_changed`	`TIMESTAMP`	The date and time when the segment member record was last updated.
`language`	`STRING`	The preferred language of the segment member.
`vip`	`BOOL`	Flag indicating if the segment member is a VIP.
`email_client`	`STRING`	The client used by the segment member to access their email.
`location__latitude`	`FLOAT64`	The latitude coordinate of the location.
`location__longitude`	`FLOAT64`	The longitude coordinate of the location.
`location__gmtoff`	`INT64`	The GMT offset of the location.
`location__dstoff`	`INT64`	The Daylight Saving Time offset of the location.
`location__country_code`	`STRING`	The country code of the location.
`location__timezone`	`STRING`	The timezone of the location.
`last_note__note_id`	`INT64`	The unique identifier of the note.
`last_note__created_at`	`TIMESTAMP`	The date and time when the note was created.
`last_note__created_by`	`STRING`	The user who created the note.
`last_note__note`	`STRING`	The content of the note.
`list_id`	`STRING`	The identifier of the list to which the segment member belongs.
`segment_id`	`INT64`	The identifier of the segment the member belongs to.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

segments (10 columns)

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`id`	`INT64`	Unique identifier for the segment
`name`	`STRING`	Name of the segment
`member_count`	`INT64`	Total number of members in the segment
`type`	`STRING`	Type of segment (static, dynamic)
`created_at`	`TIMESTAMP`	The date and time when the segment was created
`updated_at`	`TIMESTAMP`	The date and time when the segment was last updated
`options__match`	`STRING`	Type of match applied for multiple conditions (all, any)
`list_id`	`STRING`	ID of the list to which the segment belongs
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

Subtable: segmentsoptionsconditions

Column	Type	Description
`_kaivo_parent_id`	`STRING`	Foreign key referencing _kaivo_id in the parent table. Auto-generated by Kaivo.
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`condition_type`	`STRING`	Type of condition applied
`field`	`STRING`	Field to which the condition is applied
`op`	`STRING`	Operator used in the condition
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

tags (5 columns)

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`id`	`INT64`	Unique identifier of the tag.
`name`	`STRING`	Name of the tag.
`list_id`	`STRING`	Identifier of the list to which the tag belongs.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

unsubscribes (10 columns)

Column	Type	Description
`_kaivo_id`	`STRING`	Primary key that uniquely identifies the row. Auto-generated by Kaivo.
`email_id`	`STRING`	The unique ID of the unsubscribed email.
`email_address`	`STRING`	The email address of the subscriber who unsubscribed.
`vip`	`BOOL`	Indicates whether the subscriber was a VIP.
`timestamp`	`TIMESTAMP`	The date and time when the subscriber unsubscribed.
`reason`	`STRING`	The reason provided by the subscriber for unsubscribing.
`campaign_id`	`STRING`	The ID of the campaign associated with the unsubscribe.
`list_id`	`STRING`	The ID of the list from which the subscriber unsubscribed.
`list_is_active`	`BOOL`	Indicates whether the list is active or inactive.
`_kaivo_extracted_at`	`TIMESTAMP`	Timestamp that shows when the row was extracted. Auto-generated by Kaivo.

How the Mailchimp sync works

After the first load, Kaivo keeps your BigQuery warehouse up to date for you. Where Mailchimp supports it, each sync pulls only new and changed records so it stays fast; otherwise it refreshes the whole table. Every record keeps its original ID, so you won't get duplicate rows.

Frequently asked questions

How long does the initial sync take for Mailchimp?

It depends on how much history is in your Mailchimp account. Most initial syncs finish within minutes, while large accounts can take a few hours. After that, syncs only fetch new and changed records, so they're much faster.

Can I sync only some tables or columns?

Yes. You pick which tables to sync when you set up the connection and can change the selection later. Tables you don't select are never copied to your warehouse.

What happens when Mailchimp's schema changes?

New fields are never added automatically. You choose which fields to sync, so data you haven't selected (sensitive personal data, for example) never lands in your warehouse. When a new field appears, it becomes available for you to add. What happens to removed or renamed fields depends on a table's sync mode: full-refresh tables always match what's currently in Mailchimp, so dropped fields disappear, while incremental tables keep their existing columns and history, so an old field stays and newly added fields fill in over time.

Your data lives in your own Kaivo-managed BigQuery warehouse, so the most direct option is to delete or anonymise specific records right in BigQuery. If you delete data in Mailchimp instead, full-refresh tables drop it on the next sync, while incremental tables keep it, so you would remove the row in BigQuery or ask us to run a full refresh. To remove everything, delete the Mailchimp connector in Kaivo and all of its synced data is deleted with it.

Common use cases for Mailchimp data

Campaign performance

Use reports and email_activity to track opens, clicks, and conversions by campaign.

List growth

Analyse list_members and unsubscribes to monitor subscriber growth and churn.

Segment engagement

Report on segments to see which audiences engage and convert.

Marketing and revenue together

Join Mailchimp activity with order data in BigQuery to connect campaigns to sales.

Use Mailchimp data in your AI and BI tools

Once Mailchimp data lands in your Kaivo-managed BigQuery warehouse, you can explore it with AI tools or any BI tool that connects to BigQuery. Here's how the most common destinations work with Mailchimp data.

Claude

Use Kaivo's MCP server to give Claude secure, workspace-scoped access to your data. Setup guide →

Power BI

Microsoft's BI tool with a native BigQuery connector. Supports direct query and scheduled refresh. Setup guide →

Data Studio

Free Google BI tool with native BigQuery support. One-click connection to your Kaivo warehouse; great for SMB teams on Google Workspace. Setup guide →

Tableau

The premium analytics standard, with native BigQuery integration. Setup guide →

Google Sheets

Use Connected Sheets to query BigQuery directly from a spreadsheet, with no SQL. Setup guide →

Excel

Connect via Power Query's BigQuery connector. Setup guide →

Metabase

Open-source BI tool with strong BigQuery support. Setup guide →

See our pricing page for Mailchimp connector pricing and plan details.

Google Ads: Sync Google Ads to BigQuery.
Gmail: Sync Gmail to BigQuery.
Custobar: Sync Custobar to BigQuery.
Google Search Console: Sync Google Search Console to BigQuery.
HubSpot: Sync HubSpot to BigQuery.
Adform: Sync Adform to BigQuery.

Was this helpful?

Still need help? Share an idea