ListSegmentMembers

Lists subscribers that belong to a specific segment, including historical membership data.

Table Specific Information

SELECT, INSERT, and DELETE are supported for ListSegmentMembers.

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.

ListId supports the '=' operator.
SegmentId supports the '=' operator.
IncludeCleaned supports the '=' operator.
IncludeTransactional supports the '=' operator.
IncludeUnsubscribed supports the '=' operator.

SELECT * FROM ListSegmentMembers WHERE ListId = '5152' AND SegmentId = '2623'
SELECT * FROM ListSegmentMembers WHERE IncludeCleaned = true
SELECT * FROM ListSegmentMembers WHERE IncludeTransactional = true
SELECT * FROM ListSegmentMembers WHERE IncludeUnsubscribed = true

Insert

The Name and ListID are required for INSERTs.

INSERT INTO ListSegmentMembers (EmailAddress,ListId,SegmentId) VALUES ('[email protected]','44a64c46cb','7032720')

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The MD5 hash of the list member's email address, used as a unique identifier.
EmailAddress	String	False	The subscriber's email address associated with this list segment.
UniqueEmailId [KEY]	String	True	A unique identifier for the email address across all Mailchimp audiences.
EmailType	String	True	The preferred email format the subscriber has chosen to receive, either 'HTML' or 'text'.
Status	String	True	The subscriber's current status, which can be 'subscribed', 'unsubscribed', 'cleaned', or 'pending'.
StatusIfNew	String	True	The subscriber's status ('subscribed', 'unsubscribed', 'cleaned', or 'pending'), used when adding a new contact to the list via PUT if the email does not already exist.
Interests	String	True	A mapping of the subscriber's interests, where each key represents an interest category ID and its value indicates selection.
Stats_AvgOpenRate	Double	True	The subscriber's average email open rate across campaigns.
Stats_AvgClickRate	Double	True	The subscriber's average click-through rate across campaigns.
IpSignup	String	True	The IP address from which the subscriber originally signed up.
TimestampSignup	Datetime	True	The date and time when the subscriber joined the list.
IpOpt	String	True	The IP address from which the subscriber confirmed their opt-in status.
TimestampOpt	Datetime	True	The date and time when the subscriber confirmed their opt-in.
MemberRating	Integer	True	The subscriber's star rating (1–5) based on engagement and activity history.
LastChanged	Datetime	True	The date and time when the subscriber's profile or preferences were last updated.
Language	String	True	The detected or specified language preference of the subscriber.
Vip	Boolean	True	Indicates whether the subscriber is marked as a VIP contact.
EmailClient	String	True	The email client used by the subscriber, such as Gmail, Outlook, or Apple Mail.
Location_Latitude	Double	True	The geographic latitude of the subscriber's location, if available.
Location_Longitude	Double	True	The geographic longitude of the subscriber's location, if available.
Location_Gmtoff	Integer	True	The offset in hours between the subscriber's local time and Greenwich Mean Time (GMT).
Location_Dstoff	Integer	True	The daylight saving time offset for the subscriber's timezone, in hours.
Location_CountryCode	String	True	The two-letter ISO code for the subscriber's country.
Location_Timezone	String	True	The name of the subscriber's timezone, such as 'America/New_York'.
LastNote_NoteId	Integer	True	The unique identifier of the most recent note added to the subscriber's record.
LastNote_CreatedAt	String	True	The date when the latest note was created for the subscriber.
LastNote_CreatedBy	String	True	The username of the person who created the latest note.
LastNote_Note	String	True	The content or text of the latest note recorded for the subscriber.
ListId [KEY]	String	False	The unique identifier of the Mailchimp list the subscriber belongs to.
SegmentId [KEY]	String	False	The unique identifier of the list segment that this subscriber is part of.
MergeFields	String	True	A JSON dictionary of merge fields, where keys are merge tags and values are corresponding subscriber data fields.

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.