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

Google AdWords

Cooladata’s predefined integration to Google AdWords enables you to upload data from your Google AdWords account reports into your Cooladata project, and enrich your events data with information such as CTR (Click Through Rate), keywords, costs, etc., in order to link your marketing efforts to your users’ behavior.

Tip: We recommend adding the utm_campaign parameter to pass the AdWords Campaign 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=adwords&utm_medium=cpc&utm_campaign=[Campaign ID]

 

Setup

  1. On the Integration page, click the + button and choose Google Adwords.
  2. Enter an Integration Name.
  3. If you want to be notified of update issues by email, under advanced, enter the emails you want to address.
  4. Click Connect.
  5. In the popup that opens, sign in to your Adwords account, and click Allow to authorize Cooladata to access your data. Note to use an advertiser (not a manger) account.
    7-23

The first import process will start in a few minutes. On the first run the process will fetch data for the last 30 days. To fetch additional data please contact your CSM.

Cooladata automatically updates the data from your Google AdWords account twice a day, at 8AM & 8PM UTC, each time fetching data for the last 3 days, to ensure data completeness.

Once the import process is done, the Status column on the Integrations page for this integration will show “Data Fetched”.

If the integration process fails for any reason, the Status column will show “Failed” – error details can be found in the integration History.

Please notice that Google does not support reporting access for manager accounts so make sure the user you are using has access to the advertiser (non-manager) account.

 

Table Schemas

The integration fetches the following Adwords reports from all accounts assigned to the user:

  • Account Performance
  • Ad Group Performance
  • Ad Performance
  • Age Range Performance
  • Campaign Performance
  • Click Performance
  • Gender Performance
  • Geo Performance
  • Keywords Performance

See the full schema of each report here:
https://developers.google.com/adwords/api/docs/appendix/reports.

The corresponding tables in Cooladata will be:

  • adwords_account
  • adwords_adgroup
  • adwords_ad
  • adwords_age_range
  • adwords_campaigns
  • adwords_clicks
  • adwords_gender
  • adwords_geo
  • adwords_keywords
Print Friendly, PDF & Email

Intercom

Intercom is a powerful set of tools for better managing your company’s customer support system. It includes a help center with a feedback system, which you can use to focus future articles on the growing needs of your customers, and it also provides a robust conversation system that allows you to assign support teams to customers based on specific criteria (about the customer or discussion topic), rather than just based on availability. Intercom is designed to create a more effective customer support network by specifically tracking and targeting your customers’ needs.

Cooladata’s integration with Intercom empowers marketers to understand the entire user journey, and the impact of Intercom on their users’ lifetime and value.

The integration runs daily, updating the previous day’s end of day data in each table. See the full tables schemes below:

  • intercom_conversations
  • intercom_users

To integrate Intercom to your Cooladata project please contact your customer success manager or email us at support@cooladata.com.

 

Table Name: intercom_conversations

See: https://developers.intercom.com/v2.0/reference#conversations

 

Table Name: intercom_users

See: https://developers.intercom.com/v2.0/reference#users

 

Print Friendly, PDF & Email

Salesforce

Salesforce develops CRM solutions that help customers increase sales, improve customer service, collaborate with their team, and develop customized applications on the Salesforce platform.

Integrating Salesforce into your Cooladata account will and allow you to enrich the behavioral data tracked by Cooladata with your CRM data.

Setting up the Salesforce integration will fetch the following Salesforce objects. The corresponding Cooladata table names are noted in brackets:

  • Leads (sf_Lead)
  • Opportunities (sf_ Opportunity)
  • Accounts (sf_ Account)
  • Contacts (sf_ Contact)
  • Price books (sf_ Pricebook2)
  • Products (sf_ Product2)
  • Campaigns (sf_ Campaign)
  • Events (sf_ Event)

All the above objects’ fields, standard and custom, are fetched. See each object’s standard fields list here: https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_list.htm.

Data is updated daily. To configure a specific update hour please contact us at support@cooladata.com.

Print Friendly, PDF & Email

Hubspot

Hubspot’s services are centered around sales and marketing tools that help a company refocus their marketing efforts into inbound marketing. With Hubspot, a company can create content that attracts leads that are already interested in that company’s products and services. Then, users can take advantage of Hubspot’s marketing tools to collect contact information and customize visitors’ experiences so that they are more likely to make the conversion from interested leads into happy customers.

Cooladata’s integration with HubSpot empowers marketers to understand the entire user journey, from when the initial email campaign is sent to when the user converts. Critical email campaign decisions can be made based on a deeper time series analysis of email campaigns.

The integration runs daily, updating the previous day’s end of day data in each table. See the full tables schemes below.

  • hs_events: all events sent to Hubspot.
  • hs_campaigns: contains one entry for each campaign, showing the latest aggregated data for that campaign.
  • hs_campaigns_trend: contains the daily aggregated data for each campaign on each day, so you can analyze the campaign’s performance over time.

To integrate Hubspot to your Cooladata project please contact your customer success manager or email us at support@cooladata.com.

 

Table Name: hs_events

FieldDescriptionExample
appIdAn ID referencing the HubSpot Application which sent the email message22809
campaign_idAn ID referencing the HubSpot email which the user interact with49635963
email_nameTitle of the emailTop Deals
email_subjectBody of the emailBeautiful hotels, great prices!
event_idA randomly-generated ID for this event.16103650-83c5-4cdd-9ebc
event_nameName of the action done by the recipientopen/click/sent/bounce
event_time_tsThe actual timestamp when the event occurred42858.7895833
groupIdA unique identifier fot a group of the identical emails (has value in case mail is sent with smart send feature)49566019
hmidA randomly-generated ID which corresponds to the header 'X-HubSpot-MID' in the email message.CiQxNjEwMzY1MC04M2M1LM5...
sent_fromThe 'from' field of the email message.Deals Club "deals@deals.com"
user_emailRecipient's mail addressexample@yahooc.com

 

Table Name: hs_campaigns

FieldDescriptionExample
appIdAn ID referencing the HubSpot Application which sent the email message22809
appNameThe name of the HubSpot Application which sent the email message. Note that this is a derived value, and may be modified at any timeLocalTimeBatch
bouncedThe recipient's email server could not or would not accept the message, and no further attempts will be made to deliver the message. This is also referred to as a hard bounce.
campaign_idUnique idendifier for a single mail sending49635963
click_rateclick / delivered (percentage)12
clickedThe recipient clicked on a link within the message.
contentIdThe id of the email content, basically what you'd be editing in HubSpot. It's possible to have two campaigns for a single contentId if you send the same email multiple times4979414943
deferredThe recipient’s email server has temporarily rejected message, and subsequent attempts will be made to deliver the message. This is also referred to as a soft bounce.
deliver_ratedeliver / sent (percentage)66
deliveredThe recipient's email server has accepted the message and the message has been successfully delivered to the recipient.
droppedThe message was rejected, either by HubSpot or by our delivery provider, and no attempt will be made to deliver the message.
email_nameTitle of the emailTop Deals!
email_subjectBody of the emailBeautiful hotels, great prices!
forwardThe recipient forwarded the message.
groupIdA unique identifier fot a group of the identical emails (has value in case mail is sent with smart send feature)49566019
mta_droppedThe delivery provider decided to drop the message
openThe recipient opened the message.
open_rateopen / delivered (percentage)88
printThe recipient printed the message.
processedThe message has been received by our delivery provider, which has indicated it will attempt to deliver the message to the recipient's email server.
selected
send_dateThe date where campaign / email was first created42463
sending_methodWhat is responsible for scheduling the email sendingAUTOMATED_EMAIL, LOCALTIME_EMAIL, AB_EMAIL, BATCH_EMAIL
sentThe message was sent to and received by our delivery provider, which has queued it for further handling.
spamreportThe recipient flagged the message as spam.
statuschangeThe recipient changed her email subscriptions in some way.
unsubscribedThe recipients unsubscribed to your email communications

 

Table Name: hs_campaigns_trend

FieldDescription
appIdAn ID referencing the HubSpot Application which sent the email message
appNameThe name of the HubSpot Application which sent the email message. Note that this is a derived value, and may be modified at any time
bouncedThe recipient's email server could not or would not accept the message, and no further attempts will be made to deliver the message. This is also referred to as a hard bounce.
campaign_idUnique idendifier for a single mail sending
clickedThe recipient clicked on a link within the message.
contentIdThe id of the email content, basically what you'd be editing in HubSpot. It's possible to have two campaigns for a single contentId if you send the same email multiple times
deferredThe recipient’s email server has temporarily rejected message, and subsequent attempts will be made to deliver the message. This is also referred to as a soft bounce.
deliveredThe recipient's email server has accepted the message and the message has been successfully delivered to the recipient.
droppedThe message was rejected, either by HubSpot or by our delivery provider, and no attempt will be made to deliver the message.
email_nameTitle of the email
email_subjectBody of the email
forwardThe recipient forwarded the message.
groupIdA unique identifier fot a group of the identical emails (has value in case mail is sent with smart send feature)
insert_dateThe date in which all data within the record (open,click etc.) refer to
mta_dropped
openThe recipient opened the message.
printThe recipient printed the message.
processedThe message has been received by our delivery provider, which has indicated it will attempt to deliver the message to the recipient's email server.
selected
sentThe message was sent to and received by our delivery provider, which has queued it for further handling.
spamreportThe recipient flagged the message as spam.
statuschangeThe recipient changed her email subscriptions in some way.
unsubscribed

 

Print Friendly, PDF & Email