{"id":146,"date":"2018-09-04T01:06:17","date_gmt":"2018-09-04T01:06:17","guid":{"rendered":"http:\/\/thegreenbrain.com.au\/blog\/?p=146"},"modified":"2018-09-27T03:53:27","modified_gmt":"2018-09-27T03:53:27","slug":"using-python-pandas-for-marketing-data-analysis-in-excel","status":"publish","type":"post","link":"https:\/\/thegreenbrain.com.au\/blog\/using-python-pandas-for-marketing-data-analysis-in-excel\/","title":{"rendered":"Using Python Pandas for Marketing Data Analysis in Excel"},"content":{"rendered":"

If you are like me who have been working on data analysis using good old Excel, it’s been getting more difficult as the data size grow. Common tasks like vlookup, filtering and chart plotting become slower to process and hard to use. This is why I turned to Pandas to perform the common tasks in excel and a lot more..<\/p>\n

I’m writing this article to show the codes I used to perform the common tasks in email marketing data analysis. Here, I use soft bounce analysis as an example.<\/p>\n

If you are first timer to Python, please read this article on how to install Python and Pandas on your computer<\/a>.<\/p>\n

Common excel tasks<\/p>\n

    \n
  1. Read data<\/li>\n
  2. Group and count data by shared row<\/li>\n
  3. VLookup<\/li>\n
  4. Filter<\/li>\n
  5. Deduplicate<\/li>\n
  6. Calculation<\/li>\n
  7. Build Graph<\/li>\n<\/ol>\n

    1.Reading an Excel file in Python using Pandas<\/strong><\/p>\n

    To read the excel data, we need to first load the excel file in python using pandas<\/p>\n

    #import the pandas library and aliasing as pd
    \nimport pandas as pd<\/p>\n

    #read log data from excel file
    \nfilename = ‘APJC-FY18-Bounce history.xlsx’
    \ndf = pd.read_excel(filename)<\/p>\n

    xlrd module does not come with Pandas library<\/p>\n

    Make sure you install the module by running
    \npip install xlrd<\/code><\/p>\n

    You will receive the exception below when you try to read an excel file if it’s not installed
    \nImportError: No module named 'xlrd'<\/code><\/p>\n

    Here’s the sample data of bounceback histrory log<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
    Email Address<\/th>\nSMTP Error Code<\/th>\nSMTP Reply Code<\/th>\nBounceback Message<\/th>\nBounceback Date\/Time<\/th>\n<\/tr>\n<\/thead>\n
    alex@test.com<\/td>\n5.4.14<\/td>\n554<\/td>\nsmtp; 5.3.0 – Other mail system problem 554-‘5.4.14 Hop count exceeded<\/td>\n2\/18\/2018 16:01<\/td>\n<\/tr>\n
    alex@test.com<\/td>\n5.4.14<\/td>\n554<\/td>\nsmtp; 5.3.0 – Other mail system problem 554-‘5.4.14 Hop count exceeded<\/td>\n3\/18\/2018 17:04<\/td>\n<\/tr>\n
    alex@test.com<\/td>\n5.4.14<\/td>\n554<\/td>\nsmtp; 5.3.0 – Other mail system problem 554-‘5.4.14 Hop count exceeded<\/td>\n2\/14\/2018 16:10<\/td>\n<\/tr>\n
    bead@test.com<\/td>\n5.4.1<\/td>\n550<\/td>\nreply smtp; 5.1.0 – Unknown address error 550-‘5.4.1 <\/td>\n7\/24\/2018 20:00<\/td>\n<\/tr>\n
    gemma@bc.com<\/td>\n4.4.0<\/td>\n421<\/td>\n[internal] no MXs for this domain could be reached at this time<\/td>\n7\/24\/2018 21:01<\/td>\n<\/tr>\n
    gemma@bc.com<\/td>\n4.4.0<\/td>\n421<\/td>\n[internal] no MXs for this domain could be reached at this time<\/td>\n7\/24\/2018 21:01<\/td>\n<\/tr>\n
    gemma@bc.com<\/td>\n4.4.0<\/td>\n421<\/td>\n[internal] no MXs for this domain could be reached at this time<\/td>\n2\/11\/2018 16:01<\/td>\n<\/tr>\n
    abc@tnb.com<\/td>\n4.4.0<\/td>\n421<\/td>\n[internal] no MXs for this domain could be reached at this time<\/td>\n6\/7\/2018 20:02<\/td>\n<\/tr>\n
    patrick@itoc.com<\/td>\n4.4.0<\/td>\n421<\/td>\n[internal] no MXs for this domain could be reached at this time<\/td>\n7\/24\/2018 21:01<\/td>\n<\/tr>\n
    patrick@itoc.com<\/td>\n5.4.14<\/td>\n554<\/td>\nsmtp; 5.3.0 – Other mail system problem 554-‘5.4.14 Hop count exceeded<\/td>\n7\/18\/2018 23:01<\/td>\n<\/tr>\n
    patrick@itoc.com<\/td>\n5.4.14<\/td>\n554<\/td>\nsmtp; 5.3.0 – Other mail system problem 554-‘5.4.14 Hop count exceeded<\/td>\n4\/8\/2018 17:01<\/td>\n<\/tr>\n
    abc@tnb.com<\/td>\n4.4.0<\/td>\n421<\/td>\n[internal] no MXs for this domain could be reached at this time<\/td>\n4\/8\/2018 17:01<\/td>\n<\/tr>\n
    abcd@tnb.com<\/td>\n4.4.0<\/td>\n421<\/td>\n[internal] no MXs for this domain could be reached at this time<\/td>\n4\/8\/2018 17:01<\/td>\n<\/tr>\n
    abcd@tnb.com<\/td>\n4.4.0<\/td>\n421<\/td>\n[internal] no MXs for this domain could be reached at this time<\/td>\n4\/8\/2018 17:01<\/td>\n<\/tr>\n
    …<\/td>\n…<\/td>\n..<\/td>\n..<\/td>\n..<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    What I tried to accomplish<\/strong><\/em><\/p>\n

      \n
    1. Read the data in excel<\/li>\n
    2. Group and count the number of different soft bounces based on their SMTP reply Code for each contact (based on their email addresses). Go to 2<\/li>\n
    3. Select only the SMTP reply code with value of 421 and number of bounces are 3 or more from the result above. Go to 3<\/li>\n
    4. Use the result at 2. and Vlookup on the original table to get the matches. Go to 4<\/li>\n
    5. Deduplicate the same contacts (email addresses) and get the latest bounceback date. Go to 5<\/li>\n<\/ol>\n

      https:\/\/gist.github.com\/miclow\/c7c5c93fcb09abf0c3c4bc2e0b5923b1<\/p>\n

      2. Group and count data by shared row<\/strong><\/p>\n

      Here I wanted to group and count the number of occurrence based on their Email Address and SMTP Reply Code.<\/strong> The new column for the count was renamed as Count of Bounces<\/strong>.<\/p>\n

      Line 9 df1 = df.groupby(['Email Address','SMTP Reply Code'], as_index=False).size().reset_index(name='Count of Bounces')<\/code><\/p>\n

      3. Filter the data<\/strong><\/p>\n

      One of the common excel tasks is Data Filtering<\/strong>.
      \nIt’s super easy to filter and select a subset of data based on certain criteria.<\/p>\n

      At line 12, I selected only rows with SMTP Reply Code equals to 421
      \nAt line 15, I selected only rows with Count of Bounces equals or more than 3 from the results at line 12<\/p>\n

      Line 12 df2 = df1.loc[df1['SMTP Reply Code'] == 421]<\/code>
      \nLine 15 df3 = df2.loc[df2['Count of Bounces'] >=3 ]<\/code><\/p>\n

      4. VLookup<\/strong><\/p>\n

      Another common excel task is VLookup<\/strong> by comparing two excel files\/table based on shared values<\/p>\n

      In this example, I Vlookup on table df (the original table) using table df3 to get all data of shared Email Address<\/strong><\/p>\n

      Line 18 df4 = pd.merge(df, df3, on=['Email Address'])<\/code><\/p>\n

      There are 4 different merge options:<\/p>\n

      \u2018left\u2019\u200a\u2014\u200aUse the shared column from the left dataframe and match to right dataframe. Fill in any N\/A as NaN<\/p>\n

      \u2018right\u2019\u200a\u2014\u200aUse the shared column from the right dataframe and match to left dataframe. Fill in any N\/A as NaN<\/p>\n

      \u2018inner\u2019\u200a\u2014\u200aOnly show data where the two shared columns overlap. Default method.<\/p>\n

      \u2018outer\u2019\u200a\u2014\u200aReturn all records when there is a match in either left or right dataframe.<\/p>\n

      5. Deduplicate<\/strong><\/p>\n

      Lastly, I wanted to remove the duplicates and keep only the unique Email Address <\/strong>.
      \nSince the bounceback date was in ascending order, keep=\"last\"<\/code> was used to keep the last row after deduplication. Deduplication will by default keeps the first row if not specified.<\/p>\n

      Line 21 df5 = df4.drop_duplicates(subset='Email Address', keep=\"last\")<\/code><\/p>\n

      6. Calculation<\/strong><\/p>\n

      7. Plotting Chart<\/strong><\/p>\n

      Problems with plotting chart in Excel is that it’s slow and inflexible. Sometimes, it’s really hard to translate the visual in your head into Excel.<\/p>\n

      Pandas allows you to build chart easily.<\/p>\n","protected":false},"excerpt":{"rendered":"

      If you are like me who have been working on data analysis using good old Excel, it’s been getting more difficult as the data size grow. Common tasks like vlookup, filtering and chart plotting become slower to process and hard to use. This is why I turned to Pandas to perform the common tasks in…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-146","post","type-post","status-publish","format-standard","hentry","category-data-analysis"],"_links":{"self":[{"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/posts\/146","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/comments?post=146"}],"version-history":[{"count":51,"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/posts\/146\/revisions"}],"predecessor-version":[{"id":218,"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/posts\/146\/revisions\/218"}],"wp:attachment":[{"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/media?parent=146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/categories?post=146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thegreenbrain.com.au\/blog\/wp-json\/wp\/v2\/tags?post=146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}