Preppin' Data

At Chin & Beard Suds Co, we have come across another data challenge where customers who buy multiple products have their products held in a slightly complex way. Each product is listed in it's own column so two products sold leads to two separate product columns. You challenge is a fundamental one this week, to create a more logical way to hold this data. We've thrown in an address clean up challenge for you too just to spice things up a little! US Zipcodes are a lot easier to understand than UK Postal Codes so enjoy the oddity if you are not used to them! Requirements Input data Provide the address columns specified in the output Put all the products in one column, same for sales Clean the product names to remove punctuation Output 7 Columns Property Number Town Postal Code Country Product Sales 12 Rows (13 including Headers) The output can be  found here  for comparison. Don't to forget to fill in our  participation tracker ...

You can view our full solution workflow below and download it here! Our final solution workflow. 1. Split & Pivot the [Packages] from the customer data. Some of the customers are subscribed to multiple packages, which is logged as “1|2|3” for example. We firstly need to split this up using a custom split on every ‘|’. We then need to pivot all the new [Package – Split X] fields into a single field using a Column-to-Rows pivot. This single field contains many blank [Package Split] values, as not every customer had multiple subscriptions, so we need to remove all these. This can be done by right-clicking on the blank value in the profile pane and clicking ‘Exclude’. Excluding the empty values after pivoting. 2. Join the subscription pricing and frequency info. From here we can bring in all the extra data. Bring in the pricing data by using an inner join on [Package] (customers) = [Subscription Package] (pricing) and then the package data using an inner join on [F...

You can view our full solution workflow below and  download it here ! Our full solution workflow. I’ve seen a number of solutions shared with us via Twitter, some more streamlined than this so for alternate ways of achieving this outcome just search Twitter for the  #PreppinData hashtag ! In particular, the  solution by @ArseneXie  streamlines the pivoting steps and the self-join/aggregation step. For this write-up we’ll discuss our original solution as a base point. 1. Use Data Interpreter to fix up field names. As our input data is poorly formatted, by ticking ‘Use Data Interpreter’ we can get our first and second row headers merged together for something meaningful instead of ‘F6’, ‘F7’, ‘F8’, and so on. 2. Use a ‘Wildcard Union’ to pull the date from the sheet name. Part of the challenge includes getting an accurately formatted date & time field. However, the data itself is contained only in the sheet name. To get around this we can change ou...

When I think about preparing data, I instantly think about battling fields that contain string data. Customer names, product descriptions, messages and geographic names are all types of data that you will likely need to manipulate when you prepare your data for analysis. What do we mean by strings? When most people think of data, they think about the measures we are summing, averaging or simply finding the largest value. But those measures don't tell us much until we start analysing things at different levels, ie: Average Sales by Region or Total Sales per Company. Strings are often those dimensions that we are cutting the data by in tools like Tableau. Strings are shown in Tableau with the 'Abc' icon. String fields in Tableau are shown by the 'abc' icon String data fields are the most flexible in terms of the values you can hold within them. Typically, everything from A-z and 0-9 as well as punctuation and most others characters can all be found wit...

Chin & Beard Suds Co has a problem in the North of England. A number of customers are not following the normal buying behaviour. We would normally expect a customer to: Place an Order Have the Order sent to them Review the products online But this hasn't been happening. We have convinced our sales team (sorry for some poor data input if you are using certain tools) to send us their first 10 days sales of 2019 to help us spot the trend. We need you and your data preparation skills to help us understand why. Jonathan and I would like to understand: Average Time for a customer to have their order sent after placing their order Average Time for a customer to review their products after we sent their order (for those that have reviewed) In how many cities have customers not had their order sent after placing an order For orders sent, which % of each cities orders have not been sent out within 3 days or less Requirements Input data Create four outputs, one ...

Data is everywhere and when your colleague finds a fun dataset then it instantly needs to be cleaned. This is what happened when Andy Kriebel found the Serpentine Swimming Club tweets that record the temperature and a fun comment about what happened that day. So what words get used more as the temperatures increase? How about those 'nippy' 10oC days? Well I've produced a simple Tableau Public view to let you analyse the output and check your results. Requirements Serpentine Swim Club tweets Common Words file (same as PD week 6) Input the Serpentine Tweets Only keep tweets that give water / air temperatues Extract Water and Air Temperatures as separate columns Remove Common English words by linking the 2nd Input (Common English words) Remove unrequired fields and remove punctuation from your words from the tweets Output csv or file type of your choice if you want to build the view Output 7 Columns (Comment Split, Category, TempF, ...

At Chin & Beard Suds Co we are developing a growing base of customers ordering on a regular basis, as a result we have decided to release a subscription option. Customers are now able to choose a package of soap(s) they wish to be sent from our range and the frequency they would like to receive their basket of soapiness. We have a total of 7 different types of products available to choose from and customers can select any number of, or variation. A current list of subscriptions made can be found in our customers table. Reference tables for product aliases and frequency aliases are available in our subscription products table and subscription packages table. One option is our mystery package…this could be any of the other 6 package types. To make our customers happy, we have decided to mimic our current customers’ choice of package type. Therefore, if more people like the Active, it should be more likely that we would send this out as the Mystery subscription type. So how much sh...