Query API

The CoolaData API enables you to query your project data so that you can use it to create your own dashboards or to integrate it with other (non-CoolaData data) tools and presentation options. Queries can be sent using GET or POST methods.

API Endpoint

Use this API to retrieve data from CoolaData by sending a query.

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

project_id – Insert your project ID here.

Getting Your Project’s ID

To get your project’s ID –

  1. In the CoolaData Administrator console, select your project in the dropdown menu at the top right.
  2. If you are the project’s Admin user, then select Project projecticon and then select the Settings option from the menu. Your project ID is the six-figure integer under Project ID.

– OR –
If you are not the project’s Admin user, go into Project è Tracking and copy the six-figure project ID from the displayed URL, as shown below –

7-1

Header

Header

Specify the authorization and content type (optional) of the header.

Authorization (Mandatory)

To get your CoolaData User_API_Token:

Open your workspace in the CoolaData Administrator console and click on the Profile 7-2-1 icon in the top right of the window. Then, copy your user token from the API Token field, shown below.

7-2

Content Type (Optional)

Add the following line for a Post method. This line can be omitted if the content is already URL encoded. Other content types are not supported.

 Payload

Specify your CQL query to retrieve data from CoolaData.

  • query – Place your CQL query here.

Example –

Parameters (Optional)

Each of the following parameters affects the result that is returned. All these parameters are optional.

noCache

Add this to the query in order to specify that CoolaData query the database directly – and not query the cache.

tqx

By default, query data is returned as a JSON. This parameter enables you to specify that the query data is returned in a CSV file instead.

  • tqx – Specifies the filename to contain the query results.

For example, the following specifies that up to 100 user_IDs from the last seven days are stored in the out.CSV file.

Filename

This parameter enables you to return the results of the query that is larger than the API size limit of 250K rows to a file in a Google Storage bucket.

  • Filname – Specifies the name of the Google bucket file in which the results will be saved.

Before using this option, contact your CoolaData Customer Success Manager or our support support@CoolaData.com in order to request that they open a dedicated Google bucket for you for this purpose.

Sample Invocation

The following queries demonstrate how to use both mandatory and optional parameters.

Note Do not use the filters (context) and the date_range (context) features in CQL queries run via Query API. This is because there is no way for the system to know the context of this kind of query – and therefore the query will fail.  Instead write an explicit date range or a relative date range.

Method – GET

This query is URL encoded.

Method – POST

Response

Here’s the default format of the API JSON query response:

Note – The API query response is limited to 250 K rows.

 GSSC 1To see how to retrieve a larger response in a Google bucket.

Sample Response

Print Friendly

Integrations Overview

CoolaData Integrations enable you to import external data from multiple sources and integrate it with the behavioral data of your CoolaData project.

CoolaData provides two primary types of integration options –

  • Predefined Data Sources CoolaData provides a variety of predefined connections to standard external data sources. This type of integration is typically used to integrate third-party data into CoolaData – for example in order to calculate campaign ROI. Each integration runs periodically in order to retrieve data and pour it into the tables of your CoolaData project.
  • Uploading a Data File – This option enables you to upload the content of any CSV or JSON file from a Google Cloud Storage bucket or an email report into CoolaData. This type of integration is typically used to enrich your CoolaData project with your own data. This option can also be used to upload a different scheme that is different than the predefined one provided out-of-the-box by CoolaData for integration with third parties. For example, to upload Google AdWords data in a scheme that is different than the predefined one provided by CoolaData.

GSSC 1To read more about uploading files.

Creating a Predefined Data Sources

To create a new CoolaData Integration –

  1. In the CoolaData Administrator console, select your project and then the Integrations 7-7 button or select the Project projecticon button and then the Integrations menu option. A list of the active CoolaData integrations is displayed.

7-8

    • Type – One of the predefined integration data sources, such as Google Adwords and so on.
    • Name – The name you assigned to this integration.
    • Last Time Executed – The last time CoolaData checked the data source in order to integrate data. This timestamp specifies when CoolaData last ran the integration to the data source, and not necessarily when it found files and integrated them.
    • Table Name – The name of the table (or tables) created in CoolaData.
    • Status – The status of the last integration execution – which is once a day.
    • Data Fetched – CoolaData has integrated the files from this data source.
      • For error messages, contact your CoolaData customer success representative or write to support@CoolaData.com.
  1. To create a new integration, click the Add + button or the Add Integration button. The following displays –

7-9

Select one of the predefined integration options –

    • Google Cloud Storage
    • Report Email
    • Google AdWords
    • Facebook Ads

For instructions regarding other integration options, contact your CoolaData customer success representative or write to support@CoolaData.com.

Print Friendly

Google Cloud Storage

CoolaData provides an integration option that enables you to upload the content of any CSV or JSON file from a Google Cloud Storage bucket into your CoolaData project. This type of integration is typically used to enrich your CoolaData events with your own data.

CoolaData creates a dedicated bucket for you in Google Cloud Storage and then automatically uploads the file name and file type that you specify every hour.

To upload content into CoolaData from a Google Cloud Storage Bucket –

  1. Contact your CoolaData customer success representative or write to support@CoolaData.com. Ask them to create a Google Cloud Storage bucket for you into which you can dump your CSV and JSON files to be integrated into CoolaData.
  2. Upload your files into this Google Cloud Storage bucket. For example, by using the UPLOAD FILES button in the Google Cloud Platform user interface. You could also use the gsutil tool in order to automate this process. See the following link for some useful commands: https://cloud.google.com/storage/docs/gsutil

7-14

  1. Follow the instructions in Integrating with Predefined Data Sources and then select Google Cloud Storage 7-15. The following displays –

7-16

  1. Fill in the following –
    • Integration Name – The name of this integration.
    • Load data only once – Check this option to specify that CoolaData only loads the data from your CoolaData bucket once. Otherwise, data is loaded every hour, if available.
    • Upload Data Format – Select CSV or JSON to specify the format of the files to be uploaded from your CoolaData bucket. JSON files must be flat (not nested) newline – delimited.
    • File Name – The file name to be uploaded. Make sure to add the file type ending, such as csv/json.
    • Use File Name as Table Name – Check this box to specify that the name of the table that is created in CoolaData is the same as the File Name (described above). This is the table name to be used in the queries that you will perform on the uploaded data. If you choose this option, then make sure that the file name is a valid table name.
    • Table Name – If you did not select the option above, then enter the name of the table to be created in CoolaData to contain the data that is uploaded. Like all table names, it is case sensitive and cannot include spaces or special characters.
    • Append Date to the Table Name – Appends the date when the table is created to the table name. A new table partition is created for each date. Its format is TableName_YYYYMMDD. Selecting this feature enables you to use Google’s BigQuery Data Partitioning feature. You may contact your CoolaData’s customer success representative to hear more about this feature.
    • File Scheme – Define the scheme of the columns of the table to be uploaded by defining the name and data type of each column. Click the Add + button to add each new column. The following data types are supported – string, integer, float, Boolean and timestamp. For example, name:STRING, id:INTEGER, birthdate:TIMESTAMP.
    • In the Insert Strategy field, select either –
      • Append New data is added to the table each time data is uploaded.

– OR –

      • Replace The table is overwritten each time the data is uploaded.
    • In the Emails to Notify If the Failure field, type in the emails to whom to send integration upload and failure notifications.
    • [Optional] You can use the Google Project ID and Google Dataset fields to define that CoolaData uploads data from your CoolaData bucket into your own Google project instead of into your CoolaData project. Contact your CoolaData customer success representative for more information.
  1. Click the Save button. Each hour CoolaData will then integrate the files that are dropped into this bucket into your project. The first integration process should take place within a few minutes.

After CoolaData has integrated the files into your CoolaData project, the CoolaData Google bucket is emptied. The files are moved to a subfolder named Uploaded in the bucket and a timestamp is appended to the beginning of the file name. The syntax is – YYYYMMDD_filname.filetype. For example, 20160823_stats.csv.
The Status column of the Integrations list changes to show Data Received.
If the integration process fails for any reason, then the file is moved to a subfolder in the bucket named Failed. An email is sent to the specified recipient(s) (described above) alerting them regarding the integration failure. The same filename convention would be applied in the Failed folder as well.

  1. Data can now be queried using the following syntax.

Print Friendly

Email Report

CoolaData enables you to upload data contained in file attachments (CSV, JSON or Excel files) received by a Gmail account into your CoolaData project. This type of integration is typically used to enrich your CoolaData project with your own data or to upload reports you receive regularly for querying alongside your behavioral data.

Each hour, CoolaData searches the subject of incoming emails (of the Gmail account that you specify) in order to search for a specific string in the email’s subject and then uploads their content into your CoolaData project. CoolaData learns the schema of these file attachments by understanding the values of each property.

Note: Only Gmail is supported. You can use any Gmail account: business/private/freemium.

  1. Follow the instructions in Integrating with Predefined Data Sources and then select Email Report .
    The following displays:
    7-18
  2. Fill in the following:
    • Integration Name: The name of this integration.
    • Date Format: Fill in this field if the file attachments to be uploaded contain date fields. Specify the format of the date fields: as they are in the file. CoolaData can then automatically convert these date fields to timestamp format. Only one format can be defined. For example, the format of the date 18/04/2016 16:21:16 should be entered here as: dd/MM/yyyy HH:mm:ss.
      GSSC 1To read about the date syntax that you can use to define a date format in CoolaData.
    • Report File Delimiter: Select Comma or Tab to specify the delimiter between each data row and column in the email attachment.
    • Email Subject: Enter a string that specifies which emails are to be integrated by CoolaData. This can be any string, including a complete or partial name. All incoming emails that contain this string in their subject are checked for an attached CSV, JSON or Excel file. The data the attached file of matching emails is then integrated into CoolaData. We recommend making this string as unique as possible, in order to avoid CoolaData attempting to integrate irrelevant emails. If needed you can define more than one integration for the same Gmail address/inbox: each containing a different subject: and each being stored in a different table.
    • Table Name: The name of the table that is created in CoolaData to contain the data that is uploaded. This is the table name to be used in the queries that you will perform on the uploaded data. Like all table names, it is case sensitive and cannot include spaces or special characters.
    • In the Insert Strategy field, select either:
      Append: New data is added to the table each time data is uploaded.
      OR
      Replace: The table is overwritten each time the data is uploaded.
      Note: If the Insert Strategy field is set to Append, then the schema of each file attachment must be identical. Otherwise, the integration fails.
    • Add Date Column to Table: Select this option to specify that a date column is added to each table that is created by CoolaData when a new email attachment is uploaded. This date specifies the timestamp when the integration took place. This option is particularly useful when the uploaded reports do not include dates. This option is also helpful when you use the Insert Strategy: Append (described above), because it indicates when each row was added to the table.
    • [Optional] You can use the Google Project ID and Google Dataset fields to define that CoolaData uploads the email attachments into your own Google project instead of into your CoolaData project. Contact your CoolaData customer success representative for more information.
  3. Click the Connect button. The following displays:
    7-18
  4. Enter the Gmail account credentials of the email inbox to be monitored by CoolaData. If you are already logged into your Gmail account in a different window on the same computer, then there is no need to enter your Gmail account credentials. Click the Allow button.
    Each hour CoolaData will search for an email with the specified project and if found, it will then integrate the relevant email attachments into your project. The first integration process should take place within a few minutes.
    After CoolaData has integrated the files in your project, the email is labeled in Gmail as Uploaded.
    7-20
    The Status column of the Integrations list changes to show Data Fetched.
    If the integration process fails for any reason, then the email is labeled in Gmail as Failed. An email is sent to the specified recipient(s) (described above) alerting them regarding the integration failure.
  5. Data can now be queried using the following syntax.

 

Date Format

Email Report integration enables you to upload data from reports that are emailed to you from various sources.

CoolaData automatically converts the date and the Gmail email attachments according to the date syntax that you define in the Date Format field. The following formats are supported:

  •  “yyyy-MM-dd HH:mm:ss”
  •  “yyyy-MM-dd HH:mm:ss.SSSSSS”
  •  “yyyy-MM-dd HH:mm:ss,SSS”
  •  “yyyy-MM-dd HH:mm:ss:SSS”
  •  “yyyy-MM-dd HH:mm:ss:SSS”
  •  “yyyy-MM-dd HH:mm:ss.SSS”
  •  “yyyy-MM-dd’T’HH:mm:ss.SSSSSS’Z'”
  •  “yyyy-MM-dd’T’HH:mm:ss,SSS’Z'”
  •  “yyyy-MM-dd’T’HH:mm:ss:SSS’Z'”
  •  “yyyy-MM-dd’T’HH:mm:ss:SSS’Z'”
  •  “yyyy-MM-dd’T’HH:mm:ss.SSS’Z'”

For example, the format of the date 18/04/2016 16:21:16 should be dd/MM/yyyy HH:mm:ss.

The date syntax is case sensitive, so (for example) MM represents month and mm represents seconds.

Execution Frequency  – Hourly

Letter Date or Time Component Presentation Examples
G Era designator Text AD
y Year Year 1996; 96
M Month in year Month July; Jul; 07
w Week in year Number 27
W Week in month Number 2
D Day in year Number 189
d Day in month Number 10
F Day of week in month Number 2
E Day in week Text Tuesday; Tue
a Am/pm marker Text PM
H Hour in day (0-23) Number 0
k Hour in day (1-24) Number 24
K Hour in am/pm (0-11) Number 0
h Hour in am/pm (1-12) Number 12
m Minute in hour Number 30
s Second in minute Number 55
S Millisecond Number 978
z Time zone General time zone Pacific Standard Time; PST; GMT-08:00
Z Time zone RFC 822 time zone -800

Examples:

Date Format
04-18-15 MM-dd-yy
42112 dd-MM-yyyy
42112.6823264 dd/MM/yyyy HH:mm:ss
42112.6819444 dd/MM/yyyy hh:mm aa
Tue 10/20/15 EEE MM/dd/yyyy
Print Friendly

Google AdWords

CoolaData provides a predefined connection that uses the Google AdWords API to enable you to upload the Google AdWords reports into CoolaData. This type of integration is typically used to enrich your CoolaData project with AdWords data, such as CTRs (Click Through Rates) versus keywords and costs, in order to link your marketing efforts to your CoolaData project data.

CoolaData automatically uploads the latest Google AdWords reports from your account once a day and fetches yesterday’s data. New data is appended to the tables stored in CoolaData.

To upload Google AdWords data into CoolaData –

  1. Follow the instructions in Integrating with Predefined Data Sources and then select Google AdWords 7-21. The following displays –

7-22

  1. Fill in the following –
    • Integration Name – The name of this integration.
    • [Optional] You can use the Google Project ID and Google Dataset fields to define that CoolaData uploads data from your Google AdWords report into your own Google project instead of into your CoolaData project. Contact your CoolaData customer success representative for more information.
  1. Click the Connect button. The following displays –

7-23

  1. Enter the Google AdWords account credentials to be monitored by CoolaData. If you are already logged into your Google AdWords account in a different window on the same computer, then there is no need to enter your Gmail account credentials.

Click the Allow button.
Once a day CoolaData will then integrate data from the following three Google AdWords reports into your project. You can click on each table below to see the schema details.

The Integration ID (which is part of the file name) is displayed in parentheses in the CoolaData Administrator console, as shown below –

7-24

The first integration process should take place within a few minutes.
The Status column of the Integrations list changes to show Data Fetched.
If the integration process fails for any reason, then an indication appears in the Status column of the Integration List.

  1. Data can now be queried using the following syntax.

SELECT * from tablename limit 100

adwords_geo_integrationID

For example, Table Name – adwords_geo_4202

date TIMESTAMP
country STRING
source STRING
type STRING
impressions FLOAT
clicks FLOAT
cost FLOAT
integrationtime TIMESTAMP

adwords_clicks_integrationID

For example, Table Name – adwords_clicks_4202

ad_type STRING
ad_group_id FLOAT
ad_group STRING
network STRING
network_with_search_partners STRING
campaign_id FLOAT
campaign STRING
click_type STRING
ad_id FLOAT
keyword_id FLOAT
keyword__placement STRING

adwords_campaigns_integrationID

For example, Table Name – adwords_campaigns_4202

currency STRING
account STRING
time_zone STRING
active_view_avg_cpm FLOAT
active_view_viewable_ctr STRING
active_view_viewable_impressions FLOAT
active_view_measurable_impr__impr STRING
active_view_measurable_cost FLOAT
active_view_measurable_impr FLOAT
active_view_viewable_impr__measurable_impr STRING
network STRING
network_with_search_partners STRING
advertising_sub_channel STRING
advertising_channel STRING
all_conv_rate STRING
all_conv FLOAT
all_conv_value FLOAT
budget FLOAT
avg_cost FLOAT
avg_cpc FLOAT
avg_cpe FLOAT
avg_cpm FLOAT
avg_cpv FLOAT
pages__session FLOAT
avg_position FLOAT
avg_session_duration_seconds FLOAT
base_campaign_id FLOAT
bid_strategy_id FLOAT
bid_strategy_name STRING
bid_strategy_type STRING
conversion_optimizer_bid_type STRING
bounce_rate STRING
budget_id FLOAT
campaign_id FLOAT
mobile_bid_adj STRING
campaign STRING
campaign_state STRING
campaign_trial_type STRING
click_assisted_conv FLOAT
click_assisted_conv__last_click_conv FLOAT
click_assisted_conv_value FLOAT
all_conv_value FLOAT
budget FLOAT
avg_cost FLOAT
avg_cpc FLOAT
avg_cpe FLOAT
avg_cpm FLOAT
avg_cpv FLOAT
pages__session FLOAT
avg_position FLOAT
avg_session_duration_seconds FLOAT
base_campaign_id FLOAT
bid_strategy_id FLOAT
bid_strategy_name STRING
bid_strategy_type STRING
conversion_optimizer_bid_type STRING
bounce_rate STRING
budget_id FLOAT
campaign_id FLOAT
mobile_bid_adj STRING
campaign STRING
campaign_state STRING
campaign_trial_type STRING
click_assisted_conv FLOAT
click_assisted_conv__last_click_conv FLOAT
click_assisted_conv_value FLOAT
clicks FLOAT
content_lost_is_budget STRING
content_impr_share STRING
content_lost_is_rank STRING
conv_rate STRING
conversions FLOAT
total_conv_value FLOAT
cost FLOAT
cost__all_conv FLOAT
cost__conv FLOAT
crossdevice_conv FLOAT
ctr STRING
client_name STRING
day TIMESTAMP
end_date STRING
engagement_rate STRING
engagements FLOAT
enhanced_cpc_enabled STRING
enhanced_cpv_enabled STRING
customer_id FLOAT
gmail_forwards FLOAT
gmail_saves FLOAT
gmail_clicks_to_website FLOAT
impr_assisted_conv FLOAT
impr_assisted_conv__last_click_conv FLOAT
impr_assisted_conv_value FLOAT
unique_cookies STRING
impressions FLOAT
interaction_rate STRING
interactions FLOAT
interaction_types STRING
invalid_click_rate STRING
invalid_clicks STRING
budget_explicitly_shared STRING
label_ids STRING
labels STRING
month TIMESTAMP
month_of_year STRING
phone_impressions FLOAT
phone_calls FLOAT
ptr STRING
_new_sessions STRING
budget_period STRING
company_name STRING
relative_ctr STRING
search_lost_is_budget STRING
search_exact_match_is STRING
search_impr_share STRING
search_lost_is_rank STRING
campaign_serving_status STRING
start_date TIMESTAMP
tracking_template STRING
custom_parameter STRING
value__all_conv FLOAT
value__conv FLOAT
video_played_to_100 STRING
video_played_to_25 STRING
video_played_to_50 STRING
video_played_to_75 STRING
view_rate STRING
views FLOAT
viewthrough_conv FLOAT
week TIMESTAMP
integrationtime TIMESTAMP
Print Friendly