Tag Archives: Facebook

Check-In Tracking with Power Query and Power View

For me, 2014 was a particularly busy year. I was fortunate enough to have been invited to speak at a number of international conferences, and took a few wonderful personal trips as well. When travelling, I have gotten into the habit of doing Facebook check ins as I go. It occurred to me after describing my year to a friend, that since Power Query supports Facebook, I should be able to visualize my travel year easily enough.It wasn’t only easy, it was also very fast.

Acquiring the data

Getting data from your Facebook feed is really quite easy. Once you have Power Query installed, open a new Excel workbook, select the Power Query tab. Then from the “Other Data Sources” button, select Facebook.

image

Unless they’ve already been saved, you’ll be prompted to enter Facebook credentials. Once you’ve done so, you’ll be prompted for a Facebook object, and a Facebook connection (or feed). image

There is a wealth of data that can be retrieved, but in our case, we’re interested in check-ins, which are expressed in Facebook as posts. Therefore we select “me” (the currently logged in user) and “Posts” and click OK. The Power Query editor loads with all of our Facebook posts. It should be noted that Power Query is using Facebook Graph to perform the query, as can be seen in the function bar:

image

That import function can be edited, so any valid Facebook graph expression can be used to query data, not just those automatically generated by the Power Query UI.

Unfortunately, “Posts” contains a lot more data than just check ins, so we need to filter it down. We do that be applying a few transformations. All check ins are status posts, so we start by filtering the type column, selecting “status” as the filter value.

image

Not all statuses are check ins. However, all statuses with a value for location are. Where is location? It’s in a related record in the “place” column. The place column values are related records, so first, we need to expand the place column. To do so, click the small “expand” icon next to the column title.

image

Next, select the columns that you want to expand. In this case, all we need is location, so that’s all we need to expand. When OK is selected, the location data is expanded, and the original place column is replaced with a new place.location column. However, the values for location are themselves records, so they also need to be expanded. We then follow the same procedure that we did for “place” to expand them.

image

Once the columns are expanded, it is fairly obvious that only some entries have values for longitude and/or latitude. These are our check ins. We need to filter out all of the other records, and we can do this by filtering latitude or longitude with using the same approach that we used on the “type field earlier. This time, we deselect the null value.

image

We are left with nothing but check ins as records.

Now it’s time to clean up the data – we don’t want to load any more data than we need to perform the analysis, so we delete any unnecessary columns. Also, the expanded column names are cumbersome, so we rename those as well. Finally, the two date fields don’t get auto-detected as dates, so we need to change their data types to Date/Time. IN our case, the completed query appears as below.

image

Visualizing the data

We are now ready to visualize the data, so we select close and load. In our case, Power Query has been set to load only into the model, but your settings may vary. Since we’ll be using Power View, we need it in the data model. After the data has loaded, we select the Insert tab, and select Power View from the ribbon.

image

The first thing that we want to see is a map of our logins, so we select Country from our Power View fields which loads a list of the countries. Then we select Map from the ribbon, which loads the map. Next, we resize the map to fit our requirements. Finally, we drag the id field to the size field (visually showing number of check ins per location) and then we drag the city field under country in the locations field. At this point we can double click on a data point to drill down to a city.

image

We want to restrict the data to 2014, so we drag the created_time field into the filters column, select the advanced filters option, and select greater than Jan 1 2014 and less than Jan 1 2015.

image

Finally, we want to show a list of all of the locations, so we click just below the map visualization and select created_time, Country, state, and city from the fields list.

Normally, I’d finish the post up with a screenshot of the finished post, but this post on the Power BI blog yesterday inspired me to try embedding the actual product. Play around and have a look!

 

Windows Live Photo Gallery and Digital Frames–A Match Made In Heaven

This post may be a little late for the holiday season, but there’s always another one coming up. I’ve written before about Windows Live Photo Gallery and its promise when it was in Beta. It’s been out for several months now, and my opinion on it hasn’t changed – it’s an excellent photo organizing tool. It has some light editing capabilities, but I work frequently with RAW images, and I use Adobe Photoshop with Bridge for my picture processing tasks. However, once processing is complete, Live Photo Gallery takes over for tagging and organizing.

One of the things that I really like about the product is its integration with external applications and galleries. I store my photos in Windows Live and Facebook (for sharing with others) and in Flickr for both sharing and full size image storage. Live Photo Gallery makes this very easy. Once the pictures are tagged, you simply select the ones that you want to send (I also like to tag them with a destination/album name like “Flickr – 2009 General” so that I know what I’ve saved) and then click the relevant destination in the Share section on the tab.

image

Once configured, for each selection, you’ll get a dialog box prompting you for the album and other metadata, and in the case of Facebook, for people. Remember that Windows Live and Facebook are tightly integrated,so that people you tag with Photo Gallery will automatically be reflected on Facebook. In the case of Facebook,the dialog looks something like below:

image

This makes sharing photos online really easy to do. However, I have always found that digital frames were much more difficult. Being in technology, I of course have given digital frames to my parents, grandparents and in-laws. It’s a great way to get photos to them, but managing the content can be a bit of a nightmare. I have run across two major stumbling blocks doing this.

The first problem is the limited storage capacity of the frame itself. With cameras boasting higher and higher megapixel counts, their file sizes are increasing exponentially. Many older frames have storage capacities below 256 MB, which just doesn’t cut it. Even modern frames have a typical capacity of 1 GB and while that can be increased through expansion cards, it’s really only prolonging the inevitable.

The solution to this is to convert the images. Most frames are relatively low resolution, most being in the 640×480 or 800×600 range. If you’re counting, that’s 0.3 and 0.5 megapixels respectively. Converting the images to the native resolution of the frame will result in drastically lower storage requirements without any loss in displayed quality. The problem with this approach is that conversion software is a little above the heads of most casual users users, and it generates yet another group of pictures to manage.

The other problem is randomization. Believe it or not, most frames that I’ve encountered do not automatically randomize image play, leaving you to watch the same sequence over and over again. Since they’re usually sorted on filename, you’re often stuck watching things in chronological order. The way around the order is to get some sort of file renaming utility and rename all of the files before copying them over.

Those are the problems. However, Windows Live Photo Gallery supports plug ins for its destinations (Flickr, SkyDrive, Facebook, YouTube are all out of the box), and there is an excellent plug in written for digital frames written by Leo Lie. Essentially, it treats the frame, or any SD drive as a source such as Flickr, etc. Once you select the photos you want, you simply press the button, select whether or not you want the files resizes, and to what degree, and if you wish, it will randomize your photos for you. This solves the two problems (almost) in one fell swoop. If I tag the photos with the name of the frame, and I continue to be religious about tagging, very time grandma comes over, she can bring her SD card, I can erase it, and reload it. Simple.

You can get the plug in by clicking on my link above, or from within photo gallery, you can check out all of the available plug ins. It’s not obvious how, so I’m including a screenshot below. There is a scrollbar to the right of the Share section on the Home tab of the ribbon. At the bottom of that scrollbar is a drop down. Click it, and click Add a plug in, and you’ll be taken to the plug in gallery. There are several good ones.

image

I should also mention that while I use this for all of my relatives. I recently purchased a Kodak Pulse wireless frame for my use at home. If you have wireless, it’s a very good way to go. With it, you can send pictures directly to it, you can use Kodak’s file share, and you can email pictures directly to it. However the real value here is that it integrates with Facebook so that any pictures you post to Facebook (configurable) will show up on the screen. Since Facebook stores low resolution pictures, this is perfect. I simply use the Windows Live Photo Gallery integration to send the pictures to Facebook, and I’m done. I’ll be going on a diving trip by myself (more on that later) in a few weeks, but the family will be able to see my pictures as I post them.

Now I just need to keep up with my tagging.

Storing Data In The Cloud

Last week, my colleague Ed Senez posted a very good article about cloud computing, and it’s benefits.Our company has been making moves toward the cloud for a couple of years now, with both Microsoft’s BPOS offering, and our own SharePoint Extranet Accelerator. While companies struggle with the benefits and risks of moving pieces of their business to the cloud, I can see a huge role for the cloud in the consumer space, primarily because it is so cost effective. I have been moving a lot of my personal data to the cloud for the past little while, and I thought that I would share my current observations.

Photos and Videos

Almost any Facebook user is familiar with posting pictures. The social functionality is great – tagging people lets all their friends know that they are in a new picture (maybe not so great if you don’t like the picture, but I digress….). YouTube is of course great for uploading and sharing videos, but both of these services have one drawback – they convert the files on upload resulting in a loss of fidelity. If you care about the quality of your source content, you can’t rely on these services for backup.

This fact led me a few months back to Flickr. At first look, Flickr had a lot of limitations too – a maximum file size,and a maximum upload rate per month,which initially caused me to dismiss it. What I found out was that with the subscriber version there are no limits at all – you can upload to your hearts content, and it will store the images in their true source format. I have been doing just that when I could for the past few weeks, and currently have over 2000 pictures in my photostream. Just 8000 or so to go.

Flickr also allows you to share your pictures publicly, with family and friends, or just keep them private. However, Flickr doesn’t have Facebook’s ubiquity, so I use it for purely public pictures only, and continue to rely on Facebook primarily for sharing and people tagging. Flickr does allow for videos as well, but it does have some size limits, so I will be relying on YouTube for sharing my videos, along with a separate backup strategy (see below) as I get my videos organized.

So how much does this cost? For $25 per year, I know that all of my personal pictures are backed up. Pictures are quite literally irreplaceable. Documents can be recreated, but you’ll never have a chance to capture those precise moments again. The fact that I can use the services to share picture (in full source quality) is really just a bonus.

Simple Storage with SkyDrive

Did you know that you have 25 GB of storage in the cloud that you can use free of charge? If you have a Windows Live ID (also free..) then you do. It’s called Sky Drive, and it’s extremely handy. Simply upload the files you wish to private, shared, or public folders and they’re safely secured away and accessible from any machine with a web browser. Because SkyDrive also uses WebDAV, you can map your SkyDrive folders directly to folders on your computer.

When you are navigating through your SkyDrive, you also have access to the recently released Office Web Applications. These are light, browser only versions of Microsoft Word, Excel, PowerPoint, and One Note, and they’re completely free of charge. You can create a new document using these apps, or edit anything that you upload. These apps are very handy for occasional use, for viewing purposes, or just for accessing an Office document that may have been sent to you when you don’t have the Office applications readily available.

Sky drive should pretty much eliminate the need for FTP servers, certainly for personal use. Given the cost of the service ($0.00), I really don’t see why someone wouldn’t want to take advantage of it.

Backup

I think that everyone that has used a computer for any amount of time has at some point lost data. Afterwards, there is a mad rush to back up the systems, and then make sure that there is a system in place to back everything up. Corporations typically have solid backup strategies in place (that aren’t tested frequently enough, in my opinion), but personal users are often too busy to ensure that their data is backed up in a timely fashion. There are a ton of consumer backup product out there, but they all often have one fatal flaw. They require the user to actually do something to make it work.

This is where the cloud can be of great help. If we can assume that the machine will typically have a connection to the internet, then for all intents and purposes, our backup destination is always available. All that is needed is a good service to make this painless and automatic for the end user. There are a number of such providers out there, and I’m going to briefly discuss the one that I’ve settled on – Carbonite.

With Carbonite, you download a small application that runs in the background, and is constantly ensuring that your files are being backed up. For most users it is as simple as a next – next install, which will backup all standard data folders. If you want to back up a non standard folder, just right click on it and choose to add it to the backup. You can always see what the backup status is from the console, but carbonite also (optionally) places a small indicator over the icon for each file that you have to let you know its backup status. The backed up files are also browser accessible from any internet connected PC, allowing you to access your files in a pinch, and one of the nicest features is that it not only keeps a mirror image of your system off site, it maintains file versioning, so when you make a change to a file and later decide that it wasn’t such a good idea, you can retrieve a previous version.

Given most end users’ bandwidth constraints, the initial backup can take a little while. Mine took two weeks, but that’s me. After initial backup, it all goes very rapidly. So what’s the cost of all of this storage? You can back up as much as you want from a single machine for $55 US per year. To me, that’s a no-brainer.

 

I spend about 5-10% of my time inside my company firewall. Tools to help with remote connectivity are crucial, and I really see a place for cloud based services to provide a lot of these tools. They’re safe, they’re easy, they’re useful, and they’re highly cost effective. In storage alone, I now back up all of my important personal data (redundantly I might add) and enhance my convenience in accessing it. All for less than $100/year.

I’m sold.