Google BigQuery Datasets and Tables: How Data Is Organized

Google BigQuery organizes all data in a three-level hierarchy: projects contain datasets, and datasets contain tables. Understanding this structure is the first step to setting up your marketing data warehouse correctly.

The BigQuery Data Hierarchy

• Project: the top-level container. Every resource in Google Cloud, including BigQuery datasets and tables, lives inside a project. You select your project at the top of the BigQuery Studio interface.
• Dataset: a container for related tables. Think of a dataset like a folder. You group tables that belong together inside a dataset. For a marketing team, you might create one dataset per data source (a “meta_ads” dataset, a “google_ads” dataset, a “ga4” dataset) or one dataset per client.
• Table: the actual data. A table holds rows and columns. Each row is one record, such as one day of ad performance for one campaign. Each column is one attribute, such as date, campaign name, impressions, clicks, or spend.

The full address of any table in BigQuery follows this format: project_id.dataset_name.table_name. For example: my-marketing-project.meta_ads.daily_performance.

How to Create a Dataset in BigQuery

To create a dataset, open BigQuery Studio and find your project in the Explorer panel on the left. Click the three-dot menu next to your project name and select “Create dataset.”

You will see a form with these fields:

• Dataset ID: the name of your dataset. Use lowercase letters, numbers, and underscores. No spaces. For example: meta_ads or google_analytics_4.
• Data location: the region where your data is stored. Choose a region close to where your team works. US and EU are the most common choices. Once you set the location, you cannot change it.
• Default table expiration: optional. You set a number of days after which tables in this dataset are automatically deleted. Leave this blank for marketing data that you want to keep indefinitely.

Click “Create dataset” and your dataset appears in the Explorer panel.

How to Create a Table in BigQuery

You create tables in BigQuery in several ways.

• Upload a file: you upload a CSV, JSON, or Parquet file directly from your computer. BigQuery reads the file and creates a table from it.
• Query result: you run a SQL query and save the result as a new table. This is useful for creating summary tables or aggregated views of your data.
• External connection: you connect BigQuery to an external data source, such as Google Sheets or a Cloud Storage file, and query it as if it were a BigQuery table.
• Connector tool: you use a tool like Porter Metrics to load data from your ad platforms directly into a BigQuery table on a schedule. This is the most common method for marketing data.

Understanding Table Schema

Every BigQuery table has a schema. The schema defines the columns in the table, the data type of each column, and whether each column is required or optional.

Common data types in marketing tables:

• STRING: text values, such as campaign names, ad group names, and country codes.
• INTEGER: whole numbers, such as impressions, clicks, and conversions.
• FLOAT: decimal numbers, such as spend, ROAS, and cost per click.
• DATE: date values in YYYY-MM-DD format.
• TIMESTAMP: date and time values, used for event-level data like GA4 events.

When you load data from Porter Metrics into BigQuery, the schema is created automatically based on the metrics and dimensions you selected. You do not define the schema manually.

How Marketing Data Is Organized in BigQuery

A typical marketing data setup in BigQuery looks like this:

• Project: my-marketing-project
• Dataset: meta_ads
• Table: daily_performance (one row per day per campaign)
• Table: ad_level_performance (one row per day per ad)
• Dataset: google_ads
• Table: daily_performance
• Table: keyword_performance
• Dataset: ga4
• Table: events (one row per event)
• Table: sessions (one row per session)

This structure keeps data from different sources organized and easy to find. When you write a SQL query, you reference the full path: SELECT * FROM my-marketing-project.meta_ads.daily_performance.

Loading Marketing Data Into Your BigQuery Datasets

Porter Metrics creates your datasets and tables automatically when you set up your first data sync. You select your data source, choose your BigQuery project, and name your dataset and table. Porter loads your data on a daily schedule and keeps your tables up to date.