Tagarchief: Lijnsch

Trend-analyse

Excel, , , , , , , , , , ,

Ik was deze week nog eens naar Google-analytics aan het kijken hoe het met de bezoekersaantallen van G-Info gaat.

Er zit nog steeds een stijgende lijn in; altijd leuk!

Wel wat vreemde uitschieters:

  • eind april/begin mei 2015 heeft Google problemen gehad en is er niets geturfd
  • juli en aug van ieder jaar vertoont een dip; waarschijnlijk hebben mensen dan wat anders te doen
  • ook bij de dips van eind december kan ik me wat voorstellen
  • bij nadere bestudering blijken alle punten aan de onderkant op zaterdag en zondag te vallen
  • en de pieken naar boven vallen samen met de momenten dat ik een Nieuwsbrief uitstuur met de aankondiging dat er een nieuw artikel op de site is verschenen.

Maar laten we eens kijken hoe we de trend kunnen analyseren.

Trendlijn-1

Op basis van de gegevens van Google heb ik bovenstaande grafiek gemaakt (je kunt de data uit Analytics eenvoudig exporteren naar Excel):

  1. in het Voorbeeldbestand staan op het tabblad Gegevensset de aantallen bezoekers per dag (beter gezegd de aantallen sessies).
    Daar heb ik ‘natuurlijk’ direct een Excel-tabel van gemaakt (zie het artikel over de 10 voordelen van een tabel en Tabellen deel 2). De naam daarvan is Tabel1.
  2. selecteer de gegevens uit de 2 kolommen door de bovenkant van de kolommen aan te wijzen (de cursor is dan een zwart-pijltje naar beneden; de linker-muis-toets ingedrukt houden).
  3. kies in de menubalk de optie Invoegen/Grafieken/Lijn
  4. het resultaat vindt u terug in het tabblad Graf

In de voorbeeldgrafiek heb ik Excel ook een trendlijn laten toevoegen:

  1. klik ergens met de rechtermuisknop op de grafiek en kies de optie Trendlijn toevoegen
  2. laten we het simpel houden en voor een rechte lijn (de optie Lineair) kiezen
  3. vink ook de 2 onderste opties aan: Vergelijking en R-kwadraat

Excel heeft een mooie (stijgende) lijn getekend; de wiskundige formule die daar bij hoort is
y = 0,0956x – 3998,4

Het getal voor de x is de richtingscoëfficiënt (RC): hoeveel verandert de lijn als x (in dit geval de dag) met 1 toeneemt (dus ruwweg iedere 10 dagen komt er 1 bezoeker meer).
Het andere getal (-3998,4; het snijpunt met de y-as) geeft het aantal bezoekers weer als x gelijk is aan 0 (nul).

NB1 Omdat op de x-as datums staan en voor Excel een datum niets anders is dan het aantal dagen na 1-1-1900, zou het aantal bezoekers op die dag-nul dus flink negatief zijn geweest. De lijn naar het verleden doortrekken is dus onzinnig.

NB2 voor de liefhebbers: Excel gebruikt voor het bepalen van de lijn de zogenaamde kleinste-kwadratenmethode.

NB3 R² (R-kwadraat) is de zogenaamde determinatiecoëfficiënt. Deze geeft aan welk gedeelte van de variatie in de ene variabele door de andere wordt verklaard.
Ofwel: hoe ´betrouwbaar´ is de trendlijn. Is de R² kleiner dan 0,5 dan is het verband zwak tot matig, ligt die tussen 0,5 en 0,75 dan is het verband sterk en anders zeer sterk.

Hoeveel bezoekers mag G-Info op basis van deze trendlijn over een jaar verwachten? Als iedere 10 dagen er 1 extra bezoek bij komt dan zouden er over 365 dagen ongeveer 36 meer moeten zijn dan nu; dus ipv gemiddeld 95 zouden er dat ongeveer 130 zijn.
Dit kunnen we ook makkelijk grafisch laten zien:

  1. klik met de rechtermuisknop op de trendlijn in de grafiek
  2. kies Trendlijn opmaken
  3. en bij Voorspelling/Vooruit vullen we 365 in

Richting, Snijpunt, R.kwadraat en Lijnsch

Om in Excel met de trend te kunnen rekenen moeten we de richtingscoëfficiënt en het snijpunt met de y-as exact weten; we hebben niet genoeg aan een formule in de grafiek.

Uiteraard zijn daar functies voor (zie het tabblad Gegevensset in het Voorbeeldbestand):

  1. in cel H2 willen we de RC plaatsen.
    * tik in =Richting(
    * klik op de bovenrand van Sessies in kolom C (de y-waarden)
    * tik in ;
    * klik op de bovenrand van Dagindex in kolom B (de x-waarden)
    * druk op Enter (Excel zal automatisch de sluithaak van de formule toevoegen)
  2. in H3 is op dezelfde manier de functie SNIJPUNT ingevoerd
  3. en in H4 de functie R.KWADRAAT (denk aan de punt na de R)

Voor de liefhebbers: Excel kent nog een andere functie; deze levert meer statistische resultaten op, LIJNSCH.

In het kort (zie het tabblad Gegevensset in het Voorbeeldbestand):

  1. selecteer 6 lege cellen, 2 kolommen en 3 regels  (in het voorbeeld G12:H14)
  2. in de eerste cel komt de volgende formule:
    =LIJNSCH(Tabel1[Sessies];Tabel1[Dagindex];WAAR;WAAR)
    De y- en x-waarden zijn de bekende kolommen uit de tabel met Google-data.
  3. druk nu niet op Enter, maar Ctrl-Shift-Enter (de zogenaamde CSE-invoer)

De betekenis van de 6 cellen heb ik in de kolommen er naast aangegeven; de cellen G12:H14 hebben corresponderende namen gekregen, zodat formules die er naar verwijzen beter leesbaar zijn.

NB De 2 ‘boven’ de R krijg je als volgt: selecteer in de formulebalk de 2, klik Ctrl-1 (de sneltoets voor Celeigenschappen) en kies de optie Superscript. De m, b en y achter SD hebben de eigenschap Subscript gekregen.

LET OP Lijnsch is een zogenaamde array- of matrix-functie. Het resultaat bestaat niet uit 1 waarde maar uit meerdere, vandaar dat die met CSE wordt ingevoerd. Alle cellen bevatten dezelfde functie, omgeven door accolades.

NB Lijnsch levert meer dan 6 resultaten terug; probeer zelf maar uit door in de eerste stap meer kolommen en regels te selecteren.

De resultaten van bovenstaande functies kunnen we nu gebruiken om voorspellingen voor de toekomst te genereren (zie het tabblad Gegevensset in het Voorbeeldbestand).

Trendlijn-2

Omdat de bezoekersaantallen in de weekenden heel anders zijn dan op werkdagen, heb ik voor een nadere analyse op het tabblad Gegevensset in het Voorbeeldbestand nog een kolom toegevoegd:
=KIEZEN(WEEKDAG([@Dagindex];2);”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)

Ofwel: bepaal de Weekdag van de corresponderende datum in kolom B (Dagindex; we willen dat de week op maandag begint, vandaar de parameter 2). Aangezien deze functie volgnummers oplevert (een maandag is 1, dinsdag 2 etc), heb ik de nummers nog vertaald in teksten mbv de functie Kiezen.

Op basis van deze tabel heb ik een draaitabel gemaakt (zie het tabblad Draai in het Voorbeeldbestand).
Door het veld Weekdag in het Rapportfilter te plaatsen kunnen we snel een overzicht per afzonderlijke dag maken.
Om ook analyses per jaar, kwartaal of maand te kunnen maken heb ik de datums in het veld Dagindex gegroepeerd (zie Groeperen in een draaitabel).

Nog een paar stappen en we kunnen met de resultaten gaan ‘spelen’:

  1. selecteer een cel in de draaitabel
  2. in de menutab Hulpmiddelen voor draaitabellen kiezen we het tabblad Opties
  3. klik dan op de optie Draaigrafiek in het blok Extra
  4. kies een Lijngrafiek en klik OK
  5. voeg een Trendlijn toe, inclusief Vergelijking en R-kwadraat

Na wat lay-outen en het toevoegen van 2 slicers (zie Slicers in Excel) krijgen we een resultaat zoals weergegeven in het tabblad DrGraf in het Voorbeeldbestand.

LET OP de b in de vergelijking van de trendlijn gebruikt voor x=0 niet meer de datum 1-1-1900, maar de eerste datum in de betreffende draaitabel.

Wanneer we in de Slicer Weekdag alleen de maandag kiezen (klikken op de button ma) dan zien we dat de R² al veel beter wordt (0,6381).

LET OP de m in de vergelijking van de trendlijn geeft nu niet de verandering per dag aan, maar de verandering naar de volgende maandag, dus na 1 week.
Wil je de trendlijn 1 jaar vooruit laten ‘kijken’, tik bij Voorspelling/Vooruit dan ook geen 365, maar 52 in (dus over 1 jaar 160 bezoekers op maandag?).

Wil je het resultaat over alle werkdagen zien:

  1. kies in de Slicer Weekdag de button ma
  2. houd Shift ingedrukt en klik op vr
  3. laat Shift los
  4. de draaitabel, draaigrafiek en trendlijn passen zich automatisch aan

Analyse van de jaren laat zien (gebruik de betreffende Slicer), dat de trendlijn voor 2015 ´betrouwbaarder´ is dan die van 2016. De resultaten van vorig jaar worden zwaar beïnvloed door de dips in vakantie-periodes.