QVC

var divElement = document.getElementById(‘viz1493909286527’); var vizElement = divElement.getElementsByTagName(‘object’)[0]; vizElement.style.width=’100%’;vizElement.style.height=(divElement.offsetWidth*0.75)+’px’; var scriptElement = document.createElement(‘script’); scriptElement.src = ‘https://public.tableau.com/javascripts/api/viz_v1.js’; vizElement.parentNode.insertBefore(scriptElement, vizElement);

The objective of this challenge was to create a Decision Support System so that QVC can make strategic decisions to answer some of the business questions such as customer next buy (product and product category), top products and categories according to geography and the best time to sell product and brand affinity. To develop a DSS we have been given QVC data to anticipate customer behaviour.

Understanding on QVC data: Given QVC data consists of four data tables which are QVC_Product_Airtime_Master, QVC_Order_Master, QVC_Product_Master, and QVC_Customer_Master. All four tables have been inner joined together via some primary keys: Customer Number and Product ID in order to gain more understanding about other attributes which contributes to customer behaviour.

Examples:

1. QVC_Customer_Master and QVC_Order_Master are joined using Customer Number as a primary key to find out insights such as a total number of orders from a state, Which customer segment has bought maximum products.
2. QVC_Product_Master and QVC_Order_Master are joined using Product ID as a primary key to gain knowledge about Top 10 products and product categories, Best time(hour) of the day to sell a product.

Assumptions and Shortcomings:

• In QVC_Product_Master, for each Product ID, we have a complete set of the product name, brand name and product category. Also, there is no NULL value in the table.

• All Product IDs, Customer Number, and Order Number are integer values.

• Order Time, Product Airtime are in 24 Hours format (HH: MM: SS).

• Product Category and Brand Name values do not have duplicate or similar record values in the database. For example, Disney and Disney Interactive are treated as two different brands not as one brand “Disney”. Also, Earring and Earrings are treated as two different product categories.

View viz here: http://www.reckonanalytics.com/qvc/

It is important to note that, though data is well formed and formatted but it is still limited and inconsistent because the number of orders in each month has been declined or we may have incomplete order details. As Oct 2012 has 31.13% orders whereas February 2013 and March 2013 have 1.94% and 0.03% orders respectively; hence, it is difficult to forecast customer next buying behaviour with high accuracy. Although using ranking function available in Tableau, we have estimated next product customer is likely to buy.

Decision Support System and Instructions to operate:

• Storyboard in Tableau is created that represents our interactive DSS that consists of 10 Dashboards.
• Dashboard 1 and 2 are introductory slides.
• Instruction: Open Team3.twbx using Tableau software and then press F7 key to start presentation mode.
• Dashboard 3: It provides executive data summary of a total number of monthly orders, the number of brands and products in each category and in which category product has been aired the most number of times.
• Instruction – Scroll over the graphs to view details
• Dashboard 4: This tells about what product and category customer has bought in a month. Also, it visualises a number of orders that have been bought when the product airtime was less than 1 minute or greater than 1 minute.
• Instruction – Select customer number from drop-down list and then select month from multiple dropdown lists
• Dashboard 5: It provides details when a customer has bought products on which date and time. Also, the number of times that product has been aired and airdate.
• Instruction – Select customer number from the drop-down list, then click on the product name and hover on order date to view days on which product has been aired.
• Dashboard 6: It helps the user to make a decision about what product under what product category a customer is likely to buy next.
• Instruction – Select customer number range from the slider or range can be manually added by clicking on numbers. Then hover on customer number list to view product name and product category.
• Dashboard 7: This dashboard will answer questions about the top product and product categories according to customer geography, time zone and shopper segment. And, a total number of orders placed in a state has been represented on a world map.
• Instruction – Enter the value of N in Top N product input box and time zone from multiple drop-down lists. Then to view top brands and categories, select any state from the map.
• Dashboard 8: It represents best time(hours) of a day and weekday to sell a particular product and brand.
• Instruction – Select category from dropdown list and then the user can click anywhere on the graphs to view details.
• Dashboard 9: It visualizes brand connection for each customer segment.
• Instruction – Select customer segment from dropdown list or brand name from dropdown list and order range from the slider.
• Dashboard 10: This dashboard helps the user to segregate all brands in 6 clusters using k-Means clustering algorithm.
• Instruction – Select cluster from dropdown list to view products in that cluster.