Skip to content

E-commerce Analytics Tutorial

Build a complete e-commerce analytics pipeline with CSV orders and optional Stripe integration.

What You'll Build

By the end of this tutorial, you'll have:

  • CSV order data loaded into DuckDB
  • Auto-generated staging models
  • Custom dbt models for revenue analysis
  • A dashboard showing key e-commerce metrics

Duration: ~30 minutes

Prerequisites

  • Dango installed (pip install getdango)
  • Docker running
  • Sample data or your own CSV files

Step 1: Create Project

# Create new Dango project
dango init ecommerce-analytics
cd ecommerce-analytics

# Start services
dango start

Authentication

dango init prompts you to set an admin password. This password is used to log into the Dango web UI where you access Metabase, view sync status, and manage your project. See Authentication for details.

Verify services are running: 

dango status

Step 2: Prepare Sample Data

Create sample order data (or use your own):

mkdir -p data/uploads/orders

Create data/uploads/orders/orders.csv:

order_id,customer_id,order_date,status,total_amount,discount,shipping
ORD001,CUST001,2024-01-15,completed,125.99,0,10.00
ORD002,CUST002,2024-01-15,completed,89.50,5.00,10.00
ORD003,CUST001,2024-01-16,completed,245.00,20.00,0
ORD004,CUST003,2024-01-17,pending,67.25,0,10.00
ORD005,CUST002,2024-01-18,completed,199.99,15.00,10.00
ORD006,CUST004,2024-01-19,cancelled,150.00,0,10.00
ORD007,CUST001,2024-01-20,completed,312.50,25.00,0
ORD008,CUST005,2024-01-21,completed,78.99,0,10.00
ORD009,CUST003,2024-01-22,completed,425.00,50.00,0
ORD010,CUST002,2024-01-23,completed,156.75,10.00,10.00

Create data/uploads/orders/customers.csv:

customer_id,name,email,signup_date,country
CUST001,Alice Johnson,[email protected],2023-06-15,US
CUST002,Bob Smith,[email protected],2023-08-20,US
CUST003,Carol Williams,[email protected],2023-10-01,UK
CUST004,David Brown,[email protected],2023-11-15,CA
CUST005,Eve Davis,[email protected],2024-01-10,US

Step 3: Add Data Source

Run the interactive source wizard:

dango source add

When prompted:

  1. Select File Import from the source categories
  2. Source name: ecommerce_orders
  3. Directory: data/uploads/orders
  4. File pattern: *.csv

Or manually edit .dango/sources.yml:

sources:
  - name: ecommerce_orders
    type: local_files
    enabled: true
    local_files:
      directory: data/uploads/orders
      file_pattern: "*.csv"

Step 4: Sync Data

# Sync CSV files to DuckDB
dango sync

Expected output: 

Syncing source: ecommerce_orders
  Loading: orders.csv (10 rows)
  Loading: customers.csv (5 rows)
Sync complete: 2 tables, 15 total rows
Generating dbt staging models...

Verify data loaded: 

dango db status

Step 5: Explore Staging Models

Dango auto-generates staging models in dbt/models/staging/:

ls dbt/models/staging/
# stg_ecommerce_orders_orders.sql
# stg_ecommerce_orders_customers.sql
# sources.yml

View generated model: 

cat dbt/models/staging/stg_ecommerce_orders_orders.sql

Step 6: Create Custom Models

Revenue by Day

Create dbt/models/marts/fct_daily_revenue.sql:

{{ config(materialized='table') }}

with orders as (
    select * from {{ ref('stg_ecommerce_orders_orders') }}
    where status = 'completed'
),

daily_metrics as (
    select
        date_trunc('day', order_date::date) as day,
        count(distinct order_id) as order_count,
        count(distinct customer_id) as unique_customers,
        sum(total_amount) as gross_revenue,
        sum(discount) as total_discounts,
        sum(shipping) as total_shipping,
        sum(total_amount - discount + shipping) as net_revenue
    from orders
    group by 1
)

select * from daily_metrics
order by day

Customer Summary

Create dbt/models/marts/dim_customers.sql:

{{ config(materialized='table') }}

with customers as (
    select * from {{ ref('stg_ecommerce_orders_customers') }}
),

orders as (
    select * from {{ ref('stg_ecommerce_orders_orders') }}
    where status = 'completed'
),

customer_orders as (
    select
        customer_id,
        count(distinct order_id) as total_orders,
        sum(total_amount) as lifetime_value,
        min(order_date) as first_order_date,
        max(order_date) as last_order_date
    from orders
    group by 1
)

select
    c.customer_id,
    c.name,
    c.email,
    c.country,
    c.signup_date,
    coalesce(co.total_orders, 0) as total_orders,
    coalesce(co.lifetime_value, 0) as lifetime_value,
    co.first_order_date,
    co.last_order_date
from customers c
left join customer_orders co using (customer_id)

Step 7: Run Transformations

# Run all dbt models and tests
dango run

Tip

dango run executes dbt build, which runs both models and tests in dependency order. To run specific models, use dango run --select fct_daily_revenue dim_customers.

Verify marts created: 

duckdb data/warehouse.duckdb "SELECT * FROM main.fct_daily_revenue"

Step 8: Create Dashboard

Open the Dango web UI at http://localhost:8800 and log in with the admin credentials you set during dango init. Navigate to Metabase via the sidebar to create visualizations.

Create Questions

1. Daily Revenue Chart: - Click + New → Question - Select Native query - Run: 

SELECT day, net_revenue, order_count
FROM fct_daily_revenue
ORDER BY day
- Visualization: Line chart - Save as "Daily Revenue"

2. Top Customers: - New question, native query: 

SELECT name, country, total_orders, lifetime_value
FROM dim_customers
ORDER BY lifetime_value DESC
LIMIT 10
- Visualization: Table - Save as "Top Customers"

3. Revenue KPIs: - New question: 

SELECT
    sum(net_revenue) as total_revenue,
    sum(order_count) as total_orders,
    avg(net_revenue / order_count) as avg_order_value
FROM fct_daily_revenue
- Save as "Revenue KPIs"

Build Dashboard

  1. Click + New → Dashboard
  2. Name it "E-commerce Overview"
  3. Add your saved questions
  4. Arrange layout:
┌─────────────────────────────────────────┐
│  Revenue KPIs (Scalar cards)            │
├─────────────────────────────────────────┤
│  Daily Revenue (Line chart)             │
├───────────────────┬─────────────────────┤
│  Top Customers    │  Revenue by Country │
│  (Table)          │  (Pie/Bar chart)    │
└───────────────────┴─────────────────────┘

Step 9: Add More Data (Optional)

Add Stripe Payments

If you have Stripe access:

# Launch the interactive source wizard and select Stripe
dango source add

Configure with your API key, then:

# Sync Stripe data
dango sync stripe

# Run to include new staging models
dango run

Create Combined Model

dbt/models/marts/fct_revenue_combined.sql:

{{ config(materialized='table') }}

-- Combine CSV orders with Stripe charges
with csv_revenue as (
    select
        order_date as date,
        'csv' as source,
        sum(total_amount) as revenue
    from {{ ref('stg_ecommerce_orders_orders') }}
    where status = 'completed'
    group by 1
),

{% if ref('stg_stripe_charges') %}
stripe_revenue as (
    select
        date_trunc('day', created) as date,
        'stripe' as source,
        sum(amount / 100.0) as revenue
    from {{ ref('stg_stripe_charges') }}
    where status = 'succeeded'
    group by 1
)
{% endif %}

select * from csv_revenue
{% if ref('stg_stripe_charges') %}
union all
select * from stripe_revenue
{% endif %}

Step 10: Export Dashboard

Save your Metabase work to version-controlled YAML files:

# Export Metabase dashboards and questions
dango metabase save

# Commit to git
git add .
git commit -m "feat: e-commerce analytics pipeline"

Summary

You've built a complete e-commerce analytics pipeline:

  • CSV data ingestion
  • Auto-generated staging models
  • Custom fact and dimension tables
  • Interactive dashboard

Files Created

ecommerce-analytics/
├── .dango/sources.yml           # Source config
├── data/uploads/orders/         # CSV files
├── dbt/models/
│   ├── staging/                 # Auto-generated
│   └── marts/
│       ├── fct_daily_revenue.sql
│       └── dim_customers.sql
└── metabase/
    ├── dashboards/*.yml         # Dashboard exports
    └── questions/*.yml          # Question exports

Next Steps