Customer Lifetime Value (CLV) voor abonnementsgerichte bedrijven

Customer Lifetime Value is de berekening van de winst die een klant oplevert in zijn of haar tijd als klant. Door het CLV te berekenen kan een waarde worden vastgesteld dat mag worden uitgegeven om een nieuwe klant te werven. Oftewel, onmisbaar voor veel bedrijven om het marketingbudget te bepalen. Daarbij kun je de CLV voor je uitgeschreven klanten, huidige klanten, maar ook voor je toekomstige klanten berekenen/voorspellen. In dit eerste blog behandel ik de berekening van de CLV voor de uitgeschreven klanten. In het volgende blog zal ik de voorspelling van de CLV voor de huidige klanten behandelen. In dit blog beginnen we dus met de makkelijkste CLV, je berekent namelijk de werkelijk behaalde winst voor uitgeschreven klanten in de tijd dat ze klant zijn geweest. Voor het ene bedrijf is dit makkelijker en beter te berekenen dan voor het andere. Een belangrijk punt in deze berekening is de ‘lifetime’ van een klant, want word je bijvoorbeeld nog steeds als klant gezien wanneer jij al vijf jaar niks meer hebt gekocht bij een bepaalde winkel? Anders gezegd, wordt deze klant als ingeschreven of uitgeschreven gezien? Voor bedrijven met producten en/of diensten gericht op abonnementen is dit veel beter te berekenen.

In dit blog laten we zien hoe je een simpele CLV-berekening kan implementeren via Klipfolio, zodat we elke dag (of week/maand/jaar) kunnen checken hoe dit voor de meest recent uitgeschreven klanten eruitziet. Een simpele Customer Lifetime Value berekening ziet er als volgt uit:

Gemiddelde lifetime (in dagen/weken/maanden/jaren)
×
Gemiddelde winst¹ per klant (per dag/week/maand/jaar)
-------------------
Customer Lifetime Value

¹ In dit blog gaan we ervanuit dat de kosten per klant €0,00 zijn. In de werkelijkheid is dit natuurlijk geen reëel beeld. Wanneer de kosten hoger zijn dan €0,00, kunnen deze van de omzet (DEEL 2) per klant afgehaald worden om de winst per klant te berekenen.

Deel 1 – Gemiddelde Lifetime

Gemiddelde lifetime (in dagen/weken/maanden/jaren)
×
Gemiddelde winst¹ per klant (per dag/week/maand/jaar)
-------------------
Customer Lifetime Value

Zoals ik eerder al aangaf is de lifetime (het aantal dagen/weken/maanden/jaren dat een klant een klant blijft) van de klant beter te berekenen voor de klanten van abonnementsgerichte bedrijven. Bij deze klanten is er namelijk een inschrijvingsdatum en uitschrijvingsdatum. In dit voorbeeld gaan we ervanuit dat het bedrijf al een geruime tijd bestaat en al een aardig klantenbestand heeft opgebouwd, waardoor ze genoeg data binnenhaalt met inschrijvings- en uitschrijvingsgegevens. Hierdoor kunnen ze dagelijks de lifetime van een aantal uitgeschreven klanten berekenen. Met deze gegevens kunnen we een dynamische lifetime berekening maken die gebaseerd is op werkelijke inschrijvings- en uitschrijvingsdata. Ten eerste moeten we de data zo aggregeren dat de brondata op de volgende manier eruit komt te zien (zie Fig. 1: brondata), met in kolom A de datum, kolom B het aantal mensen die zich die dag hebben uitgeschreven en in kolom C de som van de abonnementsduur van deze groep (ex) klanten.

Brondata
Fig.1 – Brondata

Wanneer je dagelijks niet veel uitschrijvingen hebt, wat natuurlijk goed is, is het misschien beter om een groter tijdspan te gebruiken, bijvoorbeeld het aantal uitschrijvingen per week of per maand en de daarbij behorende ‘lifetime’. In de afbeelding Klip 1 zie je in het blauwe vlak de gemiddelde abonnementsduur in dagen van de uitgeschreven klanten van de kolom links daarvan. Je kunt zien dat de gemiddelde duur erg schommelt, van 260,3 dagen op 28 september 2018 tot 331,7 dagen op 24 september 2018.

Klip 1
Fig.2 – Klip 1

Enkele uitschrijvingen per dag kunnen namelijk de lifetime erg doen schommelen. Om dit constanter te maken hebben we per dag het aantal uitschrijvingen van de laatste 30 dagen gepakt en van deze uitschrijvingen de gemiddelde lifetime per klant berekend (op 1 oktober pakken we de uitgeschreven klanten van 1 oktober t/m 2 september, op 30 september de uitgeschreven klanten van 30 september t/m 1 september, enzovoorts). Wanneer dit nog steeds grote schommelingen voortbrengt kun je voor een nog langere periode kiezen, onthoud wel: hoe langer de periode hoe minder dynamisch de lifetime zal worden. De volgende query in klipfolio berekent de kolom # Uitgeschreven (Laatste 30 dagen). Dit is dus de som van het aantal uitgeschreven klanten in de laatste 30 dagen.

MAPFLAT( ARRAY(0,CUMULATIVE(REPEAT(1,(COUNTALL(&Column: Datum CLV)-1)))), 
"date_index", 
SUMIF( BETWEEN(DATE(@A:A;,"dd-MMM-yyyy"), 
DATE_ADD(DATE_ADD(DATE(SLICE(&Column: Datum CLV, $date_index,($date_index+1)),"dd-MMM-yyyy"),"4",-4),"5",-1), 
DATE(SLICE(&Column: Datum CLV,$date_index,($date_index+1)),"dd-MMM-yyyy")), @B:B;))

De functie MAPFLAT in klipfolio kun je gebruiken om een set van waarden terug te geven voor elke waarde gegeven in de eerste parameter (datum). MAPFLAT bevat de volgende indeling: MAPFLAT(values, variable name, expression)

Het eerste gedeelte (values):

ARRAY(0,CUMULATIVE(REPEAT(1,(COUNTALL(&Column: Datum CLV)-1))))

Dit gedeelte zorgt ervoor dat de rijen (dagen), die zichtbaar zijn in de klip, worden genummerd van 0 op de eerste rij, 1 op de tweede rij, 2 op de derde rij, enz. Deze waarden worden toegewezen aan de variabele (het tweede gedeelte)

Het tweede gedeelte (variable name):

“date_index”

Dit is de variabele waar de waarden van de dagen (0,1,2,3,4 enz.) aan zijn toegewezen.

Het derde gedeelte (expression):

SUMIF( BETWEEN(DATE(@A:A;,"dd-MMM-yyyy"), 
DATE_ADD(DATE_ADD(DATE(SLICE(&Column: Datum CLV, $date_index,($date_index+1)),"dd-MMM-yyyy"),"4",-4),"5",-1), 
DATE(SLICE(&Column: Datum CLV,$date_index,($date_index+1)),"dd-MMM-yyyy")), @B:B;))

Hier wordt aangegeven dat de waardes van kolom B:B moeten worden gesommeerd wanneer de bijbehorende datum binnen de laatste 30 dagen valt van de aangewezen datum in kolom A:A.

Met deze query berekenen we de met blauw aangegeven kolom ‘# Uitgeschreven (Laatste 30 dagen)’.

Klip 2
Fig.3 – Klip 2

Wanneer we deze zelfde MAPFLAT-query gebruiken voor ‘AbonnementsduurCumulatief’ (zie brondata 2 kolom C), dan kunnen we de totale abonnementsduur van alle uitgeschreven (ex) klanten in de afgelopen 30 dagen bij elkaar optellen.

Brondata 2
Fig.4 – Brondata 2

Het enige wat hiervoor veranderd dient te worden is in plaats van kolom B:B, kolom C:C te gebruiken:

MAPFLAT( ARRAY(0,CUMULATIVE(REPEAT(1,(COUNTALL(&Column: Datum CLV)-1)))), 
"date_index", 
SUMIF( BETWEEN(DATE(@A:A;,"dd-MMM-yyyy"), 
DATE_ADD(DATE_ADD(DATE(SLICE(&Column: Datum CLV, $date_index,($date_index+1)),"dd-MMM-yyyy"),"4",-4),"5",-1), 
DATE(SLICE(&Column: Datum CLV,$date_index,($date_index+1)),"dd-MMM-yyyy")), @C:C;))

Wanneer som van abonnementsduren (2e MAPFLAT-query) delen door de som het aantal uitgeschreven (ex) klanten (1e MAPFLAT-query) krijgen we de gemiddelde abonnementsduur van een uitgeschreven (ex) klant in de afgelopen dertig dagen (Klip 2: ‘Gem. Duur Abo (Laatste 30 dagen)’). De volgende query berekent dit:

MAPFLAT( ARRAY(0,CUMULATIVE(REPEAT(1,(COUNTALL(&Column: Datum CLV)-1)))), 
"date_index", 
SUMIF( BETWEEN(DATE(@A:A;,"dd-MMM-yyyy"), 
DATE_ADD(DATE_ADD(DATE(SLICE(&Column: Datum CLV, $date_index,($date_index+1)),"dd-MMM-yyyy"),"4",-4),"5",-1), 
DATE(SLICE(&Column: Datum CLV,$date_index,($date_index+1)),"dd-MMM-yyyy")), @C:C;))
/  MAPFLAT( ARRAY(0,CUMULATIVE(REPEAT(1,(COUNTALL(&Column: Datum CLV)-1)))), 
"date_index", 
SUMIF( BETWEEN(DATE(@A:A;,"dd-MMM-yyyy"), 
DATE_ADD(DATE_ADD(DATE(SLICE(&Column: Datum CLV, $date_index,($date_index+1)),"dd-MMM-yyyy"),"4",-4),"5",-1), 
DATE(SLICE(&Column: Datum CLV,$date_index,($date_index+1)),"dd-MMM-yyyy")), @B:B;))

Deel 2 – Gemiddelde winst

Nu het gedeelte van de gemiddelde uitgaven per dag/week/maand/jaar, dit is het tweede gedeelte (blauw) van de CLV-berekening:

Gemiddelde lifetime (in dagen/weken/maanden/jaren)
×
Gemiddelde winst¹ per klant (per dag/week/maand/jaar)
-------------------
Customer Lifetime Value

Om de berekening goed te kunnen doen moeten we dezelfde aggregatie gebruiken als in het lifetime gedeelte, we vermenigvuldigen namelijk de twee delen met elkaar. Om de gemiddelde uitgaven per klant per dag in dit geval te berekenen, delen we logischerwijs de totale uitgaven per klant met het aantal dagen dat zij klant zijn geweest. Omdat we in het lifetime gedeelte de gemiddelde lifetime van de uitgeschreven klanten van de laatste 30 dagen hebben berekend, berekenen we in dit gedeelte ook de gemiddelde uitgaven per dag voor de uitgeschreven klanten van de laatste 30 dagen. We gebruiken daarvoor dezelfde methode als in het lifetime gedeelte (MAPFLAT).

Brondata 3
Fig.5 – Brondata 3

In brondata 3 zien we in kolom A de datum, in kolom B het aantal uitgeschreven klanten op die dag en in kolom C de totale gemiddelde dagelijkse omzet van deze uitgeschreven klanten, toegeschreven aan de dag dat ze zijn uitgeschreven.

Als we van de uitgeschreven klanten in de laatste 30 dagen de totale omzet berekenen en dit vertalen naar een gemiddelde omzet per klant per dag, hebben we een goed beeld van de gemiddelde uitgaven van deze groep. Ten eerste zullen we een som van het aantal uitgeschreven klanten in de laatste 30 dagen moeten maken zoals te zien is in klip 3:

Klip 3
Fig.6 – Klip 3

Met de volgende MAPFLAT-query berekenen we deze met blauw aangegeven kolom:

MAPFLAT( ARRAY(0,CUMULATIVE(REPEAT(1,(COUNTALL(&Column: Datum CLV)-1)))), 
"date_index", 
SUMIF( BETWEEN(DATE(@A:A;,"dd-MMM-yyyy"), 
DATE_ADD(DATE_ADD(DATE(SLICE(&Column: Datum CLV, $date_index,($date_index+1)),"dd-MMM-yyyy"),"4",-4),"5",-1), 
DATE(SLICE(&Column: Datum CLV,$date_index,($date_index+1)),"dd-MMM-yyyy")), @B:B;))

In deze query wordt met B:B verwezen naar de kolom B:B ‘AantalUitgeschreven in brondata 3. Voor de kolom ‘Dagelijkse Omzet (Laatste 30 dagen)’ in de klip gebruiken we dezelfde MAPFLAT-query, alleen gebruiken we i.p.v. B:B kolom C:C ‘Dagelijkse_Omzet’ uit brondata 3.

MAPFLAT( ARRAY(0,CUMULATIVE(REPEAT(1,(COUNTALL(&Column: Datum CLV)-1)))), 
"date_index", 
SUMIF( BETWEEN(DATE(@A:A;,"dd-MMM-yyyy"), 
DATE_ADD(DATE_ADD(DATE(SLICE(&Column: Datum CLV, $date_index,($date_index+1)),"dd-MMM-yyyy"),"4",-4),"5",-1), 
DATE(SLICE(&Column: Datum CLV,$date_index,($date_index+1)),"dd-MMM-yyyy")), @C:C;))

Om de waarden van ‘Dagelijkse Gem. Omzet (Laatste 30 dagen)’ in klip 3 te krijgen moeten we de MAPFLAT-query van ‘Dagelijkse Omzet (Laatste 30 dagen)’ delen door de MAPFLAT-query van ‘Uitgeschreven (Laatste 30 dagen)’.

Wanneer we nu deze gemiddelde uitgaven per klant per dag (Dagelijkse Gem. Omzet (Laatste 30 dagen) vermenigvuldigen met de gemiddelde levensduur van de uitgeschreven klant, krijgen we de CLV van de klanten die zich in de laatste 30 dagen hebben uitgeschreven:

Resultaat uit CLV berekening
Fig.7 – Resultaat uit CLV berekening

Jorke

   

Comments(0)

    Leave a Comment