How to Connect E-Commerce Data to Google BigQuery

Connecting your e-commerce platform to Google BigQuery lets you calculate true ROAS, track revenue by product and campaign, and understand which marketing channels drive actual purchases. Platform-reported conversion values are estimates. Your Shopify data is the ground truth.

Here is how to connect your e-commerce data to BigQuery and build reports that show real marketing performance.

Why E-Commerce Data Belongs in BigQuery

Ad platforms attribute revenue using their own models. Meta uses a 7-day click, 1-day view window. Google Ads uses a 30-day click window. When you run both platforms simultaneously, they both claim credit for the same purchase. The sum of their reported revenue often exceeds your actual revenue.

Your Shopify (or WooCommerce, or Magento) data shows actual orders. Each order has one revenue number, one date, and one set of UTM parameters from the session that drove the purchase. When you join this data with your ad spend in BigQuery, you calculate true ROAS without attribution overlap.

What E-Commerce Data to Send to BigQuery

From Shopify, the most useful data for marketing analysis includes:

Orders: order ID, date, revenue, discount amount, net revenue, customer ID.

Order line items: product name, product ID, quantity, price, variant.

Customers: customer ID, email, acquisition date, total orders, lifetime value.

Sessions (if using Shopify analytics or UTM tracking): session source, medium, campaign, and the orders attributed to each session.

For marketing attribution, orders with UTM parameters are the most critical dataset.

Step 1: Connect Shopify to BigQuery With Porter Metrics

In Porter Metrics, create a new query and select Shopify as your data source. Authenticate with your Shopify store credentials.

Select the data you want to sync. For a marketing attribution report, select orders with order date, revenue, and UTM campaign fields. For product performance analysis, include order line items with product name and revenue.

Set your write mode to “update” and schedule daily syncs. Porter loads your Shopify data into BigQuery automatically.

Step 2: Calculate True ROAS by Campaign

Once your Shopify order data and your ad data are both in BigQuery, join them on the UTM campaign field.
SELECT
ads.campaign_name,
SUM(ads.spend) AS total_spend,
COUNT(orders.order_id) AS total_orders,
SUM(orders.revenue) AS actual_revenue,
ROUND(SUM(orders.revenue) / NULLIF(SUM(ads.spend), 0), 2) AS true_roas
FROM your_dataset.meta_ads AS ads
LEFT JOIN your_dataset.shopify_orders AS orders
ON ads.campaign_name = orders.utm_campaign
AND ads.date = DATE(orders.created_at)
WHERE ads.date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’
GROUP BY ads.campaign_name
ORDER BY true_roas DESC
This query returns true ROAS for each campaign, calculated from actual Shopify revenue divided by actual ad spend. Compare this to the ROAS reported in your ad platforms to see the attribution gap.

Step 3: Analyze Product Performance by Channel

BigQuery lets you see which products sell best through each marketing channel.
SELECT
orders.utm_source AS channel,
items.product_name,
COUNT(items.order_id) AS units_sold,
SUM(items.revenue) AS product_revenue
FROM your_dataset.shopify_order_items AS items
JOIN your_dataset.shopify_orders AS orders
ON items.order_id = orders.order_id
WHERE DATE(orders.created_at) BETWEEN ‘2026-01-01’ AND ‘2026-01-31’
GROUP BY channel, product_name
ORDER BY product_revenue DESC
This shows which products generate the most revenue from each channel (Meta, Google, email, organic). You use this to inform creative strategy and product-level bidding decisions.

Step 4: Track Customer Lifetime Value by Acquisition Channel

For e-commerce brands, the most valuable metric is not first-order ROAS but customer lifetime value (LTV) by acquisition channel.

SELECT

first_order.utm_source AS acquisition_channel,

COUNT(DISTINCT customers.customer_id) AS total_customers,

ROUND(SUM(customers.total_spent) / COUNT(DISTINCT customers.customer_id), 2) AS avg_ltv,

ROUND(SUM(ad_spend.spend) / COUNT(DISTINCT customers.customer_id), 2) AS cac

FROM your_dataset.shopify_customers AS customers

JOIN your_dataset.shopify_orders AS first_order

ON customers.first_order_id = first_order.order_id

LEFT JOIN your_dataset.meta_ads AS ad_spend

ON first_order.utm_campaign = ad_spend.campaign_name

GROUP BY acquisition_channel

ORDER BY avg_ltv DESC

This shows average LTV and customer acquisition cost (CAC) by the channel that brought in the first order. Channels with high LTV and low CAC are your best-performing acquisition channels, regardless of first-order ROAS.

Building Your E-Commerce Marketing Dashboard in Looker Studio

Connect Looker Studio to your BigQuery e-commerce tables. Build a report with:

Scorecards for total revenue, total orders, average order value, and true ROAS.

A comparison of platform-reported ROAS vs true ROAS by campaign.

A table of top products by revenue, broken down by channel.

A trend line showing daily revenue and orders over time.

This dashboard gives your e-commerce marketing team the data they need to make budget decisions based on actual business outcomes.