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,
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.