Looker Studio supports 64+ functions for calculated fields (Google Cloud, 2025)—but most tutorials show basic arithmetic while ignoring the ecommerce metrics WooCommerce stores actually need. Profit margin, ROAS, customer acquisition cost, average order value by channel. These require specific formulas using your actual order data, not generic GA4 sessions.

Here are the copy-paste formulas for WooCommerce metrics in Looker Studio.

Two Types of Calculated Fields#

Before diving into formulas, understand where to create them:

Data Source Calculated Fields

Data source calculated fields can be reused across multiple reports using the same data source (Porter Metrics, 2024). Create these for metrics you will use repeatedly—profit margin, AOV, conversion rate. They are available to anyone with access to that data source.

To create: Open your data source → Add a Field → Enter formula

Chart-Specific Calculated Fields

Chart-specific calculated fields can operate on blended data sources (Porter Metrics, 2024). Use these when combining data from multiple sources—like calculating ROAS using BigQuery revenue and Google Ads spend data.

To create: Select chart → Add Metric → Create Field

You may be interested in: Looker Studio and BigQuery: The Free Dashboard Stack WordPress Stores Are Missing

Essential WooCommerce Calculated Fields#

1. Profit Margin

The metric that matters most—but requires cost data in your BigQuery tables.

(SUM(revenue) - SUM(product_cost)) / SUM(revenue)

Format as percentage. If your data includes line-item costs:

SUM(line_item_total - line_item_cost) / SUM(line_item_total)

Note: This only works if your WooCommerce data pipeline includes product cost. GA4 does not capture cost data—you need server-side tracking that pulls from WooCommerce order meta.

2. Average Order Value (AOV)

Simple but essential:

SUM(order_total) / COUNT_DISTINCT(order_id)

For AOV by traffic source or campaign, use this as a chart-level metric with appropriate dimensions.

3. Return on Ad Spend (ROAS)

Requires blending your revenue data with ad platform spend:

SUM(revenue) / SUM(ad_spend)

This works when you blend BigQuery order data with Google Ads or Facebook Ads data. The result shows dollars returned per dollar spent.

For percentage format:

(SUM(revenue) / SUM(ad_spend)) * 100

4. Customer Acquisition Cost (CAC)

Total marketing spend divided by new customers acquired:

SUM(marketing_spend) / COUNT_DISTINCT(CASE WHEN customer_type = "new" THEN customer_id END)

Requires your order data to flag new vs. returning customers. WooCommerce stores this; make sure your data pipeline preserves it.

5. Conversion Rate

Orders divided by sessions:

COUNT_DISTINCT(order_id) / SUM(sessions)

This requires blending order data with session data. If using GA4 alongside BigQuery orders:

SUM(purchases) / SUM(sessions)

6. Revenue Per Session

How much each visit is worth:

SUM(revenue) / SUM(sessions)

Useful for comparing traffic sources. High-traffic, low-revenue channels become obvious.

Conditional Logic with CASE Statements#

Looker Studio calculated fields support CASE statements for conditional metrics:

Order Size Buckets

CASE 
  WHEN order_total < 50 THEN "Small (<$50)"
  WHEN order_total < 150 THEN "Medium ($50-150)"
  WHEN order_total < 300 THEN "Large ($150-300)"
  ELSE "Enterprise ($300+)"
END

Use this as a dimension to analyze behavior by order size.

Margin Category

CASE
  WHEN (revenue - cost) / revenue > 0.5 THEN "High Margin"
  WHEN (revenue - cost) / revenue > 0.25 THEN "Medium Margin"
  ELSE "Low Margin"
END

You may be interested in: WooCommerce Events to BigQuery Without GA4

Date-Based Calculations#

Days Since Last Order

DATE_DIFF(CURRENT_DATE(), last_order_date)

Useful for identifying at-risk customers who have not ordered recently.

Month-Over-Month Growth

This gets complex in Looker Studio. For reliable MoM calculations, pre-calculate in BigQuery:

-- In BigQuery, not Looker Studio
(current_month_revenue - previous_month_revenue) / previous_month_revenue

Then import the result as a simple field.

Performance Considerations#

Too many calculated fields slow down queries and report loading times—keep under 15-20 per data source (Swydo, 2025).

What slows reports down:

• Nested calculations: Calculated fields referencing other calculated fields
• Complex aggregations: COUNT_DISTINCT on large datasets
• Blended data: Calculations across multiple data sources
• Too many fields: Even simple calculations add up

Complex calculations should be pre-calculated in BigQuery before importing to Looker Studio (Swydo, 2025). This includes:

• Rolling averages
• Cohort analysis
• Customer lifetime value
• Attribution modeling

Create these as BigQuery views or scheduled queries, then connect Looker Studio to the pre-calculated results.

The Data Foundation Problem#

These formulas only work if your BigQuery data includes the right fields. GA4 ecommerce tracking captures revenue, but not:

• Product cost (required for profit margin)
• Customer type (required for CAC)
• Complete order details (required for accurate AOV)

Server-side tracking from WooCommerce can capture all of this. Transmute Engine™ routes complete order data—including product cost if configured—directly to BigQuery. That data foundation makes real profit calculations possible, not just revenue estimates.

Quick Reference: Formula Cheat Sheet#

• Profit Margin: (SUM(revenue) - SUM(cost)) / SUM(revenue)
• AOV: SUM(order_total) / COUNT_DISTINCT(order_id)
• ROAS: SUM(revenue) / SUM(ad_spend)
• CAC: SUM(marketing_spend) / COUNT_DISTINCT(new_customer_id)
• Conversion Rate: COUNT_DISTINCT(order_id) / SUM(sessions)
• Revenue Per Session: SUM(revenue) / SUM(sessions)

Key Takeaways#

• Looker Studio supports 64+ functions—use data source fields for reusable metrics, chart fields for blended data
• Keep calculated fields under 15-20 per data source to maintain report performance
• Pre-calculate complex metrics (CLV, cohorts, rolling averages) in BigQuery, not Looker Studio
• Profit margin and CAC require data GA4 does not capture—you need server-side WooCommerce tracking
• CASE statements enable conditional logic for order buckets, margin categories, and customer segments

Ready to build dashboards with real ecommerce metrics? Transmute Engine sends complete WooCommerce data to BigQuery—including the cost fields that make profit calculations possible.