Choose the right root table for your report goal

Choosing the correct root table (also called the root entity) is one of the most important decisions when building a report. The root table defines what each row in your report represents and influences which fields, dates, and metrics are available.

This guide explains how root tables work, how to think about them, and how to choose the right one based on your reporting goal.

In this article:

• What a root table represents
• How root tables affect available fields
• Choose a root table based on your goal
• Common root tables by connector
• How to choose the right root table
• Need more support?

What a root table represents

A root table defines the primary record type in your report. Each row in the report corresponds to a single record from the selected root table.

Because each row is based on one root record, the root table determines:

• What each row represents
• How records are counted
• Which dates are used for time-based reporting
• How metrics such as totals and averages are calculated

When choosing a root table, focus on what you are primarily analysing. The goal is to match the root table to the level at which the data is created or tracked, rather than selecting a table based only on which fields you want to include.

How root tables affect available fields

When selecting fields in a report, the root table determines which data can be included.

• You can always use dimensions and measures that belong directly to the root table
• You can also use dimensions from related tables, as long as a valid relationship exists

A relationship exists when the root table is directly connected to another table through a shared identifier, such as a primary key and a corresponding reference field. This direct connection allows data from the related table to be included in the report.

How relationships work

Relationships only expand in directions where each row in the root table matches at most one row in a related table.

This includes:

• One-to-one relationships
• Many-to-one relationships

From the perspective of the root table, each record links to a single related record, even if that related record is referenced by many root records. For example, a customer (related record) can relate to many orders (root record), but an order can only relate to one customer.

Relationships do not expand in a one-to-many direction, where a single root record would relate to multiple rows in another table. For example, if the root table is Products, you cannot expand to Product variants because one product can relate to many variants. To report on variant-level data, you would need to use the Product variants table as the root instead.

In some cases, advanced configurations such as custom fields can be used to reference data that is not directly available through related tables. Even when related fields are available, the root table still defines the structure and behaviour of the report.

Choose a root table based on your goal

A practical way to choose a root table is to start with a clear reporting question. This helps ensure the structure of your report matches what you are trying to measure.

Example:

Consider a simple data model with three tables:

• Orders
• Products
• Customers

If your goal is to analyse activity that happens per order, such as the number of orders per month:

• Use Orders as the root table

If your goal is to analyse inventory or product details, such as units in stock by product type:

• Use a product-related table as the root table

In general, the root table should align the level at which the data is created or counted. Choosing the table that best matches your reporting goal helps avoid incorrect counts and misleading metrics.

Common root tables by connector

While the core concepts behind root tables are consistent across connectors, the specific tables available depend on the data source. Each connector exposes many tables beyond those listed below, and the best choice will always depend on your reporting goal. The sections below highlight some of the most commonly used root tables and the type of reporting each one supports.

Common Shopify root tables

Shopify uses a complex entity model to represent sales, customers, payments, and inventory. The tables below are the most commonly used root tables for reporting.

Agreement lines

Use Agreement lines when you need item-level or event-level detail regarding orders.

Common use cases:

• Product or variant sales analysis
• Revenue including refunds
• Discounts, taxes, shipping, and other financial metrics

Each row represents a single order line item event, such as a sale, adjustment or refund. The date field reflects when the event occurred.

Orders

Use Orders when reporting at the order level without needing line item detail.

Common use cases:

• Order counts over time
• Average order value
• Order-level customer analysis

Each row represents a single order. The date field reflects when the order was processed.

Customers

Use Customers to report on all customers, including those who have not placed an order.

If you only want to include customers who have placed at least one order, use Orders or Agreement lines instead.

Transactions

Use Transactions to report on payment and gateway activity.

Each row represents a single payment transaction linked to an order. This table does not include line item detail. For some payment gateways, gateway fees are also available.

Product variants and Inventory levels

Use Product variants for store-wide product and variant information, including current inventory.

Use Inventory levels when you need inventory quantities broken down by location.

Common Stripe root tables

The Stripe connector exposes both ledger-based tables and billing-focused tables. Choosing the correct root table depends on whether you are reporting on balance movements, billing detail, or customer records.

Balance transactions

Use Balance transactions as the primary root table for financial reporting.

Each row represents a single movement in your Stripe balance. This table acts as a central ledger and can include multiple transaction types, such as:

• Charges and payments
• Refunds and disputes
• Payouts and adjustments
• Fees

Because this table tracks balance activity:

• It is the most reliable source for net financial reporting
• It reflects when funds impact your Stripe balance

Balance transactions do not connect to line-level billing or item detail. If you need item information, use a billing-focused table instead, such as Invoice lines.

Invoice lines

Use Invoice lines when you need detailed billing information.

Each row represents a single line item on an invoice, such as a subscription charge, usage-based charge or adjustment.

This table is suitable for:

• Revenue breakdowns by product or pricing component
• Subscription and invoicing analysis
• Line-level billing reporting

Invoice lines are not designed to represent balance activity and should not be used for net cash or payout reporting.

Customers

Use Customers to report on all Stripe customers, including those without invoices or balance activity.

This table is useful for:

• Customer counts
• Customer attributes and metadata
• High-level customer analysis independent of payments

Common Help Scout root tables

The Help Scout connector is centred around conversations, threads, and customer context.

Conversations

Use Conversations when reporting at the conversation level.

Common use cases:

• Conversation volume over time
• Status and workflow analysis
• High-level customer support reporting

Each row represents a single conversation.

Threads

Use Threads when you need message-level detail within conversations.

Common use cases:

• Reply volume analysis
• Agent activity reporting
• Response pattern analysis

Each row represents a single thread.

Customers

Use Customers to report on people or organisations, including those without active conversations.

How to choose the right root table

When deciding on which root table to use, start by thinking about what each row in your report should represent.

Ask yourself:

• What does a single row in this report represent?
• At what level is the data created or counted?
• Do I need detailed records, or a summary view?

Choosing the root table that best matches your reporting goal helps ensure your report is accurate, consistent, and easier to interpret.