Voting habits of the top 200 Whales - SteemitSQL Analysis
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 https://steemwhales.com. 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
Self-Voting
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 http://theexcelclub.com/free-excel-training/ 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.
Upvoted!
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. ;-)
:-)