1 of 9

MART 330 Integrated Digital Marketing

Tutorial 10: Spreadsheet graphics

John Williams

2 of 9

Agenda

Assignment 5: Requirements

Download the data from Google Analytics for the Families campaign (5–27 May, 2016) that shows the source of hits
Produce both a pie chart and bar chart that shows the proportion (not count) of referral (non-direct) hits grouped by TLD, as shown in the reference file. Modify the chart titles, as noted in the file. You must use formulas to calculate the total and proportions.
Write in the spreadsheet which graphic you’d use for a presentations to your boss for this type of analysis, and why.
Post the link (for Google Sheets) or upload the file (for Microsoft Excel or OpenOffice Calc) to Blackboard before 5PM, Monday 25th September.

Assignment 5: Get the data

Go to Google Analytics and choose the Families campaign and date range 5–27 May, 2016
Click on Acquisition→All traffic→Source/Medium. Then click on Source.
Click on the buttons above and to the right of the table to show a pie chart, and then a bar chart. Think about which gives you the most information most easily, quickly and accurately.
Transfer the data to either Google Sheets, Microsoft Excel or OpenOffice Calc

Assignment 5: Prepare the data

Delete the unnecessary cells. We just need Source and Users
Aggregate the counts for the top level domains. All the non .com TLDs are spam!
Create a new column that calculates the proportion/percentage corresponding to each count (you will need to use the sum function and an arithmetic operator, i.e. +,-,*,/)
In the sheet, calculate (i.e. use a formula) the total referral hits, the total spam and non-spam hits, and the the proportion of spam vs. non-spam hits

Assignment 5: Creating spreadsheet graphics

Select the range that you want to plot, then drop down the Insert menu and choose Chart (Google Sheets and Open Office Calc) or Charts (Microsoft Excel). You can select non-contiguous ranges using the Ctrl key.
Make the appropriate choices to produce the output shown in the reference file. The GUIs are all fairly self-explanatory. If you have trouble, just ask.
Follow general principles:

All charts should be clearly labelled, i.e. have at least a title describing what it shows
The meaning of axes (units of measurement) and what the the colours and shapes are supposed to represent should be clear.
Less is more: can you show the same information using less ink/fewer pixels?

Pie charts versus Bar charts

Most analysts hate pie charts. The human eye is not very good at judging the relative size of pie wedges, but very good at judging the relative height or width of bars, i.e. you can more quickly and accurately interpret bar charts
Pie charts have to have much more annotation for the same level of information content
Some people say that business people/managers prefer or can more easily understand pie charts. There is limited evidence for this: do you agree? How do you feel?
Simplified advice: to impress/sell use pie charts; to analyse/explain use bar charts.

Summary

Effective graphical communication involves both art and science (you can get a job as a “data artist”, believe it or not)
There are some simple principles for communicating numbers graphically that are easy to learn and follow (so do so!)
Most important principles:

It should be as simple as possible, but no simpler (less is more)
Design from the point of view of someone who has never seen or heard of the data before, i.e. clearly label things, don’t assume that it’s obvious

Activities

Upload your Assignment 5 file to Blackboard; remember to check the rubric before submitting
Consider doing all 10 rounds of Mimic Pro sooner rather than later, to avoid the end-of-semester assessment crunch