search
🧑‍💻 Task Library⚙️ Get Mechanic
githubEdit

Query analytics data with ShopifyQL

Analytics is a powerful reporting tool available to all merchants on Shopify. Mechanic can utilize the same ShopifyQL queries that generate those reports.

This tutorial will demonstrate the basic concepts on how to use ShopifyQLarrow-up-right queries in Mechanic tasks to query Shopify analytics data.

circle-info

Review this tutorial in conjunction with the Demonstration: Query analytics data with ShopifyQLarrow-up-right task.

ShopifyQL queries in Mechanic work essentially the same as other GraphQL queries, by capturing a GraphQL query and passing it to the Shopify Admin API using the shopify Mechanic Liquid filter, and then immediately accessing the query results in the same task run.

A key difference is that there will be an additional query embedded within the shopifyqlQueryarrow-up-right query structure. This embedded query holds the ShopifyQL syntaxarrow-up-right.

hashtag
Basic structure of a ShopifyQL query task

{%- capture shopifyql_query -%}
  FROM sales
  SHOW net_items_sold, gross_sales, discounts, returns, net_sales, taxes, total_sales
  WHERE product_vendor IS NOT NULL
  GROUP BY product_vendor
  DURING last_year
  ORDER BY total_sales DESC
  LIMIT 1000
{%- endcapture -%}

{% capture query %}
  {
    shopifyqlQuery(query: {{ shopifyql_query | json }}) {
      tableData {
        columns {
          displayName
          name
          dataType
          subType
        }
        rows
      }
      parseErrors
    }
  }
{% endcapture %}

{% assign result = query | shopify %}

{% assign rows = result.data.shopifyqlQuery.tableData.rows %}
{% assign columns = result.data.shopifyqlQuery.tableData.columns %}

{% log rows_count: rows.size %}
{% log rows: rows %}
{% log columns: columns %}
circle-info

Use another Liquid capture block for the ShopifyQL syntax, in lieu of trying to embed it directly within the outer GraphQL query. You may also use a multiline task options field to hold the ShopifyQL syntax.

circle-info

The columns field is mostly useful when developing your custom task, to see the field names and types returned by the query. The rows data returned in the query will contain the field names and data as key value pairs in each row object, so the columns field is technically not needed to work with the query results.

hashtag
Example ShopifyQL query result (JSON)

The rows object will contain all of the data you need from the query. Running the task once during initial development to log out the rows result will give you the data field names you can work with.

From this point, the entire arsenal of features available in Mechanic can be used to do whatever you'd like with the data. Tag customers, orders, or products. Send data to Airtable, Google Sheets, or Slack. Generate a CSV file and send via email or upload to an FTP server. Etc.

hashtag
Some additional development tips

  • Review the existing reports available in Analytics to get ideas on how you might use ShopifyQL queries in automations

  • Use Sidekick (Shopify Analytics -> Reports -> New exploration) to help you generate and validate ShopifyQL syntax

  • Clean up ShopifyQL queries for use by Mechanic:

    • Remove the VISUALIZE line (this has no effect for data retrieval)

    • When using a GROUP BY clause, generally you can safely remove the WITH TOTALS qualifier, as the summed totals across groups is often not useful

    • If expecting more than 1000 rows of data, then add a LIMIT line with a value greater than needed

PreviousFetching data from a shared Google sheetNextConverting tasks from Shopify REST to GraphQL

Last updated

Was this helpful?