ListMembers

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

Table-Specific Information

Select

The provider uses the Mailchimp API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client-side within the provider.


Column	Supported Operators
Id	=
EmailAddress	=
UniqueEmailId	=
FullName	LIKE
EmailType	=
Status	=
Vip	=
ListId	=
InterestCategoryId	=
InterestMatch	=
InterestIds	=, IN
SinceLastCampaign	=
UnsubscribedSince	=
TimestampOpt	=, <, >, <=, >=
LastChanged	=, <, >, <=, >=

For example, the following queries are processed server-side:

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'

The FullName column supports the LIKE operator server-side. For example:

SELECT * FROM ListMembers WHERE FullName LIKE 'xyz%'

However, if the FullName value contains a space (for example, 'Jane Smith'), the query is processed client-side.

COUNT(*) queries are supported server-side when the ListId column is specified with the = operator. For example:

SELECT COUNT(*) FROM ListMembers WHERE ListId = 'a1b2c3d4e5'

Insert

To insert a list member, you must specify the ListId, EmailAddress, and Status columns.

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

To insert MergeFields, you must provide the complete aggregate.

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

To insert an individual MergeField, you must use the list-specific ListMembers table (for example, ListMember_List1, where List1 is the name of the list).

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

In the example above, LName is the merge field.

Update

To update a list member, specify the ListId and Id columns.

UPDATE ListMembers SET mergefields = '{"LNAME" : "aaaABCD"}' WHERE Id = '45151asd' AND ListId = 'asd151'

To update an individual MergeField, you must use the list-specific ListMembers table (for example, ListMember_List1, where List1 is the name of the list).

UPDATE ListMembers_List1 SET LName = 'asdawd' WHERE Id = '45151asd'

In the example above, LName is the merge field.

Delete

To delete a list member, specify the ListId and Id columns.

DELETE FROM ListMembers WHERE ListId = 'ada232' AND Id = '1511asd'

Columns


Name	Type	ReadOnly	References	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'. The allowed values are html, 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', 'archived', 'pending', or 'transactional'. The allowed values are subscribed, unsubscribed, cleaned, pending, transactional, archived.
StatusIfNew	String	True		The subscriber's initial status to apply if the email address is not already present on the list when performing a PUT request. The allowed values are subscribed, unsubscribed, cleaned, pending, transactional, archived.
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	True		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	ListMemberNotes.Id	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	Lists.Id	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	ListMemberNotes.Id	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	True		The subscriber's phone number for SMS communications, formatted as a valid U.S. number.
SmsSubscriptionStatus	String	True		The subscriber's current SMS subscription status, such as 'subscribed' or 'unsubscribed'. The allowed values are subscribed, unsubscribed, nonsubscribed, pending.
SmsSubscriptionLastUpdated	Datetime	True		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 more granular control over the data 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'.

ADO.NET Provider for Mailchimp