Sunday Funday – Day 1 – Epic Metrics

So, if you read my last post you’ll have seen I decided to start a new side project. This was to give me easy to produce “epic metrics” for my transparency reports.

To save me the time in manually putting together spreadsheets and moving them into visualisations (charts) for coverage on the blog.

However, it’s something that – although I found the merit in doing my own solution, I also started to hit some walls with it.

I then found a solution online – Metorik does a lot of good things and I’m using that alongside my Sunday Funday builds.

Building “Epic Metrics”

Epic Metrics is what I’m currently focussing on with my Sunday Funday’s. Epic Metrics is effectively a “souped up” Sales Dashboard for my WooCommerce stores, which will save me time when it comes to producing transparency reports. They’ll also give you a way to hop on over and see “live” how the metrics are doing (without having to manually produce charts from WooCommerce Dashboards).

Day #1 – Recap

In the first day of Sunday Funday I scoped out exactly what I was looking to build and how I’d go about doing it. Here’s the initial set of Metrics I wanted

• Revenue Chart (New, Renewals Failed, Refunds)
• Monthly Recurring Revenue and Churn
• Cashflow Forecasts
• Product Report Breakdowns

I know I have the information on the first one in my WooCommerce reports, so this would be the easiest to tackle.

Setting up the Database and Finding the API

Great, so both WooCommerce and WooCommerce Subscriptions have APIs which I can “consume” to do my calculations.  Luckily I’ve already got a good handle on these from WooSync for Zero BS CRM and the Sales Dashboard, so a lot of this work will be expanding that into a standalone solution which connects to the APIs and maintains the site.

Here’s how my database of subscriptions looks (the variable types might need some tweaks, but it’s how I’m doing it for now)

$sql = "CREATE TABLE IF NOT EXISTS ". $woosub_t['subscriptions'] ." (
    'ID' INT NOT NULL AUTO_INCREMENT,
    'woo_site' int(11) DEFAULT NULL,
    'woo_subid' int(11) NOT NULL,
    'woo_parentid' int(11) NOT NULL,
    'woo_orderkey' varchar(100) DEFAULT NULL,
    'woo_status' varchar(100) DEFAULT NULL,
    'woo_curr' varchar(3) DEFAULT NULL,
    'woo_ver' varchar(10) DEFAULT NULL,
    'woo_inc_tax' int(1) DEFAULT 0,   
    'woo_customer' int(11) DEFAULT NULL,      
    'woo_contact' int(11) DEFAULT NULL,
    'woo_discount_total' decimal(19,4) DEFAULT '0.0000',
    'woo_discount_tax' decimal(19,4) DEFAULT '0.0000',
    'woo_shipping_total' decimal(19,4) DEFAULT '0.0000',
    'woo_shipping_tax' decimal(19,4) DEFAULT '0.0000',
    'woo_cart_tax' decimal(19,4) DEFAULT '0.0000',
    'woo_total_tax' decimal(19,4) DEFAULT '0.0000',
    'woo_total' decimal(19,4) DEFAULT '0.0000',
    'woo_method' varchar(50) DEFAULT NULL,
    'woo_method_title' varchar(200) DEFAULT NULL,
    'woo_billing_period'  varchar(5) DEFAULT NULL,
    'woo_billing_interval' int(14)  NOT NULL,
    'woo_start_date' int(14)  NOT NULL,
    'woo_trial_end_date' int(14) NOT NULL,
    'woo_next_payment_date' int(14)  NOT NULL,
    'woo_end_date' int(14) NOT NULL,
    'woo_created' int(14)  NOT NULL,
    'woo_modified' int(14) NOT NULL,
    'woo_completed' int(14) NOT NULL,
    'woo_paid' int(14) NOT NULL,
    PRIMARY KEY ('ID'))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8
    COLLATE = utf8_general_ci";
dbDelta($sql);

Quite the chunk of data which I’ll be bringing in from the API. On top of those, there’s the “Line Items” for a subscription (so you can see which products make up that subscription) as well as the Orders related to that subscription.

Importantly, I setup the data to have a top level of woo_site which is an index meaning I can run the same import routines

Here’s how it looks “sketched” out and how I’ll be linking it all together via the data.

Importing the data

This was a case of running through each page of the WooCommerce API response and storing the data, I wrote a couple of quick AJAX functions to do this. These request the subscription and transaction resource and run through updating the database.

I wrote a lot of ADD / UPDATE type functions using standard $wpdb->insert or $wpdb->update so that all the information utilises the WordPress SDK and Codex, and is properly prepared (using $wpdb->prepare).

Charting the data

The next thing was to visualise the data into a handy chart 🙂 for this I used Chart.js but also factored my SQL to be run and produce the necessary information. It’s not as simple as just running a query (this is resulting from querying the transaction database, NOT the subscription database). Here’s how the SQL looks (if you’re interested!)

$chosen_site=0;
if(isset($_POST['epicwoo_site'])&&!empty($_POST['epicwoo_site'])){
$chosen_site=(int)$_POST['epicwoo_site'];
}

$sql="SELECT
MONTH(FROM_UNIXTIME(wp_epic_woo_transaction_lineitems.woo_created)) asmonth,
YEAR(FROM_UNIXTIME(wp_epic_woo_transaction_lineitems.woo_created)) asyear,
COUNT(wp_epic_woo_transaction_lineitems.ID) ascount,
SUM(wp_epic_woo_transaction_lineitems.woo_total) as revenue,
wp_epic_woo_transaction_lineitems.woo_order_type,
wp_epic_woo_transaction_lineitems.woo_status
FROM wp_epic_woo_transaction_lineitems";
if($chosen_site>0){
$sql.=" WHERE wp_epic_woo_transaction_lineitems.woo_site = %d";
}
$sql.=" GROUP BY
wp_epic_woo_transaction_lineitems.woo_order_type,
wp_epic_woo_transaction_lineitems.woo_status,
MONTH(FROM_UNIXTIME(wp_epic_woo_transaction_lineitems.woo_created)),
YEAR(FROM_UNIXTIME(wp_epic_woo_transaction_lineitems.woo_created))
ORDER BY year ASC, month ASC";

if($chosen_site>0){
$sql=$wpdb->prepare($sql,$chosen_site);
}
$data=$wpdb->get_results($sql);