Helping tech-savvy marketers and data analysts solve real-world business problems with Excel
Using data-driven business analytics to understand customers and improve results is a great idea in theory, but in today's busy offices, marketers and analysts need simple, low-cost ways to process and make the most of all that data. This expert book offers the perfect solution. Written by data analysis expert Wayne L. Winston, this practical resource shows you how to tap a simple and cost-effective tool, Microsoft Excel, to solve specific business problems using powerful analytic techniques-and achieve optimum results.
Practical exercises in each chapter help you apply and reinforce techniques as you learn.
- Shows you how to perform sophisticated business analyses using the cost-effective and widely available Microsoft Excel instead of expensive, proprietary analytical tools
- Reveals how to target and retain profitable customers and avoid high-risk customers
- Helps you forecast sales and improve response rates for marketing campaigns
- Explores how to optimize price points for products and services, optimize store layouts, and improve online advertising
- Covers social media, viral marketing, and how to exploit both effectively
Improve your marketing results with Microsoft Excel and the invaluable techniques and ideas in Marketing Analytics: Data-Driven Techniques with Microsoft Excel.
Wayne L. Winston is John and Esther Reese chaired Professor of Decision Sciences at the Indiana University Kelley School of Business and will be a Visiting Professor at the Bauer College of Business at the University of Houston. He has won more than 45 teaching awards at Indiana University. He has also written numerous journal articles and a dozen books, and has developed two online courses for Harvard Business School.
Helping tech-savvy marketers and data analysts solve real-world business problems with Excel Using data-driven business analytics to understand customers and improve results is a great idea in theory, but in today's busy offices, marketers and analysts need simple, low-cost ways to process and make the most of all that data. This expert book offers the perfect solution. Written by data analysis expert Wayne L. Winston, this practical resource shows you how to tap a simple and cost-effective tool, Microsoft Excel, to solve specific business problems using powerful analytic techniques and achieve optimum results. Practical exercises in each chapter help you apply and reinforce techniques as you learn. Shows you how to perform sophisticated business analyses using the cost-effective and widely available Microsoft Excel instead of expensive, proprietary analytical tools Reveals how to target and retain profitable customers and avoid high-risk customers Helps you forecast sales and improve response rates for marketing campaigns Explores how to optimize price points for products and services, optimize store layouts, and improve online advertising Covers social media, viral marketing, and how to exploit both effectively Improve your marketing results with Microsoft Excel and the invaluable techniques and ideas in Marketing Analytics: Data-Driven Techniques with Microsoft Excel.
Chapter 1
Slicing and Dicing Marketing Data with PivotTables
In many marketing situations you need to analyze, or “slice and dice,” your data to gain important marketing insights. Excel PivotTables enable you to quickly summarize and describe your data in many different ways. In this chapter you learn how to use PivotTables to perform the following:
- Examine sales volume and percentage by store, month and product type.
- Analyze the influence of weekday, seasonality, and the overall trend on sales at your favorite bakery.
- Investigate the effect of marketing promotions on sales at your favorite bakery.
- Determine the influence that demographics such as age, income, gender and geographic location have on the likelihood that a person will subscribe to ESPN: The Magazine.
Analyzing Sales at True Colors Hardware
To start analyzing sales you first need some data to work with. The data worksheet from the PARETO.xlsx file (available for download on the companion website) contains sales data from two local hardware stores (uptown store owned by Billy Joel and downtown store owned by Petula Clark). Each store sells 10 types of tape, 10 types of adhesive, and 10 types of safety equipment. Figure 1.1 shows a sample of this data.
Figure 1-1: Hardware store data
Throughout this section you will learn to analyze this data using Excel PivotTables to answer the following questions:
- What percentage of sales occurs at each store?
- What percentage of sales occurs during each month?
- How much revenue does each product generate?
- Which products generate 80 percent of the revenue?
Calculating the Percentage of Sales at Each Store
The first step in creating a PivotTable is ensuring you have headings in the first row of your data. Notice that Row 7 of the example data in the data worksheet has the headings Product, Month, Store, and Price. Because these are in place, you can begin creating your PivotTable. To do so, perform the following steps:
Figure 1-2: PivotTable Dialog Box
Figure 1-3: PivotTable Field List
- Row Labels: Fields dragged here are listed on the left side of the table in the order in which they are added to the box. In the current example, the Store field should be dragged to the Row Labels box so that data can be summarized by store.
- Column Labels: Fields dragged here have their values listed across the top row of the PivotTable. In the current example no fields exist in the Column Labels zone.
- Values: Fields dragged here are summarized mathematically in the PivotTable. The Price field should be dragged to this zone. Excel tries to guess the type of calculation you want to perform on a field. In this example Excel guesses that you want all Prices to be summed. Because you want to compute total revenue, this is correct. If you want to change the method of calculation for a data field to an average, a count, or something else, simply double-click the data field or choose Value Field Settings. You learn how to use the Value Fields Setting command later in this section.
- Report Filter: Beginning in Excel 2007, Report Filter is the new name for the Page Field area. For fields dragged to the Report Filter zone, you can easily pick any subset of the field values so that the PivotTable shows calculations based only on that subset. In Excel 2010 or Excel 2013 you can use the exciting Slicers to select the subset of fields used in PivotTable calculations. The use of the Report Filter and Slicers is shown in the “Report Filter and Slicers” section of this chapter.
Figure 1.4 shows the completed PivotTable Field List and the resulting PivotTable is shown in Figure 1.5 as well as on the FirstorePT worksheet.
Figure 1-4: Completed PivotTable Field List
Figure 1-5: Completed PivotTable
Figure 1.5 shows the downtown store sold $4,985.50 worth of goods, and the uptown store sold $4,606.50 of goods. The total sales are $9592.
If you want a percentage breakdown of the sales by store, you need to change the way Excel displays data in the Values zone. To do this, perform these steps:
Figure 1-6: Obtaining percentage breakdown by Store
Figure 1.7 shows the resulting PivotTable with the new percentage breakdown by Store with 52 percent of the sales in the downtown store and 48 percent in the uptown store. You can also see this in the revenue by store worksheet of the PARETO.xlsx file.
Figure 1-7: Percentage breakdown by Store
Summarizing Revenue by Month
You can also use a PivotTable to break down the total revenue by month and calculate the percentage of sales that occur during each month. To accomplish this, perform the following steps:
Figure 1-8: Monthly percentage breakdown of Revenue
You can see that $845 worth of goods was sold in January and 8.81 percent of the sales were in January. Because the percentage of sales in each month is approximately 1/12 (8.33 percent), the stores exhibit little seasonality. Part III, “Forecasting Sales of Existing Products,” includes an extensive discussion of how to estimate seasonality and the importance of seasonality in marketing analytics.
Calculating Revenue for Each Product
Another important part of analyzing data includes determining the revenue generated by each product. To determine this for the example data, perform the following steps:
Figure 1-9: Sales by Product
You can now see the revenue that each product generated individually. For example, Adhesive 1 generated $24 worth of revenue.
The Pareto 80–20 Principle
When slicing and dicing data you may encounter a situation in which you want to find which set of products generates a certain percentage of total sales. The well-known Pareto...
Erscheint lt. Verlag | 8.1.2014 |
---|---|
Sprache | englisch |
Themenwelt | Informatik ► Datenbanken ► Data Warehouse / Data Mining |
Mathematik / Informatik ► Informatik ► Netzwerke | |
Informatik ► Office Programme ► Excel | |
Wirtschaft ► Betriebswirtschaft / Management ► Marketing / Vertrieb | |
ISBN-10 | 1-118-41730-5 / 1118417305 |
ISBN-13 | 978-1-118-41730-0 / 9781118417300 |
Haben Sie eine Frage zum Produkt? |
![PDF](/img/icon_pdf_big.jpg)
Größe: 27,1 MB
Kopierschutz: Adobe-DRM
Adobe-DRM ist ein Kopierschutz, der das eBook vor Mißbrauch schützen soll. Dabei wird das eBook bereits beim Download auf Ihre persönliche Adobe-ID autorisiert. Lesen können Sie das eBook dann nur auf den Geräten, welche ebenfalls auf Ihre Adobe-ID registriert sind.
Details zum Adobe-DRM
Dateiformat: PDF (Portable Document Format)
Mit einem festen Seitenlayout eignet sich die PDF besonders für Fachbücher mit Spalten, Tabellen und Abbildungen. Eine PDF kann auf fast allen Geräten angezeigt werden, ist aber für kleine Displays (Smartphone, eReader) nur eingeschränkt geeignet.
Systemvoraussetzungen:
PC/Mac: Mit einem PC oder Mac können Sie dieses eBook lesen. Sie benötigen eine
eReader: Dieses eBook kann mit (fast) allen eBook-Readern gelesen werden. Mit dem amazon-Kindle ist es aber nicht kompatibel.
Smartphone/Tablet: Egal ob Apple oder Android, dieses eBook können Sie lesen. Sie benötigen eine
Geräteliste und zusätzliche Hinweise
Buying eBooks from abroad
For tax law reasons we can sell eBooks just within Germany and Switzerland. Regrettably we cannot fulfill eBook-orders from other countries.
![EPUB](/img/icon_epub_big.jpg)
Größe: 54,3 MB
Kopierschutz: Adobe-DRM
Adobe-DRM ist ein Kopierschutz, der das eBook vor Mißbrauch schützen soll. Dabei wird das eBook bereits beim Download auf Ihre persönliche Adobe-ID autorisiert. Lesen können Sie das eBook dann nur auf den Geräten, welche ebenfalls auf Ihre Adobe-ID registriert sind.
Details zum Adobe-DRM
Dateiformat: EPUB (Electronic Publication)
EPUB ist ein offener Standard für eBooks und eignet sich besonders zur Darstellung von Belletristik und Sachbüchern. Der Fließtext wird dynamisch an die Display- und Schriftgröße angepasst. Auch für mobile Lesegeräte ist EPUB daher gut geeignet.
Systemvoraussetzungen:
PC/Mac: Mit einem PC oder Mac können Sie dieses eBook lesen. Sie benötigen eine
eReader: Dieses eBook kann mit (fast) allen eBook-Readern gelesen werden. Mit dem amazon-Kindle ist es aber nicht kompatibel.
Smartphone/Tablet: Egal ob Apple oder Android, dieses eBook können Sie lesen. Sie benötigen eine
Geräteliste und zusätzliche Hinweise
Buying eBooks from abroad
For tax law reasons we can sell eBooks just within Germany and Switzerland. Regrettably we cannot fulfill eBook-orders from other countries.
aus dem Bereich