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. 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(“@â€, 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: 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!
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...
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!
I'd always recommend to run this type of advanced analytics on a steam-powered or even horse-drawn computer
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.
