Get Bestseller list from Merchant Center with Content API?

[Jakob Bisgaard Jensen]

Hi,

We have a Multi Client Account in Google Merchant Center.  We are interested in accessing the Bestseller report via the Content API.

I have succeeded in connecting to the Content API in a python script as follows:

   KEY = "content-api-key.json"

   creds = service_account.Credentials.from_service_account_file(KEY)

   service = build('content', 'v2.1', credentials=creds)

   resp = service.products().list(merchantId=360508761).execute()

   print(resp)

This API call works and I get a response with product details on a set of products from our site.

I've read the documentation on segments,  fields and the resources for the API. However, I do not see any options to retrieve the Bestseller list via the Content API. 

Is this true, or is there a way to retrieve the Bestseller with the Content API?

I hope some of you can clarify on this, and please let me know if you've succeeded with retrieving this list.

Thank you,

Jakob 

[Hans Wassink]

The Bestsellers list is HUGE, gigabytes of data every day, you don't want to use the (slow) Content API to access that.

We use the BigQuery Datatransfer service to transfer the data into BigQuery daily, it makes your life way easier. It's fast, easy and cheap. https://cloud.google.com/bigquery/docs/merchant-center-transfer .

From that point on we just get the data we need from BigQuery, which works easier and more versatile than using the Content API.

On a sidenote: you have your merchantId in the script, which is sensitive information.

On sidenote 2: clear your bestsellers data every few days, because storage in bigquery is not that expensive, but if you keep it for months and months it starts to become expensive :D

Greetings,

Hans

Op dinsdag 23 augustus 2022 om 14:01:58 UTC+2 schreef j...@grouplogistic.dk:

[Shopping API Forum Advisor]

Hi all,

Currently, there is no such feature to get the best sellers list via Content API. I filed a feature request for this concern. Please keep an eye on our blog for future announcements: https://ads-developers.googleblog.com/search/label/content_api

Thanks,

Lakshmi Content API for Shopping Team

ref:_00D1U1174p._5004Q2dmfAW:ref

[Jakob Bisgaard Jensen]

@Lakshmi

Thank you for filing a request feature.

@Hans

Thanks for the tips! Can you automate your query in BigQuery to e.g. run once each week? And can you automate exports from BigQuery to e.g. Google Sheets, Onedrive, or Google cloud?

I am looking for at setup to automatically retrieve the Bestseller list, format it, and export it to relevant recipients.

Best, Jakob

[Hans Wassink]

Hi Jakob,

Yes you can, it's quite easy, I would do it like this I think:

1. Create transfer at https://console.cloud.google.com/bigquery/transfers?authuser=1 and create a transfer for Google Merchant Center with your merchant_id / schedule:weekly
2. Once that data is the BigQuery table you can create a Cloud function that gathers the data in BigQuery and sends it to what / whoever you need in the prgramming lkanguage of your choice.
   
3. Schedule that cloud function using Cloud Scheduler (I would set it 2 hours after you schedule the transfer, I sometimes notice some lag).

A sample query for your cloud function (This gets the top 10 products in NL and FR with English titles split by vertical):

SELECT

  rank,

  ranking_country,

  brand,

  product_title[OFFSET(0)] AS product_title,

  ranking_category,

  rcp.name AS full_path,

  SPLIT(rcp.name, ' > ')[OFFSET(0)] main_category

FROM

  `YOURPROJECT.YOURDATASET.BestSellers_TopProducts_YOURMERCHANT_ID`

  LEFT JOIN UNNEST(ranking_category_path) AS rcp ON rcp.locale = 'en-GB'

WHERE

  DATE(_PARTITIONTIME) = "2022-08-24"

  AND rank < 10

  AND ranking_country IN ('NL', 'FR')

ORDER BY

  ranking_category ASC,

  rank ASC

Greetings,

Hans

Op woensdag 24 augustus 2022 om 08:58:21 UTC+2 schreef j...@grouplogistic.dk:

[Hans Wassink]

Sidenote: Something that noone really ever talks about is the insane amount of information in the Bestsellers report, the `BestSellers_TopProducts_123456789` table for today was 41,7GB. Have fun! :D

Op woensdag 24 augustus 2022 om 11:12:00 UTC+2 schreef Hans Wassink:

[Jakob Bisgaard Jensen]

Hi Hans,

Thank you for this valuable information and example! Much appreciated.

I understand why you don't want to use the Content API for that amount of data.

Best,

Jakob