Query structure

  • The Merchant API is the new version of the Content API for Shopping and offers improvements to integrations.

  • Use Merchant Center Query Language to construct queries for metric and segment fields using clauses like SELECT, FROM, WHERE, ORDER BY, and LIMIT.

  • The WHERE clause is required in queries to specify conditions, including the mandatory date range for performance data retrieval.

  • ORDER BY allows arranging results in ascending or descending order based on selected fields.

  • LIMIT clause helps to specify the number of results to be returned, truncating the result set.

Queries for metric and segment fields can be sent to the reports.search method. To construct a query in the Merchant Center Query Language, you need to first compose the query using the language grammar. A query is made up of a number of clauses:

  • SELECT
  • FROM
  • WHERE
  • ORDER BY
  • LIMIT

Clauses use field names, table names, operators, conditions, and orderings to help you select the desired data. Once combined into a single query, a request can be made using the Google Content API for Shopping. Let's look at how each clause can be used.

Clauses

SELECT

The SELECT clause specifies a set of fields to fetch in the request. SELECT takes a comma-separated list of segment fields and metrics, returning the values in the response. The SELECT clause is required in a query.

Here is a sample query that selects click metrics from a given table:

SELECT
  metrics.clicks
FROM MerchantPerformanceView
WHERE segments.date BETWEEN ‘2020-08-01’ AND ‘2020-08-31’

You can also query different field types in a single request:

SELECT
  segments.date,
  segments.program,
  metrics.impressions,
  metrics.clicks
FROM MerchantPerformanceView
WHERE segments.date BETWEEN ‘2020-08-01’ AND ‘2020-08-31’

  • Segment fields

    • segments.date
    • segments.program

  • Metrics

    • metrics.impressions
    • metrics.clicks

Some fields are not allowed in the SELECT clause due to the following restriction:

  • Querying segment fields without at least one metric field.

Information related to the above condition can be found in our reference docs.

FROM

The FROM clause specifies the table to fetch data from in the request. The table in the FROM clause defines what fields can be used by all of the other clauses for the given query. Only a single table can be specified in the FROM clause. Currently, only the MerchantPerformanceView table is supported. The FROM clause is required in a query to the search method on the reports service.

WHERE

The WHERE clause specifies conditions to apply when filtering data for the request. When using the WHERE clause, one or more conditions can be specified using AND to separate them. Each condition should follow the pattern field_name Operator value. Any segments field can be used in the WHERE clause, but metrics fields need to be specified in the SELECT clause to be used in the WHERE clause. The WHERE clause is required in a query, because you must always specify the date range for when you want your performance data returned.

The following is an example of using WHERE to return metrics from a given time period:

SELECT
  segments.offer_id,
  metrics.impressions
FROM MerchantPerformanceView
WHERE segments.date BETWEEN ‘2020-08-01’ AND ‘2020-08-31’

You can combine multiple conditions to filter the data. This example will return the number of clicks per offer for the SHOPPING_ADS program where the clicks > 100 within the given 30-day period.

SELECT
  segments.offer_id,
  segments.program,
  metrics.clicks
FROM MerchantPerformanceView
WHERE metrics.clicks > 100
  AND segments.program = SHOPPING_ADS
  AND segments.date BETWEEN ‘2020-08-01’ AND ‘2020-08-31’;

In the following query, you'll notice that segments.date was selected. Regardless about whether you select segments.date, a finite date range in the WHERE clause always has to be provided to retrieve performance data.

SELECT
  segments.date,
  metrics.clicks
FROM MerchantPerformanceView
WHERE segments.date BETWEEN ‘2020-08-01’ AND ‘2020-08-31’

When filtering, the case-sensitivity of your operator is important to keep in mind.

For a complete list of operators, consult the language grammar.

ORDER BY

The ORDER BY clause specifies the order in which the results are to be returned. This allows you to arrange the data in ascending or descending order based on a field name. Each ordering is specified as a field_name followed by ASC or DESC. If neither ASC nor DESC is specified, the order defaults to ASC. Only fields specified in the SELECT clause can be used in the ORDER BY clause. The ORDER BY clause is optional in a query.

The following query orders the returned rows by number of clicks from highest to lowest:

SELECT
  segments.offer_id,
  metrics.clicks
FROM MerchantPerformanceView
WHERE segments.date BETWEEN ‘2020-08-01’ AND ‘2020-08-31’
ORDER BY metrics.clicks DESC

You can specify multiple fields in the ORDER BY clause using a comma-separated list. The ordering will occur in the same sequence as specified in the query. For example, in this query, the results will be sorted in ascending order by offer_id, then in descending order by number of impressions, then in descending order by number of clicks:

SELECT
  segments.offer_id,
  metrics.impressions,
  metrics.clicks
FROM MerchantPerformanceView
WHERE segments.date BETWEEN ‘2020-08-01’ AND ‘2020-08-31’
ORDER BY
  segments.offer_id,
  metrics.impressions DESC,
  metrics.clicks DESC

LIMIT

The LIMIT clause allows you to specify the number of results to be returned. This is useful if you're only interested in a summary.

For example, LIMIT can be used to restrict the total number of results for the following query:

SELECT
  segments.program,
  segments.offer_id,
  metrics.impressions
FROM MerchantPerformanceView
WHERE segments.date BETWEEN ‘2020-08-01’ AND ‘2020-08-31’
ORDER BY metrics.impressions DESC
LIMIT 50
Previous
Query grammar
Next
Date ranges