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).
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).
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.
Which months & days of week are popular?
Lets look at monthly trends of comments since 2008.
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.
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.
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.
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.
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.
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.
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.
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.
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]
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”
Cool
Wow, that's some analysis you did, awesome.
Congratulations Chandoo... !!
Good to see my name in the top commentators chart.
Your analysis is Kevv Kekaaaa !!
Regards,
Saran
http://www.lostinexcel.blogspot.com
Chandoo proves once again that he is the master of slicing and dicing data. =)
[...] Analyzing 20,000 Comments [...]
This is AWESOME, Cool, !!
In the
Most frequent words in the comments
I would combine the plural pairs like
+ Number/Numbers
+ Formula/Formulas
+ Chart/Charts
+ Value/Values
etc
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.
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.
Hi Chandoo!
Really great Comment O Comment crunching job! Who's taken the pain and perseverance.
Thanks & Continue the 0 0 work:)
K Mohan
fantastic data analysis
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
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!
I too would be very interested in how you did the key word analysis. I note you said you sifted through the list of unique words in a comment, but the actual extraction method would interest me greatly.
Worth noting there is an article on the actual counting of values, but not the generation of the keyword list on the site already:
http://chandoo.org/wp/2009/04/29/analyze-search-keywords-excel/
@Ashok & Steph: I use a small VBA macro to extract unqiue words from the text. I will share the macro & explanation later next week on Chandoo.org.
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
I guess I lost attention when Srini sir was stressing the difference between lose and loose. Fixed it now.
Just to add to the awesomeness quotient: Chandoo, you are awesome. Nice charts too.
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
@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!
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.
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.
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!
thanks for sharing this amazing analysis. I'd also like to tellyou, that your charts are really fantastic.
Wow! What a great analysis. I'm impressed.
Thanks John. I will cherish this comment for sure.
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.
Great job 🙂
Interesting article. Many thanks.
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.
Fascinating !!!
Another fascinating analytics!
Good on you Chandoo.
Wow I really learnt a lot just reading your blog posts...Really awesome...
Shows true power of excel. Lets wait and see what new features are in stores in Excel 2013.
Wonderful! Such a success on sharing the greatest power among the world.
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!!
I am no more a comment virgin .. yay!!!! 🙂
Any insights on geo possible?
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.
Hi Chandoo...
That was mind boggling.... Analysing 20000 comments !!!!...
I was going to fall off my chair...
cant think straight after reading this..
🙂
This is some awesome amount of analysis. It was really very insightful.
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?
*outstanding!!
I'm losing my comment virginity.
Chandoo, you make me a better analyst.
Congrats 🙂 Chandoo!
Its the best place to access excel problems...
Keep up the good work
Awesome!
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. 🙂
Waiting for Jon Peltier to come and blow a mile wide hole in all of this !
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.
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 😀
Sure. Let me note it as a post idea and write some articles explaining the techniques during next few weeks.
Awesome, cool, thank you!
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:):)
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
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.
@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.
@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.
@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.
@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.
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.
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
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.
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
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?
@David K
I believe Chandoo will be doing a series of posts in coming weeks about how he made various components of this post
Here is the recipe in brief.
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/
Man...again you shock the whole community with a great tool!!!
Any chance you will release the xlsm file? its just amazing!
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!! =)
amazing analysis chandoo sir.I am a fan of you and chandoo.org..Keep up the great work.
Awesome analysis!
saved me good time in visiting the trend topics!
great..never seen these kind of in-depth analysis of comments on any blog...very inspirational
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!
awesome...
there, i added another one ;D
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.
@Adwoa
Chandoo does consulting work
Refer: http://chandoo.org/wp/excel-consulting/
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.
@Christina
Using Thesauraus.com I found:
alarming, astonishing, awe-inspiring, awful, beautiful, breathtaking, daunting, dreadful, exalted, fearful, fearsome, formidable, frantic, frightening, grand, hairy, horrible, horrifying, imposing, impressive, intimidating, magnificent, majestic, mean, mind-blowing, moving, nervous, overwhelming, real gone, shocking, something else, striking, stunning, stupefying, terrible, terrifying, wonderful, wondrous, zero cool
I'm not sure how Hairy applies to Amazing ?
Awesome! Look to be part of the next 10k journey too! Thanks.
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.
[...] Analyzing 20,000 comments [...]
Chandoo! Your blog and articles just boost my sensation and while reading I clearly feel of a rushing adrenaline....Thanks
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.
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!
Wow! You are awesome!
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.
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.
Great post Chandoo ! You just encouraged my first comment ! haha ! Keep up the good working for anyone !
Peace