Comparison Shopping Service Center Transfer

The BigQuery Data Transfer Service for the Comparison Shopping Service (CSS) Center lets you automatically schedule and manage recurring load jobs for CSS Center reporting data.

Supported reports

The BigQuery Data Transfer Service for the CSS Center supports the following data from the product and product issues reports of associated Merchant Center accounts.

Products and product issues

This report contains data that merchants associated with your CSS Center have uploaded to their Merchant Center accounts. This report also includes item level issues detected by Google for your merchants' products. For information on how this data is loaded into BigQuery, see the CSS Center product table schema.

Data ingestion from CSS Center transfers

Limitations

Some reports might have their own constraints, such as different windows of support for historical backfills. The products and product issues report does not support backfills.

Products and product issues data in BigQuery doesn't represent the real-time view of Merchant Center accounts associated with your CSS Center account. The products and product issues data in BigQuery can have a latency of up to one hour.

Data exported for a CSS Center account will only contain information about the merchants who have agreed to share their information with their associated CSS. For more information, see How a CSS can access your Merchant Center account.

CSS Center data access and authorization

A user of a CSS Center can only access information from Merchant Center accounts based on the level of access provided to that user by the Merchant Center account. As a result, a CSS Center transfer only includes merchant data that a user of the CSS Center has access to. For more information, see How a CSS can access your Merchant Center account.

You can configure the access rights of a CSS user by configuring the user's access in the CSS Center as a CSS admin.

Query your data

When your data is transferred to BigQuery, the data is written to ingestion-time partitioned tables.

When you query your CSS 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.

CSS Center sample queries

You can use the following CSS 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 css_id with your CSS domain ID.

Products and product issues sample queries

The following queries analyze data from the products and products issues report.

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(item_issues) > 0) AS num_products_with_issues,
  SUM(ARRAY_LENGTH(item_issues)) AS num_issues
FROM
  dataset.Products_css_id
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD'
GROUP BY
  date
ORDER BY
  date DESC;

Disapproved products

The following SQL sample query provides the number of products that are not approved for display, separated by region and reporting context. Disapproval can result from the reporting context being excluded or because of an issue with the product.

SELECT
  _PARTITIONDATE AS date,
  statuses.region as disapproved_region,
  reporting_context_status.reporting_context as reporting_context,
  COUNT(*) AS num_products
FROM
  dataset.Products_css_id,
  UNNEST(reporting_context_statuses) AS reporting_context_status,
  UNNEST(reporting_context_status.region_and_status) AS statuses
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD' AND statuses.status = 'DISAPPROVED'
GROUP BY
  date, disapproved_region, reporting_context
ORDER BY
  date DESC;

Products with disapproved issues

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

SELECT
  _PARTITIONDATE AS date,
  disapproved_region,
  COUNT(DISTINCT CONCAT(CAST(css_id AS STRING), ':', product_id))
      AS num_distinct_products
FROM
  dataset.Products_css_id,
  UNNEST(item_issues) AS issue,
  UNNEST(issue.severity.severity_per_reporting_context) as severity_per_rc,
  UNNEST(severity_per_rc.disapproved_regions) as disapproved_region
WHERE
  _PARTITIONDATE >= 'YYYY-MM-DD'
GROUP BY
  date, disapproved_region
ORDER BY
  date DESC;