WordPress Reporting using Power Query - Excelerator BI

WordPress Reporting using Power Query

I recently created a Book Shop on my WordPress website using WooCommerce.  The next logical thing I wanted to do was to get an extract of all my orders/invoices that have been sold through the shop into Excel.  I couldn’t find a way to do this in the WordPress UI or via a free plug in, and then it occurred to me that maybe I could extract the data from the underlying WordPress Database using Power Query – and indeed you can.  This is how I did it.

Enable Remote Access to your Database

Log into cPanel for your website and go to “Remote mySQL”

image

By default there should be one IP address set up for access – this is your web server.  You need to add your home IP address to the list (or the IP address from where ever you are located and want to access the DB).  You can find out your IP address at http://www.whatsmyip.org/.  If you have a static IP address, you may want to use a service like Dyndns or similar http://lifehacker.com/the-best-free-alternatives-to-dyndns-1561556205 to maintain a static URL.  This is not essential; the other approach you could use is to just update your IP address each time you need to.

image

Power Query Online Training

Connect to the Database with Power Query

Open Excel, go into Power Query and commence the import process

image

Enter your Server Name and Database Name.  For the server, you can either use the web site IP address you found when you first opened cPanel (not your home IP address) or you can use your main hosting website address.  If in doubt, contact your ISP and ask them.  The database name is the name of the WordPress DB you set up when installing your account.  You can find this by going into cPanel and selecting “mySQL Databases”

image

The first time I did this, I got an error saying I didn’t have the right drivers.

mySQL connector error

I just followed the link in the error, downloaded the drivers and installed them.

image

After installing the drivers, I tried to log in – and it worked fine.

Find the tables you need and import them

You can use this approach for extracting any data from your WordPress DB.  In my case, I was looking for the WooCommerce transaction tables so I could get a list of my invoices. I downloaded the following tables

image

Build a Data Model in PowerPivot

I joined the tables using the Order ID and order Item ID as shown below

image

Power BI Online Training

I then built some simple DAX measures for Quantity, Price etc as follows

image

Total Products := CALCULATE(sum(Detail[meta_value]),Detail[meta_key]=”_qty”)

Product Val Tax := CALCULATE(sum(Detail[meta_value]),Detail[meta_key]=”_line_tax”)

Product Val ex Tax := CALCULATE(sum(Detail[meta_value]),Detail[meta_key]=”_line_subtotal”)

Product Val incl Tax :=[Product Val Ex Tax]+[Product Val Tax]

Power BI DAX Book

In WooCommerce, I set up the invoice number to be the Order ID with a prefix ‘s’.  I just repeated that same logic in is a calculated column in DAX that follows the same rules.

And I ended up with a Pivot Table Report like this.

image

Hope that helps someone experiment with accessing WordPress Data using Power Query.

If you want a comprehensive lesson on how to use Power Query, check out my training course here https://exceleratorbi.com.au/power-query-online-training/

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top