CODEWITHSUNDEEP
pythonjsonpython-2.7google-analyticsgoogle-analytics-api
Austin Trombley
Austin Trombley

Reputation: 47

Parsing Google Analytics API Python json response into python dataframe

Trying to parse Google Analytics API Python json response into python dataframe, and then ETL to MS SQL Server using python.

I get a successful output called feed import json, gdata

data_query = gdata.analytics.client.DataFeedQuery({
'ids': 'ga:67981229',
'dimensions': 'ga:userType,ga:sessionCount,ga:source', ##ga:source,ga:medium
'metrics': 'ga:pageviews',
##'filters': 'ga:pagePath==/my_url_comes_here/',
##'segment':'',
'start-date': '2015-01-01',
'end-date': '2015-01-03',
'prettyprint': 'true',
'output':'json',
})
feed = my_client.GetDataFeed(data_query)

However, when I try to parse the the data using this code it doesn't work and I get the below error

response = json.parse(feed) ## I also tried json.load(feed) and json.loads(feed)

data = json.parse(feed) Traceback (most recent call last): File "", line 1, in data = json.parse(feed) AttributeError: 'module' object has no attribute 'parse'

data = json.loads(feed) Traceback (most recent call last): File "", line 1, in data = json.loads(feed) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/init.py", line 338, in loads return _default_decoder.decode(s) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/decoder.py", line 365, in decode obj, end = self.raw_decode(s, idx=_w(s, 0).end()) TypeError: expected string or buffer

data = json.load(feed) Traceback (most recent call last): File "", line 1, in data = json.load(feed) File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/init.py", line 286, in load return loads(fp.read(), AttributeError: 'DataFeed' object has no attribute 'read' And I have already imported all of json as seen at the top, furthermore - my end objective is to ETL this to MS SQL Server - so any help on effective method to do this with a JSON Python object would help a LOT! Thanks!

Upvotes: 0

Views: 2685

Answers (1)

kushan_s
kushan_s

Reputation: 313

Instead of parsing the json response manually into a dataframe you could try using the Pandas library which has built in methods to query the Google Analytics API. Once you get your Google Analytics Metrics into a dataframe, you could insert records into SQL Server using the to_sql method.

Upvotes: 1

Related Questions