Trending Page & Bid Bots - Analysis of Top 100 Posts For The Month of May, 2018
Repository
The purpose of this analysis is to show the impact of bid bots on the trending section and answer these three specific questions
- How many posts were upvoted by bid bots that appeared in the trending section? (Top 100 Posts in terms of author rewards were taken from the data)
- How a post appeared in the top 100 posts without using a bid bot?
- How many unique authors were there in the top 100 posts and how many of them used bid bots on all of their posts in the month of May, 2018?
And one general question that will be answered in the conclusion.
How difficult it is for a post to come in the trending section without using a bid bot?
Data Collection
All the data was extracted from Steem SQL Database (Paid subscription service held and managed by @arcange). The data was taken from May 1, 2018 to May 31, 2018. SQL query that was used to extract the data can be found at the end of this post.
Percentage of Posts Upvoted By Bid Bots
I extracted all the posts whose payout was more than 100 in the month of May and after executing the query in SQL, total 3,992 posts came up. Then I selected the top 100 posts in terms of author rewards along with their permlinks, titles and checked each post manually if it was upvoted by a bid bot or not. The list of bid bots was taken from Steem Bot Tracker
Out of these top 100 posts, 90 posts were upvoted by bid bots. This number is quite high when we compare it with the percentage of bid bots used on the top 50 posts in the month of March, 2018. The sum of author rewards earned by these 100 posts was 57,291 means average author reward for each post was around 572.91
Remaining 10% Posts
- Two posts were upvoted by Dlive, though a bid bot was used in one of these posts but the author herself didn't use the bot. You can see the post here.
- Four posts appeared in the top 100 posts because of self-upvotes.
- Only three posts came up in the top 100 posts that were neither upvoted by bid bots nor self-upvoted.
- One post was from @burnpost account and since this account has a different purpose, so not adding it in the last category.
Number of Unique Authors And Consistency With Using Bit Bots
Total number of unique authors who used bid bots were 51. I wanted to know if there were any authors who used bid bots on all of their posts in the month of May, 2018. To figure this out, I checked the number of posts of every author who used the bid bots and then reconciled it with the total number of posts that were upvoted by bid bots.
There were total three authors who spent some huge amount of Steem/SBDs on all the posts they published in the month of May, however the number of posts published by each user were less than 5. You will see in the below table that most of the users used bid bots once but since this analysis covers the only top 100 posts, we can't determine the total number of instances an author used bid bots in the month of May.
Conclusion
- The usage of bid bots on Steemit has certainly increased as we can see that 90% posts were being upvoted by bid bots out of top 100 posts, whereas this number was around 72% in the month of March, 2018.
- According to this analysis, a post has roughly 2% chance of coming in the trending section if an author uses any other Steem based application like Dtube, Dlive etc. We can see in the above data that two of those posts were curated by Dlive and author didn't use any bid bots on them.
- And to answer my last question, I would say that it's becoming more & more difficult for a post to appear in the trending section without using a bid bot. Using Steem based applications like Dtube and Dlive is the best solution for those who don't want to use bid bots and want to be found organically. This doesn't mean that every post will be there in the trending section whenever they upvote any post but this is the only chance for those who hate bid bots but want to see their post in the trending section
SQL Query
SELECT author,
permlink,
category,
title,
created,
total_payout_value,
author_rewards
FROM comments
WHERE created >= CONVERT(DATE,'2018-05-01') and created< CONVERT(DATE,'2018-06-01')
AND total_payout_value > 100 AND depth = 0
a more efficient SQL query would be directly using
created >= '2018/06/01'instead of CONVERT.Great effort, but it would be better if you written a script instead of checking it manually.
In case u need list of bid bots, here's steemhunt repository: https://github.com/Steemhunt/api/blob/5dd78186c115c73ceebb27dfaf496304d4c9a143/db/bid_bot_ids.json
I actually tried to get the posts writing this query but it didn't give me the accurate results.
SELECTDISTINCT.comments.author,
COUNT(comments.permlink) AS [LINK],
SUM(comments.total_payout_value) AS [REWARDS],
COUNT(txvotes.voter) AS [BOT UPVOTES]
FROM comments
JOIN txvotes ON comments.id = txvotes.id
WHERE txvotes.voter IN ('booster', 'upme', 'upmewhale', 'boomerang', 'pushup', 'rocky1', 'postpromoter', 'mercurybot', 'sneaky-ninja', 'promobot', 'brupvoter', 'smartsteem', 'appreciator', 'therising', 'emperorofnaps', 'oceanwhale', 'inciter', 'buildawhale', 'estabond', 'jerrybanfield', 'tipu', 'speedvoter', 'bumper', 'lays', 'treeplanter', 'thundercurator', 'drotto', 'flagship', 'siditech', 'promotedpost', 'byresteem', 'moonbot', 'minnowpond', 'postdoctor', 'steemvote', 'withsmn', 'hottopic', 'echowhale', 'okankarol', 'danzy', 'hugewhale', 'upvotewhale', 'earthnation-bot', 'morwhale', 'coolbot', 'big-whale', 'friends-bot', 'steemlike', 'astrobot', 'microbot', 'queqtra', 'tisko', 'bidseption', 'talhadogan', 'postresteem', 'refresh', 'steemthat', 'blockgators', 'ottoman', 'cleansingpoetry', 'resteemyou', 'bestvote', 'resteemr', 'zerotoherobot', 'schoolofminnows', 'superbot', 'crystalhuman', 'growingpower', 'alphaprime', 'earnmoresteem', 'giftbox', 'highvote', 'jeryalex', 'fresteem', 'otobot', 'oceansbot', 'red-rose', 'botcoin', 'drewardsresteem', 'bidbot', 'gangvote', 'thehumanbot', 'minnowbooster')
AND created >= CONVERT(DATE,'2018-05-01') and created < CONVERT(DATE,'2018-06-01')
AND total_payout_value > 100
GROUP BY comments.author
Not sure what I am doing wrong here, may be you can guide me.
I think is the issue of GROUP BY. Not too sure, maybe when I had time I might take a look
Hey @superoo7
Here's a tip for your valuable feedback! @Utopian-io loves and incentivises informative comments.
Contributing on Utopian
Learn how to contribute on our website.
Want to chat? Join us on Discord https://discord.gg/h52nFrV.
Vote for Utopian Witness!
This post is very good for you. I hope this post is like a jar on the eyes
Thanks for the nice post
STOPHi @ghulammujtaba, sorry for the delay in moderation! We were discussing your contribution internally and considered it not enough for an analysis contribution. I've approved your March contribution because it was both a new approach and among your first utopian analysis contributions. However, the current contribution is mostly the same approach with little additional conclusions. While you probably spend quite some time to filter the posts, this can easily be automated. With this in place, an analysis of a large time range of the highest paying out posts is no problem, not only the top 100 from one month. Also keep in mind that trending posts are not necessarily the highest paying ones, and the highest paying posts are not necessarily the ones on trending. The haejin posts typically appear on trending because they receive a large vote within an hour, but I'm pretty sure they aren't part of the highest paying ones.
Need help? Write a ticket on https://support.utopian.io/.
Chat with us on Discord.
[utopian-moderator]
Okay.