Query API

CoolaData provides a query API that can be used  to query your project at any time. This enable you to create your own dashboards or easily integrate data from CoolaData with other tools.

Queries can be sent using GET or POST methods.

 

End Point

https://app.cooladata.com/api/v2/projects/[project_id]/cql

Replace the [project_id] placeholder with your actual Project ID.

 

Header

Authorization:Token [User_API_Token]
ContentType:application/x-www-form-urlencoded //optional

Replace the [User_API_Token] placeholder with your actual user API Token found in the user profile side-bar.

ContentType: application/x-www-form-urlencoded: Only relevant for method:post. This parameter can also be omitted if the content is already URL encoded. Other content types are not supported.

Optional Headers:

TIMESTAMP

The default format for time-stamp properties returned in the query API is: yyyy-mm-dd hh:mm:ss. Add this header (without content) to the query to get time-stamp properties in the format in which they were sent. For example, event_time_ts will be returned in Epoch time in milliseconds.

Data/Payload

tq=[query]
&noCache=true //optional
&tqx=out:csv //optional

Replace the [query] placeholder with your actual query. The query should be written in CoolaSQL (CQL).

Optional Parameters:

noCache=true

Add to the query to force the system to ignore cache. All query results are cached for 30 minutes to provide fast response time. After 30 minutes it will be refreshed, regardless of how many queries were made during this time. Using noCache will only make a difference if the same query was first performed in the last 30 minutes, and data has been altered since. This isn’t mandatory in the API.

tqx=out:csv

The default output of the query API is JSON. Add this to the query to get an output in the form of CSV instead.

 

Sample Invocations

The following queries includes both optional parameters for reference purpose – neither is mandatory.

Method: GET

The query should be URL encoded.

curl -H "Authorization:Token n8rwaqp6zj62yya3yj3pvysy7h9gvzn8" https://app.cooladata.com/api/v2/projects/123456/cql/?tq=select%20event_name,%20event_time_ts%20from%20cooladata%20where%20date_range(last%207%20days)%20limit%2010&noCache=true&tqx=out:csv

Method: POST

curl 
  -X POST
  -H "Authorization:Token n8rwaqp6zj62yya3yj3pvysy7h9gvzn8"
  -H "ContentType: application/urlencode"
  -H "TIMESTAMP"
  -d "noCache=true" \\optional
  -d "tqx=out:csv" \\optional
  -d "TQ=select event_name, event_time_ts from cooladata where date_range(last 7 days) limit 10"
  https://app.cooladata.com/api/v2/projects/123456/cql/

 

Response

The default output of the query API is JSON, in the following format:

{version,status,Number of rows,Processed bytes,Total query time,sig,table{cols,rows}}

Note: The query API result set is limited to 5M rows.

Sample Response:

{
	"version": "0.6",
	"status": "ok",
	"Number of rows": "15",
	"Processed bytes": "2952",
	"Total query time": "2129",
	"sig": "1880384400",
	"table": {
		"cols": [
			{
				"id": "0",
				"label": "date",
				"type": "string",
				"pattern": ""
			}
		],
		"rows": [
			{
				"c": [{"v": "2013-05-01"}]
			}
			,...
		]
	}
}

User Profile

User profile is a list of all your project users and their attributes, which include behavioral stats such as the user’s frequency or number of visits to your app, as well as customizable user attributes such as total deposit amount or last viewed item. In addition, all user scope properties last values are added to the user profile automatically.

The user profile attribute section allows you to create and manage a users table in your project that can be used for building reports, segments, filtering users based on their profile attributes as well as used for machine learning models.

To view your user profile attributes: Go to the project menu-> User Profile Attributes. Notice that User Profile is an add-on feature and once configured, only open for project ADMINs. Please contact your CSM or support@cooladata.com to set it up.

The user profile table runs once a day at 3 am UTC. The attribute values shown in the user profile table are always true according to the last run. Since Cooladata allows you to link between several identities of the same user, the user profile calculations are based on the Cooladata internal user id (user_id).

In the User Profile Attributes page, you can view all three types of user attributes:

  • SYSTEM: automatic behavioral attributes collected and stored by Cooladata on your users (i.e. total sessions count, frequency, last seen timestamp,  etc..). These attributes are added automatically as soon as the project is configured as open for User Profile
  • USER: last value of all user scope property sent to Cooladata. These attributes are added automatically as soon as the project is configured as open for User Profile and are added each time a new user scope property is created.
  • CUSTOM: customizable aggregative functions defined by the project admin ( i.e. number of unique page views, total deposit amount, etc.)

Defining a new custom user profile attribute:

  1. Go to the project menu-> User Profile Attributes and click on ‘+’ to add a new custom attribute.
  2. In the Enter Attribute Name field, type the name of the attribute. This will be also be the column name in the user profile table so make sure you enter a valid column name.
  3. In the builder choose the attribute type:
    • Custom – allows you to build a custom aggregation function per user. Choose an aggregation function and the field it should be calculated on:
    • count_events – counts the number of events that user had. Add a condition to specify the type of event you want to count.
    • count_sessions  – counts the number of sessions that user had. Add a condition to specify the type of session you want to count.
    • avg_events_per_session – calculates the average number of events that user had per session. Add a condition to specify the type of event you want to count.
    • avg_events_per_day – calculates the average number of events that user had per day. Add a condition to specify the type of event you want to count.
    • avg_sessions_per_day – calculates the average number of sessions that user had per day. Add a condition to specify the type of session you want to count.
  4. To add conditions to this attribute, click the filter icon on the right – filter. The following displays – enter any conditions (up to 5 per attribute):
    Notice that virtual properties are not available in user profile tables.
  5. In the during field – choose the date range for the attribute calculation:
    1. Entire project history will run on all available history for that user
    2. Last x days – will run only on the last amount of days chosen.

List of system User Profile attributes:

  • total_event_count  – counts the number of events that user had.
  • total_session_count –  counts the number of sessions that user had
  • average_events_per_session – calculates the average number of events that user had per session.
  • avg_session_duration – calculates the average duration of the user’s sessions (in milliseconds) 
  • maturity – the time in days from the first time the user was seen 
  • total_active_days – the number of days the user was active
  • frequency – the frequency of that user’s activity between 0 and 1 (total_active_days/maturity)
  • total_time_in_app – sums the total session duration the user had
  • event_versatility – Number of unique events (distinct event names) 
  • unique_device_count – Number of different devices the user used (based on the user agent) 
  • last_seen – the last timestamp the user was active
  • first_seen – the first timestamp the user was active

 

Querying the User Profile Table:

To query the User Profile Table, in a CQL query, as with any table in your project, state user_profile in the FROM clause, instead of “cooladata”:

You can also use this table as the data source for various report builders, such as KPI. See the report documentation for more information.

 

 

Delete API

*Please note that this documentation is for a BETA feature. Please contact support@cooladata.com or your CSM for more information.

The Delete API will allow you to delete any user properties or historical events and sessions of a user.
Keep in mind that if you do not opt-out the user and send any events for that user in the future, the data will be stored. In addition, since Cooladata does not control your Aggregation Tables, external data sources or data uploaded through our integrations, it is your responsibility to delete that user from these external tables.

The delete API will be carried out using customer_user_id (the original user_id sent from the event JSON, not the internal user_id Cooladata allocates per user). Since Cooladata allows you to link between multiple identities of the same user, the Delete API will also delete all the alternative identities of a single user, and their records, and not just the ones received with the user id that asked for the erasure.

The Delete API will queue the user requested for deletion and the Delete Status API will  let you know the status of request for deletion (whether it’s in progress or done).

Also, since every query in Cooladata is calculated based on raw event-level data, once these tasks are completed, the data retrieved from queries on top of Cooladata will change and will not include these events and sessions, even when querying aggregated values.

Notice – This action cannot be reversed, please use it wisely!

Delete API

Method

POST

API End Point

https://app.cooladata.com/api/v2/projects/{projectId}/users/delete

Replace the [project_id] placeholder with your actual Project ID.

Header

ContentType:application/json
Authorization:Token [User_API_Token]

Replace the [User_API_Token] placeholder with your actual user API Token found when clicking on your user in the application. The request will be permitted only for ADMIN user permissions.

Data/Payload

{ "reason":"<reason for deletion>",
 "userIds":
[<an array of comma separated customer user ids for deletion>]
 }
 

Response

The expected response is 200 OK (no body). If there was an error the body will include the reason for failure.

Sample Request:

[POST]:https://app.cooladata.com/api/v2/projects/113231/users/delete
headers:
Content-Type: application/json
Authorization: 07J2FBteX4dfgdfg431yWbO9jghkdfjg45YgfjlkoH3dCPmj
body:
{ “reason”:”testing delete api”, “userIds”:[“0e00e01b-2691-489c-93ac-74567d9e”,”4ad45365-7351-4c64-8605-25413dc216c7″,”b24f0b61-d6ce-427f-56g0-c41144504456″]}

response you should get if all is ok is 200 OK

 

Delete Status API

You can check the status of the delete request with the delete status API:

Method

POST

API End Point

https://app.cooladata.com/api/v2/projects/{projectId}/users/delete/status

Replace the [project_id] placeholder with your actual Project ID.

Header

ContentType:application/json
Authorization:Token [User_API_Token]

Replace the [User_API_Token] placeholder with your actual user API Token found when clicking on your user in the application. The request will be permitted only for ADMIN user permissions. 

 

Data/Payload

[an array of comma separated customer user_ids ] 

Response

{
 "<user_id1>": [
 {
 "status": "<status of request>",
 "deletedEvents": <number of deleted events>,
 "deletedSessions": <number of deleted sessions>,
 "statusUpdateDate": <timestamp of last update in msec>
 }
 ],
"<user id2>": [
{
"status": "<status of request>",
"deletedEvents": <number of deleted events>,
"deletedSessions": <number of deleted sessions>,
"statusUpdateDate": <timestamp of last update in msec>
}
]
}
 

 

Sending Data

Is there any size limit for each event I send?

Event size should not exceed 100KB.

I’ve noticed events on ‘Live Events’ view but cannot see them when I query.

There are two reasons that this is likely to happen:

  1. The events haven’t been loaded to the data warehouse yet (Google BigQuery), and thus cannot be queried. In this case, wait for about an hour until the data loading is completed
  2. Some of the events’ properties were not sent in the correct format (either wrong type, missing etc.) and were consequently sent as invalids. You can query all invalids up to the previous 7 days, using the following query:
    select * from invalids
    where date_range(last 7 days)

Why am I getting the following error, when I am sure this property exists in my project: “Field ‘session_duration’ not found in project, Coolalog no:5522517”?

This error occurs when trying to pull data from two different partitioned tables: events table, and session table. To solve it, join both tables using mutual property appears in both, e.g. user_id.
For instance, the following query will produce this error as event_name is a user scope property (saved in events tables), whilst session_duration is a session property (saved in sessions tables):

select event_name, session_duration
from Cooladata
where date_range (last 7 days)

Does Cooladata support multiple customer identities?

A user can start as anonymous user (hash key generated automatically), and then become a registered user. We support one old identity per user.
Once the user is sending both identities within the same event, we know how to convert it to the new identity.

Is session_id mandatory?

No

Which columns are automatically generated?

based on session_ip we are generating ip_country, ip_region, ip_city, ip_longtitue, ip_latitude.
based on DUA (device user agent) we are generating brand and model.
based on timestamp, we are generating multiple columns (hour, day, month, year, week…)

See our common properties documentation for more details.

Is the API Token the same as the App Key for using SDK implementation?

The AppKey is used to send events. It is the same regardless of how you send the events (REST, JavaScript etc.). However, the Query API is your user token and is used for querying the system. This can be retrieved by logging into Cooladata and clicking on the avatar in the top right-hand side of the screen.

How can I differentiate test data and organic data sent from my users?

There are several ways of differentiating between test data and real data:
A different project
A property
Both above methods require code intervention (in your app) to distinguish between real and test data.
In addition, if you have a distinct (and not too large) set of either devices or users that are generating test data, you can build segments that reflect those “test users”/”test devices” and filter them out in the dashboard slicers.

Handling Personally Identifiable Information (PII)

Cooladata takes the utmost precautions to ensure the security of your data in the cloud and continually upgrades with the latest security options.

Cooladata accepts any event properties that you send without filtering them. However, even so, we advise you not to send sensitive personal information (such as credit card numbers) that may help a malicious entity identify someone.

Here are a few tips for protecting personal information:

  • Conceal personally identifiable information. For example, by scrambling, cloaking, encrypting, faking or hashing it.
  • Send a person’s location, instead of their IP address.
  • Send only partial information, such as a person’s country instead of their IP address.
  • Do not send combinations of information that may help someone piece together who the person is, such as session IP, address, gender and age.
  • Read here for the feature we have released in order to help our customers comply with GDPR and other PII regulations.

Segments

Segmentation can be used to simplify queries, compare segments, analyze specific segments, and generate actionable lists quickly and easily.

Creating and using segments is open to all users, when non-admin users can only update the segments they created (like reports).

Creating Segments

Creating a segment using the Cohort Report

To create a segment from any cohort report:

  1. Open a cohort report, in the report editor or dashboard.
  2. Mark any section of the table by clicking on it: individual cells, whole rows or whole columns.
  3. From the top right of the preview area, click “Explore users” and select “Create segment”.
  4. In the popup that was opened, enter the desired segment name and click “Create segment”.
  5. A save confirmation message will appear. To edit the segment query or set an update schedule, click the link in the confirmation message to go to the segment editor page.

create segment from cohort

 

Creating a segment using the CQL Editor

You can use the CQL editor interface to create a segment. To create a new segment using the segment builder:

  1. Go to Project / Segments in the main menu.
  2. Click the “+” button, and select “CQL Editor”.

This can be used to:

  • Edit segments created from a report.
  • Create a segment based on a query you already use.
  • Create complex segment queries (not available in the segment builder).

Note to adhere to the following rules:

  • A segment must contain only user_id: no matter what query you use, the final response must include a single column containing “user_id” values to work as a user segment. If your query contains additional properties, precede it by “SELECT user_id FROM (…) ” to isolate the “user_id” column.
  • A segment should contain only unique values: to avoid getting duplicate results, end your query in “GROUP BY user_id”.

The default segment query template, available when opening a new CQL Editor segment, already includes the required syntax specified above. Edit it or replace the FROM clause to keep the correct syntax. Note that invalid syntax will not be possible to save.

select user_id
from cooladata
where date_range(last 7 days)
group by user_id

Segment CQL editor

 

Creating a segment using the Segment Builder

The segment builder provides an easy to use interface for creating advanced behavioral segments. To create a new segment using the segment builder:

  1. Go to Project / Segments in the main menu.
  2. Click the “+” button, and select “Builder”.

The builder consists of conditions and filters.

Conditions include the following 3 options:

  • Users who did anything: select a segment of users who were active in the specified time period.
  • Users who did specific events: select a segment of users who did any of a list of specific event in the specified time period.
  • Users who had a comparative KPI value: select a segment of users whose KPIs in the specified time period were above/below a value. Allows you to select from preset behavioral KPI templates or define any calculation on any property using custom KPIs.

You can add up to 5 conditions in a single segment query, for example: to filter by consecutive behaviors – by choosing different time periods for each condition, or to drill down into your segment further by intersecting several condition types.

Filters:

  • You can filter any specific condition based on any property. For example, users whose purchases are below $10 by calculating the SUM of “cost” in event “buy”.
  • You can filter all users in the segment based on any user/session property. For example, only include users from the US by filtering “ip_country” to be in “United States”.

segment builder

 

Segments List

To see and manage all saved segments in your project go to Project – Segments in the main menu.

Segments list

From here you can:

  • See a list of saved segments
  • Edit saved segments
  • Delete saved segments
  • See the run (update) history of each segment
  • Run (update) a segment manually
  • Create new segments

 

Using segments in reports and dashboards

Segmentation can be used to simplify queries, compare segments, analyze specific segments, and generate actionable lists quickly and easily.

Filtering by segment

Narrow down your reports and dashboard by showing only users from specific segments.
To filter by segments:

  1. Open any filter: report, sheet or dashboard.
  2. From the properties list, choose “segment”.
  3. Select the segments you want the user to be part of. Enter multiple segments if the users must be part of all of them, i.e. the intersection of the segments. For example: include both “new_users” and “new_shoppers” to only show users who made their first purchase and are also new to the site.
  4. You can include several segment conditions in one filter using the AND operator. However, note that the OR operator is not allowed in conjunction with segment filters.

filter by segment

Cohort report by segment

In any Cohort report, group users into cohorts based on their segments and compare between them.
To cohort by segments:

  1. Open any Cohort report
  2. In the “Group cohorts by” section, choose “segment”.
  3. Select any segments you wish to include in report.
  4. Optional: add “Other” to the segments list to also include users who do not belong to any of the segments you specified.

cohort by segment

KPI breakdown by segment

In any KPI report, add a breakdown by segments to show which segment the user belongs to.
Note that users that belong to more than one segment will appear multiple times – once for each segment.
To break down a KPI by segments:

  1. Open any KPI report
  2. In the breakdown (bottom) section, choose “segment” from the first row options.
  3. Select any segments you wish to include in report.
  4. Optional: add “Other” to the segments list to also include users who do not belong to any of the segments you specified.

kpi breakdown by segment

Funnel breakdown by segment

In any Funnel report, add a breakdown by segments to split the funnel steps to the segments and compare between them.
Note that users that belong to more than one segment will appear in each segment.
To break down a funnel by segments:

  1. Open any Funnel report
  2. In the breakdown (bottom) section, choose “segment”.
  3. Select any segments you wish to include in report.
  4. Optional: add “Other” to the segments list to also include users who do not belong to any of the segments you specified.

funnel breakdown by segment