Looking at the numbers from Steepshot // January - May 2018

in #utopian-io6 years ago (edited)

Repository

https://github.com/Chainers/steepshot.io

Introduction

According to steepshot.io

Steepshot is the blockchain-powered censorship-resistant platform that you can use to share cool pictures with your friends, curate and comment their photos and get crypto rewards for your activity.
On Steepshot, you get rewarded for sharing pictures of moments in your life.

Outline

  • Scope
  • Results
  • Findings
  • Conclusion
  • Tools and Scripts
  • Relevant Links and Resources

Scope

Date of the analysis: 6th June 2018
Timeframe of the analysed data: January to May 2018
Components of the analysis: Analyzing users and post made from Steepshot

Results

ACTIVE USERS

Between January and May 2018, there were 215,841 posts from the steepshot app. These posts were made by a total of 13,311 users. That is an average of 16 post per user.

In this post the term "Active" is defined as a user who makes at least one post a month via Steepshot.
The numbers from Steepshot are not encouraging as 53.15% users made post in only one month between January and May 2018. While a meager 3.76% of users can be termed as "active" as they posted at least once in every month between January and May 2018

POST INTERACTION/ENGAGEMENT
The data for Post and comments excluded post and comments from the most popular bots on steemit. The bots used are listed on https://steembottracker.com

This code snippet was used to filter them out.


 author not 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')


depth = 0 and depth > 0 was used to filter comments and posts

The number of comments on a post can give us an idea on the level of interaction in the community. The lower the number of comments on a post the less likely there is a good interaction on a post. The idea of Steepshot is to share moments of life via pictures and pictures ignite our inquisitive minds.

Between January and May 2018 there were a total of 47,398 comments made via Steepshot on posts.

Number of authors posting via steepshot didn't vary much between January and May 2018. The comment to post ratio averaged 0.2 for January to May 2018.

Interaction is between two or more persons and majortiy of post have 0-1 comments pointing to a low level of interaction between users.

It was also observed that comments on steepshot post were more from Steemit than Steepshot itself. Few comments were made from Steepshot app itself.

EARNINGS/FINANCIAL BREAKDOWN

For post made via steepshot, there was a total earning of about $226,582.53 from January to May 2018. This payout was nothing close to an even distributions as only few post had a reasonable payout value.

Monthly Payout volume was in an undulating pattern, going up and down with no definite pattern of growth or drop

MONTHPAYOUTAVERAGE STEEM PRICEUPVOTES
Jan66,382.61$5.45338,834
Feb46,747.75$4.03400,916
Mar27,131.35$2.37465,468
Apr48,072.24$2.70539,721
May38,248.58$3.10550,805

STEEM price was evaluated from https://coinmarketcap.com/

The number of upvotes has grown consistently from January to May 2018, Janaury had the lowest number of upvotes within the 5 months under review but the highest payout value. Payout and price of STEEM has a correlation. With high price of steem, there was a higher payout.

The analysis however has a limitation as another determining factor is STEEM POWER of individual voters. In this analysis, the STEEM POWER of individual voters was not put into consideration

94,418 Posts have a payout of $0 which is about 43% of all post and 90% of all post earned less than $2 in payout

Findings
  • The retention numbers / active users on Steepshot is poor.
  • Post engagement is also on the low
  • For now i would say Steepshot is not "stablized" as its numbers move in a rather almost unpredictable pattern.

Conclusion

With the emergence of other application for posting, The competition is rising and Steepshot has to find a way to increase user retention and with the low price of SBD minnows are finding it difficult to earn much on the blockchain as more payout go to more established few.

Engagement is necessary in order to retain users

Tools and Scripts

Microsoft SQL Server Management Studio 17 was used to access the data from STEEMSQL ( a publicly managed database by @arcange )

Microsoft Excel was used to plot graphs and Charts.

SCRIPTS

SELECT
author,
created,
json_metadata,
 (JSON_VALUE (json_metadata,'$.app')) AS App,
children,
total_payout_value,
net_votes

FROM COMMENTS (NOLOCK)
WHERE 
depth = 0
AND
created>= CONVERT(datetime,'1/01/2018') AND created< CONVERT(datetime,'6/01/2018') 
AND
IIF(isjson(comments.json_metadata) = 1, IIF(CHARINDEX('/', json_value(comments.json_metadata, '$.app')) > 0, SUBSTRING(json_value(comments.json_metadata, '$.app'), 1, CHARINDEX('/', json_value(comments.json_metadata, '$.app'))-1),json_value(comments.json_metadata, '$.app')), null) = 'steepshot'


The script was adjusted to test different parameters/indices

Relevant Links and Resources

https://steepshot.io/faq
https://steemsql.com/

Proof of Authorship

https://github.com/jingis07

Sort:  

Go here https://steemit.com/@a-a-a to get your post resteemed to over 72,000 followers.

A better query would be using a CONTAINS to get all the steepshot tag and then followed by LIKE, which makes the query much more faster and efficient. In addition to that, the created column doesn't need to have CONVERT.

A sample query:

SELECT
  *
FROM Comments
WHERE
  depth = 0 AND
  CONTAINS(json_metadata, 'steepshot') AND
  json_metadata LIKE '%app": "steepshot%' AND
  created > '2018/06/12'

Thank you. But I'm trying to get data of post made from steepshot app and not steepshot tag analysis

if you read closely, the first line will do a full text search on json_metadata containing 'steepshot', and then compare with the next line. I suppose this will be quicker.

tagging @arcange to verify this

your code above returns about 10 rows of data only. and the * picks all info from the comments table and i don't need a lot of them

hmm, you are right. I was reading the doc on CONTAINS, but I presume it's a full text search.

https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-2017

try this

SELECT
  *
FROM Comments
WHERE
  depth = 0 AND
  created >= '2018/01/01' AND
  created < '2018/06/01' AND
  CONTAINS(json_metadata, 'steepshot') AND
  json_metadata LIKE '%app": "steepshot%'

I tested, the query takes less than 30 seconds to get data, which is much faster then ur query

The benchmark test (not completed):

took some time to load and didnt load complete

Hi @jingis07, glad we could figure this out, well done! The analysis of the number of active users over a longer period of time is a very interesting metric. It shows that the number of recurring users is significantly lower than the number of active users. This approach is something to keep in mind also for other analyses. I'm curious, how did you filter out the bot comments? I think this needs a more complex query than what you've sketched with the author not in (...), or am I missing something?

Your contribution has been evaluated according to Utopian policies and guidelines, as well as a predefined set of questions pertaining to the category.

To view those questions and the relevant answers related to your post, click here.


Need help? Write a ticket on https://support.utopian.io/.
Chat with us on Discord.
[utopian-moderator]

It is not complex as you think.
First of all

depth > 0

filters post, leaving only comments

Then author not in (......) removes comments from the authors in the bracket

REFERENCE

https://www.w3schools.com/sql/sql_in.asp

https://steemit.com/utopian-io/@paulag/analysis-of-steemit-bidbot-business-steemit-business-intelligence

Hi @jingis07, thanks for your explanation. Now I understand: your query gets all comments done via steepshot, but does not include comments done via steemit, busy, etc,. Filtering out the bot authors on those comments probably makes no difference, since the bots don't post via steepshot anyway but use other/custom app settings in json_metadata. So the actual engagement level on steepshot posts is likely to be higher than the numbers you've shown.
I think it could work to get all replies to a steepshot post by joining Comments as posts with Comments as replies on posts.permlink = replies.parent_permlink etc... In that case your author exclude list could be used to filter out the bot replies.

Hey @jingis07
Thanks for contributing on Utopian.
We’re already looking forward to your next contribution!

Contributing on Utopian
Learn how to contribute on our website or by watching this tutorial on Youtube.

Want to chat? Join us on Discord https://discord.gg/h52nFrV.

Vote for Utopian Witness!

Coin Marketplace

STEEM 0.35
TRX 0.12
JST 0.040
BTC 70753.86
ETH 3589.34
USDT 1.00
SBD 4.75