Waterfall Table
The waterfall table
is one of the best tools I use and I include it with every campaign list I ever
submit to marketing. This is a separate
table that breaks down all of the criteria I that was used to create the
campaign list. It is a fantastic way of
answering most of the questions the marketing department will have when you
submit the list to them. The marketing
department will typically know that you have a certain number of customers but
they won’t know how many customers are lost every time you apply each criteria. From a marketing standpoint they will know
how many customers were available for a campaign but they will have a lot of
questions when the final list has only one quarter of the original amount. A water fall table will answer their questions
very quickly. This table accomplishes
two things, it answers what criteria was used and how many customers were lost
with each criteria. A typical water
fall table will look like the following:
|
Criteria
|
# Removed
|
# Remaining
|
|
01 Starting number
of customers
|
-
|
50,000
|
|
02 Do Not
Call customers
|
12,000
|
38,000
|
|
03 Customers
from previous campaign
|
10,000
|
28,000
|
|
04 Customers
that do not qualify
|
1,500
|
26,500
|
|
05 Customers
with a bad credit history
|
1,100
|
25,400
|
|
06 Customers
that do not live in appropriate geography
|
8,000
|
17,400
|
|
07 Customers
with a bad address
|
1,200
|
16,200
|
|
08 Customers
in the control group
|
1,500
|
14,700
|
|
Total
Available customers
|
|
14,700
|
The best way I know
how to create a water fall table is to create many versions of them as you
apply your criteria. For example, create
the first water fall table with a count query including a field describing the
reason for the number of records. The
first query should look similar to the following:
proc sql;
Create table
Waterfall01
select count(*) as count
, '01 Starting number of customers'
as Reason
from Table01
order by Reason;
quit
;
The above code will
create a table called “Waterfall01” with one record and two fields. Field 1 will be called “count” and will have
the number of records from your original table.
Field 2 will be called “Reason” and will have the descriptive text you
entered to describe the number of records.
As you move forward
use the union query to cumulatively add your criteria as you select it.
Using the example of
the waterfall table above we would then remove customers who are on the “Do
Not Call” list. We can apply the
following code (Please note, the logic
of removing the customers for each criteria is beyond the scope of this article).
proc sql;
create table Waterfall02 as
select *
from WaterFall01
union
select count(*) as count
, '02 Do Not Call customers'
as Reason
from Table02
order by Reason;
quit
;
This code selects the
one record from your “waterfall01” table and appends the one record that counts
the number of records from your second criteria and creates a second water fall
table. You can now repeat this code for
every criteria you apply. The next set
of code for the third criteria would be;
proc sql;
create table Waterfall03 as
select *
from WaterFall02
union
select count(*) as count
, '03 Customers from
previous campaign'
as Reason
from Table03
order by Reason;
quit
;
The only code I change
in the third set is the highlighted text.
I created a third waterfall table from the two rows in the waterfall02
table and the one record I created from the table03 summary query.
Your new table should
look like the following;
|
Count
|
Reason
|
|
50000
|
01 Starting number of
customers
|
|
38000
|
02 Do Not Call customers
|
|
28000
|
03 Customers from previous
campaign
|
Continue applying the
code from waterfall03 every time you remove customers due to new criteria. A fast way to apply this code is to create a
macro abbreviation that quickly writes the required text with blanks for you to
fill in. Once you have your final table
of customers you will have a Waterfall table that accompanies how many you lost
at each criteria.
Some of you may have
noticed one aspect of this code that I did not mention but is very
important. I prefixed each reason field
with a two digit number that reflects the order the criteria was applied and I ordered
the table by the Reason field in ascending order. This is important so that the table reflects
the order each criteria was applied. A
two digit field is important because the number of criteria may go beyond
nine. If you did not use a two digit
prefix number and the volume of criteria goes beyond nine then your sorting
will look like this.
1
10
11
12
2
3
4
5
6
7
8
9
It is important to
view your waterfall table in the correct criteria order so that the volume of
customers decrease and it makes sense to the audience. The order of criteria is important because
your criteria may not be mutually exclusive and if you apply the same criteria
in different order you will get different results.
Once your final
waterfall table is saved in a folder, I suggest you export it to excel and put
in some calculations so that it looks like the first table I presented in this article.
In conclusion, create
a waterfall table as you select your customers and present both to the
marketing department. The table will not
answer all of their questions but it will answer the obvious ones and save you
and them a lot of time in the future.
If you have any
suggestions on how to make a better waterfall table I would love to hear
it. Please describe how you do it in the
comments section.
Patrick
