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

    avg_margin

    Required: Yes
  • retention_rate

    Required: Yes
  • discount_rate

    Required: Yes
  • CAC_PAYBACK

    cac

    Required: Yes
  • arpu

    Required: Yes
  • gross_margin

    Required: Yes
  • LTV_CAC_RATIO

    clv

    Required: Yes
  • cac

    Required: Yes
  • BUNDLE_EFFECTIVENESS_SCORE

    lift

    Required: Yes
  • confidence

    Required: Yes
  • blended_margin_impact

    Required: Yes
  • cannibalization_penalty

    Required: Yes
  • inventory_risk_penalty

    Required: Yes
  • LFRS

    liability_ratio

    Required: Yes
  • redemption_volatility

    Required: Yes
  • breakage_rate

    Required: Yes
  • margin_impact

    Required: Yes
  • w1

    Required: Yes
  • w2

    Required: Yes
  • w3

    Required: Yes
  • w4

    Required: Yes

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.

FunctionReturnsBehaviourForm
CLVdecimalCustomer lifetime value: margin times retention, divided by 1 plus the discount rate less retentionCLV(200, 0.8, 0.1)
CAC_PAYBACKdecimalThe periods of gross margin needed to repay the acquisition costCAC_PAYBACK(500, 100, 0.7)
LTV_CAC_RATIOdecimalLifetime value divided by acquisition costLTV_CAC_RATIO(533.33, 150)
BUNDLE_EFFECTIVENESS_SCOREdecimalLift times confidence times margin impact, less both penaltiesBUNDLE_EFFECTIVENESS_SCORE(0.2, 0.9, 0.35, 0.02, 0.01)
LFRSdecimalThe loyalty financial risk score: the weighted sum of liability, volatility, one less breakage and margin impactLFRS(0.08, 0.15, 0.2, 0.05, 0.4, 0.2, 0.3, 0.1)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-5005CLV, CAC_PAYBACK or LTV_CAC_RATIO was given a figure that makes the answer undefined, such as a zero acquisition costPass values inside the range listed under Limitations
HTQL-2001BUNDLE_EFFECTIVENESS_SCORE or LFRS was given an argument out of range, such as a confidence above 1Pass values inside the range listed under Limitations
HTQL-5004 Not enough paramsAn argument is missing. The message lists the names the function expectsGive every argument it lists
Limitations
  • CLV needs 1 + discount_rate - retention_rate above 0. CAC_PAYBACK needs arpu times gross_margin above 0, and LTV_CAC_RATIO needs cac above 0.
  • The result of CAC_PAYBACK is in the periods of arpu: monthly revenue gives months.
  • BUNDLE_EFFECTIVENESS_SCORE needs lift above 0 and confidence above 0 up to 1. LFRS needs breakage_rate from 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 in IF and used in ADD, SUBTRACT, MULTIPLY and DIVIDE.
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.