Analysis of Auction Bots - December 25 - 31

in #utopian-io7 years ago (edited)

analysis of auction bots.png

Introduction

I used to spend quite a lot of time analysing the upvote bots on the Steem blockchain. One reason for my interest was to understand which service would offer me the best return. Another was a desire to introduce transparency into what was quite an opaque marketplace by reporting on my findings.

How things change in three months! The steem bot tracker website produced by @yabapmatt made huge improvements to the information available on each upvote bot and generally made the overall bidding process much clearer. At the same time I managed to gain a little traction and my own promotion needs reduced. Last week, when @clumsysilverdad asked me which was my preferred upvote bot I really did not have an answer.

I decided to take another look at the upvote bot market, starting with an analysis of the auction bots (i.e. those bots where you bid for a portion of the bot upvote rather than those that offer a fixed return).

The aims of my analysis were two-fold:

  • To compare the upvotes produced for 1 SBD or 1 Steem across the main auction bots on the steem blockchain and understand whether there were any opportunities or discrepancies in this market
  • To ascertain whether the introduction of increased transparency through the steem bot tracker project had resulted in a similar market price across all the auction bots.

Auction bots analysed

The number of auction bots has increased very significantly since I last looked at the market. I limited my analysis to the top ten by volume as measured over December - these ten represent over 95% of the auction bot market.

Auction botSBD transferSteem transfer
appreciator35,95135
boomerang4,1102,438
booster12,3617,927
buildawhale37,10523
jerrybanfield3,19934
postpromoter5,05512,736
pushup3,3328
sneaky-ninja6,5813
upme39,43417
upmyvote8,4211
Total Result155,55023,222

Data complications

Obtaining the data for the investigation is not a simple process. It requires matching up the transfer to the bot with the corresponding upvote. This is possible by matching the url in the transfer memo with the permlink of the post which received the upvote. However there are significant complications, in particular due to:

  • incorrectly formulated transfer memos and rejected bids; and
  • duplicate bids

There are also further complications once the data is obtained due to :

  • bids being possible in both SBD and Steem; and
  • the need to translate the TxVote information into the vote value

I have cleaned the resulting data and carried out numerous detailed checks. However due to the particular complexity of this analysis I would still recommend caution with the results. Please let me know if you see any obvious flaws.


Results for December 2017

I mainly focussed my analysis on the last week of 2017: 25th - 31st December. However, before I progress to the main analysis I will show the chart of the results across December for context.

The chart shows the upvote received from 1 SBD transferred for the ten upvote bots:

Screen Shot 2018-01-08 at 00.15.56.png

It appears that the significant increases in the SBD price caused a fair degree of confusion in the market and a scattering of the upvotes available for 1 SBD. However order was restored towards the last third of the month and the upvotes grouped back together.
(due to the volume of data here I have cleaned this data less than for the main analysis - I include it only for context and have deliberately removed the legend from the chart to avoid any inappropriate conclusions being drawn).


Results for 25 - 31 December 2017

SBD transfers

The first two charts again show the upvote received from 1 SBD transferred:

I have separated the ten upvote bots into two charts for clarity.

Firstly:

  • appreciator, booster, buildawhale, postpromoter, upme

Screen Shot 2018-01-08 at 00.27.02.png

Secondly:

  • boomerang, jerrybanfield, pushup, sneaky-ninja, upmyvote

Screen Shot 2018-01-08 at 00.27.47.png

As can be seen, with the exception of booster for which the upvote amount is more volatile, and to a lesser extent boomerang, the result are really very homogenous, and quite stable, sitting around the $2.50 upvote level.

On average for the week we have the following results. There is a slightly larger upvote for boomerang on average but in general the results are very similar:

Screen Shot 2018-01-08 at 00.37.12.png

Steem transfers

This third chart shows the upvote received from 1 Steem transferred. There were only three auction bots with significant steem bids across December:

  • booster, postpromoter, boomerang

Screen Shot 2018-01-08 at 00.44.15.png

The results mirror those from the SBD transfers: generally stable but with seemingly a little more volatility from booster.

On average for the week we have the following results. There is a slightly larger upvote for booster but overall the differences are very small:

Screen Shot 2018-01-08 at 00.59.59.png


Conclusions

As might be expected, the implementation of the steem bot tracker website has brought increased transparency to the auction bot process, and also effectively introduced a market price from which the results do not vary widely.

In answer to @clumsysilverdad's question, at least as far as the auction upvote bots are concerned, there is little to choose between them on average. There may be singular opportunities or auction windows to be avoided due to large bids, but there is no clear pattern to such occasions.


Questions

This concludes the analysis for December 25 -31.

If you have any questions or spot any errors please do not hesitate to leave a comment.


Methodology and Tools for Analysis

Tools

Raw data was obtained through sql queries of steemsql (thank you as always arcange) using Valentina Studio and DBeaver (thanks also to @lpessin for the tutorial).

Data was analysed in LibreOffice and illustrated in Numbers (spreadsheet tools).

Data was obtained for various timescales, including a particular focus on December 2017 and the week of December 25 to 31.

SQL query
I used the following SQL query:

SELECT
    TxVotes.voter as [VoteGiver],
    TxVotes.author as [VoteReceiver],
    TxVotes.weight as [VoteWeight],
    convert(date, TxVotes.timestamp) as [VoteDate],
    convert(time, TxVotes.timestamp) as [VoteTime],      
    TxVotes.permlink as [VoteLink],
    TxTransfers.memo as [TransferMemo],
    TxTransfers.amount as [TransferAmount],
    TxTransfers.amount_symbol as [TransferCurrency],
    TxTransfers.[FROM] as [transferFrom],
    TxTransfers.[to] as [transferTo],
    convert(date, TxTransfers.timestamp) as [TransferDate],
    convert(time, TxTransfers.timestamp) as [TransferTime],
FROM
    TxVotes (NOLOCK)
        left join TxTransfers (NOLOCK)
                on TxVotes.voter = TxTransfers.[TO]
                    and TxVotes.permlink = IIF(CHARINDEX('/@', TxTransfers.memo) > 0, IIF(CHARINDEX('#@', TxTransfers.memo) > 0, SUBSTRING(TxTransfers.memo, CHARINDEX('/', TxTransfers.memo, CHARINDEX('#@', TxTransfers.memo)+1)+1, LEN(TxTransfers.memo) - CHARINDEX('/', TxTransfers.memo, CHARINDEX('#@', TxTransfers.memo)+1)), SUBSTRING(TxTransfers.memo, CHARINDEX('/', TxTransfers.memo, CHARINDEX('/@', TxTransfers.memo)+1)+1, LEN(TxTransfers.memo) - CHARINDEX('/', TxTransfers.memo, CHARINDEX('/@', TxTransfers.memo)+1))),'')
         left join Comments (NOLOCK)
            on TxVotes.permlink = Comments.permlink
                and TxVotes.author = Comments.author
WHERE
    YEAR(TxVotes.timestamp) = 2017 AND
    MONTH(TxVotes.timestamp) = 12 AND
    TxTransfers.amount is not NULL and
    TxVotes.voter in (
'insert bot names here'
)

The string manipulation is necessary to link the transfer url to the TxVote permlink. Any suggestions as to how to make this code simpler would be most welcome!



Posted on Utopian.io - Rewarding Open Source Contributors

Sort:  

Thank you for the contribution. It has been approved.

Congratulations @miniature-tiger for sorting this out! I tired it once and gave up. Connecting bids to votes is a huge mess, given all the wrong memos, duplicate bids, bot refunds... great work!

You can contact us on Discord.
[utopian-moderator]

Thanks @crokkon, high praise!

The data is a huge mess, even once you've made the connections. However as there are, say, 20 upvotes in a window, you only need half of them to be correctly matched to get a read on the result. I use something along the lines of median ( transfer / upvote weight) as the check - since the transfer / weight factors should all be roughly the same when the connections are good.

Great post. It must have taken ages to compile all the data for it. Its great to see whats in behind the bots but i think that booster looks like the clear winner from what you have shown there.
How you did it i have no idea but i have no computer programming skills so it all seems like magic to me.

Thank you @saoirseronan. It does take a while to put it all together!

I only learned SQL last year, so anything is possible! Although I have that kind of brain (I find writing much harder!)

I think the conclusion is that there's really not much between all the auction bots - I'm not sure the differences above were statistically significant. I should probably have put in a test for that, but it was really late when I finally got it finished!

Fantastic post! Am I right to understand that the pattern shows a decline in bot effectiveness generally?

Hey @remuslord. The change in level will be due to the movements in the underlying currencies.

The part that interested me more was the tightening or banding together, suggesting a market price had been restored.

Nice job again @miniature-tiger, Thanks for mention my Dbeaver tutorial, I'm happy it was useful. About the query I've done before something similar (rebloggs x voters) for my friend @brazilians check who votes on posts that he reblogged. I used:

select
    DBSteem.dbo.Reblogs.[timestamp],  DBSteem.dbo.Reblogs.account as Reblogger, DBSteem.dbo.Reblogs.permlink, voter
from
    DBSteem.dbo.TxVotes
inner join DBSteem.dbo.Reblogs
    on DBSteem.dbo.TxVotes.permlink = DBSteem.dbo.Reblogs.permlink
where
    DBSteem.dbo.Reblogs.account = 'brazilians'
ORDER BY DBSteem.dbo.Reblogs.[timestamp] DESC;

It worked too, but I will use your query to improve mine.. seems to be more complete
thx

Nice query @lpessin. I think your code fits better what you are trying to do than my code. My query is looking at transfers and their memos and finding a workaround to match up these memos to votes made since the system doesn't allow this directly. For reblogs you can match up reblogs and votes for an article directly (as you do in your query) so I think your approach works best.

What could be interesting is adding in some timestamp (or datediff) info to your query to see whether there is a jump in votes arriving just after the reblog. Or maybe even follower information, to show whether the voters follow brazilians or the original poster.

Yes .. that's a great idea...I was thinking the same thing: a way to measure the votes arriving after the reblog and who of the voters are followers and/or brazilian. Nice! thanks again =]

Such a careful analysis. I did try to analyze these bidbots before and understand the complications you mentioned in Data complications. Thank you for doing this analysis.

Thanks @eastmael, no problem! Thanks for the resteem also!

Again fantastic quants analysis. Nice separation out as well.

I take it in terms of reward that $2.50 is *0.75 = $1.85 (then split)

Even so it's a sound return.

Problem is I just feel dirty using them.

Thanks @revisesociology!

The analysis doesn't look at value for money, as that's a very complex question at the moment with the movements in the underlying currencies. It's comparative - seeing if there are any discrepancies between bots or if there is a market price.

Broadly yes to your example, I think, but...

  • The payout to the author is reduced by the curation percentage which could be as high as 25% but is generally around 18% due to the reverse auction of the first 30 minutes where authors receive some of the curation rewards as author payout.

  • You would also need to remove any beneficiary payments from author payout (0% on steemit or busy.org, but up to 25% on some platforms) - although I would assume people are not boosting on platforms with beneficiary payments.

  • Typically most articles would then have a 50/50 split given current SBD prices.

However these upvotes are at vesting (payout) - rather than the amount added to the article immediately. They are potentially high because Steem has appreciated over time recently. There can be significant movement across the week and they can easily go the other way.

In summary, the return will depend on what the underlying currencies do over the week (and also what you assume you would have done with the SBD - hodled or exchange etc). It's a complex question - I may come back to it after more thought!


I'm supportive of upvote bots being used for promotion / advertising, particularly at small levels and for new users, or even at higher levels for really important posts - although this is harder to define. I'm against abuse, and unfortunately there is a fair amount of that, but that's a problem with the user, not the tool.

If the entire system just became bought votes, that would be a bigger problem. I don't think that's the case at the moment.

Thanks again, I'm aware it's comparative across bots, but the return is there, so I just thought, why not analyse it. I hadn't taken into account the <25% return possibility come to think of it.

As to the working out the return viz fluctuating variables, especially fluctuating exchange rates, I find it easier just to assume a no cross-platform fee, and a constant exchange rate over the week, when working out a return, although I agree it would be interesting to factor in the differences, quite easy to figure out I guess, theoretically, much more difficult to predict in practice!

Cheers for the response.

Holy cow - you just made my head spin! 🤷‍♀️

I can say with some authority this was thoughtfully researched. I'll be bookmarking this page so I can take a look with fresher eyes later.

Thanks! 👍

Thanks @mikeycolon! Let me know if you have any questions.

he follow and upvote I follow and upvote back

Better to leave an interesting comment - if you're interested!
People sometimes upvote good comments and often flag repetitive ones.

Nice research, I always wondered why some robots rejected SBDs or Steems but with your post I have come to realization that it’s because of the amount of bidders and the highest bidders win the votes. I have also learnt other things using a bot. Thank you for this research, it’s really helpful

Hey @vellzz!

The system is in fact a little different for the auction bots. Every bid gets a proportional share of the upvote at the end of the window (as long as the bid in not faulty in some way).

Some bots do not accept Steem as bid payment - they aren't set up that way. The steembottracker has an icon to tell you which ones.

Coin Marketplace

STEEM 0.17
TRX 0.24
JST 0.034
BTC 96999.67
ETH 2733.68
SBD 0.63