E-commerce & Sales Analytics

Learn product analysis, customer segmentation, and sales forecasting using sample e-commerce data.

Time: 15-20 minutes Difficulty: Beginner-friendly

---

About This Tutorial

In this tutorial, you'll analyze a fictional e-commerce store using real-world analytical techniques. You'll learn to:

• Identify top-performing products
• Segment customers by behavior (RFM analysis)
• Analyze sales trends and forecast future performance

No SQL knowledge required—just follow the prompts and see what Shadowfax creates.

---

Get the Sample Data

Download the Dataset

We've prepared realistic e-commerce sample data for you:

Download E-commerce Sample Data (placeholder link)

What's included:

• orders.csv - 10,000 orders with dates, amounts, and product info
• customers.csv - 2,500 customers with demographics and signup dates
• products.csv - 150 products with categories, prices, and costs

Import the Data

1. Download and unzip the files
2. In your Shadowfax Workbook, click "Import Data"
3. Upload all three CSV files
4. Confirm they appear under "Sources" in your sidebar

Check the Schema

Click the Graph tab to see how Shadowfax discovered relationships between your tables:

---

Choose Your Path

Pick an analysis path based on what you want to learn. Each path is independent—do one, two, or all three!

Path A: Product Performance Analysis

Goal: Find your best products and understand what drives revenue.

Path B: Customer Segmentation (RFM)

Goal: Segment customers by Recency, Frequency, and Monetary value.

Path C: Sales Trends & Forecasting

Goal: Analyze sales patterns over time and spot trends.

---

Path A: Product Performance Analysis

Goal: Identify your top 10 products by revenue and understand their contribution to total sales.

Step 1: Calculate Revenue by Product

Prompt:

@[orders] @[products]
Join these tables and calculate total revenue by product

What the AI creates:

• View name: product_revenue (Classification: Combine + Aggregate)
• Joins orders with products on product_id
• Groups by product and sums revenue

Click the View to inspect:

• See the SQL join and aggregation logic
• Preview the data: product names with total revenue

Step 2: Add Percentage of Total

Now let's add a column showing each product's percentage of total revenue.

Prompt:

@[product_revenue]
Add a column showing percentage of total revenue

What the AI creates:

• View name: product_revenue_pct (Classification: Calculate Columns)
• Adds pct_of_total column using a window function
• Builds on top of the previous View

What you see:

product_name	total_revenue	pct_of_total
Wireless Headphones Pro	$125,450	8.2%
Ergonomic Office Chair	$98,320	6.4%
...	...	...

Step 3: Visualize Top 10 Products

Let's create a horizontal bar chart showing the top 10 products.

Prompt:

/visualize @[product_revenue_pct]
Create a horizontal bar chart showing top 10 products by revenue.
Sort by revenue descending and include data labels.

What you get:

Refine it (optional):

Make the bars blue gradient, and highlight the #1 product in gold

What You Learned

Joining Sources with multiple @mentions Aggregate Views (GROUP BY operations) Calculated columns (percentage calculations) View chaining (building Views on top of Views) Visualizations with /visualize

Next: Try Path B: Customer Segmentation or explore on your own!

---

Path B: Customer Segmentation (RFM)

Goal: Segment customers using RFM analysis (Recency, Frequency, Monetary) to identify VIPs, at-risk customers, and more.

What is RFM?

RFM is a proven method for customer segmentation:

• Recency: How recently did they purchase?
• Frequency: How often do they purchase?
• Monetary: How much do they spend?

Step 1: Calculate RFM Metrics

Prompt:

@[orders] @[customers]
For each customer, calculate:
- Recency: days since their last order
- Frequency: total number of orders
- Monetary: total amount spent

What the AI creates:

• View name: customer_rfm (Classification: Combine + Aggregate + Calculate Columns)
• Joins orders with customers
• Calculates the three RFM metrics per customer

Sample data:

customer_id	customer_name	recency_days	frequency	monetary
1001	Alice Johnson	5	12	$3,450
1002	Bob Smith	127	3	$890
...	...	...	...	...

Step 2: Score Each RFM Dimension

Now let's assign scores (1-5) for each RFM dimension, where 5 is best.

Prompt:

@[customer_rfm]
Create RFM scores (1-5 scale) for each dimension:
- Recency score: 5 = most recent, 1 = least recent
- Frequency score: 5 = highest frequency, 1 = lowest
- Monetary score: 5 = highest spend, 1 = lowest

Use quintiles to divide customers into 5 equal groups per dimension.

What the AI creates:

• View name: customer_rfm_scores (Classification: Calculate Groupwise)
• Uses NTILE window functions to create quintiles
• Assigns scores 1-5 for each dimension

Step 3: Create Customer Segments

Now let's define business segments based on RFM scores.

Prompt:

@[customer_rfm_scores]
Create customer segments:
- "VIP": All three scores are 4 or 5
- "High Value": Monetary score 4-5, others 3+
- "Active": Recency and Frequency 3+, Monetary 2+
- "At Risk": Recency 1-2, but Frequency and Monetary 3+
- "Inactive": All others

What the AI creates:

• View name: customer_segments (Classification: Calculate Columns)
• Uses CASE statement to assign segments
• Adds segment column

Step 4: Analyze Segment Distribution

Prompt:

@[customer_segments]
Count customers in each segment and calculate:
- Percentage of total customers
- Average monetary value per segment
- Total revenue contribution per segment

What the AI creates:

• View name: segment_summary (Classification: Aggregate)
• Groups by segment
• Calculates counts, averages, and totals

Step 5: Visualize Segments

Prompt:

/visualize @[segment_summary]
Create a stacked bar chart showing:
- Customer count by segment
- Revenue contribution by segment
Use two bars side by side for comparison

Insights you might discover:

• "VIP" customers are only 8% of customers but drive 35% of revenue
• "At Risk" segment needs re-engagement campaigns
• "Inactive" customers represent an opportunity for win-back offers

Bonus: Use AI Table for Segment Descriptions

Want AI to generate custom segment descriptions?

Prompt:

@[customer_segments]
Create an AI Table that generates a personalized segment description
for each customer based on their RFM scores and behavior

What the AI creates:

• AI Table with input columns: customer_name, segment, recency_days, frequency, monetary
• AI output column: personalized_description

After you refresh the AI Table:

customer_name	segment	personalized_description
Alice Johnson	VIP	"Loyal high-value customer who shops frequently and recently. Prioritize for exclusive offers."
Bob Smith	At Risk	"Previously engaged customer who hasn't purchased in 127 days. Consider re-engagement campaign."

What You Learned

Multi-step analytical pipeline (5 connected Views) Window functions (NTILE for quintiles) Complex calculated columns (CASE statements for segmentation) Aggregate analysis (segment summaries) AI Tables for semantic enrichment Multi-series visualizations

Next: Try Path C: Sales Trends or apply RFM to your own data!

---

Path C: Sales Trends & Forecasting

Goal: Analyze daily and monthly sales patterns, identify trends, and add moving averages.

Step 1: Daily Sales Totals

Prompt:

@[orders]
Calculate total revenue and order count by day

What the AI creates:

• View name: daily_sales (Classification: Aggregate)
• Groups orders by date
• Sums revenue and counts orders

Step 2: Add Moving Averages

Let's smooth out daily fluctuations with a 7-day and 30-day moving average.

Prompt:

@[daily_sales]
Add 7-day and 30-day moving averages for revenue

What the AI creates:

• View name: daily_sales_ma (Classification: Calculate Groupwise)
• Uses window functions with ROWS BETWEEN for moving averages
• Adds revenue_7day_ma and revenue_30day_ma columns

Step 3: Visualize the Trend

Prompt:

/visualize @[daily_sales_ma]
Create a line chart with:
- X-axis: Date
- Y-axis: Revenue
- Three lines: actual daily revenue (gray), 7-day MA (blue), 30-day MA (red)
- Format Y-axis as currency
- Add a title: "Daily Revenue with Trend Lines"

Step 4: Month-over-Month Growth

Let's roll up to monthly view and calculate growth rates.

Prompt:

@[orders]
Calculate monthly revenue and compare to previous month:
- Total revenue per month
- Month-over-month $ change
- Month-over-month % change

What the AI creates:

• View name: monthly_sales_growth (Classification: Aggregate + Calculate Groupwise)
• Groups by month
• Uses LAG window function for previous month comparison
• Calculates absolute and percentage change

Sample data:

month	revenue	mom_change_dollar	mom_change_pct
2024-01	$125,000	-	-
2024-02	$138,500	+$13,500	+10.8%
2024-03	$142,800	+$4,300	+3.1%
...	...	...	...

Step 5: Visualize Monthly Growth

Prompt:

/visualize @[monthly_sales_growth]
Create a combination chart:
- Bars: Monthly revenue (blue)
- Line: Month-over-month % change (orange, secondary Y-axis)
- Highlight months with negative growth in red

Bonus: Identify Seasonality

Prompt:

@[orders]
Analyze sales by day of week and by month to identify seasonal patterns.
Calculate average revenue for each day of week and each month.

What the AI creates:

• View name: seasonality_analysis
• Extracts day of week and month from dates
• Calculates average revenue per period

Insights you might discover:

• Saturdays have 40% higher sales than Tuesdays
• December revenue is 2.5x the annual average (holiday season)
• Mondays have high order counts but lower average order value

What You Learned

Time-based aggregations (daily, monthly) Moving averages (window functions with frame specifications) Period-over-period comparisons (LAG function) Combination charts (bars + lines, dual Y-axes) Seasonality analysis (extracting date components)

Next: Combine insights from all three paths to create a comprehensive sales dashboard!

---

Putting It All Together

You've now learned three powerful analytical workflows:

• Product analysis → Know what sells best
• Customer segmentation → Know who your best customers are
• Trend analysis → Know where your business is headed

Create a Dashboard

Prompt:

I want to create a dashboard combining insights from all three analyses.
Can you help me build a comprehensive view?

The AI will guide you through combining Views and creating a multi-chart dashboard.

---

What's Next?

Explore More

Try these follow-up analyses:

Product Analysis:

• Which product categories have the highest margins?
• What's the relationship between price and sales volume?
• Which products are frequently bought together?

Customer Analysis:

• What's the average customer lifetime value by segment?
• How does customer acquisition channel affect segment distribution?
• Can we predict which "Active" customers will become "VIP"?

Trend Analysis:

• Can we forecast next quarter's revenue based on historical trends?
• What's the impact of promotions on daily sales spikes?
• How does customer retention trend over cohorts?

Apply to Your Data

Now that you understand the patterns, import your own e-commerce data and:

1. Adapt the prompts to your table and column names
2. Customize segments to match your business model
3. Add domain-specific calculations (e.g., returns, discounts, shipping costs)

Learn More

• Prompt Engineering Guide - Advanced techniques
• Use Cases - More examples
• FAQ - Common questions

---

Quick Reference: Prompts Used

Goal	Prompt Pattern
Join tables	@[table1] @[table2] Join these and calculate...
Aggregate	@[table] Calculate total/count/average grouped by...
Add calculated column	@[view] Add a column showing...
Moving average	@[view] Add X-day moving average for [metric]
Period comparison	@[table] Compare month/week/day to previous period
Segment customers	@[view] Create segments based on [criteria]
Visualize	/visualize @[view] Create [chart type] showing...

---

Congratulations! You've completed the e-commerce analytics tutorial. Ready for another domain?

• SaaS & Subscription Analytics →
• Marketing Campaign Analytics →
• Finance & Operations Analytics →