Projekt SQL
Bakgrund

Under min andra praktikperiod i utbildningen Digital analytics specialist fick jag arbeta med SQL och lite större datamängder. Jag fick en brief med två uppgifter som jag skulle besvara med hjälp av data. Uppgifterna var följande:

  1. LTV per kohort/månad (dvs. per registreringsmånad: hur mycket intäkt användare i respektive kohort genererat efter X månader)
  2. ARPU per kohort/månad (dvs. per registreringsmånad: hur mycket vi i genomsnitt tjänade per användare i respektive kohort genererat efter X månader)

Kohort betyder grupp och för en sådan här analys var grupperna baserade på en bestämd gemensam egenskap, exempelvis månaden en blev medlem. Det var viktigt att se till att startpunkten för varje kohort speglade det vi ville mäta, till exempel månaden för medlemsstart. Genom att analysera data utifrån dessa grupper kunde vi förstå hur kunder beroende på olika egenskaper interagerade med verksamheten och det kunde vi med enkelhet följa upp över tid för att hitta mönster och samband.

Två vanliga nyckeltal som används i samband med kohortanalys är:

  • LTV (Lifetime Value) handlar om att förstå hur mycket intäkt en användare totalt genererar över tid. Det innebär att man summerar intäkterna månad för månad (eller annan tidsperiod) från början av användarens livscykel och framåt.
  • ARPU beräknar istället den genomsnittliga intäkten per användare under en period (t.ex. en månad).

Tillsammans hjälper dessa nyckeltal till att förstå både hur snabbt en kohort genererar värde och hur mycket varje användare är värd över tid. Det gör det lättare att jämföra kundsegmentet, analysera lönsamhet och fatta beslut kring marknadsföring och retention.

Genomförande

Datainsamlingen för projektet har fokuserat på att ta fram relevant data från företagets databas med hjälp av SQL. Jag har använt ett databasverktyg som heter TablePlus för att skriva och köra SQL mot databasen. I databasen har data för exempelvis intäkter och användare registrerats systematiskt, vilket innebar att varje relevant interaktion och händelse hade sparats med tillhörande information såsom datum och användar-ID. Den registrerade datan strukturerade jag sedan med hjälp av SQL-frågor i TablePlus för att möjliggöra gruppering i kohorter och underlätta vidare analys. Noggrannhet i datainsamlingen säkerställdes genom verifiering av data och jämförelse mellan resultat för att undvika felaktigheter i det materialet som sedan analyserades och visualiserades i Looker Studio.

Utmaningar

Looker Studio är ett kraftfullt visualiseringsverktyg men att använda det för visualisering av kohort-data innebär en del utmaningar i hur datan aggregeras. Med klassisk SQL styr man all gruppering och summering själv via olika funktioner men Looker har istället inbyggda regler för detta vilket kan ge oväntade resultat om man inte är medveten.

När jag kör SQL för att returnera ackumulerad intäkt per användare och sedan använder samma query i Looker för att summera per kohort-månad, finns det risk att Looker summerar redan summerade värden vilket ger en felaktig summa då det blir en dubbelräkning. För att undvika det “för-aggregerade” jag datan i SQL och kontrollerade inställningarna i Looker, till exempel för datatyper och mätvärden.

En annan utmaning är att det finns begränsningar för vilken datamängd Looker kan hantera utan att det blir “datakonfigureringsfel”. Eftersom jag gör mina egna datakällor med anpassade SQL-frågor blir det lätt många olika. För att hantera detta har jag delat upp mina projekt i olika dashboards, så projektet “X” har en egen dashboard med 4 olika datakällor. En separat datakälla bör heller inte innehålla mer än vad som är nödvändigt då prestandan kan bli märkbart sämre om datan innehåller många beräkningar eller dimensioner, vilket också blir en utmaning med komplexa joins-uträkningar. Därför har jag förbehandlat och filtrerat datan noga och alltid haft mitt syfte i bakhuvudet.

Lärdomar och resultat

Traditionella analyser ger en övergripande trendbild och visar ofta medelvärden eller totalsummor men kan dölja viktiga mönster. Kohort analyser fokuserar istället på dessa detaljerade beteendemönster inom specifika tidsbundna grupper. Därför ger kohort analyser värdefulla insikter för att kunna optimera nyckeltal som ARPU och LTV. Tyvärr är resultaten för detta projektet sekretessbelagda.

Kohorts är en analys som kräver noggrannhet men när SQL-frågorna väl är korrekta och sparade, är det enkelt att fortsätta arbeta med dem i framtiden. Det kan alltså vara värt att lägga ner tiden och arbetet för att sedan kunna använda det igen för olika syften (i detta fallet fick företaget det av mig).