Creating the Events Table

In this guide, we’ll walk through how to create a BigQuery table to store detailed user event data from websites and apps. This structure is designed to support flexible, in-depth analysis of user behavior, conversions, and attribution.

Table schema

Here’s the schema for the events table. Each row represents a single user interaction (event), capturing both technical and marketing-related metadata.

Create table in BigQuery

CREATE TABLE `project-id.dataset-id.events`
(
  client_id STRING,
  event_name STRING NOT NULL,
  page_location STRING,
  page_referrer STRING,
  page_referrer_origin STRING,
  page_title STRING,
  event_id STRING,
  checkout_id STRING,
  order_id STRING,
  transaction_id STRING,
  value FLOAT64,
  currency STRING,
  user_agent STRING,
  language STRING,
  screen_resolution STRING,
  timestamp INT64 NOT NULL,
  search_term STRING,
  external_id STRING,
  message STRING,
  stape_user_id STRING,
  device_browser STRING,
  device_browser_version STRING,
  device_os STRING,
  city STRING,
  country STRING,
  region STRING,
  device_engine STRING,
  device_engine_version STRING,
  device_mobile BOOLEAN,
  bot STRING,
  bot_score INT64,
  ga_session_id STRING,
  ga_session_number INT64,
  _fbp STRING,
  _fbc STRING,
  _gcl_au STRING,
  FPAU STRING,
  FPGCLGB STRING,
  FPID STRING,
  session_id STRING,
  login_session_id STRING,
  wbraid STRING,
  email_hash STRING,
  phone_hash STRING,
  gclid STRING,
  utm_source STRING,
  utm_medium STRING,
  utm_campaign STRING,
  utm_term STRING,
  utm_id STRING,
  fbclid STRING,
  gad_source STRING,
  gbraid STRING,
  media_type STRING,
  srsltid STRING,
  items ARRAY<STRUCT<
    item_id STRING,
    item_name STRING,
    discount FLOAT64,
    index INT64,
    item_brand STRING,
    item_category STRING,
    item_category2 STRING,
    item_category3 STRING,
    item_category4 STRING,
    item_category5 STRING,
    item_list_id STRING,
    item_list_name STRING,
    item_variant STRING,
    price FLOAT64,
    tax FLOAT64,
    quantity INT64
  >>
);

Use cases

This schema is ideal for:

Capturing event-level behavioral analytics
Building marketing attribution models
Analyzing conversion funnels and purchase patterns
Enriching product analytics with item-level insights

Let me know if you want to add a view or query examples for segmentation, funnel tracking, or LTV analysis.