Data Analyst Professional Practical Exam Submission¶

You can use any tool that you want to do your analysis and create visualizations. Use this template to write up your summary for submission.

You can use any markdown formatting you wish. If you are not familiar with Markdown, read the Markdown Guide before you start.

📝 Task List¶

Your written report should include written text summaries and graphics of the following:

• Data validation:
  • Describe validation and cleaning steps for every column in the data
• Exploratory Analysis:
  • Include two different graphics showing single variables only to demonstrate the characteristics of data
  • Include at least one graphic showing two or more variables to represent the relationship between features
  • Describe your findings
• Definition of a metric for the business to monitor
  • How should the business use the metric to monitor the business problem
  • Can you estimate initial value(s) for the metric based on the current data
• Final summary including recommendations that the business should undertake

Start writing report here..

Here's what I'll do for validation and cleaning of each column:¶

• week: Ensure it's a numeric column with no negative or non-integer values.
• sales_method: Confirm that each entry matches one of the three expected methods.
• customer_id: Check for unique values.
• nb_sold: Make sure it's numeric, non-negative, and an integer.
• revenue: It should be numeric and rounded to two decimal places; handle NaN values appropriately.
• years_as_customer: It should be numeric, non-negative, and an integer since the company was founded in 1984 (40 years).
• nb_site_visits: Confirm that it's numeric, non-negative, and an integer.
• state: This should be a character field; no validation needed except for empty strings, perhaps.

All records in this dataset are under unique customers. 15000 unique customers in total.

Since the company was founded in 1984, the two records (13741 & 13800)contain incorrect values in the years_as_customer field. For the moment, I will leave these as they are, but I will mention this in the final recommendations. Additionally, I will be mindful of these anomalies when using the 'years_as_customer' data for future analyses

The week, nb_sold, years_as_customer, and nb_site_visits columns are confirmed to be non-negative integers.

The revenue column is indeed converted to numeric with two decimal places, though there are NaN values present. I will adress it after validating 'sales_method' column

The sales_method column contains inconsistent entries: 'em + call' and 'email', which do not match the predefined categories of 'Email', 'Call', or 'Email + Call'. There are only 33 such entries. Ideally, I would check with the sales department if it's okay to interpret 'em + call' as 'Email + Call' and 'email' as 'Email'. However, considering the circumstances, it seems reasonable to replace these irregular entries with the established categories.

Now all records in the sales_method column contain categories of 'Email', 'Call', or 'Email + Call'

Back to missing values.

There are 1,074 missing values in the revenue column, which constitutes approximately 7.16% of our data. Considering this is a significant but not overwhelming portion of the data, we have 2 options:

• Imputation: Given that revenue is a key metric for analysis, we might want to impute missing values. However, we should consider the distribution of the revenue data and the sales methods to choose an appropriate imputation strategy.

• Exclusion: Since it's a reasonable amount of data and if the missing values are randomly distributed, we could also consider excluding these rows from our analysis. This decision might depend on whether the rows with missing data have any pattern or bias.

Data validation and cleaning¶

Here is a recap of the validation and cleaning performed on each column (20 hidden cells above):¶

week: A numeric column with values ranging from 1 to 6, including 6 unique values with no negative or non-integer values, without missing values. Representing 6 weeks of sale was made, counted as weeks since product launch. No cleaning is needed.

sales_method: column contained 33 inconsistent entries: 'em + call' and 'email', which do not match the predefined categories of 'Email', 'Call', or 'Email + Call'. Ideally, I would check with the sales department if it's okay to interpret 'em + call' as 'Email + Call' and 'email' as 'Email'. However, considering the circumstances, it seemed reasonable to replace these irregular entries with the established categories. After replacement of inconsistent entries, records in the sales_method column contain character categories of 'Email', 'Call', or 'Email + Call'

customer_id: contains unique customer IDs for all records, totaling 15,000 unique customers, with each ID stored as a text field. No cleaning is needed, however, insights into the effectiveness of sales methods on customer recurrence would be enhanced if the dataset included information on repeat purchase behavior.

nb_sold: A numeric column with values ranging from 7 to 16, non-negative integers, representing number of new products sold. No cleaning is needed.

revenue: numeric and rounded to two decimal places values ranging from 32.54 to 238.32; There were 1,074 missing values in the revenue column, which constitutes approximately 7.16% of our data:

• Considering this is a significant but not overwhelming portion of the data, I had 2 options:

-- Imputation: Given that revenue is a key metric for analysis, we might want to impute missing values. However, I should've considered the distribution of the revenue data and the sales methods to choose an appropriate imputation strategy.

-- Exclusion: Since it's a reasonable amount of data and if the missing values are randomly distributed, we could also consider excluding these rows from our analysis. This decision might depend on whether the rows with missing data have any pattern or bias.

The revenue distribution varies greatly between sales methods. Given the presence of outliers, using the median revenue for each sales method seemed to me as an effective strategy for imputation.

years_as_customer: A numeric column with values ranging from 0 to 63, with no negative or non-integer values, without missing values. Since the company was founded in 1984, the two records contain incorrect values of more than 40 years in this field. I will be mindful of these anomalies when using the 'years_as_customer' data for future analysis by filtering them out when using years_as_customer field and will mention this in the final recommendations for sales team.

nb_site_visits: A numeric column with values ranging from 12 to 41, non-negative integers, representing number of times the customer has visited our websitein the last 6 months No cleaning is needed. Yet, additional data detailing site visits since introducing a new product line would offer a clearer perspective on how sales methods influence customer interest in our offerings.

state: Character field with no empty strings, appears to include all valid U.S. states. No cleaning is needed.

Exploratory Analysis¶

How many customers were there for each approach?¶

Based on the last 6 weeks of data, we observe that the email sales method attracted the highest number of customers, with a total of 7,466, which significantly exceeds those attracted by calls and the combined method of email and calls.

The call-only method falls in the middle, drawing 4,962 customers. On the other end, the combined approach of email and calls has attracted the fewest customers, with a total of 2,572. This indicates that, while the combined method might be perceived as more personalized or engaging, it does not necessarily translate to a larger customer base compared to the other methods. Given that the email method required the least effort from the team and yielded the highest number of customers, it appears to be the most efficient strategy among the three tested methods.

However, it's important to consider the revenue generated and customer retention to make a comprehensive evaluation of the sales method effectiveness.

What does the spread of the revenue look like overall?¶

The revenue distribution is visually summarized in the boxplot provided. This type of plot is excellent for understanding the spread and central tendency of a dataset. Here's an interpretation of the overall revenue spread:

Median Revenue: Positioned at 90.95, the median indicates that half of the sales generated less revenue than this amount and the other half generated more. It serves as a robust measure of central tendency that is less affected by extreme values or outliers.

Mean Revenue: Marked by the red 'X', the mean revenue is about 95.57. This value is slightly higher than the median, suggesting a distribution with a positive skew, where higher revenue sales are pulling the mean upwards.

Interquartile Range (IQR): The box represents the middle 50% of the revenue values, spanning from the first quartile (Q1) at approximately 52.65 to the third quartile (Q3) at about 107.75. This range shows where the bulk of the revenues lie and indicates variability in the middle half of the dataset.

Whiskers: The "whiskers" extend to the smallest value in the data and largest value within 1.5 times the IQR from the quartiles, which are approximately 32.54 (lower whisker) and 190.40 (upper whisker). They give a sense of the range of typical values and can help identify outliers.

Outliers: Points beyond the whiskers are considered outliers. In this plot, we can see a group of outliers near the maximum of 238.32, which could be the result of a particularly large sale or an anomaly that may require further investigation.

The boxplot indicates that there is a wide spread in revenue figures, with a tail extending to higher revenues. This suggests that while many sales generate a moderate amount of revenue, there are a significant number of sales with higher revenues contributing to the overall performance.

To further investigate a wide spread in revenue figures, I've constructed the histogram with a kernel density estimate that provides insight into the distribution of revenue. It shows a multimodal distribution, with notable peaks occurring roughly every 50 units of revenue.

These peaks suggest that there are common price points for the products sold, which, when multiplied by the quantity sold, result in these revenue concentrations, which may be extremly valuable to identify customer buying habits and how they might strategically adjust their sales tactics to capitalize on the most common revenue-generating opportunities.

Median number of products sold per each sale is 10, therefore the recurring revenue peaks should correlate with the number of items sold, with a multiplier effect of ten.

To further uncover this pattern, I introduced a column for customers' average price per item sold, calculated by dividing the revenue by the quantity of new products sold per each sale (customer).

As expected, this chart shows that the average price tends to peak at units 5, 10 and 15. This pattern will helps us understand how our revenue data and pricing are connected.

To categorize the average prices into distinct segments and further understand their relationship with the revenue generated, I conducted a statistical analysis. Firstly, I employed a Gaussian Kernel Density Estimate (KDE) to the average price data to identify the distribution's shape and locate its peaks and troughs.

The peaks represent the most common average price points, and the troughs signify potential boundaries between different price segments, that can be found below:

By defining a prominence threshold, I was able to distinguish significant peaks in the data. These peaks were indicative of the most prevalent pricing categories within our customers purchases.

I set up cut points that naturally segmented the price range into categories that I labeled as "Cheap", "Moderate", and "Expensive". These segments were determined by the troughs in the KDE. I discovered that the majority of sales fell into the "Moderate" price category, followed by "Cheap," and then "Expensive.":

I visualized these findings using histograms overlaid with a KDE, color-coded by the newly created average price categories. Now we see a clear division of the price categories, each with its distinct peak frequency.

To make it easier, lets call " "Customers' Average Spend per Product" Category" as Average Price Category further.

The second histogram, 'Revenue Distribution by Customers' Average Price Category' correlates the previously defined price categories with the revenue generated.

This graph offers insights into which price categories are contributing the most to the revenue and helps identify patterns in customer purchasing behavior. Expensive items contribute disproportionately to revenue, this might influence inventory and marketing strategies.

This analysis part has provided a clearer insight into the underlying factors of the recurring peaks at specific revenue intervals, the nature of exceptionally high sales or outliers, the broad range in revenue figures, and the multimodal distribution of sales data.

What does the spread of the revenue look like for each method?¶

The spread of the revenue across different sales methods has been quantitatively depicted in boxplot visualizations. It is evident that the revenue spread differs by sales method:

Calls: This method shows a relatively lower average revenue but with a consistent distribution, as indicated by the compact box and shorter whiskers.

Emails: Display a wider range and higher revenue on average, but with significant outliers that suggest variability in sales performance.

Email + Call: Appears to have the highest average revenue, denoted by the mean, and also the widest range, indicating a diverse set of outcomes from this approach.

This graph offers a quantitative deep dive into the revenue performance of different sales methods. The detailed annotations reveal the insights of the revenue distribution, such as the mean, quartiles, and extreme values, enabling a comprehensive understanding of each sales method's effectiveness.

Further distribution of the number of items sold by each sales method highlights the differences in sales volume, providing clues to the quantity-based preferences of customers depending on the sales approach.

This plot hints that while the 'Call' method has the potential for higher individual sales, 'Email + Call' leads to a bigger number of items sold, but introduces a great variability. In contrast, the 'Email' method, while not reaching the high numbers seen in the 'Email + Call' method, shows steadiness in sales volume.

Exploring further, Average item price distribution between sales methods plot sheds light on the pricing strategies' impact on sales performance. This visualization might suggest how customers respond to price points when engaged through different sales methods.

Calls: This method has the lowest median price point, indicating that the items sold through calls tend to be on the more affordable side.

Email: Sales through email have a slightly higher median price than calls, positioned around 10 units on the price scale, which might suggest a moderate pricing strategy. The IQR is comparable to that of calls.

Email + Call: This combined method shows a substantially higher median price, near the 15-unit mark, suggesting that this dual approach can command higher prices for items sold. Although the IQR is similar, indicating consistent pricing strategy, there are notable extreme outliers indicating that on occasion, this method results in sales with much higher average prices compared to the other methods.

The histogram showing revenue distribution per sales method underlines a pattern that echoes through the previous findings. We see the multimodal nature of revenue distribution, indicating the existence of common sales amounts that resonate across methods.

These multiple layers of analysis converge to suggest a preliminary conclusion that the sales method not only affects the volume and frequency of sales but also the pricing strategies' efficacy. The data suggests that customers might be more responsive to certain sales approaches, which can, in turn, influence the revenue outcome.

A deeper analysis might reveal if these patterns can inform a strategic decision, such as whether to prioritize a particular sales method or tailor pricing strategies to match customer engagement preferences. Therefore, the question I am trying to determine the answer to is:

Do specific sales strategies correlate with various "Customers' Average Spend per Product" Categories (do they draw sales of differently priced items)?

Does the distribution of revenue according to sales methods align with the distribution of revenue across different average price categories?

Considering a 5% significance level, let’s formulate the null and alternative hypotheses.:

$H_0$ : The distribution of revenue across sales methods is independent of the average price category of the items sold.

This implies that there is no relationship between sales methods and "Customers' Average Spend per Product" Category, or sales methods do not attract sales of different average priced goods.

$H_A$ : The revenue distribution across sales methods varies with the average price category of items sold.

This suggests that certain sales methods are more effective with Different "Customers' Average Spend per Product" Categories.

To test these hypotheses, we could use a statistical test such as ANOVA if the data is continuous and normally distributed.

The visual inspection of the revenue distribution within each sales method reveals some skewness. Test for equality of variances across the different sales methods indicates a significant difference in variances, as the p-value is less than 0.05, this implies that the assumption of homogeneity of variances is violated. Given that both the normality and homogeneity of variance assumptions are violated, it might not be the best option to use the traditional ANOVA for this data.

Instead, we can use the Kruskal-Wallis, which is a non-parametric "version of ANOVA".

The Kruskal-Wallis results show a significant difference in revenue distributions across the different sales methods, with a p-value much less than the 0.05 threshold. This suggests that at least one sales method's revenue distribution is significantly different from the others.

Now I will implement the chi-square test of independence, wich is a non-parametric test that compares the observed frequencies of occurrences in each category against the frequencies we would expect to see if the two variables were independent of each other, without making any assumptions about the distribution of the data.

The heatmap of expected frequencies illustrates the distribution of sales across different Sales Methods and Average Price Categories under the assumption that there's no association between these two variables.

However, the chi-square test of independence results in a chi-squared statistic of approximately 29259.42 and a p-value of 0.0, indicating that there is a statistically significant association between the sales method and the average price category. In other words, different sales methods seem to attract sales of products at different price points, or vice versa, different average priced goods are associated with different sales approaches.

To understand which specific categories are contributing to the significant chi-square result, we need to examine the adjusted standardized residuals. This method provides a quick way to identify which specific combinations of categories differ significantly from what would be expected under the null hypothesis of independence

The heatmap of standardized residuals visually demonstrates the deviations between observed and expected frequencies for each combination of Average Price Category and Sales Method. Positive values (warmer colors) indicate a higher-than-expected frequency of sales for the combination, whereas negative values (cooler colors) suggest a lower-than-expected frequency.

Was there any difference in revenue over time for each of the methods?¶

Based on the analysis of cumulative and average revenue over the first six weeks following the launch of the new product line, we can observe notable differences in the performance of each sales method.

The 'Email' method shows a consistent and steep increase in revenue over time, suggesting a strong uptake of the product and an effective sales method. The 'Email + Call' method also demonstrates an upward trend but with a less steep slope compared to 'Email' alone. Interestingly, the 'Call' method shows the slowest growth in cumulative revenue, which could suggest that while this method may build relationships or offer a more personal touch, it does not generate revenue as quickly as the other methods.

The second graph presents the average weekly revenue per sales method. Here, the 'Email + Call' method displays a significant growth in average revenue per sale, particularly in the latter weeks, which could imply that the combination of initial email contact followed by a personal call may encourage larger purchases as time progresses, possibly due to building better customer rapport.

The 'Email' method shows steady average revenue, indicating consistent performance.

The 'Call' method has the lowest average revenue, which aligns with its slower cumulative revenue growth, suggesting it may not be as effective in generating higher revenue per sale.

Initially, the 'Email' sales method accounts for the largest share of revenue, displaying a substantial presence, with a notable increase in the fourth week, potentially correlating with the second email campaign. Despite this peak, the trend suggests that the 'Email' method's growth in revenue decelerates towards the fifth and sixth weeks.

On the other hand, the 'Email + Call' method maintains a steady growth in revenue contribution, overtaking 'Email' in the latter half of the period, specifically from the fourth week onwards. This method appears to gain momentum when the other methods level off, indicating its effectiveness over time.

The 'Call' method, while contributing the least to cumulative revenue, maintains a consistent presence without significant fluctuations.

Considering the effort required for each method, as described, it's important to note that while the 'Email' method requires the least effort, it results in the most significant cumulative revenue. On the other hand, the 'Email + Call' method, despite requiring more effort than email alone, shows the potential for increased average revenue per week, especially in the critical phase when other methods begin to plateau.

The 'Call' method, requiring the most effort, shows the least revenue performance both cumulatively and on average, raising questions about its efficiency.

Based on the data, which method would you recommend we continue to use?¶

Some of these methods take more time from the team so they may not be the best for us to use if the results are similar.

Based on the established hypotheses that different sales methods draw sales to customers that prefere differently priced items, the data reflected in the area chart below:

Particularly for Expensive price Category items, the 'Email + Call' method is excelling. If the goal is to maximize revenue from high-ticket items, this method is performing well + shows the potential for increased average revenue per week.

In the realm of moderately priced goods, the 'Email' approach outperforms others, however the trend suggests that the 'Email' method's growth in revenue decelerates towards the fifth and sixth weeks.

Whereas the 'Call' method, prevails primarily within the Cheap Price Category product range.

The 'Email' method contributes to more than half of the total revenue, which is a significant share. Considering its low time investment, this method offers the best revenue-to-effort ratio during the past 6 weeks period. Meanwhile, the 'Email + Call' method that tops the expensive price segment secures upwards of 30% of the total revenue, with the 'Call' method, which reigns within the lower-priced category, contributing 16.5%.

To make our conclusions even more justified, let's assess the ratio of effort to efficiency, that can be further also used as a business metric to asses the sales methods efficiency during further weeks from the product deployment.

We know following information regarding the three different sales strategies:

• Email: Include sending 2 emails & require very little work for the team.
• Call: Include 1 call ~ thirty minutes per customer.
• Email and call: Include sending 1 email + 1 call ~ ten minutes per customer.

The code below calculates the time efficiency of sales methods. It assigns 30 minutes for calls, a combination of email time plus 10 minutes of calling time for 'Email + Call', and twice the average email time for pure emails,ensuring a minimum of 1 minute to prevent excessively high revenue per minute calculations for any method taking less than a minute per customer. Then it evaluates revenue generated per minute spent on each sale.

I've estimated that it takes approximately 60 minutes to configure CRM emails for a mass delivery session. If this estimate doesn't match your experience, please feel free to modify the variable below to a more accurate number. Once you've made any necessary adjustments, simply execute the 'Run All' command to update the subsequent graphs with the new data.

Nevertheless, because the algorithm calculates the average time spent on emails by dividing the total time dedicated to email outreach by the number of customers who were reached through "Email Only" and "Email + Call" methods, the resulting average time per customer email will be less than a minute, until the total time invested in sending emails exceeds the number of customers who converted through these methods in minutes.

After plotting the weekly revenue in relation to the time spent on sales by average price category and sales method, it's clear that the efficiency of the sales methods can be quantitatively analyzed.

The area graph showcases how revenue stacks up when accounting for the time invested across various pricing tiers and methods.

In particular, the 'Email' method stands out for its significant share of 'revenue per time invested' within the moderate price category, highlighting its efficiency.

In light of these findings, it would be prudent to prioritize the 'Email' method for its superior performance in generating both total and average revenue with minimal time investment. Nevertheless, the 'Email + Call' method's effectiveness in engaging customers for high-value purchases shouldn't be overlooked, suggesting a strategic use of this approach for premium sales.

Based on the data and these insights, I recommend focusing on the 'Email' sales method due to its strong overall performance in both total and average revenue, with the least amount of time required from the team. However, for sales of expensive items where the customer may benefit from a more personal touch, the 'Email + Call' method has proven effective and could be used selectively for high-value or complex sales that justify the additional time investment. The 'Call' method, while valuable for customer relationship building, may not be the best primary strategy due to its lower revenue generation and higher time cost.

Definition of a metric for the business to monitor¶

Sales Performance Index (SPI)¶

How should the business monitor what they want to achieve?

The SPI will be a comprehensive metric derived from three core components, each representing a dimension of the sales process: weekly revenue trends, cumulative revenue impact, and revenue efficiency based on time spent.

• Weekly Revenue Growth Rate (WRGR): Measures the short-term agility and responsiveness of a sales method by calculating the week-over-week percentage change in revenue. A higher WRGR indicates a method's success in quickly increasing sales revenue over time.

• Cumulative Sales Efficiency (CSE): This measures the long-term strategic value, evaluating the total revenue accrued over a period, indicating sustainability and endurance of sales methods.

• Revenue per Time Unit (RPTU): Assesses the operational efficiency of a sales method by comparing the revenue generated to the time invested in sales activities. It provides insight into how efficiently resources are being used to generate revenue.

Calculation Process¶

The SPI calculation process involves several key steps:

• Data Preparation: After ensuring data cleanliness and consistency, we group the data by sales method and average price category to analyze performance across different segments.

Estimate the initial value(s) for the metric based on the current data:

• The WRGR is calculated as the week-over-week percentage change in revenue for each sales method. The values for each method across the weeks are as follows (NaN represents the first week, where a percentage change cannot be calculated):

• Determine CSE by adding up the total revenue for each sales method over the six weeks. If the 'Call' method generated $1,000,000 over six weeks and 'Email' generated 1,500,000, the CSE for 'Email' is 1.5 times that of 'Call'.

• Compute RPTU by dividing the weekly revenue by the time spent on sales activities, adjusted for each price category. If 'Email' for the moderate price category generated 200,000 in a week with a total of 100 hours spent on sales activities, the RPTU would be $2,000 per hour.