Oct 15

SQL Beginners Cheat Sheet

So its been a while again, but push comes to shove and I’m determined to get back on to making a return to this blog. I’m on a bit of a skills development fix at the moment and decided here would be a great spot to report back on what I’ve been doing and to create some documentation on the key learnings.

I recently went through a beginners course on SQL through Code Academy (link to the course here) which I found really interesting and potentially useful for future endeavors.


What is SQL? Known as a ‘Standard Query Language’ its a method of organizing and summarizing data. My general thinking when it comes to this is that taking a structured dataset it allows the user to summarize and manipulate the data. In the case of multiple datasets, it connects them through a common tie so the user can get a better understanding of how they interact.

Why is it potentially useful? I’ve experienced SQL in the context of a data tool where the language organizes raw data in such a way that its  a readable output for the end-user. When thinking about it in terms of even a basic excel spreadsheet, the language allows for the data to be summarized and reformatted saving the user from having to do a lot of manual work.

Below is a select set of terms from the course and example code:


; is used as an ‘End statement’ and is placed at the end of a command. It tells the system to move to the next query statement.

There are three basic functions of the SQL language: To BUILD, ANALYZE or JOIN data tables.


CREATE TABLE: is a command telling the system to build a new data table.

table_name(***): labels a table for use in queries and joining with other tables.

column_1 data_type: defines what data goes into a particular column (can be text, date, number etc.) These can be layered up to create a series of columns

INSERT INTO: is a formatting command used for define the set columns. Following this, column titles can be added ex: (id, name, age).

VALUES: This is a command used to enter in individual entries. Its formatted as VALUES (row value 1, row value 2…);

UPDATE: used to change entries in a table. Using ‘SET’ and ‘WHERE’ entries, data within the table can be altered

ALTER TABLE: Where as UPDATE alters an entry, ALTER TABLE allows the user to change or add data columns.

DELETE FROM: Deletes rows from a table.


Example Code:

CREAT TABLE table_name (
 column_1 data_type,
 column_2 data_type,
 column_3 data_type
INSERT INTO celebs (id, name, age)
VALUES (1, 'Justin Bieber', 21);
 SELECT *FROM celebs;
UPDATE celebs
 SET age = 22
 WHERE id= 1;
 SELECT *FROM celebs;
 twitter_handle TEXT;
 SELECT *FROM celebs;
DELETE FROM celebs WHERE twitter_handle IS NULL;


SELECT *FROM ‘table name': displays all data from the labeled table the SQL interface. The * is used to notate ‘select the whole table’. Other commands such as ‘SELECT DISTINCT’ can be used to summarize a table further based on a column or row entry.

BETWEEN: Is a filter option for the use of numerical data.

ORDER BY: organizes a data set. A related command DESC/ASC(aka: Descending/Ascending) can be used to organize numbers by a set parameter.

LIMIT: Sets maximum number of rows pulled from a dataset.

GROUP BY: organizes data by a specific column entry (example: type of apple).

COUNT: summarizes numerical data by a category (example: how many entries contain X)

SUM: Counts up the numerical data of a particular column in a table ( Ex: How many impressions in total).

MAX(): finds the largest value of a criteria.

MIN(): finds the smallest value of a criteria.

AVG(): finds the average of a criteria.

ROUND(): rounds decimal numbers to a specific integer.


SELECT* FROM movies WHERE imdb_rating >8;
SELECT * FROM movies
 WHERE name LIKE 'Se_en';
 ORDER BY imdb_rating DESC;

SELECT price, ROUND(AVG(downloads),2) FROM fake_apps GROUP BY price;


JOIN: creates a common column between two tables which joins the data set together (ex: id_actormovie JOIN ID_Actor)

ON: states the parameter of the join.

Inner Join: joins entries when a join criteria is met.

Left Join: Combines two tables together side by side and provides a NULL value when the join parameters are not met.

AS: When joining a table this allows columns of the joined table to be renamed without affecting the original unjoined tables.


albums.name AS 'Album', albums.year, artists.name AS 'Artist'
 FROM albums JOIN artists ON albums.artist_id = artists.id WHERE albums.year >1990

Oct 13

Mid October Article Wave

Over the past few  months I noticed I haven’t been exposing myself  enough to what I would consider ‘smart reads’.  Working for Christopher Berry I would occasionally get what he coined ‘waves’ of  articles, many of which I still have in a make shift digital library.  As an exercise and a means of  getting some content up on here I figure I would start up my own version of this and try to get these ‘waves’ together as a means to summarize my own top reads for the month. The Goods:

Truthfully I need to sit down and give this a full read (perhaps a blog post is in order…) but the data set analysed on this one is massive! An analysis of 700MM words from 75,000 social media users is probably one of the largest studies of online text out there. What was done on here is pretty awesome as well. Looking at demographic and personality influences , the study takes a look at what common trends occur within these different subgroups . I figure I would group these together.

  • First there is this a research paper from JWT on The State of Men  which is a quant/qual analysis of the state of Western Men ( the sample is 500 American and 500 British guys) and shared knowledge from a select group of international experts.
  • A  great follow  up to the above article is Ad Week’s “The Millennial Male is Not Who You Think He Is“.  I think first it’s a great analysis of the not so rosy picture of the millennial guy,  but it also highlights the importance of  working off of  actual research vs. personal perception  in regards to audience insights and planning.

Next up is the article Analyzing bias in opinion polls using R which looks at whether there is bias in political polling . This blog post looked through 14 years of German polling data and did what I feel is a good job at visualizing and doing a comparative analysis of all its sources. To finish this off I’ve included two articles that are a bit more timely rather than deep. The big players online (Google and Microsoft) have started to reveal how they plan to combat the growing ineffectiveness of the tracking cookie in getting you  customized  ad results. The articles below share a glimpse of their individual plans:

So this was done  in a bit of an ad hoc manner but here are the articles that were top of mind for me over the past while.  As I mentioned earlier this is a project that I’d like to keep going on a monthly basis.  I’ll likely release the next wave in  mid November. If you want to join in on this, feel  free to share your own top reads for the past while in the comments below.

Jul 13

Building Business Value in Facebook Insights

So the recent Facebook Insights Beta launched with a lot of fanfare a few weeks ago and then there seemed like a resounding ‘meh’ as discussion of it quickly faded. Obviously using the platform day in and day out I have a few ideas of what I’d like to see and how I would push the system if I had the keys to the castle, so to speak.  Facebook holds a lot of data on what people do online and I feel there is a true opportunity for them to safely push the limit on what their data can deliver. Below are just a few ideas I’ve been considering as a result of my work with the platform:

Measuring how many people actually still care: Something that is still a focus in regards to Facebook fan pages is of course the fan base . While fans don’t really have the option of doing this any more, a fan could previously be a fan but opt out of all of the page’s posts.  Having an understanding of how many people are sticking around vs. just displaying a firms logo on their Like list would be a nice little addition and perhaps avoid inflated expectations.

Who are my new fans/Who are the ones that are leaving?:  Sticking with the idea of fan counts, it would be great to have an approximation of what kind of demographic a type of content brings in in but also pushes away. Perhaps you’re running a campaign that you thought was completely geared towards men 18-24 years old, but it turns out to be that you were off the mark and you’re attracting a completely different audience and sending your target the other way. Getting an understanding of who is liking and disliking a page will allow marketers to better test and plan their campaigns instead of simply just pointing towards their fan count and saying ” Look! Our engagement numbers/fans are up!”.

Quantitative Analysis Tools:  Truthfully, having to read through posts for a month and trying to create an actionable insight is pretty painful. Facebook’s interface for exploring comments makes it very difficult to be able to express the  consumer voice on the page. There are a few social listening firms that do qualitative visualizations really well. Facebook should really tap into this type of technology so  that Marketers can understand the voices of their customers vs. getting just a sample approximation.

Longitudinal post engagement analysis: For those who have been in social long enough, you may remember that Facebook delivered two types of engagement reporting. In what I’ll call Facebook insights v.1  you can get a pull of total Likes, Comments and Shares from all content created since the page was started over a certain time period. For Facebook Insights v.2 (current)  now you only see what engagement content posted during your chosen reporting period has received. I think it would be great to be  able to see a v.3  where you can understand the level of engagement a post receives over a period of time .

What I mean by this is perhaps content was really relevant to an audience and users continued to comment on a post for an extended period of time. On the other hand maybe a post was only sort of interesting and received a spike of interest in the first day or two and then really tapered off to nearly zero. Having just an aggregate number doesn’t really tell me how my fans engaged with content and what sort of impact this content had on the community at large. Additionally in terms of an overall content strategy it lets you understand if you’re making effective use of all of your content assets at hand.

BONUS Benefit!  Outside of measurement something like this  would also be a great tool for community managers as it allows them to see where potential issues or opportunities to engage are happening at any one time.

With the right technology in place I strongly believe that all of these potential features (and more!) fall within the realm of possibility. Facebook gathers a lot of user data and while it is important for privacy reasons to keep a tight control on it, freeing up some of the larger aggregate data would allow marketers to better target and manage their page rather than see their fans come and go based on their perceived benefit from the page.


Is there anything that you would add to improve Facebook insights? Or is it a matter of there is some data on the platform already that is completely irrelevant? Feel free to share your thoughts in the comments below!

Mar 13

Thinking About Network Efficiency

Lately a topic that’s been on my mind is the idea of network efficiency. This considers how a topic can hit a group of networked individuals so that it effectively gets spread throughout its network.  To think about this topic another way, how many people  did the Harlem Shake need to hit for it to go ‘viral’ instead of just falling into obscurity like many other videos.  Could a small subset of people effectively have enough reach and traction to reach a few hundred thousand others?

An Intro To Networks…

Something to keep in mind about networks (social networks, interpersonal networks and the like) is that there are two key components , nodes and edges. A node is an individual entity in a network ( ie: a person) and an edge is the different connections between the nodes. Now obviously, certain nodes have more edges, this connects them to many more nodes. Depending on the connections of the nodes, some have similar connections or some that are not similar at all. Why is knowing this useful? Information shared by a node with many edges spreads messaging more effectively than one with just a few. Also, two unrelated but highly connected nodes can share messaging with  a larger reach and less overlap compared to two that are similarly matched. This means a higher rate of separate connections= more reach= more effective messaging spread. (To learn a bit more about network graphs have a look here)


Here is a look at my Facebook social network. The circles would be all of the nodes and the lines between them would be the edges.

Here is a look at my Facebook social network displayed through a program called Gephi. The circles in this picture are my individual friends (or nodes) and the lines between them displays how they are connected ( the edges)


Making Something the Next Big Conversation Topic

I could go into a bit more depth with this topic but I want to keep this post pretty short. At the end of the day, the idea of nodes and edges in a social or interpersonal network  sense can be related back to how quickly and effectively a message is spread. Imagine if we could know which people in a network are needed to best get a message across. Instead of spraying a group with your message you could get it to the right people and have it evenly distributed throughout.Knowing what efforts are needed to make your content reach your target group rather than become  some little known code on the web can ensure increased success and better use of resources.

Mar 13

On Settling Into Somewhere New

After being without my family in Toronto and now Montreal I’ve learned that that there are certain places and activities that make living somewhere more comfortable.

I’ve now been 8 months in Montreal and  having a week off  has made me realize that there are certain things that make me more comfortable living out here. Recently my biggest discovery is that knowing the location of a good book store is important to me. Back in Toronto it was Swipe Books and now here in Montreal its Drawn & Quarterly. Having good quality books available to big creature comfort to me…. yup I’m a nerd.

There are other things that give me comfort. Whenever I’ve needed to think I find myself going to the Vieux Port/ Old Montreal to check out the old cobbled streets or have a view of the St. Lawrence seaway. I  also have two bakeries that I tend to frequent and a Thai fast food place I go to when I don’t feel like cooking. In terms of person to person contact I’ve also discovered the Montreal New In Town Meetup where I can go and grab beers with people who are in a similar spot to where I am.

Rather than just living in a place, I think connectedness is important and that’s where knowing things are comes into play. We aren’t just people who go from home to work and then home again. Having a feeling of familiarity and comfort in your surroundings helps with your state of mind.

What is important (for me at least) to do when you live in a new place?

  • Explore your neighbourhood. Knowing what is around in your immediate vicinity makes you feel more comfortable and as a side benefit can give you places to check out later for activities.
  • Find places relevant to your interests. Not having access to activities that you previously had can be a bit of a downer. Find a comfy coffee shop or seek out a place that provides access to the activities/classes that you’ve enjoyed. This provides you with a feeling of continuity.
  • Find a group of like minded people. Going to a place alone can be tough. Having a group of people that you can talk to and share experiences with makes things a bit easier and can relieve feelings of frustration and isolation.

So while I’ve been unpacked for a few months in Montreal I’m still sort of still settling in and understanding my surroundings. In another 8 months I’ll have to see where things go!



Jan 13

Old Becoming New Again: Getting an Old School Shave

If something is new, more technologically advanced or simply has more moving parts we tend to generally think that something is better. Simple means that someone hasn’t been thinking about something hard enough about making it better right?  Well I’ve learned in at least the case of shaving that this isn’t necessarily true.

First thing out of the way, I am one hairy dude and my hair tends to sprout up like weeds. If you were to do a poll of men to ask how often they shave I’m sure you’ll generally hear that they shave every two to three days. Well for me it’s almost every day with the exception of weekends and holidays where I tend to give my face a rest and just generally look like a hobo otherwise.

The downside I used to experience with growing in a thick mane is that multi blade razors would clog up  and become ineffective. Of course this then means that you’re constantly replenishing your razor supply.  This changed about a year and a half ago when a fellow co-worker of mine suggested I try out shaving with a safety razor (not so much like safety scissors) of which afterwards I was hooked!

The Experience:

After getting the metal handle ( a Merkur razor) and a 20 pack of blades I was set. It was a bit of a bigger upfront cost ( about $60)  but it felt really good having the metal handle in hand when I shaved. Outside of this my experience was generally the same as usual. I would wet my face, plaster on some shave gel or foam and then begin gliding the handle across my face.

In general, a clean blade netted me a really smooth experience. It feels like having the single blade results in a more accurate shave with less need to return to the same spot. With my face unfortunately nicks and cuts still occur but they’ve been less painful and much less unsightly which is a plus. All in all its an enjoyable experience.

Getting Sucked In:

Learning recently that a buddy of mine straight blade shaves as well has gotten my thinking of exploring my other options in shaving. Recently I’ve picked up a shave brush and soap package and have been experiencing a different way in how my face gets prepped and lathered up. Something about getting myself an antique razor handle also draws me in. Seeing the old pop open razors has a bit of an allure for some reason.

For something that men do fairly often in life, shaving is something that has become very casual and routine. What was once a social experience of going to the barber and getting a hot shave, has now become more of a commodity. For myself, noticing the small community of those who invest more into their regular shave I’ve started noting new products and to potentially add  to make my daily morning routine even better.Getting the parts I need may not be as easy as going around to the corner store but I’d consider that another part of the overall experience.




Sep 12

How The New Myspace is *Potentially* Getting Social Engagement Right

A brief preview of the newest iteration of Myspace went up up with a bit of fan fare this week but perhaps not a lot of sustained interest. The upcoming re-launch of the site was announced with a completely new interface and what seems to be a massive amount of music options. What I was sort of surprised about was how little people seemed to pay attention to the corporate social engagement portion of the site which is an aspect that this site could potentially have all other sites beaten in.

What am I talking about?

Displayed in a brief preview video were two aspects of what look to be actions from fan pages (in this case Justin Timberlake’s) that caused a few neurons to spark when I spotted them.

The first was a fan source map showing not only where fans were located but attributed a  fan joining your page  to another fan. This is huge in the sense that you can understand how your community forms and who are essentially the taste makers or ‘influencers’ in your community. By doing some digging you can understand how a fan is sending their friends your way and what makes them trustworthy in that others will follow his lead and join the page. In general by having this information you are understanding why people are joining your page and as a result can deliver better value to these fans.

What also caught my interest in this video was the option for the page owner to be able to reach out to “top fans’. Considering what was discussed earlier and using the idea of influencer marketing this could  allow organizations to better spread the word not only online but offline.

So for an example  say a new product is launching. With the option of contacting your ‘top fans’ you can share this information allowing them to spread the word  within their social spheres. Not only are you rewarding your top supporters by giving them exclusive information but you now have a trusted source who will be likely to pass on this information with others. You’re not spending thousands on an ad spend to get the word out, instead you have someone embedded within the community you want to target that will happily spread the word.

Are Facebook and Twitter Falling Behind?

Based off of this video my opinion is that Myspace has taken a direction that understands communications and social better than its competitors. What I mean in this is that Myspace has taken a direction that focuses on the actions of individual users and their contribution to building communities and spreading messages vs. Facebook/Twitter that have gotten too focused on pushing messaging out to as many eyeballs as possible.  On one side there is focus on who your messaging is getting out to and on the other a largely pay and spray approach is being taken.

Sep 12

A Personal Challenge: Ad Planning School Assignment

Well its been a while since I’ve written in here.  A lot has changed since my last post. Over the last 3 month’s I’ve  moved to Montreal to test my wits  (and language skills!) at Edelman Digital as a Senior Analyst reporting into their New York Office. Getting used to a new location/job…. I think that is reason enough for a bit of a blogging hiatus!

Anyways,on to the meat of this blog post. Since starting in the Marketing/Advertising/Digital (or however you would call it)  industry I guess you could say I  haven’t had very much interest in the mechanics of the different social media channels but was more so interested in how  people react to actions on these channels and simply why people do the things they do on the net. After reading “Truth,Lies & Advertising: The Art Of Account Planning” I hit a bit of a ‘AH HA!” moment in so that to do well in marketing you need to have an understanding of what your customers do and why they do it. Particularly in Digital this is a combination of understanding online conversations and actions along with  your audiences offline actions and feelings. Through reading the book I came to an understanding that while my reporting  on the numbers side of agregate online actions was strong,  to truly be good at what I’m doing I need to get a better understanding of the whole picture.

Stumbling quite randomly on a tweet from @Robertc1970 I came across the “A[P]SOTW Assignment” which peaked my interest in so much that its a challenge to look at the whole picture of online and offline consumer interaction. The intro to the assignment is found below and the full spec can be found at Rob Campbells site. 


So in general why am I posting this here? Well I figure by blogging about my process and research I can piece together my thought process. Also simply by making my process public it forces me to go through with this, rather than say I’ll do it and push it off.

Anyways, wish me luck with this! I think my big challenge will be balancing time as perhaps I already have a lot on my plate as it is ( new city, new language, new job… yikes!)  I really want to give this a go as even though I probably don’t stand a chance at winning this it will be a good exercise in stretching my brain a bit.


Jun 12

The Next Big Tech Battle: It’s Not About Single Devices Anymore

This week Google had its big I/O conference where it announced many of the new products it has coming out in the next few months. Two of the big surprises of the conference were the Nexus 7 ( a 7 inch tablet) and the  Nexus Q ( a cloud powered television media system). Most puzzling is that these products don’t particularly add anything to the Google’s core competency which is selling advertising but digging deeper you’ll see that this is part of a larger play to stay in the game against two other big rivals.

Android, IOS and soon Windows 8. What do they all have in common? Initially they were all platforms (or in Microsoft’s case based off a platform) for mobile phones that have since expanded into tablets and now television set top boxes. All three firms are making pretty heavy investments into products that most likely do not have high profit margins.  But in addition to this, products along all three of these company sets will be connected to a central content system where users can buy apps, video, audio and image (books/magazines) based content.

Ah ha! So essentially what the plan for these three is owning the media distribution system and making profit off of any royalties generated from the sale of media content. Where previously the war was over which computer operating system a person used or their search engine now the fight is over where they consume their content from. With content moving away from traditional sources such as cable providers, book stores and news-stands, being able to own an ecosystem that allows people to purchase media where ever they are is a big move with potential for a lot of profit.

Where will things go from here? So far Apple has the strongest foothold with iTunes entrenched as the first to launch. Can Microsoft and Google catch up being later into the game? Absolutely! Microsoft has the advantage of being a leader in the console gaming market which places them in a strong competitive position to Apple TV. As for Google, they are currently # 2 in the smartphone market using an open system where as long as it’s an Android powered device it will work with Google’s content distribution systems.  The big marketing move for all these players will be to get consumers to purchase into their whole line of products making them the primary content provider.

It will definitely be interesting to see where things go for these three firms and how competitive factors will come into play. Will cable providers and the big studios choose sides like in the fight for Blu-Ray vs. HD-DVD? Or will they  move to limit things all together? What will happen to single device manufacturing firms such as RIM ? With the technology industry moving so fast the next year or two will be ones to watch to see where the cards land.

May 12

There is No Magic Bullet in Social Media Measurement

Recently  a post from Avanish Kaushik was put before me for consideration. The post was “Best Social Media Metrics: Conversation, Amplification, Applause, Economic Value” and in reading it I personally found some flaws with his overall approach which I wanted to address.

Measurement Isn’t So Simple:

Knowing how complex websites and search are, Avinash should know that good measurement cannot be simplified into a few key metrics across everything and everyone. Despite this he tries to do just that with his proposed 4 metrics for measuring social media.

Different companies have different goals for what they want to achieve in Social media and assigning metrics that don’t take this into account can potentially side track these initiatives. For example in the case of say Avinash’s Conversation rate  it doesn’t speak to the quality of the conversations that a firm may want to see or the  overall goal of the content they put out. With such a focused scope these metrics don’t  take into account the many other variables that can be tracked on corporate social media channels.

The size of the fan base does matter:

Another downside I see with the type of measures that Avinash suggests is that it does not take into account the audience that the company taps into. Without taking into account a firm’s fan base an even grounding is not created to ensure a comparable measure across fan sizes. Instead what more often than not will happen is that these measures will present  a constantly increasing ratio because a larger fan base often results in more engagement.

Outside of large spikes when there is a fan base drive or large campaign, a measure that does not take fan base into account will most likely continually go up in a linear fashion. The downside of this  is that it’s not easy to discern what proportion of a fan base is engaged. As a result there is no clear way to determine whether an initiative was successful.

Measuring Awareness Still Matters:

Much like with television or radio advertising, simple exposure to social media messaging still has an effect on consumers whether they act on this immediately or if this changes their habits further down the road. While this makes sales attribution difficult, measuring how far your content reaches  is still important. Unfortunately for Avinsash’s framework it does not take content exposure into account. By knowing your content reach you are a better able to compare and contrast initiatives to see if there was a discernible change in a firms end outcome as a result of marketing messaging.

With developing field like Social Media to make broad statements like “the 4 best social media metrics” in my opinion is harmful. What this does is it has the potential to close people’s minds to other alternatives and  more meaningful approaches. That’s why beyond the fact of perhaps not being too public with my work (for confidentiality reasons of course…) I won’t say that one measure stands above all the rest.   Instead my answer will continually be that measures need to be formed based on the end user’s goals and what they are trying to better understand.