WooCommerce - Datamodel: verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
Regel 670: Regel 670:
 
   meta_key like "%country";
 
   meta_key like "%country";
 
</pre>
 
</pre>
 +
 +
== See also ==
 +
 +
* [[Ordertotalen (WooCommerce)]]

Versie van 3 jan 2023 15: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
# * Try to get at least 3 significant numbers. This will often also be the
#   maximum relevant number, because of fluctuations
#
set @aud=0.66715944;	# https://www.xe.com/currencyconverter/convert/?Amount=1&From=AUD&To=EUR
set @cad=0.74248581;	# https://www.google.com/search?q=1%20cad%20to%20eur
set @chf=0.96594098;	# https://www.xe.com/currencyconverter/convert/?Amount=1&From=CHF&To=EUR
set @cny=0.14113558;	# https://www.xe.com/currencyconverter/convert/?Amount=1&From=CNY&To=EUR
set @gbp=1.1808518;		# https://www.xe.com/currencyconverter/convert/?Amount=1&From=GBP&To=EUR
set @czk=0.040544174;	# https://www.xe.com/currencyconverter/convert/?Amount=1&From=CZK&To=EUR
set @dkk=0.13438589;	# https://www.xe.com/currencyconverter/convert/?Amount=1&From=DKK&To=EUR
set @inr=0.012267764;	# https://www.xe.com/currencyconverter/convert/?Amount=1&From=INR&To=EUR
set @jpy=0.0074278196;	# https://www.xe.com/currencyconverter/convert/?Amount=1&From=JPY&To=EUR
set @ngn=0.002295;		# https://www.xe.com/currencyconverter/convert/?Amount=1&From=NGN&To=EUR
set @nzd=0.603;			# https://www.xe.com/currencyconverter/convert/?Amount=1&From=NZD&To=EUR
set @pln=0.215;			# https://www.xe.com/currencyconverter/convert/?Amount=1&From=PLN&To=EUR
set @sek=0.09518;		# https://www.xe.com/currencyconverter/convert/?Amount=1&From=SEK&To=EUR
set @try=0.0596;		# https://www.xe.com/currencyconverter/convert/?Amount=1&From=TRY&To=EUR
set @uah=0.0324;		# https://www.xe.com/currencyconverter/convert/?Amount=1&From=UAH&To=EUR
set @usd=0.95277;		# https://www.xe.com/currencyconverter/convert/?Amount=1&From=USD&To=EUR


################################################################################
# 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.
#
# 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_postmet: "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";

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";

See also

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=WooCommerce_-_Datamodel&oldid=32847"