Trial Activation Challenge — Splendor Analytics

Author: Henry Dibie
Submitted: March 2025

---

Problem Statement

Splendor Analytics runs a 30-day free trial for new organisations on its workforce management platform. Roughly 1 in 5 trialists converts to a paying customer, but the product team cannot identify who is on track to convert, when to intervene, or which features drive the decision.

This submission defines what "Trial Activation" means for the product, builds the data infrastructure to track it, and runs rigorous analysis to understand whether current trialists are reaching it.

---

Repository Structure

trial-activation-challenge/
├── assets/
│   └── screenshots/                      # Dashboard screenshots
├── dashboard/
│   └── index.html                        # Interactive analytics dashboard (open in browser)
├── data/
│   ├── README.md                         # Dataset schema and data quality notes
│   └── DA task.csv                       # Raw behavioural events (gitignored)
├── notebooks/
│   ├── 01_cleaning_and_eda.py            # Task 1a: Cleaning, EDA, goal definition
│   ├── 02_driver_analysis.py             # Task 1b: Conversion driver analysis
│   ├── 03_descriptive_analytics.py       # Task 3: Product metrics & descriptive analytics
│   ├── 01_eda_output.png                 # EDA visualisations
│   ├── 02_driver_analysis_output.png     # Driver analysis visualisations
│   └── 03_descriptive_output.png         # Descriptive analytics visualisations
├── sql/
│   ├── staging/
│   │   ├── stg_trial_events.sql          # Staging: dedup, cast, enrich raw events
│   │   └── schema.yml                    # dbt source + staging model tests
│   └── marts/
│       ├── fct_trial_goals.sql           # Mart: goal completion per organisation
│       ├── fct_trial_activation.sql      # Mart: activation flag per organisation
│       └── schema.yml                    # dbt mart model tests
├── ARCHITECTURE.md                       # Layer decisions, analytical tradeoffs, limitations
├── DEPLOYMENT.md                         # GitHub Pages + dbt/Snowflake deployment guide
├── FINDINGS.md                           # Plain-English summary for product team
├── dbt_project.yml                       # dbt project configuration
├── run_all.py                            # Runs all three scripts end-to-end
├── .gitignore
├── README.md
└── requirements.txt

---

Dataset

• 170,526 raw events → 102,895 after deduplication (67,631 exact duplicates removed)
• 966 organisations | 28 distinct activity types
• Trial period: January–March 2024 (30-day trials)
• Conversion rate: 21.3% (206 / 966 organisations)

See data/README.md for full schema and data quality notes.

---

Definition of Trial Activation

An organisation achieves Trial Activation when it completes all 5 trial goals, demonstrating that it has experienced the core value of the platform across scheduling, management, visibility, attendance, and early habitual engagement.

The 5 Trial Goals

#	Goal	Condition	Completion Rate	Conv% if Completed
1	Core Scheduling	Created ≥ 5 shifts	46.6%	21.8%
2	Schedule Management	Applied a template OR approved ≥ 1 shift	25.6%	22.7%
3	Mobile Visibility	Loaded mobile schedule ≥ 3 times	36.0%	22.1%
4	Time Tracking	Recorded ≥ 1 punch-in	21.8%	22.7%
5	Early Engagement	≥ 10 events in first 7 days of trial	40.8%	20.8%

Activation rate: 9.3% of organisations complete all 5 goals
Conversion rate among activated orgs: 22.2% vs 21.3% baseline

Rationale & Honest Caveats

Goals are grounded in product-value logic — each represents a meaningful step in the customer journey from setup to embedded daily use. However, these goals are hypotheses, not proven conversion levers.

No individual activity or engagement feature is a statistically significant predictor of conversion (all chi-square p > 0.05, Mann-Whitney p > 0.05, all ML models AUC ≈ 0.50–0.54). The median days-to-convert is 30 — the trial length itself — suggesting conversion is primarily a pricing decision made at trial expiry. The activation framework is a structured starting point for A/B testing, not a guaranteed lever.

---

Key Findings

Conversion

• 21.3% overall conversion rate, consistent across Jan–Mar 2024 cohorts (22.9%, 22.8%, 18.2%)
• Median conversion happens at day 30 — the end of the trial window
• 0% of orgs convert by day 7; only 48.1% of converters have converted by day 30

Engagement

• Converters and non-converters show identical median engagement: 8 events, 2 unique activities, 1 active day
• Scheduling is the dominant module (88.2% adoption); all other modules used by fewer than 50% of orgs
• 81% of orgs are effectively dormant — 785 orgs with median 5 events across their entire trial

Weekly Patterns

• 924 orgs active in week 1, dropping to only 206 (22.3%) in week 2
• Week 2–4 retention is flat — orgs either engage heavily early or not at all

Segmentation

Segment	Orgs	Conv Rate	Median Events
High Engagement	16	25.0%	1,564
Medium Engagement	79	17.7%	442
Low Engagement	86	25.6%	134
Dormant	785	21.1%	5

Engagement level does not reliably predict conversion — even dormant orgs convert at the baseline rate.

---

SQL Models

Both mart tables follow dbt conventions. See sql/ for full code and ARCHITECTURE.md for design decisions.

stg_trial_events

Deduplicates on (organization_id, activity_name, timestamp), parses all columns, derives activity_module, trial_week, days_since_trial_start, and filters to in-window events only.

fct_trial_goals

Grain: one row per organisation. Goal completion flags + raw metric counts. Exposes counts so downstream consumers can adjust thresholds without rewriting SQL.

fct_trial_activation

Grain: one row per organisation. is_activated boolean, activation_tier segmentation (Fully Activated / Partially Activated / Early Engaged / Dormant), and days_trial_start_to_convert.

---

Dashboard

Interactive analytics dashboard across 5 tabs with global filters (Cohort, Conversion status, Activation tier).

start dashboard\index.html    # Windows
open dashboard/index.html     # Mac

Live demo: https://henrymorgandibie.github.io/trial-activation-challenge/

Screenshots

Overview — KPIs & Module Adoption

Activation Goals — Funnel & Goal Table

Driver Analysis — Model AUC & Feature Importance

Engagement Segments

---

Recommendations

1. Collect missing signals — NPS at day 7, CRM touchpoints, team size, industry
2. Attack week 1→2 drop-off — automated nudge at day 5–6 for orgs with <10 events
3. A/B test the activation goals — one quarter of treatment data validates or kills the framework
4. Investigate the dormant majority — day-3 survey to understand intent
5. Monitor the March cohort — 18.2% CVR vs ~23% for Jan/Feb needs watching
6. Prioritise PunchClock & template adoption — Goals 2 & 4 have the highest conversion lift

---

Setup & Reproduction

# Clone the repo
git clone https://github.com/HenryMorganDibie/trial-activation-challenge
cd trial-activation-challenge

# Create virtual environment
python -m venv .venv
.venv\Scripts\activate       # Windows
# source .venv/bin/activate  # Mac/Linux

# Install dependencies
pip install -r requirements.txt

# Add data file
# Place "DA task.csv" in the data/ directory

# Run full pipeline
python run_all.py

# Or run scripts individually
python notebooks/01_cleaning_and_eda.py
python notebooks/02_driver_analysis.py
python notebooks/03_descriptive_analytics.py

All scripts run end-to-end without manual intervention. Output PNGs saved to notebooks/.

---

SQL Usage Note

Models use dbt {{ ref() }} and {{ source() }} syntax. To run outside dbt replace:

• {{ source('raw', 'behavioural_data') }} → your raw table name
• {{ ref('stg_trial_events') }} → stg_trial_events
• {{ ref('fct_trial_goals') }} → fct_trial_goals

See DEPLOYMENT.md for full dbt + Snowflake/BigQuery setup.

---

Further Reading

Document	Purpose
FINDINGS.md	Plain-English summary for non-technical stakeholders
ARCHITECTURE.md	Layer decisions, analytical tradeoffs, limitations
DEPLOYMENT.md	GitHub Pages + dbt/Snowflake/BigQuery deployment
data/README.md	Dataset schema and data quality notes