Individuals who are currently or have been previously subscribed to this list, including members who have bounced or unsubscribed.

Table Specific Information

SELECT, INSERT, UPDATE, and DELETE are supported for ListMembers.

Select

The connector will use the Mailchimp API to process WHERE clause conditions built with the following column and operator. The rest of the filter is executed client-side within the connector.

• Id supports the '=' operator.
• EmailAddress supports the '=' operator.
• UniqueEmailId supports the '=' operator.
• EmailType supports the '=' operator.
• Status supports the '=' operator.
• Vip supports the '=' operator.
• ListId supports the '=' operator.
• InterestCategoryId supports the '=' operator.
• InterestMatch supports the '=' operator.
• InterestIds supports the '=, IN' operator.
• SinceLastCampaign supports the '=' operator.
• UnsubscribedSince supports the '=' operator.
• TimestampOpt supports the '=,<,>,<=,>=' operator.
• LastChanged supports the '=,<,>,<=,>=' operator.

SELECT * FROM ListMembers WHERE ListId = '121' and Id='456'
SELECT * FROM ListMembers WHERE EmailAddress = '[email protected]' and EmailType='html'
SELECT * FROM ListMembers WHERE SinceLastCampaign = true and Status='cleaned'
SELECT * FROM ListMembers WHERE UnsubscribedSince = '2024-02-07 00:00:37.0' and Status='unsubscribed'
SELECT * FROM ListMembers WHERE InterestCategoryId = 'abcd' and InterestIds IN ('123','321') and InterestMatch='any'
SELECT * FROM ListMembers WHERE LastChanged = '2024-02-07 00:00:37.0'
SELECT * FROM ListMembers WHERE LastChanged >= '2024-02-07 00:00:37.0'
SELECT * FROM ListMembers WHERE LastChanged <= '2024-02-07 00:00:37.0'
SELECT * FROM ListMembers WHERE LastChanged > '2024-02-07 00:00:37.0'
SELECT * FROM ListMembers WHERE LastChanged < '2024-02-07 00:00:37.0'

Insert

The ListId, EmailAddress, and Status are required for INSERTs.

INSERT INTO ListMembers (ListId, EmailAddress, Status) VALUES ('myListId', 'myEmailAddress', 'subscribed')

To insert MergeFields, you will have to provide whole aggregate.

INSERT INTO ListMembers (ListId, EmailAddress, Status, MergeFields) VALUES ('myListId', 'myEmailAddress', 'subscribed', '{\"LName\" : \"asd\"}')

To insert individual MergeField you will have to use List specific ListMembers Table, for example : ListMember_List1, here List1 is the name of the list.

INSERT INTO ListMembers_List1 (ListId, EmailAddress, Status, LName) VALUES ('myListId', 'myEmailAddress', 'subscribed', 'asd')

Here, LName is the mergefield.

Update

The ListId and Id are required for Update.

Update ListMembers set mergefields = '{  "LNAME": "aaaABCD"}' where Id = '45151asd' and ListId='asd151'

To update individual MergeField you will have to use List specific ListMembers Table, for example : ListMember_List1, here List1 is the name of the list.

Update ListMembers_List1 set LName = 'asdawd' where Id = '45151asd'

Here, LName is the mergefield.

Delete

The ListId and Id are required for Delete.

DELETE FROM ListMembers where ListId='ada232' and Id='1511asd'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	The MD5 hash of the list member's email address.
EmailAddress	String	False	Email address for a subscriber.
UniqueEmailId [KEY]	String	True	An identifier for the address across all of MailChimp.
EmailType	String	False	Type of email this member asked to get ('html' or 'text').
FullName	String	True	The contact's full name.
Status	String	False	Subscriber's current status ('subscribed', 'unsubscribed', 'cleaned', 'pending' or 'transactional').
StatusIfNew	String	False	Subscriber's status ('subscribed', 'unsubscribed', 'cleaned', or 'pending'), to be used only on a PUT request if the email is not already present on the list.
Interests	String	False	The key of this object's properties is the ID of the interest in question.
Stats_AvgOpenRate	Double	True	A subscriber's average open rate.
Stats_AvgClickRate	Double	True	A subscriber's average clickthrough rate.
IpSignup	String	False	IP address the subscriber signed up from.
TimestampSignup	Datetime	False	Date and time the subscriber signed up for the list.
IpOpt	String	False	IP address the subscriber confirmed their opt-in status.
TimestampOpt	Datetime	False	Date and time the subscribe confirmed their opt-in status.
MemberRating	Integer	True	Star rating for this member between 1 and 5.
LastChanged	Datetime	True	Date and time the member's info was last changed.
Language	String	False	If set/detected, the language of the subscriber.
Vip	Boolean	False	VIP status for subscriber.
EmailClient	String	True	The email client the address as using.
Location_Latitude	Double	False	The location latitude.
Location_Longitude	Double	False	The location longitude.
Location_Gmtoff	Integer	True	The time difference in hours from GMT.
Location_Dstoff	Integer	True	The offset for timezones where daylight saving time is observed.
Location_CountryCode	String	True	The unique code for the location country.
Location_Timezone	String	True	The timezone for the location.
LastNote_NoteId	Integer	True	The note's ID.
LastNote_CreatedAt	String	True	The date the note was created.
LastNote_CreatedBy	String	True	The author of the note.
LastNote_Note	String	True	The content of the note.
ListId [KEY]	String	False	The id for the list.
TagsAggregate	String	False	Tags of the member, displayed as an aggregate.
ContactId	String	True	As Mailchimp evolves beyond email, you may eventually have contacts without email addresses. While the id is the MD5 hash of their email address, this contact_id is agnostic of contact?s inclusion of an email address.
WebId	Integer	True	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}.
UnsubscribeReason	String	True	A subscriber's reason for unsubscribing.
ConsentsToOneToOneMessaging	Boolean	True	Indicates whether a contact consents to 1:1 messaging.
Stats_EcommerceData_TotalRevenue	Decimal	True	The total revenue the list member has brought in.
Stats_EcommerceData_NumberOfOrders	Integer	True	The total number of orders placed by the list member.
Stats_EcommerceData_CurrencyCode	String	True	The three-letter ISO 4217 code for the currency that the store accepts.
Location_Region	String	True	The region for the location.
MarketingPermissionsAggregate	String	False	The marketing permissions for the subscriber.
Source	String	True	The source from which the subscriber was added to this list.
TagsCount	Integer	True	The number of tags applied to this member.
MergeFields	String	False	A dictionary of merge fields where the keys are the merge tags.
SmsPhoneNumber	String	False	A US phone number for SMS contact.
SmsSubscriptionStatus	String	False	The status of an SMS subscription. The allowed values are subscribed, unsubscribed, nonsubscribed, pending.
SmsSubscriptionLastUpdated	Datetime	False	The datetime when the SMS subscription was last updated.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
InterestCategoryId	String	The unique id for the interest category, valid only for SELECT.
InterestMatch	String	Used to filter list members by interests. Must be accompanied by InterestCategoryId and InterestIds, valid only for SELECT. Possible values: 'any', 'all', or 'none'
InterestIds	String	Used to filter list members by interests. Must be accompanied by InterestCategoryId and InterestMatch, valid only for SELECT.
SinceLastCampaign	Boolean	Filter subscribers by those subscribed/unsubscribed/pending/cleaned since last email campaign send. Status is required to use this filter, valid only for SELECT
UnsubscribedSince	Datetime	Filter subscribers by those unsubscribed since a specific date. Using any status other than unsubscribed with this filter will result in an error, valid only for SELECT