Google Cloud Storage

Setup

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
  3. Follow the instructions in Integrating with Predefined Data Sources and then select Google Cloud Storage:
    7-16
  4. 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.
  5. 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.

Data can now be queried using the following syntax:

SELECT * 
FROM tableName

 

Date Format

Define the Date Format to recognize and convert timestamps columns so they can be used as such in Cooladata. All JAVA standard time formats are supported. The following characters can be used – 1-3 chars represents the abbreviated form, if one exists; 4+ chars represents the full form. The date syntax is case sensitive, so (for example) MM represents month and mm represents seconds.

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-17 MM-dd-yy
18-04-2017 dd-MM-yyyy
18/04/2017 19:36:55 dd/MM/yyyy HH:mm:ss
18/04/2017 07:36:55 dd/MM/yyyy hh:mm aa
Tue 04/18/2017 EEE MM/dd/yyyy
Print Friendly, PDF & Email

Gmail

Setup

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.
    • 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.
    SELECT * from tablename limit 100

 

Date Format

Define the Date Format to recognize and convert timestamps columns so they can be used as such in Cooladata. All JAVA standard time formats are supported. The following characters can be used – 1-3 chars represents the abbreviated form, if one exists; 4+ chars represents the full form. The date syntax is case sensitive, so (for example) MM represents month and mm represents seconds.

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-17 MM-dd-yy
18-04-2017 dd-MM-yyyy
18/04/2017 19:36:55 dd/MM/yyyy HH:mm:ss
18/04/2017 07:36:55 dd/MM/yyyy hh:mm aa
Tue 04/18/2017 EEE MM/dd/yyyy
Print Friendly, PDF & Email

Google Sheets

The Google Sheets integration enables you to use your Google Spreadsheets as tables in your Cooladata project, and query them directly from your Cooladata project.

Connected spreadsheets are always updated and reflect the exact data your sheet contains.

When connecting a Google Spreadsheets to your Cooladata project you have several configuration options:

  1. Integration name: any name you’d like that helps you identify the integration.
  2. Table name: the name of the table in the project that you will then use in queries. Must comply with table name standards: no special chars or spaces, no reserved names.
  3. Sheet URL: the link to the Google Spreadsheet you wish to connect. Note that only the first sheet for the spreadsheet is used for the integration.
  4. Email notification: any email address you’d like to be notified on in case of failure to connect to the Google Spreadsheet.
  5. Notification level: select whether you’d like to be notified only on failure to connect or on all issues.

Advanced:

  1. Header rows to skip: we assume your spreadsheet contains one row of column headers by default, and skip that row in the data. If your sheet doesn’t contain header, or if you’d like to skip more than 1 header row, change the number of rows to skip here.
  2. Table schema: the Google Sheets integration automatically recognizes the column header and type of data in each of your columns. However, you can also manually set it up, which might be useful when a column is wrongly classified, or if your sheet does not contain headers. Note that when you make changes to the table schema, i.e. add/remove/replace any columns, or change a column name, you can update the schema by running the integration – either click “Update” on the integration settings, or from the row options, choose “Run now”.

The new table will be added to your project Schema, under Integration Tables, in the Table Name you selected. Query it by using the following syntax:

FROM <table_name>

 

 

Print Friendly, PDF & Email

Facebook Ads

Cooladata provides a predefined connection that enables you to upload Facebook Ad data into your project in order to integrate it with your behavioral tracking data. This enables you to measure your social media return on investment and get the most out of your social media campaigns.

Tip: We recommend adding the utm_campaign parameter to pass the Facebook Ad ID in each of your ad’s URL, so you can link users’ activity to the specific ads. Use Google campaign URL builder to simplify the creation of URLs with parameters. Example:
http://www.mydomain.com/?utm_source=facebook&utm_medium=cpc&utm_campaign=[Ad ID]

 

Setup

  1. In the integrations page, click the + icon and select Facebook Ads.
  2. Fill in the Integration Name.
  3. Click the Connect button.
  4. Enter your Facebook Ads account credentials and click the Allow button. If you have already authorized cooladata to your Facebook Ads account you won’t be asked for the credentials again.

The first import process should take place within a few minutes. See the Last Run Status in the integrations list to monitor success or failed updates.

Data is updated twice a day (8AM & 8PM UTC) and fetches data from 3 days back to ensure data completeness. New data is appended to the tables stored in Cooladata.

 

Table Schemas

The integration fetches the following tables from all accounts associated with the user:

  • FaceBookAds_AdAccounts
  • FaceBookAds_AdSets

To add additional tables or columns to this integration, or upload Facebook Ads data to your own Google project (instead of into your Cooladata project), contact your customer success representative, at: support@cooladata.com.

 

FaceBookAds_AdAccounts

Column NameType
adaccountidSTRING
clicksFLOAT
costFLOAT
countrySTRING
dateTIMESTAMP
impressionsFLOAT
integrationnameSTRING
integrationtimeTIMESTAMP
sourceSTRING

FaceBookAds_AdSets

Column NameType
account_idFLOAT
actionsFLOAT
ad_account_idSTRING
adset_idFLOAT
budget_remainingFLOAT
call_to_action_clicksFLOAT
campaign_idFLOAT
canvas_avg_view_percentFLOAT
canvas_avg_view_timeFLOAT
configured_statusSTRING
cost_per_estimated_ad_recallersFLOAT
cost_per_inline_link_clickFLOAT
cost_per_inline_post_engagementFLOAT
cost_per_unique_clickFLOAT
cpmFLOAT
cppFLOAT
ctrFLOAT
daily_budgetFLOAT
effective_statusSTRING
estimated_ad_recall_rate_lower_boundFLOAT
estimated_ad_recall_rate_upper_boundFLOAT
frequencyFLOAT
idFLOAT
impressionsFLOAT
inline_post_engagementFLOAT
nameSTRING
reachFLOAT
social_impressionsFLOAT
spendFLOAT
total_action_valueFLOAT
total_actionsFLOAT
total_unique_actionsFLOAT
unique_actionsFLOAT
unique_ctrFLOAT
unique_link_clicks_ctrFLOAT

FaceBookAds_Campaigns

Column NameType
actionsFLOAT
ad_account_idSTRING
call_to_action_clicksFLOAT
campaign_idFLOAT
canvas_avg_view_percentFLOAT
canvas_avg_view_timeFLOAT
configured_statusSTRING
cost_per_estimated_ad_recallersFLOAT
cost_per_inline_link_clickFLOAT
cost_per_inline_post_engagementFLOAT
cost_per_total_actionFLOAT
cost_per_unique_clickFLOAT
cpmFLOAT
cppFLOAT
ctrFLOAT
effective_statusSTRING
estimated_ad_recall_rateFLOAT
estimated_ad_recall_rate_lower_boundFLOAT
estimated_ad_recall_rate_upper_boundFLOAT
frequencyFLOAT
idFLOAT
impressionsFLOAT
inline_post_engagementFLOAT
nameSTRING
reachFLOAT
social_impressionsFLOAT
social_reachFLOAT
spendFLOAT
total_action_valueFLOAT
total_actionsFLOAT
total_unique_actionsFLOAT
unique_actionsFLOAT
unique_ctrFLOAT
unique_link_clicks_ctrFLOAT
Print Friendly, PDF & Email

Google Search Console (GSC)

Google Search console (GSC) is a collection of Google SEO tools and reporting. It offers data and configuration control for your website as well as a variety of visitor metrics.

As an online marketer, the GSC “search analytics” section is probably the most useful report for you. GSC “search analytics” delivers insights into how your website is performing in regards to the overall organic search traffic from Google. You can see what keyword searches on Google led to the site being listed in the SERPs, and the click through rates of such listings. You can also see the most popular queries, pages, countries and devices. Additionally for better understanding of your website’s performance in Google’s search results, you can use different filters to segment traffic even further.

The table in Google is queried once a day, but the data is updated on Google’s side in a 3 days latency. The data is stored in a table called google_search_console

Table Schema

Column Name Type Comments
platform STRING
siteUrl STRING
searchTerm STRING
device STRING
countryCode STRING
countryName STRING
pageUrl STRING
impressions INTEGER
clicks INTEGER
ctr FLOAT
position INTEGER
queryTime TIMESTAMP the date queried from the Google API
insertTimeRecord TIMESTAMP The time the record was inserted in Cooladata

 

Querying the Data

Querying the data can be done using CQL reports or KPI, querying the google_search_console table, like so:

select *
 from google_search_console

The following query returns data filtered by the date range picker:

select *
 from google_search_console
 where date(queryTime)(date_range_gcp(context))
 order by queryTime

 

 

 

Print Friendly, PDF & Email