Table ListMembers

Description

This table supports bulk operations. For bulk Add/Update use UPSERT operation. All other operations are row by row.

Parameters

Parameter	Label	Required	Options	Description	Help
There are no parameters

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.

Method	Supported	Reference EndPoint
SELECT		get_list_members
INSERT		add_list_member
UPDATE		update_list_member
UPSERT		upsert_list_members
DELETE		delete_list_member
LOOKUP		get_list_member

Examples

SSIS

Use Mailchimp Connector in API Source component to read data or in API Destination component to read/write data:

Read from ListMembers table using API Source

Mailchimp

ListMembers

SSIS API Source - Read from table or endpoint

Read/write to ListMembers table using API Destination

Mailchimp

ListMembers

Select

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Get a list member by Id from a Mailchimp List

SELECT * FROM ListMembers 
WHERE Id='170a0722daae03855d6434eb3a5959fb' 
WITH (ListId='e246f7e24d')

Get a list member by Email from a Mailchimp List

SELECT * FROM ListMembers 
WITH (ListId='e246f7e24d', Id='test@abc.com')

Delete a list member (archive) from a Mailchimp List.

Delete List Member by Id and archive it. To delete permanently use delete_list_member_permanent endpoint. Archived / bounced record cannot be resubscribed. To resubscribe you must call delete_list_member_permanent

DELETE FROM ListMembers 
WHERE Id='170a0722daae03855d6434eb3a5959fb' 
WITH (ListId='e246f7e24d')

Delete list members (archive) from a Mailchimp List which are created after specific date (i.e. after Opt In time).

Delete list member operation archives the record. To delete permanently use delete_list_member_permanent endpoint. Archived / bounced record cannot be resubscribed. To resubscribe you must call delete_list_member_permanent.

DELETTE from ListMembers 
WHERE TimestampOpt > '2023-06-16' 
WITH (ListId='a4d24015f8')

Get list of all members within all lists/audiences in your Mailchimp account

SELECT * FROM ListMembers --scan all lists and then fetch members for each list

Get details on a specific member within all lists/audiences in your Mailchimp account

SELECT * FROM ListMembers 
WHERE Id='170a0722daae03855d6434eb3a5959fb'

Get specific columns in list of members within a specific list/audience in your Mailchimp account

SELECT Id, ListId, EmailAddress, UniqueEmailId, ContactId, FullName, WebId, EmailType, Status, ConsentsToOneToOneMessaging, FirstName, LastName, AddressLine1, AddressLine2, City, State, Zip, Country, Phone, Birthday, StatsAvgOpenRate, StatsAvgClickRate, IpSignup, TimestampSignup, IpOpt, TimestampOpt, MemberRating, LastChanged, Language, Vip, EmailClient, Latitude, Longitude, LocationGmtOff, LocationDstOff, CountryCode, TimeZone, Region, Source, TagsCount, Tags 
FROM ListMembers 
WITH (ListId='e246f7e24d')

Create a new member in the specified list/audience in your Mailchimp account

INSERT INTO ListMembers
(EmailAddress, Status, EmailType, MergeFields, Language, Vip, Latitude, Longitude, IpSignup, TimestampSignup, IpOpt, TimestampOpt, Tags)
VALUES
('np-brucewayne1@zappysys.com', 'subscribed', 'html',
'{"FNAME":"John","LNAME":"Doe","ADDRESS":{"addr1":"123","Freddie":"Ave","city":"Atlanta","state":"GA","zip":"12345"}}',
'en', false, '41.881832', '-87.623177', '192.168.0.8', '2023-04-01 18:00:00', '192.168.0.8', '2023-04-02 14:00:00',
'["Newtag1","Newtag2","Newtag3"]')
WITH (ListId='a4d24015f8')

Update an existing member in the specified list/audience in your Mailchimp account

UPDATE ListMembers 
SET EmailAddress='brucewayne10@mycompany.com'
, Status='subscribed' --subscribed, unsubscribed, cleaned, pending
, EmailType='text' --html, text
, Vip='false' --true
, FirstName='Bruce'
, LastName='Wayne'
/*,MergeFields= '{
        "FNAME": "Bruce1",
        "LNAME": "Wayne1",
        "PHONE": "678-111-1234"
    }',
*/
, Language='en' --fr
, TimestampOpt='2023-04-02 11:37:49' 
, Latitude='38.8951' , Longitude='-77.0364' --Washington DC
, Tags='["tag1","tag2"]'
WHERE Id='e9f73ced3b649f0ca829103bcacb2846' 
WITH (ListId='a4d24015f8',SkipMergeValidation='false')

Bulk Add / Update MailChimp Subscibers (members) from Microsoft SQL Server Table - Subscribe / Unsubscribe

UPSERT INTO ListMembers
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select ''brucewayne10@gmail.com'' EmailAddress,''first1'' as FirstName, ''subscribed'' Status 
UNION ALL
select ''test55@gmail.com'' EmailAddress,''first1'' FirstName, ''subscribed'' Status 
')
WITH(ListId='a4d24015f8')

 --//column name alias must match with InputColumns of ListMembers

Bulk Add / Update Subscibers (list members) from CSV File - Subscribe / Unsubscribe

UPSERT INTO ListMembers
SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=c:\subscribers.csv'
,'select col1 as EmailAddress,col2 as FirstName, col3 as Status from $') --//column name alias must match with InputColumns of ListMembers
WITH(ListId='a4d24015f8')

Bulk Add / Update Subscibers (list members) from any ODBC Source - Subscribe / Unsubscribe

UPSERT INTO ListMembers
SOURCE('ODBC', 'DSN=MyOdbcDsn
,'select col1 as EmailAddress,col2 as FirstName, col3 as Status from sometable') --//column name alias must match with InputColumns of ListMembers
WITH(ListId='a4d24015f8')

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway: