2024: Week 8 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Prep Air Loyalty Scheme
The first task of this week's challenge is to filter to keep the customers who are part of the loyalty scheme. One of the conditions is that you have to have flown since the 21st Feb 2023 so we need to filter the Customers table to show only customers with a flight after this date:
[Last Date Flown]>=date('2023-02-21')
Next we want to categorise the customers into different tiers based on the number of flights they have taken. For this we want to be able to control the bucket size by using a parameter so that the user can select either of the options.
The parameter needs to be a whole number which has a list of values where the user can select 5 or 10:
We can then use this parameter to create the tiers that each customer falls into:
Tier
FLOOR([Number of Flights]/[Parameters.Tier Bin Size])
Then finally we can calculate how many flights a customer takes a year, on average, by first calculating the number of years they have been a customer:
Number of Years as a Customer
DATEDIFF('year',[First Flight],[Last Date Flown])+1
Then dividing the number of flights by the number of years:
Avg Flights per Year
[Number of Flights]/[Number of Years as a Customer]
After removing some fields our table should look like this:
Step 2 - Join Loyalty Table
Now we can add in the Loyalty table by joining it to our existing Customers table. In order to join these tables together we first need to clean the Tier field to remove all the Letters and then make it a whole number. This will allow us to use this field to join to our Customers data source.
Before joining though we need to ensure we are only showing the tiers from the selected grouping in the parameter. For this we can use a filter where [Tier Grouping]=[Parameters.Tier Bin Size]
We are now ready to join to our Customers table using an inner join where the Tier from the Customers table is >= Tier from the Loyalty table. This will include all of the tiers that customers are eligible for and not just their own tier.
At this stage our table looks like this
Step 3 - Split Benefits
Next we want to ensure that we are only looking at the individual benefits that customers are receiving.
First we can remove any Null values from the benefits field as this will remove any tiers that don't receive a benefit.
We can then split out the individual benefits within each tier. These are separated by a ',' so we can use a custom split for this:
We can then remove the original Benefits field and then pivot the split values so they are in a single column using a columns to rows pivot:
After the pivot we want to remove any blank values from the Benefits Split field and remove the Pivot1 Names field as well so our table looks like this:
Step 4 - Combine Costings
We can now combine our final input table which provides the information about the costings. First we need to clean the Costings table so that we can join with our existing workflow.
Within the Costings table we can remove the '£' from the Cost field:
Cost
REPLACE([Cost],'£','')
Then we want to split out the value and whether it's a yearly or per flight cost, so we can duplicate the Cost field then remove the letters and make it a number field.
Within the Cost-1 field we can remove the numbers then rename the field to Frequency.
We can now join the tables together using the Benefit field from both side and an inner join:
Now both tables are combined, we can calculate the Yearly Cost of the benefits:
Yearly Cost
IF [Frequency]='per flight'
THEN [Cost]*[Avg Flights per Year]
ELSE [Cost]
END
Then calculate the total cost for each tier of passengers by using an aggregation to group by Tier and sum the Yearly Cost and countd the customer ID.
The last task is to order the table by using a Rank calculation. We want to rank the tiers so that they are in numeric order across the whole table.
We can then Hide the rank field so that the table appears to be ordered but the Rank calculation won't appear.
The final output should look like this:
5 Tier Bin Size:
10 Tier Bin Size:
After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
