Use a Spreadsheet to Look at Your Email List Demographics

Discussion in 'Noob Central' started by PushSend, Aug 23, 2012.

  1. PushSend

    PushSend VIP

    Joined:
    Apr 12, 2011
    Messages:
    1,927
    Likes Received:
    141
    Trophy Points:
    63
    Location:
    Paradise
    Ever wonder who’s on your mailing list and where they’re coming from? While many email service providers (including WhatCounts) offer sophisticated analysis tools, sometimes a simple spreadsheet is all that’s needed. Today, let’s look at how to see your list broken down by domains.
    First, you’ll need a list and a spreadsheet. Click here to see an example Google Doc, and click the File > Make a Copy menu item if you’d like to borrow this for your own use. This works with Microsoft Excel as well; we’re using Google Docs because it’s the least expensive option available to nearly everyone.
    [​IMG]
    In column 1, you’ll need to put in your mailing list.
    In column 2, we’ve set up a formula that looks at column 1 and truncates everything before the @ sign. The formula looks like this:
    =MID(A2, FIND([email protected], A2) + 1, 255)
    In column 3, we’re using a summary function that says, show every unique value in column 2:
    =unique(B:B)
    Finally, in column 4, we’re using the COUNTIF function, showing the count of each value from columns 3 and 2:
    =countif(B:B,C2)
    What does this level of spreadsheet trickery get you? Reports like this:
    [​IMG]
    From this kind of spreadsheet summary, you can get a very good idea of which domains and service providers your users are coming from. For example, note that in this excerpt from one of my mailing lists, there are people coming from Canada (line 15), the UK, (line 19), and even France (line 25). This tells me that my email list subscribers are an international audience, and thus I should be sure to provide content relevant to more than just one nation’s audience.
    I also know that a significant portion of people use GMail, which means I need to alter my email formatting of my design to work well with the GMail platform.
    What I find truly interesting about this sample is that the list of people is a business crowd, but so many people are subscribed with “home” email accounts, domains like GMail, Hotmail, or Yahoo. That tells me that segmentations which leap to the conclusion that webmail providers are not business accounts is, for this list, incorrect. A deeper dive into the data indicates that approximately 10% of those GMail addresses are C-level executives using their home addresses to subscribe to this list rather than their corporate address. One obvious takeaway is that if your web forms have “smart logic” that rejects webmail accounts, you could be missing out on some very valuable prospects!
    Try out the spreadsheet for yourself to see how the logic works, then incorporate it into your own email marketing analysis!
     
  2. PushSend

    PushSend VIP

    Joined:
    Apr 12, 2011
    Messages:
    1,927
    Likes Received:
    141
    Trophy Points:
    63
    Location:
    Paradise
    Excell/csv supports up to 1MM lines....which is why I posted this in the noob section for those guys with small DBs and trying to do it on a shoestring budget.

    But it never fails your replies are usually self serving promotions for your system. C'mon SG...
     
  3. roundabout

    roundabout VIP

    Joined:
    Feb 17, 2011
    Messages:
    2,713
    Likes Received:
    154
    Trophy Points:
    63
    The only thing self-serving about Push's Costa Rican Retreat is the fact he is looking forward to dressing as Rambo with his giant knife and slaughtering a pig for the roast... he's guilty as charged!
     
  4. DKPMO

    DKPMO VIP

    Joined:
    Mar 31, 2011
    Messages:
    1,452
    Likes Received:
    68
    Trophy Points:
    48
    Location:
    Elaborate Underground Base
    I'd always recommend to run this type of advanced analytics on a steam-powered or even horse-drawn computer

    [​IMG]
     
  5. sjinks

    sjinks VIP

    Joined:
    Apr 27, 2011
    Messages:
    301
    Likes Received:
    31
    Trophy Points:
    28
    Location:
    Sevastopol, Ukraine
    Home Page:
    Personally I find the CLI interface is faster and easier.

    Unique domains:
    Code:
    awk -F@ '{print $2}' emails.txt | sort -u
    
    Number of emails by domain:
    Code:
    awk -F@ '{print $2}' emails.txt | sort | uniq -c | sort -n
    
    Sort by domain and local part:
    Code:
    awk -F@ '{print $2, $1}' emails.txt | sort | awk '{print $2"@"$1 }'
    
    etc.
     
  6. WebbulaTech

    WebbulaTech New Member

    Joined:
    Sep 11, 2012
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    I'm with you SJ. Terminal is the way to go hands down.
     

Share This Page