Skip to main content
Firebolt
start free →

E-Commerce Analytics Primer

52GB · 412 million rows

ON THIS PAGE

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

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:

PropertyData TypeDescription
event_timeTIMESTAMPTZTime in UTC
event_typeTEXTCustomer event (view / cart / purchase)
product_idBIGINTID of a product
category_idTEXTProduct's category ID
category_codeTEXTProduct's category code
brandTEXTProduct brand
priceNUMERIC(38, 9)Price of a product
user_idTEXTPermanent user ID
user_sessionTEXTTemporary 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

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:

CREATE DATABASE ecommercedb WITH DESCRIPTION = 'ECommerce Analytics Primer';CREATE ENGINE ecommerceEngine;START ENGINE ecommerceEngine;USE ENGINE ecommerceEngine;USE ecommercedb;

Create the fact table:

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:

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

### Customer lifetime value (LTV)

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

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_sessionFROM ecommerceWHERE event_type = 'purchase'AND user_session IS NOT NULLGROUP BY user_idORDER BY total_revenue DESC;

### Funnel analysis for conversion optimization

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

SELECT    event_type,    COUNT(DISTINCT user_id) AS unique_usersFROM ecommerceWHERE event_type IN ('view', 'cart', 'purchase')GROUP BY event_typeORDER BY 2 DESC;

### Product recommendations and cross-selling

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

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 cpWHERE purchase_count > 2ORDER BY purchase_count DESCLIMIT 10;

Analyze sales by month to understand seasonal variation.

SELECT    EXTRACT(MONTH FROM event_time) AS sales_month,    SUM(price) AS total_revenueFROM ecommerceWHERE event_type = 'purchase'GROUP BY sales_monthORDER BY sales_month;

### Customer segmentation for targeted marketing

Segment customers by activity level and average revenue.

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_revenueFROM CustomerSegmentsGROUP BY segment;

### Cart abandonment rate

Identify where potential customers drop off in the purchase process.

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_rateFROM CartAbandonmentGROUP BY event_dateORDER BY event_date;

### User purchase frequency distribution

Analyze the distribution of user purchase frequencies for marketing planning.

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_countFROM UserPurchaseFrequencyGROUP BY purchase_frequencyORDER BY purchase_frequency;

## Optimizing performance

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

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:

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

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

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);
SELECT event_type, count(*)FROM ex_ecommerceGROUP BY ALL;