WooCommerce - Datamodel
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";
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
Order adresses
- Order IDs can be found in table
wp_posts
- Address details can be found in table
wp_postmeta
.
Example:
# Retrieve a list of all countries to which there were orders ################################################################################ # # ################################################################################ # Select database ################################################################################ # use knl; ################################################################################ # All countries to which there have been orders ################################################################################ # select distinct meta_value from wp_posts join wp_postmeta on wp_posts.ID = wp_postmeta.post_id where post_type = "shop_order" and meta_key like "%country";