Schema · Entity relationship diagram
Shopify Analytics tables and relationships
Daily Shopify Analytics reports: online store sessions and the checkout funnel by day, landing page, referrer, device, country and UTM (human and bot traffic split), sales by day with cost of goods sold and gross profit, by channel, product and discount code, and Shop campaign insights. Tables and columns are named after the Shopify Admin API and share one shopify schema with Shopify Orders, Shopify Customers, Shopify Products & Inventory, Shopify Payments & Payouts, Shopify Checkouts & Discounts.
ConnectPaste an API key
Default schema
shopifySource API docshelp.shopify.com
DestinationsAmazon Redshift, Google BigQuery, Snowflake, PostgreSQL, Amazon S3
- The six Shopify connectors load into one
shopifyschema. The others: Shopify Orders, Shopify Customers, Shopify Products & Inventory, Shopify Payments & Payouts, Shopify Checkouts & Discounts. - Daily reports from ShopifyQL, Shopify's analytics query language (the numbers behind Shopify's Analytics pages). Each table is one ShopifyQL dataset (
sessions,sales,shop_campaign_insights) grouped bydayand the dimensions in its name; columns are ShopifyQL's own dimension and metric names. Rates are fractions (0.031 = 3.1%); durations are seconds. - One row per day and grouping, in the shop's time zone. The last 14 days are re-read on every sync (Shopify keeps reclassifying bot sessions for days) and replaced day by day. Session tables split human and bot traffic (
human_or_bot_session). A day with more than 1,000 groups (for example landing pages) keeps the 1,000 largest.
Primary keyForeign keyReferencesJoins on (not a key)
Drag to pan, scroll or pinch to zoom, click a table to see what it joins to.
Table reference
11 tables · click a table for its columnssessions_by_day13 columns · Sessionsshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| human_or_bot_session | varchar(65535) | ||
| sessions | bigint | ||
| online_store_visitors | bigint | ||
| bounces | bigint | ||
| sessions_with_cart_additions | bigint | ||
| sessions_that_reached_checkout | bigint | ||
| sessions_that_completed_checkout | bigint | ||
| pageviews | bigint | ||
| average_session_duration | decimal(38,9) | ||
| conversion_rate | decimal(38,9) | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sessions_by_landing_page12 columns · Sessionsshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| human_or_bot_session | varchar(65535) | ||
| landing_page_type | varchar(65535) | ||
| landing_page_path | varchar(65535) | ||
| sessions | bigint | ||
| online_store_visitors | bigint | ||
| bounces | bigint | ||
| sessions_with_cart_additions | bigint | ||
| sessions_that_reached_checkout | bigint | ||
| sessions_that_completed_checkout | bigint | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sessions_by_referrer13 columns · Sessionsshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| human_or_bot_session | varchar(65535) | ||
| referring_channel | varchar(65535) | ||
| referrer_source | varchar(65535) | ||
| referrer_name | varchar(65535) | ||
| sessions | bigint | ||
| online_store_visitors | bigint | ||
| bounces | bigint | ||
| sessions_with_cart_additions | bigint | ||
| sessions_that_reached_checkout | bigint | ||
| sessions_that_completed_checkout | bigint | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sessions_by_device11 columns · Sessionsshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| human_or_bot_session | varchar(65535) | ||
| session_device_type | varchar(65535) | ||
| sessions | bigint | ||
| online_store_visitors | bigint | ||
| bounces | bigint | ||
| sessions_with_cart_additions | bigint | ||
| sessions_that_reached_checkout | bigint | ||
| sessions_that_completed_checkout | bigint | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sessions_by_country12 columns · Sessionsshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| human_or_bot_session | varchar(65535) | ||
| session_country_code | varchar(65535) | ||
| session_country | varchar(65535) | ||
| sessions | bigint | ||
| online_store_visitors | bigint | ||
| bounces | bigint | ||
| sessions_with_cart_additions | bigint | ||
| sessions_that_reached_checkout | bigint | ||
| sessions_that_completed_checkout | bigint | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sessions_by_utm13 columns · Sessionsshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| human_or_bot_session | varchar(65535) | ||
| utm_source | varchar(65535) | ||
| utm_medium | varchar(65535) | ||
| utm_campaign | varchar(65535) | ||
| sessions | bigint | ||
| online_store_visitors | bigint | ||
| bounces | bigint | ||
| sessions_with_cart_additions | bigint | ||
| sessions_that_reached_checkout | bigint | ||
| sessions_that_completed_checkout | bigint | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sales_by_day16 columns · Salesshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| orders | bigint | ||
| gross_sales | decimal(38,9) | ||
| discounts | decimal(38,9) | ||
| returns | decimal(38,9) | ||
| net_sales | decimal(38,9) | ||
| shipping_charges | decimal(38,9) | ||
| taxes | decimal(38,9) | ||
| total_sales | decimal(38,9) | ||
| cost_of_goods_sold | decimal(38,9) | ||
| gross_profit | decimal(38,9) | ||
| net_items_sold | bigint | ||
| customers | bigint | ||
| new_customers | bigint | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sales_by_channel12 columns · Salesshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| sales_channel | varchar(65535) | ||
| orders | bigint | ||
| gross_sales | decimal(38,9) | ||
| discounts | decimal(38,9) | ||
| returns | decimal(38,9) | ||
| net_sales | decimal(38,9) | ||
| shipping_charges | decimal(38,9) | ||
| taxes | decimal(38,9) | ||
| total_sales | decimal(38,9) | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sales_by_product13 columns · Salesshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| product_idJoins to products.id (ShopifyQL product_id is the product number.). | bigint | FK | Joins to products.id (ShopifyQL product_id is the product number.). |
| product_title | varchar(65535) | ||
| product_variant_sku | varchar(65535) | ||
| net_items_sold | bigint | ||
| gross_sales | decimal(38,9) | ||
| discounts | decimal(38,9) | ||
| returns | decimal(38,9) | ||
| net_sales | decimal(38,9) | ||
| cost_of_goods_sold | decimal(38,9) | ||
| gross_profit | decimal(38,9) | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
sales_by_discount_code8 columns · Salesshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| discount_code | varchar(65535) | ||
| orders | bigint | ||
| discounts | decimal(38,9) | ||
| gross_sales | decimal(38,9) | ||
| net_sales | decimal(38,9) | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |
shop_campaign_insights_by_day7 columns · Shop campaignsshow in diagram
Primary key: day
| Column | Type | Key | Description |
|---|---|---|---|
| dayPrimary key. | date | PK | Primary key. |
| shop_campaign_sales | decimal(38,9) | ||
| shop_campaign_customers | bigint | ||
| shop_campaign_ad_spend | decimal(38,9) | ||
| shop_campaign_return_on_ad_spend | decimal(38,9) | ||
| _jsdata_deletedTrue when the record was deleted at the source. | boolean | True when the record was deleted at the source. | |
| _jsdata_syncedWhen jsdata last wrote this row. | timestamp | When jsdata last wrote this row. |