3

I have been working on ways to import Google Analytics raw data without having to use a premium account .So far this is the nearest link to what I want to do How to extract data from Google Analytics and build a data warehouse (webhouse) from it? I want to load that data into elastic search and display using kibana .What is the best ETL approach for this ? Has anyone tried to display GA data using ELK stack ?

Community
  • 1
  • 1
user2582604
  • 39
  • 1
  • 5
  • I haven't used elastic search before looks like they have an API but how do you upload data to them? – Linda Lawton - DaImTo Dec 01 '15 at 07:50
  • 2
    This is incredibly broad. Can you split that into multiple questions (i.e. "how do I get GA raw data without a premium account", "how do I import data to elasticsearch" etc. ) ? – Eike Pierstorff Dec 01 '15 at 09:02
  • @DaImTo I am really not sure about that .May be exporting GA raw data into hadoop and using a hadoop-elastic connector as described here http://www.rittmanmead.com/2014/11/analytics-with-kibana-and-elasticsearch-through-hadoop-part-1-introduction/ – user2582604 Dec 01 '15 at 09:36
  • I think you should contact them. this question is to broad. I have never heard of elastic I was just curious I use a custom SSIS task to import my GA data into sql server data warehouse I was just curious really. If you can figure out how to insert the data the Google analytics API will let you retrieve the data. – Linda Lawton - DaImTo Dec 01 '15 at 09:41
  • @EikePierstorff when are you going to contact me don't make me go though the company website on your profile please :) – Linda Lawton - DaImTo Dec 01 '15 at 09:42
  • @DaImTo, done (via the contact form on your website). And elasticsearch is a document based storage engine, together with logstash and kibana (a dashboard software) it's often used for log file analysis (all open source, too). So this might be worth a look (I admin I'm just getting started with elasticsearch). – Eike Pierstorff Dec 01 '15 at 21:07
  • Loading csv data exported from google analytics using GA api into elastic search is achievable .What I want to be able to do is importing raw data from GA into a data ware house (probably hadoop or some others if applicable ) – user2582604 Dec 04 '15 at 04:44

3 Answers3

0

You should do it in two times

glmrenard
  • 675
  • 1
  • 8
  • 16
  • Despite what their website states, the Java Transport Client is still available. I'm able to connect to my Elasticsearch cloud service with it. –  Jan 12 '17 at 14:27
0

Have a look at this:

https://www.elastic.co/guide/en/logstash/current/plugins-inputs-http_poller.html

You can use this to poll an endpoint, in this case GA, and load the response data into Elasticsearch. You may want to filter the response with the Split and / or Mutate plugins as well.

Neil Girardi
  • 4,533
  • 1
  • 28
  • 45
0

I have done this same setup.

  1. Extracted data from Google Analytics with 7 Dimensions and 6 Metrics, out of which 2 Dimensions were primary key (Timestamp and ID). This was done using R.
  2. Did some transformations on the data using linux awk and sed commands.
  3. Loaded the data into Apache Hive with the row column formatting, created like total 9 tables.
  4. Joined all the 9 tables in Hive using Hive Join queries, with 2 primary keys.
  5. Used elasticsearch-hadoop connector to load the final resulting table to elasticsearch. Had to do a little data transformations to match Hive and Elasticsearch data types.
  6. Used Kibana to visualize the data in Elasticsearch.

Now I am planning to avoid all the manual steps and somehow automate all the steps above.

Kishore Sampathkumar
  • 1
  • 2