---
schema_version: "1"
id: 0a0fb1f2919156d91370458f7ee89d9cb6633a3a8afd21012cece7b011bc9ae4
title: Business Use Cases for the B2C Commerce Intelligence JDBC Driver
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/commerce-cloud/b2c-commerce/guides/jdbc/jdbc_use_cases.md
canonical_url: https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_use_cases.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 36a9487a522142cce22e83d6d3ecbb4036b17d1d1c194a1620669ec899a7b346
products:
  - Composable Storefront
  - Salesforce APIs
---
# 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](https://calcite.apache.org/docs/reference.html) 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](#sales-analytics)                                  | How is my business performing across revenue and orders?                            | `ccdw_aggr_sales_summary`                    | Merchandising                  |
| [Payment Method Analytics](#payment-method-analytics)                | Which payment methods are customers using and how do they perform?                  | `ccdw_aggr_payment_sales_summary`            | Operations                     |
| [Traffic Source Analytics](#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](#customer-registration-analytics)  | How effectively are we acquiring new customers and what drives registrations?       | `ccdw_aggr_registration`                     | Marketing, CRM                 |
| [Inventory Analytics](#inventory-analytics)                          | Do we have the right products available where customers want them?                  | `ccdw_fact_inventory_record_snapshot_hourly` | Operations, Supply Chain       |
| [Promotion Analytics](#promotion-analytics)                          | Are my promotions driving incremental sales or just discounting existing sales?     | `ccdw_aggr_promotion_sales_summary`          | Marketing, Merchandising       |
| [Search Analytics](#search-analytics)                                | Which search terms drive revenue vs which represent missed opportunities?           | `ccdw_aggr_search_conversion`                | Merchandising, UX              |
| [Product Analytics](#product-analytics)                              | How do my products perform across different channels?                               | `ccdw_aggr_product_sales_summary`            | Buyers, Merchandising          |
| [Customer Analytics](#customer-analytics)                            | How are we acquiring and retaining customers?                                       | `ccdw_dim_customer`                          | Marketing, CRM                 |
| [Visit & Traffic Analytics](#visit--traffic-analytics)               | What's the complete picture of site performance from traffic to revenue?            | `ccdw_aggr_visit`                            | Marketing, UX                  |
| [Technical Performance Analytics](#technical-performance-analytics)  | Is our system performing well enough to support customer experience?                | `ccdw_aggr_ocapi_request`                    | Operations, Engineering        |
| [Source Code & Campaign Analytics](#source-code--campaign-analytics) | Which marketing campaigns are driving actual sales vs just traffic?                 | `ccdw_aggr_source_code_activation`           | Marketing, Campaign Management |
| [Recommendation Analytics](#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`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_sales_summary.md) - Core sales metrics aggregation
- [`ccdw_dim_site`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_site.md) - Site dimension for multi-site analysis
- [`ccdw_dim_date`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_date.md) - 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**:

```sql
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`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_payment_sales_summary.md) - Payment method performance metrics
- [`ccdw_fact_order_payments`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_fact_order_payments.md) - Individual payment transactions
- [`ccdw_dim_payment_method`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_payment_method.md) - 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
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**:

```sql
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`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit_referrer.md) - Traffic source attribution
- [`ccdw_dim_traffic_source`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_traffic_source.md) - External traffic source dimension for order attribution
- [`ccdw_fact_line_item`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_fact_line_item.md) - Order line items, including external-traffic columns
- [`ccdw_aggr_visit`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit.md) - Visit performance metrics
- [`ccdw_dim_site`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_site.md) - 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
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**:

```sql
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`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_registration.md) - Customer acquisition tracking
- [`ccdw_fact_customer_registration`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_fact_customer_registration.md) - Individual registration events
- [`ccdw_fact_customer_list_snapshot`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_fact_customer_list_snapshot.md) - Customer counts over time
- [`ccdw_dim_customer`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_customer.md) - 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
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**:

```sql
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`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_fact_inventory_record_snapshot_hourly.md) - Real-time inventory levels
- [`ccdw_aggr_inventory_by_location`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_inventory_by_location.md) - Location-specific inventory
- [`ccdw_aggr_inventory_by_location_group`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_inventory_by_location_group.md) - Location group aggregations
- [`ccdw_dim_location_group`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_location_group.md) - Location hierarchy
- [`ccdw_dim_product`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_product.md) - Product details

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

<!-- **SQL Query - Current Inventory Levels by Location Group**:

```sql
SELECT
    lg.nlocation_group_id,
    p.nsku_id,
    p.nproduct_id,
    SUM(ibl.available_to_fulfill) AS available_to_fulfill,
    SUM(ibl.available_to_order) AS available_to_order,
    SUM(ibl.on_hand) AS on_hand,
    SUM(ibl.reserved) AS reserved
FROM ccdw_aggr_inventory_by_location_group ibl
JOIN ccdw_dim_location_group lg ON lg.location_group_id = ibl.location_group_id
JOIN ccdw_dim_product p ON p.sku_id = ibl.sku_id
WHERE ibl.record_date = CURRENT_DATE
    AND lg.nlocation_group_id = ?
GROUP BY lg.nlocation_group_id, p.nsku_id, p.nproduct_id
ORDER BY available_to_fulfill DESC
LIMIT 100
```-->

**SQL Query - Inventory Trends Over Time for Specific SKU**:

```sql
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:

1. **Promotion Performance** - [`ccdw_aggr_promotion_sales_summary`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_promotion_sales_summary.md)
2. **Overall Visit Data** - [`ccdw_aggr_visit`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit.md)
3. **Overall Sales Data** - [`ccdw_aggr_sales_summary`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_sales_summary.md)

**Primary Schema Tables**:

- [`ccdw_aggr_promotion_sales_summary`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_promotion_sales_summary.md) - Promotion-specific sales data
- [`ccdw_dim_promotion`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_promotion.md) - Promotion details and classification
- [`ccdw_dim_campaign`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_campaign.md) - Campaign information
- [`ccdw_dim_coupon`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_coupon.md) - Coupon details

**SQL Query - Total Discount Analysis**:

```sql
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`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_search_conversion.md) - Search-to-purchase conversion data
- [`ccdw_aggr_search_query`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_search_query.md) - Search behavior and results
- [`ccdw_aggr_search`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_search.md) - Search volume metrics

**SQL Query - Search Query Performance**:

```sql
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:

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`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_product_sales_summary.md) - Product sales by dimensions
- [`ccdw_fact_line_item`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_fact_line_item.md) - Individual purchase transactions
- [`ccdw_dim_product`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_product.md) - Product master data
- [`ccdw_aggr_product_cobuy`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_product_cobuy.md) - Cross-sell analysis

**SQL Query - Top Selling Products**:

```sql
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**:

```sql
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`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_registration.md) - Customer acquisition tracking
- [`ccdw_fact_customer_registration`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_fact_customer_registration.md) - Individual registration events
- [`ccdw_fact_customer_list_snapshot`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_fact_customer_list_snapshot.md) - Customer counts over time
- [`ccdw_dim_customer`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_customer.md) - Customer master data

**SQL Query - Customer Registration Trends**:

```sql
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**:

```sql
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:

1. Visit Volume - [`ccdw_aggr_visit`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit.md)
2. Visit Duration - [`ccdw_aggr_visit`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit.md) with duration calculations
3. Revenue per Visit - [`ccdw_aggr_visit`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit.md) conversion data
4. Traffic Sources - [`ccdw_aggr_visit_referrer`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit_referrer.md)
5. Device Analysis - [`ccdw_aggr_visit_user_agent`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit_user_agent.md)

**Primary Schema Tables**:

- [`ccdw_aggr_visit`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit.md) - Core visit metrics and conversion
- [`ccdw_aggr_visit_referrer`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit_referrer.md) - Traffic source attribution
- [`ccdw_aggr_visit_user_agent`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit_user_agent.md) - Device and browser analysis
- [`ccdw_aggr_visit_checkout`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_visit_checkout.md) - Checkout funnel analysis

**SQL Query - Visit Metrics by Device**:

```sql
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`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_ocapi_request.md) → Average response times
- **SCAPI Cache**: [`ccdw_aggr_scapi_request`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_scapi_request.md) → Cache hit percentages
- **Controller Performance**: [`ccdw_aggr_controller_request`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_controller_request.md) → Performance buckets

**Primary Schema Tables**:

- [`ccdw_aggr_ocapi_request`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_ocapi_request.md) - Open Commerce API performance
- [`ccdw_aggr_scapi_request`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_scapi_request.md) - Storefront API performance
- [`ccdw_aggr_controller_request`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_controller_request.md) - Controller performance
- [`ccdw_aggr_include_controller_request`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_include_controller_request.md) - Include controller metrics

**SQL Query - OCAPI Performance**:

```sql
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:

1. **Campaign Activations** - [`ccdw_aggr_source_code_activation`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_source_code_activation.md)
2. **Campaign Orders** - [`ccdw_aggr_source_code_sales`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_source_code_sales.md)
3. **Campaign Organization** - [`ccdw_dim_source_code_group`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_source_code_group.md)

**Primary Schema Tables**:

- [`ccdw_aggr_source_code_activation`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_source_code_activation.md) - Campaign activation tracking
- [`ccdw_aggr_source_code_sales`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_source_code_sales.md) - Sales attribution to campaigns
- [`ccdw_fact_source_codes_activation`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_fact_source_codes_activation.md) - Individual activation events
- [`ccdw_dim_source_code_group`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_dim_source_code_group.md) - Campaign organization

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

```sql
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._

```sql
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:

1. **Recommendation Performance** - [`ccdw_aggr_product_recommendation_recommender`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_product_recommendation_recommender.md)
2. **Product-Level Details** - [`ccdw_aggr_detail_product_recommendation_recommender`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_detail_product_recommendation_recommender.md)
3. **Cross-Algorithm Comparison** - Multiple recommender performance analysis

**Primary Schema Tables**:

- [`ccdw_aggr_product_recommendation`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_product_recommendation.md) - Overall recommendation performance
- [`ccdw_aggr_product_recommendation_recommender`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_product_recommendation_recommender.md) - Performance by recommender type
- [`ccdw_aggr_detail_product_recommendation_recommender`](https://developer.salesforce.com/docs/commerce/pwa-kit-managed-runtime/guide/jdbc_ccdw_aggr_detail_product_recommendation_recommender.md) - 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._

```sql
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

<!-- **SQL Query - Top Products by Recommendation Algorithm**:
_This query identifies which specific products perform best with each recommendation algorithm, enabling optimization of product placement and merchandising strategy for recommendation widgets._

```sql
SELECT
    s.nsite_id AS site,
    dpr.recommender_name,
    CASE p.product_id
        WHEN 0 THEN 'Unknown'
        ELSE p.nproduct_id
    END as nproduct_id,
    CASE p.product_id
        WHEN 0 THEN 'Unknown'
        ELSE p.product_display_name
    END as product_display_name,
    SUM(dpr.num_recommender_views) as recommender_views_count,
    SUM(dpr.num_product_views) as product_views_count,
    SUM(dpr.num_clicks) as clicks_count,
    SUM(dpr.num_cart_adds) as add_to_cart_count,
    SUM(dpr.num_products_purchased) as product_purchased_count,
    SUM(dpr.num_orders) as order_count,
    SUM(dpr.std_attributed_revenue) as std_attributed_revenue,
    CASE SUM(dpr.num_recommender_views)
        WHEN 0 THEN 0
        ELSE CAST(SUM(dpr.num_clicks) AS FLOAT) / SUM(dpr.num_recommender_views)
    END as ctr,
    CASE SUM(dpr.num_clicks)
        WHEN 0 THEN 0
        ELSE CAST(SUM(dpr.num_cart_adds) AS FLOAT) / SUM(dpr.num_clicks)
    END as atc_rate,
    CASE SUM(dpr.num_cart_adds)
        WHEN 0 THEN 0
        ELSE CAST(SUM(dpr.num_products_purchased) AS FLOAT) / SUM(dpr.num_cart_adds)
    END as conversion_rate
FROM ccdw_aggr_detail_product_recommendation_recommender dpr
JOIN ccdw_dim_product p ON p.product_id = dpr.product_id
JOIN ccdw_dim_site s ON s.site_id = dpr.site_id
WHERE dpr.recommendation_date >= ? AND dpr.recommendation_date <= ?
    AND s.nsite_id = ?
    AND dpr.recommender_name = ?
GROUP BY s.nsite_id, dpr.recommender_name, p.nproduct_id, p.product_id, p.product_display_name
ORDER BY std_attributed_revenue DESC
LIMIT 100
```

#### Query Details

- **Business Purpose**: Identifies top-performing products within specific recommendation algorithms for merchandising optimization
- **Product-Level Analysis**: Breaks down recommendation performance by individual product rather than algorithm aggregate
- **Cross-sell Optimization**: Shows which products work best as recommendations vs. primary purchases
- **Unknown Product Handling**: Gracefully handles cases where product_id = 0 (deleted or missing products)
- **Performance Ranking**: Orders results by attributed revenue to identify highest-value recommended products
- **Top 100 Limit**: Focuses on most significant products to avoid overwhelming results
- **Merchandising Insights**:
  - Which products should be featured more prominently in recommendation widgets?
  - Which products have high click rates but low conversion (need better targeting)?
  - Which products drive the most incremental revenue through recommendations?
- **Algorithm-Specific Results**: Filter by `recommender_name` to compare product performance across different engines

**Tip**: If you surface this in a UI, consider calculating click-through rates (CTR), add-to-cart rates, and conversion rates to measure recommendation algorithm effectiveness. Compare different recommender algorithms side-by-side and track revenue attribution to optimize personalization strategy.

**Business Value**: Merchandising teams optimize recommendation algorithms, identify best-performing product combinations, improve cross-sell/up-sell effectiveness, and increase average order value through data-driven personalization decisions. -->