Introduction to Marketing Analytics in Excel
Excel possesses many functions that can easily support Data Analytics Projects in Marketing. Most business students are either not familiar with these easily accessible functions or do not know how to apply these functions to real-world business projects. The purpose of this module is to introduce students to some easy-to-use Excel features that can be applied to various data file formats to extract information that would be useful to a marketing professional. The four main concepts in Marketing – Product, Price, Promotion, Place – are very suitable to be explored with the Data analytics functions described in this module.
Chapter 1
Excel features relevant to marketing Analytics to be covered in this Chapter:
Importing Marketing Data in Excel
Data can be available in many different formats: text files, database files, html files, xml files, pdf & document files, archive files and other less popular or proprietary formats. In this module we will focus on working with text files. Most text files come with a “txt” extension. A “csv” extension is also used for text files but in this case a comma separates the various pieces of data in those files. (CSV stands for comma separated values). For example, the first line in a csv file can look like this:
Product, Serial Number, Price, Quantity
As you can see, this file will be relevant for a marketing professional because it includes information about products and prices – variables that are usually significant in Marketing.
In some cases when a text file is created or stored on an operating system that does not require file name extensions, a text file might not have a “txt” or a “csv” extension. In addition, text files can use other visible or invisible characters to separate the pieces of data inside them like Tab, column, one or more blank spaces, etc. Text files are the simplest way to store information. One can tell if a file is a text file if the file can be opened and read with a simple text editor, for example, Notepad in Windows, Nano in Linux, TextEdit on a Mac. If one sees many unreadable characters when opening a file with a text editor, the file is most likely not a text file.
When you are downloading a CSV file through a browser in Windows, the file is by default opened in Excel if Excel is installed on the computer. Other text files are preferred to first be downloaded on a computer before being used for data analytics. Once a text file is downloaded, you can start Excel. Click on “Blank workbook”. On the menu click on “Data”. All the way to the left on the ribbon is the “Get Data” button. Click on that button, then click on “From File”, then click on “From TEXT/CSV”. Select the file that you would like to open and click “Import”. Excel will show you a preview of the file. Excel will try to detect the character that separates the different pieces of data and will place the different pieces of data in different columns. If you are happy with what you see on the screen you can click on the “Load” button in the bottom right corner. The data will then be loaded in an Excel table.
You also have an option to click on the “Load To” button. This will display the “Import Data” dialog box. Here you can choose in what form the data will display in Excel. You can choose Table, PivotTable Report, PivotChart, or Only Create Connection. The “Only create connection” option only creates a connection between the file and Excel but does not copy the data from the file into Excel. You can also decide the location within the Excel sheet where you want the data to be displayed or if you would like the data to be displayed in a new sheet. There is also a check box called “Add this Data to the Data Model”. If you select that option Excel will remember the structure of the file you are importing and Excel would be able to manipulate this structure later using another plug-in, PowerPivot, which will be discussed in a different module.
If you would like to make changes to the data (for example deleting columns, changing column names, etc.) you can click on the “Transform” button. This will activate the “Power Query” Excel plug in, which you can use to make these changes. This plugin will be covered in a later module.
In this module we will discuss how to procced further after clicking the “Load” button.
Sorting and Filtering Marketing Data with Excel Tables
Once the data is in an Excel table, you can learn a lot about it using some simple features of Excel. The column names will be formatted differently than the rest of the data. A little arrow appears to the right of each column name. If you press on the arrow, a drop down menu appears that lets you perform several different functions in Excel.
If the column contains text information, you can sort the data in the Table alphabetically by the text in that column. You can also apply reverse alphabetical order if you like. If the column contains numerical information, you can sort the data in the Table in an ascending or descending order by the number in that column. To sort by multiple columns, you have to click on “Home” on the menu, then click on the “Sort & Filter” button in the Editing section of the ribbon, then select “Custom sort”. Click on “Add Level” to add a column, then use the “Sort” and “Order” drop down menus to choose how the data should be sorted.
The menu also allows you to filter the Table based on the data in the columns. You can use either Number or Text filters depending on the values in a column. For example, to find the rows with the top 10 values in a numerical column, click on “Number filters” and click on “Top 10”. If you would like to see all rows in the data file that pertain to a certain Product, click on the Product column. In the bottom part of the drop down menu click on the very top check box that says “(Select All)”. This will deselect all the remaining check boxes. Scroll down and find the product that you need and check the box in front of it.
Applying the Analysis Toolpak to find descriptive statistics related to Price.
If you click on the arrow to the right of each column name in an Excel table you can also see a list of all the unique values of that column. Depending on the size of the table and the kind of data stores in this column, the list could be really long. We will next discuss how some important statistical variables based on the data in a numerical column can be calculated using the Analysis Toolpak plug-in. Let us assume that the data file we are using has a Price column that contains the prices of all products being sold by a business on a website.
Click on the “Data” tab. If the “Data Analysis” button does not appear to the right side of the ribbon, you have to add this plugin to Excel. Click on the “File” tab, then at the bottom left side of the screen click on “Options”, then click on “Add-ins”, select “Analysis Toolpak”, click on “Go”. In the pop-up dialog box select “Analysis Toolpak” and click “OK”. Now you are ready to use the Analysis Toolpak plug-in.
The Analysis Toolpak plug-in has many features. In this module we will only look at the Descriptive Statistics feature. This is the feature that calculates some important statistical variables about a set of numerical data. To do that, click on the “Data” tab, then click on the “Data Analysis” button, then select “Descriptive Statistics” and click “OK”. A new dialog box will appear. In that box click black arrow that points up and select all values in the Price column including the column name. Click on the check box “Labels in First Row” and click on the check box “Summary Statistics”. The default output option is “New Worksheet” so you can leave this option as it is. Then click the “OK” button. If there are no errors in the data set, Excel will display the column name and then the following key descriptive statistics about the Price column: Mean, Standard Error, Median, Mode, Standard Deviation, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum, and Count. Many of these statistics are useful in Marketing because they provide a general mathematical overview of the Price variable. The Count tells you how many values were included in the calculation. The mean shows you the average price. The relative position of the Mean and Median can give you an idea of whether the price distribution is symmetric or skewed. The Minimum and Maximum show you the highest and the lowest price. The Mode will show you the most popular price.
Price distribution visualization with Histograms
Laboratory Exercise
File to use: superstore.csv downloaded from Kaggle.com (https://www.kaggle.com/datasets/bravehart101/sample-supermarket-dataset?select=SampleSuperstore.csv)
Please follow the steps below to implement this laboratory assignment:
Chapter 2
Excel features & concepts to be covered in this Chapter:
Data Types in Excel
Normally data comes in two distinct types: Number or Text. The difference depends on the functions that can be applied to these two basic data types. If one’s intention is to use a piece of data for manipulation by mathematical operations (for example, addition, multiplication, comparison, division, etc.), one should choose or assign the Number data type. If one’s intention is just sorting or labeling, the one should choose or assign the Text data type. There is also a Date data type which stands between the Number and the Text type because some mathematical operations can be performed between dates (for example how many days have passed since a certain date, or which date came first) but also dates can be used for sorting or labeling.
Excel has a Number data type but also several other numeric data types: Currency, Accounting, Percentage, Fraction, and Scientific. Excel also has a Short Date, Long Date, and Time data types which are used to keep track of a temporal variable. When working with a data file it is important to assign the correct data types to each column. Normally this is done automatically by Excel when the file is imported, however, sometimes you will need to make changes if Excel makes a mistake by assigning the wrong type to a column (variable). For example, if you are importing a file containing five-digit zip codes, Excel might assign a Number data type to this variable, however, since you will never be applying mathematical operations to Zip codes, it is more appropriate to assign a Text data type to this column. The assignment can be done by Power Query before the data is imported into Excel.
Modifying Data with Power Query
Power Query is a plug-in of Excel that can be used to modify data before it is imported in Excel. Use Section 1 in the previous lecture to learn how to import a simple “CSV” text file into Excel. If you would like to make changes to the data before you import it using Power Query, instead of pressing the “Load” button in the instructions from Section 1 about, you should press the “Transform” button. This creates an Excel query which is displayed in the Power Query Window. The window contains five tabs: File, Home, Transform, Add Column, and View. The window also contains a ribbon with multiple button command in different sections of the ribbon.
The window also contains a Query Settings tab on the right side of the screen that records all steps that have been executed during the import and modification of the data. If you want to undo one or multiple steps in the process, you have to use this tab. You can not use Control + Z to undo a step.
The File tab contains:
“Close and Load” command that imports the data into Excel.
“Close and Load to” command that will ask you where and how to import the data into Excel
“Discard and Close” command will delete the data query.
“Options and Settings” let’s you make changes to the default Excel settings for Power Query and there is also a “Help” button
The Home tab contains:
“Close & Load” & “Close and Load to” command that correspond to these under the File tab.
The Query section of the ribbon lets you make limited changes to the current query. For example, you can change the name of the query, add a description, and so on.
The Manage Columns section lets you choose columns and then modify the data by removing some of the columns that you might not need for your analysis. This is especially helpful for large data files.
The reduce Rows section lets you remove some rows that you might not need or that contain errors.
The Sort section of the ribbon has buttons that let you sort a row in alphabetical or reverse alphabetical order for text columns and ascending or descending for numerical columns.
The Transform section of the ribbon is really important because it allows you to make more substantial changes to the data before it is imported. The ‘Split Column” button lets you split one column into multiple columns. For example, if you would like to split a Social Security Number into three sections based on the dashes in between the numbers, you can do that using the “Split Column” button with the “By Delimiter” option. There are other options to consider for other cases. These are “By Number of Characters”, “By Position”, “By Lower Case to Upper Case”, “By Upper case to Lower case”, “By Digit to non-digit” & “By non-digit to digit”. The “Group By” command allows you to aggregate the data before you import it into Excel. You can choo the Basic or Advanced way to aggregate the data. For the Basic option you have to specify which column is the basis for the aggregation, what the name of the new aggregated column will be, what mathematical operation will be chosen to aggregate the data (the options are: Count, Sum, Average, Median, Minimum, Maximum, Count Distinct Rows, and All rows), and a Column if one is used to provide the aggregation criteria. The Advanced option is only a little more flexible.
The Data Type button allows you to change the data type of a column. The data types in Power Query are a little different than those in Excel. The number type could be Whole or Decimal, the Date could contain a time zone, there is a Duration data type which is also based on time, there is also a “True/False” or “Binary” data types. The next option lets Power Query know if you would like to use the first row in the data set as the names of the columns. Replace values searches the data set for a value you specify and substitutes it with another value that you specify.
The Combine section of the ribbon lets you Merge or Append queries and also combine data files. The “Manage Parameters” button allows you to create and manage query parameters. The “Data Source Settings” button allows you to change a limited number of settings related to the data file you are using. The New Query section of the ribbon lets you select a new data source to create a new query. You can also select another recent data source or create you own data by pressing “Enter Data”.
Now lets turn our attention to the Add Column tab. Here one can add data to the query based on the data that is already there by creating a new column. One can create a column from other column as examples by pressing the ‘Column from Examples” button. To create a calculated column based on other columns you can use the” Custom Column button”, then enter the formula in the dialog box that pops up. If you have already created a custom function somewhere else in Excel, you can invoke it to create a new column by pressing the “Invoke Custom Function” button. The “Conditional Column” button lets you add a new column using logic similar to the IF function in Excel. The values in the new column depend on a condition based on another column. Choose the “Index column” button if you would like a column that simply counts all the rows starting from either 0 or 1. To duplicate a column, use the “Duplicate Column” button. This is sometimes necessary if you would like to keep the original column but then use that column and by applying a formula create another column. All these buttons are located in the General section of the ribbon. The From Text section of the ribbon contains buttons that can apply certain operations to text column and create a new column. For example you can decide on the format of a new column by selecting the “Format” button. The button allows for many options: Lower case, Upper case, Capitalize each word, trin, clean, add prefix, or add suffix. The “Extract” button lets you take only a part of a text. The options are: First Characters, Last characters, Text before delimiter, text after delimiter, and text between delimiter. The button also allows you to find the length and range of a text. If you are currently selecting a column containing text only the buttons in the “From Text” section of the ribbon will be available. If you are currently selecting a column containing a number, only the buttons/commands in the “From Number” section of the ribbon will be available to you. You can use the “Statistics” button to perform statistical operations. The “Standard” button allows you access to the simplest mathematical operations: Add, Multiply, Subtract, Divide, Divide (Integer), Modulo, Percentage, and Percent of. The “Scientific” buttons gives you access to some more complex mathematical functions: Absolute value, Power, Square Root, Exponent, Logarithm, and Factorial. The “Trigonometry” button lets you use trigonometric functions: sine, cosine, tangent, arcsine, arccosine, arctangent. The “Rounding” button allows you to round the result of a calculation either up, down, or according to a specific rule that you can define. The “Information” button can determine whether a whole number is even or odd, or specify the sign of a number. The next section of the ribbon is the Date & Time section which is enabled only if you select a column with a date data type. You have three buttons: “Date”, “Time”, & “Duration”. Each of these buttons provides access to a set of functions that can be applied to a Date column to create a new column. For example, you can use the “Age” option to calculate ages based on birthdays in a different column, you can parse the date, you can also just the year, the start of the year, or the end of the year. The same options are available for month, quarter, week, or day. You can subtract days or combine date and time. You can also use the Earliest or the Latest options from multiple columns. The same applies to the Time button, except that all the options above are based on hours, minutes, and seconds. To use the “Duration” button you need to select two columns with the Date data type. The last tab is the View tab. Here you have access to options that determine how the query will be displayed on the screen. These options are rarely used so we will discuss them in a further chapter.
Querying Data with Pivot Tables
The Pivot Table feature in excel provides a useful way to organize data. The Pivot Table provides several fundamental functions that process data: sorting, filtering, and grouping. These functions can be used to extract useful information from data. You can also use Pivot Tables to answer questions about a data set. You can use part 1 from the previous chapter to import a data file into an Excel table. Once the data is in a table format and without any errors, click somewhere inside the table, click on the Insert tab, and then click on the ‘Pivot Table” button all the way on the left on the ribbon. A dialog box will pop up where you can specify on which sheet you would like the pivot table to appear. It could be either a new sheet or the existing sheet. At the top of the dialog box you should be able to see the range of data that is being selected. At the bottom of the dialog box you should be able to see a check box “Add this data to the Data Model”. You can use this option if you are planning on adding more related data to the same Excel file or if you would like to make structural changes to the data. For now, use the default options and press the “OK” button.
Ona new screen you will see a report card on the left – this is the place where the pivot table will show once you create it. On the right side you will see the pivot table control panel. You use this panel to create your pivot table. In the pivot table control panel you will first see a list of fields. These are all the columns of the excel table that you already created. There is a check box in front of each field. If you check the box in front of the field, that field will be included in your pivot table. The bottom part of the control panel contains four different sections: Rows, Columns, Filters, and Values. You can click and drag fields from the upper part of the control panel and drop them into one of the four sections below to organize your data in a pivot table. Normally you use fields with either text or date data type in the Rows and Columns sections, fields with numeric data types in the Values section, and you can use either data types in the Filters section. For example, if you would like to group the data by a field, you can click on that field and drag it and drop it in either the row or the column section. Once you do that, the values of this field will show in your pivot table on the right. Normally, you would like to calculate some sort of value for each group – that is the idea behind grouping. To do that, take a numeric field that you would like to group based on the text field you already chose and drop it into the Values section. By default Excel will use the Sum function and will show you the sum of the numerical variable grouped by the filed you dropped in either the rows or the columns section. You can decide to group by many levels if you add more fields to either the Rows or the Columns section. You can also remove field you no longer need by right-clicking on the filed and selecting Remove. The pivot table will update in real time after every change. If you would like to group the data in the same way but use a different aggregate function instead of the sum, you should click on the arrow after the Sum of the variable you selected and then click on Value Field Settings. There you can find a list of statistical functions that you can use including: Count, Average, Max, Min, Product, etc. If you click on the “Number Format” button, you can also decide how you want the number to be displayed. (for example, adding a dollar sign, deciding on the number of decimal places, etc.) In addition to a one or more grouping fields, you can also filter the data by another filed. To do that, click on the field and drag it and drop it into the Filter section. Once you do that a new row will appear above the pivot table with the name of the field, and the word (ALL) in the next box. There will be an arrow after the word ALL. If you click on the arrow, you will be able to see all the values of the filtering field. Select one of these values to apply the filter you selected.
Visualizing data with Pivot Charts
Once you have created a Pivot Table, you can also create a pivot chart linked to the pivot table that will display visually the results of the pivot table. The chart will show the numerical variable as calculated for the various grouping levels in the pivot table. It will also update in real time if you change the structure of the pivot table or the filters. By default the pivot chart is a clustered column chart nut that can be changed if necessary. The control panel that pops up on the right once you press the “Pivot Chart” button on the top right of the ribbon is slightly different than the one for the pivot table. The list of fields is the same but the four sections below are different. They are Axis, Legend, Values, and Filters. You can drag a field to the Axis to group it by that filed and display it on the X axis of the chart. You can drag a field to the Legend section to group the data by that field but show it with different colors instead. You usually drop the numerical field that you would like to calculate based on your grouping in the Values section. The filter section plays the same role as in the Pivot Table. You can update the contents of these sections any way you want just like you can in the pivot table. The chart will update in real time as you are making changes.
Laboratory
Table of Contents
Introduction to Marketing Analytics in Excel 1
Importing Marketing Data in Excel 2
Sorting and Filtering Marketing Data with Excel Tables 3
Applying the Analysis Toolpak to find descriptive statistics related to Price. 3
Price distribution visualization with Histograms 4
Modifying Data with Power Query 7
Querying Data with Pivot Tables 9