MKTG3528 Marketing Analytics
Individual Assignment 3, due March 22 before class
Assignment Description:
Please import the data file: assign3.xlsx into your Tableau and re-create the two figures given below. Your final submission should include 1) a png file and 2) twb file generated from a dashboard include your information and two figures, and 3) a word document describing the process of creating the two figures (bullet points). Please note that, in 3), if your step involves calculation, please also write down your equation used in Tableau, and indicate the variable name created by this equation.
Figure 1 Requirements:
1. Group “CarType” so that “A”, “A0”, and “A00” are all categorized as “A”.
2. Plot a bar chart with “Made” and the grouped “Cartype” in columns and average “Posted Price” in rows.
3. Filter out CarType as “NA”, i.e., exclude the observations with “NA” in CarType.
4. Use Filter to only focus on Sale Type==”B2C”.
5. Add a reference line per cell (black dashed line) to reflect the average “Posted Price” for both B2C and C2C cars, and the reference line should be added to each cell (i.e., by “Made” and grouped “Cartype”).
Hints: use {FIXED[Made],[Cartype (group)]: ….} to create the variable
6. Add a reference band per cell to reflect the maximum and minimum of “Posted Price” (grey area in the figure).
7. Colored the bar to differentiate if the bar exceeds the average “posted price” (dashed line) and bar falls below the average “posted price” (dashed line).
Hints: use IF AVG([Posted Price])>=AVG([variable created in step 5]).
Figure 1:
Figure 2 Requirement:
1. Plot a line chart with “Post Date” (Year-Month) as Columns and count number as Rows.
2. Display the average day lasted for car listings in each month, with 2 decimals.
Hints: calculate the day lasted using “sold date” minus “post date”.
3. Filter out/exclude observations with “post date” earlier than Aug 2015.
4. Create a variable “soldrate”=number of sold/number of listings
hints: use AVG([sold])
5. Create an indicator variable “highlow” so that if soldrate of the specific month is higher than population average returns “high” and “low” otherwise.
hints: use WINDOW_AVG([soldrate]) to represent the population average
6. Use the indicator “highlow” to differentiate the month where the sold rate is higher than population average and higher than population average by both shape and color.
Figure 2:
Present your final results using a dashboard: following the format below:
Title of the dashboard, Your name and UID, title of the figure, floating legends, export as both PDF and twb.