Steemits Bidbot Business Income Analysis 2018 Half 1

The bidbot business on Steemit is subject to much controversy, with both sides having a fair and reasonable arguments for their development and use on Steemit.  Like it or not they are now part of the Steemit ecosystem. 

Repository

 https://github.com/steemit/steem 

Aim of Analysis

The aim of this analysis is to establish 

1.  Descriptive statistical information for an overview on bids sent to bidbots

2. How much SBD was sent to Bidbots in the first half of 2018

3. What % of SBD paid out in author rewards is sent to bidbots

4. Visualise year to date trends 

5. Establish what % of Curation rewards are paid to bidbots

6. Establish what % of Total Vest claimed goes to bidbots

Descriptive Statistics on bids sent

Descriptive statistics are useful for summarizing large sets of data.  The aim of this was to get an overview of the bids sent

  

It is interesting to see that median value sent to bidbots has reduced from Jan to June, however the standard deviation has increased.

A low standard deviation indicates that the data points tend to be close to the mean, while a high standard deviation indicates that the data points are spread out over a wider range of values.

We can also see the distribution of the bids in the histogram, with a right tailed curve as most bids are placed at lower values.

How much SBD was sent to Bidbots this year?

  

In total 2.58M SBD was sent to the top big bots as listed.  This value required adjusting to remove transfers not related to bids and also refunds where no votes were given.  This leaves a fully adj SBD value sent to bidbots for April to be 2.43M SBD.  It is the fully adjusted value we will be using from here on to based our calculation

We can see from the time graphs, Jan to march seen a decline in the SBD sent until 13th April, from then there was a step rise in the daily amount of SBD sent till the 5th of May and it has been downhill again since. At 30th June we were a new yet low.

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

  

SBD is the currency used to bid on these bots. SBD is earned only by taking a 50% 50% pay out on a post.  That means that SBD is NOT earned for curation. At the moment, SBD is not paid out at 50% instead some of it actually paid in liquid Steem.  This means there is less SBD available

 22.44% of total SBD rewards claimed were sent back to bidbots so far in 2018.  In January this was almost 15% and has risen each month to a whopping 35.74% in June. We can see this in the red bar chart above. 

The yellow bar chart shows how much SBD was claimed in SBD. June was down 58% on May and would therefore account for the higher % of SBD being sent to bidbots.  

Annual Trend

The chart below shows the number of daily bids in red, number of posts submitted for bits in black and the number of authors in blue.

  

 We can see that the trend for each line is the same. The number of daily bids has now dropped to its lowest level this year with only 3609 bids made on the 30th June compared to 9786 at its peak in April.  

The distinct number of Authors also sending to bidbots has reduced.  894 authors sent bids to bots on the 30th June, lowest value year to date. Whereas the highest value year to date was 2499 on the 30th April.  

What % of Curation Rewards Claimed are paid to bidbots?

 

The business model of the bidbots allows them earn curation rewards on posts they voted on in addition to the bids made.  Year to date 29.8% of Curation Rewards claimed are paid to bidbots.

  The purple bar chart shows the total vests claimed from curation rewards this year, by month. We can see that June had the highest total vests curation reward this year. However while the total curation vests earned increased 16% from May to June, bidbot owners seen almost no movement. 

As a % of total curation rewards earned by bodbots as a total of all vests earned by curation, bidbots were averaging above 30% until June where it is now down to 25.4%.  

The pie chart below shows this by bidbot

  

What % of total Vests claimed (author and curator) went to bidbots?

So far we have looked at the % of SBD earned from author rewards going to bidbots. We have also looked at the % of total curator rewards earned by bidbots.  Now we will take a look at the total vests claimed (both author and curators) made by bidbots.  The total vests claimed represents ALL author and curator rewards.

   

We can see from above for the year to date Bidbots have earned almost 11% of total rewards on the platform.  In Jan this % was as low as 7.67% rising to almost 14% in June.

Concerning Trend

When looking at the data, I did do further exploratory work and found something concerning, which is not only backed up with data but also a recent comment left on a post.

Here is the comment

 

and here is the data

what we can see is by the end of June more than 100% of SBD claimed is being sent back to bidbots. This means that people are 'saving up' their SBD and as the comment says, also powering down so they can make bids

Conclusion

The high standard deviation and range in June, coupled with the histrogram would suggest that a few people are bidding unnecessarily high.  As there is less SBD available, less people are bidding and in general the bids are getting smaller

 2.43M SBD was sent to bidbots for voting from Jan to June, peaking in May SBD is earned only as Author rewards. 

22.44% of all SBD earned on Steemit has been sent to bidbots for votes. The trends show that as the SBD claimed in rewards decreased, the % SBD sent to bidbots increased, and as the SBD claimed increase the % SBD decreased.  As SBD has not been prinited as much in June we are seeing a high % of SBD being sent back to bidbots. 

29.8% of All curation rewards earned on Steemit are earned by bidbots. 

10.4% of ALL Steemit earning are going to bidbots.  

There is concerning evidence that people are powering down to use bidbots.

The Data and Queries

First I used the Steembottracker API to get a list of bidbots.  This list was then used in the M queries below

The data was collected from SteemSQL using PowerBI.  The following M codes were used.

M code for Sent to bid bots

let    Source = Sql.Database("vip.steemsql.com", "DBSteem", [Query="Select #(lf)*#(lf)From TxTransfers (NOLOCK)#(lf)Where timestamp >=CONVERT(DATE,'2018-01-01') and timestamp <CONVERT(DATE,'2018-07-01')#(lf)and [to] in ('tainika' ,'chronoboost' ,'getkarma' ,'weupvote' ,'alliedforces' ,'a-bot' ,'boinger' ,'jerrybanfield' ,'upyourpost' ,'pwrup' ,'brotherhood' ,'haveaheart' ,'alfanso' ,'whalepromobot' ,'joeparys' ,'peoplesbot' ,'votepower' ,'t50' ,'moneymatchgaming' ,'stef' ,'sureshot' ,'ptbot' ,'th3voter' ,'edensgarden' ,'oceanwhale' ,'botox' ,'whalecreator' ,'profitbot' ,'ecotrain' ,'automation' ,'siditech' ,'cabbage-dealer' ,'profitvote' ,'steemerap' ,'ubot' ,'dlivepromoter' ,'emperorofnaps' ,'proffit' ,'lrd' ,'bodzila' ,'peace-bot' ,'brandonfrye' ,'authors.league' ,'flymehigh' ,'noicebot' ,'redwhale' ,'lost-ninja' ,'dolphinbot' ,'rocky1' ,'estabond' ,'minnowvotes' ,'thebot' ,'booster' ,'slimwhale' ,'megabot' ,'singing.beauty' ,'estream.studios' ,'dailyupvotes' ,'ebargains' ,'promobot' ,'honestbot' ,'foxyd' ,'sunrawhale' ,'mitsuko' ,'onlyprofitbot' ,'spydo' ,'isotonic' ,'brupvoter' ,'postdoctor' ,'luckyvotes' ,'therising' ,'inciter' ,'redlambo' ,'shares' ,'nado.bot' ,'bid4joy' ,'voterunner' ,'steembloggers' ,'upmewhale' ,'mercurybot' ,'smartsteem' ,'postpromoter' ,'upme' ,'msp-bidbot' ,'aksdwi' ,'pushup' ,'appreciator' ,'sneaky-ninja' ,'lovejuice' ,'minnowhelper' ,'boomerang' ,'buildawhale')"]),    #"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp", type date}}),    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([memo], "https"))in    #"Filtered Rows"

M code for Refunds

let    Source = Sql.Database("vip.steemsql.com", "DBSteem", [Query="Select #(lf)*#(lf)From TxTransfers (NOLOCK)#(lf)Where timestamp >=CONVERT(DATE,'2018-01-01') and timestamp <CONVERT(DATE,'2018-07-01')#(lf)and [from] in ('tainika' ,'chronoboost' ,'getkarma' ,'weupvote' ,'alliedforces' ,'a-bot' ,'boinger' ,'jerrybanfield' ,'upyourpost' ,'pwrup' ,'brotherhood' ,'haveaheart' ,'alfanso' ,'whalepromobot' ,'joeparys' ,'peoplesbot' ,'votepower' ,'t50' ,'moneymatchgaming' ,'stef' ,'sureshot' ,'ptbot' ,'th3voter' ,'edensgarden' ,'oceanwhale' ,'botox' ,'whalecreator' ,'profitbot' ,'ecotrain' ,'automation' ,'siditech' ,'cabbage-dealer' ,'profitvote' ,'steemerap' ,'ubot' ,'dlivepromoter' ,'emperorofnaps' ,'proffit' ,'lrd' ,'bodzila' ,'peace-bot' ,'brandonfrye' ,'authors.league' ,'flymehigh' ,'noicebot' ,'redwhale' ,'lost-ninja' ,'dolphinbot' ,'rocky1' ,'estabond' ,'minnowvotes' ,'thebot' ,'booster' ,'slimwhale' ,'megabot' ,'singing.beauty' ,'estream.studios' ,'dailyupvotes' ,'ebargains' ,'promobot' ,'honestbot' ,'foxyd' ,'sunrawhale' ,'mitsuko' ,'onlyprofitbot' ,'spydo' ,'isotonic' ,'brupvoter' ,'postdoctor' ,'luckyvotes' ,'therising' ,'inciter' ,'redlambo' ,'shares' ,'nado.bot' ,'bid4joy' ,'voterunner' ,'steembloggers' ,'upmewhale' ,'mercurybot' ,'smartsteem' ,'postpromoter' ,'upme' ,'msp-bidbot' ,'aksdwi' ,'pushup' ,'appreciator' ,'sneaky-ninja' ,'lovejuice' ,'minnowhelper' ,'boomerang' ,'buildawhale')#(lf)"]),    #"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp", type date}}),    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","#(tab)https","https",Replacer.ReplaceText,{"memo"}),    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each Text.Contains([memo], "Refund")),    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([amount_symbol] = "SBD")),    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [amount] < 100)in    #"Filtered Rows2"

M Code for Claimed rewards

let    Source = Sql.Database("vip.steemsql.com", "DBSteem", [Query="Select * From TxClaimRewardBalances (NOLOCK)#(lf)Where timestamp >=CONVERT(DATE,'2018-01-01') and timestamp <CONVERT(DATE,'2018-07-01')#(lf)"]),    #"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp", type date}})in    #"Changed Type"

M Code for Bots curator rewards

let    Source = Sql.Database("vip.steemsql.com", "DBSteem", [Query="SELECT#(lf)reward,#(lf)timestamp,#(lf)curator#(lf)FROM VOCurationRewards (NOLOCK)#(lf)Where timestamp >= CONVERT(DATE,'2018-01-01')             and         timestamp< CONVERT(DATE,'2018-07-01')#(lf)and [curator] in ('tainika' ,'chronoboost' ,'getkarma' ,'weupvote' ,'alliedforces' ,'a-bot' ,'boinger' ,'jerrybanfield' ,'upyourpost' ,'pwrup' ,'brotherhood' ,'haveaheart' ,'alfanso' ,'whalepromobot' ,'joeparys' ,'peoplesbot' ,'votepower' ,'t50' ,'moneymatchgaming' ,'stef' ,'sureshot' ,'ptbot' ,'th3voter' ,'edensgarden' ,'oceanwhale' ,'botox' ,'whalecreator' ,'profitbot' ,'ecotrain' ,'automation' ,'siditech' ,'cabbage-dealer' ,'profitvote' ,'steemerap' ,'ubot' ,'dlivepromoter' ,'emperorofnaps' ,'proffit' ,'lrd' ,'bodzila' ,'peace-bot' ,'brandonfrye' ,'authors.league' ,'flymehigh' ,'noicebot' ,'redwhale' ,'lost-ninja' ,'dolphinbot' ,'rocky1' ,'estabond' ,'minnowvotes' ,'thebot' ,'booster' ,'slimwhale' ,'megabot' ,'singing.beauty' ,'estream.studios' ,'dailyupvotes' ,'ebargains' ,'promobot' ,'honestbot' ,'foxyd' ,'sunrawhale' ,'mitsuko' ,'onlyprofitbot' ,'spydo' ,'isotonic' ,'brupvoter' ,'postdoctor' ,'luckyvotes' ,'therising' ,'inciter' ,'redlambo' ,'shares' ,'nado.bot' ,'bid4joy' ,'voterunner' ,'steembloggers' ,'upmewhale' ,'mercurybot' ,'smartsteem' ,'postpromoter' ,'upme' ,'msp-bidbot' ,'aksdwi' ,'pushup' ,'appreciator' ,'sneaky-ninja' ,'lovejuice' ,'minnowhelper' ,'boomerang' ,'buildawhale')"]),    #"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp", type date}}),    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","VESTS","",Replacer.ReplaceText,{"reward"}),    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"reward", type number}}),    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"reward", "reward VESTS"}})in    #"Renamed Columns"

M Code for  Total Curation rewards

let    Source = Sql.Database("vip.steemsql.com", "DBSteem", [Query="SELECT#(lf)reward,#(lf)timestamp,#(lf)curator#(lf)FROM VOCurationRewards (NOLOCK)#(lf)Where timestamp >= CONVERT(DATE,'2018-01-01')             and         timestamp< CONVERT(DATE,'2018-07-01')#(lf)"]),    #"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp", type date}}),    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","VESTS","",Replacer.ReplaceText,{"reward"}),    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"reward", type number}}),    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"reward", "reward VESTS"}})in    #"Renamed Columns"