Business Intelligence & Data Visualization: JSOE Trends Report

This report analyzes data from John Steed's Online Emporium (JSOE) to identify key business trends. The analysis, performed using pivot tables in Microsoft Excel, reveals four significant trends: increasing order profit for both male and female customers with a widening gap, higher spending by female customers across all age groups, highest customer spending in New South Wales, and peak order profit in July, particularly from female customers. Based on these trends, the report recommends focusing on increasing male customer profit through targeted discounts, further investigating the factors driving higher sales in July, replicating successful strategies from New South Wales, and expanding product variety for men. This data-driven decision-making aims to optimize JSOE's marketing and sales strategies.

Task C
On the basis of the data provided following trends have been found. All the four trends have
been analyzed using the pivot table function in the Microsoft excel. Identified trends are
expected to be useful for JSOE for her future marketing plan.
Trend 1.
The order profit for both male and female has increased over the years, but the gap
between male and female is increasing.
As shown in the table and the figure below the total order profit has increased from 18704976 in
2009 to 25557844. The profit from both the male and the female customers have increased.
However a close look at the data suggests that the increase in female profit order has increased at
much faster rate as compared to the male profit order.
Sum of
Orderprofit
Column
Labels
Row Labels F M
Grand
Total
2009 9409702 9295274 18704976
2010 9217005 8718150 17935155
2011 10003595 9057456 19061051
2012 11753082
1069519
7 22448279
2013 13572484
1198536
0 25557844
Grand Total 53955868
4975143
7
10370730
5

2009 2010 2011 2012 2013
0
2000000
4000000
6000000
8000000
10000000
12000000
14000000
16000000
Yearly Orderprofit
F
M
Year
Amount
Figure 1 Order profit for both male and female between 2009 & 2013
Trend 2
Female customers spend higher than the male in all age group
The second trend shows that the total spending by female customers is higher than the male
customers in all age group. This indicates that the online shopping is more popular among the
females for all age group and men are behind females. This results were also shown in the many
previous studies.
20 to 29 30 to 39 40 to 49 50 Plus Under 20 Unknown Grand Total
F
1632853
2 12275451 13935230
699095
1 2906890 1518814 53955868
M
1446358
0 11623998 14098950
601913
8 2288571 1257200 49751437

20 to 29 30 to 39 40 to 49 50 Plus Under 20 Unknown Grand Total
0
10000000
20000000
30000000
40000000
50000000
60000000
Gender-wise spending pattern
F M
Age group
Amount
Trend 3
The third trend is based on location. Result from location shows that the customers in New South
Wales spend highest on JSOE as compared to the other states. The lowest spending as per the
data is in the NT area.
Customers in NSW spend highest as compared to other states
Row Labels Sum of Orderprofit
NSW 32005479
NT 2724501
QLD 17868432
SA 6412687
TAS 4567635
VIC 26374650
WA 13753921
Grand Total 103707305

0
5000000
10000000
15000000
20000000
25000000
30000000
35000000
Total Order profit
Total
Amount
Trend 4
The fourth trend is the monthly data for male and female on the basis of order profit. As per the
data the higher orderprofit is in the month of July, both from the female and male. This is
followed by August. A close analysis of the order profit patterns shows that the profit
Highest profit in July and female customer brings more profit as compared to male.
Sum of Orderprofit Column Labels
Row Labels F M Grand Total
Jan 3885204 3729125 7614329
Feb 3578349 3559960 7138309
Mar 3749207 3570636 7319843
Apr 3697052 3529657 7226709
May 4410506 4043218 8453724
Jun 4966254 4395640 9361894
Jul 5883273 5063868 10947141
Aug 5413932 4853749 10267681
Sep 5086653 4442436 9529089
Oct 4675733 4132535 8808268
Nov 4152658 3956293 8108951
Dec 4457047 4474320 8931367
Grand Total 53955868 49751437 1.04E+08

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
0
1000000
2000000
3000000
4000000
5000000
6000000
7000000
F
M
Month
Amount
Recommendations:
 Since the order profit for both the female is increasing faster than male, the company
should focus on increasing the profit from male also. For this the company can increase
the discount and offers for male related products. However one reason for the higher
female order profit is may be because female buys most of the required products for
households and men only shop for their personal needs. This will require more detailed
analysis.
 Also the sales are higher in July which means that JSOE should try to capture as much
market as possible in July and find out the factors which led to higher sales in July. If the
factors are identified similar the sales in other months can also be increase with data
driven decision making process.
 Finally the higher sales in NSW suggests that the company should follow similar
strategies as followed in NSW. Similarly JSOE can add more variety of products
especially for the men, which can increase their sales.

Trend 4