Update prices WooCommerce

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen

How to update all prices in about 50 WooCommerce sites, with each about 15.000 products, preferably all on the same day?

Exact calculations

  • Increase prices with 15%
  • Round off to above to have a figure ending in .95 (or the equivalent in currencies with different numbers of zeros).

Example:

  • Price (incl. VAT): € 9.95
  • 15%: € 1,4925
  • New price: € 11,4425
  • Roud off to ,95: € 11,95

WP-CLI?

The royal approach would be through WP-CLI. Some thoughts:

  • With wp wc product list, I could retrieve all IDs
  • Subsequently, read-out prices, update prices, and write them back with wp wc product update

Problems:

  1. How to loop through all IDs, read & write a price? I usually use chaining, and this would probably something new. Store the id's in an array and than loop through that?
  2. This approach is probably very slow. Might takes days, maybe even weeks. That makes it very error prone.

SQL?

Quick and dirty: Do it through SQL. I could do all 60 sites in probably less than a minute. The basic SQL code isn't complicated either (source):

update
   wp_postmeta
set
   meta_value = replace(meta_value, ",", ".")
where
   meta_key="_price"
   or
   meta_key="_regular_price";

How about VAT?

  • Prices have been entered including VAT
  • WooCommerce will assume that this is against the relevant tariff of the home country of the shop (Netherlands in this case)
  • Unless a visitor places an order, he/she will she the prices incl. VAT as they are in the Netherlands.

All details have been wonderfully figured out here: WooCommerce & BTW

Solution

I have a Bash library for doing this kind of stuff rather automatically. Part of it is a routine (load-site-array.sh) to assign names of all sites and databases into an array. Subsequently, a routine would loop through this array and do stuff. However, this price update is quite a one-off event: Not something I would repeat every couple of months, probably not more frequent than every couple of years. Hence it would have been overkill to do this in such a flexible way: Program today for today and tomorrow for tomorrow.

In the end, I used plain SQL with names of databases hardcoded - Much more appropriate!

An impression of this script:

#
# Update prices on various servers
################################################################################
#
# * Just hardcoded in SQL - That's so much easier than through elegant Bash
#   code!
#
#
################################################################################
# nl_en
################################################################################
#
# use example_nl;

#
# Orientation (1)
########################################
#
# select distinct
# 	meta_value as price_old,
#     0.15*replace(meta_value,",",".") as price_increase,
#     1.15*replace(meta_value,",",".") as price_increased,
#     floor(1.15*replace(meta_value,",","."))+.95 as price_new
#     # 1.15*meta_value as price_increased
# from
# 	wp_postmeta
# where
# 	meta_key="_price"
#     or
#     meta_key="_regular_price";


# # Commit
# ########################################
# #
# update
# 	wp_postmeta
# set
# 	meta_value = floor(1.15*replace(meta_value,",","."))+.95
# where
# 	meta_key="_price"
#     or
#     meta_key="_regular_price";


# # Orientation (2)
# ########################################
# #
# select distinct
#     floor(1.15*replace(meta_value,",","."))+.95 as price_new
# from
# 	wp_postmeta
# where
# 	meta_key="_price"
#     or
#     meta_key="_regular_price";


################################################################################
# Etc.
################################################################################
#
# use example_be;
# update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price";

# use example_com_es; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price";


################################################################################
# OK - au_en
################################################################################
#
# use example_australia_com;
# select distinct
# 	meta_value as price_old,
#     0.15*replace(meta_value,",",".") as price_increase,
#     1.15*replace(meta_value,",",".") as price_increased,
#     floor(1.15*replace(meta_value,",","."))+.95 as price_new
#     # 1.15*meta_value as price_increased
# from
# 	wp_postmeta
# where
# 	meta_key="_price"
#     or
#     meta_key="_regular_price";


################################################################################
# Etc.
################################################################################
#
# use example_canada_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price";
# use example_ch; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price";
# use example_cn; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price";
# use example_co_uk; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where meta_key="_price" or meta_key="_regular_price";


################################################################################
# OK - cz_en
################################################################################
#
#
# Orientation
########################################
#
# use example_cz;

# select distinct
# 	meta_value as price_old,
# 	1.15*replace(meta_value,",",".") as price_increased,
#     (floor(1.15*replace(meta_value,",",".")/100)+.95)*100 as price_increased
# from
# 	wp_postmeta
# where
# 	(
# 		meta_key="_price"
# 		or
# 		meta_key="_regular_price"
# 	)
#     and
#     meta_value<>"";


# Production
########################################
#
# use example_cz;
# update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";


################################################################################
# Etc.
################################################################################
#
# use example_dk; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_fr; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_in; update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_ireland_com;
# update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_it; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_jp; update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_lu; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_nigeria_com; update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_nz; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_pl; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_portugal_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_se; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_shop_at; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use exampleshop_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_shop_de; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_sk; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_turkey_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_ukraine_com; update wp_postmeta set meta_value=(floor(1.15*replace(meta_value,",",".")/100)+.95)*100 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use example_usa_com; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";
# use beispiel_webshop_de; update wp_postmeta set meta_value = floor(1.15*replace(meta_value,",","."))+.95 where (meta_key="_price" or meta_key="_regular_price") and meta_value<>"";

Note:

  • It updated sites on multiple servers. How this was done: I used multiple profiles in MySQL Workbench and every time loaded the same script
  • Turned out that there were basically two kinds of updates: For sites with a currency that use two decimals (make prices end at .95), and for currencies that don't use decimals (make prices end at 95) - That was another thing I learned from doing this manually. It will make this easier next time
  • Execution per site was about .5 to .7 seconds. I didn't compare this to WP-CLI, but I suspect that would be hours per site.

See also