SELECT start_date, IFNULL(costInUsd, 0) AS costInUsd, IFNULL(conversionValueInLocalCurrency, 0) AS conversionValueInLocalCurrency FROM linkedin_ads.ad_campaign_analytics;

WITH base_data AS ( SELECT segments_date, -- Convert micros to actual EUR then to USD (metrics_cost_micros / 1000000.0) / rate AS spend_usd, -- Convert EUR value to USD metrics_all_conversions_value / rate AS revenue_usd FROM google_ads.account_performance_report apr LEFT JOIN currency.daily_history ch ON apr.segments_date = CAST(ch.date AS DATE) AND ch.from_currency = 'USD' AND ch.to_currency = 'EUR' WHERE -- Ensure we have valid cost and conversion data metrics_cost_micros IS NOT NULL ) SELECT segments_date, ROUND(spend_usd, 2) as spend_usd, ROUND(revenue_usd, 2) as revenue_usd, ROUND(revenue_usd - spend_usd, 2) as profit_usd FROM base_data ORDER BY segments_date;

SELECT charges.created AS transaction_date, charges.id AS charge_id, customers.name AS customer_name, JSON_EXTRACT_SCALAR(TO_JSON_STRING(customers.address), '$.country') as customer_country, charges.currency, charges.amount / 100 AS charge_amount, charges.source_account AS charge_source, -- Base net amount (excluding tax and refunds) (charges.amount - IFNULL(invoices.tax, 0) - IFNULL(charges.amount_refunded, 0)) / 100 AS net_amount, -- Standardized amount in USD CASE WHEN UPPER(charges.currency) = 'USD' THEN (charges.amount - IFNULL(invoices.tax, 0) - IFNULL(charges.amount_refunded, 0)) / 100 WHEN UPPER(charges.currency) = 'EUR' THEN ((charges.amount - IFNULL(invoices.tax, 0) - IFNULL(charges.amount_refunded, 0)) / 100) / IFNULL(dh.rate, 0.90) END AS amount_usd FROM views_stripe.charges AS charges LEFT JOIN views_stripe.invoices AS invoices ON charges.invoice = invoices.id LEFT JOIN views_stripe.customers AS customers ON charges.customer = customers.id LEFT JOIN `currency.daily_history` AS dh ON DATE(charges.created) = PARSE_DATE('%Y-%m-%d', dh.date) AND dh.from_currency = 'USD' AND dh.to_currency = 'EUR' WHERE charges.status = "succeeded" AND charges.refunded = false;