Here’s the problem statement for challenge #8 –
For this week’s exercise we will look at customer purchase behavior to decide if we should offer a “Meal Deal” that would add a side and drink to a purchase of pizza or a burger.
This week’s Objective:
In order to decide if we should start including a new “Meal Deal” on our menu we want to study the potential impact on recent transactions. Please identify the number and percentage of orders since July 1, 2013 which include the following categories of food: Pizza OR Burger along with a Side and Drink.
Summary of Data:
Point of Sale data includes the ticket level information, and the lookup table categorizes items into higher-level food categories.
The input consists of 1 table and 1 lookup table respectively, as shown below-
The output is as follows –
Here, if you try to decipher the objective word by word, It breaks down to this –
- Number and Percentage of orders
- Orders since July 1, 2013
- Pizza or Burger AND Side AND Drink
First order of action – it’s efficient to form an overview of the sequence of steps in our mind. One of the three points (objectives) mentioned above – ‘date’ can be sourced from the table while the other – pizza/side/drink/burger information can be sourced from the lookup table. We could create a column that flags our items of interest followed by joining it with the table containing the date. This could be followed by filtering the data based on the date and then counting the number and computing the % of potential mean deal. Lets begin implementing these steps one by one.
First we begin with the formula tool –
We use this tool to create a flag to identify items of interest. We create two columns namely MealDealItem and Flag. The conditional loop that is used to organize them into – Food, Side and Drink, is a pretty standard issue. We follow that up with setting the flag to 1 if the item is Food or Drink. The output looks as follows –
Next we use the join tool to join the output from the formula tool with the Point of Sale input.
We join the two tables on ‘Desc’ and drop the columns that we don’t need/ to avoid redundancy. The output looks like this –
Here, note that TicketID is a unique identifier for every entry in this table and could be used for computations later on in the workflow. Next we filter the data by the entries only since July 1, 2013 using the filter tool –
It’s important to note the datatypes of the column we operate on. Here, the column for ‘Date’ is of type V_String. We cannot perform boolean operations on data of type string. This we use the inbuilt function DateTimeParse(). It can be used by directly typing into the custom filter area or alternatively can be found in ‘fx’ > DateTime > DateTimeParse. The output for the above filter is obtained from the ‘T’ anchor of the filter tool.
At this point we have all the data we need however we need to transform the table such that we get a count of the items of interest listed under the ‘MealDealItem’ column. For this purpose we use the Cross Tab tool –
We group the entries by a unique identifier for each row i.e. TicketID. The column header for the transformed table have to be the distinct entries in the column MealDealType while the values inside the row of this transformed table have to be the values contained under the Flag column. The cross-tabbed table looks as follows –
Thus far we have completed one out of the three (filter by date) objectives outlined above. Now we need to count the number of drink and food and side entries we have. We could leverage the flag values for this purpose using the summarize tool –
To ensure we have all the three values existing simultaneously we write the above expression. We use the ‘T’ anchor of the filter tool to source the output. The ‘T’ anchor will give us rows with only those TicketIDs of orders that have all the 3 MealDealItems.
We now need to count the number of entries that satisfy the filter expression as shown above using the summarize tool.
The output contains the count –
We also need the total number of orders since July 1, 2013. We just need to back up a bit to the point where we created the cross tab of the table. If we count the entries before we filtered the data we will have the total number of entries. We will do that using the summarize tool –
Output is the total count –
We join these two counts using join tool –
At this point we have the Total and the Count and calculating the % from here is a piece of cake, we do that using the formula tool –
This concludes our Weekly Challenge #8 work-flow. The complete work-flow is as follows –
Again, there are more than one ways to approach this challenge. Hit me up with a simpler and more intuitive way of doing this. And like always I welcome suggestions and ways in which i can improve my content and walk-throughs!