Querying

How can I retrieve event_time_ts in different formats?

Event_time_ts shows only minutes because that is the format our UI supports. However, you can drill down to the seconds and milliseconds by using CQL functions. For instance:

  • time(event_time_ts) returns time of the event in HH:MM:SS format
  • string(event_time_ts) returns time of the event in HH:MM:SS.ms format
  • timestamp_to_msec(event_time_ts) returns event time in milliseconds

What is the default timestamp format?

%Y-%m-%d %H:%M:%S

How do I query a specific range of hours within a single day?

date_range(between TIMESTAMP('YYYY-MM-DD HH:MM') and TIMESTAMP('YYYY-MM-DD HH:MM'))

How do I query a 24 hours data, referring to my local time?

Journey Analytics timestamp is based on UTC standard. So, you first need to check for time differences.For instance, if Israel is UTC +3, and you wish to query the entire 24 hours of a specific date (for instance July 8th, 2016), the query should look as follows:

select hour(date_add(event_time_ts, 3, 'hour')) as israel_hour, count(*) as cnt
from Cooladata
where date_range(between 2016-07-07 and 2016-07-09) and
date_add(event_time_ts, 3, 'hour') >= timestamp('2016-07-08 00:00:00')
and date_add(event_time_ts, 3, 'hour') < timestamp('2016-07-09 00:00:00')
and filters(context)
group by 1
order by 1

Does Last 7 days include today?

The last N days includes all the last N days up until yesterday, not including today.

Is there a limit of rows for exporting CSV file in Journey Analytics app?

Yes. there is a limit of 20k rows for exporting via Coola app.

What does the 'Can Edit' permission enable me to do on a dashboard?

Sharing a dashboard with Edit permissions, it enables you to add or remove reports from the dashboard, reposition them, but it does not give you the ability to edit the reports themselves.When someone else creates a report – you cannot edit it unless you are the project's Admin.This is because these reports can be used in other dashboards or publications that you might not have access to.

Is there a query quota?

Journey Analytics limits the maximum rate of incoming requests and enforces appropriate quotas. Specific policies vary depending on resource availability, user profile, service usage history, and other factors, and are subject to change without notice.

The following limits apply to query-type function calls.

  • Concurrent rate limit: 1 TB of concurrent queries + 1 additional query of unlimited size, up to 20 concurrent queries.
  • Daily limit: 20,000 queries.
  • Maximum query length: 256 KB1
  • Maximum response size: 128 MB compressed2

1Approximate size based on compression ratios.2Sizes vary depending on compression ratios for the data; the actual response size may be significantly larger than 128 MB.

I got this error: "External data source format is invalid" – what should I do?

This is likely to occur when querying an external database you manage, and the firewall settings are not configured to allow Journey Analytics's IP addresses.These are the two IP addresses you need to allow access to:

  • 52.2.68.228
  • 52.44.248