Sales & marketing analyst: MQL recipes

Recipes

Monthly growth rate and two-month forecast
A/B test of a landing page
Seasonal indices and quarterly forecast
Bass diffusion curve of a new app
RFM segmentation of customers
Customer lifetime value with retention
Average basket after a product page redesign
Saturating media response and the optimal spend
Best send time of a newsletter
Media mix model through the origin

executed on MentDB

Monthly growth rate and two-month forecast

Revenue grew from 41.2 to 69.8 k EUR in 10 months. The script fits an exponential trend, turns its coefficient into a monthly growth rate and forecasts months 11 and 12.

Pipeline

01 · fitExponential trendstat fit→02 · readCoefficients from JSONjson select→03 · forecastEvaluate the modelmath eval

Script

admin

#1. Monthly revenue in k EUR over 10 months;
-> "[x]" "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]";
-> "[y]" "[41.2, 43.5, 46.9, 48.1, 52.4, 55.0, 58.9, 61.3, 66.0, 69.8]";
#2. Exponential trend y = a*exp b*x;
json load "rx_fit" (stat fit [x] [y] "exp");
-> "[b]" (json select "rx_fit" "/b");
#3. Monthly growth rate and forecast of months 11 to 12;
-> "[f]" (json select "rx_fit" "/expression");
concat "R2 = " (math round (json select "rx_fit" "/r2") 4) ", growth " (math round (math calc "(exp([b])-1)*100") 2) "% a month, forecast M11 " (math round (math eval [f] "{\"x\": 11}") 1) ", M12 " (math round (math eval [f] "{\"x\": 12}") 1) " k EUR.";

Result

mentdb

R2 = 0.9973, growth 6.03% a month, forecast M11 73.9, M12 78.3 k EUR.

Go further

Compare with a linear trend: same call with "linear" and compare the r2
Market saturation: the "logistic" model of stat fit gives the ceiling c

executed on MentDB

A/B test of a landing page

Version A converted 120 visitors out of 2400 and version B 156 out of 2350. The script tests the independence of version and conversion with a chi-square test and gives the rates, the uplift and the decision at the 5% level.

Pipeline

01 · testChi-square on the 2x2 tablestat chi2_independence→02 · readp-value from JSONjson select→03 · ratesConversion and upliftmath calc→04 · decideSignificant at 5%?if

Script

admin

#1. A/B test of a landing page: conversions and non-conversions;
-> "[table]" "[[120, 2280], [156, 2194]]";
#2. Chi-square test of independence;
json load "rx_ab" (stat chi2_independence [table]);
-> "[p]" (json select "rx_ab" "/p_value");
#3. Conversion rates and decision at 5%;
-> "[ra]" (math calc "120/2400*100");
-> "[rb]" (math calc "156/2350*100");
concat "A: " [ra] "%, B: " (math round [rb] 2) "%, uplift " (math round (math calc "([rb]-[ra])/[ra]*100") 1) "%, chi2 = " (math round (json select "rx_ab" "/chi2") 3) ", p = " (math round [p] 4) " -> " (if (< [p] 0.05) {"significant"} {"not significant"});

Result

mentdb

A: 5%, B: 6.64%, uplift 32.8%, chi2 = 5.823, p = 0.0158 -> significant

Go further

Small samples: stat fisher_exact on the same 2x2 table
Sample size needed: solve the power equation with num solve and stat dist_inv

executed on MentDB

Seasonal indices and quarterly forecast

Quarterly sales over three years show a strong Q3 peak. The script computes the seasonal index of a quarter (its mean over the mean of all quarters), fits a linear trend on the annual totals and forecasts year 4 quarter by quarter.

Pipeline

01 · indexQuarter mean / overall meanstat mean→02 · trendLinear trend of the yearsstat fit→03 · forecastYear 4 totalmath eval→04 · seasonApply the indexmath round

Script

admin

#1. Quarterly sales over 3 years;
-> "[q1]" "[182, 195, 210]";
-> "[q2]" "[240, 258, 271]";
-> "[q3]" "[310, 322, 349]";
-> "[q4]" "[205, 214, 236]";
#2. Seasonal index: mean of the quarter over mean of all quarters;
-> "[m]" (/ (+ (stat sum [q1]) (stat sum [q2]) (stat sum [q3]) (stat sum [q4])) 12);
-> "[i1]" (math round (/ (stat mean [q1]) [m]) 3);
-> "[i3]" (math round (/ (stat mean [q3]) [m]) 3);
#3. Linear trend of the annual totals, forecast of year 4;
json load "rx_tr" (stat fit "[1, 2, 3]" "[937, 989, 1066]" "linear");
-> "[y4]" (math eval (json select "rx_tr" "/expression") "{\"x\": 4}");
concat "Indices Q1 " [i1] ", Q3 " [i3] ". Year 4 forecast " (math round [y4] 0) ", Q1 " (math round (* (/ [y4] 4) [i1]) 0) ", Q3 " (math round (* (/ [y4] 4) [i3]) 0);

Result

mentdb

Indices Q1 0.785, Q3 1.311. Year 4 forecast 1126, Q1 221, Q3 369

Go further

Deseasonalised series: divide each quarter by its index before fitting the trend
Check the seasonality with stat anova on the four quarter series

executed on MentDB

Bass diffusion curve of a new app

Cumulative adopters of a new app follow the Bass model (innovation p, imitation q, market size m). The script fits the model, checks with the CAS that the curve saturates at m, and draws the measures with the fitted S curve.

Pipeline

01 · fitFit m, p and qstat fit_model→02 · limitSaturation levelcas limit→03 · drawPoints and S curveplot fit

Script

admin

#1. Cumulative adopters of a new app in thousands, month 1 to 12;
-> "[t]" "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]";
-> "[N]" "[3.1, 7.6, 14.2, 23.5, 36.0, 51.2, 67.4, 82.5, 95.1, 104.6, 111.0, 115.3]";
#2. Bass diffusion model: m market size, p innovation, q imitation;
json load "rx_bass" (stat fit_model "m*(1-exp(-(p+q)*t))/(1+q/p*exp(-(p+q)*t))" "t" [t] [N] "{\"m\": 120, \"p\": 0.01, \"q\": 0.5}");
#3. The CAS confirms the saturation level: with s = p+q t and r = q/p, the limit is m;
-> "[lim]" (cas limit "m*(1-exp(-s))/(1+r*exp(-s))" "s" "Infinity");
#4. Measures and fitted S curve;
plot fit [t] [N] (json select "rx_bass" /expression) "t"

Result

mentdb

The chart is shown in the Plot tab of the editor.

Go further

Read the parameters with json select "rx_bass" /parameters (about m = 123, p = 0.018, q = 0.49).
The peak of the adoptions is at t = ln(q/p)/(p+q).

executed on MentDB

RFM segmentation of customers

The CRM team scores each customer on Recency, Frequency and Monetary value over 12 months. The script computes the days since the last order, the quartiles of each measure, a 1 to 4 score per measure (recency reversed) and a segment: champion, at risk, promising or lost.

Pipeline

01 · recencyDays since last orderdate nb_day→02 · quartilesQ1, median, Q3stat quartiles→03 · scoreScore 1 to 4json select→04 · segmentChampion, at risk...if→05 · spendMedian and meanstat median

Script

admin

#1. Customers of an online shop: last order, number of orders and amount spent over 12 months;
json load "rx_cu" "[{\"id\": \"C01\", \"last\": \"2026-10-28\", \"f\": 14, \"m\": 1820}, {\"id\": \"C02\", \"last\": \"2026-04-02\", \"f\": 9, \"m\": 1150}, {\"id\": \"C03\", \"last\": \"2026-10-12\", \"f\": 2, \"m\": 95}, {\"id\": \"C04\", \"last\": \"2025-12-20\", \"f\": 1, \"m\": 40}, {\"id\": \"C05\", \"last\": \"2026-09-30\", \"f\": 6, \"m\": 610}, {\"id\": \"C06\", \"last\": \"2026-10-31\", \"f\": 11, \"m\": 2390}, {\"id\": \"C07\", \"last\": \"2026-06-15\", \"f\": 4, \"m\": 380}, {\"id\": \"C08\", \"last\": \"2026-08-03\", \"f\": 3, \"m\": 210}]";
json load "rx_v" "{\"r\": [], \"f\": [], \"m\": []}";
#2. Recency in days on 2026-11-01, then the quartiles of each measure;
for (-> "[i]" 0) (< [i] (json count "rx_cu" /)) (++ "[i]") {
	json iarray "rx_v" "/r" (date nb_day (json select "rx_cu" (concat "/[" [i] "]/last")) "2026-11-01") NUM;
	json iarray "rx_v" "/f" (json select "rx_cu" (concat "/[" [i] "]/f")) NUM;
	json iarray "rx_v" "/m" (json select "rx_cu" (concat "/[" [i] "]/m")) NUM;
};
json load "rx_q" (concat "{\"r\": " (stat quartiles (json select "rx_v" "/r")) ", \"f\": " (stat quartiles (json select "rx_v" "/f")) ", \"m\": " (stat quartiles (json select "rx_v" "/m")) "}");
#3. Score 1 to 4 per measure, recency is reversed, then the segment;
-> "[seg]" "";
for (-> "[i]" 0) (< [i] (json count "rx_cu" /)) (++ "[i]") {
	-> "[sc]" "";
	for (-> "[k]" 1) (<= [k] 3) (++ "[k]") {
		-> "[x]" (atom get "r,f,m" [k] ",");
		-> "[v]" (json select "rx_v" (concat "/" [x] "/[" [i] "]"));
		-> "[s]" 1;
		for (-> "[q]" 0) (< [q] 3) (++ "[q]") {if (> [v] (json select "rx_q" (concat "/" [x] "/[" [q] "]"))) {-> "[s]" (+ [s] 1);};};
		if (equal [x] "r") {-> "[s]" (- 5 [s]);};
		-> "[sc]" (concat [sc] [s]);
	};
	-> "[R]" (string left [sc] 1); -> "[F]" (string mid [sc] 1 2);
	if (and (>= [R] 3) (>= [F] 3)) {-> "[name]" "champion";} {if (>= [F] 3) {-> "[name]" "at risk";} {if (>= [R] 3) {-> "[name]" "promising";} {-> "[name]" "lost";};};};
	-> "[seg]" (concat [seg] (json select "rx_cu" (concat "/[" [i] "]/id")) "=" [sc] " " [name] ", ");
};
-> "[out]" (concat "quartiles " (string regexp_replace (json doc "rx_q") "\s" "") " | " [seg] "median spend " (stat median (json select "rx_v" "/m")) ", mean spend " (stat mean (json select "rx_v" "/m")));
json unload "rx_cu"; json unload "rx_v"; json unload "rx_q";
[out]

Result

mentdb

quartiles {"r":[16,61,157.5],"f":[2.75,5,9.5],"m":[181.25,495,1317.5]} | C01=444 champion, C02=133 at risk, C03=311 promising, C04=111 lost, C05=333 champion, C06=444 champion, C07=222 lost, C08=222 lost, median spend 495, mean spend 836.875

Go further

Send a win-back offer to the at risk customers: they ordered often but not lately
With more customers use stat percentile for quintiles, the usual RFM grid of 125 cells

executed on MentDB

Customer lifetime value with retention

The growth team values the 2025 cohort of a subscription box: average yearly margin per customer, 78 pct retention, 9 pct discount rate, 95 EUR acquisition cost. The script computes the infinite horizon CLV m.r/(1+d-r), the 5-year CLV as a discounted sum, the CLV / CAC ratio and the expected lifetime.

Pipeline

01 · marginAverage marginmath avg→02 · clvm.r / (1 + d - r)math calc→03 · horizonDiscounted summath power→04 · ratioCLV / CACmath round

Script

admin

#1. Yearly gross margin per customer of the 2025 cohort of a subscription box, retention 78 pct, discount rate 9 pct, acquisition cost 95 EUR;
-> "[m]" (math avg "[64.0, 81.5, 72.0, 58.5, 90.0, 69.0, 77.5]");
-> "[r]" 0.78; -> "[d]" 0.09; -> "[cac]" 95;
#2. Infinite horizon CLV = m . r / 1 + d - r, margins paid at the end of each year of retention;
-> "[clv]" (/ (* [m] [r]) (- (+ 1 [d]) [r]));
#3. Same on a 5-year horizon with the sum of m . r^t / 1 + d ^t, and the expected lifetime;
-> "[clv5]" 0;
for (-> "[t]" 1) (<= [t] 5) (++ "[t]") {-> "[clv5]" (+ [clv5] (/ (* [m] (math power [r] [t])) (math power (+ 1 [d]) [t])));};
concat "average margin " (math round [m] 2) " EUR, CLV " (math round [clv] 2) " EUR, 5-year CLV " (math round [clv5] 2) " EUR, CLV / CAC " (math round (/ [clv] [cac]) 2) ", expected lifetime " (math round (/ 1 (- 1 [r])) 2) " years"

Result

mentdb

average margin 73.21 EUR, CLV 184.22 EUR, 5-year CLV 149.65 EUR, CLV / CAC 1.94, expected lifetime 4.55 years

Go further

A CLV / CAC under 3 is weak: raise the retention or lower the acquisition cost
One point of retention adds about 8 EUR of CLV here: try r = 0.79

executed on MentDB

Average basket after a product page redesign

After a new product page, the e-commerce manager compares a sample of 16 orders with the historical mean basket of 42.50 EUR (standard deviation 9 EUR, known from years of orders). The script runs a z-test, gives the uplift and its 95 pct interval and a decision.

Pipeline

01 · testz and p-valuestat ztest→02 · meanMean basketstat mean→03 · margin1.96 sigma / sqrt nmath sqrt→04 · decisionKeep or notif

Script

admin

#1. Order baskets in EUR of a sample of 16 orders after the new product page, the historical mean is 42.50 EUR with sd 9 EUR;
-> "[bk]" "[48.9, 39.5, 51.2, 44.0, 56.3, 41.8, 47.5, 38.9, 52.6, 45.1, 49.7, 43.3, 55.0, 40.6, 46.8, 50.4]";
-> "[mu]" 42.50; -> "[sigma]" 9;
#2. z-test of the mean basket against the historical value;
json load "rx_z" (stat ztest [bk] [mu] [sigma]);
#3. Uplift, its 95 pct margin 1.96 sigma / sqrt n and the decision;
json load "rx_b" [bk];
-> "[mean]" (stat mean [bk]);
-> "[marg]" (/ (* 1.96 [sigma]) (math sqrt (json count "rx_b" "/")));
-> "[out]" (concat "mean basket " (math round [mean] 2) " EUR, uplift " (math round (* (/ (- [mean] [mu]) [mu]) 100) 1) " pct, 95 pct interval " (math round (- [mean] [marg]) 2) " to " (math round (+ [mean] [marg]) 2) " | z " (math round (json select "rx_z" "/z") 3) ", p " (math round (json select "rx_z" "/p_value") 4) (if (< (json select "rx_z" "/p_value") 0.05) {" -> keep the new page";} {" -> not proven";}));
json unload "rx_z"; json unload "rx_b";
[out]

Result

mentdb

mean basket 46.98 EUR, uplift 10.5 pct, 95 pct interval 42.57 to 51.39 | z 1.989, p 0.0467 -> keep the new page

Go further

p = 0.047 is just under 5 pct: collect a second sample before the general roll-out
For a real A/B test with two samples use stat ttest_2 or stat mann_whitney

executed on MentDB

Saturating media response and the optimal spend

The media planner models TV sales with a saturating curve sales = a.tanh(x/k). The script computes the sales and the marginal return at the current spend (derivative a/k / cosh^2(x/k)), then the spend where one more euro brings back only one euro, with the inverse hyperbolic cosine.

Pipeline

01 · salesa.tanh(x/k)math tanh→02 · marginalDerivativemath cosh→03 · optimumx = k.acosh(sqrt(a/k))math acosh

Script

admin

#1. Saturating response of TV sales to the weekly spend x in kEUR: sales = a . tanh of x / k, a = 120 kEUR max, k = 40 kEUR;
-> "[a]" 120; -> "[k]" 40; -> "[x]" 30;
#2. Sales at the current 30 kEUR spend and the marginal return a/k . 1 / cosh2 of x/k;
-> "[s]" (* [a] (math tanh (/ [x] [k])));
-> "[mroi]" (/ (/ [a] [k]) (math power (math cosh (/ [x] [k])) 2));
#3. Spend where the marginal return falls to 1 EUR per EUR: cosh2 = a/k, so x = k . acosh of sqrt a/k;
-> "[xopt]" (* [k] (math acosh (math sqrt (/ [a] [k]))));
concat "sales at 30 kEUR " (math round [s] 2) " kEUR, marginal return " (math round [mroi] 3) " EUR per EUR, optimal spend " (math round [xopt] 2) " kEUR giving " (math round (* [a] (math tanh (/ [xopt] [k]))) 2) " kEUR of sales"

Result

mentdb

sales at 30 kEUR 76.22 kEUR, marginal return 1.79 EUR per EUR, optimal spend 45.85 kEUR giving 97.98 kEUR of sales

Go further

Draw the curve 120*tanh(x/40) and the break-even line y = x with plot function from 0 to 150 kEUR
Fit a and k on past weeks with stat fit_model and the model a*tanh(x/k)

executed on MentDB

Best send time of a newsletter

The CRM manager looks at the open timestamps of the last newsletter to plan the next one. The script extracts the hour and the weekday of each open, finds the peak hour with the mode, its share in the frequency table and the best weekday with its name.

Pipeline

01 · hourHour of each opendate hour→02 · weekdayDay of the weekdate day_of_week→03 · peakMost frequent hourstat mode→04 · shareShare of the peakstat frequency→05 · nameDay namedate dayname

Script

admin

#1. Open timestamps of the last newsletter, local time;
json load "rx_op" "[\"2026-10-20 07:42:10\", \"2026-10-20 12:15:33\", \"2026-10-20 12:48:02\", \"2026-10-20 18:05:47\", \"2026-10-20 12:31:20\", \"2026-10-21 08:12:55\", \"2026-10-21 12:05:09\", \"2026-10-21 21:40:18\", \"2026-10-20 18:22:41\", \"2026-10-22 12:57:30\", \"2026-10-20 07:58:03\", \"2026-10-21 18:44:26\", \"2026-10-22 12:20:14\", \"2026-10-20 20:10:37\", \"2026-10-21 12:39:51\"]";
json load "rx_h" "[]"; json load "rx_d" "[]";
#2. Hour and weekday of each open;
json parse_array "rx_op" "/" "[ts]" {
	json iarray "rx_h" "/" (date hour (date time [ts])) INT;
	json iarray "rx_d" "/" (date day_of_week (string left [ts] 10)) INT;
};
#3. Peak hour with the mode, its share with the frequency table, best weekday;
-> "[peak]" (string regexp_replace (stat mode (json doc "rx_h")) "[^0-9]" "");
-> "[bd]" (string regexp_replace (stat mode (json doc "rx_d")) "[^0-9]" "");
json load "rx_f" (stat frequency (json doc "rx_h"));
-> "[share]" 0;
-> "[dname]" "";
json parse_array "rx_op" "/" "[ts]" {if (== (date day_of_week (string left [ts] 10)) [bd]) {-> "[dname]" (date dayname (string left [ts] 10) "en");};};
json parse_array "rx_f" "/" "[row]" {json load "rx_r" [row]; if (== (json select "rx_r" "/value") [peak]) {-> "[share]" (json select "rx_r" "/frequency");};};
-> "[out]" (concat "opens by hour " (string regexp_replace (json doc "rx_h") "\s" "") " | peak hour " [peak] " with " (math round (* [share] 100) 1) " pct of the opens | best weekday " [bd] " = " [dname] " | schedule the next send at 11:45");
json unload "rx_op"; json unload "rx_h"; json unload "rx_d"; json unload "rx_f"; json unload "rx_r";
[out]

Result

mentdb

opens by hour [7,12,12,18,12,8,12,21,18,12,7,18,12,20,12] | peak hour 12 with 46.7 pct of the opens | best weekday 2 = Tuesday | schedule the next send at 11:45

Go further

Send 15 minutes before the peak so the mail is on top of the inbox at lunch time
Repeat over several campaigns: one newsletter is a small sample

executed on MentDB

Media mix model through the origin

The marketing analyst estimates the incremental sales brought by 1 kEUR on search, social and radio from 8 weeks of data. With no spend there is no incremental sale, so the multiple regression has no intercept. The script fits it, reads the return of each channel and the R2, and forecasts the next media plan.

Pipeline

01 · loadSpends and salespa rm load_from_json→02 · originNo interceptpa rm set_no_intercept→03 · roiCoefficientspa rm estimate_regression_parameters→04 · fitR2pa rm calculate_r_squared→05 · planForecastpa rm predict

Script

admin

#1. Weekly incremental sales in kEUR and the spends in kEUR on search, social and radio of 8 weeks;
-> "[X]" "[[4.0, 2.0, 0.0], [5.5, 1.0, 3.0], [3.0, 3.5, 2.0], [6.0, 2.5, 0.0], [2.5, 0.5, 4.0], [4.5, 3.0, 1.5], [5.0, 1.5, 2.5], [3.5, 2.0, 3.5]]";
-> "[y]" "[21.3, 27.1, 22.8, 27.9, 17.6, 26.0, 26.4, 23.9]";
#2. No spend means no incremental sales: regression through the origin;
pa rm load_from_json "rx_mmm" [X] [y];
pa rm set_no_intercept "rx_mmm" true;
json load "rx_b" (pa rm estimate_regression_parameters "rx_mmm");
#3. Return of 1 kEUR on each channel, fit quality and the forecast of the next media plan;
-> "[out]" (concat "ROI search " (math round (json select "rx_b" "/[0]") 3) ", social " (math round (json select "rx_b" "/[1]") 3) ", radio " (math round (json select "rx_b" "/[2]") 3) " | R2 " (math round (pa rm calculate_r_squared "rx_mmm") 4) " | plan 5 / 3 / 2 kEUR gives " (math round (pa rm predict "rx_mmm" "[5, 3, 2]") 2) " kEUR of sales");
pa rm close "rx_mmm"; json unload "rx_b";
[out]

Result

mentdb

ROI search 3.687, social 2.478, radio 1.657 | R2 0.9992 | plan 5 / 3 / 2 kEUR gives 29.18 kEUR of sales

Go further

Without intercept the R2 is computed around 0, not around the mean: it is always high, compare models with the residuals
Add the adstock (carry-over of past weeks) to the spends before the regression