Wednesday, 9 July 2014

Firefox History Statistics - Import places.sqlite extract into LibreOffice Calc

If you've followed the first part, you should have extracted from your places.sqlite copy a fairly large CSV file full of website visits. What are you waiting for? Double-click it, or File -> Open in LibreOffice Calc to launch the Text Import dialog.

Importing Correctly

You'll note from the last post that the export contains 5 columns: url,  host, rev_host, title, and timestamp.
By default, columns are imported as 'standard' text. But it is critical that you import any timestamps as a date/time field, so right click the column and choose the appropriate format.

Your data should open in spreadsheet form!

Create a Pivottable

  1. There are many ways to slice the data but a good start is with a Pivot table.
  2. Data-> Pivot table -> Create ... will launch the Pivot table wizard.
  3. Based on my spreadsheet, I chose the following:

Basic Date-Time Grouping

The timestamps will likely not be grouped at all with each different visit still listed even though it shares timestamps with another; the pivot table will be too granular.
A trick that has many extraneous applications is to group the Time field by Hour, Day, Month, or the interval of your choice.
To define your groupings, click on any timestamp then choose Data -> Group and Outline -> Group ...
Choose your settings in the following dialog.
Now you have an interactive, drillable report!
This report can be used to measure how effectively I'm using my time on the Internet. As far as invasions of privacy go, I may as well be the one snooping on myself.
Warning: As cool as this is, your browsing history, or reports thereof may not be safe to show colleagues or loved ones. Here there be monsters!

No comments:

Post a Comment