12/07/2020

Calculating the Age of a Blog Post with CURRENT_DATE in Google Data Studio

Google Data Studio recently introduced a new function called CURRENT_DATE in September of 2020. Hallelujah! Now calculating the age of a blog post is easy. To start calculating the age of a blog post, the post's permalink must have a year and month embedded in it. The format of a blog post permalink URL should be

http://mydomain.com/YEAR/MONTH/nameofpost.html

where YEAR is a 4 digit year and MONTH is a 2 digit month.

To extract the year and month of a blog post, you can use the following calculated field formula, where Page is the page path Google Analytics dimension of a link (without the domain name).

Month of Blog Post
substr(Page,7,2)

Year of Blog Post
substr(Page,2,4)

To calculate the age of the blog post we use this monster of a calculated field. We will break down the nested formula functions piece by piece:

floor(
date_diff(
current_date(), parse_date('%Y%m',
  concat(Year of Blog Post,Month of Blog Post)
                          )
         )
       / 31
      )

First let’s concatenate Year of Blog Post and Month of Blog Post with the concat() function. This will create a year and month string that can be parsed into a date.

For example, a blog post with /2010/10 in the URL will have 201010 passed into the parse_date() function. The parse_date() function uses the format mask %Y%m to convert this date string into a proper DATE type. Without a day of month component the default day is the first of the month ( 1 ). Therefore the parse_date() function will return October 1, 2010 for 201010.

To calculate the difference between today's date and the date of the blog post let's use the date_diff() function along with the brand new current_date() function. The date_diff() function will return the difference between two dates in days. To get the age of the blog post in months let's divide the date difference by 31, the number of days in most months.

Lastly, let's use the floor() function to round down the age in months. And voila! Using the URL you can accurately compute the age of a blog post. This calculated field can now be used with a session or page view count for each URL to compute the lasting traffic generation capability of a post. Blog posts that continue to generate traffic long after they have been posted indeed are well optimized for searches.

Blog posts gds

 

Comments

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment