Basic Reports: Members sales volume

In this lesson, let's create a report on percentage of monthly sales by customers with membership, using the data in basic command.

The Output

Monthly sales by customers with membership

Basic commands tutorial

Month	Member Sales
200201	46.7
200202	49.45
200203	48.20
200204	38.21
200205	51.66
200206	55.78
200207	54.72
200208	59.11
200209	42.69
200210	60.32
200211	48.11
200212	40.01

The process flow

The percentage of sales from memebership is the ratio of membership sales and all sales, therefore, if there are more sales from non membership customers, the percentage of sales wil hence decrease. Customers with membership may bring in more profit through target campaign, that may only true for a certain segment of membership customers. Yet, it stil serves as a good index for store evaluation.

There are various units we can use to calculate the percentage of sales. "Purchase quantity", "purchase dollar amount", and "gross profit" are the most typical calculations. In this lesson, we will use the percentage of purchase quantity for members' respective to all customers' purchase.

The process flow

Calculate the total quantity for each customer
1. Select columns required: Date and quantity
2. Extract four digit year from date
3. Calculate the total quantity per month for each customer
Calculate the total quantity per month on membership purchase
1. Remove recrods with null customer ID
2. Select columns required: Date and quantity
3. Extract four digit year from date
4. Calculate the total quantity per month for each member
Join the results above and calculate the percentage of quantity purchased by each customer with membership
1. Join the total quantity for all customers with total quantity for each member
2. Find out the percentage of quantity purchase from total quantity for each member / total quantity for each customers.
3. Extract columns "month" and "percentage of membership sales volume"

Script

After we have laid out the workflow to create the report, let's rethink each step and start creating the script

#/bin/bash
#===============================================================
# MUSASHI bash script
#===============================================================

#---- Title
title="Percentage of membership sales volume"

#---- Comment
comment="Basic Reports"

#---- Variables
inPath="/mnt/h00/prm/tutorial"

#---------------------------------------------------------------
# Commands
#---------------------------------------------------------------
#Total quantity per month
xtcat -i $inPath/'dat*.xt.gz' |
xtcut -f date,quantity |
xtsubstr -f date:month -R 1_6 |
xtagg -k month -f quantity:totalqty -c sum -o xxall

#Quantity purchased per month for members
xtcat -i $inPath/'dat*.xt.gz' |
xtdelnul -f customer |
xtcut -f date,quantity |
xtsubstr -f date:month -R 1_6 |
xtagg -k month -f quantity:membertotalqty -c sum -o xxcust

#Join Total quantity and quantity purchased by members and calcuate the percentage
xtjoin -k month -m xxcust -f membertotalqty -i xxall |
xtcal -c 'round((($membertotalqty/$totalqty)*100),0.01)' -a salesvolpct |
xtcut -f month,salesvolpct|
xtheader -l "$title" -c "$comment" -o custratio.xt
#===============================================================

Check your results and see if the total adds up to 100 since all customers in this sample dataset has membership.

Exercise

The following are additional exercises for this lesson, try them out and check your results with the scripts and output given below.

Report Name	Script Name	Result (xt)	Result (html)
Percentage of membership sales volume by amount purchased	custratio1.sh	custratio1.xt	custratio1.html
Percentage of membership sales volume by gross profit	custratio2.sh	custratio2.xt	custratio2.html