Realcode4you.com is top rated online service provider related to all types of Data Management System. We are group of PHP researchers and IT professionals that has deep knowledge in our specific areas related to Data Management System.
Here we discuss, Analyze and design one Customer Stock DVD Database using different database models.
This rich picture shows the relationships between the employees of Movies Direct, the customers, the accountant, and the systems they use. It also shows some of the issues that Movies Direct faces, such as competition from Netflix and Piracy, old hardware, and rising DVD and videogame prices. Another issue shown is customers not paying weekly and the money that they owe adding up. It also shows how Facebook is used to advertise and asks whether this is sufficient, and shows how the process of generating round schedules is done.
Creating Context Diagram(Media Rental System)
Round Management System
Finance System
Analysis Using CATWOE
The above Power/Interest Grid shows where each stakeholder would fit into these parameters. Antony Mighall, being the owner of the company has high power and high interest, seeing as the company is his and he does the decision-making business wise. Sharon Mighall still has a high interest but has less power as she is more of a silent partner business-wise but does assist in operations. The delivery driver also has a lot of interest as the success of the business is how they are paid but doesn’t make key business decisions themselves. The accountant is interested and has some power seeing as how they make sure the financials are in shape and can make decisions based on this. The interest is only medium however as they have other clients they must split focus between. The rental customer has some interest as they use the service, but a single customer doesn’t have any power on how it operates
Media Rental System(Use Case Diagram)
Use Case Description
Use Case Diagram( Finance System)
Description
Use Case Diagram(Round Management System)
Description
ER Diagram
ELH Diagram
Driver
Round
RoundSheet
Customer
Order
Media
Class Diagram
Sequence Diagram
The main point that can be gleamed from the various business analysis methods is that while Movies Direct is a well oiled machine with simple, streamlined systems, probably due to the fact that it has been operating for so long, there is still room for improvement. The round and roundsheet system could be updated to be more modern as it is the same as when the organisation began. In fact the software used is so old and obscure that no one really knows how it functions under the hood, and if it were to break or the computer it is installed on were to break it would be almost certainly lost. The only real modernisations to how Movies Direct operates is the use of social media, and the option for customers to use a card reader to pay. The worries of the old and slow hardware are evident from the Rich Picture, which was generated after talking to and observing the Movies Direct staff. If the entire round management system was automated, including creating a website that could be used for customers to make orders instead of having to do so over the phone, and even have the option to pay, many use cases would be removed entirely due to streamlining. The driver could also use technology as a virtual roundsheet, which could update the database automatically as new orders are made in person, stock is taken, and money is paid. This would both save time and decrease the user error generated from repetitive data entry, which Panko (2008) estimates as about 0.5% for simple tasks like this. That doesn’t sound like a lot, but from the thousands of inputs that have to be done every week to maintain the database manually it adds up.
An automated system would decrease this dramatically as realising the wrong DVD name was selected is easier than realising an incorrect 6 letter ID is incorrect. An automatic system would also reduce the number of inputs being made dramatically. Another key issue is new customer generation. From the rich picture it can be seen that the company worries about this and that Facebook and word of mouth may not be enough to grow effectively. In the past Movies Direct had use a system they call canvasing which is basically promoting the service door to door, in the same vein as the more common political canvassing. This is not currently being done however as can be seen by it not being in any of the diagrams. The Resource Audit says that the organisation has members with strong cold sales skills, and this should be taken advantage of with aggressive canvasing being done. About 2% of doors knocked on leads to a sale (Gerencer, 2019) which may sound bad, but in Movies Directs case one membership can lead to a lot of money over time. Another alternative is radio advertisement but this is more expensive and the company has done it in the past to little success. 9. Evaluation (500) I believe that the techniques used for this business analysis were generally well suited and effective. The free to use draw.io was used to create the diagrams as opposed to the costly Visio, and despite being free draw.io doesn’t seem inferior, with a comparison on stackshare.io showing that it is used in slightly more company stacks than Visio (drawio vs Microsoft Visio, 2019), although the sample size is small. Draw.io was effective in this analysis and fulfilled all the requirements made of it. Porters Five Forces was also a good fit for analysis Movies Direct as it allowed an in-depth exploration of the overall standing of the company in the marketplace, as well as an easy to understand grid that quickly shows the intensity of each force. The intensities average around medium, with some high and some low, indicating a relative moderate amount of profit (Porter, 2008), which is fairly accurate for Movies Direct, although it could be argued that some of the forces are more impactful than others in this case, Threat of Outside Forces for example. The Resource Audit was also useful, leading to direct reference in the observations section and generally helping build understanding of the business. Despite the Rich Picture being the most freeform of the techniques, because of its construction generally being reliant on interviewing people in the company (Monk & Howard, 1998) it provided some strong insights and was revealing of some of the key problems that Movies Direct faces. This is shown in the observations where the Rich Picture is mentioned a few times. The context diagrams were helpful as a stepping stone for creating the use case diagrams but ultimately didn’t provide much value to the analysis individually. The CATWOE and Power Interest Grid is effective in understanding stakeholder thoughts and intentions, but for this specific company there may not be enough stakeholders with vastly different interests to make full use of these tools. The Use Case Diagrams, Descriptions, and Structured English are successful in modelling examples of system behaviours, and do provide an overview of what the processes are. However in the case of Movies Direct, a very small, family run business, the actual way things are done is a lot looser and more free form than the diagrams can show. This is because a business of this size has to be adaptable and ‘roll with the punches’ so to speak to survive and can’t strictly rely on a formula on how to conduct business.
Structured English though was the best tool, ahead of Decision Table’s and Trees, to perform the analysis as it can handle many loops and IF statements effectively (TutorialsPoint, n.d.). The Use Case Diagrams also did point to some Use Cases that could be streamlined in the observations section. The ERM and ELH diagrams provided good insight, but the Class and Sequence diagrams once again were too rigid to fully capture how Movies Direct would operate on a day to day basis. Overall the Socio-technical approach was the most effective in generating issues and solutions, as opposed to Functional and Object Oriented, although in a larger company with more defined and rigorous systems this may be different.
PART 2 - Market Basket Dataset Analysis
1. Introduction
The aim of this section is to conduct a Market Basket Analysis (MBA) of transactional data, first from a relatively small ‘groceries’ dataset, then a larger, real dataset from an online retailer, before finally a very large dataset from Instacart. The goal of an MBA is to use association rules, in this case discerned from the Apriori algorithm, to gain business insight from customer purchases by grouping frequently bought together items in order to find relationships between them. In simple terms, the goal is to better understand customer buying habits. This can then be used to create effective recommendation systems that lead to increased basket ratio (Cakir & Aras, 2012) and therefore increased profits. The algorithms and implementation of these methods are quite simple in themselves, the difficulty is actually finding useful relationships in the huge amounts of data, and being able to handle datasets of the necessary size at all (Albion Research, 2019). In the following analysis the Apriori algorithm will create frequent item sets based off the transactional data, each of which will have attributes such as support, the frequency of the item set relationship in the dataset overall, and confidence, the estimated conditional probability that the frequent item set is accurate. Another measurement to be taken into account is the Lift, which summarises the link between products on the left and right hand side of an associate rule, with a measurement over 1 suggested that a customer buying item A & B increases the probability they will buy item C (McColl, n.d.), with items A,B, and C making up the frequent item set. These three parameters will be experimented with in the code in order to find a combination that provides the most valuable output.
2. Application
The analysis of the first dataset is very limited in both scope and functionality, only looking at 5 baskets and only using the Support attribute as the dataset is only transformed into frequent pairings, not association rules. Four pairs of items and one trio have a support of 0.6, but only one is higher, the pair of Eggs and Kidney beans at a confidence of 0.8. From this you can deduce that 4 out of 5 baskets had the pair in them as Kidney Beans on their own have a support of 1.0, meaning they are included in every basket. In theory a business could look at this and decide that moving the premium Kidney Beans closer to the Eggs in the shop or vice versa is a good business decision, but the dataset is so small this result doesn’t mean much statistically.
The next dataset is much larger and from a real online retailer. The parameters are also more complex, including support, confidence, lift, leverage, and conviction as this dataset has association rules generated from it. It is also filterable by the country the baskets came from. The parameters for the final dataframe are set as a minimum confidence of 0.07, which insures the relationship has enough data to substantiate it, a large lift of at least 6, which means the relationship between the antecedents and the consequents is positive and strong, and a confidence of 0.8, also high. The country in this case is set to France. From these parameters 8 relationships are displayed. The relationship with the highest confidence is between a set/6 of red spotty paper plates and a set/20 of red retrospot paper napkins as the antecedents, and a set of 6 red spotty paper cups as the consequent. 6/8 of the relationships are combinations of these items, with the other two being a pair of different coloured alarm clocks each way. Unfortunately it is common knowledge that paper plates, paper cups, and napkins are bought together, making these rules trivial and not useful. Changing the location from France to UK also only gives trivial results even if the minimum confidence is lowered to allow for more leniency. The strongest association in the UK baskets are various coloured Teacup and Saucer sets being bought together, sometimes in pairs of colours and sometimes in trios. Despite that in a vacuum these associations are common sense, the business could take advantage of the knowledge that people commonly buy two tea sets, but sometimes buy three, by making offers geared towards the purchase of a third set. This extra incentive may push the customers who would buy two sets over the edge, swaying them to buy the third as well. This is just one example of how even trivial looking associations can be used to increase sales.
The final dataset is the largest allowing for a greater number of rules to be generated, which should lead to more actionable rules that generate new insight in purchasing habits. The majority of the rules are as you would expect, different flavours of yoghurt being paired or various kinds of baby food, but these can still be useful to know. A common association is various types of peach and strawberry yoghurts, be that unsweetened, almond milk, or organic, but the rules tend to stick to an individual variety. This knowledge could be used to make sure the varieties are placed together, or that deals are made that include them together in order to maximise sales.
One actionable rule found is the association between various protein bars and different flavoured chocolate bars, a pairing you wouldn’t necessarily expect. An action that could be taken to taken advantage of this is moving protein bars from the ‘fitness food’ section (if that is where they are located) to the sweets/chocolate bar section of the shop so customers can more easily buy both. This may even persuade customer who would normally just buy a chocolate bar to try a protein bar as well. All of these rules can be applied in an online store situation, simply by moving items to nearby sections of the website, or adding them to the recommended purchases.
Another area that wasn’t included in the datasets analysed is the ability through loyalty cards for personalised association rules to be created, or rules that transcend single baskets. If for example an association is found for people buying bread and milk on Mondays and then beer and crisps on Fridays, deals and item placements can be made accordingly, even changing throughout the week as rules change for different days. At an individual level targeted offers could be made at people if they are found to buy things on a schedule.
3. Conclusion
A Market Basket Analysis can be a very useful way to gauge how customers are engaging with a store by looking at which items are frequently bought together. Large stores with many items can quickly accumulate huge amounts of purchase data, the more of which the better when it comes to generating associations. As seen by the recommendations given in the application section, the results of an MBA can be used to influence many factors from the layout of a store, deals that are offered, the placement of items and more.
Overall, even if it takes some looking to find actionable rules amongst the trivial and inexplicable, the business advantage that can be gained from customer purchase data is very valuable and worth the effort to gather and analyse.
References
Adams, C. (n.d.). Interview Questions for Business Analysts and Systems Analysts. Retrieved from Modern Analyst: https://www.modernanalyst.com/Careers/InterviewQuestions/tabid/128/ID/1433/What-is-a-Context-Diagram-and-what-are-the-benefits-of-creating-one.aspx
Albion Research. (2019). Market Basket Analysis. Retrieved from Albion Research: https://www.albionresearch.com/data_mining/market_basket.php
Arlow, J., & Neustadt, I. (2005). UML 2.0 and the Unified Process: Practical Object-Oriented Analysis and Design. London: Addison-Wesley.
Bergvall-Kareborn, B., Mirijamdotter, A., & Basden, A. (2003). Reflections on CATWOE, a Soft Systems Methodology Technique for Systems Design. CPTS Working Conference.
Cakir, O., & Aras, M. E. (2012). A Recommendation Engine by Using Association Rules. Procedia - Social and Behavioral Sciences, 452-456.
Central Computer & Telecommunications Agency. (2000). SSADM Foundation. The Stationary Office.
drawio vs Microsoft Visio. (2019). Retrieved from stackshare:
https://stackshare.io/stackups/drawio-vs-microsoft-visio
Gach, E. (2013, November 7). Video Rental Might Survive Streaming Even If Blockbuster Didn't. Forbes Magazine.
To get more about Realcode4you.com advance database management team you can contact us or send your requirement details at:
realcode4you@gmail.com