Analytics
2.3K members online now
Understand information in your reports and troubleshoot reporting issues such as self-referrals, (not set) data, and inaccurate information
 
Guide Me
star_border
Reply

Wrong start date in Google Sheets - Analytics report.

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi

I created a custom GA report within Google sheets (with GA add-on). The report is configured to extract facebook data from GA for the past 8 weeks.

The Start date and End date is autmatically calculated with a formula

We usually run the report every Monday, and usually it is fine.

 

However, today we ran the report and found the report had pulled in data from an extra day before tha strart date, even though the Start date had been set.

 

Here's the config and data we got after runing the report:

Start Date 04/10/15
End Date 28/11/15
Last N Days
Metrics ga:transactionRevenue, ga:sessions
Dimensions ga:nthWeek
Sort
Filters ga:source=~facebook

ga:nthWeek ga:transactionRevenue ga:sessions

0                  853.11                           1386

1                  25978.4                          24017

2                  37696.75                        25716

3                  49937.14                        17154

4                  55462.17                        26006

5                  32183                             17216

6                  85989.3                         28482

7                  23710.03                        15977

8                  38303.68                        28825

 

 

 

 

 

 

Appreciate any help as to why it is ignorinng the config rule and picking up a previous day for the Start date?

 

Ange

2 Expert replyverified_user

Re: Wrong start date in Google Sheets - Analytics report.

Top Contributor
# 2
Top Contributor
Hi Ange,

I've no actual answer to your questions but it maybe helps to just use "Last N Days: 7" instead of specific start/end dates if you run weekly reports?

Christoph

Re: Wrong start date in Google Sheets - Analytics report.

Top Contributor
# 3
Top Contributor
Try including the Date dimension into the query and see what is happening. It seems to be making the first 'nth week' only 1 day long, which may be a bug.
Mike Sullivan, Google Analytics Top Contributor
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.

Re: Wrong start date in Google Sheets - Analytics report.

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭
The report is meant to pull out the last 8 business weeks. (Sunday to Saturday).
We run it on a Monday to pull out the laast 8 weeks from that Monday

Re: Wrong start date in Google Sheets - Analytics report.

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭
I have tried that and that's where I saw the problem that is was pulling in an extra day
It does not fix the issue and yes it seems like a bug because it was working for the last couple of months and this has just happened just once yesterday for this particular report

It also happened once another time on a different report but then normalized the next day. I haven't seen it happen again on that report.
These reports are meant to be automated (lots of data ) and hence can be time consuming to extract daata manually when it suddenly stops working

Just wondering if anyone has any better ideas. I tried changing the 'sampling' to higher precision but doesn't fix it.

Re: Wrong start date in Google Sheets - Analytics report.

Top Contributor
# 6
Top Contributor
Yes, but you said it was pulling an extra day - include the Date to see how it combines that day into the 8 weeks. Does the first week have 8 days?
Mike Sullivan, Google Analytics Top Contributor
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.

Re: Wrong start date in Google Sheets - Analytics report.

Top Contributor
# 7
Top Contributor
Mark,

so, what's about "Last N Days: 56" instead of specific start/end dates? Shouldn't it do the job?

Christoph

Re: Wrong start date in Google Sheets - Analytics report.

Top Contributor
# 8
Top Contributor
An interesting problem....computers should be extremely consistent, so the question is what changes? I don't like leaving bugs unresolved...

I know the GA Sheets add-on is sensitive to the regional settings in the Sheet for displaying date formats...maybe there is a regional impact from getting the date in your worksheet and converting it for the API? i.e. maybe this is a timezone-related problem?

First, check your regional settings in the Sheet. Then if you try the report very early in the morning vs noon time vs very late at night, can you recreate the problem? Maybe the addon is getting the date from the sheet in one timezone and converts it to a date for the API that is operating in a different timezone -- with some combinations being a day apart? OR the regional settings in the View are offset from your local regional settings...OR some other strange combination of the above. A simple query of Date and Users would be a good test. if you can recreate the problem, it goes a long way to understanding the cause and coming up with a solution you can rely on.
Mike Sullivan, Google Analytics Top Contributor
Was my response helpful? If yes, please mark it as the ‘Best Answer.’ Learn how here.

Re: Wrong start date in Google Sheets - Analytics report.

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭
Thanks Christoph! I have to use specific start and end dates because I need it to automatically update each day. For Start date, I am using the formula: =today()-57 So if I run it on a Monday, it picks up the last 8 weeks with the Start date being a Sunday business week. This way it is automated. Setting up "Last N Days: 56" does not solve anything. It only forces it to run from the last 56 days which isn't what I am after. So if I run it on Monday,"Last N Days: 56" would pick the day before (Sunday) which isn't what I want. As I said before the previous business week is from Sunday-Saturday. It should not pick up the latest Sunday. Anyway, the formula is not the problem here, it is supposed to pick up the Start date no matter what formula you use. If the formula is set to run from the 4th October as a Start Date, it should only pick up data from the 4th, not the 3rd. Ange

Re: Wrong start date in Google Sheets - Analytics report.

Visitor ✭ ✭ ✭
# 10
Visitor ✭ ✭ ✭
Thank you!
I agree..computers should be consistent.

And yes, I have thought about the possibility of regional settings in the sheet.This is the only closest answer I have come to given that it behaves inconsistently once in a while and is hard to reproduce.


I run it around the same time on Monday mornings, as I said, it has been fine most of the time.
The last time I tried to run it and it failed was end of month (latest Monday was the 30th), perhaps this has something to do with it..