Analysis of posts with YouTube Videos - SteemitSQL Block chain
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.
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: