Obrabiamy dane z Google Analytics w arkuszu kalkulacyjnym

Jak każdy początkujący bloger – już to kiedyś pisałem – bezustannie myślę o tym, ilu czytelników odwiedzi serwis do końca dnia, jeśli o godzinie X licznik wskazuje Y gości. Prognozę tego typu można wysnuć z danych historycznych Google Analytics, jednak chyba potrzebne byłoby do tego jakieś programowanie, prawda?

Nieprawda! Wystarczy nam do tego dowolny arkusz kalkulacyjny. Mógłby to być Excel, ale zamiast niego użyjemy Arkuszy Google, bo są dostępne całkowicie gratis no i nie trzeba ich instalować.

Jak każdy początkujący bloger bezustannie myślę o tym, ilu czytelników odwiedzi serwis do końca dnia, jeśli o godzinie X licznik wskazuje Y gości. Prognozę można wysnuć z danych historycznych Google Analytics, jednak potrzebne byłoby do tego jakieś programowanie, prawda?

— Informatyk Zakładowy (@InfZakladowy) October 5, 2020

Poniższy tekst powinien być zrozumiały dla każdego, kto kiedykolwiek używał arkusza kalkulacyjnego. Zachęcam do powtórzenia kroków opisanych w niniejszym tekście – na końcu artykułu znajdziesz surowe dane wejściowe które będziemy tu obrabiać. Zaczynajmy!

W panelu Google Analytics serwisu Informatyk Zakładowy ustawiłem zakres statystyk od 1 kwietnia do przedwczoraj, w ujęciu godzinowym. Jak być może pamiętacie, statystyki niniejszego bloga są jawne, więc mamy do czynienia z prawdziwymi liczbami z ostatniego półrocza.

W tym okresie bloga odwiedziło ponad 114 tysięcy użytkowników. Klikam przycisk „Eksportuj” i pozycję „Arkusze Google”. Dzięki temu dane zostaną przeniesione wprost do nowego arkusza.

Kilka spostrzeżeń. Po pierwsze – liczba odwiedzających w ujęciu godzinowym wzrosła do ponad 150 tysięcy. Może odwiedziny na przełomie godzin odnotowywane są podwójnie? Zakładam, że zaburzenie rozkłada się proporcjonalnie i nie psuje nam wyników analizy. Po drugie – zamiast daty mamy kolejne godziny numerowane od 0 (północ pierwszego kwietnia 2020) do 4487 (czwarty października, 23:00). Po trzecie – sześć pierwszych wierszy nie przyda nam się do niczego. Tak samo ostatni wiersz – na dolnym skraju arkusza kasujemy komórkę o wartości „150 655”.

Dane pomocnicze

Wywalamy pierwszych sześć wierszy i dodajemy kilkanaście kolumn po prawej stronie (menu „Wstaw”). Będziemy ich potrzebować do utworzenia danych pomocniczych.

Pierwszą kolumną pomocniczą będzie godzina danej doby, policzymy ją za pomocą matematycznej operacji modulo (reszta z dzielenia). Do komórki C2 wstawiamy formułę

=MOD(A2;24)

Następnie kopiujemy zawartość komórki do schowka, zaznaczamy kilkadziesiąt kolejnych komórek w kolumnie C i wklejamy skopiowaną wartość. Upewniamy się, że w wierszu nr 26 obliczona wartość wynosi zero i wszędzie poniżej rośnie cyklicznie od 0 do 23.

Kolumną pomocniczą D będzie data i godzina. Skorzystamy z funkcji DATE (polski Excel: DATA) by ustawić początek przedziału na dzień 2020-04-01 i w każdym kolejnym wierszu dodawać do niego liczbę godzin z kolumny A. Godziny musimy dzielić przez 24, bo przy dodawaniu dat podstawową jednostką jest dzień. Formuła w komórce D2 będzie miała wartość

=DATE(2020;4;1)+A2/24

W kolumnie E zrobimy podobnie, ale zaokrąglimy wartość w dół do pełnego dnia (funkcja FLOOR, w polskim Excelu ZAOKR.DÓŁ). Formuła komórki E2:

=DATE(2020;4;1)+FLOOR(A2/24)

Ponownie kopiujemy formuły D2 i E2 kilkadziesiąt komórek w dół. Zmieniamy przy okazji sposób formatowania kolumn D i E tak, by uwzględniał godzinę (ikonka „123”, pozycja „data i godzina”). Ja dla czytelności obrazków zrezygnowałem z sekund oraz zablokowałem dwie pierwsze kolumny i pierwszy wiersz (menu „Widok”).

Agregacje

Teraz będzie pierwszy z trudniejszych trików – wyznaczymy sumę liczby odwiedzin każdego dnia, używając do tego formuły SUMIF (polski Excel: SUMA.JEŻELI). W komórce F2 wstawiamy formułę:

=SUMIF(E:E;E2;B:B)

Jej znaczenie jest następujące: sumuj wszystkie pozycje z kolumny B z tych wierszy, gdzie pole w kolumnie E ma wartość taką samą, jak komórka E2. Tradycyjnie kopiujemy tę formułę w dół.

W polu G2 wstawimy prostą formułę

=100*B2/F2

która – skopiowana w dół – powie nam, jaki procent dobowych odwiedzin miał miejsce w danej godzinie. Warto też nieco zmniejszyć precyzję wyświetlanych wartości (zredukować liczbę cyfr po przecinku).

Ostatnią agregacją będzie narastająca suma dobowa, która powie nam, jaki procent z dziennych odwiedzin miał miejsce do danej godziny. Formuła w komórce H2 wykorzysta funkcję warunkową IF (polski Excel: JEŻELI)

=IF(C2=0; G2; G2+H1)

Interpretacja formuły: jeżeli dany wiersz rozpoczyna nowy dzień, wartością formuły jest procent odwiedzin w „zerowej” godzinie doby (programiści numerują wszystko od zera). W przeciwnym razie dodajemy procent z bieżącej godziny do sumy poprzednich godzin. W ten sposób w ostatniej godzinie doby sumowana wartość zawsze dobije do 100%.

To już wszystkie potrzebne agregacje, należy teraz zawartość kolumn od C do H skopiować aż na sam dół, by formuły objęły komplet 4488 wierszy. Jeśli zaznaczymy prostokąt C2:H4489, to skopiowanie pierwszego wiersza zaznaczenia do samego dołu przeprowadzimy skrótem klawiszowym CTRL-D.

Liczymy statystyki dobowe

Czas na obliczenia docelowe – wyznaczymy średni procent realizacji dobowych wizyt o zadanej godzinie na bazie danych z całego półrocza. W obszarze K2:K25 wstaw wartości od 1 do 24 (tym razem liczymy godziny jak ludzie, nie jak programiści). W komórce M2 wstaw formułę

=AVERAGEIF(C:C;K2-1;H:H)

i skopiuj w dół (funkcja AVERAGEIF, polski Excel ŚREDNIA.JEŻELI).

W ten sposób otrzymaliśmy poszukiwane statystyki i możemy ich używać do przewidywania liczby odwiedzających. Jeśli patrzę na licznik odwiedzin o godzinie 11, to mogę spodziewać się, że dobowy wynik będzie średnio trzykrotnie większy. Wartość sprawdzona o godzinie 15 to mniej-więcej połowa dziennej liczby odwiedzin.

Zakres zmienności

Ostatnim parametrem, na jaki spojrzymy, będzie zakres zmienności obliczonego wskaźnika – sprawdzimy 5 i 95 percentyl. W komórce L2 umieść formułę

=ArrayFormula(PERCENTILE.INC(IF(C:C=K2-1;H:H);0,05))

zaś w N2

=ArrayFormula(PERCENTILE.INC(IF(C:C=K2-1;H:H);0,95))

Uwaga – operator „ArrayFormula” sprawia, że użyta zostaje tzw. formuła tablicowa (tutaj objaśnienie dla Excela) ale to bardziej złożony temat i nie będę go tu omawiał.

Skopiuj formuły w dół. W efekcie dostajemy następujące wyniki:

Okazuje się, że rozrzut wyników jest znaczący. O godzinie 15 mamy na koncie średnio nieco ponad połowę dziennych odwiedzin, ale wskazanie to waha się od 1/3 do 2/3. Na wykresie wygląda to tak:

Procent dziennych odwiedzin w poszczególnych godzinach wygląda tak, jak poniżej. Za „górki” odpowiadał prawdopodobnie Wykop – czasem odpalał rano, czasem wieczorem; zawsze sprowadzał nieproporcjonalnie dużo ruchu w krótkim czasie. „Dołki” robiły się wówczas same, przed lub po okresie wzmożonego ruchu.

Podsumowanie

Jeśli chcesz przejść opisane powyżej kroki samodzielnie, oryginalny zestaw danych znajdziesz tutaj (Arkusz Google Docs). Arkusz otworzysz w trybie tylko do odczytu. Aby utworzyć własną kopię z prawami zapisu i pracować na tejże kopii, wybierz menu „Plik” i pozycję „Utwórz kopię”.

Jeśli chcesz obejrzeć arkusz wynikowy, znajdziesz go tutaj (Arkusz Google Docs). Arkusz otworzysz w trybie tylko do odczytu. Aby utworzyć własną kopię z prawami zapisu i pracować na tejże kopii, wybierz menu „Plik” i pozycję „Utwórz kopię”.

Arkusze kalkulacyjne są narzędziem niedocenianym, tymczasem pozwalają na przeprowadzenie wielu różnorakich operacji w sposób szybki i względnie wygodny. Przy większej liczbie przekształceń lub obróbce danych wielowymiarowych skoroszyt Excela czy arkusz Google Docs stają się jednak nieczytelne – wówczas warto zainwestować nieco czasu w naukę wybranego języka programowania.

Python lub R pozwoliłyby pominąć tworzenie zmiennych (kolumn) pomocniczych, zaś sumowania i agregacje zmieściłyby się w kilku linijkach programu. Aby nie być gołosłownym – oto program w języku R realizujący te same obliczenia, które przeprowadziliśmy w arkuszu Google Docs:

dane %>%
  group_by(ymd("2020-04-01")+days(floor(godzina/24))) %>%
  mutate(procent=cumsum(100*uzytkownicy/sum(uzytkownicy))) %>%
  group_by(h = godzina %% 24) %>%
  summarize(
    p5 = quantile(procent, probs = 0.05),
    avg = mean(procent),
    p95 = quantile(procent, probs = 0.95)
  ) %>%
  select(-h)

A oto wynik działania powyższego programu:

Jak widać, wyniki są identyczne. Przewagą programu nad arkuszem kalkulacyjnym jest oddzielenie danych od algorytmów oraz możliwość wygodnego wersjonowania i porównywania różnych wariantów kodu źródłowego. Gdybyśmy chcieli zbadać statystyki nieparzystych piątków z miesięcy zawierających „R” w nazwie, byłaby to dodatkowa linijka programu lub orka na ugorze w arkuszu kalkulacyjnym.

Epilog mówiący, że Excel nie nadaje się do wszystkiego

Dawno, dawno temu, w pewnej firmie pracownicy dostali do wypełnienia arkusz Excela z ankietą osobową. Makro w arkuszu liczyło w tajny sposób wskaźniki opisujące domniemaną wartość i użyteczność pracownika, potem wartości te były wysyłane na zdalny serwer. Ponieważ jednak była to firma programistyczna, pracownicy bez większego trudu złamali 4-znakowe hasło do ukrytego skoroszytu i zapoznali się z formułami stojącymi za wyliczanymi wskaźnikami. Nietrudno zgadnąć, że cały zespół osiągnął 100% we wszystkich kategoriach, zaś nikt z managementu nigdy nie zająknął się ani słowem o wynikach ankiety.