Connecting Google Data Studio to WP MYSQL tables

I’m planning to use Google Data Studio (GDS) to report on aspects of a Wordpress site. A lot of what I need to do is entirely new to me so I thought I’d reach out to see if anyone can offer advice.

I’ve built reports in GDS before, but only using Google sheets, so once the data connection is established I should be OK. It’s the data added by the Lifterlms plugin that I’m interested in by the way - so I’ll need to sort out a sql query to focus on the data I need, but I’ll worry about that later.

From reading around it appears I need

  • Host name or IP
  • Port
  • Database
  • Username
  • Password

but another source suggested I also needed to provide a read only link to prevent problems.

Initially I’ll create a fresh installation to test this all out because I have next to no experience of interacting with the database.

As a first step to scope out the problem I tried using the “Launch Database Manager”, but that led to " This site can’t be reached" error. Any ideas what might be going wrong there or advice on using the other methods of connecting to the database.

You need all that but you also have to allow remote access to the MySQL database.
https://support.cloudways.com/setting-up-remote-mysql-database-connections/

Having second thoughts about this.

I’d forgotten about plugin updates changing table structures and breaking the sql query. I need to use WP functions to extract data instead. Too much effort for too little gain.

Guess I’ll just wait until a proper integration comes along or move to a different LMS with front end reporting built in.

You don’t think there’s a suitable WP plugin?

There are plugins that extract statistics and such, e.g. https://sv.wordpress.org/plugins/wp-statistics/ (not affiliated)

Food for thought, I’ll need to look into this. After a quick look I did not see anything obvious that fits the bill, but I’m not knowledgeable in this area. I need to extract data from some custom post types (LifterLMS lessons, quizzes etc) and have it available as a data source GDS can take - sql queries linked to the database, Google sheets etc.
I’m also looking for an automated way to extract a list of user email addresses in a certain category (paid for a particular course / membership etc), so I can control external access to some of the members only resources I supply.

I’ve solved this myself in one of my web apps (fully custom and not WP-related) by implementing a script that abstracts the database access, then write a script at Google that serves as an interim between GDS and my service. It was simpler than it sounds and flexible (the user can choose what and how much data to download in detail for best performance, and GDS itself then asks for the data columns that the end-user wants to visualize), but of course it required that I knew exactly what data to retrieve and package it for GDS consumption. Maybe you could make such an interim script that would communicate via WP’s JSON API. I believe that would be possible.

It’s a nice idea but I don’t think I have the necessary coding skills. It’s not a mission critical issue, if I can’t find the right tools to achieve what I’m striving for then I’ll just work around it. I’m trying to keep quite a few balls in the air at once with my current project, pretending I’m a developer would be the last straw. Your link to the WP-statistics plugin has helped though. It’s a category of plugin I was unaware of - so I might find what I need when I eventually have the head space to do some effective research.

There are more similar plugins, but I tested this one and it provides a lot of info.

Follow these steps, they work for WP as well:

  1. Sign in to Data Studio .
  2. In the top left, click , then select Data Source.
  3. Select the MySQL connector.
  4. Configure access to your database using one of the connection options (see below)
  5. Click AUTHENTICATE. You will see a list of tables in that database .
  6. Select a table .
  7. Click CONNECT .
    Hope you find this helpful!
    Regards,
    Lewis