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
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
Year of Blog Post
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:
Year of Blog Post,
Month of Blog Post
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.