Quality Alloys Report
Carolina Duque Chopitea
12/16/2019
Web Analytics at Quality Alloys, Inc: Marketing their business and increasing sales
Executive Summary:
Company background
Quality Alloys (QA) is a small to medium size distributor that provides different types of alloys to various manufacturers. QA’s major customers are small companies that need specific alloys for various operations. These customers tend not to inventory, they typically purchase what they need for a specific job, making it hard for QA to predict future purchases. QA is a well stablished company in its niche market: it has expertise in suppling different grade of alloys; it sells in small quantities; cuts materials for user-specified size:, and QA ships items in stock the same or next day, making them a go-to solution for small companies which may find it difficult in finding the right supplier for their needs and often cannot buy directly from the mills.
Currently, QA markets its products through direct mail, advertising in trade magazines and listing on web portals. In 2008, to extend its marketing reach the company established a website. The website aims at driving new sales, make products and contact information available, and add legitimacy to QA’s brand. The website is also seen as a tool to reach out to more customers in the US, Europe and Asia. The website does not have a “shopping cart” but it allows for potential customers to submit inquiries, this often makes it hard to determine the value of the website. In addition, in an attempt to increase sales, the company invested $25k in brochures, providing an overview of the company’s products and services that were distributed to potential customers in mid-December.
Currently, most people have been reaching their website through Internet Explorer and using google as their main search engine. The top referring site is ‘googleads.g.doubleclick.net’ which tops GlobalSpec and ThomasNet.com, industrial product web portals where QA have listings. As well, most of their visits are from people located in South America followed by North America.
Business problem
QA needs support in measuring the value added of their website. In other words, QA wants to understand if the investment in the website is yielding greater revenues and increasing visibility of the company.
QA provided data about their website, financials and demographics. This data was used to understand the performance of the website as an advertising channel and driver of sales. In turn the insight derived from the analysis can help determine which marketing channels are worth promoting, and which need restructuring or should be abandoned. Therefore, the goal of this analysis is to provide recommendations as how they might best market their business with an aim towards improving sales.
At first glance seems that potential customers are visiting their website. From May 25, 2008 to August 29, 2009 an average of 150 people visited their website daily. Given that they are a small to medium size company, the website seems to be yielding some interest. It was also observed that the promotional period also drove traffic to the website. However, after analyzing the data provided by QA, it can be determining that even as it appears that the website is sparkling interest, visits are not translating into revenue (please see Figure A and Raw Analysis section for further details). Currently, sales are mainly driven by pounds sold and visit to the website seem to not have an impact on sales. As well, the analysis demonstrated that the increased in visits during the promotional period did not directly translated into revenue (please see Analysis section for further details).
Recommendations
For detailed explanation of the recommendation please see Conclusion and Recommendation section of the report.
1) No more resources should be spent printing and mailing brochures.
2) An A/B test that includes a “shopping cart” can be used to test if a direct check out can increase sales.
3) The investment currently done in referring sites and search engines should be preserved, particularly for refering stes like GlobalSpec and ThomasNet, sites that particularly target customers looking for alloys.
4) To target the Asian market (as desired), QA should advertise on search engines such as Baidu or other search engines that are used in the region, as currently most searches are done from engines not used in the region.
5) Engage in an online/email promotional campaign particularly targeting Asia customers to increase web traffic and sales from this region.
6) Inquiries, which are not happening through the website, are not resulting in sales. This means that the sales team are not closing sales after a potential customer has reach out. Offering new training for the sales team will be imperative for increasing sales.
Figure A: There appears to be no linear relationship between revenues and website visits.
Raw Anaysis
Analyzing the value of QA’s website - web and non-web metrics
Data Quality
QA provided a dataset that covers the period from May 25, 2008 to August 29, 2009, only one dataset with information about pounds sold covers a period from January 2005 to mid-July 2010. Overall, there were no major issues with the data, including no missing values and therefore no imputations needed to be carried out. One issue with the data includes the fact that records do not indicate if inquiries were generated through the website or offline. However, further analysis of the data will help derive insights on whether there is a relationship between website visits and inquiries.
R was used as the main tool to conduct statistical analysis. In addition, most of the analysis divided the data into four periods, these are: the Initial Period from May 25 to August 23, 2008, the Pre-promotion from August 24 2008 to Jan 24 2009, Promotion Period from January 25 to May 16, 2009 and Post Promotion from May 17 to August 29, 2009.
Descriptive statistics
To have a better understanding of our data, its distribution and measures of central tendency, a basic statistical analysis of visits to QA website, revenue, profit and pounds sold over time was conducted.
Firstly, a bar chart with Unique Visits and Weeks was created (Figure 1). It can be observed that during the promotional period unique visits to the website increased, decreasing afterward to a lower level than the initial and pre-promotion period. Revenue and pounds sold in turn seem to have a constant pattern over time, appearing unaffected by the promotional period (see also Figure 2, Figure 3 and Figure 4).
Figure 1: Unique Visits Over Time
Figure 2: Revenue Over Time
Figure 3: Profit Over Time
Figure 4: Pounds Sold Over Time
Evaluating Sucess of Various Periods
Measures of central tendency where also observed for each of the four period for the variables visits (Visits), unique visits (Unique.Visits) revenue (Revenue), profits (Profit) and pounds sold (Lbs.Sold) (see Table 1, Table 2, Table 3, Table 4 in Appendix). It can be observed that visits to the website peeked during the promotional period with a maximum of 3726 per week. Revenue per week in turned peek during the pre-promotional period (951,216) and reach a minimum during the post-promotional period (133,967) and pound sold also reach the highest during a Pre-promotional week. This early insights indicate than even if the promotion increases visits to QA website, neither the promotion nor the visit increase pounds sold and revenue.
Table 1: Initial Period Descriptive Statistics —> May 25 to August 23
| Measure | Visits | Unique_Visits | Revenue | Profit | Lbs_Sold |
|---|---|---|---|---|---|
| Min. | 734.00 | 668.00 | 274567.6 | 62580.40 | 8633.06 |
| Max. | 1632.00 | 1509.00 | 890076.7 | 275218.10 | 28052.92 |
| Mean | 1088.23 | 1005.31 | 595953.3 | 199199.59 | 18127.81 |
| Median | 906.00 | 850.00 | 553054.7 | 204153.00 | 17230.83 |
| Std. div. | 346.44 | 312.35 | 155071.2 | 63041.34 | 5127.16 |
Table 2: Pre-promotion Period Descriptive Statistics —> August 24 to January 24
| Measure | Visits | Unique_Visits | Revenue | Profit | Lbs_Sold |
|---|---|---|---|---|---|
| Min. | 383.00 | 366.00 | 315647.1 | 100388.4 | 8992.42 |
| Max. | 795.00 | 734.00 | 951216.2 | 273174.7 | 31968.98 |
| Mean | 565.82 | 520.32 | 544940.6 | 162374.8 | 18814.04 |
| Median | 559.50 | 510.50 | 536096.8 | 152949.0 | 17999.44 |
| Std. div. | 80.06 | 71.16 | 155103.3 | 43201.1 | 6079.42 |
Table 3: Promotion Period Descriptive Statistics —> January 25 to May 16
| Measure | Visits | Unique_Visits | Revenue | Profit | Lbs_Sold |
|---|---|---|---|---|---|
| Min. | 1000.00 | 930.00 | 268159.5 | 81841.40 | 7814.05 |
| Max. | 3726.00 | 3617.00 | 897163.7 | 266476.70 | 31496.26 |
| Mean | 1853.31 | 1776.44 | 460102.5 | 133173.01 | 17212.25 |
| Median | 1668.50 | 1599.00 | 415832.2 | 118507.85 | 17336.77 |
| Std. div. | 765.19 | 750.49 | 166299.3 | 49058.95 | 6719.57 |
Table 4:Post-promotion Period Descriptive Statistics —> May 17 to August 29
| Measure | Visits | Unique_Visits | Revenue | Profit | Lbs_Sold |
|---|---|---|---|---|---|
| Min. | 772.00 | 709.00 | 133966.9 | 32825.30 | 3825.75 |
| Max. | 1191.00 | 1137.00 | 615950.2 | 206441.20 | 23761.61 |
| Mean | 878.87 | 823.20 | 373422.2 | 111112.11 | 14640.85 |
| Median | 860.00 | 811.00 | 360032.6 | 106085.10 | 14535.71 |
| Std. div. | 110.10 | 111.34 | 140580.5 | 47281.18 | 5730.63 |
As well, the mean for each period was calculated (see Table 5) and compared. What can be observed from Table 5 and Figure 5, Figure 6, and Figure 7, once again is that visits to QA website increased during the promotion period and remained the lowest during the pre-promotion period, indicating that the promotion was effective in increasing brand awareness. Notwithstanding, revenues linearly decreased from the initial period to the post-promotion period, demonstrating that the promotion seem to not have an effect in increasing sales. In addition, even though pounds sold did not significantly decreased, they are also constantly decreasing from the initial to the post-promotion period.
Table 5: Means for each Period
| Phase | Visits | Unique_Visits | Revenue | Profit | Lbs_Sold |
|---|---|---|---|---|---|
| Initial | 1088.23 | 1005.31 | 595953.3 | 199199.6 | 18127.81 |
| Pre-Promo | 565.82 | 520.32 | 544940.6 | 162374.8 | 18814.04 |
| Promotion | 1853.31 | 1776.44 | 460102.5 | 133173.0 | 17212.25 |
| Post-Promo | 878.87 | 823.20 | 373422.2 | 111112.1 | 14640.85 |
In short, by observing descriptive statistics of the data it can be concluded that the promotion in fact increased visit to the website and therefore awareness about the brand, however, this effect was not translated into increased pounds sold and revenues. In fact, these two appear to be decreasing.
Relationship Between Variables
As mention, it appears that the website is not been effective in producing cells, but to factually conclude whether the website is effective, meaning that it yields more revenue, it is essential to examine the relationship between revenue and visits as well as other variables that may have an impact on revenue. Firstly, we look at the relationship between revenue and pounds sold, then at revenue and visits. By looking at these relationships it can be observed what variables are driving revenues. Further, the relationship between revenue and inquires, revenue and average time spent on the website, revenue and percentage of new visits were also observed as to gain additional insight on what variables are driving revenues.
There is a clear linear relationship between revenue (Revenue) and pounds sold (Lbs.Sold) (see Figure 7). Also, the statistical significance of the relationships, known as the p-value (in this case of 2e-16) is in an acceptable level (smaller than 0.05). Therefore, this relationship is of statistical significance. It was found that for every extra pound sold, my revenue will increase by $24.57. The findings in this simple regression model also indicate that 75.5% (R-squared) of revenues are explained by pounds sold (See Linear Regression:Pounds Sold and Revenue ).
Linear Regression: Revenue & Visists
##
## Call:
## lm(formula = Revenue ~ Lbs.Sold, data = web1)
##
## Residuals:
## Min 1Q Median 3Q Max
## -264664 -48183 1789 43142 179868
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 69380.947 32112.308 2.161 0.0345 *
## Lbs.Sold 24.568 1.749 14.045 <2e-16 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 85590 on 64 degrees of freedom
## Multiple R-squared: 0.755, Adjusted R-squared: 0.7512
## F-statistic: 197.3 on 1 and 64 DF, p-value: < 2.2e-16Furthermore, a simple regression model and a scatter plot between revenue (Revenue) and visits (Visits) does not show a clear relationship (see Figure 9). In the regression, it can be observed that only 0.35% of revenues seemed to be explained by website visits. However, the relationship between these variables has no statistical significance (p-value of 0.636). Given these insights, it can be inferred as suspected, that the website is not currently effective in driving revenue. What is causing this to be the case if however unknown. Regardless, it is indisputable that in the “Digital Era” companies need a website to make their content available, especially as the company seeks to increase sells, make their information available and expand their business. The website should remain a key effort in contributing towards these goals, however a more cohesive strategy should be developed to ensure that the website yields value and targets the right customers.
Linear Regression: Revenue & Visists
##
## Call:
## lm(formula = Revenue ~ Visits, data = web1)
##
## Residuals:
## Min 1Q Median 3Q Max
## -365402 -123490 -10000 113883 448733
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 512241.05 41199.59 12.433 <2e-16 ***
## Visits -15.97 33.55 -0.476 0.636
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 172600 on 64 degrees of freedom
## Multiple R-squared: 0.003527, Adjusted R-squared: -0.01204
## F-statistic: 0.2266 on 1 and 64 DF, p-value: 0.6357As stated, revenues and website visit do not seem to have a statistically significant correlation, while revenues and pounds sold do. To understand what other variables may be driving revenues, the relationship between it and other variables was also examined:
Revenue (Revenue) and inquiries (Inquiries): One would have expected that revenues and inquiries have a statistical significance relationship. However, according to the data, this is not the case. Inquiries can be made via the website or by directly contacting the Quality Alloys. What the lack of statistical relationship means is that inquiries are not necessarily resulting in sales. This in turn represent an area where the sales team can improve – converting inquiries into sales to yield greater revenue. As well it appears that there is no relationship between visits and inquiries, meaning that most inquiries are not been done through the website.
Revenue (Revenue) and average time spent on the website (Time.site.sec): It appears that these two variables have a stronger relationship (p-value 0.054), and 5.67% of revenues can be explained by the time people spend on the site. As previously explained revenues and visit do not appear to have a significant relationship, however the time people spend on the site slightly does. This means that the more time people spend on the website, the more likely they are to purchase Quality Alloy products.
Revenue (Revenue) and percentage of new visits (New.Visits): There is a weak and negative relationship between these two variables (p-values 0.081). Even if this relationship is not statistically significant as to make strong assumptions, it also represents a space for improvement, a relationship does not necessarily needs to be present between these two, however it would have been expected that there is a positive relationship between these two (new visits more revenue). As well there is no relationship between new visits and inquiries , meaning that newcomers to the website are not submitting inquiries. This represents an area of improvement.
##
## Call:
## lm(formula = Revenue ~ Inquiries, data = web1)
##
## Residuals:
## Min 1Q Median 3Q Max
## -363363 -125377 -3940 116455 450768
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 475502 53960 8.812 1.21e-12 ***
## Inquiries 3118 7757 0.402 0.689
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 172700 on 64 degrees of freedom
## Multiple R-squared: 0.002519, Adjusted R-squared: -0.01307
## F-statistic: 0.1616 on 1 and 64 DF, p-value: 0.689
##
## Call:
## lm(formula = Inquiries ~ Visits, data = web1)
##
## Residuals:
## Min 1Q Median 3Q Max
## -5.1755 -1.6513 -0.2487 1.7783 9.7188
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 5.9286970 0.6607913 8.972 6.33e-13 ***
## Visits 0.0004423 0.0005382 0.822 0.414
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 2.769 on 64 degrees of freedom
## Multiple R-squared: 0.01044, Adjusted R-squared: -0.00502
## F-statistic: 0.6753 on 1 and 64 DF, p-value: 0.4142
##
## Call:
## lm(formula = Inquiries ~ Pageviews, data = web1)
##
## Residuals:
## Min 1Q Median 3Q Max
## -5.009 -1.913 -0.198 1.802 9.761
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 5.2280167 0.9526354 5.488 7.44e-07 ***
## Pageviews 0.0005365 0.0004098 1.309 0.195
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 2.747 on 64 degrees of freedom
## Multiple R-squared: 0.02608, Adjusted R-squared: 0.01086
## F-statistic: 1.714 on 1 and 64 DF, p-value: 0.1952
##
## Call:
## lm(formula = Revenue ~ Time.site.sec, data = web1)
##
## Residuals:
## Min 1Q Median 3Q Max
## -372461 -115805 -10850 90343 465065
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 359583.2 72307.1 4.973 5.23e-06 ***
## Time.site.sec 1812.9 924.6 1.961 0.0543 .
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 168000 on 64 degrees of freedom
## Multiple R-squared: 0.05667, Adjusted R-squared: 0.04193
## F-statistic: 3.845 on 1 and 64 DF, p-value: 0.05426
##
## Call:
## lm(formula = Revenue ~ New.Visits, data = web1)
##
## Residuals:
## Min 1Q Median 3Q Max
## -384285 -113730 -6174 106223 455129
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 1377033 498274 2.764 0.00746 **
## New.Visits -1014867 573102 -1.771 0.08135 .
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 168800 on 64 degrees of freedom
## Multiple R-squared: 0.04671, Adjusted R-squared: 0.03181
## F-statistic: 3.136 on 1 and 64 DF, p-value: 0.08135
##
## Call:
## lm(formula = Inquiries ~ New.Visits, data = web1)
##
## Residuals:
## Min 1Q Median 3Q Max
## -5.4920 -1.6717 -0.1426 1.5337 9.4709
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 16.447 8.117 2.026 0.0469 *
## New.Visits -11.572 9.336 -1.240 0.2197
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 2.75 on 64 degrees of freedom
## Multiple R-squared: 0.02345, Adjusted R-squared: 0.008187
## F-statistic: 1.537 on 1 and 64 DF, p-value: 0.2197Multiple Regression Model: A multiple regression model was developed with those variables that showed to have stronger relationship to revenue, namely pounds sold, and time spent on website. These two variables explain 76.8% of the revenue and can be used to predict revenue
Multiple Regression Model
## (Intercept) Lbs.Sold Time.site.sec
## 11784.96551 24.09845 877.20601##
## Call:
## lm(formula = Revenue ~ Lbs.Sold + Time.site.sec, data = web1)
##
## Residuals:
## Min 1Q Median 3Q Max
## -256664 -40924 -1385 52428 173659
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 11784.966 43956.625 0.268 0.789
## Lbs.Sold 24.098 1.734 13.900 <2e-16 ***
## Time.site.sec 877.206 466.997 1.878 0.065 .
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 83950 on 63 degrees of freedom
## Multiple R-squared: 0.768, Adjusted R-squared: 0.7607
## F-statistic: 104.3 on 2 and 63 DF, p-value: < 2.2e-16Continuing with the analysis, data from pound sold during the period of January 3, 2005, through the week of July 19, 2010 was analyzed. Understanding the behavior of pounds sold, can give insights and depending on the distribution of the data, it can perhaps be used to model future pound sold. Figure 16 shows a histogram with the frequency of the quantity of material sold during the above-mentioned period. Despite the financial crisis in 2008 mean pounds sold seem to remain constant from 2005 (pre-crisis)to 2010 (post-crisis) but revenues appear to be decreasing, indicating that even as pounds sold remain more or less constant revenues are declining.
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 3826 14204 17673 18682 22765 44740
## Min. 1st Qu. Median Mean 3rd Qu. Max.
## 133967 372374 484857 495440 613587 951216Finally, pounds sold and daily visits where compered. It was found that increasing the number of visits does not increases the number of pounds sold (Figure 17 and Figure 18) (Period where distributed as follow - Initial: 0~14, pre-promo: 15~35, promo: 22~52, post-promo: 53~66). During the promotion period, number of visits peeked, but promotion did not affect pounds sold. As well pounds sold seem to fluctuate continuously with the time (but mean pounds sold by year remain more or less constant). This can perhaps be related to the fact that QA customers do no inventory.
