BigQuery Integration

{
  "event_name": "page_viewed",
  "event_timestamp": "2024-01-15T10:30:00.000Z",
  "user_id": null,
  "anonymous_id": "shop_client_abc123",
  "session_id": "sess_xyz789",
  "page_url": "https://mystore.com/collections/summer",
  "page_title": "Summer Collection – My Store",
  "page_path": "/collections/summer",
  "referrer": "https://google.com",
  "device_type": "Desktop",
  "browser": "Chrome",
  "os": "Mac OS X",
  "screen_width": 1440,
  "screen_height": 900,
  "language": "en-US",
  "currency": "USD",
  "value": 0,
  "utm_source": "google",
  "utm_medium": "cpc",
  "utm_campaign": "summer-sale",
  "utm_content": null,
  "utm_term": null,
  "gclid": "CjwKCAjw...",
  "fbclid": null,
  "msclkid": null,
  "ttclid": null,
  "properties": "{}",
  "products": null,
  "source": "vendo_web_pixel",
  "version": "1.0.0",
  "insert_id": "49b71cd2-295b-476a-a345-0eb50bd13b57"
}

{
  "event_name": "product_viewed",
  "event_timestamp": "2024-01-15T10:32:00.000Z",
  "user_id": null,
  "anonymous_id": "shop_client_abc123",
  "session_id": "sess_xyz789",
  "page_url": "https://mystore.com/products/classic-tshirt",
  "page_title": "Classic T-Shirt – My Store",
  "page_path": "/products/classic-tshirt",
  "referrer": "https://mystore.com/collections/all",
  "device_type": "Desktop",
  "browser": "Chrome",
  "os": "Mac OS X",
  "screen_width": 1440,
  "screen_height": 900,
  "language": "en-US",
  "currency": "USD",
  "value": 29.99,
  "utm_source": "google",
  "utm_medium": "cpc",
  "utm_campaign": null,
  "properties": "{\"product_id\":\"gid://shopify/Product/7894561230\",\"product_title\":\"Classic T-Shirt\"}",
  "products": "[{\"product_id\":\"gid://shopify/Product/7894561230\",\"sku\":\"TSHIRT-BLK-M\",\"name\":\"Classic T-Shirt\",\"price\":29.99,\"quantity\":1,\"variant_id\":\"gid://shopify/ProductVariant/42345678901\",\"variant_title\":\"Black / Medium\",\"brand\":\"My Brand\",\"category\":\"Apparel\"}]",
  "source": "vendo_web_pixel",
  "version": "1.0.0",
  "insert_id": "prod-view-event-id-123"
}

{
  "event_name": "product_added_to_cart",
  "event_timestamp": "2024-01-15T10:35:00.000Z",
  "user_id": null,
  "anonymous_id": "shop_client_abc123",
  "session_id": "sess_xyz789",
  "page_url": "https://mystore.com/products/classic-tshirt",
  "page_title": "Classic T-Shirt – My Store",
  "page_path": "/products/classic-tshirt",
  "referrer": null,
  "device_type": "Desktop",
  "browser": "Chrome",
  "os": "Mac OS X",
  "screen_width": 1440,
  "screen_height": 900,
  "language": "en-US",
  "currency": "USD",
  "value": 59.98,
  "utm_source": "google",
  "utm_medium": "cpc",
  "properties": "{\"quantity\":2}",
  "products": "[{\"product_id\":\"gid://shopify/Product/7894561230\",\"sku\":\"TSHIRT-BLK-M\",\"name\":\"Classic T-Shirt\",\"price\":29.99,\"quantity\":2,\"variant_id\":\"gid://shopify/ProductVariant/42345678901\",\"variant_title\":\"Black / Medium\",\"brand\":\"My Brand\",\"category\":\"Apparel\"}]",
  "source": "vendo_web_pixel",
  "version": "1.0.0",
  "insert_id": "add-cart-event-id-456"
}

{
  "event_name": "cart_viewed",
  "event_timestamp": "2024-01-15T10:38:00.000Z",
  "user_id": null,
  "anonymous_id": "shop_client_abc123",
  "session_id": "sess_xyz789",
  "page_url": "https://mystore.com/cart",
  "page_title": "Your Cart – My Store",
  "page_path": "/cart",
  "device_type": "Desktop",
  "browser": "Chrome",
  "os": "Mac OS X",
  "currency": "USD",
  "value": 149.97,
  "properties": "{\"quantity\":3}",
  "products": "[{\"product_id\":\"gid://shopify/Product/7894561230\",\"name\":\"Classic T-Shirt\",\"price\":29.99,\"quantity\":2},{\"product_id\":\"gid://shopify/Product/7894561231\",\"name\":\"Running Shorts\",\"price\":45.00,\"quantity\":1}]",
  "source": "vendo_web_pixel",
  "version": "1.0.0",
  "insert_id": "cart-view-event-id-789"
}

{
  "event_name": "checkout_started",
  "event_timestamp": "2024-01-15T10:40:00.000Z",
  "user_id": null,
  "anonymous_id": "shop_client_abc123",
  "session_id": "sess_xyz789",
  "page_url": "https://mystore.com/checkouts/abc123",
  "page_title": "Checkout – My Store",
  "page_path": "/checkouts/abc123",
  "device_type": "Desktop",
  "browser": "Chrome",
  "os": "Mac OS X",
  "currency": "USD",
  "value": 162.49,
  "utm_source": "google",
  "utm_medium": "cpc",
  "utm_campaign": "summer-sale",
  "properties": "{\"checkout_token\":\"abc123def456\",\"order_id\":\"gid://shopify/Order/5363269534010\",\"cart_subtotal_amount\":149.97,\"tax_amount\":12.52,\"shipping_amount\":0}",
  "products": "[{\"product_id\":\"gid://shopify/Product/7894561230\",\"name\":\"Classic T-Shirt\",\"price\":29.99,\"quantity\":2}]",
  "source": "vendo_web_pixel",
  "version": "1.0.0",
  "insert_id": "checkout-started-id-101"
}

{
  "event_name": "checkout_completed",
  "event_timestamp": "2024-01-15T10:45:00.000Z",
  "user_id": "6359703453860",
  "anonymous_id": "shop_client_abc123",
  "session_id": "sess_xyz789",
  "page_url": "https://mystore.com/checkouts/abc123/thank_you",
  "page_title": "Thank you – My Store",
  "page_path": "/checkouts/abc123/thank_you",
  "device_type": "Desktop",
  "browser": "Chrome",
  "os": "Mac OS X",
  "currency": "USD",
  "value": 162.49,
  "utm_source": "google",
  "utm_medium": "cpc",
  "utm_campaign": "summer-sale",
  "gclid": "CjwKCAjw...",
  "properties": "{\"checkout_token\":\"abc123def456\",\"order_id\":\"gid://shopify/Order/5363269534010\",\"cart_subtotal_amount\":149.97,\"tax_amount\":12.52,\"shipping_amount\":10.00,\"email\":\"customer@email.com\",\"phone\":\"+14155551234\"}",
  "products": "[{\"product_id\":\"gid://shopify/Product/7894561230\",\"name\":\"Classic T-Shirt\",\"price\":29.99,\"quantity\":2}]",
  "source": "vendo_web_pixel",
  "version": "1.0.0",
  "insert_id": "checkout-completed-id-202"
}

{
  "event_name": "collection_viewed",
  "event_timestamp": "2024-01-15T10:28:00.000Z",
  "user_id": null,
  "anonymous_id": "shop_client_abc123",
  "session_id": "sess_xyz789",
  "page_url": "https://mystore.com/collections/summer",
  "page_title": "Summer Collection – My Store",
  "page_path": "/collections/summer",
  "device_type": "Desktop",
  "browser": "Chrome",
  "os": "Mac OS X",
  "currency": "USD",
  "value": 0,
  "properties": "{\"collection_id\":\"gid://shopify/Collection/123456789\",\"collection_title\":\"Summer Collection\"}",
  "products": "[{\"product_id\":\"gid://shopify/Product/7894561230\",\"name\":\"Classic T-Shirt\",\"price\":29.99,\"quantity\":1},{\"product_id\":\"gid://shopify/Product/7894561231\",\"name\":\"Running Shorts\",\"price\":45.00,\"quantity\":1}]",
  "source": "vendo_web_pixel",
  "version": "1.0.0",
  "insert_id": "collection-viewed-id-303"
}

{
  "event_name": "search_submitted",
  "event_timestamp": "2024-01-15T10:25:00.000Z",
  "user_id": null,
  "anonymous_id": "shop_client_abc123",
  "session_id": "sess_xyz789",
  "page_url": "https://mystore.com/search?q=t-shirt",
  "page_title": "Search Results – My Store",
  "page_path": "/search",
  "device_type": "Desktop",
  "browser": "Chrome",
  "os": "Mac OS X",
  "currency": "USD",
  "value": 0,
  "properties": "{\"search_query\":\"t-shirt\"}",
  "products": "[{\"product_id\":\"gid://shopify/Product/7894561230\",\"name\":\"Classic T-Shirt\",\"price\":29.99,\"quantity\":1}]",
  "source": "vendo_web_pixel",
  "version": "1.0.0",
  "insert_id": "search-submitted-id-404"
}

-- Page views by day
SELECT 
  DATE(event_timestamp) as date,
  COUNT(*) as page_views,
  COUNT(DISTINCT anonymous_id) as unique_visitors
FROM `your_project.your_dataset.events`
WHERE event_name = 'page_viewed'
GROUP BY date
ORDER BY date DESC
 
-- Product view to add-to-cart conversion
SELECT 
  DATE(pv.event_timestamp) as date,
  COUNT(DISTINCT pv.insert_id) as product_views,
  COUNT(DISTINCT atc.insert_id) as add_to_carts,
  SAFE_DIVIDE(COUNT(DISTINCT atc.insert_id), COUNT(DISTINCT pv.insert_id)) * 100 as conversion_rate
FROM `your_project.your_dataset.events` pv
LEFT JOIN `your_project.your_dataset.events` atc
  ON pv.anonymous_id = atc.anonymous_id
  AND atc.event_name = 'product_added_to_cart'
  AND DATE(atc.event_timestamp) = DATE(pv.event_timestamp)
WHERE pv.event_name = 'product_viewed'
GROUP BY date
ORDER BY date DESC
 
-- UTM attribution for completed checkouts
SELECT 
  utm_source,
  utm_medium,
  utm_campaign,
  COUNT(*) as conversions,
  SUM(value) as revenue
FROM `your_project.your_dataset.events`
WHERE event_name = 'checkout_completed'
  AND event_timestamp >= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY utm_source, utm_medium, utm_campaign
ORDER BY revenue DESC

-- Link events to orders by customer
SELECT 
  e.*,
  o.order_number,
  o.total_price
FROM events e
JOIN orders o ON e.subject_id = o.customer.id
WHERE e.subject_type = 'Customer'

SELECT 
  DATE(created_at) as order_date,
  COUNT(*) as orders,
  SUM(total_price) as revenue,
  AVG(total_price) as aov
FROM `your_project.your_dataset.orders`
WHERE financial_status = 'paid'
  AND created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY order_date
ORDER BY order_date DESC

SELECT 
  li.product_id,
  li.title as product_name,
  COUNT(*) as times_ordered,
  SUM(li.quantity) as units_sold,
  SUM(li.price * li.quantity) as revenue
FROM `your_project.your_dataset.orders` o,
UNNEST(line_items) as li
WHERE o.financial_status = 'paid'
GROUP BY li.product_id, li.title
ORDER BY revenue DESC
LIMIT 20

SELECT 
  c.id as customer_id,
  c.email,
  c.first_name,
  c.last_name,
  COUNT(o.id) as order_count,
  SUM(o.total_price) as lifetime_value,
  MIN(o.created_at) as first_order,
  MAX(o.created_at) as last_order,
  DATE_DIFF(MAX(o.created_at), MIN(o.created_at), DAY) as customer_lifespan_days
FROM `your_project.your_dataset.customers` c
LEFT JOIN `your_project.your_dataset.orders` o ON c.id = o.customer.id
WHERE o.financial_status = 'paid'
GROUP BY c.id, c.email, c.first_name, c.last_name
ORDER BY lifetime_value DESC

SELECT 
  DATE(created_at) as date,
  COUNT(*) as abandoned_carts,
  SUM(total_price) as abandoned_value,
  AVG(total_price) as avg_abandoned_value,
  COUNT(DISTINCT email) as unique_customers
FROM `your_project.your_dataset.abandoned_checkouts`
WHERE completed_at IS NULL
  AND created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY date
ORDER BY date DESC

SELECT 
  DATE(o.created_at) as order_date,
  COUNT(DISTINCT o.id) as orders,
  AVG(DATE_DIFF(f.created_at, o.created_at, DAY)) as avg_fulfillment_days,
  AVG(DATE_DIFF(f.delivery_date, f.created_at, DAY)) as avg_delivery_days
FROM `your_project.your_dataset.orders` o,
UNNEST(fulfillments) as f
WHERE f.delivery_date IS NOT NULL
GROUP BY order_date
ORDER BY order_date DESC

WITH customer_cohorts AS (
  SELECT 
    customer.id as customer_id,
    FORMAT_DATE('%Y-%m', MIN(created_at)) as cohort_month,
    DATE_DIFF(created_at, MIN(created_at) OVER (PARTITION BY customer.id), MONTH) as months_since_first
  FROM `your_project.your_dataset.orders`
  WHERE financial_status = 'paid'
  GROUP BY customer.id, created_at
)
 
SELECT 
  cohort_month,
  months_since_first,
  COUNT(DISTINCT customer_id) as customers,
  COUNT(*) as orders
FROM customer_cohorts
GROUP BY cohort_month, months_since_first
ORDER BY cohort_month, months_since_first

SELECT 
  JSON_EXTRACT_SCALAR(d, '$.code') as discount_code,
  JSON_EXTRACT_SCALAR(d, '$.type') as discount_type,
  COUNT(*) as times_used,
  SUM(total_price) as revenue_with_discount,
  AVG(total_discounts) as avg_discount_amount
FROM `your_project.your_dataset.orders`,
UNNEST(JSON_EXTRACT_ARRAY(discount_codes)) as d
WHERE financial_status = 'paid'
GROUP BY discount_code, discount_type
ORDER BY times_used DESC

SELECT 
  REGEXP_EXTRACT(landing_site, r'utm_source=([^&]+)') as utm_source,
  REGEXP_EXTRACT(landing_site, r'utm_medium=([^&]+)') as utm_medium,
  REGEXP_EXTRACT(landing_site, r'utm_campaign=([^&]+)') as utm_campaign,
  COUNT(*) as orders,
  SUM(total_price) as revenue,
  AVG(total_price) as aov
FROM `your_project.your_dataset.orders`
WHERE financial_status = 'paid'
  AND landing_site IS NOT NULL
GROUP BY utm_source, utm_medium, utm_campaign
ORDER BY revenue DESC

-- Example: Create a purchase prediction model
CREATE OR REPLACE MODEL `your_dataset.purchase_prediction`
OPTIONS(model_type='logistic_reg') AS
SELECT 
  IF(order_count > 0, 1, 0) as will_purchase,
  page_views,
  products_viewed,
  cart_adds,
  days_since_last_visit
FROM customer_behavior_features