Bidbot Income Analysis shows bid bots earned 19% of the Rewards Pool in 2018

2018 seen the rise of the bidbots on STEEM.  However it also seen a downturn in the crypto market and in the use of STEEM.  Personally I have never been a massive fan of bidbots,  I hate the fact they killed the reputation system, but I do believe they hold their place and have fulfilled the demands of many wanting to use them.

I have previously look at data around bidbots before and with the year coming to a close, I thought I would have one last look and see how much of the rewards pool was earned by bidbots in 2018.

Repository

 https://github.com/steemit/steem 

Aim of Analysis

The aim of this analysis is to establish and present

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

2. How much in vests was sent to Bidbots in 2018

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

4. Visualize 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 April, however the standard deviation has increased.  From April to Sept the median bid set varied slightly but no big changes.  Since Sept we have seen an increase again in the median bid sent.

For the year to date December seen the largest variance in the size of the bid sent, with the maximum bid being the highest all year.

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.

What % of Curation Rewards were paid to bidbots?

 

The top chart shows how much was claimed in total for curation rewards.  The second chart shows how much bidbots earned in curation rewards and below this is the % of total curation rewards earned by bidbots.

October seen the highest % curation rewards paid to bidbots while December seen the lowest. 

In 2018 a total of 25.9% of all curation rewards were earned by bidbots. 

The pie chart below shows what % of total curation rewards each of the bots made.

 

What % VESTS earned from Authors rewards were sent to bots for bids?

 

Curator rewards are not paid in a liquid form, however author rewards do have a liquid portion and so those using them tend to send rewards they have earned as an authors back to bots.

As a % of authors rewards claimed which were then sent on as bids, we see a steady increase from Jan to June.  This declines to September and then increases again.  This can be seen in the chart with the red bars.

The yellow bar chart shows the total vests sent to bots each month of 2018.  May being the highest month, however based on %, June seen the highest %.

For 2018 bidbots were sent 18.85% of all claimed authors rewards as bids.

Annual Trend

The chart below shows the number of authors using bots in blue and, number of posts submitted for bits in black.

 

 We can see that the trend for each line is the same. The number of bids has been on a decline since May and we can clearly see the impact of HF20 on the chart.

What % of Total Vest Claimed (author and curator) were earned by bidbots?

 

we have seen the % of curator rewards earned by bidbots and we have also looked at the % of Authors rewards that were used for bidding.  Combining these we can calculate how much of total vests claimed from the rewards pool were earned by bidbots.

for the year 2018 bidbots earned between curation rewards and bids 19.1% of the rewards pool.

The pie chart below shows this by bidbot

 

Top Bot users in terms of Vests sent as bids for 2018

Conclusion

 Descriptive statistical information shows the size of the bids changes month to month and the variance can be rather considerable.  Its interesting to see that December seen the highest bid sent  yet the trend chart shows it to be a lower month in terms of usage.

 9,733,070,117 vests were sent as bids to bots for 2018

18.85% of vests paid out in author rewards for 2018 were sent to bidbots

25.95 % of Curation rewards were paid to bidbots

19.10% of Total Vest claimed in 2018  went to bidbots

4 bidbots earned 9.68% of the total rewards pool paid out in 2018. These earning were a combination of bids send and curation rewards earned. 

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-12-29')#(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-12-29')#(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-12-29')#(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"