Google Data Studio

Google Data Studio – String to date calculated field

How to create a calculated date field inside Google Data Studio using substring elements (texts) from page URL.


Text (string) respecting a specific format

For date dimension, you just need to create a string (text) respecting the format below and define it as date type 📅

YYYYMMDD

Calculated field formula

So, we need to open the data source editor panel and create a calculated field concatenating year, 2-digit month and 2 digit-day.
Here a static example corresponding to “Christmas date” dimension.

CONCAT(
	"2019"
	,
	"12"
	,
	"25"
)

Use case: Blog post publication date from URL

Let’s create a real life example: I wish to find correlations between my website traffic and post publications dates.
My blog posts URLs contain the related publication dates.
Let’s see an example:

https://www.wissi.fr/blog/analytics/20190725/google-data-studio-treemap-as-horizontal-filter-menu/

To grab this date and create a calculated date dimension “Date of post publication”, you just need to apply the formula below.

This version is reusable following your URL pattern, so possibly with various locations for each information (x3).
For my particular case, I could have simplified it because my URL contains the right format but let’s do it with a generic formula.

I use Google Analytics page dimension combining the following functions:

  • REGEXP_EXTRACT to extract year, month and day
    • following the position of each number
    • using the same regex and just changing the position of “(…)” group for extract selection
  • CONCAT to build YYYYMMDD default date format

CONCAT(
  REGEXP_EXTRACT(Page,"^/blog/analytics/([0-9]{4})[0-9]{2}[0-9]{2}/(.+)$")
  ,
  REGEXP_EXTRACT(Page,"^/blog/analytics/[0-9]{4}([0-9]{2})[0-9]{2}/(.+)$")
  ,
  REGEXP_EXTRACT(Page,"^/blog/analytics/[0-9]{4}[0-9]{2}([0-9]{2})/(.+)$")
)

Field type

After saving the calculated field corresponding to the formula above, select the YYYYMMDD Date and time type on the data source fields list.

This step is not required for some data source types like Google Analytics because Google Data Studio recognizes automatically the field type. In this case, just check if the field type is correct.


Google Data Studio - Date type for calculated field

Now, the new calculated dimension is ready to be used in your report.


J’ai le choix…