osg analytics & business intelligence microsoft use only, internal & confidential osg...

OSGAnalytics & Business Intelligence

Microsoft Use Only, Internal & Confidential

OSG Content PublishingAnalytics & Business Intelligence

Data Analytics & Visualization Training

July 2014



Pivot Tables 101 Elements of a Pivot Table Type of Pivot Tables

−Connected Pivot Tables (Cubes)

−Local Pivot Tables

When to use them Exercise

Data Visualization Storytelling through data Do’s and don’ts Examples of good vs. bad

visualizations Templates

Updates & Agenda


Microsoft Use Only, Internal & ConfidentialOSGAnalytics & Business Intelligence

Data Visualization



Focus

Intuitive

Context

Data Visualization PillarsClear and

concise data visualizations

have the following

characteristics:

Minimize the noise of the data so the audience can focus on what’s important

Baselines, benchmarks, or targets are highlighted to put data into context

The audience can instantly see the key point the visualization is trying to convey



Data Visualization: More Best Practices1. Always think about what is the key take

away for the chart and slide

2. Identify the right type of data visualization for the job

3. Minimize the ‘noise’ in the chart by reducing the # of variables

4. Use colors, icons, arrows word bubbles & circles to bring emphasis to your key points

5. When in doubt, use our templates!

Jennifer Griffin

The point is to not show all the data, but to communicate conclusions / insights. Only show data that helps you better illustrate your conclusions.



What type of data visualization to use?

Eric Shuai

Simplify this chart



Our simplified decision chart…

What kind of data would you like to show?

Comparison

Before & After

Data table

Column Chart

Trend over Time Line chart

Composition

Over time

Stacked column

Line chart

Single point in time

Pie Chart

Stacked Column



Data Visualization ExamplesThe good, the bad the ugly…



What’s wrong w/ the chart?

Y-axis scale needs to be

adjusted



Column chart best practices

Befor

e Opt

imiza

tion

After O

ptim

izatio

n0%

10%

20%

30%

40%

50%

60%

70%

50%

65%

[Insert Metric Here]

X% increase in [insert metric]

Y-axis clearly labeled & not

inflated

Different colors used

for emphasis

*Source: WinSight, [insert global URL stem], [insert dates], [insert markets]

Data source cited with

key dates & filters

Callout to highlight key

point

Context to understand

relative performance



Pie charts gone wild

Too many dimensions



XP ESD Upgrade Assistant ReferralsMost ESD Upgrade Assistant DL’s occurred on the EOS Help page

308,442ESD Upgrade Assistant DL’s Initiated by users who interacted w/ EOS promotions

End Support Help; 77.9%

Upgrade from Vista/XP Tuto-

rial; 11.0%

Upgrade assis-tant download

online faq; 6.2%

Upgrade to Win 8; 4.0%

Windows Compare,

0.9%

ESD Downloads by Page



Bad Stacked Column Example



Good Stacked Column (Area) Example

Win 8.x traffic to Support has

remained flat since April



PC Referrals by XP GroupXP users who engage with EOS promos are more likely to click through on a laptop/desktop form factor

XP + EOS Click (17,106) XP No EOS (53,334) Win 7 (165,716)0%

10%

20%

30%

40%

50%

60%

70%

80%

90%

100%

19.0%8.3% 3.9%

36.3%

28.7%24.5%

13.7%

17.5%

18.5%

31.0%

45.5%53.0%

Desktop Laptop Misc Tablet

XP who engagew/ EOS promosmuch more likelyto click thru onlaptops ordesktops

Source: MDA, January 2013, global



PC Referral Year over Year Comparison

November December January -

50,000

100,000

150,000

200,000

250,000

300,000

350,000

PC Referral Volume YoY

2013 2014

November December January0%

2%

4%

6%

8%

10%

12%

14%

9.1%

7.8%

5.0%

9.3%

11.6%10.4%

PC Referral Rate YoY

2013 2014

Source: WinSight Data Cube, Nov 2012 – Jan 2013 & Nov 2013 – Jan 2014, global



PowerPoint TemplatesYou’re welcome…



Good for conversion rates, bounce rates or CSAT for pages with lots of traffic / ratings

Right click the chart and click on “edit data” to change the data

Day 1

Day 2

Day 3

Day 4

Day 5

Day 6

Day 7

Day 8

Day 9

Day 1

0

Day 1

1

Day 1

2

Day 1

3

Day 1

4

Day 1

5

Day 1

6

Day 1

7

Day 1

80%

2%

4%

6%

8%

10%

12%

Insert Metric & Description Here

Before After

Trending – Before & After



Tip 1: Reduce the # of variables−By reducing the # of variables,

you won’t get the huge kaleidoscope effect in your pie or stacked bar chart

Tip 2: When showing comparisons, use stacked bars−Comparing pie charts often are

hard on the eye and don’t convey the difference

Tip 3: Don’t’ make too many comparisons−Think about the point you would

like to make

Composition (OS, Browser, etc.)

Win 7; 30%

Win 8.x; 20%

Win XP; 40%

Other; 10%

Insert Title Here

Jennifer Griffin

Tip: Always start the first data set for the pie chart at the 12 o'clock position

Jennifer Griffin

Tip: percentages should always add up to 100%. You can group unessisary data into an 'other' category if needed.

Jennifer Griffin

Tip 2 and 3 belong on next slide?

Jennifer Griffin

Maybe give a guideline for #1, no more then 6 slices? 8 Slices?



Tip 1: Reduce the # of variables−By reducing the # of variables,

you won’t get the huge kaleidoscope effect in your pie or stacked bar chart

Tip 2: When showing comparisons, use stacked bars−Comparing pie charts often are

hard on the eye and don’t convey the difference

Tip 3: Don’t’ make too many comparisons−Think about the point you would

like to make

Comparing Compositions (OS, Browser, etc.)

Category 1 Category 2 Category 3 Category 4

25% 30%

10%

40%

45% 40%70%

30%

30% 30%20%

30%

Insert Chart Title

Series 1 Series 2 Series 3

Look at this

amazing data

point!



Why we updated the page: [Describe what data prompted you to

change/optimize this page]

What we learned from VoC: [What did you see in the page comments / page

onsite search / page clicks / etc?]

What we changed on the page: [What did you do to address what you learned from

the data above?]

Befor

e Opt

imiza

tion

After O

ptim

izatio

n0%

10%

20%

30%

40%

50%

60%

70%

50%

65%

[Insert Metric Here]

X% increase in [insert metric]

Comparing two volumes or conversion rates[Insert Page Change Description]

*Source: WinSight, [insert global URL stem], [insert dates], [insert markets]



Pivot Tables 101



What are Pivot Tables? Pivot Tables (also just

called ‘Pivots’) are one of the most powerful tools that enable analyzing large data sets

Pivots display data in either tables or standard charts and allow users to dynamically filter, segment, aggregate, and display complex data quickly



Pivot Table Basics

Dimensions

• Typically non-quantitative values that make up the data set. These can include dates, customer demographics (i.e. OS, browser), location, etc.

Metrics

• The quantitative values associated with dimensions. In web analytics, this is typically visitors, visits, page views, clicks, and custom events (video views, onsite search, etc.)



Elements of a Pivot Table

ValuesThis is the area where you add the metrics and calculated fields

Rows & ColumnsThis is where you can break out the data by your desired dimensions. You can have multiple dimensions in both rows & columns

FiltersAllows you to narrow down the results of the dataset to a specific dimension



Data Cubes−The most common Pivot

Tables you will use are from connected data sources such as the Windows Online WinSight data cube

−The data lives in connected database servers and allow users read only access to slice and dice the data as they see fit

−Often requires permissions from the database administrator

Local Data−You can create a Pivot Table

using any properly formatted data table in Excel

−Often, we export formatted data from reporting services like WinSight or MDA and then use Pivots to analyze the data

−Can create formulas within

Pivot Table Data Sources



There are two ways to create data visualizations with Pivot Tables1. Create a Pivot Chart: This is a

dynamic chart that will change whenever you alter the filters. Not recommended due to complex formatting.

2. Copy and paste values: This is the recommended way to create data visualizations using Pivot Table data. The chart will remain static and you can make custom changes to the data.

Creating Data Visualizations w/ Pivots



Create the following Pivot Tables:−Page views by OS & browser

type

−Onsite search by day

−CSAT by market

Use the following data visualizations:−Line chart to compare before

& after

−Stacked bar chart to compare composition

−Pie chart to show composition

WinSight Cube Exercise



Advanced Pivot Table Functions Value filters Text filters Calculated fields Pivot Table Formatting



Appendix


Microsoft Use Only, Internal & ConfidentialOSGAnalytics & Business Intelligence

Data Analytics



10,000 foot view of data driven decision making

Planning analysis & research Continuous publishing (GOUT) Integration & impact on product usage (the future)



Basic Analysis Framework

Goal setting• Aligne

d with Business Objective

• Identify metrics that roll up to business goals

• Use incentives and create accountability to scale across org

• ID data gaps

Observe the data• Collec

t key dimensions and metrics using sound methodology

• Set baselines (internal or competitive)

• Put #’s into context

Understand what’s going on• Segm

ent, segment, segment

• Capture and listen to voice of customer (VOC)

• Engage w/ other teams for additional context

Take action• Creat

e hypothesis based on VOC

• A/B test or experiment and tease out causality

• Use statistical analysis

• Repeat /iterate if necessary



Outline Business Goals First

Increase product market share vs. competition

Generate customer interest in the product

Increase customer loyalty while reducing customer churn

Maximize customer satisfaction

What does our group contribute to the Windows organization?

How do our goals tie into revenue and costs?

−NOTE: Do not define goals as metrics

The right tiles list some examples of business goals

Jennifer Griffin

Idea: Add something about them being clear and executable. What delivers value to the company (short term and long).



Linking Goals to Metrics

Keep it to the critical few

Identify goals linked to data

How to best pull the data

• Anything more than this would be too cumbersome to monitor consistently

• Ensure that goals are linked to the right web metrics

• Find data sources to track non-web data

• Clicks to download, sign up and video views are difficult to trend over time and would require a data cube

2-3

Eric Shuai

Update this blurb to be less web centric



SkyDrive Goals to Metrics ExampleBusiness Goal Relevant Metric Data Source

Increase market share against competitors

• Market share reports• Search volume compared to

Google Drive & Dropbox

• 3rd party market share research

• Google Trends report

How is WOL contributing to SkyDrive’s downloads & sign ups

• Click to download• Click to Sign Up

− Completed sign ups

• Data cube custom report & WinSight page clicks report

• Windows Live report of completed sign ups from specific FW link

How is the satisfaction with SkyDrive content performing over time?

• Content Satisfaction (CSAT)• Page Comments

• WinSight Page Overview report & Page Comments report

How does it compare to other relevant WOL pages?

• Compare click to download & click to sign up to WOL Outlook pages

• Compare CSAT to Outlook pages & Download site section

• KPI Forecast Sheet• Outlook Page Clicks Report• Outlook Page Overview

report

http://www.google.com/trends/

http://windows/content/sitemanagement/windows-live/Shared%20Documents/Forms/AllItems.aspx?RootFolder=/content/sitemanagement/windows-live/Shared%20Documents/Wave%205%20Releases/M3%20Release/SkyDrive/Scorecards&FolderCTID=&View=%7B2B9AFEA1-CC28-4248-9B80-BE287C30634A%7D

http://winbi03/portal/Default.aspx?emailID=514






Eric Shuai

Focused on clicks initially, but spun off project to look down funnel



Putting Metrics into Context 1. Benchmark against relevant pages and site sections on

WOL

−Link to KPI forecast

−WinSight page usage reports

2. Look at historical variation to understand when to freak

out

−Link to forecasting instructions to calculate standard deviation &

control limits (see next slide)

http://windows/content/businessintelligence/BI%20Documents/Forms/AllItems.aspx?RootFolder=/content/businessintelligence/BI%20Documents/KPI's/FY13%20KPI%20Forecast&FolderCTID=&View=%7B48E79236-EBF8-4686-9C89-0E7431B30168%7D

Eric Shuai

Update this w/ relevant examples such as related support pages, dev pages, etc.



Identifying Red Flags Several ways to identify

when a trend has a significant negative trend:

−Look at Control Limits in the KPI Forecast for your site section (currently on SharePoint, will also be added to KPI Scorecard on WinSight in November/December 2012)

−Compare trend to 2-3 month historical trends and look for significant swings

http://windows/content/businessintelligence/BI%20Documents/Forms/AllItems.aspx?RootFolder=/content/businessintelligence/BI%20Documents/KPI's/FY13%20KPI%20Forecast&FolderCTID=&View=%7B48E79236-EBF8-4686-9C89-0E7431B30168%7D



What is statistical significance?aka – How many [insert metric] do I need before I can analyze my data?

Any metric we look at, whether it's CSAT or CTR is an average of a distribution

The core idea of statistical significance between two averages is to prove that the overlap between the two distributions is less than 5%



What impacts statistical significance?Spoiler Alert: It’s not just the size of the sample

The two inputs that determine statistical significance are variance and sample size−An average with little variance will would require a smaller sample size to prove

statistical significance

Rule of Thumb: Try to have a minimum of 150 observations for each thing you’re comparing (page, timeframe, etc.)−Observations can be rating count for CSAT, clicks for click rate, etc.



We try to make it easyUse our Test Calculator to determine statistical significance

We have created a nifty statistical confidence calculator where you just plug in the data and it will tell you statistical significance

Works for any conversion or Content SAT data set!

Eric Shuai

add link



Exercise to determine statistical significance



Root Cause Analysis (RCA)

• Root Cause Discovery is a good exercise to conduct to identify all of the possible influences of a conversion rate

• This analysis will inform which reports within WinSight would be used to diagnose when a trend goes south

http://winbi03/portal/





http://www.google.com/trends/

mailto:[email protected]



Four Steps if You Haven’t Done a RCA

This process should identify a majority of the common root causes trouble

Full Visio process document is located here

After this process you are still stumped, let us know!

Start: Conversion Trend exceeds control limits

1. Check referrals by type (Page

Overview Report)

2. Compare Page Clicks report (if DL or Sign Up)

3. Check Onsite Search

report

4. Review Page Comments

Report

If problem is not identified,

contact BI

mailto:http://windows/content/businessintelligence/BI%20Documents/Forms/AllItems.aspx?RootFolder=/content/businessintelligence/BI%20Documents/WinSight%20training/Analytics%20Process%20Docs&FolderCTID=&View=%7B48E79236-EBF8-4686-9C89-0E7431B30168%7D

mailto:[email protected]

Eric Shuai

Jennifer to think about what other reports should be included for Dev



Take Action• Sequential / A/B tests• Testing best practices• Measuring before & after

Eric Shuai

Make sure we add examples & consumer A/B test examples

osg analytics & business intelligence microsoft use only, internal & confidential osg...

Documents

internal confidentialalways

internal confidentialhttp

internal confidentialgood

internal confidentialthe

concise data visualizations

key pointcontext

key pointswhen

key dates filterscallout