Analysis of Steemit Bidbot Business - Steemit Business Intelligence

The Bitbot business has been growing on steemit for some time now. Some people love them and some people hate them, but facts remain. For better or for worse they are here and they are providing a service that people are using.

For that reason I am developing a weekly report to analyse the amount of SBD and Steem Vests that are recycles through steemit via the use of Bidbots. The aim of this analysis is to track the growth or decline in the use of these services over time. By creating a weekly report, accurate information will be freely available on how much these services are actually used and we can also begin to spot trends or changes in trends between these bots.

As this is the first report, to improve it moving forward it might need some tweeks. So I will detail the actual findings and I will fully detail how I went about this analysis. I hope that you will read the steps taken to carry out this analysis and help me fine tune the results.

This analysis has been carrier out on 1 week of data from the 18th March to 24th March.

All of the Bidbots included in this analysis are listed on https://steembottracker.com

How much SBD has been sent to Bidbots for Votes?

Looks a bit messy right? But stick with me because there was a bit of work involved in calculating how much SBD was sent to bidbots for the week in question.
First I ran a query to gather all of the transfer's of SBD to these bots. The SQL query used here was

Select 
*
From TxTransfers (NOLOCK)
Where timestamp >=CONVERT(DATE,'2018-03-18') and timestamp <=CONVERT(DATE,'2018-03-25')
and [to] in ('kittybot', 'isotonic', 'getboost', 'booster', 'lightningbolt', 'nado.bot', 'shares', 'peace-bot', 'postdoctor', 'spydo', 'singing.beauty', 'minnowhelper', 'jerrybanfield', 'sunrawhale', 'sleeplesswhale', 'zapzap', 'cryptoempire', 'pwrup', 'redwhale', 'lovejuice', 'foxyd', 'noicebot', 'minnowfairy', 'honestbot', 'seakraken', 'upboater', 'whalebuilder', 'mrswhale', 'upgoater', 'smartsteem', 'dailyupvotes', 'pushup', 'allaz', 'sneaky-ninja', 'upmyvote', 'dolphinbot', 'minnowvotes', 'chronocrypto', 'thebot', 'inciter', 'oceanwhale', 'promobot', 'mitsuko', 'bearwards', 'voterunner', 'brupvoter', 'payforplay', 'edensgarden', 'redlambo', 'mercurybot', 'appreciator', 'slimwhale', 'moneymatchgaming', 'boomerang', 'childfund', 'buildawhale', 'youtake', 'megabot', 'authors.league', 'upme', 'alphaprime', 'steembloggers', 'msp-bidbot', 'lost-ninja', 'estream.studios', 'upyou', 'postpromoter', 'rocky1', 'bluebot', 'flymehigh', 'aksdwi', 'puppybot', 'onlyprofitbot', 'boostbot', 'discordia', 'canalcrypto', 'therising', 'ebargains', 'fishbaitbot', 'luckyvotes', 'brandonfrye', 'estabond', 'upmewhale', 'hotbot', 'adriatik', 'steembidbot')

However what I found with this query was that there were transfer's made between accounts that had nothing to do with bid, these looked like funding type payments, so I needed to exclude these. From looking at the memos on these transfers, most of these had no memos, and if they did, the was not url included in the memo.

So using Power BI, on the query above, I also added a filter to remove any transfers where the memo did not include some sort of url.

This has returned a value of 79,410 SBD sent to bidbot. In the above visualization the table on the left shows this 79K by bidbot.

I then looked at the memos for these transfers again. You can also see a section of this table in the above visualization. From here it was clear that there were some transfers still to be filtered out. What it appears from the data set is that all transferred for votes begin with a url (so the first few characters were https).

To account for this, I used power bi to tot the value of all transfers that where the memo does not begin with Https and I deducted this value from the total transfers. You can see in the above a value for total SBD transferred adj.

The next step in calculating the total SBD transferred to these bidbots was to look at refunds. The SQL query used here was

Select 
*
From TxTransfers (NOLOCK)
Where timestamp >=CONVERT(DATE,'2018-03-18') and timestamp <=CONVERT(DATE,'2018-03-25')
and [from] in ('kittybot', 'isotonic', 'getboost', 'booster', 'lightningbolt', 'nado.bot', 'shares', 'peace-bot', 'postdoctor', 'spydo', 'singing.beauty', 'minnowhelper', 'jerrybanfield', 'sunrawhale', 'sleeplesswhale', 'zapzap', 'cryptoempire', 'pwrup', 'redwhale', 'lovejuice', 'foxyd', 'noicebot', 'minnowfairy', 'honestbot', 'seakraken', 'upboater', 'whalebuilder', 'mrswhale', 'upgoater', 'smartsteem', 'dailyupvotes', 'pushup', 'allaz', 'sneaky-ninja', 'upmyvote', 'dolphinbot', 'minnowvotes', 'chronocrypto', 'thebot', 'inciter', 'oceanwhale', 'promobot', 'mitsuko', 'bearwards', 'voterunner', 'brupvoter', 'payforplay', 'edensgarden', 'redlambo', 'mercurybot', 'appreciator', 'slimwhale', 'moneymatchgaming', 'boomerang', 'childfund', 'buildawhale', 'youtake', 'megabot', 'authors.league', 'upme', 'alphaprime', 'steembloggers', 'msp-bidbot', 'lost-ninja', 'estream.studios', 'upyou', 'postpromoter', 'rocky1', 'bluebot', 'flymehigh', 'aksdwi', 'puppybot', 'onlyprofitbot', 'boostbot', 'discordia', 'canalcrypto', 'therising', 'ebargains', 'fishbaitbot', 'luckyvotes', 'brandonfrye', 'estabond', 'upmewhale', 'hotbot', 'adriatik', 'steembidbot')

What I found running this query was the amount of bitbots using other bidbots, but as that is not the aim of the analysis, I needed to filter these transactions out, as they have been included in the first query above. To do this, I placed a filter in Power BI to remove all transfers with memos that did not include the word ‘refund’

This refund value can also be seen in the visualization above
From here, I deducted the refund amount from the Total SBD sent adj. to come up with a fully adjusted SBD transferred value.

For the week 18th – 24th March, the amount of SBD sent was 75.70K.

What % of SBD Rewards Claimed on Steemit were sent back to Bidbots?

All curation rewards are paid in vest or SP. Only author rewards are paid in SBD. Now that I know how much was sent to the bidbot businesses I wanted to see what % of actual SBD rewards claimed were sent back to bidbots.

To gather this data the query used was

Select * From TxClaimRewardBalances (NOLOCK)
Where timestamp >=CONVERT(DATE,'2018-03-18') and timestamp <=CONVERT(DATE,'2018-03-25')

Using the data returned from that query and the total SBD sent, I was able to calculate the % of Rewards Claimed that was sent to bidbots

30% of all Author SBD Rewards were sent back to bidbots last week.

What % of Steemit Vests are paid to bidbots for Curation.

Vests are paid to both authors and curators. All curation payments are in vests. For authors if you power up your post 100% then all the authors rewards are in vests, but if you take payment 50/50 then 50% of your rewards are paid in vests. Therefore Vests make up a very large portion of the rewards.

To get the curation rewards for bidbots only I carried out the following SQL query

SELECT
reward,
timestamp,
curator
FROM VOCurationRewards (NOLOCK)
Where timestamp >= CONVERT(DATE,'2018-03-18')             and         timestamp<= CONVERT(DATE,'2018-03-25')
and [curator] in ('kittybot', 'isotonic', 'getboost', 'booster', 'lightningbolt', 'nado.bot', 'shares', 'peace-bot', 'postdoctor', 'spydo', 'singing.beauty', 'minnowhelper', 'jerrybanfield', 'sunrawhale', 'sleeplesswhale', 'zapzap', 'cryptoempire', 'pwrup', 'redwhale', 'lovejuice', 'foxyd', 'noicebot', 'minnowfairy', 'honestbot', 'seakraken', 'upboater', 'whalebuilder', 'mrswhale', 'upgoater', 'smartsteem', 'dailyupvotes', 'pushup', 'allaz', 'sneaky-ninja', 'upmyvote', 'dolphinbot', 'minnowvotes', 'chronocrypto', 'thebot', 'inciter', 'oceanwhale', 'promobot', 'mitsuko', 'bearwards', 'voterunner', 'brupvoter', 'payforplay', 'edensgarden', 'redlambo', 'mercurybot', 'appreciator', 'slimwhale', 'moneymatchgaming', 'boomerang', 'childfund', 'buildawhale', 'youtake', 'megabot', 'authors.league', 'upme', 'alphaprime', 'steembloggers', 'msp-bidbot', 'lost-ninja', 'estream.studios', 'upyou', 'postpromoter', 'rocky1', 'bluebot', 'flymehigh', 'aksdwi', 'puppybot', 'onlyprofitbot', 'boostbot', 'discordia', 'canalcrypto', 'therising', 'ebargains', 'fishbaitbot', 'luckyvotes', 'brandonfrye', 'estabond', 'upmewhale', 'hotbot', 'adriatik', 'steembidbot')

to get the Total Vests paid out I carried out the following query

Select 
Account,
Reward_vests,
timestamp 
From TxClaimRewardBalances (NOLOCK)
Where timestamp >=CONVERT(DATE,'2018-03-18') and timestamp <=CONVERT(DATE,'2018-03-25')

Almost 6.5% of all Vests earned on Steemit are paid to bidbots.

Which bidbots earn the most in Vests for curation rewards?

If we take a look at this by bidbot we can see that almost half of this is split between 4 bidbots.

Conclusion

With this analysis I have answered 4 important questions in relation to the bidbot business on Steemit.

How much SBD has been sent to Bidbots for Votes?
What % of SBD earn on steemit is sent to bidbots?
What % of Steemit Vests are paid to bidbots for Curation?
Which bidbots earn the most in Vests for curation rewards?

Moving forward I will be tracking this data an reporting on it here as it makes up a large part of the current steemit ecosystem.

What other information would improve this report? What do you think of the findings? Please do leave your comments and feedback below

---

Posted on Utopian.io - Rewarding Open Source Contributors

---