Jan. 1, 2010

Posted by in Facebook | 13 comments

Backup or Export Your Facebook Account

Update

This post has been superseded by “An Updated Guide to Backing up or Exporting Your Facebook.” Please refer to the new post for more current instructions and disregard the information below.

Original Post

Last update: 4 Jan 2010, 4:56PM EST

Introduction

Over the last several years, I’ve seen several technology analysts criticize Facebook for not giving users greater control over their data. Many have commented that while it’s simple for data to flow into Facebook, getting data back out can prove much harder. In the ensuing debate, various issues can get oversimplified – after all, Facebook has a business interest in keeping users, and friends create some of the data users may want to export.

But while Facebook provides few official options for users wanting to export or backup their information and activity on the site, they have steadily widened the amount of data available to third-party applications via their API over the last year or so. Consequently, users with a little technical know-how can use the API to download a wealth of information. This makes it entirely possible to create an archive of your Facebook account.

I do not believe anything I’m about to share violates Facebook’s Terms of Service, since I am simply providing tips for users to more easily download information already available to them via facebook.com for their own personal and archival use. The methods presented here do not require any techniques that avoid or work around Facebook’s official APIs.

Among the interfaces developers can use to access Facebook data, one involves sending FQL (Facebook Query Language) queries to Facebook. These FQL statements resemble standard SQL requests against a set of database tables containing user information. If you’re logged in to Facebook, you can execute FQL queries right now by visiting Facebook’s API Test Console and setting the method to “fql.query”.

With that test console, you can request most of your Facebook account information and simply copy-paste the results to a text file for your own use. In my testing, I set the response format to JSON for two reasons. First, I’m more familiar with handling Facebook information in JavaScript, so JSON is an easier format for me to work with. Second, JSON is far more compact than XML, producing much smaller responses and backup file sizes.

I’ve put together a series of 21 FQL queries that together provide a fairly complete backup/export of a Facebook user’s account. Fifteen All but six of these queries can be executed using the API Test Console without any further effort. The others can only be made from an application with certain extended permissions. Developers familiar with creating Facebook applications can enable these permissions using the following two URIs, replacing “ffffffffffffffffffffffffffffffff” with an application’s API key:

  • http://www.facebook.com/authorize.php?api_key=ffffffffffffffffffffffffffffffff&v=1.0&ext_perm=read_mailbox
  • http://www.facebook.com/authorize.php?api_key=ffffffffffffffffffffffffffffffff&v=1.0&ext_perm=read_stream

After authorizing an application you administer, you can return to the API Test Console and specify the authorized application for making API requests. This allows you to all of the FQL queries via console and not have to worry about creating code to issue the requests. Please note that I make no warranties or guarantees about using this information – I provide these tips merely as a helpful resource to users who are interested in exporting their data. I cannot guarantee this will work for you, and I cannot promise any support if you have trouble getting these tips to work for you.

The 21 Queries

Here now are the FQL queries, each with a short description. For each query, replace 00000000 with your Facebook ID number.

  1. Profile information of friends (note that friends may limit this data via their application privacy settings) (Update 3; added “quotes” on 3 Jan 2010): SELECT uid, first_name, last_name, name, pic_big, affiliations, religion, birthday, birthday_date, sex, hometown_location, political, current_location, activities, interests, music, tv, movies, books, quotes, about_me, hs_info, education_history, work_history, profile_url, profile_blurb, family, username, website FROM user WHERE uid IN (SELECT uid2 FROM friend WHERE uid1 = 00000000)
  2. Events you have attended: SELECT eid, name, tagline, pic_big, host, description, event_type, event_subtype, start_time, end_time, creator, location, venue FROM event WHERE eid IN (SELECT eid FROM event_member WHERE uid = 00000000 AND rsvp_status = “attending”)
  3. Your friend lists: SELECT flid, name FROM friendlist WHERE owner = 00000000
  4. The members of your friend lists: SELECT flid, uid FROM friendlist WHERE flid IN (SELECT flid FROM friendlist WHERE owner = 00000000)
  5. Your notes: SELECT note_id, title, created_time, content FROM note WHERE uid = 00000000
  6. Comments on your notes: SELECT object_id, post_id, fromid, time, text FROM comment WHERE object_id IN (SELECT note_id FROM note WHERE uid = 00000000)
  7. Pages you’re a fan of: SELECT page_id, name, pic_big, website, type FROM page WHERE page_id IN (SELECT page_id FROM page_fan WHERE uid = 00000000)
  8. Links you have posted: SELECT link_id, owner_comment, created_time, title, summary, url, image_urls FROM link WHERE owner = 00000000
  9. Threads in your inbox (requires “read_mailbox” permissions): SELECT thread_id, folder_id, subject, recipients, updated_time, parent_message_id, parent_thread_id, message_count, snippet, snippet_author, object_id FROM thread WHERE folder_id = 0
  10. Threads in your outbox (requires “read_mailbox” permissions): SELECT thread_id, folder_id, subject, recipients, updated_time, parent_message_id, parent_thread_id, message_count, snippet, snippet_author, object_id FROM thread WHERE folder_id = 1
  11. Messages in your inbox (requires “read_mailbox” permissions): SELECT message_id, thread_id, author_id, body, created_time, attachment FROM message WHERE thread_id IN (SELECT thread_id FROM thread WHERE folder_id = 0)
  12. Messages in your outbox (requires “read_mailbox” permissions): SELECT message_id, thread_id, author_id, body, created_time, attachment FROM message WHERE thread_id IN (SELECT thread_id FROM thread WHERE folder_id = 0)
  13. Your wall posts (requires “read_stream” permissions): SELECT post_id, app_id, source_id, updated_time, created_time, attribution, actor_id, target_id, message, app_data, action_links, attachment, comments, likes, privacy, permalink, tagged_ids, is_hidden FROM stream WHERE source_id = 00000000
  14. Comments on your wall posts (requires “read_stream” permissions): SELECT post_id, fromid, time, text FROM comment WHERE post_id IN (SELECT post_id FROM stream WHERE source_id = 00000000)
  15. Your photo albums: SELECT aid, cover_pid, name, created, modified, description, location, size, link, visible, modified_major, type, object_id FROM album WHERE owner = 00000000
  16. Comments on your photo albums: SELECT object_id, post_id, fromid, time, text FROM comment WHERE object_id IN (SELECT object_id FROM album WHERE owner = 00000000)
  17. Your photos: SELECT pid, aid, src_big, src_big_height, src_big_width, link, caption, created, modified, object_id FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = 00000000)
  18. Comments on your photos: SELECT object_id, post_id, fromid, time, text FROM comment WHERE object_id IN (SELECT object_id FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = 00000000))
  19. Your videos: SELECT vid, title, description, thumbnail_link, embed_html, updated_time, created_time FROM video WHERE owner = 00000000
  20. Comments on your videos: SELECT object_id, post_id, fromid, time, text FROM comment WHERE object_id IN (SELECT vid FROM video WHERE owner = 00000000)
  21. Groups you’re a member of: SELECT gid, name, nid, pic_big, description, group_type, group_subtype, recent_news, creator, update_time, office, website, venue, privacy FROM group WHERE gid IN (SELECT gid FROM group_member WHERE uid = 00000000)
  22. People tagged in your photos (Update 2; added 2 Jan 2010): SELECT pid, subject, text, xcoord, ycoord, created FROM photo_tag WHERE pid IN (SELECT pid FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = 00000000))
  23. People tagged in you videos (Update 5; added 4 Jan 2010): SELECT vid, subject FROM video_tag WHERE vid IN (SELECT vid FROM video WHERE owner = 00000000)

Notes

  • These queries produce JSON (or XML) data that can then be parsed by code written to handle them. The results are not in a format (such as CSV) that can be immediately opened as a spreadsheet. If you are expecting files you can easily read and edit, you will probably be disappointed.
  • Note that this does not provide you with direct contact information for your friends (e.g. e-mail addresses, phone numbers, etc.) as one cannot access such data via the Facebook API. This is a limitation imposed by Facebook that I can’t get around without breaking the TOS. (Addendum: The URI http://www.facebook.com/friends/?filter=pfp lets you pull up all your friends’ phone numbers, which would be fairly easy to copy-paste.)
  • Update 1: A little more investigation reveals that you can save all of your friends’ phone numbers in a JSON format by visiting this URI: http://www.facebook.com/friends/ajax/superfriends_phonebook.php?__a=1 The “payload” parameter lists the phone numbers, sorted by each friend’s Facebook ID number.
  • These queries only yield metadata about photos and videos, not the actual files. If you want to backup the photos and videos themselves, you will either need to load them using the URIs in the query results or use a service designed to backup such information.
  • I’ve chosen at this point to only include queries for photos and videos you’ve actually posted. It’s possible to request all photos or videos you’ve tagged in, but that starts raising more questions about the type of information you’re saving, as well as creating duplicate results for photos of yourself that you’ve uploaded.
  • Not all of these queries return all possible fields for a given FQL table. I have made selections regarding what I thought would be the most useful fields and what would yield reasonable response sizes. While I tried to make each request complete enough to represent a backup of the relevant information, you may disagree with my choices.
  • Update 4: I originally said that the query for friends’ profile information could be limited based on friends’ application privacy settings. However, I’ve since discovered that using the Test Console as the requesting application allows access to all information visible to the user. Remember, application privacy settings only apply to applications you’ve not authorized but your friends have authorized. Apparently, Facebook operates as though every user has authorized the Test Console.
  • Some of these requests may generate very large responses, so wait a few minutes if the results do not appear right away. In some cases, you may need to limit requests (i.e. adding “LIMIT 1,500″ for the first 500 results, “LIMIT 501,1000″ for the second 500, etc.). I tested the first query against an account with about 700 friends and did eventually receive a response using the Test Console, but it was about 1.2MB of data.
  • In my testing, the responses to these queries were complete, with one apparent exception. The query for comments on wall posts seemed to include only recent comments. I haven’t investigated the issue much yet but will post any updates if I find more information on the issue.
  • Please feel free to send questions or feedback to me (my e-mail is theharmonyguy on Gmail), but realize I may not be able to provide technical support if you’ve never used FQL or worked with JSON data. This is not a general solution for non-technical users by any stretch, but it at least provides an option for backing up Facebook information that’s more complete than other methods I’ve seen elsewhere.
  1. and for those not into programming our small Adobe Air app ($2.99) will do the backup for you – see http://www.socialsafe.net. Julian, Chairman, SocialSafe

  2. Julian, I have used SocialSafe and was honestly rather disappointed by it. The original version only backed up the info tab of my profile, my photos, and a list of my friends’ names, ID numbers, and profile pictures. The newest version has added wall posts. Another service I’ve tried is Backupify, which is free; that one saves photos and notes. If you look at the queries I posted, you’ll see that they back up far more information than SocialSafe or Backupify (though the queries do not download actual photo files).

  3. Is there a way to get friends email addresses?

  4. AJ: Basically, no. Facebook is very protective of users’ e-mail addresses and provides no official means to access them. In the past, one company tried running a script that scraped them from profiles manually, and Facebook shut their tester’s profile down.

  5. OzzyGreene says:

    Awesome

  6. Just had a little play with the URLs returned by the photos query (#17). Seems there is no actual access control on the images themselves, just the fact that it is a pretty obscure URL.

    I tested this using a different browser that has never been logged into Facebook before, and just cut and pasted the URL from the XML.

    Not sure how much of a privacy concern this presents. Presumably somebody putting in random variations of URLs could come across a picture or two of yours – either that or the URL is not that obscure and somebody figures out the naming convention.

  7. Michael Fomkin says wow this is indepth research

  8. Pep Oncios says:

    You can also backup your facebook account with Backupify…

  9. @Pep: As I mentioned above, in my experience, the range of data that Backupify backs up pales in comparison to this method.

  10. I never thought we were on facebook data can be the backup. Thanks for the information

  11. This is great stuff. I’d like even more, ideally all content that I post, and all information directly relating to me. So: 1) all my comments, whether on things I posted or things other people posted (I should have a right to access my comments, right?), 2) everything I have Liked, 3) everyplace I am tagged in.

    If some of the items or threads have restricted access or have been deleted by others, I should still be able to access my data. You say some information requests could “start raising more questions about the type of information you’re saving”. No doubt there are tricky issues with extracting just your noodles from the big plate of spaghetti that is Facebook, but it would be nice to know how far we can go. I’ve started to realize I can’t find stuff I posted in Facebook some time ago, and that’s really not acceptable.

    In case you haven’t seen it already, there’s a recent Wired article comparing “data liberation” on various platforms:
    http://www.wired.com/epicenter/2010/02/what-do-we-want-our-data-when-do-we-want-it-now/

  12. I have a question in regards to getting all the data from a Fan page, not just from my account. This past year I had a buddy battle with brain cancer so we had set up a Fan page and had a lot of interaction. Unfortunately he passed January 1st of this year and I wanted to try and download all the posts and all the comments from the posts, put them together in some sort of book to give to his wife as a keep-sake but I can’t seem to find an easy way of doing this…I’m relatively tech savvy, but not a hard core developer so get lost pretty quickly, though if you have any tips I would greatly appreciate any direction.

    Thanks

    Matt

  13. Great, Thanks…

Trackbacks/Pingbacks

  1. Tweets that mention Backup or Export Your Facebook Account | Social Hacking -- Topsy.com - [...] This post was mentioned on Twitter by theharmonyguy and greychampion, SocialMediaSecurity. SocialMediaSecurity said: Backup or Export Your Facebook Account ...
  2. uberVU - social comments - Social comments and analytics for this post... This post was mentioned on Twitter by theharmonyguy: New Post: Backup or Export Your ...
  3. Backup or Export Your Facebook Account | Social Hacking « Blogging - [...] More here:  Backup or Export Your Facebook Account | Social Hacking [...]
  4. Social Media Security » Social Media Security Podcast 8 – Would You Commit Social Media Suicide? - [...] – A solution for backing up all your social media site content.  Check out theharmonyguy’s manual method for [...]