Simple Ruby script to parse Pivotal Tracker CSV export and extract useful data. See comments below for usage. [Revision 5: adds output of total points per sprint]

pivotal_parse.rb

require 'csv'
require 'sqlite3'

def setupDB(db, csv_file)
	db.execute("drop table if exists stories")
	db.execute("create table stories(id, labels, iterationend, created, accepted, size, requester, owner, type)")
	CSV.foreach(File.path(csv_file), :headers => true) do |col| 
	   db.execute("insert into stories(id, labels, iterationend, created, accepted, size, requester, owner, type) " + 
	   	          "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", 
	   	          [col['Id'], 
	   	          col['Labels'], 
	   	          col['Iteration End'] ? Date.parse(col['Iteration End']).to_s : 0, 
	   	          Date.parse(col['Created at']).to_s, 
	   	          col['Accepted at'] ? Date.parse(col['Accepted at']).to_s : 0, 
	   	          col['Estimate'].to_i, col['Requested By'], 
	   	          col['Owned By'], col['Story Type']])
	end
	puts "Parsed #{csv_file} and stored in database"
end 

def storySizeAndAgePerSprint(db, output_filename)
	File.open(output_filename, 'w') { |file| 
		file.puts "iteration end,count,average size,total points,min age,max age,av age"
		db.execute( "select iterationend, " +
		"count(id) as count, " +
		"avg(size) as avsize, " + 
		"sum(size) as points, " + 
		"min(julianday(accepted) - julianday(created)) as minage, " +
		"max(julianday(accepted) - julianday(created)) as maxage, " +
		"avg(julianday(accepted) - julianday(created)) as avage " +
		"from stories where accepted != 0 " +
		"group by iterationend" ) do |row|
			file.puts "#{row['iterationend']},#{row['count']},#{row['avsize'].round(3)},#{row['points'].round(3)},#{row['minage']},#{row['maxage']},#{row['avage'].round(3)}"
		end
	}
	puts "Written #{output_filename}"
end

def storySizePerSprint(db, as_percentage = false, output_filename)
	File.open(output_filename, 'w') { |file| 
		if as_percentage then
			file.puts "iteration end,% 0s,% 1s,% 2s,% 3s,% 5s,% 8s"
		else
			file.puts "iteration end,0s,1s,2s,3s,5s,8s"
		end
		db.execute( "select iterationend, " +
		"count(case when size = 0 then id else null end) as zeros, " +
		"count(case when size = 1 then id else null end) as ones, " +
		"count(case when size = 2 then id else null end) as twos, " +
		"count(case when size = 3 then id else null end) as threes, " +
		"count(case when size = 5 then id else null end) as fives, " +
		"count(case when size = 8 then id else null end) as eights " +
		"from stories where accepted != 0 " +
		"group by iterationend" ) do |row|
			if as_percentage then
				zeros = row['zeros']
				ones = row['ones']
				twos = row['twos']
				threes = row['threes']
				fives = row['fives']
				eights = row['eights']
				total = zeros + ones + twos + threes + fives + eights
				file.puts "#{row['iterationend']},#{(zeros.to_f/total*100).round(3)},#{(ones.to_f/total*100).round(3)},#{(twos.to_f/total*100).round(3)},#{(threes.to_f/total*100).round(3)},#{(fives.to_f/total*100).round(3)},#{(eights.to_f/total*100).round(3)}"
			else 
				file.puts "#{row['iterationend']},#{row['zeros']},#{row['ones']},#{row['twos']},#{row['threes']},#{row['fives']},#{row['eights']}"
			end 
		end
	}
	puts "Written #{output_filename}"
end

def typePerSprint(db, as_percentage = false, output_filename)
	File.open(output_filename, 'w') { |file| 
		if as_percentage
			file.puts "iteration end,% bugs,% chores,% features"
		else 
			file.puts "iteration end,bugs,chores,features"
		end
		db.execute( "select iterationend, " +
		"count(case when type = 'feature' then id else null end) as feature, " +
		"count(case when type = 'chore' then id else null end) as chore, " + 
		"count(case when type = 'bug' then id else null end) as bug " +
		"from stories where type != 'release' and accepted != 0 group by iterationend" ) do |row|
			if as_percentage then
				features = row['feature']
				chores = row['chore']
				bugs = row['bug']
				total = features + chores + bugs
				file.puts "#{row['iterationend']},#{(bugs.to_f/total*100).round(3)},#{(chores.to_f/total*100).round(3)},#{(features.to_f/total*100).round(3)}"
			else
				file.puts "#{row['iterationend']},#{row['bug']},#{row['chore']},#{row['feature']}"
			end
		end
	}
	puts "Written #{output_filename}"
end

def allLabels(db, output_filename)
	File.open(output_filename, 'w') { |file| 
		file.print "label,count"
		labels_hash = Hash.new(0)
		db.execute("select labels,count(labels) as count from stories group by labels order by count desc") do |row|
			count = row['count']
			labels = row['labels']
			labels.split(",").each{|label|
				labels_hash[label.strip] += count
			}
		end
		labels_hash.each{|key,count|
			file.puts "#{key},#{count}"
		}
	}
	puts "Written #{output_filename}"
end


def labelsPerSprint(db, as_percentage = false, tags, output_filename)
	File.open(output_filename, 'w') { |file| 
		if as_percentage
			file.print "iteration end,"
			tags.each {|key| file.print "% #{key},"}
			file.print "% other\n"
		else
			file.print "iteration end,", tags.join(','), ",other\n"
		end
	    sql = "select iterationend, "
	    tags.each {|key| sql += "count(case when labels like '%#{key}%' then id else null end) as #{key.gsub(/[- :]/, '')},\n"}
		sql += "count(case when "
		tags.each{|key| sql += "labels not like '%#{key}%' and "}
		sql += "1 = 1 then id else null end) as other \n"
		sql += "from stories where accepted != 0 group by iterationend"
		db.execute(sql) do |row|
			if as_percentage
				total = 0
				tags.each {|key| total+=row[key.gsub(/[- :]/, '')]}
				total += row['other']
				file.print "#{row['iterationend']},"
				tags.each {|key| file.print "#{(row[key.gsub(/[- :]/, '')].to_f/total*100).round(3)},"}
				file.puts (row['other'].to_f/total*100).round(3)
			else
				file.print "#{row['iterationend']},"
				tags.each {|key| file.print "#{row[key.gsub(/[- :]/, '')]},"}
				file.puts row['other']
			end
		end
	}
	puts "Written #{output_filename}"
end

def averageAgeBySize(db, output_filename)
	File.open(output_filename, 'w') { |file|
		file.puts "size,average age" 
		db.execute("select size, avg(julianday(accepted) - julianday(created)) as av_age from stories where accepted != 0 group by size" ) do |row|
			file.puts "#{row['size']},#{row['av_age'].round(3)}"
		end
	}
	puts "Written #{output_filename}"
end

def ages(db, output_filename)
	File.open(output_filename, 'w') { |file|
		file.puts "av age done,av age backlog,av age icebox"
		db.execute("select " +
		"avg(case when accepted != 0 then julianday(accepted) - julianday(created) else null end) as avage_done, " +
		"avg(case when (iterationend != 0 and accepted = 0) then julianday(date('now')) - julianday(created) else null end) as avage_backlog, " +
		"avg(case when iterationend = 0 then julianday(date('now')) - julianday(created) else null end) as avage_icebox " +
		"from stories" ) do |row|
			file.puts "#{row['avage_done'].round(2)},#{row['avage_backlog'].round(2)},#{row['avage_icebox'].round(2)}"
		end
	}
	puts "Written #{output_filename}"
end

def lengths(db, output_filename)
	File.open(output_filename, 'w') { |file|
		file.puts "done,backlog,icebox"
		db.execute("select " +
		"count(case when accepted != 0 then id else null end) as count_done, " +
		"count(case when (iterationend != 0 and accepted = 0) then id else null end) as count_backlog, " +
		"count(case when iterationend = 0 then id else null end) as count_icebox " +
		"from stories" ) do |row|
			file.puts "#{row['count_done']},#{row['count_backlog']},#{row['count_icebox']}"
		end
	}
	puts "Written #{output_filename}"
end


csv_file = "mainstream__development__20131213_1126.csv"
labels = ['bfsf', 'fco', 'smart-answer', 'travel advice']

db_file = "#{csv_file}.db"
db = SQLite3::Database.new(db_file)
puts "Opened #{db_file}"
db.results_as_hash = true
setupDB(db, csv_file)
storySizeAndAgePerSprint(db, "1_sizeandage_#{csv_file}")
storySizePerSprint(db, false, "2a_sizepersprint_#{csv_file}")
storySizePerSprint(db, true, "2b_sizepercentpersprint_#{csv_file}")
typePerSprint(db, false, "3a_typepersprint_#{csv_file}")
typePerSprint(db, true, "3b_typepercentpersprint_#{csv_file}")
allLabels(db, "4_labels_#{csv_file}")
labelsPerSprint(db, false, labels, "5a_labelspersprint_#{csv_file}")
labelsPerSprint(db, true, labels, "5b_labelspercentpersprint_#{csv_file}")
averageAgeBySize(db, "6_agebysize_#{csv_file}")
ages(db, "7_ages_#{csv_file}")
lengths(db, "8_lengths_#{csv_file}")

db.close

1. Export the CSV file from Pivotal, with all the checkboxes ticked
   
2. Grab the pivotal_parse.rb script from https://gist.github.com/rooreynolds/7943047 and put it in the same directory as your exported CSV file
3. Edit line 188 to match the name of your Pivotal CSV file export
4. Edit line 189 to be a list of any pivotal labels you care about (if you're not sure which ones you need at first, don't worry about it for now. One of the outputs from the script is a list of the most popular labels)
5. If you don't already have the sqlite3 gem, gem install sqlite3
6. At the command line, making sure your exported csv and the ruby script are in the current directory, ruby pivotal_parse.rb
7. It might take a few seconds to run, and should create a .db file (an sqlite database which you can use or ignore) and a load of new .csv files (with names like 1_sizeandage_....csv, 2a_sizepersprint_....csv, etc etc) which you can open in your favourite spreadsheet. From those, you can trivially graph the various things, like this:

PS: if you want to play with the database directly but are new to SQLite, MesaSQLite is a handy gui tool.

Alternatively, sqlite3 should just work from the command line...

$ sqlite3 -csv -header yourfile.db
SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from stories limit 1;
id,labels,iterationend,created,accepted,size,requester,owner,type
18524765,"",2011-09-27,2011-09-20,2011-09-21,2,"Person One","Person Two",feature

[ctrl+D to quit]

Or even

$ sqlite3 -csv -header yourfile.db "select type,count(type) as count from stories group by type;"
type,count
bug,1196
chore,189
feature,1819
release,23

which then means you can do

$ sqlite3 -csv -header yourfile.db "select type,count(type) as count from stories group by type;" > newfile.csv

This is how computers are meant to be used.

+1 for "This is how computers are meant to be used." :-)