Google Merchant Center Transfers

Preview

This product is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Note: To get support or provide feedback for Google Merchant Center transfers with BigQuery Data Transfer Service, contact [e-mail].

The BigQuery Data Transfer Service for Google Merchant Center lets you automatically schedule and manage recurring load jobs for Google Merchant Center reporting data.

Supported reports

The BigQuery Data Transfer Service for Google Merchant Center supports the following data:

Products and product issues

The products and product issues report includes product data provided to Merchant Center through feeds or using the Content API for Shopping. This report also includes item level issues detected by Google for your products. You can view product and product issues data in the Google Merchant Center or by querying the Content API for Shopping. For information on how this data is loaded into BigQuery, see the Google Merchant Center product table schema.

Regional Inventories

The regional inventories report includes additional product data about regional availability and pricing overrides of your products.For information on how this data is loaded into BigQuery, see the Google Merchant Center regional inventories table schema.

Local Inventories

The local inventories report includes additional product data about local inventory of your products. This report contains data on local pricing, availability, quantity, pick-up and in-store product location. For information on how this data is loaded into BigQuery, see the Google Merchant Center local inventories table schema.

Performance

The performance report provides granular segmentation of your performance data across both Ads and Free Listings. For information on how this data is loaded into BigQuery, see the Google Merchant Center performance table schema.

Best Sellers

The Best sellers report provides the same data found in the Google Merchant Center UI and lets you backfill the data across countries or categories for up to 2 years. This includes data about the most popular products and brands in Shopping ads and unpaid listings, as well as whether or not you have them in your inventory. This report is based on the best sellers report available through Google Merchant Center. For information on how this data is loaded into BigQuery, see the Google Merchant Center best sellers tables schemas.

Price Competitiveness

Formerly known as the price benchmarks report, the price competitiveness report includes product level attributes and price benchmark data and is based on the same definitions as the price competitiveness report available through Google Merchant Center. For information on how this data is loaded into BigQuery, see the Google Merchant Center price competitiveness table schema.

Price Insights

Use the price insights report to see suggested sale prices for your products, and predictions for the performance that you can expect if you update your products' prices. Using the price insights report can help you price your products more effectively. For more information on how to use the data in this report, see Improve product pricing with the price insights report for more information on how to use the data in this report. For information on how this data is loaded into BigQuery, see the Google Merchant Center Price Insights table schema.

Product Targeting

Enable the Product Targeting report when you set up a transfer to expose Ads targeting information when you load data from Google Shopping into BigQuery. For information on how the data is loaded into BigQuery, see the Google Merchant Center Product Targeting table schema.

Reporting options

The BigQuery Data Transfer Service for Google Merchant Center supports the following reporting options:

Reporting option	Support
Schedule	Configurable to daily, weekly, monthly, or custom. By default, this is set to daily when the transfer is created. The minimum interval between transfers is 6 hours.

Data ingestion from Google Merchant Center transfers

When you transfer data from Google Merchant Center into BigQuery, the data is loaded into BigQuery tables that are partitioned by date. The table partition that the data is loaded into corresponds to the date from the data source. If you schedule multiple transfers for the same date, BigQuery Data Transfer Service overwrites the partition for that specific date with the latest data. Multiple transfers in the same day or running backfills don't result in duplicate data, and partitions for other dates are not affected.

Limitations

Some reports might have their own constraints, such as different windows of support for historical backfills. The following sections describe the limitations for each report.

Historical Backfills Support

Not all reports support historical backfills in the same way. The following are a list of reports and the level of support for historical backfills.

• Products and product issues - 14 days
• Local Inventories - 14 days
• Regional Inventories - 14 days
• Performance - 2 years
• Best sellers - 2 years
• Price competitiveness - No backfill support
• Price insights - No backfill support

Automatic Backfill Transfer Runs

The Performance report can have latencies in "today's" data. Therefore, when a data export is requested, data might update up to 3 days in the past to account for corrections.

To support this functionality, whenever a transfer is triggered on any report, two more transfer runs are created for today - 1 and today - 2. These transfer runs only affect the Performance table; other tables are not impacted.

The automatic backfills can't be disabled.

Product and product issues

• Products and product issues data in BigQuery does not represent the real-time view of your Merchant account. To see the timeline of your data, refer to the product_data_timestamp field in the schema.

Price benchmarks

• Price benchmarks data does not represent the real-time view of price benchmarks. To see the timeline of your data, refer to the price_benchmark_timestamp field in the Price Benchmarks schema.
• Not all products will have a price benchmark.

Best sellers

• Not all countries will contain ranking data. Countries included in the report (using ISO 3166-1 alpha-2): AT, AU, BE, BR, CA, CH, CZ, DE, DK, ES, FI, FR, GB, HU, IE, IN, IT, JP, MX, NL, NO, NZ, PL, PT, RO, RU, SE, SK, TR, UA and US.
• Best sellers data does not represent a real-time view of brand rankings. To see the timeline of your data, refer to the rank_timestamp field in the BestSellers_TopProducts_ and BestSellers_TopBrands_ schema.
• There may be rows in the Products_ table with a google_brand_id that does not map to any brands in the BestSellers_TopBrands_ table.
• Brand IDs in the google_brand_id field might change over time to reflect the changing nature of businesses and brands. Because of this, tracking a set of brands over a long period of time is not guaranteed.
• While brands are ranked across many different categories, all products in the Products_ table are in leaf categories. To join brands and products on non-leaf categories, use the google_product_category_ids field, as shown in Best sellers sample queries.

Note: For more information about product categories, including a full list of category codes, see Definition: google_product_category.

Multi-client account (MCA) support

Existing customers with multiple Merchant IDs are encouraged to configure a parent Multi-Client Account (MCA). Configuring an MCA lets you create a single transfer for all your Merchant IDs.

Using Google Merchant Center MCAs provides several benefits over using individual Merchant IDs:

• You no longer need to manage multiple transfers to transfer reporting data for multiple Merchant IDs.
• Queries involving multiple Merchant IDs are much simpler to write because all Merchant ID data is stored in the same table.
• Using MCAs alleviates potential BigQuery load job quota issues because all your Merchant ID data is loaded in the same job.

One possible disadvantage of using MCAs is that your subsequent query costs are likely to be higher. Because all of your data is stored in the same table, queries that retrieve data for an individual Merchant ID must still scan the entire table.

Note: The BigQuery Data Transfer Service pulls reports for all listed Merchant IDs. If there are no products in Google Shopping for a specific day, you may not see Merchant IDs in the BigQuery table.

If you are using an MCA, the MCA ID is listed under aggregator_id and the individual sub-accounts are listed under merchant_id. For accounts that don't use an MCA, aggregator_id is set to null.

Not all reports support MCAs. MCAs are supported by the following reports:

• Products and product issues
• Local Inventories
• Regional Inventories
• Product Performance
• Price Competitiveness
• Price Insights
• Product Targeting

Query your data

When your data is transferred to BigQuery, the data is written to ingestion-time partitioned tables. For more information, see Introduction to partitioned tables.

When you query your Google Merchant Center table, you must use the _PARTITIONTIME or _PARTITIONDATE pseudocolumn in your query. For more information, see Querying partitioned tables.

The Products_ table contains nested and repeated fields. For information on handling nested and repeated data, see Differences in repeated field handling in the GoogleSQL documentation.

Google Merchant Center sample queries

You can use the following Google Merchant Center sample queries to analyze your transferred data. You can also use the queries in a visualization tool such as Looker Studio.

In each of the following queries, replace dataset with your dataset name. Replace merchant_id with your Merchant ID. If you're using an MCA, replace merchant_id with your MCA ID.

Products and product issues sample queries

Products and product issues statistics

The following SQL sample query provides the number of products, products with issues, and issues by day.

SELECT
  _PARTITIONDATE AS date,
  COUNT(*) AS num_products,
  COUNTIF(ARRAY_LENGTH(issues) > 0) AS num_products_with_issues,
  SUM(ARRAY_LENGTH(issues)) AS num_issues
FROM
  dataset.Products_merchant_id
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD'
GROUP BY
  date
ORDER BY
  date DESC

Products disapproved for Shopping Ads

The following SQL sample query provides the number of products that are not approved for display in Shopping Ads, separated by country. Disapproval can result from the destination being excluded or because of an issue with the product.

SELECT
  _PARTITIONDATE AS date,
  disapproved_country,
  COUNT(*) AS num_products
FROM
  dataset.Products_merchant_id,
  UNNEST(destinations) AS destination,
  UNNEST(disapproved_countries) AS disapproved_country
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD'
GROUP BY
  date, disapproved_country
ORDER BY
  date DESC

Products with disapproved issues

The following SQL sample query retrieves the number of products with disapproved issues, separated by country.

SELECT
  _PARTITIONDATE AS date,
  applicable_country,
  COUNT(DISTINCT CONCAT(CAST(merchant_id AS STRING), ':', product_id))
      AS num_distinct_products
FROM
  dataset.Products_merchant_id,
  UNNEST(issues) AS issue,
  UNNEST(issue.applicable_countries) as applicable_country
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD' AND
  issue.servability = 'disapproved'
GROUP BY
  date, applicable_country
ORDER BY
  date DESC

Note: This query constructs a unique key by using merchant_id and product_id. This is only required if you have an MCA account. When you use an MCA account, there is the potential for product_id collisions across multiple sub-accounts.

Price benchmark sample queries

Compare product prices to benchmarks

The following SQL query joins Products and Price Benchmarks data to return the list of products and associated benchmarks.

WITH products AS
(
  SELECT
    _PARTITIONDATE AS date,
    *
  FROM
    dataset.Products_merchant_id
  WHERE
   _PARTITIONDATE >= 'YYYY-MM-DD'
),
benchmarks AS
(
  SELECT
    _PARTITIONDATE AS date,
    *
  FROM
    dataset.Products_PriceBenchmarks_merchant_id
  WHERE
    _PARTITIONDATE >= 'YYYY-MM-DD'
)
SELECT
  products.date,
  products.product_id,
  products.merchant_id,
  products.aggregator_id,
  products.price,
  products.sale_price,
  benchmarks.price_benchmark_value,
  benchmarks.price_benchmark_currency,
  benchmarks.country_of_sale
FROM
  products
INNER JOIN
  benchmarks
ON products.product_id = benchmarks.product_id AND
   products.merchant_id = benchmarks.merchant_id AND
   products.date = benchmarks.date

Notes on joining the data:
1. Not all products have benchmarks, so use INNER JOIN or LEFT JOIN accordingly.
2. Each product may have multiple benchmarks (one per country).

Best sellers sample queries

Top products for a given category and country

The following SQL query returns top products for the "Smartphones" category in the US.

SELECT
  rank,
  previous_rank,
  relative_demand.bucket,
  (SELECT name FROM top_products.product_title WHERE locale = 'en-US') AS product_title,
  brand,
  price_range
FROM
  dataset.BestSellers_TopProducts_merchant_id AS top_products
WHERE
  _PARTITIONDATE = 'YYYY-MM-DD' AND
  ranking_category = 267 /*Smartphones*/ AND
  ranking_country = 'US'
ORDER BY
  rank

Note: For more information about product categories, including a full list of category codes, see Definition: google_product_category.

Top products in your inventory

The following SQL query joins BestSellers_TopProducts_Inventory_ and BestSellers_TopProducts_ data to return a list of top products you have in your inventory.

WITH latest_top_products AS
(
  SELECT
    *
  FROM
    dataset.BestSellers_TopProducts_merchant_id
  WHERE
    _PARTITIONDATE = 'YYYY-MM-DD'
),
latest_top_products_inventory AS
(
  SELECT
    *
  FROM
    dataset.BestSellers_TopProducts_Inventory_merchant_id
  WHERE
    _PARTITIONDATE = 'YYYY-MM-DD'
)
SELECT
  top_products.rank,
  inventory.product_id,
  (SELECT ANY_VALUE(name) FROM top_products.product_title) AS product_title,
  top_products.brand,
  top_products.gtins
FROM
  latest_top_products AS top_products
INNER JOIN
  latest_top_products_inventory AS inventory
USING (rank_id)

Top brands for a given category and country

The following SQL query returns top brands for the "Smartphones" category in the US.

SELECT
  rank,
  previous_rank,
  brand
FROM
  dataset.BestSellers_TopBrands_merchant_id
WHERE
  _PARTITIONDATE = 'YYYY-MM-DD' AND
  ranking_category = 267 /*Smartphones*/ AND
  ranking_country = 'US'
ORDER BY
  rank

Note: For more information about product categories, including a full list of category codes, see Definition: google_product_category.

Products of top brands in your inventory

The following SQL query returns a list of products in your inventory from top brands, listed by category and country.

  WITH latest_top_brands AS
  (
    SELECT
      *
    FROM
      dataset.BestSellers_TopBrands_merchant_id
    WHERE
      _PARTITIONDATE = 'YYYY-MM-DD'
  ),
  latest_products AS
  (
    SELECT
      product.*,
      product_category_id
    FROM
      dataset.Products_merchant_id AS product,
      UNNEST(product.google_product_category_ids) AS product_category_id,
      UNNEST(destinations) AS destination,
      UNNEST(destination.approved_countries) AS approved_country
    WHERE
      _PARTITIONDATE = 'YYYY-MM-DD'
  )
  SELECT
    top_brands.brand,
    (SELECT name FROM top_brands.ranking_category_path
    WHERE locale = 'en-US') AS ranking_category,
    top_brands.ranking_country,
    top_brands.rank,
    products.product_id,
    products.title
  FROM
    latest_top_brands AS top_brands
  INNER JOIN
    latest_products AS products
  ON top_brands.google_brand_id = products.google_brand_id AND
     top_brands.ranking_category = product_category_id AND
     top_brands.ranking_country = products.approved_country

Note: For more information about product categories, including a full list of category codes, see Definition: google_product_category.