Using tGoogleAnalyticsInput in JasperETL/Talend

This is a short article on getting Google Analytics into JaspersoftETL/Talend ETL tools. Google Analytics has a nice interface and many great ways of analyzing web traffic, it'd be hard to beat the work Google did there. The purpose of this connector would be to bring the data into your own data warehouse and join it with other intelligence you might have. A great way to display the aggregated data would be with a BI tool like Jaspersoft.

I'm writing because the help for the tGoogleAnalyticsInput is in French and incomplete.

So let's get started. You'll need JasperETL 4.1.2 or older (commercial editions here, open source here).

Installing the plugin:
Create a new Job Design and drag the tGoogleAnalyticsInput component from the Palette (under Internet):


















Configure the component. First start with finding some fields you'd like to use from the Google API: http://code.google.com/apis/analytics/docs/gdata/dimsmets/dimsmets.html. Note that you need to have some metrics (can't be all dimensions, vice versa).

Here's an example schema (you'll have to put in the fields yourself):
Now configure the component. In order to get your Profile ID (note it doesn't start with UA) read the cryptic Google Documentation about it. I got mine by examining the URL while logged into GA and looking at the &id= parameter.

The "value" column comes directly from the Google API and will also have to be filled in. I've also found that I HAVE to filter on period. You can do 1980-2020 if you want to include everything. 

Now you can run the the job and see your visitors:

Funny enough. If you're reading this right now then you will eventually flow through the GA database and the very job I'm displaying above ;)

Comments

Unknown said…
Ernesto - did you implement this? Do you have a working example?
February 16, 2012 at 1:54 PM
R. A. Burrell said…
The Start and End Date are helpful. But, how do you set it up to pull yesterday?
April 12, 2012 at 3:22 AM
Ernesto Ongaro said…
RA, I read up on TalendDate.getDate and TalendDate.addDate - with these two I was able to calculate today's date and subtract 1 from today (the -1 param to addDate)

So to get yesterday and today:

End Date (today):
TalendDate.getDate("yyyy-MM-dd")

Start Date(tomorrow):
TalendDate.addDate(TalendDate.getDate("yyyy-MM-dd"),"yyyy-MM-dd",-1,"dd")
* -1 is the field that I added
April 12, 2012 at 10:17 AM
Anonymous said…
Hi,
I tried giving all the required parms
and the dates are giving error, MM can not be resolved to a variable.Any clue? The date I gave is "2012-04-11" and "2013-07-10".What is wrong here?
Anitha.
July 11, 2012 at 7:38 AM
Ernesto Ongaro said…
Anitha, did you include the dates in quotes? That's all I can really think could be the problem
July 11, 2012 at 9:10 AM
Post a Comment

Popular posts from this blog

Dealing with Nested Documents in MongoDB and Talend (aka Baking a Cake)

Image
Abstract: MongoDB is a very popular document database that gives you a huge amount of flexibility when it comes to storing data. On the other hand, the traditional relational model is far less flexible in how data is stored - you're limited to columns and rows. Sometimes you want to go from a flexible model like MongoDB to a relational one, that's what this post attempts to explain using  Talend / JaspersoftETL  (ETL tools). I do not want to get into the relational vs non-relational model argument in this post - it's only an example if you need to do this for some reason...I'm pro choice :) Scenario: We have a JSON document in MongoDB that looks like this: { "id" : "0001", "type" : "donut", "name" : "Cake", "ppu" : 0.55, "batters" : { "batter" : [ ...
Read more

Add sbin to user PATH in Debian

Debian does not add sbin to your path by default. I have no idea why. I know sbin is supposed to be administrative tools that you would only want to run as root, however, there are some useful ones that don't require root, and even if they need root, why hide them from useful tools like auto complete? Apparentely it's not a new disucssion - this Debian mailing list thread dates back to a decade ago! Regardless, I want it in my path - the easiest way that I've found is to modify the top of /etc/profile to remove the if statement that sets path if you're user id 0 (root) or not: Before: if [ "`id -u`" -eq 0 ]; then PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin" else PATH="/usr/local/bin:/usr/bin:/bin:/usr/games" fi After: PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin" Save the file, log out and log back in and you're all set. Note that this affects all users on you...
Read more

Batch convert Asterisk GSM WAV files to mp3

Image
I am working on a fun project at work to provide web based visual voice-mail for a ticketing system. I realized Flash audio players are not able to play WAV files so had to work around that. Since users are also using voicemail files in other ways I can't just change the output format from Asterisk. The type of file that I'm working with is identifed with file ms0012.WAV as: msg0012.WAV: RIFF (little-endian) data, WAVE audio, GSM 6.10, mono 8000 Hz First I'll say that I found this post http://www.thiscoolsite.com/?p=73 but like some of the commenters I couldn't get the script to work. The author assumes some other format than Asterisk spits out by default. Lame would complain that Unsupported data format: 0x0031 The Tools Sox: http://sox.sourceforge.net/ Lame: http://lame.sourceforge.net/ So here's what I do: Convert GSM encoded wav to Microsoft PCM sox msg0012.WAV -s msg0012.wav Convert the PCM wav to mp3: lame msg0012.wav msg0012.mp3 And here's a script to...
Read more