google-sheetsgoogle-bigquerycalculated-columnslooker-studiodate-arithmetic

Data/Looker Studio - Custom Date Range in Calculated Field


/Edit 1

I created a mockup of the expectation here:

https://lookerstudio.google.com/s/iObb1VOlRGY

While the report above works, the problem I'm having is the date range. It does not have dates 14, 30, and 365 days from the original post date of the landing page.

How do we get the post date for the landing page?

For now it seems the easiest way is to use a spreadsheet like this:

https://docs.google.com/spreadsheets/d/1m233aPHPVF0vYLzwYl-rUBvWL3oPDOxtakX0MPpiOFA/edit?usp=sharing

So I'm trying to blending the two data series together, the Google Sheet will be the point of reference for custom date ranges for Analytics to pull pageviews for the table. How can I make this work?

If there is a better way to arrive at the expectation in Google Data Studio Report for the custom date range, Let me know!

/Original Post

So I'm trying to create a table in Data Studio using Google Sheets + Google Analytics.

Google sheet is:

URL Page Post Date
/this-is-a-page-url/ 10/1/2022
/this-is-also-a-page-url/ 5/1/2021

Table in Looker Studio Needs to Display:

URL Page Views 14 days from post date "" 30 Days "" 1 Yr
/this-is-a-page-url/ 5 10 200
/this-is-also-a-page-url/ 1 50 5000

My question is, Is this possible with Data Studio?? (not pro, and not looker)

I have tried nearly everything I can think of to make this work. Really it's the date range as a calculated field that is the major trouble. If there is a method for this let me know. Any help appreciated.


Solution

  • I have tried solving the question in the Looker studio link you shared, https://lookerstudio.google.com/s/iObb1VOlRGY

    First, I have created a blend of tables first Google Analytics ( dimension - Url and date ; metrics - Views), and second Google sheet ( dimension - Url , Date posted) taking Url as left join condition. Then create a chart field for each condition i.e. 14 days, 30 days etc.

    ##Condition to use ---

    case when date_diff(Date, Date Posted)<=14 then Views else 0 end

    I have created a table in report itself.

    One more correction you have to parse the date posted column in google Sheets into date as its format is text that will give error. Then use that in formula instead of 14 days date filed.