Google Analytics

Knowi enables data discovery, visualization, data manipulation, warehousing and reporting automation from Google Analytics, along with the ability to merge that data with other data stores.

If you are not a current Knowi user, check out our Google Analytics Instant Reporting page to get started.

Connecting

1. Login to Knowi and select Queries -> New Datasources from the left down menu.

2. Click on Google Analytics. An authentication popup will be shown. Sign in to your Google Analytics account to complete the OAuth process, where a secure token is exchanged between Google Analytics and Knowi.

3. After the authorization, select a Google Analytics Profile ID set up the datasource against.

4. Save the Connection. Click on the "Configure Queries" link on the success bar.

Alternatively, Use our Cloud9Agent to connect to your Google Analytics account. This can securely pull data inside your network. See agent configuration for more details.

Queries & Reports

1. This section enables you to set up automated data pulls from Google Analytics that can either be run on a schedule or once.

   

   Report Name: Specify a name for the report.

   Metrics : Select a list of metrics you want to track from the drop down (or type in)

   Dimensions : Dimensions enable grouping of data for the metrics selected. For example, ga:date groups the metrics selected on a daily basis.

   Max Results: Maximum number of records to pull. Note: Google Analytics allows up to 10k rows per data pull.

   Sort By Field: Field sorting for the data returned. Example: Ascending: ga:visits Descending: -ga:visits

   Cloud9QL Query: Optional syntax that can be applied to the results of the Google Analytics data to further transform the results. Example:

   select sum(ga:visits) as Visits, sum(ga:newVisits) as New visits,month(ga:date) as Month group by Month(ga:date) order by Month asc
   

   This groups Visits and New Visits on a monthly basis (with custom labels). Detailed Cloud9QL documentation 

   Date Range: Specify a date range to pull data from. Use a number followed by the date identifier. Examples:

   Date Range Op	Comments
   min	Date range of n minutes back from now. Example (10 minutes) : 10min
   d	Date range of n days back from today. Example (up to 120 days) : 120d
   w	Date range of n weeks back from today. Example (up to 10 weeks): 10w
   m	Date range of n months back from today. Example (up to 3 months): 3m
   y	Date range of n years back from today. Example (up to last 1 year): 1y
   today	Midnight of today till now
   yesterday	Midnight of yesterday till now
   this hour	Current hour till now
   this week	Midnight of Monday of the current week till now
   this month	Midnight of the 1st of the month, to now till now
   last hour	Last hour, adjusted to 0 mins and 0 secs till now
   last week	Last Week Monday, adjusted to Midnight till now
   last month	Last Month, adjusted to the first of that month, to now.

1. Scheduling: Configure how often this should be run. Select 'None' for a one-time operation. The results are stored in your warehouse in Knowi.

2. Overwrite Strategy (for scheduled query runs):

   This determines how the data is stored in your warehouse in Knowi:

   i. If empty, data will be added on to the existing data for this dataset within Knowi. Or,

   ii. "All": Any existing data for this dataset will be replaced by this results.

   iii. One or More Field Names (Example: "A,B,C"): A new record is created where the values of the combination of the field names do not exist, and, updates current records for the field grouping where it exists. For example, if this is set to say "Date, Type", existing data with the same Date and Type values will be updated with the latest data, and new records created when they do not exist.

3. Click 'Preview' to see the results.

4. Click on 'Save' to complete setting up the report. This also sets up this data extraction on a schedule, if configured.

5. Click on 'Dashboards' to access dashboards. You can drag and drop the newly created report from the bottom list into to the dashboard.

Cloud9Agent Configuration

Use Cloud9Agent as an alternative to UI based connectivity outlined above. The agent runs inside your network to extract data from Google Analytics and sends the extracted/manipulated data into your Knowi warehouse. Check out Cloud9Agent to download your agent.

For sample Google Analytics configuration, see the datasource_example_googleanalytics.json and query_example_googleanalytics.json examples folder under the Cloud9Agent install directory.

Highlights:

• Connects to Google Analytics using OAuth tokens

• Pulls data using GA API, with optional manipulations using Cloud9QL

  Obtain the Refresh token using the Connect step in the UI section above.

Datasource Configuration:

Query Configuration:

Datasource Example:

[
  {
   "name":"demoGA",
   "datasource":"ga",
   "authToken":"1/JL-_Wkuan6c_Hs5FdEkgGMJ4wCgHm4STz2J7EgjN4OI",
   "gaProfileID":"ga:66465881"
  }
]

Query Example:

[
  {
    "entityName":"Website Visits",
    "dsName":"demoGA",
    "gaMetrics":"ga:visitors,ga:newVisits",
    "gaDimensions":"ga:date",
    "gaDateRange":"10d",
    "gaMaxResults":"1000",
    "gaSort":"-ga:date",
    "c9QLFilter":"select ga:visitors as Visitors, ga:newVisits as New Visits, ga:date as Date",
    "overrideVals":{
      "replaceAll":true
    },
    "frequencyType":"hour",
    "frequency":2
  }
]