7117-IBA Univariate And Multivariate Analysis For ABC Retail Organization

Q1. What's the total amount of money spent per weekend on each shopping week?

Q2. How much money was spent by each age group?

Q3. What is the total quantity sold and total amount of sales of each product in each state? (make sure to include the State Name. hint: join tables)

Q4. Which 20 products have the highest sales in value where more than 1 item was sold?

Q5. How many unique customers does region W01 have?
 

 

Answer:

Introduction

Business intelligence is the buzzword of data science in today’s world. Informed decision making is the crux of business intelligence by crunching data via cutting edge statistical softwares (Marr, 2016). SAS (Statistical Analysis System) is one of the industry leading business intelligence platforms that is used by industry leaders to take informed decisions by treating huge unstructured data (Windham, 2014). SAS is expert in the following areas:

• Univariate and multivariate analysis
• Data mining & visualization
• Advanced reporting
• Inferential analysis

In this exercise with the help of SAS Enterprise edition, the objective is to analyze the transactions of a retail organization i.e. ABC Retail, an organization based out of United States of America.

Analysis

Question 1 

Amount spent on each shopping week

 

SHOP_WEEK	TOTAL SPEND
200607	$13,797.83
200609	$14,785.95
200612	$14,341.26
200619	$14,652.97

SAS Code 

ODS HTML FILE ="C:UsersuserDesktop703671_solutionmyZip_7070071.html";

PROC SQL;

SELECT SHOP_WEEK, SUM(SPEND) "TOTAL SPEND" FORMAT DOLLAR16.2

FROM mylib.data

GROUP BY SHOP_WEEK;

QUIT;

ODS HTML Close;

Title "Amount spent on each shopping week"; 

The highest spend has been in the 9^th week in the year 2006 and minimum in the 7^th week of year 2006. There has been a decline in the expenditure on 9^th week to 12^th week in the year 2006. Therefore, a considerable increase has been witnessed between the week 7 and week 9. There is a slight fall in the expenditure in week 12 followed by increase in the 19^th week. 

Question 2 

Amount spent on each shopping week

 

CUST_LIFESTAGE	TOTAL SPEND
XX	$17,194.16
OA	$5,805.45
OF	$3,097.99
OT	$12,821.90
PE	$3,123.07
YA	$5,987.48
YF	$9,547.96

SAS Code

ODS HTML FILE = "C:UsersuserDesktop703671_solutionmyZip_7070072.html";

PROC SQL;

SELECT CUST_LIFESTAGE, SUM(SPEND) "TOTAL SPEND" FORMAT DOLLAR16.2

FROM mylib.data

GROUP BY CUST_LIFESTAGE;

QUIT;

ODS HTML Close;

Title "Amount spent on each shopping week"; 

There is one grey area for the company as because the maximum spend has been witnessed for XX i.e. the unclassified segment followed by OT i.e. the others segment. The young adults (YA) spends $ 5,987.48, the older adults (OA) spends $ 5,805.45, the young families (YF) spends $ 9,547.96, the pensioners (PE) spends $ 3,123.07 and older families (OF) spend $ 3,097.99. 

Question 3 

Total quantity sold and total amount of sales of each product in each state 

Total quantity sold and total amount of sales of each product in each state

 

Total QTY	TOTAL SALE AMOUNT	PRODUCT CATEGORY	STORE_STATE
764	$971.85	Fresh	ACT
7154	$9,288.67	Fresh	NSW
1488	$1,904.00	Fresh	NT
2095	$2,915.86	Fresh	QLD
2513	$3,199.16	Fresh	SA
892	$1,127.97	Fresh	TAS
4436	$5,882.56	Fresh	VIC
1984	$2,783.51	Fresh	WA
232	$360.06	Grocery	ACT
1792	$2,469.06	Grocery	NSW
335	$355.43	Grocery	NT
572	$716.94	Grocery	QLD
641	$961.37	Grocery	SA
212	$305.16	Grocery	TAS
1033	$1,440.92	Grocery	VIC
522	$582.11	Grocery	WA
655	$910.14	Mixed	ACT
5206	$6,797.76	Mixed	NSW
1097	$1,558.58	Mixed	NT
1620	$2,125.31	Mixed	QLD
1916	$2,379.70	Mixed	SA
633	$797.37	Mixed	TAS
3391	$4,438.74	Mixed	VIC
1495	$1,954.23	Mixed	WA
24	$66.14	Nonfood	ACT
173	$296.81	Nonfood	NSW
26	$47.50	Nonfood	NT
60	$90.87	Nonfood	QLD
76	$192.23	Nonfood	SA
19	$74.14	Nonfood	TAS
110	$201.09	Nonfood	VIC
48	$79.11	Nonfood	WA
1	$9.27	XX	ACT
27	$178.37	XX	NSW
1	$4.72	XX	QLD
7	$34.81	XX	SA
1	$0.96	XX	TAS
5	$31.06	XX	VIC
8	$44.47	XX	WA

SAS Code

ODS HTML FILE = "C:UsersuserDesktop703671_solutionmyZip_7070073.html";

PROC SQL;

SELECT SUM(QUANTITY) "Total QTY", SUM(SPEND) "TOTAL SALE AMOUNT" FORMAT DOLLAR16.2, BASKET_DOMINANT_MISSION "PRODUCT CATEGORY", STORE_STATE

FROM mylib.data

GROUP BY BASKET_DOMINANT_MISSION, STORE_STATE;

QUIT;

ODS HTML Close;

Title "Total quantity sold and total amount of sales of each product in each state";

It can be seen that the highest selling product is Fresh and is being sold in the state of Victoria followed by mixed product category which is also highest in the state of Victoria i.e. VIC. Tasmania, Australian Capital Territory and West Australia have witnessed the minimum selling stocks. 

Question 4

20 products having the highest sales in value where more than 1 item was sold

 

Row	TOTAL SALE AMOUNT	PROD_CODE	QUANTITY
1	$13.14	PRD0901637	9
2	$11.13	PRD0902765	3
3	$9.63	PRD0900508	3
4	$8.64	PRD0902163	3
5	$6.60	PRD0904911	3
6	$6.39	PRD0904806	3
7	$5.79	PRD0901512	3
8	$5.46	PRD0904461	3
9	$5.46	PRD0903269	6
10	$4.89	PRD0902907	3
11	$4.05	PRD0901488	3
12	$3.84	PRD0902232	3
13	$3.78	PRD0900940	3
14	$3.57	PRD0900947	3
15	$2.73	PRD0903269	3
16	$2.12	PRD0901672	4
17	$1.64	PRD0900407	4
18	$1.62	PRD0902742	3
19	$0.96	PRD0903471	3
20	$0.84	PRD0900684	3

SAS Code

ODS HTML FILE = "C:UsersuserDesktop703671_solutionmyZip_7070074.html";

PROC SQL number;

Select SPEND "TOTAL SALE AMOUNT" FORMAT DOLLAR16.2, PROD_CODE, QUANTITY

FROM mylib.data (firstobs = 1 obs = 20)

where QUANTITY>1

order BY SPEND desc;

quit;

ODS HTML Close;

Title "20 products having the highest sales in value where more than 1 item was sold";

The product  that got sold maximum is PRD0901637 and the least selling product is PRD0900684. 

Question 5 

Row	Unique Customers	STORE_REGION
1	1857	W01

SAS Code

ODS HTML FILE = "C:UsersuserDesktop703671_solutionmyZip_7070075.html";

PROC SQL number;

Select COUNT (DISTINCT CUST_CODE) " Unique Customers", STORE_REGION

FROM mylib.data

where STORE_REGION='W01';

quit;

ODS HTML Close;

Title "Unique customers in store region W01"; 

The store region W01 has 1857 unique customers. 

III. Creative Insights

The organization has to focus on several areas to calibrate its sales and give strategic dimension.

 

• Identify the sales properly in the unclassified and others segment
• Pitch marketing activities between 7^thand 9^th week
• Calibrate marketing activities in Tasmania , Australian Capital Territory and West Australia
• Non-food segment has to be focused properly
• Focus on the young families

1. Creative Insights

Through the study, we could identify various grey areas of the business and provide good strategies for future business development. Pin point analysis has been possible through crunching data in SAS which would not be possible otherwise. The exercise also helped to focus on SAS coding which is very much necessary for finding useful insights from the data.

Conclusion

The study has helped in deriving various useful insights about the business of ABC Retail company by analysing its transactions by using SAS, one of the most reputed business analytics software used in the industry. The areas of concerns have been identified and subsequent remedies have been recommended.  

References

Marr, B., (2016). Key Business Analytics: The 60+ Business Analysis Tools Every Manager Needs To Know. Pearson UK

Windham, M, (2014). Introduction to Regular Expressions in SAS. SAS Institute