Voting habits of the top 200 Whales - SteemitSQL AnalysissteemCreated with Sketch.

in #steemit4 years ago

Recently I was asked by @bisade on Discord

'Is it possible to get data on how active whales are in voting and commenting on the Steemit platform?'

So lets dive into the details of how and what I have found out

First of all I got a list of Whales from  I have use Power BI to connect to the website and pull down the table.  For the sample data I have taken the first 200 whales.

Next I used Power BI to connect to SteemitSQL where I pulled down the data from TXVotes and Comment.  

Due to the large amounts of data and the limitations of PowerBI Desktop I have only taken data for the last two week for votes, and three weeks for comments. (I have taken the extra week for comments because of the 7 day pay out thingy…)

Next I merge these TX Votes and Comment tables together using the permalink as the common field of data.  This data is probably already merge in one of the tables, but look I am new here so I took the difficult route to get there lol…. If there is a table, please do tell me….

So what have I found out about the voting habits of whales?

From 15 July to 30 July @21:00 gmt there were in total 5,423,382 votes made on the platform.  These votes were made for both Posts and Comments on Posts.  These votes were made up from 52,750 distinct voters on 37,579 different Authors posts.

On Average over the time period taken (15 days) each voter voted 6.85 times per day.

Filtering this down to the top 200 whales.

141,439 votes were made by 124 whales.  That makes up 2% of the votes by less than .25% of the number of Voters. Averaging out at 76 votes each per day, which is well above the average.  These votes were spread across 11,376 different authors.

Here is the top 15 of the top 200 whales for number of votes given


Out of the votes made by Whales, I looked at how many of these votes were self-votes.  A whales vote is worth quite a lot, especially if there are in the top 200 whales.  

49 Whales did not self-vote at all. 57 Whales self-voted between 1-20 times. In total there were 1863 self-votes made by whales, thats only 1.3% of all votes made by the 200 top whales. Here is a table with the top 15 from the top 20 whales guilty of self-voting.

Voting for Other Whales

A lot of minnows are under the impression that Whales just vote for whales.  Well my analysis tells a different story.  Only 2.25% of votes made by the top 200 whales are for other Whales also in the top 200.

This tables shows the whales with the largest % of votes that went to other whales included in this analysis

What do Whales like to vote for and when?

In total the top 200 whales voted on 17330 tag.  Some are obviously way more popular than others.  Have a look at the graph below for more details.

Comparing this to overall

To my surprise the weekend are the most popular times for whale voting


Whereas overall on Steemit the most popular day is actually Saturday

And the most popular voting times are 5am and 13pm

Whereas overall the most popular time is 5pm

Finally I looked to see if whales tend to post more on Posts or Comments.

Of the 141,439 votes made by these whales, 118,688 were votes made directly on blog posts. That's 84%.  The rest (16%) of the votes were made on comments on posts.

Unfortunately I am not able to embed a Power BI interactive dashboard into Steemit.  But with the dashboard I have created I can drill down to each Whale and see exactly who and when their votes went to.  In fact the data would allow me predict who the whales might vote for so I can vote just before them….. 

For example I have selected Honeybeee in the table of data and all other reports have updated.  I can see that Honeybeee only votes on posts, not comments and 243 votes were on 25 Authors, @barrydutton, @papa-pepper and so on……..

Each one of the visualization, when clicked will drill down to the other charts, so I could also select an Author and see which whales votes for them.

This is a really awesome dashboard that gives me real actionable insights….If you want a copy, well we can swap. I will give you a copy for Steem?????

And this is only the Voting habits’……we will look at commenting in a different post!

If you liked this post and found it of use, I hope that you will resteem, Vote up and Comment....


Great job - interesting data. :)

Great info as always Paula. Im glad to see that the Whales for the most part are quite honorable. I do have one question. In the section where you discuss popular voting times are you basing this on UTC or GMT?

I need to find out what time zone the server is at and I will come back to you on that one......

@ammonite @paulag Pretty sure it is PST.

I really loved your analysis - hope you do more in the future.

o yeah please did you find the time zone, I appreciate this your post quite a lot, really helpful, something different from all the shitposts I've been seeing.

the time zone is UTC

As I was really busy the whole month of July, I barely looked at my steemit feed... (Holiday, festivals, family, work, ... )... But now I finally took a look and I see this post! I do remember why I followed you in the first place! This is again a great post, with a very interesting topic, you took your time, did your research, and did write a nice text next to the facts and graphics! I love it, it's worth a 100% upvote and a resteem 😉 ! Keep up the good work!

awesome feedback, thank you

This is a really interesting analysis. I guess my self voting is higher than the whales but the whales do have more inc drive to direct votes to where it will help he platform more. The 16 percent comment vote is also a little higher than I thought it would be.

Thanks for another very useful report!

Fantastic information paula, I am looking at learning analytics software such as tableau and mongodb/sql to further my career so this is really fascinating. When you reference times, are these times in UTC?

yes it is UTC

I am blown away by how sophisticated your skills are. You should adopt me as your protege :)

adoption granted......we will work on some projects together. let me start by offering you some training courses (free of course). i will contact you on discord. thanks for the extra vote, you rock

How did you learn how to do these analytics @paulag? I just pulled up Power BI to check it out ... is it something you can teach yourself or do you have suggestions about how to pic it up. Do I need to learn how to code and all that techy stuff?

Hi, Power BI is easy to use if you know advanced excel. It is based on Excel Power Pivot, Power View and Power Query. There is a free intro course on my site that will get you going

Awesome! Thanks! I'll have to brush up on my excel skills... can't say they're the finest ( :

wow... ask and you shall receive

Holy crap this is astonishing Paula. I've never seen something like this since I've been here. Sending you a tip right now for all your hard work! Thank you for this awesome contribution to the community 🙏🏼

well now what an honor , thank you very much for the tip

My pleasure :) Just a small token of appreciation :)

Wow!!! This is super interesting info! And useful as well. As a newbee it gives me a better understanding of things. I was pretty much on the right track after much research. But this kind of confirms my theory. Thanks again! Great post! Upvoted and Resteamed.

thank you for the vote and the resteem....I love resteems

@paulag, WOW! You have really done your homework with the data. It indeed does tell a very different story to what rumour, theory and expectation that is the mainstay for the minnows of this network. This is one of the things I love about Steemit. If you want to take the time and effort, you find out for certain what is fact or fiction.

blockchain hides no secrets.....all you need is good tools. i know no programming for analysis, I just have a good self service business intelligence system.....Power BI rocks

I'm not familiar with Power BI. If I made the time, I could figure out how to pull the information out, filter and sort it. I used be a web programmer.

But now, I'd rather devote the time and energy to my career of painting. Besides, there are wonderful stats geeks such as you to do the number crunching. Hence why I am following you. ;-)

Great info and good pithy charts. Once observation I had however is that most of the upvotes of goodkarma come from the auto voting of the posts made from esteem app. I don't have any issue with it as it promotes people to use steemit and it is going in the right direction. I just wish they had a desktop app thats all.

Great insights @paulag. Thanks and keep up the great work, would love to read more on this in the future.

thanks...there is more to come

did you really say 'follow me' and then vote for your own post???????

great information thanks!!!!!!

Very good information .., i am new player in steemit .., before i have never heard of whales .., after reading your post, i better understand how to work steemit .., thanks @paulag informations .. my @rerycore like Your post .. I wait for your next post

Great post @paula! I am sure you are helping a lot of people with it, including myself! Cheers

holy hell... new follower here... amazing post ! ty & gl (-:

What an incredible analysis. Good job. I don't know too many other people who would take the time to do this kind of thing. It's got some great insights in it, too. Thank you, @paulag.

you are most welcome

This was a fascinating read. I'm surprised at the 16% comment vote - thought it would be less. I usually get tired after the first 10 or so. It's nice to see others actually read the comments. Great post!

Thanks for the stats on the whales ;)

Really good info bro and I like how you broke it down. Well done!

you are welcome

And by bro I mean good looking lady...

Your have done a very good research to create this post. Upvoted for your hard work.

good stuff learnt lots


This was Really Truly a Great post!!
it has literally caused me to change my way of thinking..

Thank You


Intresting but i think you have to aply this to the people on dayli trending hot page . The picture will be much diferent i bet :)

The picture sure would be much different. Give me a few days and I will have something to share with you. thank you for the comment xx

Hi paula, what's the purple section in the first graph? seems it has no tag

Great article! is that 5:00 AM eastern time?

Hey @paulag, I really liked your article. So much so, that I included it in my weekly collection of those I want to share with others:

Another top statistical post! How much we talking for a copy of your dashboard?

I am open to offers......

Upvoted and following! As a new steemian i was experimenting with tags and this information was very helpful! I was also experimenting with days/ times to post to see how my posts can gain more exposure.

Great information - many thanks, @paulag ! As my posts are mostly in 'photography' (my chief interest here) - it's interesting to know the whales don't really focus on that (at a low 0.51%), but the rest do (top at 8.75%)! So, I can add 'life' (5.6%) or 'steemit' (5.4%) to my tag list!

Also of interest is that I have only seen two whales on my posts before (no wonder I'm slipping through cracks... lol) There's a whole lot of ocean of whales out there who don't see me... sighhh! But I'm not the kind to go knock on their doors now; I'm more the kind to let Life's experiment take place - see if I get the others' attention naturally... Upvoting and resteeming!

Hi @paulag, I've noticed there might be a couple of problems with your analysis.

You seem to have used totals over 15 days rather than 14, which means you didn't use the same number of each weekday. I think if you correct this, you'll find it makes quite a difference to your weekday graphs.

It probably affects two days, Saturday and Sunday because you started at 21:00 rather than on a day boundary.

Our provisional graph for votes over days (last 4 weeks) looks like this, which is surprisingly different from yours:

Our times distribution matches pretty well though (again 4 weeks):

Thank you so much for this information. But, something tells me that you loved every minute of this research. It shows in your post, your zeal for numbers and order.

lol I am a bit of a geek

Great stats... thanks a lot!
Useful to understand (or trying to) Steemit.
Big hug
Steemitri The Mannequin

awe thanks for the hug. Hugs rock. Glad you liked the post

Great to see an analysis on what big folks here are interested in voting on! Very well made.

I didn't know that there was such a difference between what whales vote on and what everyone generally votes on. Bitcoin vs Photography for example.

Maybe while Steemit have many people interested in crypto, a large amount of smaller users enjoy more traditional life-blog topics, although Bitcoin still makes up 3.3% of these votes as well.

Thanks for this article!

thank you for the comment. I am sure that there are so many niches here on steemit that its hard to get to the top. but having this info really helps.

what I would say to people is that if they think their # needs more exposure then join LFN on discord and find people also liking that topic and work together to grow it...

@rawbinhutt yes, @paulag's work is very insightful, some things aren't such big surprises, as crypto dominating. But interests of smaller users might indicated where the network will head if Steemit becomes popular. It would be great if @paula could say do some long term and progressive analysis of voting patterns.

Really interesting analytics here! I really appreciate the pie-type charts. I got really excited to see that some whales like to vote for Permaculture things! Thanks for sharing!

you are most welcome

Honestly, this is the first post that I actually wanted to upvote. Great job.

That's some really good analytics Paula. Thanks for running these.

Great information, wow @good-karma over 2000 votes?

Great work! Always glad to see more data!

always happy to work with data

Wow you sure did a lot of research ... Thank you for sharing your insight

I enjoyed the research. thanks for the comment

Interesting breakdown @paulag.

Forgive the naive question (if it is) but do these patterns also take into account SP delegated by whales or is it strictly straight whale votes?

no it does not take into consideration sp that one I must look this space!

I've got my eyes on you, so to speak. ;-)

Thank you, I have been looking for this data for a while,....

you are most welcome

That's some serious data analysis! Thanks for putting in the work needed to debunk the "self-voting and friend-voting whales are killing Steemit" rumors. It's really interesting to see the differences in content categories between whale upvotes and the entire population's voting habits.

yes it is nice to see the rumors are not exactly true, however I am doing another analysis at the moment on posts from the trending page that might raise some eyebrows

Very valuable! [email protected]
Thank you for the contribution.

thank you

this is some insight for some needed foresight, how much steem are we talking about ?

if you have power bi to use the file, I will take donations....for a copy of the report

This post was featured in Cool Stuff Curation! Thank you for being awesome!

Read it here.

awesome thanks

This is really interesting! Thanks for putting this together!!

This is amazing, I used to do a similar analysis on sales every day for almost a year. I get your point of view perfectly, perhaps you could share it with me?? Steem can get me a copy you say? hahahaha

What world time is it? UTC?

Also your post are too useful for not following you, so I corrected that :)

Congratulations! This post has been upvoted from the communal account, @minnowsupport, by paulag from the Minnow Support Project. It's a witness project run by aggroed, ausbitbank, teamsteem, theprophet0, and someguy123. The goal is to help Steemit grow by supporting Minnows and creating a social network. Please find us in the Peace, Abundance, and Liberty Network (PALnet) Discord Channel. It's a completely public and open space to all members of the Steemit community who voluntarily choose to be there.

If you like what we're doing please upvote this comment so we can continue to build the community account that's supporting all members.

You justify your description perfectly. Interesting numbers. Thank you for crunching them down :)

@paulag thats a hell lot of statsitics....great job!!!

Thank you for your work, very interesting. :)

you are welcome

Wow, that was a lot of data and you presented it very well. The best piece of information is the tags with the most votes. Helps me rethink how I tag. Thanks for the info

When comparing votes for self versus votes for others, is there a way to also see the VP used on those votes? To know that someone voted 10% of the NUMBER of votes on themselves is very different than knowing that out of 100 votes, 9 were at full power (on themselves) and 91 were at 1% for others. The 2 sets of data paint very opposing pictures, don't they?

Super awesome you have the knowledge and skills to do something like this. I wouldn't know where to start! And seeing women who know how to do it is even cooler (:
Just curious what time zone are you stating these times in?

I'm in Denver Colorado, the south side of town near Park Meadows Mall. Do you know of any trusted people I could purchase bitcoin from in cash? I probably only want about $40 worth for now. Still new to the crypto currency game.

Check out they are probably the best place to look. It's pretty hard to find someone who will sell you bitcoin in cash these days (most don't want to sell). And there is also a BitcoinATM somewhere down in Denver. I know there's one in Boulder.

Thanks for the information, I understand why people don't want to sell the bitcoin right now because it's going up so quickly.

Interesting analysis and also of great value! I learning some new things each day and I have begun to understand the importance of votes from the whales:-) Thanks a lot!

whale votes are very important, not just from a payment point of view but it also seems to have an impact on reputation

I have begun to understand that. I'm glad that there are so many people here providing great help and information for me as a newbie:) Thank you!

Awesome data analysis and interpretation.

Get ahead of them whales!

Incredible insight! I wish I knew this before...but I do know and can start taking advantage of my new knowledge. Thanks!!

I love everything about the post. Reaearch at its finest.

@paulag, great analysis

This is a really awesome dashboard that gives me real actionable insights….If you want a copy, well we can swap. I will give you a copy for Steem?????

How much?

I used to do data anaysis through excel and sql queries but that was my life in the past. I quit my corporate job to travel and now I'm more into travel blogging and creative writing.

But the data, excel and analysis are still part of me, though I'm not updated anymore. So I would like to have a copy of that dashboard. I'm more into analyzing the data about the travel tag and its trending page (that's my niche).

I am following you for more data analysis as I might feature you on my future post.

Would love to be featured in one of your posts.

Let me see if I can put a dashboard together for you in the next few days focusing on travel....remove all the other noise

Thanks, if you can do that then I will surely feature as soon as you have the data. :)

Really nice analysis, gives us minnows a lot of information for when to post or what to expect about whales ;)

stay tuned for the update

Good synthesis of info. Thanks.

Whereas overall the most popular time is 5pm

It is good to know that most votes in Steemit are done at 5pm, but you didn't specify the time zone ?
You have a worldwide audience with internet now you know.. ;)

That's how you trim the mass just show them what they desire wealth and royalty as every single person on this planet wish to be rich and financially secured same as today with Bitcoin a punch of numbers too complicated to understand makes you rich I guess we're not the smartest generation ever lived!! @thirdmanh3

awesome work and way to crunch the numbers on the big timers here on steemit. voted and resteemed..

I have been looking forward for this type of material. Wow you deserve a thumb hand. Awesome, good job, keep it up.