Online store sessions table

The online store sessions table contains information about the visitors to your online store. This table has one row per session, where one session can contain many page views.

If you use Urchin Traffic Module (UTM) parameters in marketing campaigns, then you can use this table to track how many customers they direct to your store.

Columns

Anchor link to section titled "Columns"
Column Data type Description
derived_session_token varchar The ID of the visitor session. This column is the primary key for the table.
shop_id numeric The ID of the store. This column is a foreign key reference to the shop_id column in the shops table.
session_duration numeric (seconds) The time that the visitor spent in your store during the session. This value is the sum of the time spent on each page view (except for the last page viewed) during the session. The maximum value is 1800 seconds (30 minutes).
count_of_pageviews numeric The number of pages viewed during the session.
session_started_at timestamp The date (ISO 8601) and time (UTC) when this visitor session begins. It is the timestamp when the visitor first visits the store during this session. The format is YYYY-MM-DD HH:mm:ss (for example, 2016-02-05 17:04:01).
session_token varchar The ID of the session token that is created by the browser. A session token expires after 30 minutes of inactivity.
user_token varchar The unique token assigned to the user. A user token expires after 2 years of inactivity.
landing_page_url varchar The full URL of the first page visited during the session. For example, https://www.myshop.com/products/cool-tshirt?utm_source=Shop&utm_medium=....
landing_page_type varchar The type of the first page visited during the session, if it is identifiable. This value is based on the landing page URL. Values include, Product, Homepage, Checkout, Blog Index, Blog Article, Collection, Cart, Custom Page, Search, and List of Collections. For example, if the landing page URL is https://stockroom.shopify.com/products/10x-entrepreneurs-journal, then the value in this column is Product.
landing_page_resource_id numeric When the landing page is a resource page, such as a product page, this column contains the Shopify ID of the resource. Otherwise, it contains NULL. For example, if the landing page is a product page, then the value in this column is the ID of the product in the Shopify admin. Not all pages in a store are resource pages. For example, the Home page and the Checkout page are not resource pages.
exit_page_type varchar The type of the exit page, if it is identifiable. Otherwise, the field contains NULL. Valid values include, Product, Homepage, Checkout, Blog Index, Blog Article, Collection, Cart, Custom Page, Search, and List of Collections.
exit_page_resource_id numeric When the exit page is a resource page, this column contains the Shopify ID of the resource. Otherwise, it contains NULL. For example, if the exit page is a product page, then landing_page_resource_id is the ID of the product in the Shopify admin. Not all pages in a store are resource pages. For example, the Home page and the Checkout pages are not resources.
exit_page_path varchar The path portion of the URL of the last page that the visitor visited during the session. This field is the same as exit_page_url except that it doesn’t contain the name of the store or any parameters. For example, /products/cool-tshirt.
exit_page_url varchar The full URL of the last page that the visitor visited during the session. For example, https://www.myshop.com/products/cool-tshirt?utm_source=Shop&utm_medium=....
referrer_domain varchar The hostname of the website that contained the link to your store. For example, facebook.com.
referrer_host varchar The first portion of the URL of the referring website. For example, www.facebook.com.
referrer_is_direct boolean True when the visitor was referred to the store by a link from your mobile app or from your online store. Contains False when the visitor is referred by another website.
referrer_name varchar The human-readable name of the referring website. This value is based on the hostname. For example, if the host is www.facebook.com, then the referrer_name is Facebook.
referrer_path varchar The path portion of the URL of the referring website. For example, /pages/about.
referrer_source varchar The type or medium of the referrer. For example, unknown, email, social, or search.
referrer_subdomain varchar The first portion of the URL of the referring website. For example, if the host is www.facebook.com, then the value in this column is www.facebook.
referrer_tld varchar The top-level domain of the referring website. For example, if the hostname is www.facebook.com, then the referrer_tld is com.
referrer_terms varchar The search terms that your visitor used to find your store. Contains NULL if a search was not used or if this data could not be captured.
referrer_url varchar The full URL of the referring website. That is, the website that contained the link to your store. For example, facebook.com/pages/about.
utm_campaign_content varchar The content used in the Urchin Traffic Module parameters (UTM) campaign, such as details about A/B testing and content-targeted ads. For example, 300x300_jeans.jpg.
utm_campaign_medium varchar The UTM campaign medium. For example, Email or CPC.
utm_campaign varchar The name of the UTM campaign. For example, Spring sale or Jeans.
utm_campaign_source varchar The referral source of the UTM campaign. This is the name of the website or application where the referral link exists. For example, Facebook.
utm_campaign_term varchar The UTM campaign parameters captured from the URL of the referral link. These parameters can be the keywords used in a paid search ad. For example, Jeans.
ua_browser varchar The name of the browser that the visitor used. For example, Mobile Safari, Chrome.
ua_browser_version varchar The version number of the browser that the visitor used.
ua_form_factor varchar The form factor (type) of the device that the visitor used. For example, Desktop, Mobile, or Tablet.
ua_os varchar The name of the operating system that runs on the device that the visitor used. For example, Mac OS X, iOS, or Android.
ua_os_version varchar The version number of the operating system on the device that the visitor used.
ua_raw varchar The string that identifies the user agent of the user. User agent shows data about the operating system and device that a visitor uses to browse your store.
ua_associated_app varchar The name of the mobile app that the visitor used to visit the store. For example, Twitter or Instagram.
count_of_orders_completed numeric The number of orders created during the session.
completed_first_order_at timestamp The date (ISO 8601) and time (UTC) when the visitor completes their first order during this session. Contains NULL if the visitor never completes an order during this session. The format is YYYY-MM-DD HH:mm:ss (for example, 2016-02-05 17:04:01).
hit_first_checkout_at timestamp The date (ISO 8601) and time (UTC) when the visitor first visits the checkout page during this session. Contains NULL if the visitor never visits the checkout page during the session. The format is YYYY-MM-DD HH:mm:ss (for example, 2016-02-05 17:04:01).
started_first_checkout_at timestamp The date (ISO 8601) and time (UTC) when the visitor first starts entering their information into the checkout fields. Contains NULL if the visitor never enters their information during the session. The format is YYYY-MM-DD HH:mm:ss (for example, 2016-02-05 17:04:01).
count_of_cart_additions numeric The number of items added to the cart during the session. For example, if a customer adds 5 t-shirts and 1 lipstick to their cart, and then removes one of the t-shirts, then the value in this column is 6.
count_of_distinct_products_added_to_cart numeric The number of distinct products added to the cart during the session. For example, if a customer adds 5 t-shirts and 1 lipstick are added to the cart, then the value in this column is 2.
count_of_distinct_product_variants_added_to_cart numeric The number of distinct product variants added to the cart during the session. For example, if a customer adds 2 small and 3 large t-shirts and 1 lipstick to the cart, then the value in this column is 3.
used_gift_card boolean True when a gift card is used to pay or partially pay for the order at the checkout, False otherwise.
had_error boolean True when an an error appears during the checkout that is not a payment error, False otherwise.
had_payment_error boolean True when a payment error occurs during the checkout, False otherwise.
had_out_of_stock_warning boolean True when an out-of-stock warning appears on the checkout page, False otherwise.
had_credit_card_info_error boolean True when a payment error occurs because of a credit card error, False otherwise.
had_discount boolean True when a checkout error occurs because of a discount error, False otherwise.
had_free_shipping boolean True when the order has no shipping cost, False otherwise.
hashed_ip varchar The unique, 128-bit hash value of the IP address of the visitor's device.
location_city varchar The name of the city where the visitor is located. For example, Topeka.
location_region varchar The name of the region, such as a province or state, where the visitor is located. For example, Kansas.
location_region_code varchar The code for the region, such as a province or state, where the visitor is located. For example, KS.
location_country varchar The name of the country where the visitor is located. For example, United States.
location_country_code varchar The two-digit ISO country code where the visitor is located. For example, US.

Remarks

Anchor link to section titled "Remarks"

A session starts when a visitor visits a store page and ends when they stop viewing your pages. A session can have multiple page views.

If a session is running at 12:00 am UTC, then this session is recorded as two sessions: one session that occurs before 12:00am and one session that starts at 12:00am.

Session data for the current day could be temporarily incomplete in your data warehouse. For example, if a session is still running when your data warehouse is updated, then some of that session's data, such as the session_duration value, will be incomplete until the next day's update. For many merchants, this type of inaccuracy is tolerable because it lets them view the most current data. Other merchants handle this inaccuracy by making sure that the current date's data is excluded from their queries and reports.

A referrer is another site that contains a link to your store. For example, if a visitor clicks a Facebook ad, then Facebook is the referrer. In contrast, a referral is the actual link that the visitor clicked to visit your site.

Location data is based on GeoIP data about the devices that are used to view your store pages.

This table records when a customer uses a gift card to pay for an order. Information about gift card sales can be found in the Sales table.

Examples

Anchor link to section titled "Examples"

The following SQL query returns the number of sessions by month:

The following SQL query returns the number of sessions by the type of device that was used to visit the online store:

The following SQL query returns the number of sessions by the type of the referrer:

The following SQL query returns the number of distinct visitors who visited by month:

The following SQL query returns the number of visits that were made to the store:

The following SQL query returns the percentage of mobile visits:

The following SQL query returns the percentage of mobile visits by month:

The following SQL query returns session information excluding data from today:

Anchor link to section titled "Related information"