|
|
Advanced Data Analysis
An important marketing technique is trying different approaches to a problem
(usually ones with one, small measurable distinction) and comparing results to
determine which is the most effective. In Customer Relationship Management
(CRM), even on the web, you generally analyze clients by looking at different
sub-groups (or ‘target groups’) and seeing which are most lucrative.
Commonly this kind of analysis is done with a spreadsheet program and two
analysis techniques. Most modern spreadsheets support What-if Analysis and Pivot
Tables to allow you to test different scenarios and evaluate data from varying
perspective.
|
| Figure 8. A What-if
scenario shows the projected values of a campaign. |
What-if Scenarios
Many spreadsheets support What-if Analysis or Scenarios. This allows you to
fill in a table of data with several possible options and compare them by
switching between scenarios. For example, Figure 8 shows monthly projections
for expected response from several advertising campaigns.
|
| Figure 9. Another
scenario includes the actual values for comparison. |
At the end of each month, the marketing manager can add information from the
Monthly Advertising report to the spreadsheet
as a new scenario, Figure 9. Then, by switching scenarios, he can compare the
projected response to the actual values and see whether each campaign was better
or worse than expected.
When doing What-if Analysis, you only change a particular set of cells in the
spreadsheet. This means that all the other calculated values will be
recalculated in reference to the changed values. In the example above, the Total
Revenue is calculated based on the number of hits for all ads over the
quarter. A more complicated example might have many calculated cells.
Pivot Tables
Web analytics offers many different views of your traffic, but the available
views may not always exactly correspond to how you think about your site or may
not precisely provide the insight you need to answer (or even discover)
questions about your site. Most spreadsheets provide a powerful feature for
interactively viewing data in a variety of ways called a Pivot Table. By
transferring data from your web anayltics reports into a spreadsheet and
organizing the data as a pivot table you may be able to find new insight into
your visitors’ behavior or your business goals.
|
| Figure 10. A Pivot Table in
a spreadsheet allows you to see data from varying axes. |
|
Figure 11. Sample source data for the
pivot table in Figure 10. |
Figure 10 shows a simple example of the use of a pivot table for comparing
performance of three Internet properties. In the pivot table, each web site is
listed in the rows and the number of registrations counted for each site is
summarized for each quarter listed in the columns. The pivot table also totals
the number of registrations for each site (row) and each quarter (column.) The
source data is shown in Figure 11. This is a simple example, but pivot tables
can be used for much more complex calculations. For instance, another column of
data could be added to include the revenue (gathered from sales reports, rather
than web reports) and the pivot table could track registrations, revenue,
average sales, or all of these.
Because pivot tables are constructed in spreadsheets, they are perfect for
calculating ratios (average sale size, return on investment, etc.) or comparing
budgets or expected income to actual values. The What-if analysis can be
summarized in a pivot table too. Because each scenario corresponds to another
dimension, you could summarize the revenue in each scenario in Figures 8 and
9.
|
Filtering by Referrer