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:

SELECT
    ss.submit_date AS "date",
    SUM(std_revenue) AS std_revenue,
    SUM(num_orders) AS orders,
    SUM(std_revenue) / SUM(num_orders) AS std_aov,
    SUM(num_units) AS units,
    SUM(num_units) / SUM(num_orders) AS aos,
    SUM(std_tax) AS std_tax,
    SUM(std_shipping) AS std_shipping
FROM ccdw_aggr_sales_summary ss
JOIN ccdw_dim_site s
    ON s.site_id = ss.site_id
WHERE ss.submit_date >= ?
    AND ss.submit_date <= ?
    AND s.nsite_id = ?
GROUP BY ss.submit_date
ORDER 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:

SELECT
    pm.display_name AS payment_method,
    SUM(pss.num_payments) AS total_payments,
    SUM(pss.num_orders) AS orders_with_payment,
    SUM(pss.std_captured_amount) AS std_captured_amount,
    SUM(pss.std_refunded_amount) AS std_refunded_amount,
    SUM(pss.std_transaction_amount) AS std_transaction_amount,
    (SUM(pss.std_captured_amount) / SUM(pss.num_payments)) AS avg_payment_amount
FROM ccdw_aggr_payment_sales_summary pss
JOIN ccdw_dim_payment_method pm ON pm.payment_method_id = pss.payment_method_id
JOIN ccdw_dim_site s ON s.site_id = pss.site_id
WHERE pss.submit_date >= ? AND pss.submit_date <= ?
    AND s.nsite_id = ?
GROUP BY pm.display_name
ORDER BY std_captured_amount DESC

SQL Query - Gift Certificate Analysis:

SELECT
    pss.submit_date AS "date",
    s.nsite_id AS site,
    SUM(pss.num_orders) AS redemption_count,
    SUM(pss.std_transaction_amount) AS std_redemption_value
FROM ccdw_aggr_payment_sales_summary pss
JOIN ccdw_dim_site s
    ON s.site_id = pss.site_id
JOIN ccdw_dim_payment_method pm
    ON pm.payment_method_id = pss.payment_method_id
WHERE pss.submit_date >= ?
    AND pss.submit_date <= ?
    AND s.nsite_id = ?
    AND pm.npayment_method_id = 'GIFT_CERTIFICATE'
GROUP BY
    pss.submit_date,
    s.nsite_id
ORDER BY
    pss.submit_date ASC,
    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_dim_traffic_source - External traffic source dimension for order attribution
ccdw_fact_line_item - Order line items, including external-traffic columns
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:

WITH total AS (
    SELECT SUM(num_visits) AS total_visits
    FROM ccdw_aggr_visit_referrer
    WHERE visit_date >= ?
        AND visit_date <= ?
)
SELECT
    vr.referrer_medium AS traffic_medium,
    vr.referrer_source AS traffic_source,
    SUM(vr.num_visits) AS total_visits,
    SUM(vr.num_visits) * 100.0 / total.total_visits AS visit_percentage
FROM ccdw_aggr_visit_referrer vr
JOIN ccdw_dim_site s
    ON s.site_id = vr.site_id
JOIN total ON TRUE
WHERE vr.visit_date >= ?
    AND vr.visit_date <= ?
    AND s.nsite_id = ?
GROUP BY
    vr.referrer_medium,
    vr.referrer_source,
    total.total_visits
ORDER BY total_visits DESC
LIMIT 20

SQL Query - Traffic Source Conversion Analysis:

SELECT
    vr.referrer_medium AS traffic_medium,
    SUM(v.num_visits) AS total_visits,
    SUM(v.num_converted_visits) AS converted_visits,
    SUM(v.std_revenue) AS std_revenue,
    CASE WHEN SUM(v.num_visits) > 0
         THEN (CAST(SUM(v.num_converted_visits) AS FLOAT) / SUM(v.num_visits)) * 100
         ELSE 0
    END AS conversion_rate,
    CASE WHEN SUM(v.num_visits) > 0
         THEN SUM(v.std_revenue) / SUM(v.num_visits)
         ELSE 0
    END AS revenue_per_visit
FROM ccdw_aggr_visit_referrer vr
JOIN ccdw_aggr_visit v
    ON v.visit_date = vr.visit_date
    AND v.site_id = vr.site_id
    AND v.device_class_code = vr.device_class_code
JOIN ccdw_dim_site s
    ON s.site_id = vr.site_id
WHERE vr.visit_date >= ?
    AND vr.visit_date <= ?
    AND s.nsite_id = ?
GROUP BY vr.referrer_medium
ORDER 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:

SELECT
    r.registration_date AS "date",
    SUM(r.num_registrations) AS new_registrations,
    r.device_class_code,
    s.nsite_id
FROM ccdw_aggr_registration r
JOIN ccdw_dim_site s ON s.site_id = r.site_id
WHERE r.registration_date >= ? AND r.registration_date <= ?
    AND s.nsite_id = ?
GROUP BY r.registration_date, r.device_class_code, s.nsite_id
ORDER BY r.registration_date

SQL Query - Total Customer Growth:

WITH customer_snapshots AS (
    SELECT
        cls.site_id,
        cls.ncustomer_list_id,
        CAST(cls.utc_record_timestamp AS DATE) AS snapshot_date,
        cls.utc_record_timestamp,
        cls.num_customers,
        ROW_NUMBER() OVER (
            PARTITION BY cls.site_id, CAST(cls.utc_record_timestamp AS DATE)
            ORDER BY cls.utc_record_timestamp DESC
        ) AS rn
    FROM ccdw_fact_customer_list_snapshot cls
    JOIN ccdw_dim_site s
        ON cls.site_id = s.site_id
    WHERE CAST(cls.utc_record_timestamp AS DATE) >= ?
        AND CAST(cls.utc_record_timestamp AS DATE) <= ?
        AND s.nsite_id = ?
),
unique_lists AS (
    SELECT
        ncustomer_list_id,
        snapshot_date,
        num_customers,
        ROW_NUMBER() OVER (
            PARTITION BY ncustomer_list_id, snapshot_date
            ORDER BY utc_record_timestamp DESC
        ) AS rn
    FROM customer_snapshots
    WHERE rn = 1
)
SELECT
    snapshot_date,
    SUM(num_customers) AS total_customers
FROM unique_lists
WHERE rn = 1
GROUP BY snapshot_date
ORDER 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:

SSELECT
    ih.utc_record_timestamp AS "timestamp",
    p.nsku_id,
    p.nproduct_id,
    SUM(ih.available_to_fulfill) AS available_to_fulfill,
    SUM(ih.available_to_order) AS available_to_order,
    SUM(ih.on_hand) AS on_hand,
    SUM(ih.reserved) AS reserved
FROM
    (SELECT utc_record_timestamp, sku_id, location_group_id,
            available_to_fulfill, available_to_order, on_hand, reserved
     FROM ccdw_fact_inventory_record_snapshot_hourly) ih
JOIN
    (SELECT sku_id, nsku_id, nproduct_id FROM ccdw_dim_product) p
    ON p.sku_id = ih.sku_id
JOIN
    (SELECT location_group_id, nlocation_group_id FROM ccdw_dim_location_group) lg
    ON lg.location_group_id = ih.location_group_id
WHERE
    ih.utc_record_timestamp >= ?
    AND ih.utc_record_timestamp <= ?
    AND p.nsku_id = ?
    AND lg.nlocation_group_id = ?
GROUP BY
    ih.utc_record_timestamp, p.nsku_id, p.nproduct_id
ORDER BY
    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:

WITH TOTAL_ORDERS AS (
    SELECT
        ss.submit_date AS submit_day,
        SUM(num_orders) AS total_orders
    FROM ccdw_aggr_sales_summary ss
    WHERE ss.submit_date >= ?
        AND ss.submit_date <= ?
    GROUP BY ss.submit_date
),
PROMOTION_DISCOUNT AS (
    SELECT
        pss.submit_date AS submit_day,
        p.promotion_class AS promotion_class,
        SUM(std_total_discount) AS std_total_discount,
        SUM(num_orders) AS promotion_orders
    FROM ccdw_aggr_promotion_sales_summary pss
    JOIN ccdw_dim_promotion p
        ON p.promotion_id = pss.promotion_id
    WHERE pss.submit_date >= ?
        AND pss.submit_date <= ?
    GROUP BY pss.submit_date, p.promotion_class
)
SELECT
    t.submit_day,
    t.total_orders,
    p.promotion_class,
    p.std_total_discount,
    p.promotion_orders,
    p.std_total_discount / p.promotion_orders AS avg_discount_per_order
FROM TOTAL_ORDERS t
LEFT JOIN PROMOTION_DISCOUNT p
    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:

WITH conversion AS (
    SELECT
        LOWER(sc.query) AS query,
        SUM(sc.num_searches) AS converted_searches,
        SUM(sc.num_orders) AS orders,
        SUM(sc.std_revenue) AS std_revenue,
        SUM(sc.std_revenue) / NULLIF(CAST(SUM(sc.num_orders) AS FLOAT), 0) AS std_revenue_per_order
    FROM ccdw_aggr_search_conversion sc
    JOIN ccdw_dim_site s
        ON s.site_id = sc.site_id
    WHERE sc.search_date >= ?
        AND sc.search_date <= ?
        AND s.nsite_id = ?
        AND sc.has_results = ?
    GROUP BY LOWER(sc.query)
)
SELECT
    query,
    converted_searches,
    orders,
    std_revenue,
    std_revenue_per_order,
    CASE WHEN converted_searches > 0
         THEN (CAST(orders AS FLOAT) / converted_searches) * 100
         ELSE 0
    END AS conversion_rate
FROM conversion
ORDER 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:

SELECT
    p.nproduct_id,
    p.product_display_name,
    SUM(pss.num_units) as units_sold,
    SUM(pss.std_revenue) as std_revenue,
    SUM(pss.num_orders) as order_count,
    pss.device_class_code,
    pss.registered,
    s.nsite_id
FROM ccdw_aggr_product_sales_summary pss
JOIN ccdw_dim_product p ON p.product_id = pss.product_id
JOIN ccdw_dim_site s ON s.site_id = pss.site_id
WHERE pss.submit_date >= ? AND pss.submit_date <= ?
    AND s.nsite_id = ?
GROUP BY p.nproduct_id, p.product_display_name, pss.device_class_code, pss.registered, s.nsite_id
ORDER BY std_revenue DESC

SQL Query - Product Co-Purchase Analysis:

SELECT
    p1.nproduct_id as product_1_id,
    p1.product_display_name as product_1_name,
    p2.nproduct_id as product_2_id,
    p2.product_display_name as product_2_name,
    pcb.frequency_count as co_purchase_count,
    pcb.std_cobuy_revenue
FROM ccdw_aggr_product_cobuy pcb
JOIN ccdw_dim_product p1 ON p1.product_id = pcb.product_one_id
JOIN ccdw_dim_product p2 ON p2.product_id = pcb.product_two_id
JOIN ccdw_dim_site s ON s.nsite_id = pcb.nsite_id
WHERE pcb.submit_date >= ? AND pcb.submit_date <= ?
    AND s.nsite_id = ?
ORDER 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:

SELECT
    r.registration_date AS registration_dt,
    SUM(r.num_registrations) AS new_registrations,
    r.device_class_code,
    s.nsite_id
FROM ccdw_aggr_registration r
JOIN ccdw_dim_site s
    ON s.site_id = r.site_id
WHERE r.registration_date >= ?
    AND r.registration_date <= ?
    AND s.nsite_id = ?
GROUP BY
    r.registration_date,
    r.device_class_code,
    s.nsite_id
ORDER BY r.registration_date

SQL Query - Total Customer Counts:

SELECT
    cls.std_record_timestamp as snapshot_date,
    SUM(cls.num_customers) as total_customers,
    s.nsite_id
FROM ccdw_fact_customer_list_snapshot cls
JOIN ccdw_dim_site s ON s.site_id = cls.site_id
WHERE cls.std_record_timestamp >= ? AND cls.std_record_timestamp <= ?
    AND s.nsite_id = ?
GROUP BY cls.std_record_timestamp, s.nsite_id
ORDER 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:

SELECT
    v.visit_date AS visit_dt,
    v.device_class_code,
    SUM(v.num_visits) AS visits,
    SUM(v.num_converted_visits) AS converted_visits,
    SUM(v.visit_duration) AS total_duration,
    SUM(v.std_revenue) AS std_revenue,
    CASE WHEN SUM(v.num_visits) > 0
         THEN SUM(v.std_revenue) / SUM(v.num_visits)
         ELSE 0
    END AS revenue_per_visit
FROM ccdw_aggr_visit v
JOIN ccdw_dim_site s
    ON s.site_id = v.site_id
WHERE v.visit_date >= ?
    AND v.visit_date <= ?
    AND s.nsite_id = ?
GROUP BY
    v.visit_date,
    v.device_class_code
ORDER BY
    v.visit_date,
    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:

SELECT
    o.request_date,
    o.api_name,
    o.api_resource,
    SUM(o.num_requests) as total_requests,
    SUM(o.response_time) as total_response_time,
    CASE WHEN SUM(o.num_requests) > 0
         THEN SUM(o.response_time) / SUM(o.num_requests)
         ELSE 0 END as avg_response_time,
    o.client_id
FROM ccdw_aggr_ocapi_request o
JOIN ccdw_dim_site s ON s.site_id = o.site_id
WHERE o.request_date >= ? AND o.request_date <= ?
    AND s.nsite_id = ?
GROUP BY o.request_date, o.api_name, o.api_resource, o.client_id
ORDER 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.

SELECT
    s.nsite_id AS site,
    g.nsource_code_group_id AS group_id,
    CASE
        WHEN sca.source_code_status = '0' THEN 'ACTIVE'
        WHEN sca.source_code_status = '1' THEN 'INACTIVE'
        WHEN sca.source_code_status = '2' THEN 'INVALID'
        ELSE sca.source_code_status
    END AS status,
    SUM(sca.num_activations) AS activations
FROM ccdw_aggr_source_code_activation sca
JOIN ccdw_dim_site s
    ON s.site_id = sca.site_id
JOIN ccdw_dim_source_code_group g
    ON g.source_code_group_id = sca.source_code_group_id
WHERE sca.activation_date >= ?
    AND sca.activation_date <= ?
    AND s.nsite_id = ?
    AND sca.device_class_code = ?
    AND sca.registered = ?
GROUP BY
    s.nsite_id,
    g.nsource_code_group_id,
    sca.source_code_status
ORDER BY
    s.nsite_id ASC,
    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.

SELECT
    s.nsite_id AS site,
    g.nsource_code_group_id AS group_id,
    CASE
        WHEN sco.source_code_status = '0' THEN 'ACTIVE'
        WHEN sco.source_code_status = '1' THEN 'INACTIVE'
        WHEN sco.source_code_status = '2' THEN 'INVALID'
        ELSE sco.source_code_status
    END AS status,
    SUM(sco.num_orders) AS orders,
    SUM(sco.num_units) AS units,
    SUM(sco.std_revenue) AS std_revenue
FROM ccdw_aggr_source_code_sales sco
JOIN ccdw_dim_site s
    ON s.site_id = sco.site_id
JOIN ccdw_dim_source_code_group g
    ON g.source_code_group_id = sco.source_code_group_id
WHERE sco.submit_date >= ?
    AND sco.submit_date <= ?
    AND s.nsite_id = ?
    AND sco.device_class_code = ?
    AND sco.registered = TRUE
GROUP BY
    s.nsite_id,
    g.nsource_code_group_id,
    sco.source_code_status
ORDER BY
    s.nsite_id ASC,
    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.

SELECT
    s.nsite_id,
    rpr.recommender_name,
    SUM(rpr.num_recommender_views) AS recommender_views_count,
    SUM(rpr.num_product_views) AS product_views_count,
    SUM(rpr.num_clicks) AS clicks_count,
    SUM(rpr.num_cart_adds) AS add_to_cart_count,
    SUM(rpr.num_products_purchased) AS product_purchased_count,
    SUM(rpr.num_orders) AS order_count,
    SUM(rpr.std_attributed_revenue) AS std_attributed_revenue,
    CASE SUM(rpr.num_recommender_views)
        WHEN 0 THEN 0
        ELSE CAST(SUM(rpr.num_clicks) AS FLOAT) / SUM(rpr.num_recommender_views)
    END AS ctr,
    CASE SUM(rpr.num_clicks)
        WHEN 0 THEN 0
        ELSE CAST(SUM(rpr.num_cart_adds) AS FLOAT) / SUM(rpr.num_clicks)
    END AS atc_rate,
    CASE SUM(rpr.num_cart_adds)
        WHEN 0 THEN 0
        ELSE CAST(SUM(rpr.num_products_purchased) AS FLOAT) / SUM(rpr.num_cart_adds)
    END AS conversion_rate
FROM (
    SELECT
        site_id, recommender_name, recommendation_date,
        num_recommender_views, num_product_views, num_clicks,
        num_cart_adds, num_products_purchased, num_orders, std_attributed_revenue
    FROM ccdw_aggr_product_recommendation_recommender
) rpr
JOIN (
    SELECT site_id, nsite_id
    FROM ccdw_dim_site
) s ON s.site_id = rpr.site_id
WHERE rpr.recommendation_date >= DATE ?
  AND rpr.recommendation_date <= DATE ?
  AND s.nsite_id = ?
  AND rpr.recommender_name = ?
GROUP BY s.nsite_id, rpr.recommender_name
ORDER 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