Query icon
If icon
Log icon
Schedule icon

Pure-SQL Cohort Retention Analytics with DuckDB

Lightweight cohort retention analytics in Kestra using in-process DuckDB SQL on input CSV files, featuring threshold evaluation without Python or external webhook requirements.

Categories
Data

Automated cohort retention pipeline that computes monthly signup-to-order user retention metrics directly from user-uploaded event files using pure DuckDB SQL.

Unlike cohort-retention-duckdb (which requires Python scripting, Parquet datasets, and Slack webhooks), this blueprint offers a zero-dependency, pure-SQL alternative that teams can drop in immediately with standard CSV event logs.

How it works

  1. The flow takes an input CSV file containing raw user event data (user_id, event_name, signup_date, event_date).
  2. DuckDB's embedded read_csv_auto() reads and parses the event file in-memory.
  3. An analytical SQL query groups users into monthly signup cohorts, identifies repeat purchasers, and calculates precise retention percentages.
  4. An If conditional gate checks whether the cohort retention meets the target threshold (>= 50%), routing to success logs or alert notifications.

What you get

  • Pure SQL analytical transformations with zero Python runtime dependencies.
  • Direct support for CSV event file uploads via Kestra inputs.
  • Automated percentage calculation for user retention and churn tracking.
  • Standalone, lightweight execution with no third-party API keys or webhooks required.

Who it's for

  • Analytics and Data Engineers who want fast SQL-only retention metrics.
  • Product teams looking for an out-of-the-box template to audit customer retention from CSV exports.
  • Teams looking for a simpler alternative to complex Python/webhook pipelines.

Why orchestrate this with Kestra

  • Declarative YAML definition keeps analytical transformations version-controlled and reproducible.
  • Serverless in-process DuckDB execution avoids spinning up dedicated database clusters.
  • Built-in UI file upload allows ad-hoc analyses alongside scheduled production runs.

Prerequisites

  • A CSV file with user activity containing user_id, event_name, signup_date, and event_date.

Secrets

  • None required.

Quick start

  1. Import this blueprint into your Kestra instance.
  2. Click Execute and upload your user events CSV file.
  3. View calculated cohort retention metrics in the Outputs tab.

How to extend

  • Connect read_csv_auto() directly to S3 or Google Cloud Storage buckets.
  • Add notification tasks (Slack, Email) in the failure branch when retention drops below targets.
  • Export calculated cohorts directly to Postgres, Snowflake, or BigQuery.

Links

Orchestrate with Kestra
Orchestrate DuckDB with Kestra
Share this Blueprint
See How

New to Kestra?

Use blueprints to kickstart your first workflows.