WooCommerce - Datamodel

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen

The datamodel of WooCommerce and how to manipulate stuff through SQL. This article functions as a data snippet repository.

Orders & order lines

An impression. There are considerably more fields available, but this goes a long way. Also note the self-joins:

################################################################################
# Get quite some order data
################################################################################
#
select
    ID,
    post_date,
    post_type,
    post_status,
    wp_woocommerce_order_items.order_item_id,
    wp_woocommerce_order_items.order_item_type,
    wp_woocommerce_order_items.order_item_name,
    itemmeta_01.meta_value                      as sellprice_ex_vat,
    itemmeta_02.meta_value                      as product_post_id,
    wp_postmeta.meta_value                      as sku

from
    # wp_posts: Basic order stuff
    ########################################
    #
    wp_posts

join
    # wp_woocommerce_order_items: Various
    ########################################
    #
    wp_woocommerce_order_items
    on
    wp_posts.ID = wp_woocommerce_order_items.order_id

join
    # wp_woocommerce_order_items (1): sellprice
    ########################################
    #
    # First of several self-joins
    #
    wp_woocommerce_order_itemmeta as itemmeta_01
    on
    wp_woocommerce_order_items.order_item_id = itemmeta_01.order_item_id

join
    # wp_woocommerce_order_items (2): product_id
    ########################################
    #
    # Product_id is used for retrieving sku further on
    #
    wp_woocommerce_order_itemmeta as itemmeta_02
    on
    wp_woocommerce_order_items.order_item_id = itemmeta_02.order_item_id

join
    # wp_postmeta: sku
    ########################################
    #
    # Finally: Here's the sku!
    #
    wp_postmeta
    on
    wp_postmeta.post_id = itemmeta_02.meta_value

where
    post_type = "shop_order"
    and
    post_status = "wc-completed"
    and
    itemmeta_01.meta_key = "_line_subtotal"
    and
    itemmeta_02.meta_key = "_product_id"
    and
    wp_postmeta.meta_key = "_sku";

Revenue per month

#
# Read-out monthly revenue figures webshops server dvb12
################################################################################
#
#
# Output
########################################
#
# * First figure: "Sales" - "Verkoopbedrag"
# * Second figure: "Revenue" - "Omzet"
#
#
# Recommendations
########################################
#
# * Develop similar script for sites on server "dvb13"
# * Use the same associate array loop as now used for site maintenance 
#   + use WP-CLI as SQL-wrapper
#
#
################################################################################
# Exchange rates
################################################################################
#
# * Value of 1 unit expressed in euros
# * Use 3 significant numbers
# * Date is the month of the most recent update of the exchange rate
# * The second date was another recent update: To get an impression of
#   fluctuations
# * Quickest way to update exchange rates (so far): Just go to
#   https://www.xe.com/currencyconverter/convert/?Amount=1&From=AUD&To=EUR and
#   change the "from" currency through the browser interface


# aud
########################################
#
# * https://www.xe.com/currencyconverter/convert/?Amount=1&From=AUD&To=EUR
# * 2022.06: 0,667
# * 2022.08: 0,684 +2,5% 
# * 2022.10: 0.643
# * 2022.12: 0.635
#
set @aud=0.635;


# cad
########################################
#
# * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CAD&To=EUR
# * 2022.06: 0,743
# * 2022.08: 0,763 +2,7%
# * 2022.10: 0.742
# * 2022.12: 0.688
#
# set @cad=0.688;
set @cad=0.742;


# chf
########################################
#
# * https://www.xe.com/currencyconverter/convert/?Amount=1&From=CHF&To=EUR
# * 2022.06: 0,966
# * 2022.08: 1,03 +6,6%
# * 2022.10: 1,02
# * 2022.12: 1.01
#
set @chf=1.01;


# cny
########################################
#
# Etc.


################################################################################
# Set variable for month & year
################################################################################
#
# Set to last month
########################################
#
# set @month=date_format(now(),"%m")-1;
# set @month=if(@month=0,12,@month);
# set @year=date_format(now(),"%Y");
# set @year=if(@month=12,@year-1,@year);


# Set manually
########################################
#
set @month=5;
set @year=2022;


################################################################################
# 4 - example_ch
################################################################################
#
select
    "example_ch" as webshop,
    round(@chf*sum(wp_postmeta_order_total.meta_value),2) as sales,
    round(@chf*sum(round(wp_postmeta_order_total.meta_value - wp_postmeta_order_shipping.meta_value - 
     wp_postmeta_order_shipping_vat.meta_value - wp_postmeta_vat.meta_value, 2)),2) as subtotal
from
    example_ch.wp_posts
join
    example_ch.wp_postmeta as wp_postmeta_order_shipping
    on 
    wp_posts.ID = wp_postmeta_order_shipping.post_id
join
    example_ch.wp_postmeta as wp_postmeta_order_shipping_vat
    on
    wp_posts.ID = wp_postmeta_order_shipping_vat.post_id
join
    example_ch.wp_postmeta as wp_postmeta_order_total
    on
    wp_posts.ID = wp_postmeta_order_total.post_id
join
    example_ch.wp_postmeta as wp_postmeta_vat
    on
    wp_posts.ID = wp_postmeta_vat.post_id    
where
    post_type = "shop_order"
    and
    post_status = "wc-completed"
    and
    wp_postmeta_order_total.meta_key="_order_total"
    and
    wp_postmeta_vat.meta_key="_order_tax"
    and
    wp_postmeta_order_shipping.meta_key="_order_shipping"
    and
    wp_postmeta_order_shipping_vat.meta_key="_order_shipping_tax"
    and
    date_format(post_date, "%Y")=@year
    and
    date_format(post_date, "%m")=@month


################################################################################
# 5 - example_uk
################################################################################
#
union select
    "example_uk" as webshop,
    round(@gbp*sum(wp_postmeta_order_total.meta_value),2) as sales,
    round(@gbp*sum(round(wp_postmeta_order_total.meta_value - wp_postmeta_order_shipping.meta_value - 
     wp_postmeta_order_shipping_vat.meta_value - wp_postmeta_vat.meta_value, 2)),2) as subtotal
from
    example_uk.wp_posts
join
    example_uk.wp_postmeta as wp_postmeta_order_shipping  # for "subtotal"
    on
    wp_posts.ID = wp_postmeta_order_shipping.post_id
join
    example_uk.wp_postmeta as wp_postmeta_order_shipping_vat  # for "subtotal"
    on
    wp_posts.ID = wp_postmeta_order_shipping_vat.post_id
join
    example_uk.wp_postmeta as wp_postmeta_order_total # For "sales" & "subtotal"
    on
    wp_posts.ID = wp_postmeta_order_total.post_id
join
    example_uk.wp_postmeta as wp_postmeta_vat     # For "subtotal"
    on
    wp_posts.ID = wp_postmeta_vat.post_id    
where
    post_type = "shop_order"
    and
    post_status = "wc-completed"
    and
    wp_postmeta_order_total.meta_key="_order_total"
    and
    wp_postmeta_vat.meta_key="_order_tax"
    and
    wp_postmeta_order_shipping.meta_key="_order_shipping"
    and
    wp_postmeta_order_shipping_vat.meta_key="_order_shipping_tax"
    and
    date_format(post_date, "%Y")=@year
    and
    date_format(post_date, "%m")=@month

# Etc.

See also Ordertotalen (WooCommerce).

Revenue per product

################################################################################
# Calculate revenue per product
################################################################################
#
select
    wp_postmeta.meta_value as sku,
    wp_woocommerce_order_items.order_item_name,
    sum(itemmeta_01.meta_value) as product_revenue_ex_vat
    
from
    # This table is only used to select the right related data
    #
    wp_posts

join
    wp_woocommerce_order_items
    on
    wp_posts.ID = wp_woocommerce_order_items.order_id

join
    # Revenue
    #
    wp_woocommerce_order_itemmeta as itemmeta_01
    on
    wp_woocommerce_order_items.order_item_id = itemmeta_01.order_item_id

join
    # product-post-id
    #
    wp_woocommerce_order_itemmeta as itemmeta_02
    on
    wp_woocommerce_order_items.order_item_id = itemmeta_02.order_item_id

join
    # wp_postmeta: sku
    ########################################
    #
    # Finally: Here's the sku!
    #
    wp_postmeta
    on
    wp_postmeta.post_id = itemmeta_02.meta_value
    
where
    post_type = "shop_order"
    and
    post_status = "wc-completed"
    and
    itemmeta_01.meta_key = "_line_subtotal"
    and
    wp_postmeta.meta_key = "_sku"

group by
    sku

order by
    product_revenue_ex_vat desc;

Product data - Short variant

WooCommerce product information is mainly found in two places [1]:

  • wp_posts
  • wp_postmeta

Example: Select the key data from wp_posts and the SKUs from wp_postmeta:

select
    wp_posts.ID			as post_id,
    wp_posts.post_title		as post_title,
    wp_posts.post_name		as slug,
    wp_posts.post_content       as content,   # =product description
    wp_postmeta.meta_value	as sku
from 
    wp_posts
join
    wp_postmeta
    on
    wp_posts.ID = wp_postmeta.post_id
where 
    post_type="product"
    and
    post_status="publish"
    and
    meta_key="_sku";

Product data - With sku & sku_oem

select
    wp_posts.ID			as post_id,
    wp_posts.post_title		as post_title,
    wp_postmeta.meta_value	as sku,
    wp_postmeta_02.meta_value	as sku_oem
from 
    wp_posts
join
    wp_postmeta
    on
    wp_posts.ID = wp_postmeta.post_id
join
    wp_postmeta as wp_postmeta_02
    on
    wp_posts.ID = wp_postmeta_02.post_id
where
    post_type="product"
    and
    post_status="publish"
    and
    wp_postmeta.meta_key="_sku"
    and
    wp_postmeta_02.meta_key="sku_oem";

Product data - With sku, sku_oem & prices

In this case, both _price and _regular_price contains the same price. However, I think only one of these fields is actually used (I don't know which one right now).

select
    wp_posts.ID			as post_id,
    wp_posts.post_title		as post_title,
    wp_postmeta.meta_value	as sku,
    wp_postmeta_02.meta_value	as sku_oem,
    wp_postmeta_03.meta_value	as price
from 
    wp_posts
join
    wp_postmeta
    on
    wp_posts.ID = wp_postmeta.post_id
join
    wp_postmeta as wp_postmeta_02
    on
    wp_posts.ID = wp_postmeta_02.post_id
join
    wp_postmeta as wp_postmeta_03
    on
    wp_posts.ID = wp_postmeta_03.post_id
where
    post_type="product"
    and
    post_status="publish"
    and
    wp_postmeta.meta_key="_sku"
    and
    wp_postmeta_02.meta_key="sku_oem"
    and
    wp_postmeta_02.meta_value="1824"
    and
    (
        wp_postmeta_03.meta_key="_price"
	or
        wp_postmeta_03.meta_key="_regular_price"
    );

With these two price-related fields both included in the output:

select
    wp_posts.ID			as post_id,
    wp_posts.post_title		as post_title,
    wp_postmeta.meta_value	as sku,
    wp_postmeta_02.meta_value	as sku_oem,
    wp_postmeta_03.meta_value	as price,
    wp_postmeta_04.meta_value	as price_regular
from 
    wp_posts
join
    wp_postmeta
    on
    wp_posts.ID = wp_postmeta.post_id
join
    wp_postmeta as wp_postmeta_02
    on
    wp_posts.ID = wp_postmeta_02.post_id
join
    wp_postmeta as wp_postmeta_03
    on
    wp_posts.ID = wp_postmeta_03.post_id
join
    wp_postmeta as wp_postmeta_04
    on
    wp_posts.ID = wp_postmeta_04.post_id    
where
    post_type="product"
    and
    post_status="publish"
    and
    wp_postmeta.meta_key="_sku"
    and
    wp_postmeta_02.meta_key="sku_oem"
    and
    wp_postmeta_03.meta_key="_price"
    and
    wp_postmeta_04.meta_key="_regular_price";

Product data - Long variant

  • Includes primary and secondary image, price + explanation, but without taxonomic information
  • MISSING: Short product description of Yoast (in its own Yoast table)
  • Note that most of the constraints on the self-joins, are included as relations, not as WHERE-clauses. This might be less confusing for now everything related to a relation, is located at the same spot.
#
# Example: Retrieve all product data
################################################################################
#
use wp_tmp;
#
#
################################################################################
# Select
################################################################################
#
select 
    wp_posts.id			as post_id,
    wp_posts.post_title		as post_title,
    wp_posts.post_name		as slug,
    wp_posts.post_content	as content,
    wp_posts.guid		as guid,
    wp_postmeta.meta_value	as sku,
    wp_postmeta_03.meta_value	as thumbnail_path,
    wp_postmeta_05.meta_value	as second_image_path,
    wp_postmeta_06.meta_value	as price
from 
    wp_posts


# Join 01 on wp_postmete: "sku"
############################################################
#
left join
    wp_postmeta
    on
    wp_posts.id = wp_postmeta.post_id


# Join 02 on wp_postmeta: "_thumbnail_id"
############################################################
#
# "_thumbnail_id" on its turn, is a wp_postmeta.product_id
#
left join
    wp_postmeta as wp_postmeta_02
    on
    wp_postmeta_02.post_id = wp_posts.id
    and
    wp_postmeta_02.meta_key = "_thumbnail_id"


# Join 03 on wp_postmeta: thumbnail-path
############################################################
#
# This join is different from the two below, as it is a
# double join: It retrieves the meta_id for the thumbnail-
# attachment
#
left join
    wp_postmeta as wp_postmeta_03
    on
    wp_postmeta_03.post_id = wp_postmeta_02.meta_value
    and
    wp_postmeta_03.meta_key = "_wp_attached_file"


# Join 04 on wp_postmeta: "_product_image_gallery"
############################################################
#
# Retrieve the meta_value for "_product_image_gallery":
# It is the post_id of everything related to the second
# image
#
left join
    wp_postmeta as wp_postmeta_04
    on
    wp_postmeta_04.post_id = wp_posts.id
    and
    wp_postmeta_04.meta_key = "_product_image_gallery"


# Join 05 on wp_postmeta: "_product_image_gallery"
############################################################
#
# Retrieve the meta_value for "_product_image_gallery":
# It is the post_id of everything related to the second
# image
#
left join
    wp_postmeta as wp_postmeta_05
    on
    wp_postmeta_05.post_id = wp_postmeta_04.meta_value
    and
    wp_postmeta_05.meta_key = "_wp_attached_file"


# Join 06 on wp_postmeta: "_price"
############################################################
#
# "_price" and "_regular_price" seem always identical → Only
# fetch the first one
#
left join
    wp_postmeta as wp_postmeta_06
    on
    wp_postmeta_06.post_id = wp_posts.id
    and
    wp_postmeta_06.meta_key = "_price"


# Where clause
############################################################
#    
where 
    post_type="product"
    and
    post_status="publish"
    and
    wp_postmeta.meta_key="_sku"


# Limit
############################################################
#
limit 
    0, 10;

Product data - Figuring out missing images

Missing data from gMC was imported into table missing_image. How this was connected to existing data:

#
# Missing images - Overview, diagnosis, etc.
################################################################################
#
#
################################################################################
# Select db
################################################################################
#
use tmp;


################################################################################
# Retrieve all info
################################################################################
#
select
    missing_image.title         as gmc_title,
    missing_image.image_link    as gmc_image_url,
    missing_image.file_name     as gmc_file_name,
    wp_posts.ID                 as wp_post_id,
    concat("https://example.com/",wp_posts.post_name) as wp_product_url,
    wp_postmeta_sku.meta_value                        as wp_sku,
    wp_postmeta_sku_oem.meta_value                    as wp_sku_oem,
    concat("https://example.com/wp-content/uploads/",wp_postmeta_thumbnail_path.meta_value) as wp_image_url
from
    missing_image
join
    wp_posts
    on
    missing_image.title = wp_posts.post_title
join
    wp_postmeta as wp_postmeta_sku
    on
    wp_posts.ID = wp_postmeta_sku.post_id
join
    wp_postmeta as wp_postmeta_sku_oem
    on
    wp_posts.ID = wp_postmeta_sku_oem.post_id
join
    #
    # Retrieve _thumbnail_id
    #
    wp_postmeta as wp_postmeta_thumbnail_id
    on
    wp_posts.ID = wp_postmeta_thumbnail_id.post_id
join
    #
    # Retrieve _thumbnail-path
    #
    wp_postmeta as wp_postmeta_thumbnail_path
    on
    wp_postmeta_thumbnail_path.post_id = wp_postmeta_thumbnail_id.meta_value
where
    wp_postmeta_sku.meta_key = "_sku"
    and
    wp_postmeta_sku_oem.meta_key = "sku_oem"
    and
    wp_postmeta_thumbnail_id.meta_key = "_thumbnail_id"
    and
    wp_postmeta_thumbnail_path.meta_key = "_wp_attached_file";

Product data - Retrieve all unique meta_keys

For situations where I can't remember the exact meta_key that I'm looking for:

################################################################################
# Retrieve all distinct meta_keys for products
################################################################################
#
select distinct
   meta_key
from
   wp_postmeta
join
   wp_posts
   on
   wp_postmeta.post_id = wp_posts.ID
where
   wp_posts.post_type="product";

Product data - All products without EAN

################################################################################
# Retrieve all products without EAN
################################################################################
#
select
   ID,
   wp_postmeta_sku.meta_value as sku,
   post_title
from
   wp_posts
left join
   wp_postmeta
   on
   wp_postmeta.post_id = wp_posts.ID
   and
   wp_postmeta.meta_key like "%ean"
join
   wp_postmeta as wp_postmeta_sku
   on
   wp_postmeta_sku.post_id = wp_posts.ID
   and
   wp_postmeta_sku.meta_key like "%sku"
where
   wp_posts.post_type="product"
   and
   (
      wp_postmeta.meta_value is null
      or
      wp_postmeta.meta_value = ""
   );

Product data - All content

Retrieve all content to check translations. So no figures or numbers. Only text. The resulting query is suprisingly small:

################################################################################
# Retrieve all product-related content
################################################################################
#
select
   wp_posts.ID,
   wp_posts.post_name,
   wp_posts.post_title,
   wp_posts.post_content,
   wp_posts.post_excerpt,
   wp_postmeta_post_tools.meta_value as post_tools,
   wp_postmeta_focuskw.meta_value as yoast_focuskw
#
#    wp_postmeta.meta_key,
#    wp_postmeta.meta_value
#
from
   wp_posts
join
   wp_postmeta as wp_postmeta_focuskw
   on
   wp_posts.ID = wp_postmeta_focuskw.post_id
join
   wp_postmeta as wp_postmeta_post_tools
   on
   wp_posts.ID = wp_postmeta_post_tools.post_id
#
# join
#    wp_postmeta
#    on
#    wp_posts.ID = wp_postmeta.post_id
#
where
   wp_posts.post_type="product"
   and
   wp_posts.post_status="publish"
   and
   wp_postmeta_focuskw.meta_key="_yoast_wpseo_focuskw"
   and
   wp_postmeta_post_tools.meta_key="post_tools";

Shop-page widgets

The widgets/filters on the shop page are coded in table wp_options in a few fields with serialised content:

select option_value from wp_options where option_name = "widget_woocommerce_product_categories"   # Category-widget
select option_value from wp_options where option_name = "widget_woocommerce_layered_nav"          # Attribute-widgets

Orders per country

#
# Retrieve a list with the number of orders per country
################################################################################
#
#
# * This script was developed to figure out what payment methods a site should
#   offer
# * Country is determined by the value of the meta field "_billing_country".
#   With the argument "%country%", you get about three times as many results
# * "Billing country" seems most relevant, as this determines the payment
#   method
#
#
################################################################################
# Select database
################################################################################
#
use example_de;


################################################################################
# Orders per country
################################################################################
#
select distinct
   wp_postmeta_country.meta_value as country,
   count(wp_postmeta_country.meta_value) as order_number
from
   wp_posts
join
   wp_postmeta as wp_postmeta_country
   on
   wp_posts.ID = wp_postmeta_country.post_id
where
   post_type = "shop_order"
   and
   post_status = "wc-completed"
   and
   wp_postmeta_country.meta_key like "%billing_country"
group by
   wp_postmeta_country.meta_value
order by
   order_number desc;

Payment method usage

Which payment methods are used, and how often?

################################################################################
# Payment method
################################################################################
#
select distinct
    wp_postmeta_payment_method.meta_value as payment_method,
    count(wp_postmeta_payment_method.meta_value) as order_number
from
    wp_posts
join
    wp_postmeta as wp_postmeta_payment_method
    on
    wp_posts.ID = wp_postmeta_payment_method.post_id
where
    post_type = "shop_order"
    and
    post_status = "wc-completed"
    and
    wp_postmeta_payment_method.meta_key like "%payment_method"
group by
    wp_postmeta_payment_method.meta_value
order by
    order_number desc;

See also