Skip to main content
LayersQL provides six datasets for building metrics: products for product-level performance, sales for order-level analytics, search_text for text search analytics, search_image for image search analytics, search_similar for similar item search analytics, and collections for collection browsing analytics.

​
Products dataset

  • Label: Products
  • Description: Catalog and performance metrics for products.
  • Default group key: product_id

​
Metrics

  • total_sales (USD)
    • Sales after discounts and before taxes/returns.
    • Example: SUM(total_sales)
  • quantity_purchased (items)
    • Number of items purchased.
    • Example: SUM(quantity_purchased)
  • view_sessions (sessions)
    • Sessions where the product was viewed.
    • Example: COUNT_DISTINCT(view_sessions)
  • cart_sessions (sessions)
    • Sessions where the product was added to cart.
  • quantity_added_to_cart (items)
    • Items added to cart from online store sessions.

​
Dimensions

  • geo_country: Two-letter country code.
  • geo_state: Up to three-letter province code.
  • geo_city: City name as captured.
  • marketing_source: UTM source.
  • marketing_medium: UTM medium.
  • marketing_campaign: UTM campaign.
  • device_type: Device category.
  • variant_id: Variant ID.

​
Product attributes (dynamic)

  • Usage: Use product_attributes.* to group or segment by dynamic product attributes defined in your catalog.
  • Examples: 
    GROUP BY product_attributes.color
GROUP BY product_attributes.size
SEGMENT BY product_attributes.material

​
Examples

Sales by product (last 7 days) 
FROM products
SHOW SUM(total_sales)
GROUP BY product_id
SINCE -7d
Views by campaign (this month) 
FROM products
SHOW COUNT_DISTINCT(view_sessions)
GROUP BY marketing_campaign
SINCE this_month
Sales by color (past 30 days) 
FROM products
SHOW SUM(total_sales)
GROUP BY product_attributes.color
SINCE past_30_days

​
Sales dataset

  • Label: Sales
  • Description: Order-level metrics from the purchases table.
  • Default group key: transaction_id

​
Metrics

  • total_sales (USD)
    • Sales after discounts and before taxes/returns.
    • Example: SUM(total_sales)
  • quantity_purchased (items)
    • Number of items purchased.
    • Example: SUM(quantity_purchased)
  • orders (orders)
    • Unique orders (transaction_id).
    • Example: COUNT_DISTINCT(orders)
  • line_items (items)
    • Unique line items purchased.
    • Example: COUNT_DISTINCT(line_items)
  • customers (customers)
    • Unique customers who purchased.
    • Example: COUNT_DISTINCT(customers)

​
Dimensions

  • transaction_id: Shopify order ID.
  • line_item_id: Shopify line item ID.
  • product_id: Shopify product ID.
  • variant_id: Shopify variant ID.
  • customer_id: Shopify customer ID.
  • session_id: Session identifier derived from the order.
  • geo_country: Country from the shipping address.
  • geo_state: State or province from the shipping address.
  • geo_city: City from the shipping address.
  • shopping_channel: Channel of purchase (e.g., online_store).
  • marketing_source: UTM source.
  • marketing_medium: UTM medium.
  • marketing_campaign: UTM campaign.
  • device: Device category derived from User-Agent.
  • browser: Browser family from User-Agent.

​
Examples

Total revenue by country (last 30 days) 
FROM sales
SHOW SUM(total_sales)
GROUP BY geo_country
SINCE -30d
Order count by marketing source (this month) 
FROM sales
SHOW COUNT_DISTINCT(orders)
GROUP BY marketing_source
SINCE this_month
Customer count by shopping channel (past 90 days) 
FROM sales
SHOW COUNT_DISTINCT(customers)
GROUP BY shopping_channel
SINCE past_90_days

​
Search (Text) dataset

  • Label: Search (Text)
  • Description: Text search analytics.
  • Default group key: term

​
Metrics

  • requests (requests)
    • Number of search requests.
    • Example: COUNT_DISTINCT(requests)
  • results_count (results)
    • Results returned per search request.
    • Example: AVG(results_count)
  • total_sales (USD)
    • Sales attributed to search.
    • Example: SUM(total_sales)
  • quantity_purchased (items)
    • Number of items purchased from search traffic.
    • Example: SUM(quantity_purchased)
  • view_sessions (sessions)
    • Sessions where a product was viewed from search results.
    • Example: COUNT_DISTINCT(view_sessions)
  • cart_sessions (sessions)
    • Sessions where a product from search results was added to cart.
  • quantity_added_to_cart (items)
    • Items added to cart from search results.

​
Dimensions

  • term: The search query string.
  • query_type: Type of search query.
  • num_results: Number of results returned.
  • shopping_channel: The shopping channel (e.g., online_store).
  • billable: Whether this request is counted for billing.
  • attribution_token: Token used to attribute downstream events/purchases.
  • experiment_id: Experiment identifier.
  • experiment_group: Experiment group/variant.
  • device: Device category.
  • os: Operating system.
  • geo_country: Two-letter country code.
  • geo_state: Up to three-letter province code.
  • geo_city: City name as captured.
  • marketing_source: UTM source.
  • marketing_medium: UTM medium.
  • marketing_campaign: UTM campaign.

​
Examples

Search requests by term (last 7 days) 
FROM search_text
SHOW COUNT_DISTINCT(requests)
GROUP BY term
SINCE -7d
Search revenue by country (this month) 
FROM search_text
SHOW SUM(total_sales)
GROUP BY geo_country
SINCE this_month
Top search terms with zero results 
FROM search_text
SHOW COUNT_DISTINCT(requests)
GROUP BY term
WHERE num_results = 0
SINCE -7d

​
Search (Image) dataset

  • Label: Search (Image)
  • Description: Image search analytics.
  • Default group key: image_hash

​
Metrics

  • requests (requests)
    • Number of image search requests.
    • Example: COUNT_DISTINCT(requests)
  • results_count (results)
    • Results returned per search request.
    • Example: AVG(results_count)
  • total_sales (USD)
    • Sales attributed to image search.
    • Example: SUM(total_sales)
  • quantity_purchased (items)
    • Number of items purchased from image search traffic.
    • Example: SUM(quantity_purchased)
  • view_sessions (sessions)
    • Sessions where a product was viewed from image search results.
  • cart_sessions (sessions)
    • Sessions where a product from image search results was added to cart.
  • quantity_added_to_cart (items)
    • Items added to cart from image search results.

​
Dimensions

  • image_hash: Hashed representation of the image query.
  • num_results: Number of results returned.
  • shopping_channel: The shopping channel (e.g., online_store).
  • billable: Whether this request is counted for billing.
  • attribution_token: Token used to attribute downstream events/purchases.
  • experiment_id: Experiment identifier.
  • experiment_group: Experiment group/variant.
  • device: Device category.
  • os: Operating system.
  • geo_country: Two-letter country code.
  • geo_state: Up to three-letter province code.
  • geo_city: City name as captured.
  • marketing_source: UTM source.
  • marketing_medium: UTM medium.
  • marketing_campaign: UTM campaign.

​
Examples

Image search requests by country (last 7 days) 
FROM search_image
SHOW COUNT_DISTINCT(requests)
GROUP BY geo_country
SINCE -7d
Image search conversion by device (this month) 
FROM search_image
SHOW SUM(total_sales)
GROUP BY device
SINCE this_month

​
Search (Similar) dataset

  • Label: Search (Similar)
  • Description: Similar item search analytics.
  • Default group key: product_id

​
Metrics

  • requests (requests)
    • Number of similar search requests.
    • Example: COUNT_DISTINCT(requests)
  • results_count (results)
    • Results returned per search request.
    • Example: AVG(results_count)
  • total_sales (USD)
    • Sales attributed to similar search.
    • Example: SUM(total_sales)
  • quantity_purchased (items)
    • Number of items purchased from similar search traffic.
    • Example: SUM(quantity_purchased)
  • view_sessions (sessions)
    • Sessions where a product was viewed from similar search results.
  • cart_sessions (sessions)
    • Sessions where a product from similar search results was added to cart.
  • quantity_added_to_cart (items)
    • Items added to cart from similar search results.

​
Dimensions

  • product_id: Product ID used as the similar search anchor.
  • query_type: Type of search query.
  • num_results: Number of results returned.
  • shopping_channel: The shopping channel (e.g., online_store).
  • billable: Whether this request is counted for billing.
  • attribution_token: Token used to attribute downstream events/purchases.
  • experiment_id: Experiment identifier.
  • experiment_group: Experiment group/variant.
  • device: Device category.
  • os: Operating system.
  • geo_country: Two-letter country code.
  • geo_state: Up to three-letter province code.
  • geo_city: City name as captured.
  • marketing_source: UTM source.
  • marketing_medium: UTM medium.
  • marketing_campaign: UTM campaign.

​
Examples

Similar search requests by product (last 7 days) 
FROM search_similar
SHOW COUNT_DISTINCT(requests)
GROUP BY product_id
SINCE -7d
Similar search revenue by country (this month) 
FROM search_similar
SHOW SUM(total_sales)
GROUP BY geo_country
SINCE this_month

​
Collections dataset

  • Label: Collections
  • Description: Analytics for collection browsing and conversions.
  • Default group key: collection_handle

​
Metrics

  • requests (requests)
    • Number of collection browse requests.
    • Example: COUNT_DISTINCT(requests)
  • total_sales (USD)
    • Sales attributed to collection browsing.
    • Example: SUM(total_sales)
  • quantity_purchased (items)
    • Number of items purchased from collection browsing.
    • Example: SUM(quantity_purchased)
  • view_sessions (sessions)
    • Sessions where a collection page was viewed.
  • cart_sessions (sessions)
    • Sessions where items from a collection browse were added to cart.
  • quantity_added_to_cart (items)
    • Items added to cart from collection browsing.

​
Dimensions

  • collection_handle: The collection handle.
  • collection_title: Resolved collection title.
  • collection_id: Resolved collection ID.
  • sort_order_id: The sort order ID used for the collection.
  • sort_order: The sort order applied.
  • rule_id: The merchandising rule applied.
  • applied_filters: Serialized applied filters on the browse request.
  • shopping_channel: The shopping channel (e.g., online_store).
  • attribution_token: Token used to attribute downstream events/purchases.
  • geo_country: Two-letter country code.
  • geo_state: Up to three-letter province code.
  • geo_city: City name as captured.
  • marketing_source: UTM source.
  • marketing_medium: UTM medium.
  • marketing_campaign: UTM campaign.
  • device: Device category.
  • os: Operating system.

​
Examples

Collection impressions by handle (last 7 days) 
FROM collections
SHOW COUNT_DISTINCT(requests)
GROUP BY collection_handle
SINCE -7d
Collection revenue by sort order (this month) 
FROM collections
SHOW SUM(total_sales)
GROUP BY sort_order_id
SINCE this_month
Collection performance by merchandising rule 
FROM collections
SHOW COUNT_DISTINCT(requests), SUM(total_sales)
GROUP BY rule_id
SINCE -7d

​
Dates (all datasets)

  • Relative offsets: -24h, -48h, -7d, -14d, -30d, -90d, -12m, -1y
  • Keywords: today, yesterday, this_week, last_week, this_month, last_month, this_quarter, last_quarter, this_year, last_year
  • Rolling windows: past_7_days, past_14_days, past_30_days, past_60_days, past_90_days, past_180_days, past_365_days
  • ISO timestamps: 2025-03-15 or 2025-03-15T10:30:00Z