Queries for metric and segment fields can be sent to the
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:
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.
SELECT clause specifies a set of fields to fetch in the request.
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’
Some fields are not allowed in the
SELECT clause due to the following
- Querying segment fields without at least one metric field.
Information related to the above condition can be found in our reference docs.
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.
FROM clause is required in a query to the
search method on 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
AND to separate them. Each condition should follow the pattern
field_name Operator value. Any segments field can be used in the
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
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 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
DESC. If neither
DESC is specified, the order defaults
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 clause allows you to specify the number of results to be returned.
This is useful if you're only interested in a summary.
LIMIT can be used to restrict the total number of results for the
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