In [4]:
# %load mailchimp_analytics.py
__author__ = "Mike Portanova"

import pandas as pd
import numpy as np
import requests
import json
import csv
import sys

from time import sleep
from pprint import pprint as pp

"""Grabs a list of campaign IDs from Mailchimp and then loops through grabbing
the top performing content from each campaign"""


apikey = json.load(open('mailchimp_key.json'))
apikey = apikey["apikey"].encode('ascii','ignore')

def get_campaigns():
    """Get a list of campaigns with a lookback count set as argv[1]. Return a DF with the campaign IDs and the list to which they were delivered (main list only)"""
    endpoint = 'https://us1.api.mailchimp.com/3.0/campaigns'
    params = {'sort_field':'send_time',
        'sort_dir' : 'DESC',
        'type':'absplit',
        'count':500}
    response = requests.get(endpoint, auth=('apikey', apikey),params=params)
    response_json = response.json()
    IDs = pd.Series([response_json["campaigns"][item]["id"] for item in range(len(response_json["campaigns"])) if response_json["campaigns"][item]["recipients"]["list_id"] == "62ba9246c0"],name="Campaign ID")
    Names = pd.Series([response_json["campaigns"][item]["recipients"]["list_id"] for item in range(len(response_json["campaigns"])) if response_json["campaigns"][item]["recipients"]["list_id"] == "62ba9246c0"],name="List ID")
    df = pd.concat([IDs, Names],axis=1)
    return df

#I need to loop through the campaigns/<campaign id> endpoint, grab the winning id
#and then request the campaigns/<campaign id>/click-details endpoint and the
#campaigns/<campaign id> endpoint (grabbing the opens). Save that to a DataFrame
#with the campaign ID and opens as two different columsn.

def get_opens():
    """Loop over the DF from get_campaigns() and grab the winning campaign ID and opens for each absplit campaign. Return a new DF with just winning IDs and Opens"""
    df = get_campaigns()
    opens = []
    winning_id = []
    for item in range(len(df["Campaign ID"])):
        campaign_ID = df["Campaign ID"][item]
        endpoint = 'https://us1.api.mailchimp.com/3.0/campaigns/'+campaign_ID
        response = requests.get(endpoint, auth=('apikey', apikey)).json()
        try:
            winning_id.append(response["variate_settings"]["winning_campaign_id"])
            campaign_ID2 = response["variate_settings"]["winning_campaign_id"]
            endpoint2 = 'https://us1.api.mailchimp.com/3.0/campaigns/'+campaign_ID2
            response2 = requests.get(endpoint2, auth=('apikey', apikey)).json()
            opens.append(response2["report_summary"]["opens"])
        except KeyError:
            try:
                opens.append(response["report_summary"]["opens"])
                winning_id.append(campaign_ID)
                continue
            except KeyError:
                pp(response)
                break
    print "winning id", len(winning_id), winning_id
    print "opens", len(opens), opens
    print "df length", len(df["Campaign ID"])
    winning_ids = pd.Series(winning_id, name="Winning ID")
    total_opens = pd.Series(opens, name="Opens")
    #we don't need df anymore, so let's create a new DataFrame with just IDs/opens
    df1 = pd.concat([winning_ids, total_opens], axis=1)
    return df1



def get_urls():
    """Loop over the dataframe returned from get_opens() and create a dictionary of URLs : clicks for each campaign. Add that back to the DataFrame."""
    df = get_opens()
    urls = []
    for item in range(len(df["Winning ID"])):
        urls_campaign = {}
        campaign_ID = df["Winning ID"][item]
        endpoint = 'https://us1.api.mailchimp.com/3.0/reports/'+campaign_ID+'/click-details'
        response = requests.get(endpoint, auth=('apikey', apikey)).json()
        for item in range(len(response["urls_clicked"])):
            url = response["urls_clicked"][item]["url"].split("?utm_source")
            urls_campaign[url[0]] = response["urls_clicked"][item]["total_clicks"]
        urls.append(urls_campaign)
    df.loc[:,"URLs"] = urls
    print df
    return df

def get_top_content():
    """Create a new DF that has the URL, the associated campaign ID, the clicks, and the CTR from the DF from get_urls()"""
    df = get_urls()
    #pre-allocate memory by creating index ahead of time
    df1 = pd.DataFrame(index=np.arange(0,len(df["Winning ID"])*8), columns=['URL','Clicks','Opens','Campaign ID'])
    #Loop through entire DF
    index_position = 0
    for item in range(len(df["URLs"])):
        for thing in df["URLs"][item].iterkeys():
            df1.loc[index_position] = [thing,float(df["URLs"][item][thing]),float(df["Opens"][item]),df["Winning ID"][item]]
            index_position += 1
    df1["Click Rate"] = df1["Clicks"] / df1["Opens"]
    df1.to_csv('mailchimp_stats.csv')
    print df1
    return df1

    #Then, loop through each dictionary



#Then, I want to set a fourth column in the second dataframe to have the open rate for the campaign correlating to that data.

#Then I create a 5th column that gives the CTR of those URLs.

#Then write that to a csv.

#Then, save each url and click count




# def main():
#     get_top_content()

# if __name__ == '__main__':
#   main()

In [12]:
df = pd.DataFrame(get_top_content())


{u'_links': [{u'href': u'https://us1.api.mailchimp.com/3.0/campaigns',
              u'method': u'GET',
              u'rel': u'parent',
              u'schema': u'https://us1.api.mailchimp.com/schema/3.0/CollectionLinks/Campaigns.json',
              u'targetSchema': u'https://us1.api.mailchimp.com/schema/3.0/Campaigns/Collection.json'},
             {u'href': u'https://us1.api.mailchimp.com/3.0/campaigns/8fa6c0f1a1',
              u'method': u'GET',
              u'rel': u'self',
              u'targetSchema': u'https://us1.api.mailchimp.com/schema/3.0/Campaigns/Instance.json'},
             {u'href': u'https://us1.api.mailchimp.com/3.0/campaigns/8fa6c0f1a1',
              u'method': u'DELETE',
              u'rel': u'delete'},
             {u'href': u'https://us1.api.mailchimp.com/3.0/campaigns/8fa6c0f1a1/actions/send',
              u'method': u'POST',
              u'rel': u'send'},
             {u'href': u'https://us1.api.mailchimp.com/3.0/campaigns/8fa6c0f1a1/actions/cancel-send',
              u'method': u'POST',
              u'rel': u'cancel_send'},
             {u'href': u'https://us1.api.mailchimp.com/3.0/campaigns/8fa6c0f1a1/feedback',
              u'method': u'GET',
              u'rel': u'feedback',
              u'targetSchema': u'https://us1.api.mailchimp.com/schema/3.0/Campaigns/Feedback/Collection.json'},
             {u'href': u'https://us1.api.mailchimp.com/3.0/campaigns/8fa6c0f1a1/content',
              u'method': u'GET',
              u'rel': u'content',
              u'targetSchema': u'https://us1.api.mailchimp.com/schema/3.0/Campaigns/Content/Collection.json'},
             {u'href': u'https://us1.api.mailchimp.com/3.0/campaigns/8fa6c0f1a1/send-checklist',
              u'method': u'GET',
              u'rel': u'send_checklist',
              u'targetSchema': u'https://us1.api.mailchimp.com/schema/3.0/Campaigns/Checklist/Collection.json'}],
 u'ab_split_opts': {u'from_name_a': u'',
                    u'from_name_b': u'',
                    u'pick_winner': u'opens',
                    u'reply_email_a': u'',
                    u'reply_email_b': u'',
                    u'send_time_a': u'',
                    u'send_time_b': u'',
                    u'send_time_winner': u'',
                    u'split_size': 5,
                    u'split_test': u'subject',
                    u'subject_a': u'Lake Bed Secrets, Disney Cults, and The Atomic Secretary',
                    u'subject_b': u"What Lies Beneath America's Lakes",
                    u'wait_time': 1,
                    u'wait_units': u'days'},
 u'archive_url': u'http://eepurl.com/bq_r3z',
 u'content_type': u'template',
 u'create_time': u'2015-06-16T21:11:44+00:00',
 u'delivery_status': {u'enabled': False},
 u'emails_sent': 0,
 u'id': u'8fa6c0f1a1',
 u'recipients': {u'list_id': u'62ba9246c0',
                 u'segment_opts': {u'conditions': [{u'condition_type': u'SignupSource',
                                                    u'field': u'source',
                                                    u'op': u'source_is',
                                                    u'value': u'API - Generic'},
                                                   {u'condition_type': u'SignupSource',
                                                    u'field': u'source',
                                                    u'op': u'source_is',
                                                    u'value': u'Admin Add'},
                                                   {u'condition_type': u'SignupSource',
                                                    u'field': u'source',
                                                    u'op': u'source_is',
                                                    u'value': u'Hosted Signup Form'},
                                                   {u'condition_type': u'Interests',
                                                    u'field': u'interests-432523bb47',
                                                    u'op': u'interestcontains',
                                                    u'value': [u'8d970f72ba']},
                                                   {u'condition_type': u'TextMerge',
                                                    u'field': u'MMERGE17',
                                                    u'op': u'blank_not',
                                                    u'value': u''}],
                                   u'match': u'any',
                                   u'saved_segment_id': 73},
                 u'segment_text': u'<p class="nomargin">Subscribers match <strong>any</strong> of the following conditions:</p><ol id="conditions" class="conditions"><li class="mar-lv1 mar-lr0">Signup Source source was API - Generic</li><li class="mar-lv1 mar-lr0">Signup Source source was Admin Add</li><li class="mar-lv1 mar-lr0">Signup Source source was Hosted Signup Form</li><li class="mar-lv1 mar-lr0"><strong>Remaining 2015</strong> one of <strong>merged</strong></li><li class="mar-lv1 mar-lr0">DataValidationGrade is not blank  </li></ol><span>For a total of <strong>0</strong> emails sent.</span>'},
 u'send_time': u'',
 u'settings': {u'authenticate': True,
               u'auto_footer': False,
               u'auto_tweet': False,
               u'drag_and_drop': True,
               u'fb_comments': False,
               u'folder_id': u'',
               u'from_name': u'Atlas Obscura',
               u'inline_css': False,
               u'reply_to': u'info@atlasobscura.com',
               u'subject_line': u'News from Atlas Obscura',
               u'template_id': 37693,
               u'timewarp': False,
               u'title': u'Newsletter 6/16/2015 (copy 01)',
               u'to_name': u'',
               u'use_conversation': False},
 u'status': u'save',
 u'tracking': {u'clicktale': u'',
               u'ecomm360': True,
               u'goal_tracking': False,
               u'google_analytics': u'',
               u'html_clicks': True,
               u'opens': True,
               u'text_clicks': True},
 u'type': u'absplit'}
winning id 16 [u'907b6cf718', u'1159d123cc', u'477d08f842', u'7aa82d7965', u'5e3279e7b9', u'c7b0cfaead', u'8c9e77d3c7', u'fee984c0fa', u'fa8a652bb4', u'418f26dd44', u'c9046d0d40', u'9a0b8878fb', u'9dfe62ed87', u'3b4f0f95dd', u'fc63a6bca7', u'1b1a67d468']
opens 16 [44367, 49238, 44895, 46098, 44641, 45454, 43934, 42220, 41427, 42062, 43949, 36926, 39156, 39704, 36804, 36522]
df length 105
    Winning ID  Opens                                               URLs
0   907b6cf718  44367  {u'http://www.atlasobscura.com/articles/resurr...
1   1159d123cc  49238  {u'http://www.atlasobscura.com/articles/the-bi...
2   477d08f842  44895  {u'http://www.atlasobscura.com/places/gereja-a...
3   7aa82d7965  46098  {u'http://www.atlasobscura.com/places/penn-hil...
4   5e3279e7b9  44641  {u'http://www.atlasobscura.com/articles/how-to...
5   c7b0cfaead  45454  {u'http://www.atlasobscura.com/articles/100-wo...
6   8c9e77d3c7  43934  {u'http://www.atlasobscura.com/articles/step-u...
7   fee984c0fa  42220  {u'https://www.youtube.com/watch?v=0XNyDfoFp-4...
8   fa8a652bb4  41427  {u'http://www.atlasobscura.com/articles/inside...
9   418f26dd44  42062  {u'http://www.atlasobscura.com/articles/100-wo...
10  c9046d0d40  43949  {u'http://www.atlasobscura.com/articles/100-wo...
11  9a0b8878fb  36926  {u'http://www.atlasobscura.com/places/pyramid-...
12  9dfe62ed87  39156  {u'http://www.atlasobscura.com/articles/the-pa...
13  3b4f0f95dd  39704  {u'https://www.youtube.com/channel/UCKnEAhIjPr...
14  fc63a6bca7  36804  {u'http://www.atlasobscura.com/articles/the-be...
15  1b1a67d468  36522  {u'http://www.atlasobscura.com/articles/i-made...
                                                   URL Clicks  Opens  \
0    http://www.atlasobscura.com/articles/resurrect...   2943  44367   
1    http://www.atlasobscura.com/events/obscura-soc...    609  44367   
2    http://www.atlasobscura.com/articles/the-conta...   1824  44367   
3    http://eccentricculinary.com/the-great-sushi-c...    809  44367   
4    http://www.atlasobscura.com/places/vance-creek...    759  44367   
5    http://www.atlasobscura.com/articles/matrimoni...    818  44367   
6    http://www.atlasobscura.com/articles/the-bizar...   1027  49238   
7    http://www.atlasobscura.com/articles/places-yo...    594  49238   
8    http://www.atlasobscura.com/articles/the-almos...   1860  49238   
9    http://www.atlasobscura.com/articles/object-of...     20  49238   
10   http://www.atlasobscura.com/places/gereja-ayam...   1718  44895   
11   http://www.atlasobscura.com/articles/the-secre...   1683  44895   
12   http://www.atlasobscura.com/articles/francisco...   1066  44895   
13   http://www.atlasobscura.com/articles/how-ferri...    880  44895   
14   http://www.atlasobscura.com/articles/inside-to...    901  44895   
15   http://www.atlasobscura.com/places/penn-hills-...    606  46098   
16   http://www.atlasobscura.com/articles/meet-the-...   2212  46098   
17   http://www.atlasobscura.com/articles/exit-inte...    951  46098   
18   http://www.atlasobscura.com/articles/new-yorke...    859  46098   
19   http://www.atlasobscura.com/articles/why-russi...    961  46098   
20   http://www.atlasobscura.com/articles/how-to-es...    830  44641   
21   http://www.atlasobscura.com/articles/the-mind-...     28  44641   
22   http://www.atlasobscura.com/articles/strong-me...     20  44641   
23   http://www.atlasobscura.com/places/blankenese-...   1147  44641   
24   http://www.atlasobscura.com/articles/100-wonde...    343  45454   
25   http://www.atlasobscura.com/articles/i-am-a-no...    210  45454   
26   http://www.atlasobscura.com/articles/theres-an...   2620  45454   
27   http://www.atlasobscura.com/articles/the-great...   3751  45454   
28   http://www.atlasobscura.com/articles/emily-the...   2002  45454   
29   http://www.atlasobscura.com/articles/step-up-2...   2072  43934   
..                                                 ...    ...    ...   
98                                                 NaN    NaN    NaN   
99                                                 NaN    NaN    NaN   
100                                                NaN    NaN    NaN   
101                                                NaN    NaN    NaN   
102                                                NaN    NaN    NaN   
103                                                NaN    NaN    NaN   
104                                                NaN    NaN    NaN   
105                                                NaN    NaN    NaN   
106                                                NaN    NaN    NaN   
107                                                NaN    NaN    NaN   
108                                                NaN    NaN    NaN   
109                                                NaN    NaN    NaN   
110                                                NaN    NaN    NaN   
111                                                NaN    NaN    NaN   
112                                                NaN    NaN    NaN   
113                                                NaN    NaN    NaN   
114                                                NaN    NaN    NaN   
115                                                NaN    NaN    NaN   
116                                                NaN    NaN    NaN   
117                                                NaN    NaN    NaN   
118                                                NaN    NaN    NaN   
119                                                NaN    NaN    NaN   
120                                                NaN    NaN    NaN   
121                                                NaN    NaN    NaN   
122                                                NaN    NaN    NaN   
123                                                NaN    NaN    NaN   
124                                                NaN    NaN    NaN   
125                                                NaN    NaN    NaN   
126                                                NaN    NaN    NaN   
127                                                NaN    NaN    NaN   

    Campaign ID   Click Rate  
0    907b6cf718    0.0663331  
1    907b6cf718    0.0137264  
2    907b6cf718    0.0411116  
3    907b6cf718    0.0182343  
4    907b6cf718    0.0171073  
5    907b6cf718    0.0184371  
6    1159d123cc    0.0208579  
7    1159d123cc    0.0120639  
8    1159d123cc    0.0377757  
9    1159d123cc   0.00040619  
10   477d08f842    0.0382671  
11   477d08f842    0.0374875  
12   477d08f842    0.0237443  
13   477d08f842    0.0196013  
14   477d08f842    0.0200691  
15   7aa82d7965    0.0131459  
16   7aa82d7965    0.0479847  
17   7aa82d7965      0.02063  
18   7aa82d7965    0.0186342  
19   7aa82d7965    0.0208469  
20   5e3279e7b9    0.0185928  
21   5e3279e7b9  0.000627226  
22   5e3279e7b9  0.000448019  
23   5e3279e7b9    0.0256939  
24   c7b0cfaead   0.00754609  
25   c7b0cfaead   0.00462006  
26   c7b0cfaead    0.0576407  
27   c7b0cfaead     0.082523  
28   c7b0cfaead    0.0440445  
29   8c9e77d3c7    0.0471617  
..          ...          ...  
98          NaN          NaN  
99          NaN          NaN  
100         NaN          NaN  
101         NaN          NaN  
102         NaN          NaN  
103         NaN          NaN  
104         NaN          NaN  
105         NaN          NaN  
106         NaN          NaN  
107         NaN          NaN  
108         NaN          NaN  
109         NaN          NaN  
110         NaN          NaN  
111         NaN          NaN  
112         NaN          NaN  
113         NaN          NaN  
114         NaN          NaN  
115         NaN          NaN  
116         NaN          NaN  
117         NaN          NaN  
118         NaN          NaN  
119         NaN          NaN  
120         NaN          NaN  
121         NaN          NaN  
122         NaN          NaN  
123         NaN          NaN  
124         NaN          NaN  
125         NaN          NaN  
126         NaN          NaN  
127         NaN          NaN  

[128 rows x 5 columns]

In [7]:
from bs4 import BeautifulSoup

In [20]:
def get_link_position(campaign):
    endpoint = 'https://us1.api.mailchimp.com/3.0/campaigns/'+campaign+'/content'
    response = requests.get(endpoint, auth=('apikey', apikey)).json()
    soup = BeautifulSoup(response["html"], 'html.parser')
    all_links = []
    link_list = []
    for link in soup.find_all('a'):
        all_links.append(link.get('href'))
    for link in all_links:
        if link not in link_list:
            link_list.append(link)

In [26]:
def get_link_position(campaign):
    endpoint = 'https://us1.api.mailchimp.com/3.0/campaigns/'+campaign+'/content'
    response = requests.get(endpoint, auth=('apikey', apikey)).json()
    soup = BeautifulSoup(response["html"], 'html.parser')
    all_links = []
    link_dict = {}
    index = 0
    for link in soup.find_all('a'):
        if "atlasobscura.com" in link.get('href'):
            all_links.append(link.get('href'))
    for link in all_links:
        if link not in link_dict:
            link_dict[link] = index
            index += 1
    print link_dict
    return link_dict

In [27]:
get_link_position("fee984c0fa")


{u'http://www.atlasobscura.com/events/obscura-society-il-a-festivity-for-the-pharoahs': 5, u'http://www.atlasobscura.com/articles/man-made-animal-crossings-from-bat-bridges-to-toad-tunnels': 2, u'http://www.atlasobscura.com/articles/tiny-skateboards-and-the-fingers-that-ride-them': 1, u'http://www.atlasobscura.com/events/obscura-society-il-a-festivity-for-the-pharoahs?utm_source=Atlas+Obscura&utm_campaign=fee984c0fa-Newsletter_7_17_20157_16_2015&utm_medium=email&utm_term=0_62ba9246c0-fee984c0fa-&ct=t(Newsletter_7_17_20157_16_2015)&mc_cid=fee984c0fa&mc_eid=%5BUNIQID%5D#get-tickets': 6, u'http://www.atlasobscura.com/articles/haunting-photos-of-an-abandoned-air-force-base': 7, u'http://www.atlasobscura.com/articles/the-tycoon-who-planned-his-very-own-island-utopia-in-the-1970s': 4, u'http://www.atlasobscura.com/articles/dark-crystal-the-secrets-of-swarovski': 3, u'mailto:info@atlasobscura.com?subject=Email%20Response': 11, u'http://www.atlasobscura.com/articles/100-wonders-the-walking-sausage-comes-home': 0, u'mailto:eric@atlasobscura.com': 8, u'http://atlasobscura.com': 10, u'http://www.atlasobscura.com/places/new': 9}
Out[27]:
{u'http://atlasobscura.com': 10,
 u'http://www.atlasobscura.com/articles/100-wonders-the-walking-sausage-comes-home': 0,
 u'http://www.atlasobscura.com/articles/dark-crystal-the-secrets-of-swarovski': 3,
 u'http://www.atlasobscura.com/articles/haunting-photos-of-an-abandoned-air-force-base': 7,
 u'http://www.atlasobscura.com/articles/man-made-animal-crossings-from-bat-bridges-to-toad-tunnels': 2,
 u'http://www.atlasobscura.com/articles/the-tycoon-who-planned-his-very-own-island-utopia-in-the-1970s': 4,
 u'http://www.atlasobscura.com/articles/tiny-skateboards-and-the-fingers-that-ride-them': 1,
 u'http://www.atlasobscura.com/events/obscura-society-il-a-festivity-for-the-pharoahs': 5,
 u'http://www.atlasobscura.com/events/obscura-society-il-a-festivity-for-the-pharoahs?utm_source=Atlas+Obscura&utm_campaign=fee984c0fa-Newsletter_7_17_20157_16_2015&utm_medium=email&utm_term=0_62ba9246c0-fee984c0fa-&ct=t(Newsletter_7_17_20157_16_2015)&mc_cid=fee984c0fa&mc_eid=%5BUNIQID%5D#get-tickets': 6,
 u'http://www.atlasobscura.com/places/new': 9,
 u'mailto:eric@atlasobscura.com': 8,
 u'mailto:info@atlasobscura.com?subject=Email%20Response': 11}

In [30]:
df = pd.read_csv("Jan_8_2016 copy.csv")

In [32]:
df.head()


Out[32]:
Title Subject List Send Date Send Weekday Total Recipients Successful Deliveries Soft Bounces Hard Bounces Total Bounces ... New Visits Pages/Visit Bounce Rate Time on Site Goal Conversion Rate Per Visit Goal Value Transactions Ecommerce Conversion Rate Per Visit Value Average Value
0 Newsletter 1/8/2016 | Winner We Are Living in the Golden Age of the Goatee Atlas Obscura 1/8/16 9:59 Friday 166627 166477 122 28 150 ... n/a n/a n/a n/a n/a n/a n/a n/a n/a n/a
1 Newsletter 9/1/2015 (CONTROL) The Zipper Capital, Food Dye History, and Drun... Atlas Obscura 9/1/15 10:00 Tuesday 127427 127164 168 95 263 ... 1129 5.349210823 41.57% 3:44 5.47% $0.00 0 0.00% $0.00 $0.00
2 Newsletter 10/8/2015 | Winner National Park Superfans, Atomic Tourism, and S... Atlas Obscura 10/8/15 10:00 Thursday 121841 121704 93 44 137 ... 1529 4.797832338 45.03% 2:33 4.56% $0.00 0 0.00% $0.00 $0.00
3 Newsletter 10/15/2015 | Winner This Con Man Reinvented Himself as... a Realit... Atlas Obscura 10/15/15 10:00 Thursday 123713 121373 2297 43 2340 ... 4912 1.84489986 41.06% 3:27 1.51% $0.00 0 0.00% $0.00 $0.00
4 Newsletter 12/10/2015 | Winner Vintage Images of Canine Cosmonauts From the USSR Atlas Obscura 12/10/15 10:00 Thursday 161405 161238 92 75 167 ... 3880 2.071352785 39.36% 3:36 1.07% $0.00 0 0.00% $0.00 $0.00

5 rows × 37 columns


In [35]:
df.columns


Out[35]:
Index([u'Title', u'Subject', u'List', u'Send Date', u'Send Weekday',
       u'Total Recipients', u'Successful Deliveries', u'Soft Bounces',
       u'Hard Bounces', u'Total Bounces', u'Times Forwarded',
       u'Forwarded Opens', u'Unique Opens', u'Open Rate', u'Total Opens',
       u'Unique Clicks', u'Click Rate', u'Total Clicks', u'Unsubscribes',
       u'Abuse Complaints', u'Times Liked on Facebook', u'Folder Id',
       u'Unique Id', u'Analytics ROI', u'Campaign Cost', u'Revenue Created',
       u'Visits', u'New Visits', u'Pages/Visit', u'Bounce Rate',
       u'Time on Site', u'Goal Conversion Rate', u'Per Visit Goal Value',
       u'Transactions', u'Ecommerce Conversion Rate', u'Per Visit Value',
       u'Average Value'],
      dtype='object')

In [41]:
df1 = df[["Unique Id","Send Date"]]

In [42]:
df1.head()


Out[42]:
Unique Id Send Date
0 1f6745c60b 1/8/16 9:59
1 b94454dbe6 9/1/15 10:00
2 3678a0f4d8 10/8/15 10:00
3 265ca17dfe 10/15/15 10:00
4 0ab26ec6b2 12/10/15 10:00

In [44]:
df2 = pd.read_csv("mailchimp_stats.csv")

In [45]:
df2.loc[:,"Send Date"] = df1["Send Date"][df1["Unique Id"] == df2["Campaign ID"]]


---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-45-d7e0c660e36c> in <module>()
----> 1 df2.loc[:,"Send Date"] = df1["Send Date"][df1["Unique Id"] == df2["Campaign ID"]]

/Users/Mike/anaconda/lib/python2.7/site-packages/pandas/core/ops.pyc in wrapper(self, other, axis)
    697             name = _maybe_match_name(self, other)
    698             if len(self) != len(other):
--> 699                 raise ValueError('Series lengths must match to compare')
    700             return self._constructor(na_op(self.values, other.values),
    701                                      index=self.index, name=name)

ValueError: Series lengths must match to compare

In [ ]: