Move from Excel to Python with Pandas Transcripts
Chapter: Case study walk through: Sales commissions
Lecture: Product commission scenarios
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, and what we want to do now is create one day to frame so we can
0:06
do all of our analysis. So we need to combine the customers and the envoy stated together, so we'll call this sales detail.
0:18
So the invoice in the customer level data phrase both had customer numbers, but they called it to you for things custom and accounting.
0:26
but due to our business knowledge, we know they're the same thing. So we merge those together, and now we have a single file that has all
0:33
the customer information, the state in the region, information as well as a sales rep and invoice level detail.
0:40
And the reason we did this is now when we do our group buy, everything is on one place, and we can easily iterated through our analysis.
0:47
So we have 2000 rows, which is what we would expect. There are some remains the same. So the next step we need to bring in our commissions.
0:59
Now we set our commission rate and let's add the commission amount to our sales details Now we have commissioned for each transaction.
1:11
Now we have all the low level details we need so we can start aggregating grouping and pivoting to understand it. More detail.
1:19
Let's take a look at the skew level commissions. So this works correctly, But I'm gonna use sorting to make it a little bit
1:28
easier to view. So you sort values. Now we can see that s w 500 nest up you 200 s w 1 21 have a lot more commission than some of the other products.
1:42
You can see the A C C products have very little commission. Maybe we want to look at now what the sales reps are earning.
1:52
So in this example is that type today and last name didn't come through. So if I look and made a mistake when I typed in my use call So
2:03
I need to put J in there. No, it's rerun everything. And now we have her last name.
2:11
So this shows us which salespeople are earning and we definitely have a difference between Sutton
2:18
and Tiner. We're earning three and $400,000 whereas Malcolm in munis are only about 100,000 Let's bring in a channel.
2:26
See what that looks like. This is a little hard to read, so let's turn it into a pivot table. This starts to give us some of interesting information.
2:44
Tiner has partner commissions, whereas no one else does. And if we look at Sutton has a lot of commissions for resellers ous well as
2:56
tighter. And Tiner also has a lot of retail commissions. So there may be something we need to look at here from the distribution,
3:04
understand how toe equal isis a little bit more.