Business Use Cases for the B2C Commerce Intelligence JDBC Driver

This section provides analytics scenarios with production SQL queries to 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: 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.

Use Case	Business Question	Key Tables	Primary Users
Sales Analytics	How is my business performing across revenue and orders?	`ccdw_aggr_sales_summary`	Merchandising
Payment Method Analytics	Which payment methods are customers using and how do they perform?	`ccdw_aggr_payment_sales_summary`	Operations
Traffic Source Analytics	Where is my traffic coming from and which sources drive the most valuable visitors?	`ccdw_aggr_visit_referrer`	Marketing, Digital
Customer Registration Analytics	How effectively are we acquiring new customers and what drives registrations?	`ccdw_aggr_registration`	Marketing, CRM
Inventory Analytics	Do we have the right products available where customers want them?	`ccdw_fact_inventory_record_snapshot_hourly`	Operations, Supply Chain
Promotion Analytics	Are my promotions driving incremental sales or just discounting existing sales?	`ccdw_aggr_promotion_sales_summary`	Marketing, Merchandising
Search Analytics	Which search terms drive revenue vs which represent missed opportunities?	`ccdw_aggr_search_conversion`	Merchandising, UX
Product Analytics	How do my products perform across different channels?	`ccdw_aggr_product_sales_summary`	Buyers, Merchandising
Customer Analytics	How are we acquiring and retaining customers?	`ccdw_dim_customer`	Marketing, CRM
Visit & Traffic Analytics	What’s the complete picture of site performance from traffic to revenue?	`ccdw_aggr_visit`	Marketing, UX
Technical Performance Analytics	Is our system performing well enough to support customer experience?	`ccdw_aggr_ocapi_request`	Operations, Engineering
Source Code & Campaign Analytics	Which marketing campaigns are driving actual sales vs just traffic?	`ccdw_aggr_source_code_activation`	Marketing, Campaign Management
Recommendation Analytics	How effective are our product recommendations and personalization?	`ccdw_aggr_product_recommendation`	Merchandising, Personalization

Sales Analytics

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:

1SELECT
2    ss.submit_date AS "date",
3    SUM(std_revenue) AS std_revenue,
4    SUM(num_orders) AS orders,
5    SUM(std_revenue) / SUM(num_orders) AS std_aov,
6    SUM(num_units) AS units,
7    SUM(num_units) / SUM(num_orders) AS aos,
8    SUM(std_tax) AS std_tax,
9    SUM(std_shipping) AS std_shipping
10FROM ccdw_aggr_sales_summary ss
11JOIN ccdw_dim_site s
12    ON s.site_id = ss.site_id
13WHERE ss.submit_date >= ?
14    AND ss.submit_date <= ?
15    AND s.nsite_id = ?
16GROUP BY ss.submit_date
17ORDER BY ss.submit_date

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

Payment Method Analytics

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:

1SELECT
2    pm.display_name AS payment_method,
3    SUM(pss.num_payments) AS total_payments,
4    SUM(pss.num_orders) AS orders_with_payment,
5    SUM(pss.std_captured_amount) AS std_captured_amount,
6    SUM(pss.std_refunded_amount) AS std_refunded_amount,
7    SUM(pss.std_transaction_amount) AS std_transaction_amount,
8    (SUM(pss.std_captured_amount) / SUM(pss.num_payments)) AS avg_payment_amount
9FROM ccdw_aggr_payment_sales_summary pss
10JOIN ccdw_dim_payment_method pm ON pm.payment_method_id = pss.payment_method_id
11JOIN ccdw_dim_site s ON s.site_id = pss.site_id
12WHERE pss.submit_date >= ? AND pss.submit_date <= ?
13    AND s.nsite_id = ?
14GROUP BY pm.display_name
15ORDER BY std_captured_amount DESC

SQL Query - Gift Certificate Analysis:

1SELECT
2    pss.submit_date AS "date",
3    s.nsite_id AS site,
4    SUM(pss.num_orders) AS redemption_count,
5    SUM(pss.std_transaction_amount) AS std_redemption_value
6FROM ccdw_aggr_payment_sales_summary pss
7JOIN ccdw_dim_site s
8    ON s.site_id = pss.site_id
9JOIN ccdw_dim_payment_method pm
10    ON pm.payment_method_id = pss.payment_method_id
11WHERE pss.submit_date >= ?
12    AND pss.submit_date <= ?
13    AND s.nsite_id = ?
14    AND pm.npayment_method_id = 'GIFT_CERTIFICATE'
15GROUP BY
16    pss.submit_date,
17    s.nsite_id
18ORDER BY
19    pss.submit_date ASC,
20    s.nsite_id ASC

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

Traffic Source Analytics

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:

1WITH total AS (
2    SELECT SUM(num_visits) AS total_visits
3    FROM ccdw_aggr_visit_referrer
4    WHERE visit_date >= ?
5        AND visit_date <= ?
6)
7SELECT
8    vr.referrer_medium AS traffic_medium,
9    vr.referrer_source AS traffic_source,
10    SUM(vr.num_visits) AS total_visits,
11    SUM(vr.num_visits) * 100.0 / total.total_visits AS visit_percentage
12FROM ccdw_aggr_visit_referrer vr
13JOIN ccdw_dim_site s
14    ON s.site_id = vr.site_id
15JOIN total ON TRUE
16WHERE vr.visit_date >= ?
17    AND vr.visit_date <= ?
18    AND s.nsite_id = ?
19GROUP BY
20    vr.referrer_medium,
21    vr.referrer_source,
22    total.total_visits
23ORDER BY total_visits DESC
24LIMIT 20

SQL Query - Traffic Source Conversion Analysis:

1SELECT
2    vr.referrer_medium AS traffic_medium,
3    SUM(v.num_visits) AS total_visits,
4    SUM(v.num_converted_visits) AS converted_visits,
5    SUM(v.std_revenue) AS std_revenue,
6    CASE WHEN SUM(v.num_visits) > 0
7         THEN (CAST(SUM(v.num_converted_visits) AS FLOAT) / SUM(v.num_visits)) * 100
8         ELSE 0
9    END AS conversion_rate,
10    CASE WHEN SUM(v.num_visits) > 0
11         THEN SUM(v.std_revenue) / SUM(v.num_visits)
12         ELSE 0
13    END AS revenue_per_visit
14FROM ccdw_aggr_visit_referrer vr
15JOIN ccdw_aggr_visit v
16    ON v.visit_date = vr.visit_date
17    AND v.site_id = vr.site_id
18    AND v.device_class_code = vr.device_class_code
19JOIN ccdw_dim_site s
20    ON s.site_id = vr.site_id
21WHERE vr.visit_date >= ?
22    AND vr.visit_date <= ?
23    AND s.nsite_id = ?
24GROUP BY vr.referrer_medium
25ORDER BY std_revenue DESC

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

Customer Registration Analytics

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:

1SELECT
2    r.registration_date AS "date",
3    SUM(r.num_registrations) AS new_registrations,
4    r.device_class_code,
5    s.nsite_id
6FROM ccdw_aggr_registration r
7JOIN ccdw_dim_site s ON s.site_id = r.site_id
8WHERE r.registration_date >= ? AND r.registration_date <= ?
9    AND s.nsite_id = ?
10GROUP BY r.registration_date, r.device_class_code, s.nsite_id
11ORDER BY r.registration_date

SQL Query - Total Customer Growth:

1WITH customer_snapshots AS (
2    SELECT
3        cls.site_id,
4        cls.ncustomer_list_id,
5        CAST(cls.utc_record_timestamp AS DATE) AS snapshot_date,
6        cls.utc_record_timestamp,
7        cls.num_customers,
8        ROW_NUMBER() OVER (
9            PARTITION BY cls.site_id, CAST(cls.utc_record_timestamp AS DATE)
10            ORDER BY cls.utc_record_timestamp DESC
11        ) AS rn
12    FROM ccdw_fact_customer_list_snapshot cls
13    JOIN ccdw_dim_site s
14        ON cls.site_id = s.site_id
15    WHERE CAST(cls.utc_record_timestamp AS DATE) >= ?
16        AND CAST(cls.utc_record_timestamp AS DATE) <= ?
17        AND s.nsite_id = ?
18),
19unique_lists AS (
20    SELECT
21        ncustomer_list_id,
22        snapshot_date,
23        num_customers,
24        ROW_NUMBER() OVER (
25            PARTITION BY ncustomer_list_id, snapshot_date
26            ORDER BY utc_record_timestamp DESC
27        ) AS rn
28    FROM customer_snapshots
29    WHERE rn = 1
30)
31SELECT
32    snapshot_date,
33    SUM(num_customers) AS total_customers
34FROM unique_lists
35WHERE rn = 1
36GROUP BY snapshot_date
37ORDER BY snapshot_date

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

Inventory Analytics

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:

1SSELECT
2    ih.utc_record_timestamp AS "timestamp",
3    p.nsku_id,
4    p.nproduct_id,
5    SUM(ih.available_to_fulfill) AS available_to_fulfill,
6    SUM(ih.available_to_order) AS available_to_order,
7    SUM(ih.on_hand) AS on_hand,
8    SUM(ih.reserved) AS reserved
9FROM
10    (SELECT utc_record_timestamp, sku_id, location_group_id,
11            available_to_fulfill, available_to_order, on_hand, reserved
12     FROM ccdw_fact_inventory_record_snapshot_hourly) ih
13JOIN
14    (SELECT sku_id, nsku_id, nproduct_id FROM ccdw_dim_product) p
15    ON p.sku_id = ih.sku_id
16JOIN
17    (SELECT location_group_id, nlocation_group_id FROM ccdw_dim_location_group) lg
18    ON lg.location_group_id = ih.location_group_id
19WHERE
20    ih.utc_record_timestamp >= ?
21    AND ih.utc_record_timestamp <= ?
22    AND p.nsku_id = ?
23    AND lg.nlocation_group_id = ?
24GROUP BY
25    ih.utc_record_timestamp, p.nsku_id, p.nproduct_id
26ORDER BY
27    ih.utc_record_timestamp

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

Promotion Analytics

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

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

Promotion Performance - ccdw_aggr_promotion_sales_summary
Overall Visit Data - ccdw_aggr_visit
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:

1WITH TOTAL_ORDERS AS (
2    SELECT
3        ss.submit_date AS submit_day,
4        SUM(num_orders) AS total_orders
5    FROM ccdw_aggr_sales_summary ss
6    WHERE ss.submit_date >= ?
7        AND ss.submit_date <= ?
8    GROUP BY ss.submit_date
9),
10PROMOTION_DISCOUNT AS (
11    SELECT
12        pss.submit_date AS submit_day,
13        p.promotion_class AS promotion_class,
14        SUM(std_total_discount) AS std_total_discount,
15        SUM(num_orders) AS promotion_orders
16    FROM ccdw_aggr_promotion_sales_summary pss
17    JOIN ccdw_dim_promotion p
18        ON p.promotion_id = pss.promotion_id
19    WHERE pss.submit_date >= ?
20        AND pss.submit_date <= ?
21    GROUP BY pss.submit_date, p.promotion_class
22)
23SELECT
24    t.submit_day,
25    t.total_orders,
26    p.promotion_class,
27    p.std_total_discount,
28    p.promotion_orders,
29    p.std_total_discount / p.promotion_orders AS avg_discount_per_order
30FROM TOTAL_ORDERS t
31LEFT JOIN PROMOTION_DISCOUNT p
32    ON t.submit_day = p.submit_day

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).

Search Analytics

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:

1WITH conversion AS (
2    SELECT
3        LOWER(sc.query) AS query,
4        SUM(sc.num_searches) AS converted_searches,
5        SUM(sc.num_orders) AS orders,
6        SUM(sc.std_revenue) AS std_revenue,
7        SUM(sc.std_revenue) / NULLIF(CAST(SUM(sc.num_orders) AS FLOAT), 0) AS std_revenue_per_order
8    FROM ccdw_aggr_search_conversion sc
9    JOIN ccdw_dim_site s
10        ON s.site_id = sc.site_id
11    WHERE sc.search_date >= ?
12        AND sc.search_date <= ?
13        AND s.nsite_id = ?
14        AND sc.has_results = ?
15    GROUP BY LOWER(sc.query)
16)
17SELECT
18    query,
19    converted_searches,
20    orders,
21    std_revenue,
22    std_revenue_per_order,
23    CASE WHEN converted_searches > 0
24         THEN (CAST(orders AS FLOAT) / converted_searches) * 100
25         ELSE 0
26    END AS conversion_rate
27FROM conversion
28ORDER BY std_revenue DESC

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.

Product Analytics

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

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

By Device Type (Mobile vs Desktop)
By Site (Multi-site performance)
By Channel (Online vs other channels)
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:

1SELECT
2    p.nproduct_id,
3    p.product_display_name,
4    SUM(pss.num_units) as units_sold,
5    SUM(pss.std_revenue) as std_revenue,
6    SUM(pss.num_orders) as order_count,
7    pss.device_class_code,
8    pss.registered,
9    s.nsite_id
10FROM ccdw_aggr_product_sales_summary pss
11JOIN ccdw_dim_product p ON p.product_id = pss.product_id
12JOIN ccdw_dim_site s ON s.site_id = pss.site_id
13WHERE pss.submit_date >= ? AND pss.submit_date <= ?
14    AND s.nsite_id = ?
15GROUP BY p.nproduct_id, p.product_display_name, pss.device_class_code, pss.registered, s.nsite_id
16ORDER BY std_revenue DESC

SQL Query - Product Co-Purchase Analysis:

1SELECT
2    p1.nproduct_id as product_1_id,
3    p1.product_display_name as product_1_name,
4    p2.nproduct_id as product_2_id,
5    p2.product_display_name as product_2_name,
6    pcb.frequency_count as co_purchase_count,
7    pcb.std_cobuy_revenue
8FROM ccdw_aggr_product_cobuy pcb
9JOIN ccdw_dim_product p1 ON p1.product_id = pcb.product_one_id
10JOIN ccdw_dim_product p2 ON p2.product_id = pcb.product_two_id
11JOIN ccdw_dim_site s ON s.nsite_id = pcb.nsite_id
12WHERE pcb.submit_date >= ? AND pcb.submit_date <= ?
13    AND s.nsite_id = ?
14ORDER BY pcb.frequency_count DESC

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.

Customer Analytics

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:

1SELECT
2    r.registration_date AS registration_dt,
3    SUM(r.num_registrations) AS new_registrations,
4    r.device_class_code,
5    s.nsite_id
6FROM ccdw_aggr_registration r
7JOIN ccdw_dim_site s
8    ON s.site_id = r.site_id
9WHERE r.registration_date >= ?
10    AND r.registration_date <= ?
11    AND s.nsite_id = ?
12GROUP BY
13    r.registration_date,
14    r.device_class_code,
15    s.nsite_id
16ORDER BY r.registration_date

SQL Query - Total Customer Counts:

1SELECT
2    cls.std_record_timestamp as snapshot_date,
3    SUM(cls.num_customers) as total_customers,
4    s.nsite_id
5FROM ccdw_fact_customer_list_snapshot cls
6JOIN ccdw_dim_site s ON s.site_id = cls.site_id
7WHERE cls.std_record_timestamp >= ? AND cls.std_record_timestamp <= ?
8    AND s.nsite_id = ?
9GROUP BY cls.std_record_timestamp, s.nsite_id
10ORDER BY cls.std_record_timestamp

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.

Visit & Traffic Analytics

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

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

Visit Volume - ccdw_aggr_visit
Visit Duration - ccdw_aggr_visit with duration calculations
Revenue per Visit - ccdw_aggr_visit conversion data
Traffic Sources - ccdw_aggr_visit_referrer
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:

1SELECT
2    v.visit_date AS visit_dt,
3    v.device_class_code,
4    SUM(v.num_visits) AS visits,
5    SUM(v.num_converted_visits) AS converted_visits,
6    SUM(v.visit_duration) AS total_duration,
7    SUM(v.std_revenue) AS std_revenue,
8    CASE WHEN SUM(v.num_visits) > 0
9         THEN SUM(v.std_revenue) / SUM(v.num_visits)
10         ELSE 0
11    END AS revenue_per_visit
12FROM ccdw_aggr_visit v
13JOIN ccdw_dim_site s
14    ON s.site_id = v.site_id
15WHERE v.visit_date >= ?
16    AND v.visit_date <= ?
17    AND s.nsite_id = ?
18GROUP BY
19    v.visit_date,
20    v.device_class_code
21ORDER BY
22    v.visit_date,
23    v.device_class_code

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.

Technical Performance Analytics

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:

1SELECT
2    o.request_date,
3    o.api_name,
4    o.api_resource,
5    SUM(o.num_requests) as total_requests,
6    SUM(o.response_time) as total_response_time,
7    CASE WHEN SUM(o.num_requests) > 0
8         THEN SUM(o.response_time) / SUM(o.num_requests)
9         ELSE 0 END as avg_response_time,
10    o.client_id
11FROM ccdw_aggr_ocapi_request o
12JOIN ccdw_dim_site s ON s.site_id = o.site_id
13WHERE o.request_date >= ? AND o.request_date <= ?
14    AND s.nsite_id = ?
15GROUP BY o.request_date, o.api_name, o.api_resource, o.client_id
16ORDER BY total_requests DESC

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.

Source Code & Campaign Analytics

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:

Campaign Activations - ccdw_aggr_source_code_activation
Campaign Orders - ccdw_aggr_source_code_sales
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.

1SELECT
2    s.nsite_id AS site,
3    g.nsource_code_group_id AS group_id,
4    CASE
5        WHEN sca.source_code_status = '0' THEN 'ACTIVE'
6        WHEN sca.source_code_status = '1' THEN 'INACTIVE'
7        WHEN sca.source_code_status = '2' THEN 'INVALID'
8        ELSE sca.source_code_status
9    END AS status,
10    SUM(sca.num_activations) AS activations
11FROM ccdw_aggr_source_code_activation sca
12JOIN ccdw_dim_site s
13    ON s.site_id = sca.site_id
14JOIN ccdw_dim_source_code_group g
15    ON g.source_code_group_id = sca.source_code_group_id
16WHERE sca.activation_date >= ?
17    AND sca.activation_date <= ?
18    AND s.nsite_id = ?
19    AND sca.device_class_code = ?
20    AND sca.registered = ?
21GROUP BY
22    s.nsite_id,
23    g.nsource_code_group_id,
24    sca.source_code_status
25ORDER BY
26    s.nsite_id ASC,
27    g.nsource_code_group_id ASC

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.

1SELECT
2    s.nsite_id AS site,
3    g.nsource_code_group_id AS group_id,
4    CASE
5        WHEN sco.source_code_status = '0' THEN 'ACTIVE'
6        WHEN sco.source_code_status = '1' THEN 'INACTIVE'
7        WHEN sco.source_code_status = '2' THEN 'INVALID'
8        ELSE sco.source_code_status
9    END AS status,
10    SUM(sco.num_orders) AS orders,
11    SUM(sco.num_units) AS units,
12    SUM(sco.std_revenue) AS std_revenue
13FROM ccdw_aggr_source_code_sales sco
14JOIN ccdw_dim_site s
15    ON s.site_id = sco.site_id
16JOIN ccdw_dim_source_code_group g
17    ON g.source_code_group_id = sco.source_code_group_id
18WHERE sco.submit_date >= ?
19    AND sco.submit_date <= ?
20    AND s.nsite_id = ?
21    AND sco.device_class_code = ?
22    AND sco.registered = TRUE
23GROUP BY
24    s.nsite_id,
25    g.nsource_code_group_id,
26    sco.source_code_status
27ORDER BY
28    s.nsite_id ASC,
29    g.nsource_code_group_id ASC

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.

Recommendation Analytics

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:

Recommendation Performance - ccdw_aggr_product_recommendation_recommender
Product-Level Details - ccdw_aggr_detail_product_recommendation_recommender
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.

1SELECT
2    s.nsite_id,
3    rpr.recommender_name,
4    SUM(rpr.num_recommender_views) AS recommender_views_count,
5    SUM(rpr.num_product_views) AS product_views_count,
6    SUM(rpr.num_clicks) AS clicks_count,
7    SUM(rpr.num_cart_adds) AS add_to_cart_count,
8    SUM(rpr.num_products_purchased) AS product_purchased_count,
9    SUM(rpr.num_orders) AS order_count,
10    SUM(rpr.std_attributed_revenue) AS std_attributed_revenue,
11    CASE SUM(rpr.num_recommender_views)
12        WHEN 0 THEN 0
13        ELSE CAST(SUM(rpr.num_clicks) AS FLOAT) / SUM(rpr.num_recommender_views)
14    END AS ctr,
15    CASE SUM(rpr.num_clicks)
16        WHEN 0 THEN 0
17        ELSE CAST(SUM(rpr.num_cart_adds) AS FLOAT) / SUM(rpr.num_clicks)
18    END AS atc_rate,
19    CASE SUM(rpr.num_cart_adds)
20        WHEN 0 THEN 0
21        ELSE CAST(SUM(rpr.num_products_purchased) AS FLOAT) / SUM(rpr.num_cart_adds)
22    END AS conversion_rate
23FROM (
24    SELECT
25        site_id, recommender_name, recommendation_date,
26        num_recommender_views, num_product_views, num_clicks,
27        num_cart_adds, num_products_purchased, num_orders, std_attributed_revenue
28    FROM ccdw_aggr_product_recommendation_recommender
29) rpr
30JOIN (
31    SELECT site_id, nsite_id
32    FROM ccdw_dim_site
33) s ON s.site_id = rpr.site_id
34WHERE rpr.recommendation_date >= DATE ?
35  AND rpr.recommendation_date <= DATE ?
36  AND s.nsite_id = ?
37  AND rpr.recommender_name = ?
38GROUP BY s.nsite_id, rpr.recommender_name
39ORDER BY std_attributed_revenue DESC

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