Revenue per month (WordPress, SQL)

This article discusses a DIY SQL script to retrieve monthly revenue & related figures.

Why reinvent the wheel?

WooCommere has some reporting capabilities, so why reinvent the wheel by creating an own SQL script for this?

There are a couple of reasons for doing this myself:

Last time I checked, the build-in reporting wasn't reliable: It incorporated cancelled orders in a way that was not traceable
This issue was a symptom of a larger problem, of not being transparent
It's actually quite complicated to compile these reports and they involve quite a number of choices that might probably faily arbitrary. Some examples:
1. Orders are grouped in the month of latest change. Not the month in which they were created
2. How to treat VAT over delivery costs?
3. Orders with exactly what order statusses, to include?

Formulated differently: The main reason for doing this myself, is to have complete control and understanding of the calculations. And yes: That's a double-edged sword, because it will equally require quite some efforts to make sure that the results are accurate and reliable - I might rely for years on these figures without actually checking them again.

Definitions

It's quite tricky to know exactly what to calculate. So let's define that here first.

Sales amount → Terminology (bookkkeeping) » Sales amount
Revenue → Terminology (bookkkeeping) » Revenue » Webshops: Revenue including delivery costs (ex. VAT)
Commission basis → Terminology (bookkkeeping) » Commission basis

Which orders to include?

An order should be included, if any of these two conditions hold:

Include all orders for which payment has been received. This does not mean, that the order status in WordPress has to have been updated accordingly. When payment has been recorded at database level, the order is eligible to be included in a revenue overview
WooCommerce generates an order object (a record in table wp_posts with post_type = 'shop_order', as soon as payment has been received. Before this, no such object is present in this table. So, include all objects of this type.

Additionally, I tend to filter for orders with certain statusses: post_status = 'wc-completed' or post_status = 'wc-processing'. This might be redundant.

Which month?

Orders are associated with the month in which they are created. So, if an order has been created at the end of January, but payment only received at the beginning of February, this order will be part of the statistics over January.

This implies, that figures over e.g., January, won't be accurate on the last day of that month. In my experience, waiting 15 days seems to solve this problem.

Version 2023.01 (old)

This version of the 'revenue script' has been used from January 2023 until January 2024, until a weakness was discovered: It only includes orders with status wp-completed. In January 2024, a situation occured where orders were payed, but somehow the order status wasn't updated accordingly.

#
# 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.

Version 2024.01

#
# Project "FixRevenueCalculations (Jan. 2024)"
#
################################################################################
# Select db
################################################################################
#
use example_ch;


################################################################################
# Recreate revenue query - Over 2023.12
################################################################################
#
# * When orders have a status before "Processing", there is no actually order
#   record, hence not incorporated in the query below - That actually makes
#   stuff easy
# * It's actually overkill to check for a value at "paid_date": these records are
#   only created once payment has been received. Maybe don't even need to check
#   for the status: When a record exists, stuff has been paid - Simple.
#
# To incorporate:
#
# * OK - make 100% sure that it has been paied
# * #Orders - That doesn't fit here, actually
#
#
# Set year & month
########################################
#
set @year=2023;
set @month=12;


# Set exchange rate
########################################
#
set @xr=1.01;


# Drop tmp table
########################################
#
drop table if exists sales_tmp;


# Create & select
########################################
#
create temporary table
    sales_tmp
select
    ID,
    # post_title,
    post_status,
    post_name,
    # post_type,
    # date_format(post_date, '%Y') as "order_year",
    # date_format(post_date, '%m') as "order_month",
    wp_postmeta_order_total.meta_value as sales_amount_orgcur,
    @xr*wp_postmeta_order_total.meta_value as sales_amount_eur,
    wp_postmeta_order_vat.meta_value as order_vat_orgcur,
    @xr*wp_postmeta_order_vat.meta_value as order_vat_eur,
    #
    #
    # order_shipping
    ########################################
    #
    wp_postmeta_order_shipping.meta_value as order_shipping_orgcur,
    @xr*wp_postmeta_order_shipping.meta_value as order_shipping,
    wp_postmeta_order_shipping_vat.meta_value as order_shipping_vat_orgcur,
    @xr*wp_postmeta_order_shipping_vat.meta_value as order_shipping_vat,
    wp_postmeta_paid_date.meta_value as paid_date,
    #
    #
    # revenue
    ########################################
    #
    # * Revenue = order totals - VAT (including VAT over delivery)
    #
    wp_postmeta_order_total.meta_value - 
        wp_postmeta_order_vat.meta_value - 
        wp_postmeta_order_shipping_vat.meta_value as revenue_orgcur,
    @xr*(wp_postmeta_order_total.meta_value - 
	wp_postmeta_order_vat.meta_value - 
        wp_postmeta_order_shipping_vat.meta_value) as revenue_eur,
    #
    #
    # Commission-basis - orgcur
    ########################################
    #
    # Commission-basis = revenue -/- shipping -/- all VAT
    #
    wp_postmeta_order_total.meta_value - 
        wp_postmeta_order_vat.meta_value - 
        wp_postmeta_order_shipping_vat.meta_value -
        wp_postmeta_order_shipping.meta_value as commission_basis_orgcur,


    # Commission-basis - eur
    ########################################
    #
    @xr*(wp_postmeta_order_total.meta_value - 
	wp_postmeta_order_vat.meta_value - 
        wp_postmeta_order_shipping_vat.meta_value -
        wp_postmeta_order_shipping.meta_value) as commission_basis_eur 
#
#
# from
########################################
#
from
    wp_posts
join
    wp_postmeta as wp_postmeta_order_total
    on
    wp_posts.ID = wp_postmeta_order_total.post_id
join
    wp_postmeta as wp_postmeta_order_vat
    on
    wp_posts.ID = wp_postmeta_order_vat.post_id
join
    wp_postmeta as wp_postmeta_order_shipping
    on
    wp_posts.ID = wp_postmeta_order_shipping.post_id
join
    wp_postmeta as wp_postmeta_order_shipping_vat
    on
    wp_posts.ID = wp_postmeta_order_shipping_vat.post_id
join
    wp_postmeta as wp_postmeta_paid_date
    on
    wp_posts.ID = wp_postmeta_paid_date.post_id
#    
#
# where
########################################
#
where
    post_type = "shop_order"
    and
    date_format(post_date, '%Y') = @year
    and
    date_format(post_date, '%m') = @month
    and
    (
	post_status = 'wc-completed'
        or
        post_status = 'wc-processing'
    )
    and
    length(wp_postmeta_paid_date.meta_value) > 0
    and
    wp_postmeta_order_total.meta_key = '_order_total'
    and
    wp_postmeta_order_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
    wp_postmeta_paid_date.meta_key='_paid_date'
order by
    ID;


################################################################################
# Create aggregated figures
################################################################################
#
select
    count(*)                                  as sum_orders,
    format(sum(sales_amount_orgcur),2)	      as sum_sales_amount_orgcur,
    format(sum(sales_amount_eur),2)	      as sum_sales_amount_eur,
    format(sum(revenue_orgcur),2) 	      as sum_revenue_orgcur,
    format(sum(revenue_eur),2) 		      as sum_revenue_eur,
    format(sum(commission_basis_orgcur),2)    as sum_commission_basis_orgcur,
    format(sum(commission_basis_eur),2)	      as sum_commission_basis_eur
from
    sales_tmp;

Revenue per month (WordPress, SQL)

Inhoud

Why reinvent the wheel?

Definitions

Which orders to include?

Which month?

Version 2023.01 (old)

Version 2024.01

See also

Navigatiemenu

Zoeken