So one thing I’ve wanted to do for a while is to determine how many sessions each blog post has had since they were published. Since the URLs of my blog posts contain the date they were published, I tried to work this out using regex and some other methods. It got a bit messy and wasn’t very accurate.

Fast forward a few months and Google Data Studio has now added a DATE_DIFF function! This allows us to quickly and simply subtract today’s date from the date the blog post was published to determine how many days its been live on the site for. We can then divide the number of sessions by the number of days published to see which blog posts have generated the most sessions over time!

There also seem to be improvements in how Data Studio interprets strings with dates in them, as my blog post URLS can be easily understood as dates if I change the data type.

In this blog post I’ll show a method of using the new function DATE_DIFF in Google Data Studio to work out Sessions per day for blog posts

Below is the Google Data Studio report.

Step 1: How to get the date from your blog post title

So on my website all the blog posts contain the date they were published in their URLs.

For example in this URL you can see it was published on /2020/09/26.
https://michaelhoweely.com/2020/09/26/change-bubble-size-and-colour-in-a-google-data-studio-map-using-parameters/.

To extract the date from your page you can simply switch the data type from text to Date as shown below.

So below we have two columns, the left column being page as Date and the right column being Page as text. As you can see Data Studio automatically extracts and displays the date without us having to resort to Regex or anything like that.

Step 2: Use DATE_DIFF function to determine how many the blog post has been published for

DATE_DIFF is a recently introduced function. Read more about the Date functions available in Data Studio at this link.

Here is the formula to determine the number of days since the blog post has been published.

DATE_DIFF(TODAY(), Page (Date))

By subtracting today’s date with the date the page was first published we get the number days since the post was published.

Note: Set the ‘Days published’ metric to MAX and not SUM to get the correct number of days.

Step 3: Create a new field to measure the number of Sessions by Day

So in order to calculate the number of Sessions per day for each blog post.

SUM(Sessions)/MAX(Days published)

Below is how it should appear in Data Studio. We can name it Sessions per day to indicate the number of sessions / number of days since publication.

Step 4: Create a table showing these new metrics in Data Studio

So the final step is to create a table showing these metrics in Data Studio.

Dimensions = Page Title, Page (Date)
Metrics = Date published, Sessions, Sessions per day

My Other Blog posts

I hope you found this article about how to use the function DATE_DIFF in Google Data Studio useful. You might like my other blog posts.