Preppin' Data

Challenge by Amalia García-Vellido Santías We have another guest challenge creator with this week's challenge coming from Amalia.  This week we want to analyse the orders that customers have made over a period of time in our restaurant Serendipia. In order to identify how much money we earn each day of the week and also to discover who our top customer is. We are going to be using calculations, pivots and aggregations so lots of the fundamental techniques that are used within data prep! Inputs Menu - contains the menu of the restaurant (notice that the structure is not ideal) 9 fields 10 rows (11 + header) Orders  - each row represents the order a single customer have made at a certain date 3 fields 40 rows (41 + headers) Requirements Input the data Modify the structure of the Menu table so we can have one column for the Type (pizza, pasta, house plate), the name of the plate, ID, and Price ( hint ) Modify the structure of the Orders table to have each item ID in a different r...

Challenge by Vivien Ho (@vivienho22)   This week's challenge has been put together by Vivien to challenge a lot of the fundamental prep skills you have built up so far this year. Vivien's challenge is looking at cocktail pricing (a common trend over the years at Preppin' HQ) and whether you can determine how much profit you can make from certain cocktails because who doesn't talk about data preparation when you are in a bar? Input Cocktails: names, prices and their recipe with measurements Sourcing: ingredient prices, quantity per bottle, currency of price  Conversion rates: currencies and their conversion rates (e.g. 1.14 euros = 1 pound)  Cocktails Sourcing Conversion rates Requirements:  Input the dataset Split out the recipes into the different ingredients and their measurements Calculate the price in pounds, for the required measurement of each ingredient Join the ingredient costs to their relative cocktails Find the total cost of each cocktail  Include...

This is the last in the 'Starter Challenges' series to get you up and Preppin' to start the new year. We've enjoyed running this mini-series so much we're already looking at creating another similar series later in the year.  This week's challenge involves picking up some more of the fundamental skills and gives you some chances to practice some of the skills you've picked up over the last few weeks. As always, we'll be guiding you along the way with some useful help links if you need a couple of reminders or chance to explore those new techniques.  The new technique for you to learn this week is Joins. If you've worked with different data solutions for a number of years, you'll be familiar with Joins but if you are new you're in for a treat! Joins allow us to bring two data sources together. This allows for much easier, richer and deeper analysis as data is often in many different locations. Use the help links if this is a new technique for ...

Chin & Beard Suds Co are relatively new players in the Soap Market and are looking to do a bit of analysis around their competitors. To do this, they'll be looking at a variety of different metrics. Market Share = Company Sales / Total Market Sales Growth  = ( This Month Sales - Last Month Sales ) / Last Month Sales Contribution to Growth  = ( This Month Sales - Last Month Sales ) /  Overall Last Month Sales e.g. if calculating the Contribution to the Market's Growth then the numerator would use each Company's sales whilst the denominator would be the Total Market sales Outperformance  = Company A's Growth - The Growth of the Rest of the Market excluding Company A Input Requirements Input the data . At a total sales level for each company (i.e. not taking Soap Scent into consideration):  Calculate each company's Market Share for April. How many bps* has this changed from March's Market Share? *10 bps = 0.01% Calculate each co...

This week’s challenge is a follow-up from last week ( Week 18 ) as we want to do some further analysis! Last week we found out how many minutes each player had played. Now we want to do some analysis about what positions they were playing in, and how many goals were scored. We are again going to use the lineup data source from last week, then also add some new data sources: 1. Player List. A list of all players at Liverpool, and their preferred position group. The numbers at the start of the string is their squad number, and not the position number from the lineup table. 2. Position List.  This provides us with data about the formations that Liverpool have used, and how these player numbers mapped to positions and position types. Requirements Input the following:  Your Week 18 Workflow Player List  Position List All of these can be downloaded here . We require two outputs this week; they have been broken down here: Output 1 Calculate...

Due to the social distancing and isolation caused by Covid-19, all football games have been postponed across the majority of the world. Therefore to help fill the void, this week’s challenge is all about analysing football team line-ups and in-particular the Premier League games for Liverpool. We start with a nicely formatted spreadsheet that includes the following: Match Details - Including dates, teams, location and formation Match Day Squad - 18 players broken down into Starting XI & Substitutes Starting XI - the 11 players who started the game for Liverpool Substitutes - the 7 players who started as a substitute for Liverpool Substitution Information - Includes the Player On/Off and the Time From this we want to answer some questions about the playing time of each of the players. Requirements Input Data Update the headers for Match Details, Starting XI, Substitutes. Ideally this will be done without just renaming each individual header separately. Note, for su...

This week's challenge is all about Conversion Rates. Any business that either buys or sells in multiple currencies will face the issue of what exchange rate to take. Sadly for Chin & Beard Suds Co. the team have been capturing all the sales of the business in British Pounds as they have totalled all the sales together. Luckily C&BS Co have recorded what % of sales split is between the US and UK. The question our executives have is what is the total of USD revenue each week? Therefore, we have agreed to provide a best case and worst case conversion rate for the US sales each week. Now we just need to work out what that range and variance is... Requirements Exchange Rates (GBP to USD) Sales Value by Week Input the data set Determine the best and worst GBP to USD exchange rates on a calendar week basis Take the Sales data and determine the UK / US Split Apply the UK / US sales split to the value sold in GBP For the calendar week US sales, determine: W...

Chin & Beard Suds Co management team have heard there is unrest in a few of their Northern stores and asked their HR data manager to pull together some supporting files. Sadly for us, those files are going to take some data prep to ensure we can conduct the analysis we want to.  Leeds Store Staff   Sheffield Store Staff Salary Ranges Store Sales The rumours we've heard are: We are paying people more than our Corporate Pay Ranges that we have agreed Bonuses account for very little (less than 10%) of someone's salary We want your help to answer these questions for us. There are some contractual pay conditions you should consider: Bonus will be paid as long as you are still an employee for at least the 1st day of the final month of the quarter. Consider only employees who received salary during 2019 Anyone paid above the salary range will receive no bonus (we pay them enough already) Assume today's date for the analysis is 1st October 20...

Hi all, after last week's marathon challenge that challenged the creators to find the 'right' answer as much it challenge our "Preppers", I decided to go for a slightly more straight forward challenge this week [insert side-eyes emoji] - it's only easy when you can work it out! This week, we are putting ourselves in the shoes of a family firm of stock-brokers. The firm has a number of branches across the country, but thankfully they have learned from previous challenges of Preppin' Data, how to put together their lists of their clients purchases. The regulators want to know whether there are any 'weird' behaviour from their clients. Because their client base is quite small, they have agreed to send a file to the regular where the same share has been purchased by multiple customers of the firm. To give the regulator more evidence, they will share what percentage that sale makes up of the whole client portfolio of the firm and within the region. ...