WooCommerce - Datamodel: verschil tussen versies
Regel 864: | Regel 864: | ||
order by | order by | ||
order_number desc; | order_number desc; | ||
+ | </pre> | ||
+ | |||
+ | == 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 <code>wp_postmeta</code>, as might have been expected: | ||
+ | |||
+ | <pre> | ||
+ | # | ||
+ | # 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"; | ||
+ | </pre> | ||
+ | |||
+ | Output first select statement. Note that the id's are ''serialised'', which sucks: | ||
+ | |||
+ | <pre> | ||
+ | meta_id post_id meta_key meta_value | ||
+ | ------- ------- -------------- ------------------ | ||
+ | 2432126 78108 _crosssell_ids a:1:{i:0;i:81956;} | ||
+ | </pre> | ||
+ | |||
+ | Output second select statement: | ||
+ | |||
+ | <pre> | ||
+ | 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;} | ||
</pre> | </pre> | ||
Huidige versie van 30 mrt 2023 om 13:54
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;
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;}