Basic Reports: Percentage of gross margin for each customer

In this lesson, we will create a report on the percentage of gross margin for each customer, using the data in basic command.

The Output

Customer's gross profit margin

Tutorial

Customer Profit Margin
A00001 24.6
A00002 20.7
A00003 21.1
A00004 21.9
A00005 23.9
A00006 23
A00007 22.5
A00008 19.2
A00009 21.7
A00010 20
A00011 21.8
A00012 22.8
A00014 21.4
A00015 22.2
A00016 25.4
A00017 22.8
A00018 23.6
A00019 24.4
A00020 21.1

The process flow

Gross profit is the total revenue of the store having deducted cost of goods sold. The gross profit for customers only shopping for bargain products is likely to be low. Nevertheless, the gross profit from loyal customers is high. By deriving the gross profit, it helps us differentiate profitable customers and bad customers.

Most commands to create the report is covered in Basic commands. Below is the process flow to generate the report, think through each step carefully before proceeding.

Answer: Process flow

  1. Remove recrods with null customer ID
  2. Select the required columns "customer", "amount", "gross profit"
  3. Calculate the "total amount" and "total gross profit" from the purchases for each customer
  4. Calculate the gross profit margin by dividing toal amount from total gross profit (round off the the nearest 3rd decimal places)
  5. Display columns "customer" and "gross profit margin" in the report

Script

After we have prepared the work flow, let's rethink each step and start creating the script.

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

#---- Title
title="Customer's gross profit margin"

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

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

#---------------------------------------------------------------
# Commands
#---------------------------------------------------------------
xtdelnul -f customer -i $inPath/dat.xt |
xtcut -f customer,amount,grossprofit |
xtagg -k customer -f amount,grossprofit -c sum |
xtcal -c 'round((($grossprofit/$amount)*100),0.1)' -a grossprofitmargin |
xtcut -f customer,grossprofitmargin |
xtheader -l "$title" -c "$comment" -o profit.xt
#===============================================================

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)
The 20 customers with the lowest gross profit margin profit1.sh profit1.xt profit1.html
Top 5 Customer with lowest gross profit margin by Year Month and Day profit2.sh profit2.xt profit2.html