Business Use Cases for the B2C Commerce Intelligence JDBC Driver

This section provides analytics scenarios with production SQL queries that you can copy, paste, and execute against the Commerce Intelligence data warehouse. Each use case includes the business question being answered, the data tables involved, sample SQL queries, and practical implementation tips.

Note: You can refer to the Apache Calcite SQL Reference for a list of supported SQL functions and expressions. The Commerce Intelligence JDBC Driver uses the PostgreSQL dialect for query parsing and execution.

Business Question: How is my business performing across revenue and orders?

Primary Schema Tables:

• ccdw_aggr_sales_summary - Core sales metrics aggregation
• ccdw_dim_site - Site dimension for multi-site analysis
• ccdw_dim_date - Time dimension for trend analysis

Business Value: Merchandising teams track daily performance with automatic AOV/AOS calculations, seasonal trends, and cross-site comparisons.

SQL Query:

Tip: If you surface this in a UI, consider automatically calculating Average Order Value (AOV) and Average Order Size (AOS) from the aggregated data. Implement multiple currency support with automatic conversion display for global commerce scenarios.

Key Metrics: Revenue, Order count, Average Order Value, Units sold, Tax/Shipping amounts

Business Question: Which payment methods are customers using and how do they perform?

Primary Schema Tables:

• ccdw_aggr_payment_sales_summary - Payment method performance metrics
• ccdw_fact_order_payments - Individual payment transactions
• ccdw_dim_payment_method - Payment method details

Business Value: Operations teams track payment method adoption, optimize payment processing costs, and analyze gift certificate usage patterns.

SQL Query - Payment Method Performance:

SQL Query - Gift Certificate Analysis:

Tip: If you surface this in a UI, consider calculating payment method conversion rates and failure rates. Track gift certificate redemption patterns to optimize gift certificate marketing campaigns and identify seasonal trends in gift giving behavior.

Key Metrics: Payment volume by method, Average payment amount, Gift certificate redemption rates, Payment success/failure rates

Business Question: Where is my traffic coming from and which sources drive the most valuable visitors?

Primary Schema Tables:

• ccdw_aggr_visit_referrer - Traffic source attribution
• ccdw_aggr_visit - Visit performance metrics
• ccdw_dim_site - Site dimension for multi-site analysis

Business Value: Marketing teams optimize ad spend by traffic source, measure campaign effectiveness, identify high-value referrer domains, and understand traffic performance.

SQL Query - Top Referrers Analysis:

SQL Query - Traffic Source Conversion Analysis:

Tip: If you surface this in a UI, consider creating traffic source performance scorecards that combine visit volume, conversion rates, and revenue per visit. Distinguish between direct traffic, search engines, social media, and email campaigns to help marketing teams allocate budget effectively.

Key Metrics: Visit volume by source, Conversion rates by traffic medium, Revenue per visit by source, Traffic source diversity

Business Question: How effectively are we acquiring new customers and what drives registrations?

Primary Schema Tables:

• ccdw_aggr_registration - Customer acquisition tracking
• ccdw_fact_customer_registration - Individual registration events
• ccdw_fact_customer_list_snapshot - Customer counts over time
• ccdw_dim_customer - Customer master data

Business Value: Marketing teams track customer acquisition trends, measure registration conversion rates, identify optimal acquisition channels, and monitor customer base growth over time.

SQL Query - Customer Registration Trends:

SQL Query - Total Customer Growth:

Tip: If you surface this in a UI, consider calculating registration conversion rates from visits, tracking registration velocity trends, and segmenting registrations by acquisition source. Create cohort analysis to show customer lifetime value by registration period and device type.

Key Metrics: Daily registration counts, Total customer base growth, Registration conversion rates, Device-based registration patterns

Business Question: Do we have the right products available where customers want them?

Primary Schema Tables:

• ccdw_fact_inventory_record_snapshot_hourly - Real-time inventory levels
• ccdw_aggr_inventory_by_location - Location-specific inventory
• ccdw_aggr_inventory_by_location_group - Location group aggregations
• ccdw_dim_location_group - Location hierarchy
• ccdw_dim_product - Product details

Business Value: Operations teams monitor stock levels, optimize allocation across locations, reduce carrying costs, prevent stockouts, and improve fulfillment efficiency.

SQL Query - Inventory Trends Over Time for Specific SKU:

Tip: If you surface this in a UI, consider creating inventory alerts for low stock situations, tracking inventory velocity trends, and providing fulfillment optimization recommendations. Implement location-based inventory allocation suggestions to optimize shipping costs and delivery times.

Key Metrics: Available to fulfill quantities, Available to order quantities, On-hand inventory, Reserved inventory, Inventory turnover rates

Business Question: Are my promotions driving incremental sales or just discounting existing sales?

Data Combination Strategy: Combines 3 data sources for comprehensive promotion effectiveness:

1. Promotion Performance - ccdw_aggr_promotion_sales_summary
2. Overall Visit Data - ccdw_aggr_visit
3. Overall Sales Data - ccdw_aggr_sales_summary

Primary Schema Tables:

• ccdw_aggr_promotion_sales_summary - Promotion-specific sales data
• ccdw_dim_promotion - Promotion details and classification
• ccdw_dim_campaign - Campaign information
• ccdw_dim_coupon - Coupon details

SQL Query - Total Discount Analysis:

Tip: If you surface this in a UI, consider calculating incremental metrics by subtracting promotional performance from total performance to show "Without Promo" analysis. Create side-by-side comparison views showing promotional vs non-promotional conversion rates for clear impact assessment.

Business Value: Marketing teams compare promotional vs non-promotional performance, measure incremental revenue, and optimize promotion mix by type (product/order/shipping).

Business Question: Which search terms drive revenue vs which represent missed opportunities?

Data Combination: Search queries + results filtering → revenue attribution analysis

Primary Schema Tables:

• ccdw_aggr_search_conversion - Search-to-purchase conversion data
• ccdw_aggr_search_query - Search behavior and results
• ccdw_aggr_search - Search volume metrics

SQL Query - Search Query Performance:

Tip: If you surface this in a UI, consider providing toggles between "With Results" vs "Without Results" to highlight successful vs failed searches. Calculate revenue per search and conversion rates to show search effectiveness. Present failed searches as catalog gaps and missed opportunities for merchandising teams.

Business Value: Merchandising teams identify high-converting search terms, optimize product catalog for failed searches, and improve search result relevance.

Business Question: How do my products perform across different and channels?

Multi-Dimensional Transformation: Single product query → 4 different dimensional views:

1. By Device Type (Mobile vs Desktop)
2. By Site (Multi-site performance)
3. By Channel (Online vs other channels)
4. By Customer Type (Registered vs guest)

Primary Schema Tables:

• ccdw_aggr_product_sales_summary - Product sales by dimensions
• ccdw_fact_line_item - Individual purchase transactions
• ccdw_dim_product - Product master data
• ccdw_aggr_product_cobuy - Cross-sell analysis

SQL Query - Top Selling Products:

SQL Query - Product Co-Purchase Analysis:

Tip: If you surface this in a UI, consider transforming single product queries into multiple dimensional views (Device/Site/Channel/Customer) with toggleable filters. Calculate cross-sell opportunities and product affinity scores from co-purchase data to guide merchandising decisions.

Business Value: Buyers optimize product placement by channel and guide inventory allocation decisions.

Business Question: How are we acquiring and retaining customers?

Primary Schema Tables:

• ccdw_aggr_registration - Customer acquisition tracking
• ccdw_fact_customer_registration - Individual registration events
• ccdw_fact_customer_list_snapshot - Customer counts over time
• ccdw_dim_customer - Customer master data

SQL Query - Customer Registration Trends:

SQL Query - Total Customer Counts:

Tip: If you surface this in a UI, consider calculating new vs returning customer ratios and tracking customer acquisition velocity over time. Segment customers by registration source and device type to understand acquisition patterns and optimize marketing channels.

Business Value: Marketing teams track customer acquisition trends, segment new vs returning customers, and measure retention rates.

Business Question: What's the complete picture of site performance from traffic to revenue?

Multi-Query Coordination: 5 different data sources in unified presentation:

1. Visit Volume - ccdw_aggr_visit
2. Visit Duration - ccdw_aggr_visit with duration calculations
3. Revenue per Visit - ccdw_aggr_visit conversion data
4. Traffic Sources - ccdw_aggr_visit_referrer
5. Device Analysis - ccdw_aggr_visit_user_agent

Primary Schema Tables:

• ccdw_aggr_visit - Core visit metrics and conversion
• ccdw_aggr_visit_referrer - Traffic source attribution
• ccdw_aggr_visit_user_agent - Device and browser analysis
• ccdw_aggr_visit_checkout - Checkout funnel analysis

SQL Query - Visit Metrics by Device:

Tip: If you surface this in a UI, consider coordinating multiple data sources to create a unified traffic dashboard. Calculate conversion rates, average session duration, and revenue per visit across different dimensions for comprehensive traffic analysis.

Business Value: Marketing managers see complete traffic funnel from visits to revenue to customer acquisition.

Business Question: Is our system performing well enough to support customer experience?

UI Data Transformation: Raw technical data → business-relevant performance indicators:

• OCAPI Performance: ccdw_aggr_ocapi_request → Average response times
• SCAPI Cache: ccdw_aggr_scapi_request → Cache hit percentages
• Controller Performance: ccdw_aggr_controller_request → Performance buckets

Primary Schema Tables:

• ccdw_aggr_ocapi_request - Open Commerce API performance
• ccdw_aggr_scapi_request - Storefront API performance
• ccdw_aggr_controller_request - Controller performance
• ccdw_aggr_include_controller_request - Include controller metrics

SQL Query - OCAPI Performance:

Tip: If you surface this in a UI, consider calculating average response times from raw request counts and total response times. Create performance bucket visualizations and cache hit percentage calculations for operational monitoring and SLA tracking.

Business Value: Operations teams monitor system health, identify performance issues before customer impact, and optimize infrastructure spend.

Business Question: Which marketing campaigns are driving actual sales vs just traffic?

Data Combination Strategy: Combines source code activations with order data to calculate campaign conversion rates and attribution:

1. Campaign Activations - ccdw_aggr_source_code_activation
2. Campaign Orders - ccdw_aggr_source_code_sales
3. Campaign Organization - ccdw_dim_source_code_group

Primary Schema Tables:

• ccdw_aggr_source_code_activation - Campaign activation tracking
• ccdw_aggr_source_code_sales - Sales attribution to campaigns
• ccdw_fact_source_codes_activation - Individual activation events
• ccdw_dim_source_code_group - Campaign organization

SQL Query - Source Code Activations Analysis: This query measures marketing campaign traffic volume and health status across different sites and campaign groups.

Query Details:

• Business Purpose: Tracks how many times customers clicked on or activated marketing campaigns (source codes)
• Status Logic: Converts numeric status codes (0/1/2) to human-readable campaign states
• Segmentation: Groups results by site and campaign group for campaign portfolio analysis
• Filters: Allows filtering by date range, site, device type (mobile/desktop), and customer registration status
• Key Metric: activations = total number of times campaigns were activated/clicked

SQL Query - Source Code Sales Performance: This query measures actual sales results (orders, units, revenue) attributed to marketing campaigns, enabling ROI calculation and campaign effectiveness analysis.

Query Details:

• Business Purpose: Measures actual sales attributed to marketing campaigns for ROI analysis
• Attribution Logic: Only counts orders that were directly attributed to specific source codes/campaigns
• Key Metrics:
  • orders = number of orders attributed to this campaign
  • units = total units sold through this campaign
  • std_revenue = gross merchandise value in site's base currency
• ROI Calculation: Combine with activations data to calculate conversion rate (orders/activations)
• Campaign Performance: Compare revenue across different campaign groups and statuses

Tip: If you surface this in a UI, consider combining activations and orders data to calculate campaign conversion rates (orders/activations) and campaign efficiency metrics. Track ACTIVE vs INACTIVE vs INVALID source codes separately to understand campaign lifecycle performance.

Business Value: Marketing teams measure campaign effectiveness, calculate ROI by source code group, and optimize marketing spend allocation based on actual sales attribution rather than just traffic volume.

Business Question: How effective are our product recommendations and personalization?

Data Transformation Strategy: Combines recommendation viewing data with purchase attribution to calculate recommendation algorithm effectiveness:

1. Recommendation Performance - ccdw_aggr_product_recommendation_recommender
2. Product-Level Details - ccdw_aggr_detail_product_recommendation_recommender
3. Cross-Algorithm Comparison - Multiple recommender performance analysis

Primary Schema Tables:

• ccdw_aggr_product_recommendation - Overall recommendation performance
• ccdw_aggr_product_recommendation_recommender - Performance by recommender type
• ccdw_aggr_detail_product_recommendation_recommender - Product-level recommendation data

SQL Query - Recommendation Performance by Algorithm: This query analyzes the effectiveness of different recommendation algorithms (Einstein, collaborative filtering, etc.) by measuring user engagement and sales attribution across the entire recommendation funnel.

Query Details:

• Business Purpose: Compares effectiveness of different recommendation algorithms to optimize personalization strategy
• Recommendation Funnel Analysis:
  • recommender_views_count = how many times recommendation widgets were shown
  • product_views_count = how many times users viewed products within recommendations
  • clicks_count = how many times users clicked on recommended products
  • add_to_cart_count = how many times users added recommended products to cart
  • product_purchased_count = how many recommended products were actually purchased
• Performance Metrics:
  • ctr (Click-Through Rate) = clicks / widget views (measures initial engagement)
  • atc_rate (Add-to-Cart Rate) = cart adds / clicks (measures purchase intent)
  • conversion_rate = purchases / cart adds (measures final conversion)
• Revenue Attribution: std_attributed_revenue = revenue directly attributed to recommendations
• Algorithm Comparison: Results ordered by revenue to identify best-performing algorithms