BLOGS

Analyzing Your WordPress Site with Power BI

Power BI is very famous for self-service BI which includes a variety of data sources. As it supports almost all famous data sources, sometimes we are getting a question that Power BI does have any data source for WordPress blog posts?

I could see most of them have this common questions. To answer this question, if I search Power BI desktop or online services, I couldn’t identify the dedicated WordPress data source.

But it is possible to connect WordPress with Power BI.

I take this opportunity to show how to connect your WordPress account with Power BI. We need to follow the below steps to visualise your post count, author, category and tag information in Power BI visualisation.

Pre-Requisites

It requires a plugin which should be installed on your WordPress blog.

1. Visit your blog
2. Go to your blog plugins section and click add new.
3. Type “WP REST API” in the search box and you could see the below plugin.
4. Activate the plugin

It captures all your blog information in JSON file format and through API we can access the JSON file from Power BI.

Once the above configuration is successfully completed then follow the below steps in Power BI Desktop.

We are going to use Web data source to connect WordPress.

Getting the URL of your blog with plugin extension is a bit tricky part but easy. The format of the URL should be like below.

Blog URL + /wp-json/wp/v2/ +Section

In my case, http://myblog.com/wp-json/wp/v2/ is the URL. Here section is nothing but tables which holds the specific information.

The most common tables,

1. Posts
2. Users
3. Categories
4. Tags

The framed URL will give you only 10 records as a result. To get all the records from WordPress we need to follow the below strategy.

WordPress stored all the information in pages so we need to play with pages to get all the records.

Modify your URL as like below,

http://myblog.com/wp-json/wp/v2/posts?page=1&per_page=100

In the above URL, I added the page and per page property. We can increase the page count based on the number of posts that we need to access. For example, if you have 200 posts in your blog then you need to extract two times. 100 is the maximum number of posts so we can change only the page to extract all the posts.

Once the above URL is passed in Power BI Web source as like below then you can see 100 records.

You can see an option called “To Table” which converts your list of records into the table. We need to click the “<-||->” symbol to expand.

Again, use the web data source and pass the URL        and do the same procedures to expand the columns in table format.

Once you extracted all the posts, you could see multiple queries created for each 100 records. The next important step, we need to append all the queries into a single query.

Use “Append Queries” and add all the queries.

Finally, you will get a single query with all the records on it. You can use this query for visualisations.

Repeat the same steps for all other tables’ users & category.

http://myblog.com/wp-json/wp/v2/users

http://myblog.com/wp-json/wp/v2/categories

http://myblog.com/wp-json/wp/v2/tags

Once you added all the tables, need to prepare the relationship between the tables. Each table has a common id column with matching with Posts table.

Finally, you can start building the report with the extracted tables.