WordPress is one of the most popular open-source website making tools which is vastly used by many bloggers including myself. WordPress developers provide tons of custom plugins to fulfil different needs. But not all of the plugins are well designed and secured. One of the most interesting pieces of information anyone who owns a website/blog needs is their website statistics. In this post, I explain how to analyse your WordPress website stats in Power BI. Before I start, I want to point out that there is a WordPress stat already available in WordPress Admin Dashboard which provides very informative information about your blog like Total Views, Today Views, Best Ever Views and so forth. You can also install the WordPress app on your mobile device to easily access your website stats. But, the stats WordPress gives me in not enough. I want more. I need a more detailed analysis on
- Current Month vs. Last Month
- Current Year Vs. Last Year
- Most Popular Day of Week
- Most Popular Month of Year
- Top 10 Posts
and so on.
As non of the above analysis are available in the normal stats , I decided to build my own version of “WordPress Website Stats Analysis in Power BI”. This gives me the flexibility of creating as much analysis as I need , and… it is so much fun.
If you own a WordPress blog or any other sort of websites or if you’re just curious to learn how to use a website API in Power BI, then this post is for you.
I managed to create a Power BI Desktop template that you can download and use it for free. You’re welcome to modify it based on your needs. You can find the download link at the button of the page.
How It Works
To be able to analyse your WordPress stats in Power BI you need to own a WordPress blog or website. Then a WordPress API key is assigned to your account. The key was included in your WordPress Welcome Email. You can use that API key in Power BI Desktop to create your customised reports and analyse your blog/website stats then you can publish the model into Power BI Service that is accessible anytime anywhere. You can also create your own dashboard in Power BI Service. Moreover, you can setup Schedule Refresh for the dataset to refresh your dashboards and reports automatically.
Requirements
You’ll need to
- own a WordPress blog/website and have the API assigned to your account
- install the Jetpack plugin in your blog/website as WordPress stats is no longer maintained and you should not use it. Instead, you can install the Jetpack plugin
- have Power BI Desktop installed on your machine (Download it from here, it’s free!)
- have a Power BI account (Don’t have an account? Signup for it here, it’s free too!)
Find your WordPress API Key
As I mentioned earlier the API key is included in the Welcome email you received when you registered your blog/site with WordPress. However, you might not have that email anymore. You can get it from Akismet website.
The other way top find it is from your account when you login using your admin account, then click “Akismet” from “Jetpack” menu from left pane and you’ll find the API key under “Settings” section.
Get WordPress Stats API
You can get WordPress stats API from here: http://stats.wordpress.com/csv.php
When you click the above URL you see a list of “GET” parameters available which you can generate different queries to get different datasets. To build your query use the following generic URL construction:
So, to get data about post views for 30 days the above URL will look like this:
- If you want to get all data about post views ever then use table=postviews&days=-1
- To get all data of your referrers then table=referrers&days=-1
- To get search terms data then table=searchterms&days=-1
If you copy/paste the URLs above in your browser you should get the data you’re looking for.
Read more at “phoxis.org“.
Building a Power BI Desktop Model to Analyse Your WordPress Site Stats
Building a Power BI model is fairly easy. We just need to load data using the URLs above and a Date table for sure. To do so:
- Open Power BI Desktop
- Click “Get Data”
- Find and click “Web” then “Connect”
- Paste the URL to get post views data then click OK
- Click “Edit” in the next page. This navigates you to the Query Editor
- As you see Power Query already promoted the first row as column names
- Rename the query to “Views” then click “Apply”
You may repeat the above process to get “Referrers” and “Search Terms” data as well. For the sake of this post I keep it as simple as possible and carry on with only “Views” table.
Therefore you may click “Close & Apply”.
The next step, as mentioned earlier, is to create a “Date” table.
- Close the “Query Editor” and go back to Power BI Desktop
- Click “Modeling” tab then click “New Table”
- Type in the following DAX code then press Enter
Date = var dateTable = CALENDAR(DATE(YEAR(MIN(Views[Date])),1,1), DATE(YEAR(TODAY()), 12,31))
return
SELECTCOLUMNS(dateTable, "Date", [Date]
, "Year", YEAR([Date])
, "Month", FORMAT([Date], "MMMM")
, "MonthOrder", MONTH([Date])
, "Year - Month", FORMAT([Date], "YYYY-MMMM")
, "YearMonthOrder", VALUE(FORMAT([Date], "YYYYMM"))
, "Week", CONCATENATE("Wk-", FORMAT(WEEKNUM([Date], 2), "00"))
, "Year - Week", YEAR([Date]) & " Wk-" & FORMAT(WEEKNUM([Date]), "00")
, "Week Day", FORMAT([Date], "DDDD")
, "WeekDayOrder", WEEKDAY([Date], 2)
)
- The code above creates a simple Date dimension starting from 1st Jan of the starting year up to 31st Dec of the current year. The reason for that is that we have to cover the whole date range to be sure Time Intelligence functions work precisely.
- Click “Mark as Date Table” from “Modeling” tab
- Select “Date” column from the drop-down list and make sure the date table is validated successfully, then click OK
- Hide “MonthOrder” and “YearMonthOrder” columns from “Date” table as we created those columns to sort “Month” and “Year – Month” columns
- Click “Month” column then click “Sort by Column” from “Modeling” tab and tick “MonthOrder”. This guarantees that Months names are always shown in the right order.
- You may do the same for “Year – Month” column and sort it by “YearMonthOrder”
- Create a relationship between “Views” and “Date” tables on ‘Views'[date] and ‘Date'[Date] columns by clicking “Manage Relationships” button from “Modeling” tab
- Click “New”
- Select “Date” from the first drop-down list
- Select “Views” from the second drop-down
- Select “Date” column from “Date” table and “date” column from “Views”
- Make sure “Cross filter direction” is set to “Single” then click OK
- Close the “Manage relationships” window
- Expand “Views” tables and hide “post_id” column as we don’t need it now
So far we created our model. Now it’s time to create some useful measures to analyse our WordPress site stats.
Creating Measures
You can now create some measures to get more insights about your website stats.
Below is a list of interesting measures you may create or perhaps you need a lot more that are not included in the list:
- Overall Views: Shows total number of site views from the beginning
- Total Views: Shows number of site views
- Most Popular Month: Shows Most popular month along with number of views on that particular month
- Most popular Week: Same calculation, but, for week numbers
- Most Popular Day: Same as “Most Popular Month”, but, at day level
- Most Popular Post: Shows the most popular post along with number of views
- Views MTD: MTD stands for “Month to Date” which shows cumulative number of views from the beginning of month till current date. The number resets at the beginning of each month.
- Views YTD: YTD stands for “Year to Date” which shows cumulative number of views from the beginning of year till current date, The number resets at first day of each year
- Views LMTD: LMTD stands for “Last Month to Date” which shows cumulative number of views from the beginning of previous month till same date as current date in the previous month. The number resets at the beginning of each month.
- Views LYTD: LYTD stands for “Last Year to Date” which shows cumulative number of views from the beginning of previous year till same date as current date in the previous year. The number resets at the beginning of each year.
- Views LY MTD: LY MTD stands for “Last Year Month to Date” which shows cumulative number of views from the beginning of same month as current month in previous year till same date as current date. The number resets at the beginning of each month.
With the above measures you can easily build very informative reports in Power BI to see how your WordPress website performs.
For instance you can compare “Views MTD” and “Views LY MTD” to see how your website is doing in current month in compare with the same period last year.
Download WordPress Stats Analyser
I Built the Power BI report and made the Power BI Template available for download.
Download “WordPress Stats Analyser” here.
Just open the PBIT file then copy paste the API URL of your website which is explained earlier in this post.
Looking at the above report you can quickly see how your website improved over time, what is the most popular month, the most popular week and the most popular date. You also can see the analysis of your website popularity over months. In the sample above you see that the most popular month is March with about 11% of total views of the website, while July is the least popular month with about 7% of total views.
If you hover over the three middle visuals you’ll see more insights built using Page Tooltips.
I hope you enjoy this Power BI Template. I’m really curious to see how you improve the report, so please let me know in the comments section below how you made it a better report.
