Leads, campaigns, cross-sell/upsell and market basket analysis

Lead generation, behaviour and conversion

Worked example — conversion by source (telecom):

SELECT source, COUNT(*) AS leads,
       SUM(status = 'converted') AS converted,
       ROUND(100.0 * AVG(status = 'converted'), 1) AS conversion_pct,
       ROUND(AVG(CASE WHEN status = 'converted' THEN julianday(converted_at) - julianday(created_at) END), 1) AS avg_days
FROM leads GROUP BY source ORDER BY conversion_pct DESC;

Interpretation. Referral and partner-shop leads convert best; social ads bring volume but convert poorly. Decision: don't cut social ads on rate alone — compare cost per conversion and the value of resulting customers (do social-ad customers churn more?).

Lead behaviour and filtering

Lead behaviour = how leads move through statuses over time (new → contacted → qualified → converted/lost). A lead-filtering rule (e.g. contact only leads with score ≥ 40) trades sales effort against lost conversions:

SELECT CASE WHEN lead_score >= 40 THEN 'score >= 40' ELSE 'score < 40' END AS band,
       COUNT(*) AS leads, SUM(status = 'converted') AS conversions,
       ROUND(100.0 * AVG(status = 'converted'), 1) AS conv_pct
FROM leads GROUP BY band;

Decision logic. The low band holds about 17% of leads but only about 10% of conversions: filtering it saves roughly a sixth of contact effort at the cost of about 320 conversions over two years — worth it only if capacity is the constraint and the freed time converts better elsewhere.

Sales vs leads

Relate monthly leads to conversions (with a lag, since conversion takes days). A rising lead count with flat sales signals falling lead quality or sales capacity limits. (The founder-specified "Custar method" for sales vs leads is awaiting definition and is not taught here.)

Campaign analysis

Baselines (from weakest to strongest): before/after; same period last year × growth; matched control stores/regions (DiD); randomised holdout.

Worked example — Black Friday 2025 daily orders vs the two weeks before:

SELECT CASE WHEN order_date BETWEEN '2025-11-21' AND '2025-12-01' THEN 'Black Friday 2025 (11 days)'
            ELSE 'Baseline: 2025-11-07..2025-11-20 (14 days)' END AS period,
       ROUND(1.0 * COUNT(*) / (CASE WHEN order_date BETWEEN '2025-11-21' AND '2025-12-01' THEN 11 ELSE 14 END), 1) AS orders_per_day,
       ROUND(AVG(discount_pct) * 100, 1) AS avg_discount_pct
FROM orders
WHERE status = 'completed' AND order_date BETWEEN '2025-11-07' AND '2025-12-01'
GROUP BY period;

Interpretation. Orders per day rise sharply during the campaign — but at a much higher discount. The campaign "worked" only if the extra orders' margin exceeded the discount given to customers who would have bought anyway plus the 80,000 budget. Also check the weeks after for a dip (pull-forward). Common mistakes. Counting all campaign-period sales as incremental; ignoring discount cost; comparing with a non-seasonal baseline. (The founder's "Campaign Analysis 40%" rule is awaiting definition.)

Attribution fundamentals. When customers touch several channels, rules assign credit: last-click (all to last touch), first-click, linear, time-decay, position-based, or data-driven models. All rule-based attribution describes paths, not causal effect; incrementality tests answer "what did the channel cause?".

Cross-sell and upsell

• Cross-sell: selling an additional different product (laptop → bag). Metric: categories per customer, category penetration.
• Upsell: selling a higher-value version (Basic → Pro). Metric: share of upgrades, revenue per customer from upgrades.

WITH cc AS (SELECT o.customer_id, COUNT(DISTINCT p.category) AS n_cat
            FROM orders o JOIN order_items oi ON oi.order_id = o.order_id JOIN products p ON p.product_id = oi.product_id
            WHERE o.status = 'completed' GROUP BY o.customer_id)
SELECT n_cat AS categories_bought, COUNT(*) AS customers FROM cc GROUP BY n_cat ORDER BY n_cat;

Market basket analysis

Worked example by hand. 1,000 baskets; coffee in 200, chocolate in 100, both in 40. Support(coffee) = 0.20; Support(chocolate) = 0.10; Support(both) = 0.04. Confidence(coffee → chocolate) = 0.04 ÷ 0.20 = 0.20. Lift = 0.20 ÷ 0.10 = 2.0 — customers buying coffee are twice as likely as average to buy chocolate. Confidence(chocolate → coffee) = 0.04 ÷ 0.10 = 0.40 — confidence is directional; lift is symmetric.

Decisions. Bundles, shelf adjacency, "frequently bought together" recommendations — then test them (lift is an association, not proof that a bundle will add sales). Common mistakes. Ranking by confidence alone (popular items get high confidence with everything); ignoring minimum support (rare pairs give noisy lift).

Practice

Summary

• Measure lead conversion on cohorts; judge sources on cost and value per conversion, not rate alone.
• Lead-filter rules trade effort against lost conversions — quantify both.
• Campaign value = incremental margin vs a credible baseline minus cost; watch pull-forward.
• Support, confidence (directional) and lift (vs chance) quantify basket associations; test before acting.