Skip to main content
LayersQL provides seven datasets for building metrics:
  • products — product-level performance
  • sales — order-level analytics
  • search_text — text search analytics
  • search_image — image search analytics
  • search_similar — similar item search analytics
  • collections — collection browsing analytics
  • blocks — recommendation block 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
When you group search text metrics by term, query variants are automatically consolidated into a single canonical form. This includes:
  • Spelling and plural variations — searches for “charm”, “charms”, and “charmed” are grouped together under one entry.
  • Autocomplete prefixes — keystroke-by-keystroke terms that users type before reaching their final query (for example, “cro”, “cros”, and “cross”) are rolled up into the longest matching term.
When both apply, prefixes resolve through the cluster mapping. For instance, if “cross” maps to the canonical term “cross necklace”, then “cro” and “cros” are also attributed to “cross necklace”.After consolidation, results are re-sorted so the highest-volume terms appear first. This gives you a clearer picture of true search demand without noise from partial keystrokes, spelling variations, or plural forms.

​
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.
  • current_page: Current page index for paginated results (1-based).
  • 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

​
Blocks dataset

  • Label: Blocks
  • Description: Analytics for recommendation block requests and conversions.
  • Default group key: block_id
Use the blocks dataset to measure how your recommendation blocks perform — including how often they are requested, how many clicks and views they generate, and the sales they drive.

​
Metrics

  • requests (requests)
    • Number of block recommendation requests.
    • Example: COUNT_DISTINCT(requests)
  • total_sales (USD)
    • Sales after discounts and before taxes/returns attributed to block recommendations.
    • Example: SUM(total_sales)
  • quantity_purchased (items)
    • Number of items purchased from block recommendation traffic.
    • Example: SUM(quantity_purchased)
  • click_sessions (sessions)
    • Sessions where a product from block recommendations was clicked.
    • Example: COUNT_DISTINCT(click_sessions)
  • view_sessions (sessions)
    • Sessions where a product was viewed from block recommendations.
    • Example: COUNT_DISTINCT(view_sessions)
  • cart_sessions (sessions)
    • Sessions where a product from block recommendations was added to cart.
    • Example: COUNT_DISTINCT(cart_sessions)
  • quantity_added_to_cart (items)
    • Items added to cart from block recommendations.
    • Example: SUM(quantity_added_to_cart)

​
Dimensions

  • block_id: The block identifier.
  • block_title: Title of the block at request time.
  • anchor_type: Anchor type of the block (product, collection, cart, none).
  • strategy_type: Strategy type used (interaction, similar_products, manual).
  • strategy_key: Strategy key (e.g., recipe name for interaction strategy).
  • num_results: Number of results returned.
  • shopping_channel: The shopping channel (e.g., online_store).
  • attribution_token: Token used to attribute downstream events/purchases back to this block request.
  • device_type: Device category.
  • 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

Block requests by block (last 7 days) 
FROM blocks
SHOW COUNT_DISTINCT(requests)
GROUP BY block_id
SINCE -7d
Revenue attributed to blocks by strategy (this month) 
FROM blocks
SHOW SUM(total_sales)
GROUP BY strategy_type
SINCE this_month
Click-through and sales for a specific anchor type 
FROM blocks
SHOW COUNT_DISTINCT(click_sessions), SUM(total_sales)
GROUP BY block_id
WHERE anchor_type = 'product'
SINCE -30d
Block performance by country (past 30 days) 
FROM blocks
SHOW COUNT_DISTINCT(requests), SUM(total_sales)
GROUP BY geo_country
SINCE past_30_days

​
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