Move from Excel to Python with Pandas Transcripts
Chapter: Case study walk through: Sales commissions
Lecture: Product commission scenarios

Learn more about this course
Login or purchase this course to watch this video and the rest of the course contents.
0:00 So now let's look at the file,
0:02 and what we want to do now is create one day to frame so we can
0:05 do all of our analysis. So we need to combine the customers and the envoy
0:09 stated together, so we'll call this sales detail.
0:17 So the invoice in the customer level data phrase both had customer numbers,
0:22 but they called it to you for things custom and accounting.
0:25 Um, but due to our business knowledge,
0:27 we know they're the same thing.
0:29 So we merge those together, and now we have a single file that has all
0:32 the customer information, the state in the region,
0:35 information as well as a sales rep and invoice level detail.
0:39 And the reason we did this is now when we do our group buy,
0:42 everything is on one place, and we can easily iterated through our analysis.
0:46 So we have 2000 rows, which is what we would expect.
0:51 There are some remains the same.
0:53 So the next step we need to bring in our commissions.
0:58 Now we set our commission rate and let's add the commission amount to our sales details
1:04 Now we have commissioned for each transaction.
1:10 Now we have all the low level details we need so we can start aggregating grouping
1:16 and pivoting to understand it. More detail.
1:18 Let's take a look at the skew level commissions.
1:23 So this works correctly, But I'm gonna use sorting to make it a little bit
1:27 easier to view. So you sort values.
1:32 Now we can see that s w 500 nest up you 200 s w 1 21
1:38 have a lot more commission than some of the other products.
1:41 You can see the A C C products have very little commission.
1:45 Maybe we want to look at now what the sales reps are earning.
1:51 So in this example is that type today and last name didn't come through.
1:56 So if I look and made a mistake when I typed in my use call So
2:02 I need to put J in there.
2:05 No, it's rerun everything. And now we have her last name.
2:10 So this shows us which salespeople are earning and we definitely have a difference between Sutton
2:17 and Tiner. We're earning three and $400,000 whereas Malcolm in munis are only about 100,000
2:23 Let's bring in a channel.
2:25 See what that looks like. This is a little hard to read,
2:29 so let's turn it into a pivot table.
2:40 This starts to give us some of interesting information.
2:43 Tiner has partner commissions, whereas no one else does.
2:48 And if we look at Sutton has a lot of commissions for resellers ous well as
2:55 tighter. And Tiner also has a lot of retail commissions.
3:00 So there may be something we need to look at here from the distribution,
3:03 understand how toe equal isis a little bit more.