Ad

Blending Data Sets Google Data Studio (Google Analytics + WordPress)

I am trying to show complex data inside Google Data Studio which includes posts of a WordPress blog and Google Analytics Pageviews of them.

Each of these posts has certain Pageviews record, so I need to fetch and show them inside a table. Also I need to show Pageviews of their AMP version as well.

In order to do that, I connected to the WordPress SQL database inside Google Data Studio. Now I am able to show all posts, on the other side I connected my Google Analytics to show pages with Pageviews.

Now I need to bind them with a Join Key. On one side we have page name which is the slug of that page and the other side (on Google Analytics) we have a path to the page.

Question is how to match the slug with the path name on Google Analytics.

Sample page name on Google Analytics:

/mag/post-name

or

/mag/amp/post-name

Sample WordPress slug:

post-name

Is there a way to formulate the condition of the Join Key such as:

WHEN GA_Page_name is like WP_post_name
Ad

Answer

It could be achieved using the REGEXP_EXTRACTCalculated Field below which accounts for a trailing / (where Field represents the respective Google Analytics field):

REGEXP_EXTRACT(Field,"/([^/]*)/?$")

Google Data Studio Report and a GIF to elaborate:

Ad
source: stackoverflow.com
Ad