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
4.3K members online now
4.3K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Data Studio, issue with metrics and dimensions

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I have data in BigQuery (BQ) and the Data Source is available in Data Studio but there I have some issues:

Data Studio seems to always import data from BQ integer as Number metric, and BQ strings as Text dimension. In my case I had the data in BQ in numeric format but still in the type of String in BQ. Even though I manually changed the type in Data Studio/Edit Connection to Number it is not activated. I tried temporarily to change the schema in BQ (but that’s not a realistic workaround in a live case) to Integer, which makes it visible as Number in Data Studio/Data Sources, but Data Studio still complaints about the data being in String format, and I get Configuration error of “0dd10e1e”. Any ideas on how to resolve this?

 

/Erling

 

2 Expert replyverified_user
1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Erling M
July 2016

Re: Data Studio, issue with metrics and dimensions

Google Employee
# 10
Google Employee
In that case, Data Studio should work just fine. I have verified using a table of my own with similar characteristics.

At datasource creation time, Data Studio marked the field as TEXT. In the screen that lists the fields of the datasource, I modified the type of the particular field to NUMBER and selected an aggregation function for it (say SUM).

I can then use this field as a metric.

Now, if the BQ table contains values for this field that are not parse-able as number (e.g. 'abc'), the report will fail because the aggregation operation (SUM() in my example) cannot handle non-numeric values.

I hope that helps.

View solution in original post

Re: Data Studio, issue with metrics and dimensions

Visitor ✭ ✭ ✭
# 2
Visitor ✭ ✭ ✭
I managed to find the answer to this issue myself; in my case I had multiple BQ tables in the same dataset. Earlier tables had the original schema with this field of type String and later dates had the field as integer. This confused Data Studio, for good reasons, when I removed the old table it worked again. Generally I suspect there is a bug in Data Studio in this regard that the type I set when adding a data source do not fully override what is in the source BQ table. Additionally I would prefer if I could have Data Studio data source field of arbitrary type and still use them as Metric or Dimension freely, so there is no need to change the type of the BQ source data.

/Erling

Re: Data Studio, issue with metrics and dimensions

Google Employee
# 3
Google Employee
Converting strings into numbers is not allowed - even though the documentation does not make that clear and the user interface allows you do that (sorry!). I am afraid you need to configure the proper data type in the underlying data.

By the way, casting numbers as strings (the reverse) is an allowed operation.

Re: Data Studio, issue with metrics and dimensions

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭
Thanks for this answer Dimitris.

However it may be difficult to reconfigure the underlaying data. It may e.g. be data generated by some other team or even another company . I may be able to create a new BQ table where the field types are adjusted to fit Data Studio, that is possible, but it is still an additional task which also would duplicate the data, so I don't think that is the best way of creating a flexible tool, instead I would very much prefer the Data Studio to be able to handle this itself. Would it be possible?

/Erling

Re: Data Studio, issue with metrics and dimensions

Top Contributor
# 5
Top Contributor
Hi Erling, it's either that or pull data out of BQ regularly and have it processed by an ETL of sorts before using it as a data source for DS.
VP & Chief Evangelist at Hub'Scan | Contact me
Level 80 Digital Analytics Warrior, KPI Therapist and Keeper of the One True Tagging Plan

Re: Data Studio, issue with metrics and dimensions

Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭
Hi Julien, I haven't heard about ETL. I tried searching for it but found no really good information. Can you point me in the right direction?

I would really prefer that Data Studio would cope with the data at hand. Other tools may have other constraints and theoretically they could be conflicting so to adjust the underlaying data to the tools seems not to be a good way to me.

Perhaps I'm just missing some obvious solution here, and perhaps ETL is the key?!

/Erling

Re: Data Studio, issue with metrics and dimensions

Top Contributor
# 7
Top Contributor
Sorry, I should have mentioned that ETL is a generic term (Extract, Transform and Load) for data processing.
Look for products such as Talend Open Studio. (http://www.talend.com)
VP & Chief Evangelist at Hub'Scan | Contact me
Level 80 Digital Analytics Warrior, KPI Therapist and Keeper of the One True Tagging Plan

Re: Data Studio, issue with metrics and dimensions

Google Employee
# 8
Google Employee
Erling, please help me make sure I understand your situation:

- You have a BQ table that has a field (column) declared to be a STRING
- The field actually contains numbers. To verify that, run the following query from the BQ Web Interface :
SELECT SUM(INTEGER(X)) FROM T; (assuming T is your table and X is the field in question)
- You would like to use this field as a metric in Data Studio and thus you want Data Studio to treat it as a NUMBER

Correct so far?

Re: Data Studio, issue with metrics and dimensions

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭
Hi Dimitri,

I have changed the schema of my underlaying BQ table to be able to get it working again so I cannot easily try the query you proposed any more, but I can confirm that your understanding is correct.

/Erling
Marked as Best Answer.
Solution
Accepted by topic author Erling M
July 2016

Re: Data Studio, issue with metrics and dimensions

Google Employee
# 10
Google Employee
In that case, Data Studio should work just fine. I have verified using a table of my own with similar characteristics.

At datasource creation time, Data Studio marked the field as TEXT. In the screen that lists the fields of the datasource, I modified the type of the particular field to NUMBER and selected an aggregation function for it (say SUM).

I can then use this field as a metric.

Now, if the BQ table contains values for this field that are not parse-able as number (e.g. 'abc'), the report will fail because the aggregation operation (SUM() in my example) cannot handle non-numeric values.

I hope that helps.