Practice Use Case In this Chapter you will practice applying what you learned in the previous lessons in the context of a use case working with customer data. Before you begin working on this lesson, read the following background information about the Customers dataset and use case. Once you have completed the use case, you can find the steps we took and the results produced with in the following pages. This example is meant to test what you have learned thus far. When you feel comfortable with this use case example, we recommend you challenge your wrangling skills by taking our Certification Exam. Before getting started, download the following datasets: • Use Case Data
Example
You work for a technical services company that sells three monthly subscription products: • • •
Silver (price: $9.99/month) Gold (price: $14.99/month) Platinum (price: $29.99/month)
The company occasionally offers promotional discounts – therefore, some product prices may be slightly lower than those listed above. Your overall goal is to provide an analysis of sales activity by zip code. In order to do this, you’ll need to join your Lab_customers dataset (where zip code information resides) with sales data from your transactions datasets. Once you’ve joined the data, you’ll need to aggregate the results.
Create a new flow
Add Following Datasets: • • • • • • • •
lab_customers.csv lab_2010_transactions.csv lab_2011_transactions.csv lab_2012_transactions.csv lab_2013_transactions.csv lab_2014_transactions.csv lab_2015_transactions.csv zip_to_state_map.csv
Add recipe to Lab_Customers
Add a Recipe to the Lab_Customers dataset. Edit the Recipe to enter the Transformer Grid
Filter out faulty data
Remove the contacts who are not actual customers: We identify actual customers by the start_date column.
Provide a default end_date value for active customers
For those current customers without an end date, set the default end date to January 01, 2050.
Fix the start_date data type mismatches
Notice that the start_date column contains in two different formats. You want all of the dates in this column to conform to the format ‘yyyy/MM/dd’.
Lab_Transactions
Add a Recipe to the Lab_2010_transactions dataset. Edit that Recipe to enter the grid
Remove dummy data
Since this company sells online subscription products, the website sends a test transaction of $0.01 to validate that a customer’s credit card is valid. Test transactions are recorded in the database and appear in the transactions file. You need to remove these transactions so they don’t distort your analysis.
Clean up Discount Column
Remove the % from the Discount column, then convert the column to a decimal. Fill in the missing values with 0.
Standardize product names
After 2011, the company renamed the subscription products. To maintain consistency between the transactions files from each year, you need to replace the old product names with the new product names. • •
Change ‘basic’ to ‘silver’ Change ‘deluxe’ to ‘gold’
Determine the real price for each product
The real price for each product is a combination of the list price and the discount. Create a new column that contains the actual charge for each transaction.
Union transactions datasets
Union 2010 – 2015 datasets. We want to apply the above transforms to all data, make sure your recipe step is added to the beginning of your recipe, so that all the steps in your recipe are applied to all of the unioned datasets. • • • •
2011_Transactions 2012_Transactions 2013_Transactions 2014_Transactions
•
2015_Transactions
Join with the Customers dataset
Join transaction data with the customer data, keep the following columns. • • • • • • • • •
Customer_id (2010_transactions) transaction_date (2010 Transactions) adjusted_price (2010 Transactions) product (2010 Transactions) address_state (Customers) address_zip (Customers) region (Customers) start_date (Customers) end_date (Customers)
Lookup state by zip
Scroll to the address_state column and notice that there are some blank records. Perform a lookup on the address_zip column to zip_to_state_map.csv. This Lookup file contains a state mapping for each zip code. Rename column3 to State and delete Address_state
Filter transactions based on date
Only keep transactions that have occurred during the course of a customer’s active subscription (i.e. the transaction_date is before the end_date
Create a new table with aggregated results
Create a table that lists the count, sum, min and max of adjusted price, grouped by address zip.
Run the job at scale
Run job and view job results.
Solution Solution Guide