ListMembers
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.
- FullName supports the 'LIKE' 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 EmailAddress = '[email protected]' 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'
For columns, FullName LIKE operator is supported server side for the following queries:
If the FullName contains ' ' (SPACE) in the value then that query will be processed client side.
SELECT * FROM ListMembers where FullName like 'xyz%'
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 subscriber's email address, used as a unique identifier for the list member. |
| EmailAddress | String | False |
The subscriber's email address used for receiving campaign communications. |
| UniqueEmailId [KEY] | String | True |
A Mailchimp-wide identifier that distinguishes the email address across all lists and campaigns. |
| EmailType | String | False |
The preferred format of the email that the subscriber has opted to receive, such as 'HTML' or 'text'. |
| FullName | String | True |
The subscriber's full name, typically composed of the first and last name provided during signup. |
| Status | String | False |
The subscriber's current status in the list, which may be 'subscribed', 'unsubscribed', 'cleaned', 'pending', or 'transactional'. |
| StatusIfNew | String | False |
The subscriber's initial status to apply if the email address is not already present on the list when performing a PUT request. |
| Interests | String | False |
A list of interest category IDs that define the subscriber's preferences, allowing targeted segmentation. |
| Stats_AvgOpenRate | Double | True |
The subscriber's average open rate across all received campaigns. |
| Stats_AvgClickRate | Double | True |
The subscriber's average clickthrough rate across all received campaigns. |
| IpSignup | String | False |
The IP address from which the subscriber originally signed up for the list. |
| TimestampSignup | Datetime | False |
The date and time when the subscriber signed up for the list, recorded in ISO 8601 format. |
| IpOpt | String | False |
The IP address from which the subscriber confirmed their opt-in status. |
| TimestampOpt | Datetime | False |
The date and time when the subscriber confirmed their opt-in status, recorded in ISO 8601 format. |
| MemberRating | Integer | True |
The subscriber's engagement score, rated from 1 to 5 stars based on campaign interactions such as opens and clicks. |
| LastChanged | Datetime | True |
The date and time when the subscriber's information was last updated. |
| Language | String | False |
The language preference detected or set for the subscriber, used to send localized content when available. |
| Vip | Boolean | False |
Indicates whether the subscriber is marked as a VIP, typically used for high-value or priority contacts. |
| EmailClient | String | True |
The email client used by the subscriber, such as Outlook or Gmail, determined from campaign interaction data. |
| Location_Latitude | Double | False |
The geographical latitude of the subscriber's location, inferred from IP or profile data. |
| Location_Longitude | Double | False |
The geographical longitude of the subscriber's location, inferred from IP or profile data. |
| Location_Gmtoff | Integer | True |
The time difference in hours between the subscriber's local time and GMT. |
| Location_Dstoff | Integer | True |
The daylight saving time offset for the subscriber's location. |
| Location_CountryCode | String | True |
The two-letter ISO country code representing the subscriber's location. |
| Location_Timezone | String | True |
The subscriber's local timezone, used for scheduling campaigns appropriately. |
| LastNote_NoteId | Integer | True |
The unique identifier of the most recent note added to the subscriber's profile. |
| LastNote_CreatedAt | String | True |
The date and time when the most recent note was created. |
| LastNote_CreatedBy | String | True |
The name or username of the user who created the most recent note on the subscriber's profile. |
| LastNote_Note | String | True |
The text content of the subscriber's most recent note, providing additional context or manual observations. |
| ListId [KEY] | String | False |
The unique identifier of the Mailchimp audience (list) that the subscriber belongs to. |
| TagsAggregate | String | False |
A list of all tags applied to the subscriber, aggregated into a single field for easier querying and reporting. |
| ContactId | String | True |
A universal Mailchimp contact identifier that exists independently of an email address, allowing tracking of contacts across multiple channels. |
| WebId | Integer | True |
The Mailchimp web application ID that enables viewing this subscriber's details directly in the Mailchimp interface. |
| UnsubscribeReason | String | True |
The subscriber's stated reason for unsubscribing from the list, if provided. |
| ConsentsToOneToOneMessaging | Boolean | True |
Indicates whether the subscriber has given consent for one-to-one messaging, such as direct replies or personalized outreach. |
| Stats_EcommerceData_TotalRevenue | Decimal | True |
The total amount of revenue generated by the subscriber's orders, linked through e-commerce integrations. |
| Stats_EcommerceData_NumberOfOrders | Integer | True |
The total number of e-commerce orders placed by the subscriber. |
| Stats_EcommerceData_CurrencyCode | String | True |
The three-letter ISO 4217 currency code associated with the subscriber's e-commerce transactions. |
| Location_Region | String | True |
The geographic region or state associated with the subscriber's location. |
| MarketingPermissionsAggregate | String | False |
A list of the subscriber's marketing permissions, defining what types of communication they have consented to receive. |
| Source | String | True |
The origin from which the subscriber was added to the list, such as a signup form, import, or API integration. |
| TagsCount | Integer | True |
The total number of tags currently applied to the subscriber. |
| MergeFields | String | False |
A key-value collection of merge fields used for personalization, where the keys are merge tags like FNAME or LNAME. |
| SmsPhoneNumber | String | False |
The subscriber's phone number for SMS communications, formatted as a valid U.S. number. |
| SmsSubscriptionStatus | String | False |
The subscriber's current SMS subscription status, such as 'subscribed' or 'unsubscribed'. The allowed values are subscribed, unsubscribed, nonsubscribed, pending. |
| SmsSubscriptionLastUpdated | Datetime | False |
The date and time when the subscriber's SMS subscription status 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 identifier for the interest category used for filtering results, valid only for SELECT queries. |
| InterestMatch | String |
Defines how interests are matched when filtering subscribers. Accepts 'any', 'all', or 'none' and must be used with InterestCategoryId and InterestIds. |
| InterestIds | String |
Specifies one or more interest IDs to filter list members by, used in combination with InterestCategoryId and InterestMatch. |
| SinceLastCampaign | Boolean |
Filters subscribers by changes in status (subscribed, unsubscribed, pending, or cleaned) since the last campaign was sent. Valid only for SELECT queries. |
| UnsubscribedSince | Datetime |
Filters subscribers who unsubscribed after a specific date. Only works when the status is set to 'unsubscribed'. |