Customer Profitability Analysis

READ ALL INSTRUCTIONS COMPLETELY BEFORE PROCEEDING

In this assignment you will evaluate the profitability (actually the revenue) of each customer in the CDW database, form groups (deciles and percentiles) of customers based on revenue, and evaluate those groups. You should turn in a written paper that describes the purpose of the analysis and interprets the results. Include tables and figures that show the following:

1. A listing of the total revenue generated by each of the ten deciles and the percent of revenue contributed by each decile.
2. A bar chart showing the revenue of each decile in descending order from the highest to lowest revenue decile.
3. A line graph of the revenue of each percentile in descending order from the highest to the lowest.
4. A very brief written description of the profile of each of the first few deciles. You may use short bullet points or write a short paragraph for each.

Computing Revenue per Customer

In order to eventually compute deciles and percentiles of revenue, we must first compute total revenue per customer. Note that you must decide several aspects about the revenue measurement. As discussed in class, you must ascertain if any customers are to be excluded from the analysis, what to do with negative amounts of purchase, etc.

Use the DATA, AGGREGATE command window. Enter into the box for the break variable the variable name customer then enter into the box for the aggregate variables the following two variables: amount and count. You must click on the function, which will default to MEAN. You don’t want the mean of the values by customer; you want the sum. So a window will appear when you click on the MEAN function and in that window click on the SUM option (then click OK to get back to the aggregate window). Then give an output file name by clicking on the FILE box in the bottom of the aggregate window (put the file somewhere that you can find it, such as the desktop). Click OK and the aggregate will run and put the data into your output file.

Now you must open that aggregate file (which will automatically close the file you had been working in—you may want to save it first). You should see three variables in the new file, one for customer number and two others that are the sums of amount and count. You may want to change the names of these by clicking on "variable view" at the bottom left of the screen. The default name will be amount_1.

Next you will create two new variables, one to identify the percentile that each customer falls into and another for the decile. To create percentiles: Go to TRANSFORM, RANK CASES. Enter the revenue variable as the variable. Click the option to assign rank 1 to the largest value. Click the RANK TYPES box. Then click the NTILES option and set the umber of ntiles to 99 (it will not take 100).

To compute deciles, repeat the above process exactly the same except set the number of ntiles to 10. The decile and percentile variables will have names such as nti001, nti002. You should change these to decile and percentile.

Evaluating Revenue Distribution

Now you should evaluate the revenues of the percentiles and deciles. To do this you must use the aggregate command to sum the revenue across all customers within a given percentile or decile. I’ll explain it for percentiles, do deciles the same way (the outputs will go to separate aggregate files).

Within the file with the decile and percentile variables, go to DATA, AGGREGATE. Set the break variable to percentile . The aggregate variable is your total revenue per customer variable. Make sure you again set the function for SUM, not MEAN. Click OK to run.

Now open the output file (save the old file as it closes). You should see 99 lines of data. This represents the level of revenue produced by each of the percentiles. Generate a plot showing the distribution of revenues across percentiles. To do this go to GRAPHS, LINE, click the last option—values of individual cases. Then click OK. You should see a curve that descends sharply as is goes from left to right (indicating the unevenness us revenues). Print out this plot.

Repeat the above process for the decile variable. This requires going back to the file where you have the decile variable and doing another AGGREGATE. This time the break variable will be decile . Go to the output file for that aggregation, which will be 10 lines long. Compute the sum of revenues across the 10 deciles. One way to do this is to do a STATISTICS, DESCRIPTIVES and find the mean of the revenues for the decile then multiply the mean by 10 to get the total revenues. Use the total amount as the denominator to calculate the percent of total revenue each decile accounts for. Do this with TRANSFORM, COMPUTE and set a new variable called portion . In the compute box, enter an equation that divides the revenue total for that decile by the figure you found for the total of all deciles. Print out the ten-line file showing the amount and proportion of revenue from each decile. Also create a Bar chart of the deciles by going to GRAPHS, BAR, and clicking the option for values of individual cases. The bars should become lower as you move to the right. Print out the bar chart. (Note—the vertical axis should be dollar amount, not percents.)

The last step is evaluating the characteristics of the people in the deciles. Limit yourself to looking at the top 2 or 3 deciles. Give a cursory overview demonstrating the nature of the purchase behavior of the customers in those top deciles (e.g., products purchased most, average number of purchases).

Price: $7.98

Solution: The downloadable solution consists of 5 pages, 298 words and 3 charts.
Deliverable: Word Document