Analyzing Steemit Data Using Power BI
Power BI a Brief History
In 2010 Microsoft released Power Pivot, the first of the Excel Power Tools. Power Pivot will allow you analyse multiple tables of data against each other, removing the hell in a cell associated with Excel. Along with Power Pivot comes a functional language very like Excel formula that can be used to model and carry out complex calculations on the data.
Next, in 2013 Microsoft released Power Query and Power View. Power Query will allow you connect to multiple sources of data and transform that data into a usable format. Like Power Pivot, Power Query also comes with its own language know as M.
Power View is then use to visualize and tell stories about that data.
These Excel Power Tools became the basis for self-service business intelligence for the end user.
In 2015 Power BI was released. Power BI is made up of Power Pivot, Power Query and Power View, but without excel.
Big Data, Business intelligence, actionable insights, and all these words are floating about and you are wondering, How can I use data to get and insights? What data can I use? What software can I use? How much will it cost me? What skills are required? How can I get started?
Let me debunk the first myth. High cost of entry. Business Intelligence, actionable insights, interactive dashboards are within the budget of every company, every business, every brand.
In 2015 Microsoft released PowerBI to the general public. The desktop versions is completely free, and there is also a free trial period for PowerBI Services.
The second myth is that a company or business needs a data scientist to make sense out of the data. A company needs someone that understands the business, so when a trend is spotted the business expert knows why and if corrective action is needed.
PowerBI offers Insights to data by carrying out Microsoft machine learning on your data. Power BI also offers a suite of Connectors that comes with pre built dashboards, all you need to do is connect to your data. Power Bi is a self-service system, its aim is to allow anyone analyse data to get meaningful insights.
Analyzing Steemit Data with Power BI
To set up the interactive dashboard, I connected to the SQL SteemitDB via Power BI Desktop. This database is kept by @arcange (awesome job by the way!). I found the log in details on an old post from 10 months ago https://steemit.com/steemit/@arcange/steemsql-com-how-to-create-a-steem-analytic-report-with-microsoft-excel
As I have connected to an SQL server, this data is not live. However if the database was stored in Azure SQL Database I could run live reports……this is something I would like to do but I would need to fund the Azure account so let’s see how these posts do here on Steemit and see if this is something the community want
In the interactive report, I have only looked at two tables of data available in the database. This table is Accounts and AccountsTX. I have filtered these datasets to only include accounts that have been set up from the 1st 1 July 2017 and 11th July. So the current month.
I do not know SQL, and I also do know the query language M, so I am reliant on the fact the Power BI is self-service. I found it simple to connect to the database, but there was an element of difficulty when I was transforming the data, especially when I was trying to extract the Profile information such as location and websites. I haven’t yet read any documentation on the Database, so the information is purely from exploration.
The idea was to look at new users and find out a bit more about them and their activity on Steemit. However what has stood out to me is the signs of low retention, and I think this warrants more exploration in another post and analysis.
I was very surprised to see that less than 50% of accounts registered with Steemit this month have actually made a post. I also found it very interesting that new users are not utilizing their profile. Only 18% have completed the ‘about’ section and less than 9% have included a link back to a website or other social profile.
Anyway this dashboard is interactive, so please do play around with it. (Amended, Steemit does not support the iframe for this interactive dashboard so below is a screen print and you can find a link to the interactive report further down. I am very disappointed as I had spent the time analyzing the data using this tool because I thought Steemit users would find it of benefit, now I am posting a screenshot with a link….grrrrrrr....click here to open interactive dashboard)
If you would like to learn more about Power BI, I have a free course on my website – Power BI the Ultimate Orientation. I do intend on creating how to Power BI posts, but in the meantime, please do feel free to sign up for the free course
I would love to hear your comments and feedback on this report and what you think it says about first impressions and retention
I am a bit confused, I see no
AccountsTXtable, do you meanTxComments?Another question, are you distinguishing between 'posts' and 'comments'? The reason I ask is that comments are posts, with a parent.
Maybe you could update your dashboard and show the number of new accounts with posts vs new accounts with comments.
Looking forward to what else you uncover from the data!
thank you for this feedback, which i am only seeing now!
The tables I used is TX AccountsCreated and Accounts
@paulag got you a $0.44 @minnowbooster upgoat, nice! (Image: pixabay.com)
Want a boost? Click here to read more!
Congratulations! This post has been upvoted from the communal account, @minnowsupport, by paulag from the Minnow Support Project. It's a witness project run by aggroed, ausbitbank, teamsteem, theprophet0, and someguy123. The goal is to help Steemit grow by supporting Minnows and creating a social network. Please find us in the Peace, Abundance, and Liberty Network (PALnet) Discord Channel. It's a completely public and open space to all members of the Steemit community who voluntarily choose to be there.
If you like what we're doing please upvote this comment so we can continue to build the community account that's supporting all members.
Love SteemSQL and power query / power pivot. Have been using that combo for about a week. I was also surprised at how many accounts are inactive/bots/fake.
Feels like just fake accounts setup to earn self-voting dollars.
It looks that way to me too @financialcritic.
Have you used powerbi at all?
No, haven't tried that. Is it a free download or part of office?
It's a Microsoft product, free to download. You can use it free. And if you're on the free version, I believe you can post your reports to the public repository. If you want to keep it private then you'll need to get onto one of the pricing plans. A pro plan is $10/mo so not terrible for someone who will make good use of it.
I'm using it in my work right now. Looking at data in ways that were too labor intensive to set up and constantly update in something like Excel.
One great feature is the ability to use online dashboards which can be viewable through your mobile app as well. Not sure if this is part of the free or pro plan but we're making use of this daily at work.
Great example and use case of Power BI. Have you tried looking at any other cryptocurrencies with Power BI? I'm interested in the project but looking for a good source of historical data on the many currencies out there or at least like the top 100.
Yes, I have taken historic data from coinmarketcap with power bi. Need a separate query for each coin on historic data, but then all the queries can be appended. their api does not support historic data so I have to connect to the webpage and modify the date range in M
Using power bi for analysis is convenient in the first place.
Hi! I usually use power bi for analysis because it is the easiest way to visualize your business data. I think you will also find power bi jira integration useful. This is the best solution for the fastest data export. I advise you to read an article about it. Maybe it will be more convenient for you.