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:

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

Note

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

Field Name	Data Type	Definition	When to Use
`std_revenue`	NUMERIC	Gross Merchandise Value (GMV) in the realm’s reporting currency (auto-converted)	All KPIs, reporting, and analytics

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:

1-- Line Item Level (Source of Truth)
2gross_merchandise_value = (quantity × unit_price) -
3                         ((promotion_discount + promo_discount_order_allocated) +
4                          (manual_discount + manual_disc_order_allocated))
5
6-- Aggregated Level (What you query)
7std_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:

1SELECT
2    submit_date,
3    SUM(std_revenue) AS daily_revenue,
4    SUM(num_orders) AS daily_orders,
5    SUM(std_revenue) / SUM(num_orders) AS avg_order_value
6FROM ccdw_aggr_sales_summary
7WHERE submit_date BETWEEN ? AND ?
8GROUP BY submit_date
9ORDER BY submit_date;

Site Performance Comparison:

1SELECT
2    s.nsite_id,
3    SUM(ss.std_revenue) AS site_revenue,
4    SUM(ss.num_orders) AS site_orders,
5    SUM(ss.std_revenue) / SUM(ss.num_orders) AS site_aov
6FROM ccdw_aggr_sales_summary ss
7JOIN ccdw_dim_site s ON s.site_id = ss.site_id
8WHERE ss.submit_date BETWEEN ? AND ?
9GROUP BY s.nsite_id
10ORDER BY site_revenue DESC;

Product Performance Analysis:

1SELECT
2    p.product_display_name,
3    SUM(pss.std_revenue) AS product_revenue,
4    SUM(pss.num_units) AS units_sold,
5    SUM(pss.std_revenue) / SUM(pss.num_units) AS revenue_per_unit
6FROM ccdw_aggr_product_sales_summary pss
7JOIN ccdw_dim_product p ON p.product_id = pss.product_id
8WHERE pss.submit_date BETWEEN ? AND ?
9GROUP BY p.product_display_name
10HAVING SUM(pss.std_revenue) > 1000  -- Focus on significant revenue products
11ORDER 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:

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