topic Re: Challenge #8: Aggregate Consumer Purchases in Weekly Challenges

Challenge #8: Aggregate Consumer Purchases

GeneR — Mon, 04 Sep 2023 20:19:18 GMT

Aggregate Consumer Purchases:

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. The incoming data is larger than usual for these exercises so I have packaged the workflow as an Alteryx Package. The link to the solution for last challenge #7 is HERE.

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.

Hint:

Don't forget to join to the lookup table and filter by date.

As always we look forward to your feedback and suggestions!

UPDATE 01/18/2016:

The solution has been uploaded.

UPDATE 12/28/2016:

The challenge, text and solution have been updated.

Re: Alteryx Weekly Exercise #08 Aggregate Consumer Purchases

TaraM — Mon, 18 Jan 2016 15:06:44 GMT

Thanks for playing along. The solution has been uploaded to the article.

Re: Alteryx Weekly Exercise #08 Aggregate Consumer Purchases

wiese-til — Thu, 19 May 2016 13:54:34 GMT

Thanks for sharing the solution, but in this case I'm wondering if it actually fits with the instructions of the exercise. Those ask for orders that have a burger or pizza and a salad. The way I understand the solution however identifies those orders that have a burger or pizza and a side. Am I missing something?

Re: Alteryx Weekly Exercise #08 Aggregate Consumer Purchases

GeneR — Thu, 19 May 2016 15:05:30 GMT

@wiese-til,

You are corect, the workfolw does not match the text. The Objective should read:

We are thinking of including a new "Meal Deal" on our menu and want to study the potential impact on recent transactions. Identify the number and percentage of tickets since June 2013 which include the following categories of food: Pizza OR Burger along with a Side and Drink.

Great catch, thanks for letting us know!

Re: Challenge #8: Aggregate Consumer Purchases

TomWelgemoed — Wed, 28 Dec 2016 14:46:58 GMT

Hi,

Maybe a subtle language point, but I think the "since June 2013" text is misleading - I see the solution filters from July onwards. I took that to mean from the start of June.

Either way, another good exercise, so thanks for that.

Regards,
Tom

Re: Challenge #8: Aggregate Consumer Purchases

TomWelgemoed — Wed, 28 Dec 2016 14:53:03 GMT

I've added a slightly different approach for anyone who is interested - if I may say so I think it's slightly cleaner. You don't need the parsing tools in this workflow, but I think in a real-world scenario your data is unlikely to be as clean as the example, so would then be useful.

Re: Challenge #8: Aggregate Consumer Purchases

kconner — Tue, 17 Jan 2017 20:11:49 GMT

I perhaps made a long-form solution without the use of a flag table. Basically I broke the tables apart with filters and used the Unions to help remove the Tickets without the food types we needed. A basic count of all tickets vs the tickets that remained after the unions made the formula possible.

sol. #8

Re: Challenge #8: Aggregate Consumer Purchases

mceleavey — Wed, 08 Feb 2017 10:47:40 GMT

I think my approach is similar to others:

I first used the "DateTime" tool to convert the date string, which I then filtered using the "Date Filter" tool to those records "since" June 2013 (I agree with a previous poster that this is ambiguous):

I then applied a filter to remove the irrelevant food types.
Now the two streams contained the relevant records, I joined them on the description.
I then applied a "Title" field, which was just text for use in pivoting the data. I sorted by ticket ID and used the "Crosstab" tool with the text field as the column title. This put all food types on the order into a single cell per ticket. I then filtered out the records that included the appropriate combinations, and created a count of the Ticket ID field:

I also created a count of tickets on the primary stream. This gave me the total number of tickets since June 2013.

I then simply joined the streams and used a formula to calculate the percentage:

Re: Challenge #8: Aggregate Consumer Purchases

SeanAdams — Sun, 05 Mar 2017 16:44:46 GMT

Very similar solution to @TomWelgemoed

- Clean up the dates
- Join to the lookup data
- Summarize by food type
- Transpose to get this into columns (one ticket ID with a count of each food type)
- Clean up nulls
- Simple formula that evaluates to 1 if it hits the deal criteria; and 0 otherwise - DealCount
- Summarize to count the tickets, and get an overall Sum of the DealCount column
- Simple formula for percentage

Re: Challenge #8: Aggregate Consumer Purchases

MarqueeCrew — Mon, 06 Mar 2017 14:17:11 GMT

@JoeM,

Lots of edits.

Re: Challenge #8: Aggregate Consumer Purchases

KatieA — Wed, 08 Mar 2017 05:24:49 GMT

Solution attached.

Re: Challenge #8: Aggregate Consumer Purchases

ulrich_schumann — Sat, 18 Mar 2017 13:08:25 GMT

For data joining I like to use the "FindReplace" tool where you can use "append" instead of "replace" to match 2 data sources.

Re: Challenge #8: Aggregate Consumer Purchases

ulrich_schumann — Sat, 18 Mar 2017 13:36:34 GMT

I recognized that the Inputdata is missing in my uploaded file.

2 questions here:

- how can I change/update an existing post from myself?

- I tried to attach into a new post the .yxzb file but got the error message: wrong file Format. How can I upload this format?

Re: Challenge #8: Aggregate Consumer Purchases

NicoleJohnson — Sat, 01 Apr 2017 21:50:07 GMT

Solution, nothing earth-shatteringly different from the others on this one. :)

Re: Challenge #8: Aggregate Consumer Purchases

Laurap1228 — Sun, 16 Apr 2017 14:22:23 GMT

Here is my solution to challenge #8.

Re: Challenge #8: Aggregate Consumer Purchases

Anurag — Wed, 26 Apr 2017 04:53:55 GMT

Its a simple but interesting problem because doing it in the alteryx framework , makes it interesting.

Re: Challenge #8: Aggregate Consumer Purchases

estherb47 — Wed, 26 Apr 2017 21:02:06 GMT

my numbers are slightly different, but I'm guessing that's because I used a date filter after May 31.

Re: Challenge #8: Aggregate Consumer Purchases

JustinMatthews — Fri, 05 May 2017 16:50:11 GMT

I seem to have approached it slightly different than others.

Re: Challenge #8: Aggregate Consumer Purchases

DE0413 — Fri, 05 May 2017 20:28:47 GMT

May not have been the cleanest approach, but here's my solution

Re: Challenge #8: Aggregate Consumer Purchases

mceleavey — Fri, 05 May 2017 20:22:41 GMT

A word of caution when using Append, this generally the slowest method as you creating a Cartesian join, which requires computational grunt. Sometimes it can't be avoided, and it's very easy to write, but if you are spending many to many it may not be the best method.