Using Google to create site stats

I use Google docs to create the stats graphics that I use on my site. You can see them live here and here. I love it because they're incredibly easy to keep up to date and create these gorgeous interactive graphics. I've gotten a lot of positive feedback about these graphics and requests for explanation, so here goes!

Let's start out. You'll need a Google account for this, so if you don't have one, you'll want to go ahead and make one for yourself.

Head over to docs.google.com and sign in.

On the left, click the red Create button and select spreadsheet.



Choose what data you want to track and make a column for each. Keep in mind that some of this can be for your own information--not everything you put in has to be for graphics.

I include: character name, player, play-by, character age, membergroup, character gender. You can include as much or as little info as you want.



Enter in information for the characters.



Sheet 1 (where you are now) will be your character info.

Now we're going to generate some stats. To keep things organized, we'll use a new sheet. Click the + sign at the bottom.


Decide what information you want to create your stats.

Ages: I split ages into 10 year ranges. You can do any range you want, or do individual years.

In column A, type in what your stats are.
In column B, use these codes:
UNDER X age

Code: Select

=countif(Sheet1!RANGE, "<##")

Where ## is the age (I do "under 20") and RANGE is where your ages are in sheet 1 (ex: D2:D27)

Range age

Code: Select

=countif(Sheet1!RANGE, "<30")-countif(Sheet1!RANGE, "<20")

This is for characters in their 20s (20-29). You can adjust the numbers for the ranges you want.

Over age

Code: Select

=countif(Sheet1!RANGE, ">##")

Where ## is the age (I do "Over 50") and RANGE is where your ages are in sheet 1 (ex: D2:D27).


Genders

Code: Select

=countif(Sheet1!RANGE, "X")

Where range is where your genders are in sheet 1 (ex: G2:G27) and X is your gender as you write it in sheet one (if you write M or F put that in the quotations; if you wrote the whole words male or female, write that).

Other text input (such as membergroups)

Code: Select

=countif(Sheet1!RANGE, "MembergroupName")

Okay, so now we have a few lists of stats.


Highlight one section of stats.

Along the top toolbar, choose the graph icon (Insert Chart). You can also go to Insert > Chart.


Choose what type of chart you want. There is a section of recommended charts, but you can see all types by going to the Charts tab across the top.


Under the tab Customize, you can choose what colors to use. If the exact color you want isn't an option, you can change the hex code later when you insert the graph into your site.


When you are satisfied, click the blue Insert button.

Mouse over the graph. Click the down arrow. Choose "Publish Chart".


Select a publish format: Interactive Chart


Copy and paste the script into your site. Within the code, you can edit the chart height and width as well as tweak the color hex codes.

When you get a new member, insert a new row into sheet 1 and fill in the data. The numbers on sheet 2 will automatically update and so will your graphs.

This is the first time I've written a documentation, so please let me know if you'd like me to expand the details or explanations for anything! If you have trouble, make sure to include a screenshot or copy and paste your code here.

Dude, this is soooo useful! Thanks for sharing!

• This is incredible. I'll be incorporating this on my board. Thank you so much!

ooooh this looks awesome! Im soo gonna try this...thank you

This is great! I've been using googledocs to keep a spreadsheet of character traits for a while, but I'd never thought to use these other features. Thank you!

I do have a question though: Is there an easy way to break the statistics down more? So if, say, I wanted to only count female characters under age ten? I've tried different ways of narrowing it down, but I can't figure out how to get it right.

Okay, this presented a bit of a challenge for me in playing around with appropriate formulas. Woo! (I really really love working with spreadsheets, so a challenge is always good!)

Here's what I've got for you. For this formula, the D column is my age, the G column is my gender. In GoogleDocs, this formula works slightly different than in Excel, although through the same principle, something called an array formula. For an array formula, you give a task and several conditions. In this case, I'm telling it to sum up the counts (task) for ages under 10 and female (conditions).

Code: Select

=ARRAYFORMULA(SUM((Sheet1!D2:D27<10)*(Sheet1!G2:G27="F")))

You can add more conditions if you'd like. All conditions must be true for something to be counted. To add a condition, separate with an asterisk. Here's another formula. In this case I want to find characters 1) over age 25, 2) under age 50, 3) female, and 4) played by Bee (player is column B in the formula).

Code: Select

=ARRAYFORMULA(SUM((Sheet1!D2:D27>25)*(Sheet1!D2:D27<50)*(Sheet1!G2:G27="F")*(Sheet1!B2:B27="Bee")))

As a note, if you want to match text (like "F" or "Bee"), then make sure to include quotation marks. Numerical conditions are written normally.

I think this should accomplish everything you are looking for. If you have any trouble with the syntax or getting it to work, let me know.

Thank you! I was struggling with it for a few minutes, until I realized I could still use the named ranges. It works perfectly now.

Edit to add: A screencap of some charts looking great on the site!

OMG I never thought you could actually use Google for stuff like that! Google really DOES have everything, thanks!