How to add Sparkline data visualization to Google spread sheets?

Standard

I like using spark lines data viz when it makes sense! It’s a great way to visualize trends in the data without taking too much space. Now, I knew how to add sparklines in Excel but recently, I wanted to use that on Google sheet and I had to figure it out so here are my notes:

1. Google has an inbuilt function called “SPARKLINE” to do this.

2. Sample usage: =SPARKLINE(B2:G2) — by default you can put line chart in your cells.

3. Then there are other options including changing the chart type. You can find them documented here:  https://support.google.com/docs/answer/3093289

4. One of the best practices that I advocate when you spark-line to “compare” trends is to make sure that you have the consistent axis definition. So the sample usage for that could like this:

=SPARKLINE(B2:G2,{“ymin”,0;“ymax”,110})

(if you want to do this for excel then here’s the post: http://parasdoshi.com/2015/03/10/how-to-assign-same-axis-values-to-a-group-of-spark-lines-in-excel/ )

After you’re done, here’s what a finished version could like on Google sheet:

Google Sheet Data visualization spark line

Here’s the working google sheet: https://docs.google.com/a/parasdoshi.com/spreadsheets/d/1EJYDTxOifeEL-YwW1a0oxXw7tFG1iAVQlwjo4EU8R-s/edit?usp=sharing

How to analyze “new users” metric by specific pages in Google Analytics?

Standard

Problem:

you want to create a funnel chart of how your new users move from their landing page to your desired destination. Ideally it’s goes something like this:

Stage 1) lands on your home/landing page

Stage 2) goes to a product page

stage 3) goes to a checkout page

stage 4) sees a thank you page

Now, if you want to analyze the conversion among these stages for a “new” user then you will need create custom reports in google analytics. You will basically need to create a report for each specific page that you want to analyze. So how to set one up?

Solution: 

1) Navigate to Google analytics profile

2) On the top of the go to “customization” section and click on create a new custom report

3) here’s how you can set up a custom report that will use you new users by a specific page (notice the page filter?).

New users by specific pages funnel visualization google analytics

Conclusion:

In this post, I outlined the steps that you need to take to setup a custom report in google analytics that shows you new users by specific pages.

Nice series on WordPress Blog Statistics:

Standard

WordPress recently did a good series on how to analyze the data that’s available to you via WordPress Blog Stats tool. This series is great if you’ve a WordPress.com blog PLUS it’s a good read for any one in the data analytics role to learn how to write-up content like this.

1. Stats Wrangling I: Digging into Your Data
2. Stats Wrangling II: Days, Weeks, and Months
3. Stats Wrangling III: Top Posts and Pages
4. Stats Wrangling IV: Referrers and Clicks
5. Stats Wrangling V: The Words that Bring You Traffic

Along with WordPress Stats, I also use data from the Google Webmaster Tools. It’s a great way to see Keywords, Top posts & pages from a search engine point of view. It’s always good to have a healthy number of people searching for your content on search engines like Google.

I hope you take a look at how Data Analytics can help your Blog Grow. The series that WordPress ran focused on their platform but if you run your blog on other platform, this should give you a good sense of how to analyze the blog statistics.

Know who is Talking about YOU on the web – set up Google Alerts!

Standard

I’ve been using Google Alerts for more than a year now and I thought I’ll talk about how it helps me keep track of who is talking about XYZ on the web. here XYZ could be a brand, your full name, your competitor’s name, your company name among other things.

So why should you care?

Well, whether you know it or not, someone out there is talking about your brand, about YOU or about your company. you can’t control that – but what you can do is to “Monitor” it. Keep an eye out on what people are talking about YOU or your brand on the internet. That way, you get to stay current w/ the conversations about things that matter to you.

And If you’re a blogger then you can set up an alert for when your blog gets found (a.k.a indexed) by Google – nice! right?

Great! How can you set it up?

1. Go To http://www.google.com/alerts

2. Configure the options:

Google Alerts For BLOGGERS SEO

Example:

Here are a couple of alerts that I’ve set up:

GOOGLE ALERTS TO TRACK INDEXED DOCUMENTS

Conclusion:

Set up Google Alerts so that you can monitor mentions about your brand, company or just things that interest you!

Google Analytics: How to Track an email campaign?

Standard

In this post, I’ll share how I learned to track an email campaign via Google Analytics.

First up, what do I mean by email campaign?

let’s say you email 1000 newsletter subscribers a link (URL) along w/ a summary – How do you track the traffic that is generated via this email campaign? Well – that’s where Google analytics can help you track your email campaigns. One metric would be how may people clicked on that link and visited your site.

Why should I care?

“If you can’t measure it, you can’t manage it” – Peter Drucker

If you do not measure what’s working or what’s not working, then you can’t improve – can you? Let’s take a hypothetical example. supposing it’s cost you $25 dollars to email 1000 people. How do you calculate the ROI on it? Well – track it! And the tool you can consider using is Google Analytics.

Now, Here are the steps to track an email campaign via Google Analytics:

Here’s the visual:

google analytics track email campaign

Here are the steps:

1. First Step is to create an URL.

Why do you need this? Basically this URL would have “meta data” that helps Google Analytics identify this link belongs to one of the campaigns.

How do we create it? Use this web service: http://support.google.com/analytics/bin/answer.py?hl=en&answer=1033867 to create an URL:

This is how an URL that I created looks: http://parasdoshiblog.blogspot.com/?utm_source=newsletter&utm_medium=email&utm_campaign=UTDEmailCampaign

google URL builder google analytics

2. Create an advanced segment in Google Analytics:

> Open Google Analytics.

> Select your site

> you should be in the audience overview report

> From here, click on advanced segment and click on new custom segment

google analytics advance segments> Here I’ve configured it like shown in the image below. Note the name of the campaign is same as the name of the campaign in STEP 1.

email campaign track google analytics> Save segment

> next time you visit, you’ll see this custom segment – select it and you’ll see only from the campaign that you want to track:

google analytics custom segments traffic

That’s about it for this post. your comments are very welcome!

Found something interesting by exploring a “List of companies by revenue” Data Set:

Standard

I like exploring data sets to find interesting patterns from them. To that end, I was exploring a data-set: List of companies by revenue and I added a column to calculate Revenue/Employee to explore the dataset:

And I found an outlier!

Here’s the outlier: Exor

Here’s what it’s interesting:

It’s revenue in 2012 is: 109.15 billion USD

And number of employees is just 40!

Just think of Revenue/Employee !

To put things in perspective, Lets Compare that with its neighbor in the data-set:

Rank | Company | Industry | Revenue in USD billion | Employees

48 Koch Industries Conglomerate 110.00 60000.00
49 EXOR Investment 109.15 40.00
50 Cardinal Health Pharmaceuticals 107.55 40000.00
51 CVS Caremark Retail 107.10 202000.00
52 IBM Computer services 106.92 433362.00

I got to know about this by quickly creating a data visualization to explore the data-set:

list of companies by revenue

And removing Trafigura, Vitol and Exor, this is what we have:

power view excel 2013 rank revenue employees

Observation: oil and gas industry have relatively higher revenue/employee ration.

That’s about it for this post. Thanks for reading about my data exploration!

Data visualization: List of largest IT companies in the world

Standard

I was going through the list of largest IT companies in the world. And I thought, it would be great to see it visually! so here it goes:

[created using Power View in Excel 2013)

power view scatter chart excel 2013Configuration of Scatter Plot:

fields list power view scatter chart excel 2013

Some of observations:

– Foxconn has low revenue/employee ratio (I guess, it’s because they must be employing a lot of workers for their electronic manufacturing plant at low cost)

– Samsung is ranked number 1 and Apple is ranked 2. But apple has better revenue/employee ratio. Also Apple’s market cap (represented by Size of bubble) is greater then that of Samsung

– there’s a cluster that comprises of MS. Google, Amazon.. etc Also one more cluster of HP, Panasonic and IBM

And here’s the Data that I’ve used:

Rank Company Revenue (USD Billion) Employees Market cap
1 Samsung Electronics $188.10 221,726 $200
2 Apple $156.50 76,100 $427.62
3 HP $120.30 331,800 $32.46
4 Foxconn $119 1,230,000 $27.20
5 IBM $104.50 433,362 $229.45
6 Panasonic $99.65 327,512 $22.70
7 Microsoft $73.72 94,000 $231.03
8 Dell $62.07 106,700 $22.97
9 Amazon.com $61.09 88,400 $120.03
10 Fujitsu $54.46 173,155 $125.83
11 Intel $53.34 104,700 $105.26
12 Google $50.17 53,546 $248.31