Analyzing 20,000 Comments

Posted on July 19th, 2012 in Charts and Graphs , Cool Infographics & Data Visualizations - 88 comments

On 14th July, evening 4:51 PM (GMT), Chandoo.org received its 20,000th comment. 20,000!
The lucky commenter was Ishav Arora, who chimed, “Like super computers…Excel is a super calculator!!!!” in our recent poll.

It took us 8 years & 15 days since the very first comment to get here. And it took just 1 year 7 months & 23 days to add the last 10,000 comments (we had our 10,000th comment on 21st November, 2010).

Chandoo.org 20,000 Comments - Analysis

Out of curiosity, I wanted to understand more about these 20,000 comments. So I downloaded our comment database, dumped it in Excel and start analyzing.

Understanding the comment growth

Although Chandoo.org has been around since 2004 July, we grew particularly chatty since 2009, when the site started becoming popular. If you look the time from first comment to now & plot total comments by date, this is how it looks. Each 1000 is highlighted (and 5,000s are marked in green).

Comment Growth by date - Chandoo.org comments

While it took us more than 5 years to get to 5,000 comment mark, the next 5k came in less than an year. Now a days, we are adding 114 comments every week.

Here is another chart, showing how many days it took us to get each successive thousand comments.

Days taken for each 1000 comments - Chandoo.org 20,000 comment analysis

Which months & days of week are popular?

Lets look at monthly trends of comments since 2008.

Comment trend by month - Chandoo.org 20,000 comment analysis

As you can see, All the months have seen growth since 2008 (and yoy for most months).

And when it comes to weekdays, Thursdays & Fridays are most popular with Chandoo.org commenters.

Comments by weekday - Chandoo.org 20,000 comment analysis

Who comments on Chandoo.org?

Between Hui & me, we have left 2,650 odd comments on Chandoo.org. The top 10 commenters have left a whopping total of 3,695 comments to date.

Lets look at how many comments are left by first time commenters vs. existing commenters.

Existing commenter is someone who has left a comment earlier with same email id.

# of comments left by first timers vs. repeat commenters  - Chandoo.org 20,000 comment analysis

As you can see, During first 10,000 comments, existing commenters used to rule. Now a days, about 40% comments are from new commenters.

Do newsletter subscribers comment?

We have more than 36,00 odd people tuned in to our newsletter. I wanted to know how many of them leave comments.

Comments by subscribers vs. non-subscribers - Chandoo.org 20,000 comment analysis

About 45% of comments are from Newsletter commenters. About 5% of our newsletter subscribers (2,055 people) actually comment. The rest are happy to read the newsletter and learn.

That means, on average, each newsletter subscriber adds 5 comments (where as non-subscribers add only 2 comments)

How much % of comments are from Top 10 commenters?

In the early days (for first few thousand comments), Top 10 commenters used to contribute 50% of comments. Now a days, their contribution is at 20%. This is because of the huge number of commenters we are adding every month. As our community grew, we have lots of people who are helping each other.

# of comments by Top 10 commenters vs. rest - Chandoo.org 20,000 comment analysis

Top 10 commenters – then & now

Here is how top 10 commenters fared since first 5000 comments. You can see how Hui raised to Top 2 from nowhere & how we lost some of the frequent commenters over time.

Who are the top 10 commenters and how they ranked over time - Chandoo.org 20,000 comment analysis

Where do the comments go?

In early days, comments are always on the latest articles. So if a post is one month old, it is quiet. But now a days, we are adding more comments on older posts than on new ones. Thanks to Google, people are discovering older content more and asking questions (or thanking us) there.

Comments on older posts vs. latest posts - Chandoo.org 20,000 comment analysis

Which posts attract most comments

Next, lets see which posts are most chatty. But looking at # of comments alone is not enough. So I added % of page views (out of total page views on Chandoo.org between a sample period of APR-JUN 2012) and yearly break-up of comments received since 2008. As you can see, some posts are like blips, they get lots of comments and then become quiet. These are often polls, one time messages (like congratulations, happy new year etc.). The other posts consistently attract a lot of comments because they are visited by hundreds of people every week.

PS: You can click on link to see the actual post.

What do the commenters say?

I have an in house metric to see what the commenters say. It is called as Awesomeness Quotient. It is very simple to measure. I check the comment text to see if any of these words are in it.

Love, awesome, wow, !!, great, incredible, super, fantastic, blowing, perfect, excellent

If so, I give the comment 1 point. Else 0 points.

Then, I add up all these points to see how many points we have over the total number of comments.

Comment awesomeness quotient - Chandoo.org 20,000 comment analysis

As you can see, we have been hovering around 45% awesomeness quotient since inception.

PS: If I had a $ every time, someone said cool, I would have 335 cool ones.

Most frequent words in the comments

The most frequent word in our comments is Excel, used 4,650 times. The next frequent word is thank used 4,554 times. I guess that sums up what commenters say nicely.

Here is a list of 64 59 most frequent words (arranged by frequency and alphabetical order).

Note: Each sparkline has its own axis maximum. You cannot compare frequency of one word with another by looking at their heights.

Note 2: If you want this info with same axis maximum for all, click here.

Frequent words in comments - Chandoo.org 20,000 comment analysis

Comments vs. Posts

Here are 2 tag clouds, one for the content in posts & the other for comments. Can you guess which is which?

[click here for larger version]

Text in comments vs. text in posts - wordle word cloud - Chandoo.org 20,000 comment analysis

The left one is for comments.

Interesting Trivia

  • 51% of comments are made with in one week after an article is published.
  • We add 4% more in 2nd week, 4% more in next 2 weeks. That is, only 59% of comments are made with in one month of writing an article.
  • We get 70% comments between 8AM-8PM (GMT). The busiest hours for commenting are 1PM & 5PM GMT
  • Since 1st Jan 2010,
    • We had 7 quiet days (days with 0 comments).
    • And on 4 days, we received 100 or more comments
    • We got 17 comments on Christmas & New year days
  • The longest comment was 11,274 characters long by Ronald on 2nd June, 2010.
  • There are 5 comments with more than 5,000 characters long.
  • For every legitimate comment, we get 20 spam comments. So since Jan 2008, our spam filters have blocked 417,104 spam comments.

How these charts are made?

At least 5 cups of coffee, 2 hours of thinking, several hours of SQL, VBA, Pivot & SUMIFS, an hour of formatting & conditional formatting and may be 10 minutes on Wordle.net.

I am unable to share the actual Excel file with you as there is lots of sensitive data (email addresses, IPs etc.) and the file is too heavy – 30 MB at last count.

Do you comment on Chandoo.org?

If you have never left a comment, now is your time. Go ahead and lose your comment virginity. It feels awesome to share your thoughts with rest of us.

And if you are a commenter, well, you have my love & good thoughts. Go ahead and say something more. You know I am all ears to hear what you say.
Go ahead and leave a comment. Next stop, 30k.

Thank you

Thank you so much for taking time to learn from Chandoo.org. Special thanks to 7,278 of you who left a comment on Chandoo.org ever.

88 Responses to “Analyzing 20,000 Comments”

  1. Sawan says:

    Wow, that's some analysis you did, awesome.

  2. Saran says:

    Congratulations Chandoo... !!

    Good to see my name in the top commentators chart.

    Your analysis is Kevv Kekaaaa !!

    Regards,
    Saran
    http://www.lostinexcel.blogspot.com

  3. Luke M says:

    Chandoo proves once again that he is the master of slicing and dicing data. =)

  4. Vijay Sharma says:

    This is AWESOME, Cool, !!

  5. Hui says:

    In the
    Most frequent words in the comments
    I would combine the plural pairs like
    + Number/Numbers
    + Formula/Formulas
    + Chart/Charts
    + Value/Values
    etc

    • Chandoo says:

      This is fixed now. There were more than 45,000 unique words in all comments. I had to manually sift thru the top 300 most frequent list and identify the ones to show here (as some of the most frequent words are everyday words like a, the, in, on etc.). So I missed cleaning some of the plurals.

  6. Jong says:

    Wow!! I love this awesome post. I think this is a perfect example of the incredible analysis and fantastic insight you provide on this great website. You are an excel super hero. Keep up the excellent work.
    Uhh... blowing.

  7. Hi Chandoo!
    Really great Comment O Comment crunching job! Who's taken the pain and perseverance.

    Thanks & Continue the 0 0 work:)
    K Mohan 

  8. kdibai says:

    fantastic data analysis

  9. Joanne Forsythe says:

    I have just recently started visiting your site.  I love your e-mail updates.  This site is awesome!  I hope that I can save up some cash to attend one of your training classes so I can become an expert like you.  I love Excel.

    Joanne

  10. Ashok says:

    Hi Chandoo,
    How to find frequency of keywords used in comments? Have you used Excel for the same purpose? Can you please explain the same. Also, If it is doable in excel, can we do it for phrases as well?
    Curious to know as this sounds really interesting thing to do in excel!

  11. Saswata says:

    Hey Chandoo!
    Since you told us to "loose" our comment virginity, I couldn't help but be the Grammar troll here 🙂
    Please edit it to "lose". Let more Indian brothers and sisters correct this one mistake they make 99% times LOL
    Great work Machan!!
    Cheers,
    Sas 

  12. Just to add to the awesomeness quotient: Chandoo, you are awesome. Nice charts too. 

  13. Jae says:

    Chandoo -

    Often I check the website - and prepare to comment, only to see that someone else has beaten me to the punch ... 

    And when it comes to questions - when I read the explanations through - any question I had, has been answered.

    I can't tell you how often you and your team have helped me!

    -- Jae 

  14. SirJB7 says:

    @Chandoo
    Hi!
    Congratulations! ... 1st. 20K, or 2nd. 10K, or 40% of 50K,...
    Keep on going for nexts... whatever unit you choose... but let Excel rest for a while and do the calculations manually 🙂
    My best wishes!

  15. Jeff Weir says:

    Nice post, but raises privacy issues because the "Top Commenters" heatmap shows our email addresses - which you promise will not be pubsished - instead of our names - which are public. While the full address is not shown, it's easy to work out that @h means hotmail, and @g means gmail.
    Can you edit this so that names show instead of email addresses?
     
    Thanks Chandoo.

    • Chandoo says:

      Sorry. I did not realize this. I removed email ids & used names. Also this changed the numbers (as people used different email IDs, but same name and vice-a-versa)

       

      And I added top 30 posts by #comments they get too.

  16. Jeff Weir says:

    Also would have been interesting to see the top 20 most commented articles, and how many of them were arguments over whether charting techniques are good or bad!
     
    Also, rank people by the length of comments (excluding code). That way I'd get ahead of Jon for sure!
     

  17. stefan says:

    thanks for sharing this amazing analysis. I'd also like to tellyou, that your charts are really fantastic.

  18. Wow! What a great analysis. I'm impressed.

  19. Sean L. says:

    Two thoughts... 

    The thing I found most interesting is how the rate of comments has flattened out. (And what was happening in the world or on this site between those 13th, 14th and 15th thousand comments to make the rate slow, then pick up to the current norm?)

    And, @Jeff... If you hadn't mentioned it, I would never have noticed. 

  20. vietnam rum says:

    Interesting article. Many thanks.

  21. Kiev says:

    I love your post although do not comment very often, but it really help me a lot, your way of thinging and processing is amazing. thank you.

  22. Paresh Shah says:

    Fascinating !!!

  23. Krishna Mysore says:

    Another fascinating analytics!
    Good on you Chandoo.  

  24. Arun David says:

    Wow I really learnt a lot just reading your blog posts...Really awesome...
     

  25. Mohak says:

    Shows true power of excel. Lets wait and see what new features are in stores in Excel 2013.

  26. Elmer says:

    Wonderful! Such a success on sharing the greatest power among the world.

  27. It's pleasing to see that 'thank' ranks highly; it's just a shame that 'please' doesn't score more!
    Come on Chandoo'ers let's ask questions politely!
    And 'My Bad' is terrible use of the English language too!
    Other than that...AWESOME!!   

  28. Pradeep says:

    I am no more a comment virgin .. yay!!!! 🙂

  29. Pradeep says:

    Any insights on geo possible?

  30. Brian says:

    Great charts and interesting analysis but not sure if the frequent words chart really tells us anything. I think it would be more useful to have some sort of analysis on the type of issues that concern excel users i.e. what is giving them trouble, features they like and those they don't like etc. You would probably have to initiate some sort of polling feature or questionnaire. But great charts nevertheless. Thanks for sharing.

  31. Anil Managoli says:

    Hi Chandoo...
    That was mind boggling.... Analysing 20000 comments !!!!...
    I was going to fall off my chair...
    cant think straight after reading this..
    🙂

  32. Prateek says:

    This is some awesome amount of analysis. It was really very insightful.

  33. Cristian says:

    I'm losing my comment virginity and, also, becoming part of the 5% who are suscribed to newsletter and comment on chandoo.org. Thanks a lot, your effort is outstanding?

  34. Mike the Analist says:

    I'm losing my comment virginity.

    Chandoo, you make me a better analyst.

  35. Dinesh says:

    Congrats 🙂 Chandoo!

    Its the best place to access excel problems...
    Keep up the good work

  36. Mauro says:

    Awesome!

  37. Hey, what happened?! J Before the update, my email was number seven on the list as a top commenter. Now, after the update, I’m nowhere to be found! Oh well, that previous rank did seem high to me. I’ll just make up for it by commenting more. 🙂 

  38. Waiting for Jon Peltier to come and blow a mile wide hole in all of this !

    • Chandoo says:

      Lets not be sarcastic. I am glad Jon feels passionate about good charting and tries to share his wisdom & opinions with all of us so kindly. I would be delighted to hear from him if there are any mistakes in my charts. He is my first real Excel guru and I continue to learn from him every now and then.

  39. rom says:

    This is bloody brilliant.I am reading this post time & again from the day it was posted.
    Chandoo would you like to do a week series only aroud the techniques used to come up with these meanigful graphical representations

    Pleeeease. You'll get soo many donuts from soo many people 😀

  40. Lyn says:

    Awesome, cool, thank you!

  41. Vivek says:

    Hi Chandoo,

    Excellent article...I was just wondering,if trying to find a correlation between the number of posts and overall comments over a specific period of time would give us more insights.
    As people reading one post might go to another post and end up commenting on it.

    Its just a thought. 

    Thanks.

    P.S.:This is my first comment and hope to make more:):)  

  42. SomeintPhia says:

    Dear Chandoo,

    well done, a "simple" analyse created a lot of interesting news, thank you for your work; it is always a pleasure to see what you are doing with simple things.

    Keep on analysing, kind regards, SomeintPhia    

  43. Pedro Wave says:

    I am amazed at the position 14, tied with Daniel Ferry nothing less! Of what I have to make this comment to raise my rank up.
    @Chandoo, to add something constructive in this comment, I love the way of linking your posts with more comments with the help of MS ExcelWebApp, but will force you to upgrade each day because raises the number of comments of the most read posts of your blog.
    Sure one of these days I'm using this app  to publish my own statistics when my blog has more comments.

    • Chandoo says:

      @Pedro... I found MS Excel Web app quite interesting and powerful, although slow & faulty. I wish MS would add few more servers behind it so that we can load it quickly and use it often.

      PS: I am not planning to update this sheet frequently. Rather, I will keep it as a snapshot and revise when we hit 50,000 comment mark or something like that.

      • Pedro Wave says:

        @Chandoo, MS ExcelWepApp has serious limitations: only allows sheets without protection, comments, forms, control forms, ActiveX Controls, XML, OLE, DDE, data validation, PivotTables and even macros VBA are stripped-down, that it's worse.

        I would like to share this file but has all the above, except macros, and it is built with iterative formulas that either supports Excel Web Access!

        https://skydrive.live.com/?id=6B219F16DA7128E3!315&cid=6b219f16da7128e3#  

        Not try to see it into the explorer, download it: Beta Chess Game

        Only made with formulas exclusively here in gratitude and admiration for your blog, Chandoo. 

        • Chandoo says:

          @You are right. But I guess, it would be too much to expect web apps to support Active x, form contrls, xml etc.

          I really loved your Chess game viewer. Very clever 🙂 I am really thankful for your support to Chandoo.org and the passion you show to share your Excel knowledge with all of us.

          • Pedro Wave says:

            @Chandoo, thank you very much for your compliments to my chess viewer, but the link I shared in my previous comment is a new chess game made only with formulas and only one iteration that I'm developing now. Have you tried it?
             
            It would be too much to ask you to do a comment on my blog?
            Thanks in advance for take your time off testing my spreadsheets, knowing that time is one of the most precious values we have in this life.
            Signed by a fan of your brilliant ideas and your great facility to expose them. 

          • Chandoo says:

            I checked your newest version only (the one with radio buttons to track movements). Very brilliant as I said.

            I will post a comment on your blog too 🙂 Sorry, but with so many comments and forum posts here, I find it hard to post a comment elsewhere.

  44. Hui... says:

    RE: Where do the comments go?

    In addition to the Google Search Box,

    1. there have been a number of posts written in the past two years which are like indexes or catalogs linking back to previous works

    2. A lot of new posts link back to or reference previous works

    3. I know I refer a lot of question responses back to existing posts both here and in the Forums

     

    • Chandoo says:

      Out of total comments, about 12% are track backs. Not all links to previous articles generate trackbacks (as wordpress seems to ignore few trackbacks). And usually forum links do not generate trackbacks.

      The good thing is, over the years, we have been linked by many websites outside chandoo.org and that leads to more trackback / pingback comments.

    • Hui says:

      I should have explained that I was referring to the fact that we now link back to a lot of old posts and so people have exposure via a number of sources to all the old posts and hence these are available to comment on, where as 2 or 3 years ago there weren't any old posts, they were all new.

      Also the readership has gone from practically nil to about 60,000 hits per day
      This brings a whole new generation of readers who can use the above tools to explore older posts  

  45. David K says:

    Can somebody point me to the tutorial that Chandoo used for the "Journey to 20,000" chart? (if he has one for it?)
     
    If not, Chandoo can you give a few hints?  Am I on the right track with multiple axises, Pivot Table Sum of Running total?

    • Hui says:

      @David K

      I believe Chandoo will be doing a series of posts in coming weeks about how he made various components of this post

    • Chandoo says:

      Here is the recipe in brief.

      • I have collected cumulative totals by month since September 2008 by using SUMIFS formula.
      • Then, I created a regular line chart with circle markers and formatted it.
      • Then, I formatted the 5k points manually to have bigger circles with green in them.
      • Changed vertical axis number formatting to #,"k"
      • Added text box to the chart and manually typed the years 2008, 2009, 2010, 2011, 2012 in that. Added extra spaces in between years so that it looks like the years appear beneath the months at correct positions.
      • Added another text box to show messages ... "First 5k .." and moved it somewhere inside the chart.
      • Formatted gridlines, axis lines etc to make them subtle.

      As you can see this is done manually as the data is static. If you want a dynamic chart with both year and month, you can use grouped axis labels. See this for more: http://chandoo.org/wp/2010/11/17/show-months-years-in-charts/

  46. Rodrigo says:

    Man...again you shock the whole community with a great tool!!!
    Any chance you will release the xlsm file? its just amazing! 

  47. sprlvlyldy says:

    Congrats! You all are awesome and have been such a great help! I have learned so much just from all the posts, articles, questions...
    The analysis looks fantastic and I love this site!!  =)

  48. lokesh says:

    amazing analysis chandoo sir.I am a fan of you and chandoo.org..Keep up the great work.

  49. Affonso says:

    Awesome analysis!
    saved me good time in visiting the trend topics!

  50. SD says:

    great..never seen these kind of in-depth analysis of comments on any blog...very inspirational

  51. Meganb says:

    CHANDOO! You have fans in Cape Town, South Africa too 🙂

    your newsletters and videos are userfriendly and fun... the dashboards are particularly useful for our reporting and we look forward to every installment!

    Keep going and thanks so much!
     

  52. xsaed says:

    awesome...
    there, i added another one ;D 

  53. Adwoa says:

    That was some analysis. I am amazed you did all of that in excel!! How can I hire you to slice and dice my data 🙂 I'm an excel newbie. 

  54. Christina Hendricks says:

    I cannot believe how amazingly awesome and super cool this site is. Really. It's not only hugely helpful, but also very funny! I am learning more and more and someday hope to become awesome in excel. The site, therefore, is working (at least for me and the other thousands and thousands of people who comment on its awesomeness). Must think of some other synonyms for awesome.

  55. Vipul says:

    Awesome! Look to be part of the next 10k journey too! Thanks. 

  56. May says:

    Hi Chandoo,

    Great site. Thanks for all the tips.

    Could you please share how you generated the list of keywords please? I'm using it for a work project analysis. It would come in very handy.

    Thank you.

  57. Arunava says:

    Chandoo! Your blog and articles just boost my sensation and while reading I clearly feel of a rushing adrenaline....Thanks
     

  58. Paleo Diet says:

    Awesome. Thank you for taking the time. I'll definitely come to your site to find out more and inform my friends about your writing.

  59. You are truly a excellent webmaster. The web site loading pace is amazing. It sort of feels that you're doing any unique trick. Also, The contents are masterwork. you've done a magnificent activity in this topic!

  60. mark says:

    Wow! You are awesome!

  61. here says:

    It's actually a nice and useful piece of information. I am satisfied that you just shared this useful info with us. Please keep us up to date like this. Thank you for sharing.

  62. homepage says:

    Woah this blog site is excellent i enjoy reading through your content regularly. Stay inside the great! You are aware of, most people want rounded due to this facts, it is possible to guide these people greatly.

  63. Stephen Silveira says:

    Great post Chandoo ! You just encouraged my first comment ! haha ! Keep up the good working for anyone !
    Peace