Queries Library
Queries LibraryCorrelate user data from WordPress and Mailchimp

Correlate user data from WordPress and Mailchimp

This query combines the user data from the local WordPress site, and data from those same users from Mailchimp, using the user's email as the common identificator between the two systems.

The result will be the email, name and lastname user data coming from WordPress, augmented with field location from Mailchimp.

query FetchUserDataFromMailchimpList(
  # mailchimpDataCenterCode: Code for the data center of your account on Mailchimp (See: https://mailchimp.com/developer/marketing/docs/fundamentals/#api-structure)
  $mailchimpDataCenterCode: String!
  # Audience ID for the list on Mailchimp to which to subscribe the email
  $mailchimpAudienceID: String!
) {
  mailchimpUsername: _env(name: "MAILCHIMP_API_CREDENTIALS_USERNAME")
  mailchimpPassword: _env(name: "MAILCHIMP_API_CREDENTIALS_PASSWORD")
  mailchimpAPIEndpoint: _sprintf(
    string: "https://%s.api.mailchimp.com/3.0/lists/%s/members",
    values: [$mailchimpDataCenterCode, $mailchimpAudienceID]
  mailchimpListMembersJSONObject: _sendJSONObjectItemHTTPRequest(input: {
    url: $__mailchimpAPIEndpoint,
    method: GET,
    options: {
      auth: {
        username: $__mailchimpUsername,
        password: $__mailchimpPassword
  mailchimpUserData: _objectProperty(
    object: $__mailchimpListMembersJSONObject,
    by: { key: "members"}
      @objectKeepProperties(keys: ["email_address", "location"])
    @export(as: "mailchimpUserData")
query FetchUserDataFromWordPress {
  users(pagination: { limit: -1 }) {
    email_address: email
        as: "wordpressUserData",
        type: LIST,
        affectAdditionalFieldsUnderPos: [1, 2, 3]
query CombineUserDataFromMailchimpAndWordPress
  @depends(on: [
  combinedWordPressAndMailchimpUserData: _arrayInnerJoinJSONObjectProperties(
    source: $mailchimpUserData,
    target: $wordpressUserData,
    index: "email_address"

And define in wp-config.php:

define( 'MAILCHIMP_API_CREDENTIALS_USERNAME', '{ username }' );
define( 'MAILCHIMP_API_CREDENTIALS_PASSWORD', '{ password }' );