Customer economics and scoring functions
Judge whether winning a customer pays, and score a bundle offer or a loyalty programme from figures you already have.
Syntax
Write the function's name and its figures. Money is in major units and rates are fractions, so 80 percent retention is 0.8.
CLV(avg_margin, retention_rate, discount_rate)
CAC_PAYBACK(cac, arpu, gross_margin)
LTV_CAC_RATIO(clv, cac)
BUNDLE_EFFECTIVENESS_SCORE(
lift,
confidence,
blended_margin_impact,
cannibalization_penalty,
inventory_risk_penalty
)
LFRS(
liability_ratio,
redemption_volatility,
breakage_rate,
margin_impact,
w1,
w2,
w3,
w4
)Parameters and arguments
Arguments are written in brackets, separated by commas. An argument in square brackets is optional.
CLV
Required: Yesavg_margin
Required: Yesretention_rate
Required: Yesdiscount_rateCAC_PAYBACK
Required: Yescac
Required: Yesarpu
Required: Yesgross_marginLTV_CAC_RATIO
Required: Yesclv
Required: YescacBUNDLE_EFFECTIVENESS_SCORE
Required: Yeslift
Required: Yesconfidence
Required: Yesblended_margin_impact
Required: Yescannibalization_penalty
Required: Yesinventory_risk_penaltyLFRS
Required: Yesliability_ratio
Required: Yesredemption_volatility
Required: Yesbreakage_rate
Required: Yesmargin_impact
Required: Yesw1
Required: Yesw2
Required: Yesw3
Required: Yesw4
Examples
A customer who earns 200 of margin each year, with 80 per cent kept each year and a 10 per cent discount rate, costs 150 to win; separately a 500 acquisition cost against 100 of monthly revenue at a 70 per cent margin.
Return type and behaviour
Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
CLV | decimal | Customer lifetime value: margin times retention, divided by 1 plus the discount rate less retention | CLV(200, 0.8, 0.1) |
CAC_PAYBACK | decimal | The periods of gross margin needed to repay the acquisition cost | CAC_PAYBACK(500, 100, 0.7) |
LTV_CAC_RATIO | decimal | Lifetime value divided by acquisition cost | LTV_CAC_RATIO(533.33, 150) |
BUNDLE_EFFECTIVENESS_SCORE | decimal | Lift times confidence times margin impact, less both penalties | BUNDLE_EFFECTIVENESS_SCORE(0.2, 0.9, 0.35, 0.02, 0.01) |
LFRS | decimal | The loyalty financial risk score: the weighted sum of liability, volatility, one less breakage and margin impact | LFRS(0.08, 0.15, 0.2, 0.05, 0.4, 0.2, 0.3, 0.1) |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-5005 | CLV, CAC_PAYBACK or LTV_CAC_RATIO was given a figure that makes the answer undefined, such as a zero acquisition cost | Pass values inside the range listed under Limitations |
HTQL-2001 | BUNDLE_EFFECTIVENESS_SCORE or LFRS was given an argument out of range, such as a confidence above 1 | Pass values inside the range listed under Limitations |
HTQL-5004 Not enough params | An argument is missing. The message lists the names the function expects | Give every argument it lists |
Limitations
CLVneeds1 + discount_rate - retention_rateabove 0.CAC_PAYBACKneedsarputimesgross_marginabove 0, andLTV_CAC_RATIOneedscacabove 0.- The result of
CAC_PAYBACKis in the periods ofarpu: monthly revenue gives months. BUNDLE_EFFECTIVENESS_SCOREneedsliftabove 0 andconfidenceabove 0 up to 1.LFRSneedsbreakage_ratefrom 0 to 1 and every other argument 0 or more.- A finance function's result cannot be an argument of another finance function, so type the figure in or name it with
LET. The result can be compared inIFand used inADD,SUBTRACT,MULTIPLYandDIVIDE.
Permissions and security
These functions read nothing from your tables and write nothing.
When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.
Best practices
- Use margin, not revenue, as the customer's yearly value in
CLV. - Keep the same period for the margin and the retention rate.