Raport pozycji ze Stat4Seo w Google Data Studio

Aktualizacja: Opisane tu rozwiązanie posiada szereg wad, które rozwiązuje własny Connector opisany tutaj.

Google Data Studio to bardzo wygodne narzędzie do prezentacji danych z różnych źródeł i jest ono chętnie używane do raportowania wyników działań w branży SEO. W sieci można pobrać gotowe szablony raportów wykorzystujących dane z Google Search Console czy Google Analytics, jednak korzystanie wyłącznie z tych źródeł często okazuje się niewystarczające. Przygotowując raport, w którym chcemy przedstawić dodatkowe informacje, najprostszym sposobem jest wklejenie tam screenów z innych zewnętrznych narzędzi, ale to rozwiązanie mało eleganckie no i pracochłonnne. Poniżej opiszę w jaki sposób przygotowujemy jeden z elementów naszego raportu w Google Data Studio – automatyczny raport pozycji z wykorzystaniem danych z popularnego narzędzia do monitoringu pozycji Stat4Seo.

Na końcu znajdziecie link do gotowego raportu, w którym wystarczy podpiąć dostęp do bazy własnej instancji Stat4Seo.

Stat4seo to skrypt instalowany na kliencie serwera, przechowujący dane pozycji w lokalnej bazie MySQL. Jedyne czego potrzebujemy to namiary na bazę danych. Instalując Stat4Seo zakładamy taką bazę, więc z definicji wiemy jakie są do niej dane dostępowe. Jeśli nie wiemy, możemy to sprawdzić w pliku konfiguracyjnym Stat4Seo, który znajduje się w katalogu stat4seo/includes/config.php, gdzie stat4seo to katalog w którym mamy zainstalowane narzędzie. Alternatywnie, możemy dodać nowego użytkownika posiadającego dostęp do tej bazy i dla bezpieczeństwa umożliwić mu jedynie możliwość zadawania zapytań SELECT, a więc uniemożliwić modyfikowanie zawartości bazy.

Większość systemów hostingowych domyślnie blokuje dostęp do baz z zewnątrz. Zakładamy, że tworzony przez nas raport chcemy pokazać klientom, więc ten dostęp powinien być możliwy. W konfiguracji hostingu możemy zazwyczaj znieść to ograniczenie. W panelu Direct Admin, który dostępny jest w używanym przez nas hostingu, można to zrobić dodając znak % jako adres hosta który ma dostęp do bazy danych z zewnątrz:

Ok, mamy dostęp do bazy danych i możemy zabrać się za przygotowanie raportu. Otwieramy Google Data Studio  i wybieramy opcję „Utwórz nowy raport”. Po otwarciu pustego raportu, w prawym dolnym rogu mamy możliwość dodania nowego źródła danych:

Z listy dostępnych rodzajów źródeł, wybieramy MySQL i otwiera się strona z  formularzem, w którym wpisujemy namiary na bazę danych Stat4Seo:

Po zautoryzowaniu połączenia z bazą danych, wybieramy zakładkę 'Niestandardowe zapytanie’ i wklejamy zapytanie pobierające pozycje z bazy Stat4Seo dla określonego serwisu i określonego okresu:

Treść zapytania jakie wykorzystałem jest następująca:

SELECT `key` as klucz,substring(data,9,2) as data,position FROM
 `monitoring_history` left join `monitoring_keys` on
 `monitoring_history`.`key_id`=`monitoring_keys`.`key_id` WHERE
 `monitoring_history`.site_id=68 and data>="2018-08=01" and data<"2018-09-01"

key_id to identyfikator serwisu. Skąd to wziąć?  Najprościej otworzyć panel Stat4Seo, wybrać raport dla interesującego nas serwisu i z paska adresu odczytać identyfikator serwisu:

Zapytanie zwraca wyniki z konkretnego miesiąca. Wadą tego rozwiązania jest fakt, że tworząc raport dla tego samego klienta w kolejnym miesiącu, trzeba będzie je co miesiąc modyfikować. Można skonstruować zapytanie, które będzie zwracać zawsze wyniki dla poprzedniego miesiąca. Tu z kolei wadą jest fakt, że taki raport będzie działać prawidłowo jedynie w miesiącu następnym po tym, którego dotyczy. Ja zdecydowałem się na to pierwsze rozwiązanie.

Po wpisaniu zapytania wybieramy przycisk „Połącz” w prawym górnym rogu.

W kolejnym kroku wybieramy przycisk „Dodaj do raportu”, aby dodać źródło do naszego raportu. Dane w bazie danych Stat4Seo gromadzone są w ten sposób, że dla każdego dnia i każdej frazy dodawany jest osobny rekord do tabeli pozycji. Aby to sensownie zaprezentować musimy skorzystać z tabeli przestawnej.

Po dodaniu takiej tabeli, Google Data Studio automatycznie podepnie ostatnio dodane przez nas źródło danych i uzyskamy efekt widoczny poniżej:

Jest już nieźle, ale raczej chcielibyśmy aby poszczególne dni tygodnia były w nagłówkach kolumn, a frazy w nagłówkach wierszy, szczególnie jeśli mamy większą ilość fraz. Aby to osiągnąć, w panelu 'Dane’ z prawej strony, zamieniamy wartości w pozycjach „Rozmiar wiersza” i „Rozmiar kolumny”.

Na koniec, nieco niżej warto jeszcze ustawić właściwe sortowanie dla wierszy i kolumn. W efekcie, po krótkiej zabawie stylami dostajemy całkiem zgrabną tabelę z pozycjami poszczególnych fraz:

Brakuje mi tu kilku rzeczy, które chętnie widziałbym w takim raporcie, np. największe zmiany pozycji, średnia, liczba fraz w TOP3, TOP10, itp. i w tym kierunku będziemy ten szablon rozbudowywać. Jednak nie bez powodu Google Data Studio wciąż znajduje się w fazie beta – wykonanie na pozór prostych rzeczy nie jest takie proste.

Zachęcamy do eksperymentowania z tym raportem i dzielenia się efektami swojej pracy!

Pod tym adresem  znajduje się gotowy przykładowy raport pobierający pozycje z bazy Stat4Seo. Aby wykorzystać go do własnych celów, musicie wykonać jego kopię oraz podpiąć własne źródło danych zgodnie z wcześniejszym opisem.

Aktualizacja: Opisane tu rozwiązanie posiada szereg wad, które rozwiązuje własny Connector opisany tutaj.