Data Analysis by SQL - Essay Example

Part Data Analysis by SQL The aim of this management report is to analyse the current database of a company and to identify any behaviour patterns, in terms of consumer behaviour, products or suppliers. This will provide the management with valuable insight about the various aspects of the business and enable them to make appropriate decisions and plans for improvement. The report also will provide suitable recommendations for improvement based on the findings. Customer Behaviour: The customers are the most valuable assets for any business. As an initial step, the customer database is segmented to find the most valuable customers. This is carried out by segmenting the customers based on their Recency, Frequency and Monetary value (RFM) of their purchases. Apart from this, the customer and order database are analysed to based on the products purchased, location of the customer, handling employees and the shipping preferences, to identify any patterns and behaviour. Based on the results obtained, it is evident that the highest value customers in terms of the revenue are mainly from Europe, including, Denmark, Germany and France. The total revenue from US customers amounts to $ 56,029, whereas the revenue from France is $ 68,011. By identifying the products bought by the customers, their preferences can be identified. This will enable the firm to create a customer profile and make the appropriate products readily available in the proper locations. It will also be beneficial to identify the most preferred shipping service of the customers. From the database, it is evident that the United Package has been chosen for 326 times, the Federal Express was preferred for around 255 orders, whereas Speedy Express for 249 orders. Hence it is evident that the three shipping services are equally important. Analysis of Products: The orders are analysed based on the products, in order to identify the most preferred and the highest revenue generating products. The high revenue generating categories are also identified and the role of discounts in the orders are also analysed. From the analysis, the highly sold product has been found as Gorgonzoo Telino whereas the product generating the highest revenue is Cote de Blaye. However, when analysing the categories, it is found that the best selling categories in terms of revenue and number of products are Beverages, Dairy products and Confections. It is imperative to note that the least revenue generating revenues include Produce and Grains and Cereals. Also, analysing the discounts applied indicates that there was no algorithm set in deciding discounts and they were applied arbitrarily, based on the situation. Analysis of Suppliers: The suppliers of high demand and high value products are identified in order to formulate a supplier relationship strategy. The high value suppliers are identified by analysing the products that are highly sold and the corresponding suppliers. The discontinued products are also analysed on the basis of suppliers. The high value suppliers identified include, Pavlola Ltd., Plutzer and Gai pasturage. It is interesting to note that products from two of these suppliers have been discontinued. It should also be noted that the products in high demand, in terms of quantity are supplied by Formaggi Fortini and Pavlola. Hence these suppliers should be given high importance and steps should be taken to improve relationships with these suppliers. Recommendations: The company can strengthen the relationship with the right customers by providing more value and offers. For instance, the top three high value customers identified are Simon Bistro, Drachenblut and Blondel pere et fils. Stronger relationships can be maintained with them by identifying their most ordered products and offering discounts for those items. Currently, there is no effective customer relationship management in place and hence importance has to be given to implementing this value – based customer retention approach. This is important, as acquiring a new customer is six times costlier than retaining an existing one. The less performing categories and products can be analysed in terms of the profit to the firm. This will give a clearer picture about these items and help make the decision whether these products and categories can be discontinued. This will enable the firm to focus on the revenue generating and high demand products as this increase the total value of the firm. A more detailed and clear study can be done if the profit margin for each of the products is also specified in the product database. This will help in analysing the income potential of each product and help make a better decision. It is evident from the analysis of the orders that the discounts were applied solely based on the situation and ordering quantities and there is no business logic involved. The firm can bring in structured discount programs. This will create better marketing opportunities. The business model of the firm is ‘intermediary’ and hence it is very important that the company maintains a strong supplier as well as customer relationship. As discussed, the relationships with the high value suppliers along with suppliers whose products are in demand are to be strengthened and effective communication and logistical facilities should be implemented. On the other hand, the low value suppliers, for instance, Tokyo Traders, whose products are not in great demand and also the revenues, are lesser. It should also be noted that already one of the products of Tokyo traders has been discontinued. Conclusion: The database of the firm was analysed and the matching patterns and key facts are identified. This has shed light on various facts including ways to improve the revenue, marketing and also customer and supplier relationship. Finally, suitable recommendations are made based on the analyses. Thus SQL techniques are utilized to analyse the database to make business level decisions. SQL Statements Consumer Behaviour: The following view is initially created: Create View vw_Orders as select * from orderdetails left outer join orders on orders.orderID=orderdetails.orederID go 1. Monetary Value of Purchases: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=vw_Orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=vw_Orders.CustomerID) as Country, sum([unit price]*quantity) as Amount from vw_Orders group by CustomerID order by Amount desc 2. Frequency of Purchase: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=orders.CustomerID) as Country, count(*) as NOTime from orders group by CustomerID order by NOTime desc 3. Recency: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=orders.CustomerID) as Country, max([order date]) as [order date] from orders group by CustomerID order by [order date] desc 4. Products Purchased: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=vw_orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=vw_orders .CustomerID) as Country, productID, (select [Product Name] from Products where Products.ProductID=vw_orders.ProductID) as [Product Name], sum(Quantity) as Quantity from vw_orders group by productID, CustomerID order by CustomerID, Quantity desc 5. Analysis based on Location: select CustomerID, (select [Company Name] from Customers where Customers.CustomerID=vw_orders.CustomerID) as [Company Name], (select Country from Customers where Customers.CustomerID=vw_orders .CustomerID) as Country from vw_Orders group by CustomerID order by Country 6. Customer and Employee Relations: select customerID, (select [Company Name] from Customers where Customers.CustomerID=Orders.CustomerID) as [Company Name], employeeid, (select [First Name] from employees where employees.employeeid=Orders.employeeID) as [Employee Name], count(*) as NOTime from Orders group by customerID, employeeid order by NOTime desc, customerID 7. Customers and shipping details: select customerID, (select [Company Name] from Customers where Customers.CustomerID=Orders.CustomerID) as [Company Name], [ship via], count(*) as NOTime, (select [Company Name] from shippers where shippers.ShipperID=Orders.[ship via]) as [Ship Via] from Orders group by customerID, [ship via] order by NOTime desc, customerID 8. Discounts and Consumer Behaviour: select productid, (select [product name] from products where products.productid=vw_orders.productid) as [product name], (select customerid from customers where customers.customerid=vw_orders.customerid) as customerid, (select [company name] from customers where customerid=(select customerid from customers where customers.customerid=vw_orders.customerid)) as [customer name], sum(quantity) as quantity , discount from vw_orders where discount0 group by discount, customerid, productid order by discount desc, customerid, quantity desc Products: 1. Most Number of Products Sold: Select [OrderDetails].[ProductID], (select [Products].[Product Name] from [Products] where [Products].[ProductID]=[OrderDetails].[ProductID]) as [Product Name], sum([OrderDetails].[Quantity]) as [Quantity], sum([OrderDetails].[Unit Price]*[OrderDetails].[Quantity]) as [Amount] from [OrderDetails] group by [OrderDetails].[ProductID] order by [Quantity] desc 2. High Revenue Product: Select [OrderDetails].[ProductID], (select [Products].[Product Name] from [Products] where [Products].[ProductID]=[OrderDetails].[ProductID]) as [Product Name], sum([OrderDetails].[Quantity]) as [Quantity], sum([OrderDetails].[Unit Price]*[OrderDetails].[Quantity]) as [Amount] from [OrderDetails] group by [OrderDetails].[ProductID] order by [Amount] desc 3. Category with the most number of Products ordered: The following view is created: create view vw_CategorySum as select category, (select sum(quantity) from orderdetails where orderdetails.productID=products.productID) as Quantity, (select sum([unit price]*quantity) from orderdetails where orderdetails.productID=products.productID) as Amount from products go select category, (select [category name] from Categories where category=Categories.categoryid) as CategoryName, sum(Quantity) as Quantity, sum(Amount) as Amount from vw_CategorySum group by category order by Quantity desc 4. High Revenue Product and Category: select category, (select [category name] from Categories where category=Categories.categoryid) as CategoryName, sum(Quantity) as Quantity, sum(Amount) as Amount from vw_CategorySum group by category order by Amount desc 5. Analysis of Discount: select productid, (select [Products].[Product Name] from [Products] where [Products].[ProductID]=[OrderDetails].[ProductID]) as [Product Name], discount, sum(Quantity) as Quantity from orderdetails group by productid,discount order by discount asc , Quantity desc 6. Analysis of Discontinued Products: select ProductID, (select [Products].[Product Name] from [Products] where [Products].[ProductID]=[OrderDetails].[ProductID]) as [Product Name], Quantity, ([Unit price]*Quantity) as Amount, Discount, [Order Date] from orderdetails left outer join orders on orders.orderid=orderdetails.orederid where exists (select productid from products where discontinued=1 and orderdetails.productid=productid) order by productID, [Order Date] Suppliers: The following view is initially created: create view vw_SupplierSum as select supplier, (select sum(quantity) from orderdetails where orderdetails.productID=products.productID) as Quantity, (select sum([unit price]*quantity) from orderdetails where orderdetails.productID=products.productID) as Amount from products go 1. Suppliers of High Demand Products: --Sort by Quantity select supplier, (select [company name] from Suppliers where supplier=Suppliers.supplierid) as SupplierName, sum(Quantity) as Quantity, sum(Amount) as Amount from vw_SupplierSum group by supplier order by Quantity desc --Sort by Amount select supplier, (select [company name] from Suppliers where supplier=Suppliers.supplierid) as SupplierName, sum(Quantity) as Quantity, sum(Amount) as Amount from vw_SupplierSum group by supplier order by Amount desc 2. Analysis of Discontinued Products: create view vw_DiscProdSupplier as select supplierid, [company name], productid, [product name] from suppliers left outer join products on discontinued=1 and supplier=suppliers.supplierid where productid is not null go Discontinued products list select * from vw_DiscProdSupplier; Active Products of suppliers who discontinued their products: select vw_DiscProdSupplier.supplierid, vw_DiscProdSupplier.[company name], products.productid, products.[product name] from products left outer join vw_DiscProdSupplier on vw_DiscProdSupplier.supplierid=products.supplier where supplierid is not null order by supplierid, products.productid 3. High Value Suppliers: select productid, (select [product name] from products where products.productid=orderdetails.productid) as [product name], (select supplier from products where products.productid=orderdetails.productid) as supplierid, (select [company name] from suppliers where supplierid=(select supplier from products where products.productid=orderdetails.productid)) as [supplier name], sum(quantity) as quantity from orderdetails group by productid order by quantity desc 4. Discounts and Suppliers select productid, (select [product name] from products where products.productid=vw_orders.productid) as [product name], (select supplier from products where products.productid=vw_orders.productid) as supplierid, (select [company name] from suppliers where supplierid=(select supplier from products where products.productid=vw_orders.productid)) as [supplier name], sum(quantity) as quantity , discount from vw_orders where discount0 group by discount, productid order by discount desc, supplierid, quantity desc Read More