Use Google Content API for Shopping Site – 1

Google API

One of our e-commerce customers just built/upgraded their new business websites to sell thousands of merchandise items. They have multiple shopping sites build on top of Shopify and WooCommerce platforms. Initially they used 3rd party plugin and several commercial solutions to publish and manage their merchandise to Google Ads via Google Merchant Center.

Since there are so many internal data/process integration need, they decide to use programming way such as Google Content API to manage their goods inventory as well as building the pipe to Google shopping center to improve their business performance. Here are highlights to implement this:

Programming/Knowledge Need: Python (Medium), SQL (Advanced), API (Medium)

References:

Step 1, Set the Google API development environment. This includes creating Google Merchant Center account, enable Google Content API etc. The above two documents will guide perfectly on this step.

Step 2, Setup backend database connection (we use Woocommerce+Wordpress MySQL as sample) and create necessary additional backend database objects. This maybe the most challenging step in the whole projects, since we have to understand where and how the records are stored and linked: Product Title, Pricing (regular price and sales price), Images, Attributes (color, weight, size), shipping class, tax class etc. so many things.

  • wp_posts: the master table of the products
  • wp_postmeta: the master table of the products attributes
  • wp_termmeta, wp_term… the tables stored customized variable of the products
  • wp_woocommerce_shipping_* tables, stored the shipping class information of the products. Google Merchant Center need the shipping information as mandatory, so the records in these tables will be involved.

To use those tables properly we create several Views/Stored Procedures/Staging Tables so that the Python code can use the MySQL records easily. For example this view query the primary records for product that has multiple customized variables:

CREATE OR REPLACE view vw_Main_Product
AS 
WITH a AS
(
SELECT
    p.id,
    p.post_title as title,
    IFNULL(p.post_excerpt,p.post_title) as description,
    concat('https://oaktreetools.com/?product=',p.post_name) as link,
    p1.guid as imageLink
FROM 
    wp_posts p 
INNER JOIN
    wp_postmeta pm on p.id = pm.post_id
INNER JOIN
    vw_alt_posts p1 on pm.meta_value = p1.id
WHERE
    p.post_type = 'product' and pm.meta_key = '_thumbnail_id'
),
d AS (
SELECT
    c.id as offerId,
    c.title,
    c.description,
    c.link,
    c.imageLink,
    concat(c.id,'-',c.sub_id) as mpn,
    max(case when c.meta_key = '_regular_price' then c.meta_value end) price,
    max(case when c.meta_key = '_price' then c.meta_value end) salePrice,
    max(case when c.meta_key = '_weight' then c.meta_value end) shippingWeight,
    max(case when c.meta_key = '_length' then c.meta_value end) shippingLength,
    max(case when c.meta_key = '_height' then c.meta_value end) shippingHeight,
    max(case when c.meta_key = '_width' then c.meta_value end) shippingWidth
FROM vw_product_pricing 
GROUP BY c.id,c.title,c.sub_id,c.description,c.link,c.imageLink
)
SELECT 
    d.offerId,
    concat(d.title,' ',f.sub_title) as full_title,
    d.title as title,
    d.description,
    d.link,
    d.imageLink,
    d.mpn,
    d.price,
    d.salePrice,
    IFNULL(d.shippingWeight,0) shippingWeight,
    IFNULL(d.shippingLength,0) shippingLength,
    IFNULL(d.shippingHeight,0) shippingHeight,
    IFNULL(d.shippingWidth,0) shippingWidth
FROM d
LEFT JOIN f on substr(d.mpn)= f.post_id
;

Step 3, The Python samples that the Reference 2 provided can be a fundamental library that we will use. The sample provides a couple of hardcoding records for testing purpose. We only need replace the hardcoding records by dynamic records that populated from MySQL. For example:

import mysql.connector
from shopping.content import _constants
from bridata_connector import mysql_wp1 as mydb

....
def get_product(post_id,mpn,simple_flag):
    if simple_flag == None:
        sql1 = "SELECT full_title as title,description,link,imageLink,mpn FROM vw_Main_Product where offerid = " + post_id + " and mpn = '" + mpn + "'"
    else:
        # the simple_flag (in vw_gcp) used to make the title as simple without title + attribute combination
        sql1 = "SELECT title,description,link,imageLink,mpn FROM vw_gcp where offerid = " + post_id + " and mpn = '" + mpn + "'"

    cusor = mydb.cursor(buffered=True,dictionary=True)  #basic record
    cusor2 = mydb.cursor(buffered=True)  #additional images
....
  • The Google Content API references provides detailed JSON format that the API needs, the elements in JOSN have different data types (dictionary, list, array etc.) which need the Python scripts to pass it correctly.
  • The Sample of Python scripts have two different methods to populate the products: single product and batch process. Obviously the batch process will be productive in real situations: Save API call time/cost especially when updating huge amount of products.
  • Obviously, we should add filter in either SQL views or Python scripts to select the products we want to update/update

After above codes executed properly, we should be able to see selected products have been added into Google Merchant Center, but there are several things that we should realize that this process has some unique characteristics:

  • The Data Feed was automatically added when calling Google Content API, the Default name is ‘Content API’ (only one API source is supported), moreover once we delete this source manually all products that populated by this API will be gone. Screen shot as below:
  • The API uploaded products cannot be deleted manually, we have to use API to delete it. The deleting script was also provided by Reference 2.
  • Another place we can also see the source is ‘API’ is in the single product page, and it also tells the we cannot delete it from here.

Once we publish the product, we should be able to see it from Google Shopping tab (after several hours) if there is no error (no review need). The next topic we should consider about the schedule our API execution so that everything is automate!

— Continue