Import Google Analytics data into SQL Server / Reporting / ETL

In our previous article we saw how to read Google Analytics data using SSIS. But what if you don’t use SSIS and you still like to Import Google Analytics data into SQL Server or Read inside other Reporting / ETL Apps (e.g. Excel, Power BI, MS Access … and many more). Not all BI tools comes with native driver / connectivity for Google Analytics. Sometimes they do offer Google Analytics connectivity but don’t offer feature you looking for (e.g. Sort, Segment Filter). In such case you have to look for solution outside.

Well not to worry we will show you how simple it is to connect to Google Analytics using ZappySys JSON Driver (For REST API / Files) in few mins. This driver is one of the best REST API drivers you can find in the market. It is so generic that it can virtually access any REST API you may find internally or on public sites (e.g. Facebook, Salesforce, Azure, AWS). Check these articles to see many more use cases of JSON Driver.

At the end of this article you will learn how to Query Google Analytics data in SSMS like below (Yes that’s correct .. Pretty Awesome!!! ).

Import Google Analytics Data into SQL Server Table (using T-SQL Code)

Requirements

This article talks about few tools and techniques in order to load API data in SQL Server. Please make sure following prerequisites are met.

Download and Install ZappySys ODBC PowerPack (This includes XML / JSON / REST API and few other drivers for SQL Server and ODBC connectivity in tools like Excel, Power BI, SSRS)
Make sure you have access to SQL Server Instance. If you cant find one still want to try what is mentioned in this article then install FREE SQL Express Edition
Confirm that you have SSMS Installed. If you don't have then you can download from here.

Testing Google Analytics API

If you are new to Google Analytics API then read little bit about Google Analytics REST API here. Google also offers a really good way to test Analytics API using Query Explorer Tool here. So please check that and understand how to craft correct REST API URL which will be used in the next section.

Using Google Analytics Query Explorer Tool – Create REST API Url (Dimension / Metrics Selection Browser)

Sample Google Analytics API Response

Behind the scene here is how API Request and Response Looks like for Google Analytics REST API. Notice that Authorization Header in below request is automatically added by ZappySys Driver or Query Testing tool above. Also notice nextLink attribute in response, its used to fetch more data. By default each response contains upto 10000 rows.

Request

GET https://www.googleapis.com/analytics/v3/data/ga?ids=ga:185737326&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration&dimensions=ga:date
Authorization: Bearer ya29.GlwUxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

1 2	GET https://www.googleapis.com/analytics/v3/data/ga?ids=ga:185737326&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration&dimensions=ga:date Authorization: Bearer ya29.GlwUxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Response

{
    "kind": "analytics#gaData",
    "id": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:123456789&dimensions=ga:date&metrics=ga:avgSessionDuration&start-date=30daysAgo&end-date=yesterday",
    "query": {
        "start-date": "30daysAgo",
        "end-date": "yesterday",
        "ids": "ga:123456789",
        "dimensions": "ga:date",
        "metrics": ["ga:avgSessionDuration"],
        "start-index": 1,
        "max-results": 1000
    },
    "itemsPerPage": 1000,
    "totalResults": 30,
    "selfLink": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:123456789&dimensions=ga:date&metrics=ga:avgSessionDuration&start-date=30daysAgo&end-date=yesterday",
    "nextLink": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:123456789&dimensions=ga:date&metrics=ga:avgSessionDuration&start-date=30daysAgo&end-date=yesterday&start-index=10001&max-results=10000",
    "profileInfo": {
        "profileId": "123456789",
        "accountId": "33445566",
        "webPropertyId": "UA-33445566-3",
        "internalWebPropertyId": "454545454",
        "profileName": "All Web Site Data",
        "tableId": "ga:123456789"
    },
    "containsSampledData": false,
    "columnHeaders": [
        {
            "name": "ga:date",
            "columnType": "DIMENSION",
            "dataType": "STRING"
        },
        {
            "name": "ga:avgSessionDuration",
            "columnType": "METRIC",
            "dataType": "TIME"
        }
    ], "totalsForAllResults": {
        "ga:avgSessionDuration": "49.3779488037477"
    },
    "rows": [ 
		["20190425", "401.38912133891213"], 
		["20190426", "512.49723756906077"], 
		["20190427", "11.53488372093024"], 
		["20190428", "432.148648648648646"], 
		["20190429", "234.650735294117645"],
		.......
		.......
		.......
		.......
		["20190525", "74.123435294117645"]
	]
}

{

"kind": "analytics#gaData",

"id": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:123456789&dimensions=ga:date&metrics=ga:avgSessionDuration&start-date=30daysAgo&end-date=yesterday",

"query": {

"start-date": "30daysAgo",

"end-date": "yesterday",

"ids": "ga:123456789",

"dimensions": "ga:date",

"metrics": ["ga:avgSessionDuration"],

"start-index": 1,

"max-results": 1000

"itemsPerPage": 1000,

"totalResults": 30,

"selfLink": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:123456789&dimensions=ga:date&metrics=ga:avgSessionDuration&start-date=30daysAgo&end-date=yesterday",

"nextLink": "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:123456789&dimensions=ga:date&metrics=ga:avgSessionDuration&start-date=30daysAgo&end-date=yesterday&start-index=10001&max-results=10000",

"profileInfo": {

"profileId": "123456789",

"accountId": "33445566",

"webPropertyId": "UA-33445566-3",

"internalWebPropertyId": "454545454",

"profileName": "All Web Site Data",

"tableId": "ga:123456789"

"containsSampledData": false,

"columnHeaders": [

{

"name": "ga:date",

"columnType": "DIMENSION",

"dataType": "STRING"

{

"name": "ga:avgSessionDuration",

"columnType": "METRIC",

"dataType": "TIME"

}

], "totalsForAllResults": {

"ga:avgSessionDuration": "49.3779488037477"

"rows": [

["20190425", "401.38912133891213"],

["20190426", "512.49723756906077"],

["20190427", "11.53488372093024"],

["20190428", "432.148648648648646"],

["20190429", "234.650735294117645"],

.......

["20190525", "74.123435294117645"]

]

}

Creating Google API Project (Obtain Client ID / Secret)

Very first step to call any Google API including Google Analytics is to create Google API Project and register OAuth App to obtain Client ID and Client Secret. If you dont want to go through this hassle and start easy way then ZappySys offers Inbuilt Default App on OAuth Connection UI but we strongly recommend you create your own app rather than using Default App because in Default App API call limit is shared by many. Your data is never shared with ZappySys in any case even you use Default App.

Here is how to create API Project to call Google API. Once you create Google API Project make sure you enable Google Analytics API.

So once you have Client ID and Client Secret we can move forward to the next step.

Loading Google Analytics data into SQL Server

In our previous article we saw in depth general idea on how to import REST API data in SQL Server (T-SQL Script) without relying on any ETL tools. Now lets look at step by step on how to load Google Analytics data into SQL Server.

In this example, our goal is to extract a Google Analytics report which shows average session duration and new users count by date (For last 30 days).

Setup ZappySys Data Gateway

Very first step to access any REST API Data inside SQL Server is to configure ZappySys Data Gateway. We covered this in previous article. But here are high level steps for initial setup of Data Gateway.

Search for Gateway in Start menu and Select ZappySys Data Gateway
Open ZappySys Data Gateway
Create new User in Data gateway on Users tab. Enter username and password (we will use this when we create Linked Server) . Check Admin Option
Add Data Gateway User

Setup Google Analytics API Data Source in Gateway / ODBC

Once gateway user is setup, now lets create a new Data Source for Google Analytics API. In this section we will talk how to create data source in gateway but most instructions can be used to create data source in ODBC too. At the end of this article we explained how to Launch ODBC Datasource UI. Step#1 and 2 are only different, all other steps same in ODBC DSN creation.

Click Add Data Source option (Select Native – JSON Driver )
Add Gateway Data Source (Native JSON Driver)
Click Edit to configure data source
Edit Gateway Data Source Settings
Now lets configure Driver settings. Click on Load Connection String button.
Load ZappySys Driver ConnectionString to configure UI

Enter the following ConnectionString to get started with predefined settings.
Change ids (111223344 to your own Profile ID in DataPath URL). Other URL Parameters explained in next section.

DRIVER={ZappySys JSON Driver};
DataPath='https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration,ga:newUsers&dimensions=ga:date';
DataConnectionType=OAuth;
ScopeSeparator='{space}';
Scope='https://www.googleapis.com/auth/analytics https://www.googleapis.com/auth/analytics.readonly';
ServiceProvider=Google;
Filter='$.rows[*]';
ArrayTransformType=TransformSimpleTwoDimensionalArray;
ArrayTransColumnNameFilter='$.columnHeaders[*].name';
RequestMethod='GET';
NextUrlAttributeOrExpr='$.nextLink';
ClientId='xxxxxxxxxxxx';
ClientSecret='yyyyyyyyyyyyyy';
UseCustomApp='True'

DRIVER={ZappySys JSON Driver};

DataPath='https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration,ga:newUsers&dimensions=ga:date';

DataConnectionType=OAuth;

ScopeSeparator='{space}';

Scope='https://www.googleapis.com/auth/analytics https://www.googleapis.com/auth/analytics.readonly';

ServiceProvider=Google;

Filter='$.rows[*]';

ArrayTransformType=TransformSimpleTwoDimensionalArray;

ArrayTransColumnNameFilter='$.columnHeaders[*].name';

RequestMethod='GET';

NextUrlAttributeOrExpr='$.nextLink';

ClientId='xxxxxxxxxxxx';

ClientSecret='yyyyyyyyyyyyyy';

UseCustomApp='True'

URL used in DataPath is most important (parameters explained below) . You can use Query Explorer Tool here to build same URL.

https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration,ga:newUsers&dimensions=ga:date

1	https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration,ga:newUsers&dimensions=ga:date

Parameters Explained

Here is some required parameters used in above REST API URL.
ids=ga:11223344
This is your Profile ID which you like to extract. Read more
start-date=30daysAgo
This can be yyyy-MM-dd or some other some predefined date keywords (e.g today, yesterday 10daysAgo). Read more.
end-date=yesterday
Same as above
metrics=ga:avgSessionDuration,ga:newUsers
This can be comma separated list of metrics you like to extract (max 10). Read more.
dimensions=ga:date
This can be comma separated list of metrics you like to extract (max 7). Read more.

Now its time to configure OAuth settings. Click on Configure Connection. Enter Client ID and Client Secret Obtained in the previous section (see here) and then click Generate Token as below.
Configure Google API Data Source / DSN (Google Analytics API Example)

Thats it now can click on Preview Tab and run sample query like below.

select * from $

1	select * from $

–OR–
(Select Table name from dropdown and generate default query and then you can add alias , remove unwanted columns)

SELECT 
"ga:date" date, 
"ga:avgSessionDuration" avgSessionDuration,
"ga:newUsers" newUsers
FROM "rows"

SELECT

"ga:date" date,

"ga:avgSessionDuration" avgSessionDuration,

"ga:newUsers" newUsers

FROM "rows"

–OR–
(Type custom query with your own URL generated from here, Change Profile ID – from 11223344 to your own id)

SELECT * FROM $
WITH(
	 Src='https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration,ga:newUsers&dimensions=ga:date'
	,IncludeParentColumns='False'
)

SELECT * FROM $

WITH(

Src='https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration,ga:newUsers&dimensions=ga:date'

,IncludeParentColumns='False'

)

Create Linked Server in SQL Server using T-SQL Script

Once our Gateway Data Source is configured we can now move to SQL Server Part to define Linked Server.

Here is how you can define linked server to access Google Analytics Data inside SQL Server. There are two ways to create Linked Server (1) via T-SQL script (2) via SSMS UI)

Here is how to create using Script.

USE [master]
GO

--drop existing linked server for same name if already found
EXEC master.dbo.sp_dropserver @server=N'GOOGLE_ANALYTICS_LS', @droplogins='droplogins'
GO

EXEC master.dbo.sp_addlinkedserver 
	@server = N'GOOGLE_ANALYTICS_LS', 
	@srvproduct=N'', 
	@provider=N'SQLNCLI', 
	@datasrc=N'localhost,5000', --this is host name and port number where  ZappySys Data Gateway is running
	@catalog=N'ZS-GA' --this must be same name as defined on Data Source tab Grid on ZappySys Data Gateway

EXEC master.dbo.sp_addlinkedsrvlogin 
	@rmtsrvname=N'GOOGLE_ANALYTICS_LS',
	@useself=N'False',
	@locallogin=NULL,
	@rmtuser=N'SOME-GATEWAY-USER', -- user name created on ZappySys Data Gateway
	@rmtpassword='SOME-GATEWAY-PASSWORD' -- password for ZappySys Data Gateway user
GO

USE [master]

--drop existing linked server for same name if already found

EXEC master.dbo.sp_dropserver @server=N'GOOGLE_ANALYTICS_LS', @droplogins='droplogins'

EXEC master.dbo.sp_addlinkedserver

@server = N'GOOGLE_ANALYTICS_LS',

@srvproduct=N'',

@provider=N'SQLNCLI',

@datasrc=N'localhost,5000', --this is host name and port number where ZappySys Data Gateway is running

@catalog=N'ZS-GA' --this must be same name as defined on Data Source tab Grid on ZappySys Data Gateway

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'GOOGLE_ANALYTICS_LS',

@useself=N'False',

@locallogin=NULL,

@rmtuser=N'SOME-GATEWAY-USER', -- user name created on ZappySys Data Gateway

@rmtpassword='SOME-GATEWAY-PASSWORD' -- password for ZappySys Data Gateway user

Create Microsoft SQL Server Linked Server using SSMS UI

Once you configured data source in Gateway, we can now setup Linked Server in SQL Server to query API data.

Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here: Get SQL Server Express and Get SSMS
Open SSMS and connect to SQL Server.
Go to Root > Server Objects > Linked Servers node. Right click and click New Linked Server...

Add Linked Server in SQL Server
Now enter linked server name, select Provider as SQL Native Client
Enter data source as GatewayServerName,PORT_NUMBER where server name is where ZappySys Gateway is running (Can be same as SQL Server machine or remote machine). Default PORT_NUMBER is 5000 but confirm on Data gateway > General tab incase its different.
Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column

Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection
Click on Security Tab and select last option "Be made using this security context". Enter your gateway user account here.
Optional: Under the Server Options Tab, Enable RPC and RPC Out and Disable Promotion of Distributed Transactions(MSDTC).

RPC and MSDTC Settings

You need to enable RPC Out if you plan to use EXEC(...) AT [MY_LINKED_SERVER_NAME] rather than OPENQUERY.
If don't enabled it, you will encounter the 'Server "MY_LINKED_SERVER_NAME" is not configured for RPC' error.

Query Example: EXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]

If you plan to use 'INSERT INTO...EXEC(....) AT [MY_LINKED_SERVER_NAME]' in that case you need to Disable Promotion of Distributed Transactions(MSDTC).
If don't disabled it, you will encounter the 'The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MY_LINKED_SERVER_NAME" was unable to begin a distributed transaction.' error.

Query Example:
```
Insert Into dbo.Products 
EXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]
```
Click OK to save Linked Server

In SSMS execute below SQL query to test your connectivity.

SELECT * FROM OPENQUERY( MY_LINKED_SERVER_NAME, 'SELECT * FROM $')

--OR--

SELECT * FROM OPENQUERY( MY_LINKED_SERVER_NAME, 
'SELECT * FROM $
 WITH (Src=''https://services.odata.org/V3/Northwind/Northwind.svc/Customers?$format=json''
 ,Filter=''$.value[*]''
 ,DataFormat=''OData''
)');

Here is the preview after you run some REST API query in SQL Server. Notice that you can override default configuration by supplying many parameters in WITH clause (second query example in screenshot).

SSMS Output - Query REST API via Linked Server OPENQUERY statement (Connect to ZappySys Data Gateway)
You can wrap your queries inside View or wrap inside Stored procedure to parameterize. Here is an example of create view which calls REST API queries. Below View can be consumed like a normal table from any Tools or Programming Language which supports connectivity to SQL Server.
```
CREATE VIEW dbo.vwApiInvoices 
AS 
/*Call REST API inside SQL Server View*/
SELECT * FROM OPENQUERY( LS , 
'SELECT * FROM $
WITH (Src=''https://services.odata.org/V3/Northwind/Northwind.svc/Invoices?$format=json''
	 ,Filter=''$.value[*]''
	 ,DataFormat=''OData''
)');

GO
```
Notice in above approach if you parameterize Stored Procedure then check this article to understand Dynamic Metadata.
That's it. We are now ready to move forward with more interesting things in next section.

Import Google Analytics Data into SQL Server Table (T-SQL Code)

Now lets see how to run sample query to import Google Analytics Data into SQL Server. Notice that how we have escaped quotes in OPENQUERY.
NOTE: Change id 11223344 with your own Id (use this tool to create URL)

select * into GaApiLoad from OPENQUERY([GOOGLE_ANALYTICS_LS] ,
'SELECT * FROM "rows"
WITH 
 (
   SRC=''https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration,ga:newUsers&dimensions=ga:date''
  ,IncludeParentColumns=''False''
 )
')
select * from GaApiLoad

select * into GaApiLoad from OPENQUERY([GOOGLE_ANALYTICS_LS] ,

'SELECT * FROM "rows"

WITH

(

SRC=''https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:avgSessionDuration,ga:newUsers&dimensions=ga:date''

,IncludeParentColumns=''False''

)

select * from GaApiLoad

Import Google Analytics Data into SQL Server Table (using T-SQL Code)

Expose Google Analytics Data as View

To make it simple you can expose above query as SQL Server views so you can access them from any Reporting / ETL Tools.

CREATE VIEW vw_Google_Analytics_BounceRate_PageViews_Last30Days
AS
--change ids to your own Profile ID
select * from OPENQUERY([GOOGLE_ANALYTICS_LS] ,
'SELECT * FROM "rows"
WITH 
 (
   SRC=''https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=2019-04-01&end-date=2019-04-30&metrics=ga:bounceRate,ga:uniquePageviews&dimensions=ga:date''
  ,IncludeParentColumns=''False''
 )
')
GO

CREATE VIEW vw_Google_Analytics_BounceRate_PageViews_Last30Days

--change ids to your own Profile ID

select * from OPENQUERY([GOOGLE_ANALYTICS_LS] ,

'SELECT * FROM "rows"

WITH

(

SRC=''https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=2019-04-01&end-date=2019-04-30&metrics=ga:bounceRate,ga:uniquePageviews&dimensions=ga:date''

,IncludeParentColumns=''False''

)

Making things Dynamic using Stored Procedure with Parameters

Now let’s make few things dynamic. We will change above query so we can pass Id, Dimensions and Metrics as parameters.

Run below script to Create a stored proc and fetch google analytics data in SQL Server.

use MyDatabase
go
create proc usp_GoogleAnalyticsData
	@ids varchar(1000),
	@startDate varchar(50)='30daysAgo',
	@endDate varchar(50)='yesterday',
	@metrics varchar(200)='ga:avgSessionDuration,ga:newUsers',
	@dimensions varchar(200)='ga:date'	
as

declare @sql varchar(4000)

set @sql='select * from OPENQUERY([GOOGLE_ANALYTICS_LS] ,
''SELECT * FROM "rows"
WITH 
 (
   SRC=''''https://www.googleapis.com/analytics/v3/data/ga?ids=ga:' + @ids 
   +'&start-date='+ @startDate 
   +'&end-date='+ @endDate 
   +'&metrics='+ @metrics 
   +'&dimensions='+ @dimensions 
   +'''''
  ,IncludeParentColumns=''''False''''
 )
'')'

print @sql
execute(@sql)

go

--Example: query google analytics data
exec usp_GoogleAnalyticsData 
	@ids='11223344',  --Change this ID - Obtain from here https://ga-dev-tools.appspot.com/query-explorer/
	@startDate ='30daysAgo',  --or like this 2019-04-01
	@endDate   ='yesterday', --or like this 2019-04-30
	@dimensions='ga:date', 
	@metrics   ='ga:bounceRate,ga:uniquePageviews'

use MyDatabase

create proc usp_GoogleAnalyticsData

@ids varchar(1000),

@startDate varchar(50)='30daysAgo',

@endDate varchar(50)='yesterday',

@metrics varchar(200)='ga:avgSessionDuration,ga:newUsers',

@dimensions varchar(200)='ga:date'

declare @sql varchar(4000)

set @sql='select * from OPENQUERY([GOOGLE_ANALYTICS_LS] ,

''SELECT * FROM "rows"

WITH

(

SRC=''''https://www.googleapis.com/analytics/v3/data/ga?ids=ga:' + @ids

+'&start-date='+ @startDate

+'&end-date='+ @endDate

+'&metrics='+ @metrics

+'&dimensions='+ @dimensions

+'''''

,IncludeParentColumns=''''False''''

)

'')'

print @sql

execute(@sql)

--Example: query google analytics data

exec usp_GoogleAnalyticsData

@ids='11223344', --Change this ID - Obtain from here https://ga-dev-tools.appspot.com/query-explorer/

@startDate ='30daysAgo', --or like this 2019-04-01

@endDate ='yesterday', --or like this 2019-04-30

@dimensions='ga:date',

@metrics ='ga:bounceRate,ga:uniquePageviews'

Insert Google Analytics Data into table (Save Stored Procedure Output)

Now what if you like to save Stored Proc output into a table? Well for that you must create table with same structure as stored proc output and then use INSERT INTO sql like below.

--import google analytics data in a table
if(object_id('GaDataLoad') is not null)
	drop table GaDataLoad
create table GaDataLoad([Date] date, BounceRate numeric(10,5), PageViews numeric(10,5))  

insert into GaDataLoad
exec usp_GoogleAnalyticsData 
	@ids='11223344',  --Change this ID - Obtain from here https://ga-dev-tools.appspot.com/query-explorer/
	@startDate ='30daysAgo',  --or like this 2019-04-01
	@endDate   ='yesterday', --or like this 2019-04-30
	@dimensions='ga:date', 
	@metrics='ga:bounceRate,ga:uniquePageviews'

select * from GaDataLoad

--import google analytics data in a table

if(object_id('GaDataLoad') is not null)

drop table GaDataLoad

create table GaDataLoad([Date] date, BounceRate numeric(10,5), PageViews numeric(10,5))

insert into GaDataLoad

exec usp_GoogleAnalyticsData

@ids='11223344', --Change this ID - Obtain from here https://ga-dev-tools.appspot.com/query-explorer/

@startDate ='30daysAgo', --or like this 2019-04-01

@endDate ='yesterday', --or like this 2019-04-30

@dimensions='ga:date',

@metrics='ga:bounceRate,ga:uniquePageviews'

select * from GaDataLoad

Performance Tips / Handling data errors due to missing rows

By default ZappySys API Drivers sends minimum 2 API requests. First one to fetch metadata and second, third… for Data. There will be a time when you wont have any data and it may throw error about no records found because it fails to parse metadata. You can avoid such issue by supplying metadata before hand so you can avoid expensive API calls. Check this article see how you can supply cached metadata in your SQL Query as below. This will avoid Metadata call and speedup significantly. It will also avoid errors due to no rows found for specified criteria.

select * from OPENQUERY([GOOGLE_ANALYTICS_LS] ,
'SELECT * FROM "rows"
WITH 
 (
   SRC=''https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:bounceRate,ga:uniquePageviews&dimensions=ga:date''
  ,IncludeParentColumns=''False''
  ,Meta=''[ 
     {"Name": "ga:date","Type": "String", "Length": 16 },
     {"Name": "ga:bounceRate", "Type": "String", "Length": 36 },
     {"Name": "ga:uniquePageviews", "Type": "String", "Length": 10 }
    ]''
 )
')

select * from OPENQUERY([GOOGLE_ANALYTICS_LS] ,

'SELECT * FROM "rows"

WITH

(

SRC=''https://www.googleapis.com/analytics/v3/data/ga?ids=ga:11223344&start-date=30daysAgo&end-date=yesterday&metrics=ga:bounceRate,ga:uniquePageviews&dimensions=ga:date''

,IncludeParentColumns=''False''

,Meta=''[

{"Name": "ga:date","Type": "String", "Length": 16 },

{"Name": "ga:bounceRate", "Type": "String", "Length": 36 },

{"Name": "ga:uniquePageviews", "Type": "String", "Length": 10 }

]''

)

ODBC Connection – Google Analytics Data in Other Apps (e.g. SSRS / Power BI / Excel / Tableau)

So far we have talked accessing data inside SQL Server using Data Gateway Approach but what if you like to access in other apps ? Well you have three options.

Use Microsoft SQL Server Driver to call Linked Server Queries (OPENQUERY approach we saw earlier)
Access Google Analytics Data using ODBC connectivity
Use Microsoft SQL Server Driver to send direct SQL queries to Gateway (Bypass Linked Server – Use inner SQL query without OPENQUERY in this case)

Method-1 : Linked Server Approach

First approach most likely works in all cases because most apps will support connecting to SQL Server using OLEDB / ADO.net / ODBC / JDBC Drivers. So calling SQL Queries which uses Linked Server (i.e. OPENQUERY statement) is advisable.

Method-2 : ODBC Driver Approach

However in some cases this may not be possible (e.g. You don’t have SQL Server inhouse or you don’t want to rely on SQL Server to access API). In such case you can use ODBC connectivity in your app. Since ODBC is widely adopted standard most app out there (Except JAVA apps) should support ODBC Drivers. If you like to use this approach then create ODBC DSN rather than Data Gateway Data source and use it in your Reporting / ETL / Custom Apps.

Open ODBC Data Sources (Create DSN)

ODBC User DSN Tab: Add new Driver Screen

Method-3 : Direct connection to Data Gateway (By pass Linked Server)

Last option we suggest for cases like JAVA Apps / Linux / Mac machines where you cannot install ZappySys ODBC Drivers and you dont have option to use SQL Server Linked Server either. In such case you can try to send SQL Queries to Gateway directly using Microsoft SQL Server Compatible Drivers (i.e. ODBC /JDBC). See this example how we called API Queries inside JAVA Apps.

Google Analytics / REST API Integration in various apps

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn't appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.

ZappySys ODBC Drivers for REST API, JSON, XML - Integrate with Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#, VB.net, PHP. PowerShell

BI / Reporting Tools Integration	ETL Tools Integration	Programming Languages Integration
Microsoft Power BI Tableau SSRS (SQL Reporting Services) QlikView /Qlik Sense MicroStrategy Google Sheet Microsoft Excel Microsoft Access Oracle OBIEE Many more (not in this list).....	Informatica PowerCenter (Windows) Informatica Cloud SSIS (SQL Integration Services) SQL Server Talend Data Studio Pentaho Kettle Oracle OBIEE Many more (not in this list).....	Visual Studio C# C++ JAVA Python PHP PowerShell T-SQL (Using Linked Server)

Conclusion

In this article we explored many ideas of JSON / API integration in SQL Server. We saw how to create OAuth App for Google API and import Google Analytics data into SQL Server Table without doing any ETL using pure T-SQL code (Query / Views /Stored Procs). You can Download FREE Trial of ODBC PowerPack and try yourself see how easy it is to query any API inside SQL Server and avoid expensive ETL processes.