WooCommerce - Datamodel
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
→ Revenue per month (WordPress, SQL)
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;
Cross-sales
How to add a 'universal cross-sale product' to all products on a site? How are cross-sale products administrated anyway?
Through table wp_postmeta, as might have been expected:
# # Figure out how cross-sale products are administrated - 2023.03 ################################################################################ # # ################################################################################ # Trace a cross-sale product ################################################################################ # # * The "universal cross-sale product": # * Title: "xxx" # * SKU: yyy # * id: 81956 # # * It has been associated as a cross-sale to this product: # * Title: "zzz" # * No SKU # * id: 78108 # select * from wp_postmeta where post_id = 78108 and meta_key = "_crosssell_ids"; ################################################################################ # All cross-sale products associated with products ################################################################################ # select * from wp_postmeta where meta_key = "_crosssell_ids";
Output first select statement. Note that the id's are serialised, which sucks:
meta_id post_id meta_key meta_value
------- ------- -------------- ------------------
2432126 78108 _crosssell_ids a:1:{i:0;i:81956;}
Output second select statement:
meta_id post_id meta_key meta_value
------- ------- -------------- -------------------------------------------------------------------------
2290881 79912 _crosssell_ids a:6:{i:0;i:8380;i:1;i:8452;i:2;i:4686;i:3;i:4967;i:4;i:3580;i:5;i:65644;}
2290982 16920 _crosssell_ids a:1:{i:0;i:65644;}
2291035 16921 _crosssell_ids a:1:{i:0;i:65644;}
2291067 16922 _crosssell_ids a:1:{i:0;i:65644;}
2291158 16966 _crosssell_ids a:1:{i:0;i:65644;}
2291190 16965 _crosssell_ids a:1:{i:0;i:65644;}
2291247 16967 _crosssell_ids a:1:{i:0;i:65644;}
2291296 9254 _crosssell_ids a:1:{i:0;i:65644;}
2291328 9255 _crosssell_ids a:1:{i:0;i:65644;}
2291359 9256 _crosssell_ids a:1:{i:0;i:65644;}
2291391 9211 _crosssell_ids a:1:{i:0;i:65644;}
2291423 9210 _crosssell_ids a:1:{i:0;i:65644;}
2291455 9209 _crosssell_ids a:1:{i:0;i:65644;}
2432125 72205 _crosssell_ids a:1:{i:0;i:81956;}
2432126 78108 _crosssell_ids a:1:{i:0;i:81956;}