Scribd
Upload
125 views5 pages

Monthly Customer Order Analysis

The document contains order data for various customers from December 2019 to April 2020. It provides information on the number of new and repeat customers each month, as well as lifetime metrics like new versus repeat customers overall and the number of heavy, active and dormant customers based on order frequency thresholds.

Uploaded by

Chinmaya Mishra
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
125 views5 pages

Monthly Customer Order Analysis

The document contains order data for various customers from December 2019 to April 2020. It provides information on the number of new and repeat customers each month, as well as lifetime metrics like new versus repeat customers overall and the number of heavy, active and dormant customers based on order frequency thresholds.

Uploaded by

Chinmaya Mishra
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

Order Date Customer Order Value

Sunday, December 01, 2019 Customer1 100


Sunday, December 01, 2019 Customer5 150
Wednesday, January 01, 2020 Customer2 80
Sunday, March 01, 2020 Customer1 150
Sunday, March 01, 2020 Customer2 150
Sunday, March 01, 2020 Customer1 100
Sunday, March 01, 2020 Customer3 200
Saturday, January 25, 2020 Customer4 200
Tuesday, February 25, 2020 Customer6 200
Wednesday, January 01, 2020 Customer4 200
Wednesday, April 01, 2020 Customer3 150
Wednesday, April 01, 2020 Customer3 200
Wednesday, April 01, 2020 Customer4 200

Format for submission of answers

Month Rep_Column_month # New customers in the month

Sunday, December 01, 2019 Dec'19 2


Wednesday, January 01, 2020 Jan'20 2
Saturday, February 01, 2020 Feb'20 1
Sunday, March 01, 2020 Mar'20 3
Wednesday, April 01, 2020 Apr'20 2
Rep 2 ID Occurrence of ID
Dec'19 Dec'19Customer1 1
Dec'19 Dec'19Customer5 1
Jan'20 Jan'20Customer2 1
Mar'20 Mar'20Customer1 2
Mar'20 Mar'20Customer2 1
Mar'20 Mar'20Customer1 2
Mar'20 Mar'20Customer3 1
Jan'20 Jan'20Customer4 2
Feb'20 Feb'20Customer6 1
Jan'20 Jan'20Customer4 2
Apr'20 Apr'20Customer3 2
Apr'20 Apr'20Customer3 2
Apr'20 Apr'20Customer4 1

# Repeat customers in the


# New customers lifetime # Repeat customers lifetime
month

0 2 4
1
0
1
1
Occurrence of customer Calculated paramecalculated parameter (2)
3 1 0.333333333333333
1 1 1
2 1 0.5
3 0.5 0.333333333333333
2 1 0.5
3 0.5 0.333333333333333
3 1 0.333333333333333
3 0.5 0.333333333333333
1 1 1
3 0.5 0.333333333333333
3 0.5 0.333333333333333
3 0.5 0.333333333333333
3 1 0.333333333333333

# active
# heavy customers in the month customers in the # dormant customers in the month
month
Please build the logic & calcuate the following :
1. Number of new users/ repeat users in each month
2. Number of new users/ repeat users in lifetime
3. Number of heavy customers
4. Number of active customers
6. Number Dormant customers

Definitions :
1. New user in the month : Customer who has ordered at least once in that month
2. Repeat user in the month : Customer who has ordered more than once in that month
3. New user in lifetime : Unique customer in overall database
4. Repeat user in lifetime : Customer who has ordered more than once in lifetime
5. Heavy customer : Customer who has ordered more than once in last 90 days
6. Active customer : Customer who has ordered at least once in last 90 days
7. Dormant customer : Customer who has not ordered in last 90 day

3
e in that month

not ordered in last 90 days

4 1 0.5

9 1

You might also like