Posted on

I am amazed at how little attention the new Google Data Studio has gotten. I’ve been working with complex systems out there; There are a ton of “data studios” which are extremely complex and need trained people to operate and deliver something, usually not that pretty, on the screen.

The Google Data Studio is simple and offers great integration of the default data sources, especially Google Analytics.

However, it’s not limited to Google data. And that’s a huge step! Today I’ll show you how you can import WordPress Data to create something like the following dashboard:

(yes it’s a simple dashboard, and yes it’s called a “dummy” for a reason.)

The interesting part is the first table with WordPress data, namely:

  1. Post titles.
  2. Associated publishing dates.
  3. The authors’ display name (ok it’s all me, but for collaborative blogs, this might be of interest).
  4. The number of posted comments (take that as a hint to post more comments!).

First, let us walk through the scheme of the WordPress database to build an SQL query which we then use for our connector.

A Quick Prior on the WordPress Database Structure

If you’re not familiar with the database behind your blog, that’s ok. If it’s on WordPress, this will work, and all you need is the access data to the database itself.

We want data on the posts, which is in the table called wp_posts and data on the authors, which is in the table called wp_users.

The wp_posts table contains a column with the name post_author containing the author id. With that, we can search for the correct author in the wp_users table. So basically, we are looking at the two following tables:

The green highlighted fields are the ones we want to display in our table and we have to join on the post_author. That is a n:1 relation, one author may have a bunch of posts, in my case, a single author has all the posts.

The following SQL query will do the job.

SELECT wp_posts.post_title,wp_posts.post_date,
wp_posts.comment_count,wp_users.display_name
FROM wp_posts
INNER JOIN wp_users ON
wp_posts.post_author=wp_users.ID
WHERE wp_posts.post_status = ‘publish'

The WHERE clause is to exclude everything else but the actual posts. For instance, multiple drafts might be saved in the same table and have a post_status set to “draft”.

If you want to have the full description of the WordPress database head over to the Codex.

The SQL Connector

Now let us create a new data source, using a MySQL Connector. Select the “BASIC” option and paste in all your SQL information. Usually, you get that either from your IT department or from your web hosting service panel. Look for any option/tab that’s called “SQL server” or the likes.

Next hit “Authenticate”, then paste in the query, maybe check the ’s and retype them.

then hit “Connect”. We get to our data source edit section which should now look like this.

You can click on the fields and edit their names, or calculate something new and interesting out of them, some kind of weighted comment count comes to my mind.

The Table

Finally, in the last step, we can either select “Create Report” straight out of the data source, or add the data source to an existing report by opening the existing one, creating a table and adding this source.

For a new one simply drag a table across the report, and make sure to select the following options.

  1. Select the correct data source.
  2. Select the dimensions you want to display (dimension are the things without numerical values: Text, dates,…). In our case those are the title, date and the authors’ display name.
  3. Select the metrics you want to display. In our case that is only the comment count.

If you actually want to use this, you probably also want to have a filter to select only posts within a specific date range, alongside a different table with traffic data.

To get that to work, you’ll have to modify the data source. Specifically, you’ll have to duplicate the field called “post_date” and then edit the field ID. I’ve already outlined the process in my other post on a filter for multiple data.

What’s Up Next, Joining the Data Sources

I know what you’re all thinking. Can’t I put this into one table? Currently, in Data Studio, the answer is NO, you cannot join different data sources. The workaround is to perform the join somewhere else, namely

  1. either in Google Sheets (take the free plugin for Google Analytics)
  2. or in “a” SQL database.

but since I find the data studio so beautiful because it’s so simple, I won’t bother you with those options right now.

By svenbalnojan

Leave a Reply