Reference

Endpoint Add or Update Upsert List Members (Bulk Subscribe or Unsubscribe)

Name

upsert_list_members

Description

Add or Update Upsert List Members (Bulk Subscribe or Unsubscribe). [API reference]

Related Tables

ListMembers , ListMembersBulk

Parameters

Parameter Required Options
Name: ListId

Label: List Id

The ID of the list for which you like to perform this operation. 		YES
Name: SkipMergeValidation

Label: Skip Merge Validation

If skip_merge_validation is true, member data will be accepted without merge field values, even if the merge field is usually required. This defaults to false.
Option Value
true true
false false
Name: SkipDuplicateCheck

Label: Skip Duplicate Check

If skip_duplicate_check is true, we will ignore duplicates sent in the request when using the batch sub/unsub on the lists endpoint. The status of the first appearance in the request will be saved. This defaults to false.
Option Value
true true
false false
Name: UpdateExisting

Label: Update Exising Subscription Status?

Whether this batch operation will change existing members' subscription status.
Option Value
true true
false false
Name: SyncTags

Label: Sync Tags

Whether this batch operation will replace all existing tags with tags in request.
Option Value
true true
false false

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
Id DT_WSTR nvarchar(200) 200
ListId DT_WSTR nvarchar(200) 200
EmailAddress DT_WSTR nvarchar(200) 200
UniqueEmailId DT_WSTR nvarchar(200) 200
ContactId DT_WSTR nvarchar(200) 200
FullName DT_WSTR nvarchar(100) 100
WebId DT_I4 int
EmailType DT_WSTR nvarchar(20) 20
Status DT_WSTR nvarchar(20) 20
ConsentsToOneToOneMessaging DT_BOOL bit
FirstName DT_WSTR nvarchar(50) 50
LastName DT_WSTR nvarchar(50) 50
AddressLine1 DT_WSTR nvarchar(200) 200
AddressLine2 DT_WSTR nvarchar(200) 200
City DT_WSTR nvarchar(200) 200
State DT_WSTR nvarchar(100) 100
Zip DT_WSTR nvarchar(20) 20
Country DT_WSTR nvarchar(100) 100
Phone DT_WSTR nvarchar(50) 50
Birthday DT_WSTR nvarchar(50) 50
StatsAvgOpenRate DT_I4 int
StatsAvgClickRate DT_I4 int
IpSignup DT_WSTR nvarchar(200) 200
TimestampSignup DT_DBTIMESTAMP datetime
IpOpt DT_WSTR nvarchar(200) 200
TimestampOpt DT_DBTIMESTAMP datetime
MemberRating DT_I4 int
LastChanged DT_DBTIMESTAMP datetime
Language DT_WSTR nvarchar(100) 100
Vip DT_BOOL bit
EmailClient DT_WSTR nvarchar(200) 200
Latitude DT_I4 int
Longitude DT_I4 int
LocationGmtOff DT_I4 int
LocationDstOff DT_I4 int
CountryCode DT_WSTR nvarchar(100) 100
TimeZone DT_WSTR nvarchar(100) 100
Region DT_WSTR nvarchar(100) 100
Source DT_WSTR nvarchar(100) 100
TagsCount DT_I4 int
Tags DT_WSTR nvarchar(4000) 4000
RowStatus DT_WSTR nvarchar(50) 50
TotalCreated DT_I4 int
TotalUpdated DT_I4 int
ErrorCount DT_I4 int
ErrorMessage DT_WSTR nvarchar(4000) 4000
ErrorCode DT_WSTR nvarchar(255) 255
ErrorField DT_WSTR nvarchar(255) 255
ErrorFieldMessage DT_WSTR nvarchar(4000) 4000
If the column you are looking for is missing, consider customizing Mailchimp Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
EmailAddress DT_WSTR nvarchar(200) 200
Status DT_WSTR nvarchar(80) 80 Subscriber's current status. Possible values: subscribed, unsubscribed, cleaned, pending, or transactional.
EmailType DT_WSTR nvarchar(20) 20 Type of email this member asked to get ('html' or 'text').
FirstName DT_WSTR nvarchar(255) 255
LastName DT_WSTR nvarchar(255) 255
AddressLine1 DT_WSTR nvarchar(200) 200
AddressLine2 DT_WSTR nvarchar(200) 200
City DT_WSTR nvarchar(200) 200
State DT_WSTR nvarchar(100) 100
Zip DT_WSTR nvarchar(20) 20
Country DT_WSTR nvarchar(100) 100
Phone DT_WSTR nvarchar(50) 50
Birthday DT_WSTR nvarchar(50) 50
MergeFields DT_WSTR nvarchar(4000) 4000 A dictionary of merge fields where the keys are the merge tags.
Interests DT_WSTR nvarchar(4000) 4000 The key of this object's properties is the ID of the interest in question.
Language DT_WSTR nvarchar(10) 10 If set/detected, the subscriber's language.
Vip DT_BOOL bit VIP status for subscriber.
Latitude DT_WSTR nvarchar(4000) 4000 The location latitude.
Longitude DT_WSTR nvarchar(4000) 4000 The location longitude.
MarketingPermissions DT_WSTR nvarchar(4000) 4000 The marketing permissions for the subscriber.
IpSignup DT_WSTR nvarchar(200) 200 IP address the subscriber signed up from.
TimestampSignup DT_DBTIMESTAMP datetime The date and time the subscriber signed up for the list in ISO 8601 format.
IpOpt DT_WSTR nvarchar(200) 200 The IP address the subscriber used to confirm their opt-in status.
TimestampOpt DT_DBTIMESTAMP datetime The date and time the subscriber confirmed their opt-in status in ISO 8601 format.
Tags DT_WSTR nvarchar(4000) 4000 The tags that are associated with a member.
Required columns that you need to supply are bolded.

Examples

SSIS

Use Mailchimp Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Destination

This Endpoint belongs to the ListMembers table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to add or update upsert list members:

API Destination - Mailchimp
Read / write MailChimp Campaigns, Lists, Members, Reports and more in without coding.
Mailchimp
ListMembers
Upsert
Required Parameters
List Id Fill-in the parameter...
Optional Parameters
Skip Merge Validation false
Skip Duplicate Check false
Update Exising Subscription Status? true
Sync Tags false
ExtraFilters $.updated_members[*]::$.errors[*]
ExtraFilterColumns RowStatus=New::RowStatus=Updated::RowStatus=Error
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

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')

upsert_list_members endpoint belongs to ListMembers , ListMembersBulk table(s), and can therefore be used via those table(s).

SQL Server

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

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

DECLARE @MyQuery NVARCHAR(MAX) = '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';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

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

DECLARE @MyQuery NVARCHAR(MAX) = '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'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

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

DECLARE @MyQuery NVARCHAR(MAX) = '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'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

upsert_list_members endpoint belongs to ListMembers , ListMembersBulk table(s), and can therefore be used via those table(s).