Wizualizacja pozycji fraz ze stat4seo w Looker Studio

05 marca, 2025 Wojciech Grądzki
Analityka Narzędzia SEO

Dlaczego powstała własna wizualizacja?

Stat4Seo to świetne narzędzie do monitorowania pozycji fraz w Google, które wygrywa z konkurencyjnymi rozwiązaniami przede wszystkim ceną. Największą bolączką narzędzia jest sposób prezentowania pozycji w niezbyt atrakcyjnym i funkcjonalnym interfejsie, uniemożliwiającym obserwację zmiany pozycji w dłuższej perspektywie. Częściową odpowiedzią na te wady było stworzenie Connectora do Looker Studio, który udostępniłem jakiś czas temu. Connector pozwala na prezentowanie danych ze Stat4Seo w raportach Lookera, a dzięki raportowi z listą fraz które zanotowały największe wzrosty lub spadki w porównaniu do poprzedniego miesiąca, zwiększa funkcjonalność narzędzia:

Zestawienie pozycji konkretnych fraz dzień po dniu nadal jednak jest mało czytelne, nawet mniej czytelne niż w interefejsie Stat4Seo. Ograniczeniem jest tu wachlarz opcji prezentacji danych jakie dostępne są domyślnie w Looker Studio. Mamy w zasadzie możliwość dodania tabeli z pozycjami liczbowymi/tekstowymi i to tyle. Rozwiązaniem pozwalającym na prezentowanie pozycji w bardziej atrakcyjnej formie jest stworzenie własnego wizualizacji dla Lookera. Wizualizację tworzy się w javascripcie z pewnymi ograniczeniami, w szczególności sam skrypt nie może pobierać żadnych danych z zewnątrz, musi korzystać ze źródła danych dostarczonego w ramach Looker Studio. Więcej informacji na temat tworzenia własnych wizualizacji dla Looker Studio dostępne jest pod adresem https://developers.google.com/looker-studio/visualization?hl=pl. Na potrzeby raportowania pozycji fraz w atrakcyjny i czytelny sposób, stworzyłem własną wizualizację. Przykładowy raport prezentujący możliwość wykorzystania wizualizacji dostępny jest pod adresem https://lookerstudio.google.com/s/vxlLZ7lsk30.

Wizualizacja pozycji wg Stat4Seo

Funkcjonalność wizualizacji

  • Prezentowane dane:
    • Prezentacja fraz z podziałem na kategorie;
    • Średnia pozycja frazy w poprzednim miesiącu;
    • Zmiana pozycji w stosunku do wcześniejszego miesiąca;
    • Średnia pozycja rok wcześniej;
    • Zmiana pozycji w stosunku do zeszłego roku;
    • Pozycja w momencie rozpoczęcia monitoringu danej frazy;
    • Data rozpoczęcia monitoringu;
    • Miniwykres pozycji za poprzedni miesiąc;
    • Miniwykres pozycji dla ostatnie 12 miesięcy;
  • Tooltipy wyjaśniające znaczenie poszczególnych wartości;
  • Możliwość filtrowania fraz wg wpisanego fragmentu.

Instrukcja korzystania z modułu krok po kroku:

Krok 1. Przygotuj dane w bazie

Stat4Seo dane zapisuje w bazie danych MySQL. Jeśli nie pamiętasz namiarów na bazę danych, znajdziesz ją w pliku /includes/config.php w katalogu w którym zainstalowany jest Stat4Seo. Pamiętaj tylko, że jeśli nazwa hosta bazy danych to „localhost”, to właściwym adresem hosta jest adres serwera na którym zainstalowane jest Stat4SEO. Gdy znasz namiary bazy, musizz połączyć się z nią przy pomocy narzędzia umożliwiającego zadawanie zapytań do bazy danych. Ja korzystam z MySQL Workbench, ale może to być phpMyAdmin dostępny zazwyczaj z poziomu hostingu. Na potrzeby raportu należy stworzyć widok w bazie, posiadający wszystkie niezbędne dane. Poniżej zapytanie tworzące taki widok, wykonaj je po połączeniu się z bazą danych:

 

CREATE OR REPLACE VIEW raport_looker_studio AS
SELECT
mk.key AS keyword,
mkc.cat_name AS category,
mh.key_id,
mh.site_id,
— Średnia pozycja w poprzednim miesiącu
IFNULL(
CASE
WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 1 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 1 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END
END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 1 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 1 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END
END))
END,
0
) AS avg_position_last_month,
— Pozycja sprzed 2 miesięcy
IFNULL(
CASE
WHEN ROUND(AVG(CASE
WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 2 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 2 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100
THEN 0
ELSE ROUND(AVG(CASE
WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 2 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 2 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END))
END,
’-’
) AS avg_position_two_months_ago,
— Pozycja rok temu (grudzień poprzedniego roku w przypadku stycznia bieżącego)
IFNULL(
CASE
WHEN ROUND(AVG(CASE
WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 13 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 13 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100
THEN 0
ELSE ROUND(AVG(CASE
WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 13 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 13 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END))
END,
’-’
) AS avg_position_last_year,
— Pozycja startowa i data startu
MIN(mh.data) AS start_date,
(SELECT mh_start.position
FROM monitoring_history mh_start
WHERE mh_start.key_id = mh.key_id
ORDER BY mh_start.data ASC LIMIT 1) AS start_position,
— Miniwykres: pozycje dzień po dniu w poprzednim miesiącu
GROUP_CONCAT(CASE
WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 1 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 1 MONTH)
THEN mh.position
END ORDER BY mh.data ASC) AS daily_positions_last_month,
— Miniwykres: średnie miesięczne z ostatnich 12 miesięcy (odwrócona kolejność i zamiana 100 na 0)
CONCAT(
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 12 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 12 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 12 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 12 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 11 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 11 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 11 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 11 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 10 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 10 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 10 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 10 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 9 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 9 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 9 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 9 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 8 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 8 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 8 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 8 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 7 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 7 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 7 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 7 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 6 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 6 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 6 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 6 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 5 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 5 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 5 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 5 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 4 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 4 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 4 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 4 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 3 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 3 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 3 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 3 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 2 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 2 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 2 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 2 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’), ’,’,
IFNULL(CASE WHEN ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 1 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 1 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) = 100 THEN 0
ELSE ROUND(AVG(CASE WHEN mh.data BETWEEN DATE_FORMAT(CURDATE() – INTERVAL 1 MONTH, '%Y-%m-01′)
AND LAST_DAY(CURDATE() – INTERVAL 1 MONTH)
THEN CASE WHEN mh.position = 0 THEN 100 ELSE mh.position END END)) END, ’-’)
) AS avg_positions_last_12_months
FROM
monitoring_history mh
JOIN
monitoring_keys mk ON mk.key_id = mh.key_id
LEFT JOIN
monitoring_keys_cats mkc ON mk.key_cat = mkc.cat_id
GROUP BY
mh.key_id, mk.key, mkc.cat_name, mh.site_id;

Krok 2. Wstaw dane do raportu

Utwórz nowy raport Looker Studio, od razu zostaniesz poproszony o dodanie źródła danych:

Wybierz źródło danych MySQL:

Dodaj Mysql

 

Wpisz namiary na bazę danych Twojej instancji Stat4Seo (1), wybierz  Uwierzytelnij (2), Niestandardowe zapytanie (3) i wpisz zapytanie jak niżej (4):

 

Konfiguracja źródła danych

select * from raport_looker_studio where site_id=372

gdzie ostatnia liczba to identyfikator serwisu w Stat4Seo. Jak zdobyć ten identyfikator?

Otwieramy panel Stat4Seo i wybieramy interesujący nas serwis (1). Identyfikator serwisu pojawia się jako ostatni parametr w adresie url:

Identyfikator serwisu w Stat4SEO

Po dodaniu źródła w raporcie automatycznie pojawi się jakaś tabelka z danymi, możesz ją usunąć

Krok 3. Dodaj wizualizację Stat4SEO

Wybierz opcję dodawania wizualizacji stworzonej przez społeczność (1) i a następnie +Odkryj więcej (2):

Dodaj własną wizualizację

 

Następnie  w polu „Ścieżka pliku manifestu” wpisz wartość gs://stat4seo (1), wybierz opcję Wyślij (2) i kliknij w znalezioną wizualizację (3):

Dodaj wizualizację

W kolejnym kroku wyraź zgodę na dodanie tej wizualizacji i po dodaniu wizualizacji do raportu, ponownie wyraź tę samą zgodę:

Wyraź zgodę

Po wyrażeniu zgody, powinieneś otrzymać widok jak poniżej:

Nieskonfigurowana wizualizacja

Wizualizacja wymaga konfiguracji, tj. przypisania odpowiednich pól ze źródła danych. Robimy to wybierając pola z listy w panelu Konfiguracja po prawej stronie. Na koniec Konfiguracja wizualizacji powinna wyglądać następująco:

Końcowa konfiguracja

 

Jeśli wszystkie kroki zostały zrealizowane prawidłowo, w raporcie powinna pojawić się tabela ze wszystkimi danymi. Możesz dostosować styl tabeli zmieniając fonty czy kolory w zakładce Styl.

Jeśli będziesz miał gotowy, działający raport na własnej bazie, stworzenie kolejnego raportu wymagać będzie skopiowania gotowej tabeli do nowego raportu i zmiany id serwisu w zapytaniu do bazy danych przez edycję źródła danych.

Wizualizacja dostępna jest za darmo, również do wykorzystania komercyjnego. Jedynym ograniczeniem jest wyświetlanie informacji o autorze na dole  raportu oraz watermark  z logiem SAMOSEO wewnątrz tabeli. Jeśli jesteś zainteresowany usunięciem tych elementów, napisz na biuro@samoseo.pl.

Odpowiedzi na pytania, które mogą się pojawić:

1. Czy można wykorzystać wizualizację do prezentacji danych z innych systemów do monitorowania pozycji, np. SENUTO, WebPozycja czy SeoStation?

To kwestia dostarczenia danych do wizualizacji, musiałyby one być w formacie identycznym jak zwraca zapytanie do spreparowanego widoku. Najprościej byłoby postawić bazę danych zawierającą tabelę z przygotowanymi danymi. Poszczególne systemy do mierzenia pozycji udostępniają API, które umożliwia pobieranie danych. Zadanie nie wydaje się specjalnie trudne.

2. Czy twórca wizualizacji lub SAMOSEO mają wgląd w dane moich klientów?

Nie, cały proces dzieje się po stronie Lookera i użytkownika raportu. Nie mamy dostępu do tych danych.

3. Czy możecie udostępnić edytowalną wersję raportu ze skonfigurowaną wizualizacją?

Niestety nie. Miałbyś wtedy dostęp do namiarów na naszą bazę i mógł sprawdzić listę naszych klientów wraz z monitorowanymi frazami. Jeśli prześlesz mi namiary na Twoją bazę, mogę ci taki raport przygotować.

4. Czy można podzielić raport na strony?

Domyślnie raport pokazuje wszystkie frazy dla danego serwisu. Jeśli chciałbyś podzielić raport na strony, to należałoby zrobić kopię źródła danych dla każdej ze stron i zmodyfikować zapytanie do bazy, np. przy stronicowaniu po 100 fraz:

select * from raport_looker_studio where site_id=372 order by category,keyword limit 1,100

select * from raport_looker_studio where site_id=372 order by category,keyword limit 100,200

5. Czy można zmienić zakres czasowy prezentowanych danych?

Wizualizacja służy do prezentowania miesięcznych raportów dla klientów. Jeśli chciałbyś mieć analogiczną tabelę z aktualnymi pozycjami, należałoby zmodyfikować zapytanie tworzące widok w bazie Stat4Seo

6. Nie mam fraz podziału na kategorie, czy wizualizacja będzie działać prawidłowo?

Tak, w takiej sytuacji kategorie nie zostaną wyświetlone w tabeli.

 

Wszelkie uwagi/pytania/sugestie dotyczące wizualizacji proszę przesyłać na wyżej podany adres lub w komentarzu  do tego wpisu.

 

 

Poprzedni wpis
Jak mierzyć wyświetlenia w ChacieGPT i obliczyć CTR poprzez analizę logów serwera? Case Study 4 przypadków
Wojciech Grądzki
Autor wpisu

Wojciech Grądzki

Przez 17 lat pracował w Wirtualnej Polsce, od początku przy wyszukiwarkach a przez 10 ostatnich lat kierował działaniami SEO Grupy WP pracując przy wp.pl, o2.pl, money.pl, abczdrowie.pl, SportoweFakty, Pudelek. Od 2017 roku, wspólnie ze współpracownikiem z WP, Tomkiem Mielewczykiem prowadzi agencję SAMOSEO.

0 komentarzy

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Powiązane artykuły