Analytics
5.7K members online now
Discuss discrepancies between AdWords and Google Analytics data, linking AdWords and Google Analytics, importing your goals/Ecommerce transactions into AdWords, and setting up Remarketing
 
Guide Me
star_border
Reply

Creating a rolling bar chart

[ Edited ]
Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello,

 

I'm trying to create a rolling weekly bar chart based on Google Analytics data.

Our weekly dashboard contains variety of metrics such as Users, Sessions, Bounce Rate etc.

For each of those we would like to have a bar chart to display the trend for the past few weeks (let's say last eight weeks). 

So the table should display eight bars each one representing Last Week, Previous Week, Two Weeks Previously, Three Weeks Previously etc.

 

The Date dimension in GA does not contain a 'Week' value. And I'm not sure how to construct the formula in the Calculated Field in order to create the necessary weekly data aggregations to match with Last Week, Two Weeks Previously, Three Weeks Previously etc. 

 

I also couldn't find any reference in the Help portal that seemed relevant. 

 

Any help would be greatly appreciated.

 

Michael

1 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Michael F
March 2017

Re: Creating a rolling bar chart

Rising Star
# 4
Rising Star

Can you not schedule your reports in Google Sheets? If you use the Google Sheets GA add on this allows you to automatically refresh your reports. 

Screen Shot 2017-03-03 at 08.18.51.png

John Wedderburn, Advertiser Community, Rising Star
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.

View solution in original post

Creating a rolling bar chart

Rising Star
# 2
Rising Star

Google Analytics does contain a week value. Search for 'week' here:

https://developers.google.com/analytics/devguides/reporting/core/dimsmets

 

What are you building your dashboard in?

John Wedderburn, Advertiser Community, Rising Star
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.

Creating a rolling bar chart

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi John,

 

Thanks for your reply.

I've actually ended up using two functions in Google Sheets to calculate the start and end of the last eight weeks: 

=today()-weekday(today(),2)-55

=today()-weekday(today(),2)

 

Then ran the report in Google Sheets using the dimension ga:isoWeek against a bunch of metrics (ga:users, ga:sessions etc.).

 

The only snag is that I still need to manually refresh the report in Google Sheets every week.

 

I then connect that sheet with Data Studio which is the visualisation tool I'm using.

 

Not perfect but it works.

 

Any improvement suggestions (especially around getting the process completely automated) would be greatly accepted.

 

Cheers,

Michael

Marked as Best Answer.
Solution
Accepted by topic author Michael F
March 2017

Re: Creating a rolling bar chart

Rising Star
# 4
Rising Star

Can you not schedule your reports in Google Sheets? If you use the Google Sheets GA add on this allows you to automatically refresh your reports. 

Screen Shot 2017-03-03 at 08.18.51.png

John Wedderburn, Advertiser Community, Rising Star
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.

Creating a rolling bar chart

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Yes, that is very true John. Thank you.

 

I'm an Adobe Analytics person. Does it show? ;-)

 

Take care,

Michael

Creating a rolling bar chart

Rising Star
# 6
Rising Star

But now you are a Google Analytics person. Resistance is futile.

John Wedderburn, Advertiser Community, Rising Star
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.