Analysis of posts with YouTube Videos - SteemitSQL Block chainsteemCreated with Sketch.

in #stats7 years ago

Did you ever wonder how many posts on Steemit are link drops of YouTube videos?  Or how many people post video content?

On my last SteemitSQL block chain Analysis @greyman asked me if it was possible to see the % of posts that contained links to YouTube Videos.

Well @greyman here is your answer

 

In April ’16 only 5.97% of posts included videos, but April ’17 had 20.34%.  However May 16’ and May ’17 both had around 20% of posts with YouTube videos. June and July of ’16 and ’17 also have similar % of posts of between 16% and 17%.  So no change really.

But this is just the tip of the iceberg in terms of the analysis I carried out.

First of all let me tell you how I got the data I used in the Analysis. Using Power BI, I connected to the SQL database and the Comments table.  I then filterer this table so that only Posts would be included.  This means no comments on posts are included in this analysis.

Next I set a filter on the body of the text where it included only posts with a link to a YouTube video.

(Just so you know, Power BI is a self-service BI system from Microsoft.  As it is self-service, no programming knowledge is required to use it.  Perfect for me as I don’t even know SQL and it’s an SQL database I am looking at)

High level Analysis

The first analysis I did on the data was a high level overview.

The Total number of posts with YouTube videos is 241K, 16% of total post.

After this I then looked the number of unique authors posting YouTube Videos. 27,127 Authors have included links to YouTube Videos.  So who posts the most amount of Videos?  The table below shows the top authors sorted by number of posts with YouTube Videos

 

Tags are an important part of content being found on Steemit, so next I looked at how many posts with YouTube Videos are in each category.  It is not to my surprise that Music is on top.

 

Next I decided to look at posts with YouTube videos over time

  

 We can see that there are 3 data types on this time chart.  The largest bars are the total number of posts with YouTube Videos.  The darker color is the number of posts that contains less than 50 words* and the red are post with more than 500 words.

As Steemit is a blogging platform, I feel word count is an important factor.  However I am limited in Power BI on what I can do, so to get the word count I used an estimate.  I counted the number of characters in the body of the post and divided this by 5.  Why 5?  Well the average characters in a word is 5 so it’s my best option……

 

Almost 40% of posts have less than 50 words.  In my opinion these posts are basically link dumps.  With 44% containing less than 500 words.

More Detailed Analysis of Steemit Posts with Youtube Videos

This word count analysis deserves more investigation.  How has it changed over time?

The charts above show the % number of posts by word count, by year and by months.  We can see that in 2016 a larger % of posts had more than 500 words than in 2017.  The % of posts with less than 500 words remains consistent.  What is worrying is that the % of posts with less than 50 words, so the link dumping posts have increase from 34% in 2016 to 39% in 2017.  

We can also see from these charts that the biggest change over the time period is in April and May.  This seems to have levelled back off in June and July.  Hopefully signalling that the link dumping is reducing……

I went a lot further with this analysis. But unfortunately I cannot embed the interactive report for you here on steemit.

What I have found is that posts with YouTube videos and with higher average pay out mostly have more than 500 words.  Good to see.  The low value pay out posts and posts with zero pay-out have  less than 50 words…Link dumping will not get rewards!.  

The total average pay out for a post with a YouTube video is $8.33

The total average vote count is 23.47

Here is some of the table from which I got this data.  This table is sorted by average pay out value per author

If you would like a copy of this Power BI report to analyse yourself, ping me on Discord or on Steemit Chat, as a lot more information can be extracted.  

Conclusion

This analysis has shown that the number of youtube videos included with posts is increasing in line with the total number of posts on steemit.  What I have found is the link dumping Youtube posts are not really rewarded.  Something many of us will be glad to hear.   Rewards are paid to those that put in the effort to blog.  After all this is a blogging platform, not a video platform.

I will continue to update this report and post monthly on progress.  

For July 17, here are our top 20 YouTube posters by average pay out

 

 And here are our top 20 YouTube posters by the number of post made with YouTube Videos

 

Hope you found this post of interest and of value

Found this post interesting.....you might also like:

https://steemit.com/steemit/@paulag/how-well-do-blog-posts-really-do-on-steemit-analysis-steemitsql-database

https://steemit.com/steemit/@paulag/steemit-retention-problem-analysis-of-steemit-database

Sort:  

Really great analysis. I am glad to hear that link dumps are not getting rewarded. There is so much nonsense on steemit that is causing clutter in the feed. Meaning that original content is becoming difficult to find. I hope that there will soon be some way to filter these posts.

It sucks for me, I'm putting so much effort into each video I make, I don't just dump others works, I post my own work that is on Youtube videos.

What I do is not blogs and text, It's all in my videos.

What should I do to make people understand that I'm behind every video I post? should I just give up any big dreams here on steemit? :(

Took me 1.5 years to make 28 videos, I'm soon out of content to post here, 10 more days or so :P

I hope the community feature that i believe is promised by the developers will help your work get the exposure you deserve. As you gain more followers you may be able to repost your videos to the new audience.

I hope so too! :)

Thanks for the advice! @ammonite

I'm gonna figure out something extra when my content get depleted :)

I am also a video content creator and I feel your pain. Do not give up, just dont link dump. There is a difference.
A blog post should have at least 300 words. Do a quick summary of the video and mention it is your own content in the summary

I feel your pain too! :D :D

I'm never gonna just link dump, not even on a bad day ^^

I will keep that in mind, with the summary, and to remind people that I'm doing the content in the video.

Take care @paulag and good luck :) Steem on!

yes it would be nice to filter these out, for the moment thou, just follow people with good posts and your feed will not get cluttered

Very true. I try to be discerning on who i follow but this issue makes it difficult to for new people to get an audience.

Upvoted and Resteemed by xx-votesplus, the dropAhead curation team! Want more earnings? Join @dropahead in Streemian https://streemian.com/profile/curationtrail/trailing/396, or delegate/donate some STEEM POWER to @dropahead!

Please upvote this comment! We will earn more STEEM POWER (SP) to give you more earnings!

Keep up the good work!


Most recent post: First 7 weeks of xx-votesplus: YOUR dropAhead Curation Team!

Great work once again @paulag!
It's obvious how effort and dedication you put these posts and I see them as highly relevant to both us as posters but also to the devs working behind it all. I really hope they are following your work as its the closest thing they get to free data on how the platform is functioning.
Your findings are reassuring btw :D

cryptodan thank you so much for the comment, I do like to put effort into my analysis. Maybe I should consider using dev as a tag!

The finding are reassuring :-)

I think that would be a great idea @paulag :)

Thanks, that's useful!

I sometimes think it's a shame that the blockchain is going to have loads of broken links in future because the videos can't be embedded. I guess it's the same with images though.

There's a project called Viewly, that aims to replace youtube with a decentralised alternative, it's currently in alpha.

I am aware of this project....the person behind it was working on Steemq...also for video on blockchain so i dont really know where it will go seem steemq was dropped.

I did try test Viewly but got an error

I'm pretty sure it's the same project rebranded.

Thanks for this, I just had a dealing with one of the Top 5 in that last image on MSP's Discord. When I looked over his posts, I was disgusted by the number of posts that were clearly a dump of a Youtube embed.

This one worked purely on quantity to get his rewards, but still. I wish there was more oversight for at least this. This just doesn't feel like it's adding value to the platform.

Unlike this post, which most definitely has. Thank you.

yes high number of posts with YT links are not good, but what is good is the average payout for these is $0 :-)

I guess your dealing was not a positive one then

Awwsome information! Upvoted and resteemed.

Thanks for doing this analysis @paulag. Good to get hard data to reinforce what I thought re YT link dumping.

You are welcome

it seems Grant is the king of video!

Great analysis paulag, thank you for putting this together!

you are welcome....it only took 6 hours

Yikes! I hope it gets rewarded accordingly. I will resteem and see if that helps!

lol I dont think it will get rewarded accordingly in short term financial gains, but I may get new followers and increase my social proof here on steemit.

Thanks for the resteem, this really helps

no problem! I gave you a little mention in my most recent blog too, just for good measure!

Wow! This is a really well-research Post, followed!

Lol I spent hours on this.....

Coin Marketplace

STEEM 0.27
TRX 0.30
JST 0.047
BTC 100046.65
ETH 3883.03
USDT 1.00
SBD 3.67