Google sheets + Google Script + Mailchimp API

sheets-script-mailchimp

function getMailChimpData(){
  
  // Here are the things you will change
  var API_KEY = 'YOUR-KEY-HERE';
  var MEMBER_LIST_ID = 'LIST-ID-WITH-SEGMENTS';
  var KYCIR_LIST_ID = 'LIST-ID-WITH-NO-SEGMENTS';
  
  function calculateAvgRates(runType, segmentID, rateType){
    var ratesList = [];
    
    if(runType == 'segment'){
      for(var i = 0; i < 51; i++){
        if(campaigns[i]['recipients']['segment_opts']['saved_segment_id'] != null){
          if(campaigns[i]['recipients']['segment_opts']['saved_segment_id'] ==  segmentID){
            ratesList.push(campaigns[i]['report_summary'][rateType]);
          }
        }
      }
    }else if(runType == 'list'){
      for(var i = 0; i < 101; i++){
        if(campaigns[i]['recipients']['list_id'] != null){
          if(campaigns[i]['recipients']['list_id'] ==  segmentID){
            ratesList.push(campaigns[i]['report_summary'][rateType]);
          }
        }
      }
    }
    
    var totalRates = 0;
    for(var i = 0; i < ratesList.length; i++) {
      totalRates += ratesList[i];
    }
    var rateAvg = totalRates / ratesList.length;
    
    return rateAvg
  }
  
  // URL and params for the Mailchimp API
  // NOTE: your region may be different (us7)
  var root = 'https://us7.api.mailchimp.com/3.0/';
  var listsEndpoint = 'lists';
  var segmentsEndpoint = 'lists/' + MEMBER_LIST_ID + '/segments'; 
  var campaignsEndpoint = 'campaigns?count=200';
  
  // parameters for url fetch
  var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'apikey ' + API_KEY
    }
  };
  
  try {
    // call the Mailchimp API
    var segmentData = [];
    
    var listsResponse = UrlFetchApp.fetch(root+listsEndpoint, params);
    var listsData = listsResponse.getContentText();
    var listsJSON = JSON.parse(listsData);
    var lists = listsJSON['lists'];
    
    var segmentsResponse = UrlFetchApp.fetch(root+segmentsEndpoint, params);
    var segmentsData = segmentsResponse.getContentText();
    var segmentsJSON = JSON.parse(segmentsData);
    var segments = segmentsJSON['segments'];
    
    var campaignResponse = UrlFetchApp.fetch(root+campaignsEndpoint, params);
    var campaignData = campaignResponse.getContentText();
    var campaignJson = JSON.parse(campaignData);
    var campaigns = campaignJson['campaigns'];
    
    for(var i = 0; i < lists.length; i++){
      var listID = lists[i]['id'];
      if(listID == MEMBER_LIST_ID){
        // Add the segment data to the array
        for (var s = 0; s < segments.length; s++) {
          var segmentID = segments[s]['id'];
          
          // put the segment data into a double array for Google Sheets
          if (segments[s]['member_count'] != 0) {
            segmentData.push([
              segments[s]['name'],
              segments[s]['member_count'],
              calculateAvgRates('segment', segmentID, 'open_rate'),
              calculateAvgRates('segment', segmentID, 'click_rate')
            ]);
          }
        };
                
      }else if(listID == KYCIR_LIST_ID){
        var kycirMembers = lists[i]['stats']['member_count'];
        
        segmentData.push([
          'KyCIR',
          kycirMembers,
          calculateAvgRates('list', listID, 'open_rate'),
          calculateAvgRates('list', listID, 'click_rate')
        ]);
      }
    }
    
    // select the campaign output sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('newsletter-analytics');
    
    // calculate the number of rows and columns needed
    var numRows = segmentData.length;
    var numCols = segmentData[0].length;
    
    // output the numbers to the sheet
    sheet.getRange(2,1,numRows,numCols).setValues(segmentData);
    
    // adds formulas to calculate open rate and click rate
    //for (var i = 0; i < numRows; i++) {
    //  sheet.getRange(4+i,9).setFormulaR1C1('=iferror(R[0]C[-2]/R[0]C[-3]*100,"N/a")');
    //  sheet.getRange(4+i,10).setFormulaR1C1('=iferror(R[0]C[-2]/R[0]C[-4]*100,"N/a")');
    //}
    
  }
  catch (error) {
    // deal with any errors
    Logger.log(error);
  };
}