Export TrackLayer events to BigQuery for warehouse attribution

Platform-native attribution is useful for daily channel operations, but it is not enough for multi-touch attribution across paid media, lifecycle, affiliates, organic search, direct, and sales-assisted paths. Each platform gives itself a particular view of credit, usually inside its own attribution window and identity graph. Exporting TrackLayer events to BigQuery gives the data team an independent event log for MTA, incrementality analysis, and finance-facing revenue reconciliation.

Warehouse export also lets you join events with data that ad and email platforms will never fully understand. CRM stages, customer tiers, subscription status, refunds, product margins, inventory availability, sales ownership, and support outcomes can all change the meaning of a conversion. When TrackLayer events sit next to CRM and product data, attribution can answer whether a campaign created profitable customers, not just whether it touched a purchase.

Custom cohort analysis becomes much easier once the event stream is queryable in SQL. You can compare signup cohorts by first source, purchase cohorts by first product viewed, repeat purchase timing by region, or median journey length by lifecycle segment. Those questions are hard to answer inside destination dashboards because the model needs your business definitions, not just the destination's reporting primitives.

Keep the raw export schema compact and stable. BigQuery is best used as the durable source table, with views or marts layered on top for specific teams. The fields below are enough to support attribution, identity checks, delivery diagnostics, and downstream modeling without forcing every event-specific property into a top-level column.

These examples assume a single events table at acme-prod.tracklayer_events.events. Replace project and dataset names with your own, then adapt JSON paths to match the field mapping you selected in TrackLayer.

SELECT
  delivery.platform AS platform,
  SUM(SAFE_CAST(JSON_VALUE(custom_data, "$.value") AS NUMERIC)) AS revenue,
  COUNT(DISTINCT event_id) AS purchases
FROM `acme-prod.tracklayer_events.events`,
UNNEST(platform_deliveries) AS delivery
WHERE event_name = "purchase"
  AND event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY platform
ORDER BY revenue DESC;

WITH user_steps AS (
  SELECT
    JSON_VALUE(custom_data, "$.utm_source") AS utm_source,
    COALESCE(JSON_VALUE(custom_data, "$.external_id"), user_data.em_hash) AS user_key,
    MAX(event_name = "view_content") AS viewed,
    MAX(event_name = "add_to_cart") AS added,
    MAX(event_name = "purchase") AS purchased
  FROM `acme-prod.tracklayer_events.events`
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND event_name IN ("view_content", "add_to_cart", "purchase")
  GROUP BY utm_source, user_key
)
SELECT
  utm_source,
  COUNTIF(viewed) AS viewers,
  COUNTIF(added) AS adders,
  COUNTIF(purchased) AS purchasers,
  SAFE_DIVIDE(COUNTIF(purchased), COUNTIF(viewed)) AS view_to_purchase_rate
FROM user_steps
GROUP BY utm_source
ORDER BY purchasers DESC;

WITH journeys AS (
  SELECT
    DATE_TRUNC(DATE(MIN(IF(event_name = "sign_up", event_time, NULL))), MONTH) AS signup_cohort,
    COALESCE(JSON_VALUE(custom_data, "$.external_id"), user_data.em_hash) AS user_key,
    TIMESTAMP_DIFF(
      MIN(IF(event_name = "purchase", event_time, NULL)),
      MIN(IF(event_name = "sign_up", event_time, NULL)),
      HOUR
    ) AS hours_to_purchase
  FROM `acme-prod.tracklayer_events.events`
  WHERE event_name IN ("sign_up", "purchase")
  GROUP BY user_key
)
SELECT
  signup_cohort,
  APPROX_QUANTILES(hours_to_purchase, 100)[OFFSET(50)] AS median_hours_to_purchase,
  COUNT(*) AS converted_users
FROM journeys
WHERE signup_cohort IS NOT NULL
  AND hours_to_purchase IS NOT NULL
GROUP BY signup_cohort
ORDER BY signup_cohort DESC;

Partition the events table by day using event_time, exposed as an event_time_date partition column. Most attribution questions have a time range, so this one choice prevents queries from scanning years of events when an analyst only needs the last 30 days. Use event_time rather than created_at because attribution follows the moment the user acted, not the moment an export worker wrote the row.

Cluster by merchant_id and event_name. Merchant clustering helps multi-store and agency workspaces isolate each account's rows. Event clustering helps common filters such as purchase, add_to_cart, lead, and refund. Together, partitioning and clustering keep dashboards responsive while preserving a single canonical table.

Always query on partition columns. A WHERE clause on event_time or event_time_date should be part of every scheduled dashboard, dbt model, notebook, and exploratory query. Avoid SELECT * on the raw export table because nested JSON and delivery arrays can make scans more expensive than expected.

For large aggregates, use approximate functions where exactness is not materially useful. APPROX_COUNT_DISTINCT and APPROX_QUANTILES are often good enough for funnel exploration, median journey length, audience sizing, and early campaign reads. Save exact joins for finance reconciliation, invoicing, and critical executive reporting.

If you have very high event volume, compare continuous BigQuery sync with a raw archive in Google Cloud Storage plus on-demand external querying or batch loads. Continuous sync is simpler for analysts. Raw GCS storage can be cheaper for cold data, replay, audit, and infrequent historical analysis.

The warehouse export becomes most valuable when TrackLayer events are joined to the business tables that define customer quality, product economics, and marketing cost. Keep the raw event table neutral, then build governed views for the joins analysts run every week.