Ręczna migracja danych z Google Analytics do Oracle RDBMS

Anna Kowalczyk-Niewiadomy

Wprowadzenie, czyli co, czemu i jak?

W obecnym świecie IT jednym z największych i najbardziej liczących się graczy jest koncern Google. Swoją pozycję zawdzięcza on już nie tylko powszechnie stosownej wyszukiwarce Google, przeglądarce Chrome, czy też systemowi Android. Bardzo ważne dla przeciętnego użytkownika Internetu stały się takie produkty jak GMail, GDocs, GCalendar, GMaps, Picasa czy Youtube, które są dostępne bez konieczności ponoszenia żadnych kosztów przez użytkownika. Z drugiej strony koncern Google jest otwarty dla bardzo ważnej z punktu widzenia IT grupy, czyli dla programistów i twórców stron internetowych. Wszystkie wyżej wymienione systemy dostarczane przez Google udostępniają swoje interfejsy programistyczne (API, ang. Application Programming Interface), w szczególności w takich językach jak Java czy Python.

Czemu piszemy o tym na forum grupy PLOUG? W artykule tym pokażemy jak w prosty sposób zintegrować naszą aplikację Java z przykładowym systemem Google oraz jak przenieść dane z systemu Google do bazy danych Oracle. Zadanie to zrealizujemy na podstawie systemu monitoringu i analizy witryn internetowych Google Analytics, a migrację pokażemy na przykładzie standalone’owej aplikacji napisanej w języku Java (w wersji co najmniej 1.5).

Google Analytics dla niewtajemniczonych

Czym jest zatem Google Analytics (w dalszej części skrótowo nazywane GAnalytics)? System ten jest jednym z ważniejszych narzędzi w całej gamie produktów koncernu, ze względu na masowe wykorzystanie go przez twórców i operatorów witryn internetowych do śledzenia statystyk ruchu. Narzędzie GAnalytics stanowi prosty w obsłudze, aczkolwiek bardzo bogaty pakiet umożliwiający gromadzenie statystyk analitycznych pozwalających na śledzenie oglądalności witryn internetowych, przeglądanie informacji źródłowych o osobach odwiedzających witryny (lokalizacja, typ przeglądarki, typ systemu operacyjnego i wiele innych). Z punktu widzenia programisty stron WWW GAnalytics wymaga tylko i wyłącznie zamieszczenia kawałka kodu JavaScript w kodzie witryny (najczęściej w nagłówku lub stopce). Potęga narzędzia widoczna jest dopiero z punktu widzenia analityka operującego na danych gromadzonych w systemie. Każdy typowy bazodanowiec od razu przyrówna końcówkę kliencką Google Analytics do typowej końcówki klienckiej hurtowni danych, gdzie przechowywane są wielowymiarowe statystyki ruchu.

Poniżej przedstawiony został typowy widok interfejsu udostępnianego przez GAnalytics pokazujący hurtowniane metryki liczby wejść (visits), stron na wizytę (Pages/Visit), średnio spędzony czas na stronie (Avg. time on Site), procentowy udział nowo odwiedzających(% New Visits) oraz współczynnik odrzuceń (Bounce Rate) globalnie oraz w wymiarze lokalizacji, z której dokonano odwiedzin strony. Dalszym celem artykułu będzie pokazanie sposobu jak dostać się do tych danych z poziomu aplikacji Java oraz jak przenieść te dane do naszej bazy danych.

Nasuwa się pytanie: po co przenosić takie dane do naszej bazy skoro mamy do tego dostępną i gotową końcówkę kliencką? Odpowiedzi jest tak naprawdę tyle ile pomysłów na wykorzystanie tych danych. Poniżej przedstawione zostało kilka przykładów potencjalnego wykorzystania danych zaimportowanych z GAnalytics.

Poprawnie skonfigurowany system GAnalytics potrafi weryfikować akcje wykonywane przez użytkowników. Można zatem za pomocą API pobierać informację o najczęściej wyszukiwanych towarach na naszej witrynie biorąc pod uwagę lokalizację z jakiej dokonano przeszukiwania i dynamicznie dostosowywać np. kampanie reklamowe do lokalizacji. Czyli mając dużą korporacyjną sieć sprzedaży możemy weryfikować potrzeby rynku i w pełni automatyczny sposób reagować na nie w możliwie krótkim czasie. Kolejnym ciekawym rozwiązaniem opartym o dane pobierane z GAnalytics jest dynamiczna prezentacja na mapie ilości osób zalogowanych z podziałem np. na miasta. Tak zdefiniowana konfiguracja pozwala np. na połączenie usług analitycznych z innym produktem Google jakim są GMaps. Innym ciekawym sposobem wykorzystania GAnalytics jest możliwość zautomatyzowanego nadzorowania obciążenia rozproszonej sieci witryn. Rozważmy firmę, która zajmuje się zagadnieniami SEO, która posiada wiele witryn internetowych na różnych serwerach w całym świecie. Korzystając z API GAnalytics można weryfikować, które witryny są aktualnie obciążone, a które od dawna nie są przeglądane. Tak jak już wyżej wspomniano, ile pomysłów tyle potencjalnych zastosowań. Zatem jeśli masz pomysł jak wykorzystać GAnalytics w sposób wykraczający poza wykorzystanie standardowego interfejsu użytkownika zapraszam do dalszej części artykułu w celu zapoznania się z podstawami API GAnalytics opartego o bibliotekę GData.

GAnalytics i GA API, czyli od czego zacząć?

Od czego zacząć przygodę z integracją GAnalytics z twoją aplikacją? Na wstępie musisz posiadać aktywne konto GAnalytics. Jeśli jeszcze go nie masz, wejdź na stronę http://www.google.com/analytics i zapisz się do korzystania z usługi. Mając już aktywne konto należy dodać kod źródłowy do badanej witryny i poczekać aż zaczną się gromadzić dane. Aby skorzystać z API Java pozwalającego na dostęp do usług Google należy zalogować się na konsolę interfejsów: https://code.google.com/apis/console i wskazać wybraną usługę. W naszym przypadku będzie to Analytics API. Klikając na przycisk OFF zmienisz jego stan na ON przez co otwieramy sobie furtkę do korzystania z API.

Należy zwrócić uwagę, iż Google z góry określa limit odwołań poprzez API do usług. Jak widać na powyższym obrazie limit ten dla GAnalytics określony jest na 50 tysięcy zapytań poprzez API na dzień. Istotne jest tu także słowo „Courtesy”, czyli ang. grzecznościowo, gdyż limit ten może zostać zmieniony wraz ze zmianą regulaminu. Dodatkowo dla API GAnalytics obowiązuje drugi limit pozwalający na równolegle realizowanie maksymalnie 10 zapytań pochodzących z jednego adresu IP. Biorąc pod uwagę powyższe limity oraz maksymalną wielkości paczki danych dla pojedynczego zapytania wynoszący 10 tysięcy elementów, warto zintegrować GAnalytics z własną bazą danych. W dalszej części artykułu zaprezentowane zostaną czynności, które pozwolą przenieść dane z repozytorium google do twojej bazy danych.

Krok 1: Integracja i odniesienie się do kontekstu domeny

Pierwszym krokiem jaki należy wykonać jest stworzenie projektu Java lub wykorzystanie już istniejącego projektu w Javie w wersji co najmniej 1.5. Następnie należy pobrać biblioteki klienckie w chwili pisania tego artykułu są to:

gdata-analytics-2.1.jar gdata-core-1.0.jar google-collect-1.0-rc1.jar jsr305.jar

ze strony http://code.google.com/p/gdata-java-client i dodać je do CLASSPATH aplikacji. Pierwsza zaprezentowana aplikacja pozwoli zalogować się do Google Analytics oraz pobrać listę profili witryn podpiętych do naszego konta. Poniższy kod stanowić będzie bazę do dalszego rozwoju, więc uwzględni ono całą strukturę klasy Java. W dalszej części dodawane będą tylko metody niezbędne do wykonania kolejnych czynności. W celu ich uruchomienia należy odpowiednio usuwać komentarz przed ich wywołaniami w metodzie main klasy GoogleAnalyticsAPIClient.

package pl.org.ploug.gaintegration;
import com.google.gdata.client.analytics.AnalyticsService;
import com.google.gdata.util.AuthenticationException;
public class GoogleAnalyticsAPIClient {private static final String GOOGLE_APIS_APPLICATION_NAME = „API Project”;
private static final String GOOGLE_USERNAME = „tu_podaj_swój_login_w_usługach_google”;
private static final String GOOGLE_PASSWORD = ” tu_podaj_swoje_haslo_w_usługach_google ” ;
public static void main(String[] args) throws Exception

{
// Logowanie do Google Analztics
AnalyticsService myAnalyticsService = authenticateGoogleAnalzticsAccount();

// Poniżej przedstawione są wywołania metod, opisane w dalszej części artykułu
// Odkomentuj wywołania w celu uruchomienia poszczególnych metod
// showGoogleAnalyticsProfiles(myAnalyticsService);
// String tableID = getTableIdForDomainByName(myAnalyticsService,”www.domena2.com”);
// DataQuery dataQuery = preapreGoogleAnalyticsQueryObject(tableID);
// processGAnalyticsQuery(myAnalyticsService, dataQuery);
// processGADataMigration(myAnalyticsService, dataQuery);
}

/**
* Metoda weryfikujaca autentykująca użytkownika
* @return zalogowany obiekt AnalyticsService pozwalajacy na interakcje z Google Analytics
*/

private static AnalyticsService authenticateGoogleAnalzticsAccount()

{
AnalyticsService myAnalyticsService = new AnalyticsService(GOOGLE_APIS_APPLICATION_NAME);
try

{
myAnalyticsService.setUserCredentials(GOOGLE_USERNAME, GOOGLE_PASSWORD);
System.out.println(„Uzytkownik „+GOOGLE_USERNAME+” zalogowany w Google Analytics”);
} catch (AuthenticationException e) {

System.err .println(„Blad podczas logowania: ” + e.getMessage());
System.exit(0);
}

return myAnalyticsService;
}
}

Posiadając tak zdefiniowaną klasę i podając odpowiednio login i hasło powinieneś móc poprawnie zalogować się na serwerze Google poprzez API. Ponieważ Google Analytics pozwala na pracę z wieloma witrynami, kolejnym etapem niezbędnym do dalszej pracy będzie pobranie listy profili witryn do których twój użytkownik ma dostęp. Poniższa metoda pobiera witryny zintegrowane i prezentuje ich identyfikatory.

private static void showGoogleAnalyticsProfiles(AnalyticsService myAnalyticsService) throws Exception {

//wskazanie URL do informacji o kontach uzystkownika
URL feedAccountsUrl = new URL(„https://www.google.com/analytics/feeds/accounts/default”);

//pobranie informacji o kontach
AccountFeed feed = myAnalyticsService.getFeed(feedAccountsUrl, AccountFeed.class);

for (AccountEntry accountEntry : feed.getEntries())

{
System.out .print(„Identyfikator witryny: „+ accountEntry.getId() + ” „);
String accountEntryTableID = accountEntry.getTableId().getValue();
String accountEntryProfile = accountEntry.getTitle().getPlainText();
System.out .print(„TABLE_ID: ” + accountEntryTableID + ” „);
System.out .println(„Witryna: ” + accountEntryProfile);
}
}

Wynikiem działania powyższej funkcji jest wyświetlenie informacji o wszystkich zintegrowanych witrynach. Szczególnie istotnym jest tu identyfikator TABLE_ID, który w dalszej części artykułu będzie wskazywał kontekst witryny, czyli główne źródło przeszukiwań. Dla przykładowego zestawu 3 domen monitorowanych przez GAnalytics otrzymamy następujący wynik wywołania w/w metody.

Identyfikator witryny: http://www.google.com/analytics/feeds/accounts/ga:00000001 TABLE_ID: ga:00000001 Witryna: www.domena1.com
Identyfikator witryny: http://www.google.com/analytics/feeds/accounts/ga:00000002 TABLE_ID: ga:00000002 Witryna: www.domena2.com
Identyfikator witryny: http://www.google.com/analytics/feeds/accounts/ga:00000003 TABLE_ID: ga:00000003 Witryna: www.domena3.com

Elementy wytłuszczone zastępowane są faktycznymi identyfikatorami w systemie Google Analytics. Posiadając zatem taką wiedzę, jesteśmy w stanie pobrać identyfikator TABLE_ID na podstawie nazwy domeny. Przyjmijmy zatem, że zależy nam na pobraniu statystyk tylko dla www.domena2.com.

private static String getTableIdForDomainByName(AnalyticsService myAnalyticsService,String domainName) throws Exception{
URL feedAccountsUrl = new URL(„https://www.google.com/analytics/feeds/accounts/default”);
AccountFeed feed = myAnalyticsService.getFeed(feedAccountsUrl, AccountFeed.class);
for (AccountEntry accountEntry : feed.getEntries())

{
String accountEntryProfile = accountEntry.getTitle().getPlainText();
if (accountEntryProfile.equals(domainName))

{
return accountEntry.getTableId().getValue();
}
}

throw new Exception(„Brak monitoringu domeny: „+domainName);
}

Powyższą metodę należy wykonać z podaniem nazwy domeny. Jeśli takowa jest monitorowana otrzymujemy jej TABLE_ID i na tej podstawie możemy wykonywać zapytania.

Krok 2: O co i jak pytać API GAnalytics

Na wstępie należy zastanowić się, o co możemy pytać API. Wszelkie zapytania powinny zawierać informację o metryce, czyli zagregowanej wartości oraz o wymiarze, w którego kontekście wykowywana jest agregacja. GAnalytics API udostępnia nam metryki i wymiary dostępne przez interfejs graficzny. Należy pamiętać, że nie o wszystkie metryki można pytać w kontekście każdego wymiaru. Pełna specyfikacja dostępnych metryk i wymiarów dostępna jest pod adresem:

http://code.google.com/intl/pl/apis/analytics/docs/gdata/dimsmets/dimsmets.html

Jak zatem korzystać z powyższych informacji? Budowę zapytania należy rozpocząć od stworzenia obiektu DataQuery i uzupełnienia go parametrami podstawowymi takimi jak: TABLE_ID, czyli kontekst domeny, dla której zbierane były dane, zakres czasowy zapytania oraz metryki i wymiary. Dodatkowo w treści zapytania można zawrzeć informację o sposobie sortowania oraz określić limit ilości danych pobieranych w pojedynczym rezultacie zapytania. Poniższy kod prezentuje kwerendę wybierającą liczbę wizyt oraz czas spędzony na wizytach w okresie od 01.01.2010 do 10.01.2012 posortowanych malejąco po liczbie wizyt (określone przez minus przed nazwą metryki) w wymiarze kraju skąd było wejście:

private static DataQuery preapreGoogleAnalyticsQueryObject(String tableID){
DataQuery dataQuery = null ;
try{
dataQuery = new DataQuery(new URL(„https://www.google.com/analytics/feeds/data”));
} catch (MalformedURLException e) {

System.err .println(„Blad podczas nawiazywania polaczenia: ” + e.getMessage());
System.exit(0);
}

dataQuery.setIds(tableID);
dataQuery.setStartDate(„2010-01-01”);
dataQuery.setEndDate(„2012-01-01”);
dataQuery.setDimensions(„ga:country”); //definicja wymiaru, bądź wymiarów
dataQuery.setMetrics(„ga:visits,ga:timeOnSite”); //definicja metryki, bądź metryk
dataQuery.setSort(„-ga:visits”); //sortowanie malejące
dataQuery.setMaxResults(4);
return dataQuery;
}

Warto zwrócić uwagę na fakt, iż w zapytaniu może być kilka metryk. Samo stworzenie obiektu DataQuery nie powoduje wykonania zapytania. W celu obsłużenia wykonania zapytania należy wywołać poniżej zamieszczoną metodę.

private static void processGAnalyticsQuery(AnalyticsService myAnalyticsService, DataQuery dataQuery) throws Exception{
DataFeed feedData = myAnalyticsService.getFeed(dataQuery, DataFeed.class );
List<DataEntry> entries = feedData.getEntries();
for(DataEntry dataEntry: entries){
System.out .print(„Kraj: „+ dataEntry.stringValueOf(„ga:country”) + „: ” );
System.out .println( dataEntry.longValueOf(„ga:visits”) + ” odwiedzin”);
//System.out.println( dataEntry.doubleValueOf(„ga:visits”) + ” odwiedzin„);
//System.out.println( dataEntry.stringValueOf(„ga:visits”) + ” odwiedzin„);
}
}

W metodzie tej przekazywany do obiektu DataFeed jest rezultat wykonanego zapytania. Następnie, jeśli istnieją elementy spełniające kryteria wyszukiwania można dalej je przetwarzać. W tym przypadku wynik zapytania podlega wyświetleniu. Przykładowy rezultat wykonania powyższego kodu został zamieszczony poniżej:

Kraj: Poland: 32846 odwiedzin
Kraj: United Kingdom: 513 odwiedzin
Kraj: Germany: 462 odwiedzin
Kraj: United States: 205 odwiedzin

Warto zwrócić uwagę na fakt, iż dane można pobrać do 3 typów zmiennych: jako ciąg tekstowy za pomocą metody stringValueOf(PAR), jako liczba zmiennoprzecinkowa za pomocą metody doubleValueOf(PAR) oraz jako liczba całkowita za pomocą metody longValueOf(PAR) podstawiając pod PAR nazwę metryki bądź wymiaru. W przypadku pobierania metryki, bądź wymiaru, która nie została uwzględniona w DataQuery zwrócona zostanie wartość null. Jeśli natomiast chcesz mieć pewność, czy dana metryka bądź wymiar została ujęta w data query możesz skorzystać z metod getMetrics() oraz getDimensions() zwracających odpowiednio wszystkie metryki oraz wszystkie wymiary zawarte w wyniku zapytania. Powyżej przedstawione metody poprzez odpowiednie kształtowanie zapytania pozwolą ci na pobieranie danych źródłowych z systemu GAnalytics i dowolne przetwarzanie ich we własnej aplikacji.

Krok 3 ostatni: Przenosimy dane

Wiemy już jak pobrać dane, zatem przejdziemy do następnego etapu, czyli ich migracji do bazy Oracle. W celu przeniesienia danych wykorzystamy mechanizm JDBC (biblioteka ojdbc.jar) przepisując paczkami dane z GAnalytics API. Ponieważ samo JDBC było już opisywane na łamach PLOUG, pokazana zostanie jedynie metoda odpowiedzialna za przepakowanie danych. Załóżmy zatem, że wykorzystamy wcześniej zdefiniowany obiekt zapytania DataQuery. Przyjmijmy także, że mamy tabelę TABELA_TESTOWA z dwoma kolumnami, w których zapisane będzie państwo, oraz liczba wizyt w czasie określonym we wcześniej zdefiniowanym zapytaniu. Poniższy kod wykona zapytanie poprzez GAnalytics API i przekaże paczkę danych w postaci zestawu insertów do wstawienia do naszej bazy danych:

private static void processGADataMigration(AnalyticsService myAnalyticsService, DataQuery dataQuery) throws Exception {
DataFeed feedData = myAnalyticsService.getFeed(dataQuery, DataFeed.class);
List<DataEntry> entries = feedData.getEntries();
if (entries.isEmpty()) {
System.out.println(„Brak danych do migracji.”);
return;}
String sqlInsertSQL = „INSERT INTO TABELA_TESTOWA (KOL_1, KOL_2) VALUES (?, ?)”;
PreparedStatement ps = null;
Connection conn = null ;
try {
conn = getConnection();
ps = conn.prepareStatement(sqlInsertSQL);
for (DataEntry dataEntry : entries)

{
ps.setString(1, dataEntry.stringValueOf(„ga:country”));
ps.setLong(2, dataEntry.longValueOf(„ga:visits”));
ps.addBatch();
}

ps.executeBatch(); //faktyczne wykonanie paczki insertów
} catch (SQLException e) {
throw new RuntimeException(„Blad przetwarzania SQL: „+e.getMessage(),e);
} finally {
closeDbResources(ps, null, conn);
}
}

Jak widać powyżej wszystkie elementy z zapytania API umieszczane są poleceniem addBatch w paczce, która finalnie przesyłana jest do bazy danych metodą executeBatch. Powoduje to zmniejszenie narzutu związanego z wieloma osobnymi insertami przechodzącymi przez connector Oracle JDBC. Jeśli paczka z GAnalytics miałaby duży rozmiar warto rozważyć sekwencyjne wywoływanie metody executeBatch w pętli obsługi danych pochodzących z API Google.

Spróbuj teraz samodzielnie stworzyć tabelę testową dla dowolnie wybranych przez siebie metryk i wymiarów i przenieś te dane do swojej bazy danych. Patrząc na powyższy kod nie powinno to już stanowić dla ciebie żadnego problemu.

Podsumowanie

Powyższy artykuł pokazuje jak w prosty sposób zintegrować się niemalże z każdą aplikacją udostępnianą przez Google, a w szczególności z Google Analytics. Zaprezentowano proces pobierania lokalizacji opartej o nazwę państwa i zapis jej zwykłej bazie danych Oracle. Jeśli interesuje cię szersza analiza np. geograficzna i chciałbyś zintegrować się z innym produktem Oracle np. ze Spatial’em możesz za pomocą GA API pobierać szerokość i długość geograficzną. To tylko drobny przykład możliwości wykorzystania API do integracji. Zachęcam do zapoznania się z innymi wymiarami i metrykami w celu poznania pełnego spektrum dostępnych opcji.

Zasadniczo czy będziesz integrował się GAnalytics, czy z inną aplikacją Google schemat jest zawsze podobny. Na wstępie logujesz się do systemu, a następnie przekazujesz odpowiednio przygotowane zapytanie do wykonania po stronie serwera Google. Proces ten jest zatem prosty i nie wymaga szczególnie wysokiej wiedzy z zakresu programowani w Javie czy Pythonie. Jeśli jesteś programistą baz danych np. PL/SQL i Java jest Ci obca to zamieszczone przykłady powinny Ci wystarczyć do samodzielnego stworzenia aplikacji migrującej dane między systemami. Z drugiej strony posiadanie wiedzy z zakresu programowania w Javie oraz poszczególnych API Google daje możliwość zautomatyzowania wielu procesów, które do tej pory robiłeś ręcznie. Zachęcam zatem do własnoręcznego sprawdzenia jakie korzyści może przynieść dla ciebie taka integracja.