Google Workspace, BigQuery and Looker/Data Studio

Question

I wonder if someone is able to help me out here.

My company is a Google Workspace based company, we pipe our data through to BigQuery using the settings within the Admin Panel. That all works fantastically and there is no issue there.

Within BigQuery, one of the data points is time_usec - which is the Epoch time. In one table (activity), this is done in Micros which is fine as I can transform the data in BigQuery using TIMESTAMP_MICROS(time_usec).

However, in Looker/Data Studio we are bringing the data in its raw format. I have tried the calculated field TODATE(time_usec, "MICROS", "%x") which certainly transforms the data for display purposes but it doesn't appear to work with Date Ranges.

To explain this better:

Here is an example of the data in BigQuery. This is part of the Activity table with:

• time_usec being epoch time (in microseconds)
• email being the user account (every activity that is logged has a time_usec entry)
• device_type being the type of device (everything from Windows machines, to iOS etc)
• device_model being the model of the device
• os_version being the version of the OS running on the device

To be fair, the data for the most part is irrelevant here (other than the time_usec) but I'm adding it to give a better idea of the type of data.

time_usec	email	device_type	device_model	os_version
1659952732837000	new.user.3@somemailservice.com	DESKTOP_CHROME		ChromeOs 14816.131.0
1659952299942000	some.email@somemailservice.com	WINDOWS	HP EliteBook 850 G5	Windows 10.0.19044
1659952366245000	new.user.3@somemailservice.com	DESKTOP_CHROME
1659952736142000	new.user.3@somemailservice.com	DESKTOP_CHROME		ChromeOs 14816.131.0
1659945047719000	some.email@somemailservice.com	WINDOWS	HP EliteBook 850 G5	Windows 10.0.19044
1659959338167000	another.email@somemailservice.com	DESKTOP_CHROME	HP Elite Dragonfly Chromebook	ChromeOs 14909.100.0
1659959340697000	another.email@somemailservice.com	DESKTOP_CHROME	HP Elite Dragonfly Chromebook	ChromeOs 14909.100.0
1659961092792000	some.email@somemailservice.com	WINDOWS	HP EliteBook 850 G5	Windows 10.0.19044
1659958186331000	another.email@somemailservice.com	WINDOWS	HP COMPAQ PRO 6305 SFF	Windows 10.0.19044
1659957469855000	some.email@somemailservice.com	WINDOWS	HP EliteBook 850 G5	Windows 10.0.19044

Here is how the connection is set up in Looker/Data Studio:

Here is how the time_usec field in configured in Looker/Data Studio:

Here is the page in Looker/Data Studio:

• The column titled Date with the red underline is using TODATE(time_usec, "MICROS", "%x").
  • This is configured as a Date Range Dimension using the above method, then added as a normal dimension after. For the purpose of displaying a date, this works fine.
• The yellow box gives us the ability to select a date range. In the image it is set to 'Auto date range' and it works fine. However, if I change this to, let's say the last 14 days it returns No data on the table.

As far as the Data itself is concerned, the time_usec field is set as a Number with no default aggregation. I have seen other questions about this answered in the past, with people saying that you can configure the field to be a Date rather than a number. However, attempting to do so produces the following message:

Looker Studio can't convert time_usec (Number) to a Date or Date & Time because it doesn't recognize the date format. Possible solutions:

• Change your data to use a supported format
• Create a calculated field to convert time_usec to a valid date. For example, to convert "202011" to a date consisting of year and month: Example: PARSE_DATE("%Y%m", time_usec)

I have also tried PARSE_DATETIME("%x", time_usec) and PARSE_DATE("%x", time_usec) as a calculated field. Again, for the purpose of displaying a date these seem to work fine. However, when then appying the date range it breaks with a message saying:

Am I doing something wrong here? I would rather not mess with the data at the BigQuery level. And I know I can use custom SQL to do the TIMESTAMP_MICROS(time_usec) then bring it in. But surely there is a better way to do this within Looker/Data Studio?

EDITED: Added an example table with data and some config screenshot.

Answer 1

I searched for a long time for a solution to my similar problem.

I had the time in Unix microseconds. This is how I converted it to a date:

PARSE_DATETIME("%d-%m-%Y %H:%M:%S", CONCAT(DAY(time_usec, "MICROS"),"-",MONTH(time_usec, "MICROS"),"-",YEAR(time_usec, "MICROS")," ",HOUR(time_usec, "MICROS"),":",MINUTE(time_usec, "MICROS"),":",SECOND(time_usec, "MICROS")))

Answer 2

here's a formula that's working good on my end with date range control filter as well. please try it

DATE_FROM_UNIX_DATE(CAST(Time Only/86400000000 as INT64))