AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
4K members online now
4K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Data Studio not picking up date metrics from Google Sheets

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I'm trying to set up a very simple report using data pulled in from a Google Sheet. Several of the columns are dates in the format YYYY-MM-DD but only one is recognised in this format by Data Studio. The others are seen as 'Text'. Although I can change the type to a date format the metrics then don't pull through if I use that dimension as my date dimension. 

 

I've refreshed and reconnected the data source many times and the report is picking up all the metric totals across all dates for each of the dimensions in question, it just doesn't when I select a specific date dimension period. I know the metrics are in the data source within the date dimension period as I've checked using simple =countifs formulas, but it just won't adjust correctly in the report. 

 

This is super frustrating and basically renders the report useless. Any ideas or suggestions to what I could be doing wrong?

1 Expert replyverified_user

Data Studio not picking up date metrics from Google Sheets

Explorer ✭ ☆ ☆
# 2
Explorer ✭ ☆ ☆

From what I've experienced, you can only use one field (column) for a date range in GDS. So if you have more than one column with dates, you cannot use the date range to control for all of those dates.

 

One workaround for this might be to use filters instead of a date range. This way, you can create filters for each date column, and then give the end-user the ability to filter out certain dates.

Data Studio not picking up date metrics from Google Sheets

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks Corey. Frustrating but very helpful to know. I'll take a look at the filters as a workaround. 

Data Studio not picking up date metrics from Google Sheets

Google Employee
# 4
Google Employee

Hi Kirsten,

 

Sorry for the frustration Smiley Sad

 

You can have as many date fields in your data source as you want. The fact that those other fields are not coming through as dates leads me to believe something else is going on with the data. (Are they all full 4 digit year, 2 digit month, and 2 digit day dates in the Sheet?)

 

Regards,

 

Rick

Data Studio not picking up date metrics from Google Sheets

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi Rick

 

Thanks for your reply. Good to know we can pull multiple date fields into DS. All the date data in my Google Sheet source is in the full 4 digit MMDDYYYY format - this was the first thing I verified. I have four columns of dates, all of which I need to use as dimensions and metrics in order to calculate conversion ratios. The first and fourth column of dates seem to be pulling through into DS correctly, columns 2 and 3 don't. They are all formatted identically. 

 

DS is a fantastic product but it seems from the research I've done around this problem that many other users have experienced the same issue and are having to use filters or QUERY functions in their source data to manipulate it into separate sheets/data sources for each date field, so I'm not alone in this frustration. Perhaps something for the Google DS team to investigate further?

 

Thanks again for replying.

 

Kirsten

 

 

Data Studio not picking up date metrics from Google Sheets

Google Employee
# 6
Google Employee

Hi Kirsten,

 

Thanks for the additional information.

 

Can you clarify what you mean when you say columns 2 and 3 don't "pull through" to Data Studio? Do you mean they don't appear in the data source? Or they appear but are not formatted as dates? Or that you can't use them as date range dimensions in the report? Or something else?

 

Could you share a copy of the Google Sheets data with us at the data-studio-help@google.com address? Put a link to this thread in the sharing note so I can find it.

 

Thanks!

 

Rick

Data Studio not picking up date metrics from Google Sheets

[ Edited ]
Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭
Hi Rick
 
Picking this up from some time ago but what I mean is that columns 2 and 3 pull through into Data Studio but they aren't recognised as dates - they are always seen as text. Although I can then manually change the format to YYYYMMDD, DS then doesn't recognise them as date dimensions from which I can create metrics. For example, if I create a 'count' metric from the dimension related to column 1 (which DS recognises as a date) I get a correct result for my metric. If I create a 'count' metric from the dimension related to columns 2 and 3 having manually changed them to YYYYMMDD, I get a 0 or incorrect metric returned. I'm checking against the totals in my source data so know the number being returned by DS is wrong. 
 
If I download my Google Sheet data into a csv file and create a data source by uploading this csv file, DS recognises two of my date columns as dates - the third is still being seen as text. 
 
I've created numerous data sources in Sheets now with multiple date columns and have used them as test data sources in a variety of reports - some created from a Blank, some from the templates provided, with exactly the same result.
 
It's incredibly frustrating and renders DS useless for the reporting I need to run :-(
 
 

Data Studio not picking up date metrics from Google Sheets

Google Employee
# 8
Google Employee

Hi Kirsten

 

I'm sorry this continues to be frustrating!

 

Can you share the Google Sheet (or just a sample of it) with data-studio-help@google.com? If you could also share the data source you've built off that, that could be helpful.

 

If the data is sensitive you can obscure that: I just want to know how the fields are defined in Sheets, and how they are being seen by Data Studio.

 

Finally, to reiterate what Corey said earlier, you need to specify just one field as the date range dimension for a chart. You can have different ones for different charts, but only one dimension can control the date range for a given component. (Eg. you can't blend dates from 2 different columns, or build a date range control to use Date 1 OR Date 2, etc.)

 

Best regards,

Rick

Data Studio not picking up date metrics from Google Sheets

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

Thanks Rick

 

I understand that it's one dimension per chart, the challenge I've had is getting DS to recognise date columns from my Sheet as YYYYMMDD so I can select them as a date dimension in the first place. 

 

After much trial and error (and swearing!) I think I've established the issue and a workaround. 

 

The issue seems to be stemming from empty cells in the date columns within my Sheet - a complete column of dates is recognised as YYYYMMDD but as soon as there are empty cells DS sees the data as text. For example, I have a column that records Introduction Date and a second column of that records Presentation Date. The Row corresponds to a buying Opportunity. Every Opportunity has been introduced so there is a date in every cell of the Introductions column. However, not all Introductions lead to a Presentation, in which case the cell in the Presentation Date column might be blank. This continues through a series of columns recording dates for other events in the buying process until we get to Contract Complete Date. At each stage in the process, the number of blank cells in the column increases as Opportunities are filtered out. 

 

All my date columns are formatted as YYYYMMDD in Sheets.

 

I want to use each of these columns as a date dimension in Data Studio, however, DS only recognises the full column of Introduction Dates as YYYYMMDD. The other columns are pulled in, but seen as Text. When I change the data type for these columns to YYYYMMDD within DS, the metrics thrown out are incorrect. For example, I know that we've recorded 61 introductions in the last 30 days and have made 8 presentations. The count metric for the Introduction Date is correct. However DS is giving me a count metric for Presentation Date of 4. In other words, when the data type is manually corrected within DS, it does not pick up or recognise all the data. 

 

To solve the issue I've used a datevalue/if/isblank formula in my original Google Sheet to fill in these blanks with an 'articial date' that lies outside my main date range. Once all the date columns are complete, DS reconises them as YYYYMMDD and I can use each of them as date dimensions with a filter to exclude my artificial filler date. It's a faff, but it seems to work and I'm now getting the correct metrics.

 

Thanks for your help in trying to find a solution - I guess these pains are what the beta phase is for!

 

Kirsten

Re: Data Studio not picking up date metrics from Google Sheets

Google Employee
# 10
Google Employee

Hi Kirsten,

 

Again, apologies for the frustration (and swearing!).

 

That is very weird that blanks in the date columns are causing them to be seen as text. I actually couldn't reproduce that: I have a worksheet with dates in different formats, so I deleted some of the data in one of the columns and created a new data source: the field with missing dates came through fine:

 

2017-09-28_10-02-21.png

 

 2017-09-28_10-01-59.png 

 

Have you tried creating a new data source with that data? 

 

If you can provide an example of the data,  maybe I can spot something in there that's causing Data Studio to think it's text and not date data. Otherwise, I hope your solution / workaround is sufficient.

 

Best regards,

Rick