# E-Commerce Analytics Primer (/free-sample-datasets/e-commerce)



This primer introduces data engineers to sample use cases for data-driven decision-making in
e-commerce. It walks through ingesting data and exploring performance optimizations using the
Firebolt Cloud Data Warehouse, with data from the Open Customer Data Platform (CDP) Project.

## Understanding the e-commerce data model [#understanding-the-e-commerce-data-model]

A well-thought-out schema is critical for efficiently managing the diverse data in an e-commerce
warehouse — user interactions, product details, and transaction records. This dataset uses a
single-table design:

| Property        | Data Type      | Description                             |
| --------------- | -------------- | --------------------------------------- |
| `event_time`    | TIMESTAMPTZ    | Time in UTC                             |
| `event_type`    | TEXT           | Customer event (view / cart / purchase) |
| `product_id`    | BIGINT         | ID of a product                         |
| `category_id`   | TEXT           | Product's category ID                   |
| `category_code` | TEXT           | Product's category code                 |
| `brand`         | TEXT           | Product brand                           |
| `price`         | NUMERIC(38, 9) | Price of a product                      |
| `user_id`       | TEXT           | Permanent user ID                       |
| `user_session`  | TEXT           | Temporary user session ID               |

The schema supports user identification, event tracking, product trends, and session details for
personalization and segmentation.

## Working with the e-commerce dataset [#working-with-the-e-commerce-dataset]

The data comes from a Kaggle dataset loaded into a public Amazon S3 bucket
(`firebolt-sample-datasets-public-us-east-1`). It spans seven months of activity (October 2019 –
April 2020), roughly 32GB uncompressed as CSV, converted to Parquet for ingestion. Once ingested it
is approximately **412 million records** — 52GB uncompressed, 21GB compressed.

Start by creating a database and engine:

```sql
CREATE DATABASE ecommercedb WITH DESCRIPTION = 'ECommerce Analytics Primer';

CREATE ENGINE ecommerceEngine;

START ENGINE ecommerceEngine;

USE ENGINE ecommerceEngine;

USE ecommercedb;
```

Create the fact table:

```sql
CREATE FACT TABLE IF NOT EXISTS "ecommerce" (
  "event_time" TIMESTAMPTZ NOT NULL,
  "event_type" TEXT NOT NULL,
  "product_id" BIGINT NOT NULL,
  "category_id" TEXT NULL,
  "category_code" TEXT NULL,
  "brand" TEXT NULL,
  "price" NUMERIC(38, 9) NULL,
  "user_id" TEXT NULL,
  "user_session" TEXT NULL
);
```

Ingest the data:

```sql
COPY INTO ecommerce FROM 's3://firebolt-sample-datasets-public-us-east-1/ecommerce_primer/parquet/'
WITH PATTERN='*.gz.parquet' TYPE = PARQUET;

SHOW TABLES;
```

## Sample analytics on the e-commerce dataset [#sample-analytics-on-the-e-commerce-dataset]

### Customer lifetime value (LTV) [#customer-lifetime-value-ltv]

Assess the total revenue generated by customers over their engagement with your brand.

```sql
SELECT
    user_id,
    SUM(price) AS total_revenue,
    COUNT(DISTINCT user_session) AS total_sessions,
    (SUM(price) / COUNT(DISTINCT user_session)) AS average_revenue_per_session
FROM ecommerce
WHERE event_type = 'purchase'
AND user_session IS NOT NULL
GROUP BY user_id
ORDER BY total_revenue DESC;
```

### Funnel analysis for conversion optimization [#funnel-analysis-for-conversion-optimization]

Track the customer journey from views to purchases and identify drop-off points.

```sql
SELECT
    event_type,
    COUNT(DISTINCT user_id) AS unique_users
FROM ecommerce
WHERE event_type IN ('view', 'cart', 'purchase')
GROUP BY event_type
ORDER BY 2 DESC;
```

### Product recommendations and cross-selling [#product-recommendations-and-cross-selling]

Analyze co-purchase data to reveal products frequently bought together.

```sql
WITH CoPurchaseCounts AS (
    SELECT a.product_id AS product_A, b.product_id AS product_B, COUNT(*) AS purchase_count
    FROM ecommerce a
    JOIN ecommerce b ON a.user_session = b.user_session AND a.product_id < b.product_id
    WHERE a.event_type = 'purchase' AND b.event_type = 'purchase'
    GROUP BY a.product_id, b.product_id
)
SELECT *
FROM CoPurchaseCounts cp
WHERE purchase_count > 2
ORDER BY purchase_count DESC
LIMIT 10;
```

### Seasonal sales trends and insights [#seasonal-sales-trends-and-insights]

Analyze sales by month to understand seasonal variation.

```sql
SELECT
    EXTRACT(MONTH FROM event_time) AS sales_month,
    SUM(price) AS total_revenue
FROM ecommerce
WHERE event_type = 'purchase'
GROUP BY sales_month
ORDER BY sales_month;
```

### Customer segmentation for targeted marketing [#customer-segmentation-for-targeted-marketing]

Segment customers by activity level and average revenue.

```sql
WITH CustomerSegments AS (
    SELECT
        user_id,
        COUNT(DISTINCT user_session) AS session_count,
        SUM(price) AS total_revenue
    FROM ecommerce
    WHERE event_type = 'purchase'
    GROUP BY user_id
)
SELECT
    CASE
        WHEN session_count <= 5 THEN 'Low Activity'
        WHEN session_count <= 20 THEN 'Medium Activity'
        ELSE 'High Activity'
    END AS segment,
    COUNT(user_id) AS user_count,
    AVG(total_revenue) AS avg_revenue
FROM CustomerSegments
GROUP BY segment;
```

### Cart abandonment rate [#cart-abandonment-rate]

Identify where potential customers drop off in the purchase process.

```sql
WITH CartAbandonment AS (
    SELECT event_time::DATE as event_date,
        user_id,
        COUNT(CASE WHEN event_type = 'cart' THEN 1 ELSE NULL END) AS cart_count,
        COUNT(CASE WHEN event_type = 'purchase' THEN 1 ELSE NULL END) AS purchase_count
    FROM ecommerce
    WHERE event_type IN ('cart', 'purchase')
    GROUP BY event_date, user_id
)
SELECT
    event_date, COUNT(*) AS total_users,
    SUM(CASE WHEN cart_count > 0 AND purchase_count = 0 THEN 1 ELSE 0 END) AS abandoned_carts,
    (SUM(CASE WHEN cart_count > 0 AND purchase_count = 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) AS abandonment_rate
FROM CartAbandonment
GROUP BY event_date
ORDER BY event_date;
```

### User purchase frequency distribution [#user-purchase-frequency-distribution]

Analyze the distribution of user purchase frequencies for marketing planning.

```sql
WITH UserPurchaseFrequency AS (
    SELECT
        user_id,
        COUNT(DISTINCT event_time::DATE) AS purchase_frequency
    FROM ecommerce
    WHERE event_type = 'purchase'
    GROUP BY user_id
)
SELECT
    purchase_frequency,
    COUNT(user_id) AS user_count
FROM UserPurchaseFrequency
GROUP BY purchase_frequency
ORDER BY purchase_frequency;
```

## Optimizing performance [#optimizing-performance]

Use the `recommend_ddl` command to find an optimal primary index for the workload:

```sql
CALL recommend_ddl (
  ecommerce,
  (
    SELECT
      query_text
    FROM
      information_schema.engine_query_history
    where query_text ilike 'select%'
    and end_time > NOW() - INTERVAL '30 minutes'
  )
);
```

For this workload, Firebolt recommends a primary index on `event_type` and `user_session`, which
achieves roughly 94% average data pruning. Recreate the table with that index and reload:

```sql
CREATE FACT TABLE IF NOT EXISTS "ecommerce_pi" (
  "event_time" TIMESTAMPTZ NOT NULL,
  "event_type" TEXT NOT NULL,
  "product_id" BIGINT NOT NULL,
  "category_id" TEXT NULL,
  "category_code" TEXT NULL,
  "brand" TEXT NULL,
  "price" double precision NULL,
  "user_id" TEXT NULL,
  "user_session" TEXT NULL
) PRIMARY INDEX event_type, user_session;

INSERT INTO ecommerce_pi SELECT * FROM ecommerce;
```

Data analytics is a critical part of e-commerce operations today, and performance and efficiency are
essential in cloud-based analytics to avoid slower performance and cost overruns.

## Appendix: querying the data lake with external tables [#appendix-querying-the-data-lake-with-external-tables]

You can query data directly from S3 without loading it into the warehouse — useful for ad-hoc
analysis.

```sql
CREATE EXTERNAL TABLE IF NOT EXISTS ex_ecommerce (
  event_time TIMESTAMPTZ NOT NULL,
  event_type TEXT NOT NULL,
  product_id BIGINT NOT NULL,
  category_id TEXT NULL,
  category_code TEXT NULL,
  brand TEXT NOT NULL,
  price NUMERIC(38, 9) NULL,
  user_id TEXT NULL,
  user_session TEXT NULL
) URL = 's3://firebolt-sample-datasets-public-us-east-1/ecommerce_primer/parquet/'
 OBJECT_PATTERN = '*.gz.parquet' TYPE= (PARQUET);
```

```sql
SELECT event_type, count(*)
FROM ex_ecommerce
GROUP BY ALL;
```

<Callout type="info">
  Querying directly against the data lake does not leverage Firebolt's performance optimizations in
  the form of columnar storage and indexes.
</Callout>

<InlineCta ctaLabel="Get started for free" ctaUrl="https://go.firebolt.io/signup">
  Spin up an engine and load this dataset yourself — Firebolt is free to try.
</InlineCta>