Revenue Field Guide and Calculation Logic for the B2C Commerce Intelligence JDBC Driver

Understanding revenue calculations is critical for accurateA Key Performance Indicator (KPI) creation and ensuring consistency across different dashboards and analytics tools. This section provides definitive guidance based on actual CCAC implementation, addressing common customer concerns about revenue discrepancies and metric consistency.

This topic addresses revenue calculation discrepancies between different analytics tools, the need for detailed metric descriptions and business context, and understanding how data models connect to dashboard implementations.

Revenue Field Selection

This section provides the essential rules for selecting revenue fields in your analytics queries.

Primary Rule: Use std_revenue for All Analytics

The system automatically handles currency complexity for you:

note: Any column with the prefix std refers to a monetary value that is converted to a common realm reporting currency.

• std_revenue = Always in your site's configured base currency
• Multi-currency transactions are automatically converted to std_revenue
• No complex business type decisions needed

Revenue Field Definitions

Critical: Revenue = Gross Merchandise Value

All revenue fields represent GROSS merchandise value (which includes certain discounts), not net revenue. This ensures consistent baselines across all analytics, matching the approach for Commerce reports and dashboards.

Revenue Calculation:

-- Line Item Level (Source of Truth)
gross_merchandise_value = (quantity × unit_price) -
                         ((promotion_discount + promo_discount_order_allocated) +
                          (manual_discount + manual_disc_order_allocated))

-- Aggregated Level (What you query)
std_revenue = SUM(std_li_gross_merchandise_value)

GMV includes all orders placed on the storefront, converted to common reporting currency, and adjusted for reporting timezone.

GMV isn't adjusted by returns, cancellations, etc.

The std_li_gross_merchandise_value column in ccdw_fact_line_item represents GMV at the line item level in standard reporting currency.

In aggregate tables (like ccdw_aggr_sales_summary or ccdw_aggr_product_sales_summary), the std_revenue column is an aggregate of the std_li_gross_merchandise_value column.

Standard Revenue Analytics

These examples demonstrate common revenue analysis patterns using the standardized revenue fields.

All Business Teams: Use std_revenue

Daily Revenue Trend:

SELECT
    submit_date,
    SUM(std_revenue) AS daily_revenue,
    SUM(num_orders) AS daily_orders,
    SUM(std_revenue) / SUM(num_orders) AS avg_order_value
FROM ccdw_aggr_sales_summary
WHERE submit_date BETWEEN ? AND ?
GROUP BY submit_date
ORDER BY submit_date;

Site Performance Comparison:

SELECT
    s.nsite_id,
    SUM(ss.std_revenue) AS site_revenue,
    SUM(ss.num_orders) AS site_orders,
    SUM(ss.std_revenue) / SUM(ss.num_orders) AS site_aov
FROM ccdw_aggr_sales_summary ss
JOIN ccdw_dim_site s ON s.site_id = ss.site_id
WHERE ss.submit_date BETWEEN ? AND ?
GROUP BY s.nsite_id
ORDER BY site_revenue DESC;

Product Performance Analysis:

SELECT
    p.product_display_name,
    SUM(pss.std_revenue) AS product_revenue,
    SUM(pss.num_units) AS units_sold,
    SUM(pss.std_revenue) / SUM(pss.num_units) AS revenue_per_unit
FROM ccdw_aggr_product_sales_summary pss
JOIN ccdw_dim_product p ON p.product_id = pss.product_id
WHERE pss.submit_date BETWEEN ? AND ?
GROUP BY p.product_display_name
HAVING SUM(pss.std_revenue) > 1000  -- Focus on significant revenue products
ORDER BY product_revenue DESC;

Promotion Revenue Analysis

This section shows how to analyze the impact of promotions on revenue using the standardized revenue calculations.

Example: Analyze promotion impact using revenue data:

-- Promotion revenue analysis using aggregated data
SELECT
    p.display_name AS promotion_name,
    SUM(pss.std_revenue) AS promoted_revenue,
    SUM(pss.std_total_discount) AS discount_given,
    SUM(pss.num_orders) AS orders_with_promotion,
    ROUND(SUM(pss.std_total_discount) / SUM(pss.std_revenue) * 100, 2) AS discount_percentage
FROM ccdw_aggr_promotion_sales_summary pss
JOIN ccdw_dim_promotion p ON p.promotion_id = pss.promotion_id
WHERE pss.submit_date BETWEEN ? AND ?
GROUP BY p.display_name
ORDER BY promoted_revenue DESC;