With marketers are poised to be the largest users of data within the organization, there is a need to make sense of the variety of consumer data that the organization collects. Surveys, transaction histories and billing records can all provide insight into consumers’ future behavior, provided that they are interpreted correctly. In Introduction to Marketing Analytics, we introduce the tools that learners will need to convert raw data into marketing insights. The included exercises are conducted using Microsoft Excel, ensuring that learners will have the tools they need to extract information from the data available to them. The course provides learners with exposure to essential tools including exploratory data analysis, as well as regression methods that can be used to investigate the impact of marketing activity on aggregate data (e.g., sales) and on individual-level choice data (e.g., brand choices).
To successfully complete the assignments in this course, you will require Microsoft Excel. If you do not have Excel, you can download a free 30-day trial here: https://products.office.com/en-us/try

AG

This course is a must for anyone who is serious about learning about data driven marketing and gleaning practical insights.

JA

Sep 01, 2019

Filled StarFilled StarFilled StarFilled StarFilled Star

One of the best courses on analytics. You will use excel, real data, and your own insights. It's a package.

從本節課中

Exploring your Data with Visualization and Descriptive Statistics, Part 2

Modules 2 and 3 focus on identifying appropriate descriptive statistics (measures of central tendency and dispersion) for different types of data, as well as recoding data using reference commands to prepare it for analysis. Additionally, you will manipulate and summarize data using pivot tables in Excel, produce visualizations that are appropriate based on the type of data being analyzed, and interpret statistics and visualizations to draw conclusions to address relevant marketing questions.

教學方

David Schweidel

Associate Professor of Marketing

腳本

So we've discussed already techniques that can be used when we're working with categorical data. So in the context of the movies we looked at movie genres, romantic comedies, drama, comedies as different types of genres. The way it might be presented in our data, though, we might have coded that as genre number one, genre number two, genre number three. That's one type of data that we're working with. Quantitative variables make up another type of data that we're often working with. So think about the number of units sold. The prices charged for a product. The volume of advertising, that we're doing. The number of products that we have on order hour, inventory levels. All of these variables that might appear in our data sets. We can perform arithmetic on them. We can perform multiplication. All of the addition, subtraction, those type of operations that we're used to, can be performed on that type of data. What I want to discuss right now are what are the numeric techniques, so how do we summarize that data numerically, and how do we describe those relationships that may exist among quantitative variables, as well as discuss some visual techniques that can be used to understand these relationships. Very much related when we're thinking about summarizing quantitative data. An example, in finance that often comes to mind are the concepts of risk and return. So if we think about a financial investment, there's an expected return that we might earn on that investment, but we're also taking on a degree of risk. And typically, when we're looking for a high return we're willing to accept more risk. Well the return that we're getting kind of gives us the expectation. The level of risk tells us how much volatility there is, so in addition to be relevant to the contents of financial Investments, we might also apply those same concepts of returning risk to customer valuation. If we think about how casinos and hotels look at their guests, some guests are the high rollers, the whales who are expected to spend a lot of money during their visit, so a high expected return. But from visit to visit we might see a lot of variation, there might also be a high level of risk. Now those customers are very different from customers who are predictable, reliable in terms of their behavior, where they might not spend as much, but what we do to observe is there's less volatility in their behavior. We might observe that with product demand as well. Some products that are very popular, well those products may not always sell well, so over the course of the year there might be a lot of volatility in those sales, as opposed to more niche offerings, that perhaps have a smaller interest base, but it tends to be more reliable. So if we look at the financial context just as an example, these are stock returns that have been pulled for Verizon in 2015 and are displayed as histograms. So the height of the bars in these histograms reflect how frequently a given return is observed. So if we look, for example, at the one-day returns In the upper left of this graph, the height of the bars, particularly the ones near 0%, indicating the high likelihood that on a given day, we're going to see very small returns. But occasionally, we observe the 4% return. We observe the minus 4% return, so that spread that we're observing, that's reflecting the degree of risk, or the degree of volatility that we're looking at in this data. So, we're going to look at numerical methods to summarize what's the expected return. We'll also look at numerical methods to summarize the degree of variability that we're observing, and so one thing that I want to show you how to do though very briefly is how do we build these histograms. The height of the bars on the histogram reflecting how frequently were observing particular observations. And then the horizontal axis or the x-axis are the different bins that we've chunked our data into. So this is just giving you an outline for the steps that are involved. We make the decision of how wide each of the bins is going to be then we have to summarize our data, figure out how many observations fall into a particular bin, and then we make a decision of those border line observations. Do they go in the bin to the left or right on the histogram? So what I'm going to do is, and follow along with me, go and launch your web browser. And why don't we jump online and go to whatever finance site you're comfortable with. I'll walk through this using Google Finance. But we're going to pull some stock return information and construct a histogram using Excel from that information. Now if you have other data available to you to build a histogram from, by all means use that. I'm using this just because it's readily available. So, and we'll go over to Verizon. The ticker symbol is v as in victor, z as in zebra, and we'll pull up the stock hold information for the company. And then once this launches what we're going to do next is download the historic prices. All right, so on Google Finance, you'll see under the company information we can click on the Historical Prices. And this'll pull up for us. What it's displaying right now is the very recent information. What I'm going to do is I'm going to change the window. But let's look at say the first six months of 2015. So let's start in beginning of January 1st, 2015, and we'll go through the end of June. Update that information. And then you'll see in Export option to Download This Correction. So we're going to click on that and it should save the file for you. And we're just going to launch that file in Excel. We maximize that window and zoom in. So what we have, the Date of the Observations, Open Price, the High-Low, the Close and the Volume Traded. We're going to create one new column. We're going to create the percentage change on that day. So in sell G1, I'm just going to type that in as a label, The Percentage Change. We're going to calculate that. So hit the Equal sign. Open parenthesis. What's our closing price, minus the opening price, divided by that opening price. And that's going to give us the percentage change. I'm going to click on the Percent button to change that, so it's displayed as a percentage. And I'm going to click on this increase Decimal button, so that we get a little bit more detail. All right, so on June 30th had a percentage change of -1.12%. If I hover over the lower part of cell G2 until I get that crosshair, I can double click on that. That's going to copy the formula all the way down column G, so long as I have observations in column F. So that's a shortcut for copying down a formula. You can also do that manually, but you're going to have to scroll through a lot of screens in order to do that. All right, so now that I've calculated the percentage change, how do I go about making the histogram? Unfortunately, Excel does not have a tool built into it that is designed for histograms. So what we're going to do is actually use the Pivot Table functionality that we looked up before, and we're going to use that to construct our histogram. All right, so I'm going to click on Insert and the first button up there is going to be to create that pivot table. And we're going to make sure that it captures column A all the way through column G. And we'll have it create that pivot table for us on a new worksheet. So I'm just going to click on OK, and we've got the pivot table, at least the skeleton of that. All right, well what fields do we want to display? We're going to focus on the percentage change piece. And what I'm going to do first is, I'm going to drag that percentage change cut variable into the Values field of our Pivot Table Builder. And one thing that we're going to do here is, what I'm interested in for constructing the histogram are the frequency of the observations. So we're going to make one change here. I don't want to look at sum, so I'm going to go into the Value Field settings, and I'm going to summarize the data using the Count.