DOVE E-Promises – Crowdsourced Community of Therapy-by-Chocolate

Share Button

This past week—post-Valentines Day weekend—a (married) coworker discreetly deposited two chocolate wrappers into my trashcan when visiting my cube.  While most reasonable people would ball up the foil before flicking it up, these two happened to be left unwrapped.  I was intrigued, seeing messages on the inside:

DOVE E-Promise 1DOVE E-Promise 2

I was curious what the source of the messages were.  Was this Maya Angelou?  E. L. James?  Shakespeare?  Of what source might other intellectual treasures behold the seeker of such wisdom?

After a quick search, I happened to stumble across what was the source data for Dove’s website, that was linked to the main interactive page for all of the Dove E-Promises messages displayed their wrappers.  What was fun about this discovery was that included in this data sheet was also a shortened name, as well as the city that the contributors had apparently submitted these messages from.  Armed with user data, I was excited to see what I could learn from this chocolate community.

Fast-Facts of DOVE E-Promises Campaign

1) 213 chocolate lovers responded to their call.

I have yet to find the original plea for responses by Dove, as there weren’t any relevant messages on either of Dove’s Facebook pages as far back as November 2014, as of mid-August their dedicated Dove Pinterest board already contained these messages, and while I didn’t find anything on their Twitter account either, I couldn’t imagine that a tweet would get that kind of response.

2) 28 states were represented.

While this state count is of unique users, one particular woman in Detroit contributed one full third of responses, a total of 78 messages!  I take it that she is a fan.

3) The top categories of messages were Inspiration, Indulgence, and Attitude.

DOVE E-Promise Contributor Themes

No surprise, chocolate lovers seek inspiration and a moment of indulgence.

There is no surprise that these chocolate lovers would reinforce their supportive community with sought-out inspiration and justified indulgence.  Still, what intrigues me the most is pairing the categories and messages to the regions from where they originated.  Do New Yorkers have different methods of chocolate therapy than their South Carolina cohorts?

What inferences can you deduce about women who love chocolate?  Play with the map, then leave a comment below with any thoughts you have.

Coke Names: For Those Who Drink Young?

Share Button

Earlier this summer, Coca Cola has implemented a recent campaign with 250 names on the labels of their 20-oz bottles. According to their website, the Coca Cola company claims “The campaign, which concludes Aug. 30, features 250 of the most popular names among teens and Millennials on 20-oz. bottles.” In using their name finding tool, my nickname “Jay” was not included in the list, which made sense since I had recently learned on that my name had apparently peaked in usage around the 1960s (though I’m not 50 years old… my legal name John is included, however). This aroused my curiosity, in wondering just how exclusive their name choice was to Millennials, or if their targeting had other intent.

Coke names johnPerhaps for intuitive reasons, the names that Coke chose didn’t include Thelma nor Louise (both peaked usage in the 1910s, would be around 100 years old), however as a Millennial myself I didn’t grow up knowing a Bobby, and I don’t know of any young girls running around with the name Cathy, both of which they did include in their list. How much inclusiveness did Coke decide to have in choosing names for their selective list? Are they only looking for hip young consumers (no bias), or do they actually include something for everyone?

After doing a bit of plugging-and-chugging on this baby name website, I extracted the peak decades for each of the 250 names that Coke included in their list. I added up the frequency of use, and here are the ages that Coke targeted:

coke names age frequency So even though Coke claims to have included popular Millennial names, it seems they have also been inclusive enough to include names of nearly everyone to some degree. Perhaps more specifically, I think that this curve is what I would expect of the ages of people who would drink Coca Cola. However I don’t think that 10% of Coke drinkers are infants, but my hypothesis is that they included some newly-popular name for parents who would want to have a historical collectable for their kids, having their love of Coca Cola forever sealed on a shelf somewhere in their basement or in dad’s man cave.

What would be even more fun to investigate, if possible, would be use of ethnicities in the names chosen, or by demographics in specific North American cities. Apparently this campaign was first launched in Australia in 2011, was in Europe last summer, and I wonder to what degree consumption by age is different in other regions of the world.

Did your name make the list? And are you older or younger than most others with your name? I have included all 250 names here linked with the page on that I used to extract the most commonly used decade. Check out your name’s usage through the years, and see if you may come to a different conclusion than I. Enjoy! And enjoy Coca Cola.

1. Aaron Male 1980
2. Abby Female 2005
3. Adam Male 1980
4. Alejandra Female 1990
5. Alejandro Male 2005
6. Alex Male 1990
7. Ali Female 1970
8. Alicia Female 1980
9. Alisha Female 1980
10. Allie Female 2010
11. Alyssa Female 1990
12. Amanda Female 1980
13. Amber Female 1980
14. Amir Male 2010
15. Amy Female 1970
16. Ana Female 1990
17. Andrea Female 1980
18. Andres Male 2010
19. Andrew Male 1980
20. Angel Female 1970
21. Angela Female 1970
22. Anna Female 2000
23. April Female 1970
24. Ashley Female 1980
25. Asia Female 1990
26. Austin Male 1990
27. Becca Female never a top-1000 name (you’re special)
28. Ben Male 1880
29. Beth Female 1960
30. Blake Male 1990
31. Bobby Male 1930
32. Brad Male 1970
33. Brandon Male 1990
34. Brandy Female 1970
35. Bree Female 1960
36. Brett Male 1980
37. Brian Male 1970
38. Brittany Female 1990
39. Brittney Female 1990
40. Brooke Female 1990
41. Bryan Male 1980
42. Caitlin Female 1990
43. Cameron Male 2010
44. Carlos Male 2010
45. Caroline Female 1990
46. Carrie Female 1970
47. Casey Female 1980
48. Cassandra Female 1990
49. Cathy Female 1950
50. Chad Male 1970
51. Charles Male 1930
52. Chase Male 2010
53. Chelsea Female 1990
54. Chris Male 1960
55. Cierra Female 1990
56. Claudia Female 1990
57. Cody Male 1990
58. Connor Male 2010
59. Corey Male 1980
60. Cory Male 1980
61. Courtney Female 1990
62. Cristina Female 1980
63. Crystal Female 1980
64. Cynthia Female 1950
65. Dana Female 1970
66. Daniel Male 1980
67. Danielle Female 1980
68. Darius Male 1990
69. David Male 1950
70. Deb Female 1950
71. Derek Male 1980
72. Devin Male 1990
73. Dominique Female 1990
74. Dustin Male 1980
75. Dylan Male 2010
76. Edgar Male 1990
77. Eduardo Male 2010
78. Edward Male 1880
79. Emily Female 1990
80. Emma Female 1990
81. Eric Male 1970
82. Erica Female 1980
83. Erika Female 1980
84. Erin Female 1980
85. Ethan Male 2010
86. Evan Male 2010
87. Francisco Male 1990
88. Gabe Male 1880
89. Gabriela Female 2010
90. Garrett Male 1990
91. George Male 1880
92. Greg Male 1960
93. Haley Female 1990
94. Hannah Female 1990
95. Hassan Male 2010
96. Heather Female 1970
97. Hector Male 2010
98. Holly Female 1970
99. Hunter Male 2010
100. Ian Male 2010
101. Ibrahim Male 2010
102. Jack Male 1920
103. Jackie Female 1960
104. Jacob Male 1990
105. Jake Male 2000
106. Jamal Male 1990
107. James Male 1940
108. Jamie Female 1980
109. Janelle Female 1980
110. Jared Male 1980
111. Jasmine Female 1990
112. Jason Male 1970
113. Javier Male 2010
114. Jeff Male 1960
115. Jenna Female 2000
116. Jennifer Female 1970
117. Jeremy Male 1970
118. Jess Female never a top-1000 name (you’re special)
119. Jesse Male 1980
120. Jill Female 1960
121. Joe Male 1930
122. Joel Male 1980
123. John Male 1940
124. Jon Male 1960
125. Jordan Female 1990
126. Jorge Male 2000
127. Jose Male never a top-1000 name (you’re special)
128. Josh Male 1980
129. JR Male never a top-1000 name (you’re special)
130. Juan Male 2000
131. Julia Female 2000
132. Julie Female 1960
133. Justin Male 1980
134. Kara Female 1980
135. Karen Female 1950
136. Karina Female 1990
137. Kate Female 1990
138. Kathy Female 1950
139. Katie Female 1980
140. Kayla Female 1990
141. Keisha Female 1970
142. Keith Male 1960
143. Kelly Male 1960
144. Kelsey Female 1990
145. Ken Male 1960
146. Kendra Female 1980
147. Kevin Male 1960
148. Kim Female 1960
149. Kris Male 1970
150. Kyle Male 1990
151. Latasha Female 1980
152. Latoya Female 1980
153. Laura Female 1960
154. Lauren Female 1980
155. Leah Female 2000
156. Lee Male 1930
157. Leslie Female 1960
158. Linda Female 1940
159. Linds Male never a top-1000 name (you’re special)
160. Lisa Female 1960
161. Liz Female 1960
162. Logan Male 2010
163. Luis Male 2010
164. Luke Male 2010
165. Maddy Female never a top-1000 name (you’re special)
166. Makayla Female 2000
167. Malcolm Male 1920
168. Manuel Male 1920
169. Marcus Male 1980
170. Maria Female 1960
171. Mariam Female 2000
172. Marissa Female 1990
173. Mark Male 1960
174. Mary Female 1920
175. Matt Male 1960
176. Maurice Male 1910
177. Mayra Female 1980
178. Meg Female 1960
179. Mel Male 1940
180. Michael Male 1960
181. Michelle Female 1970
182. Miguel Male 2000
183. Mike Male 1960
184. Miranda Female 1990
185. Mitch Male 1960
186. Monica Female 1970
187. Morgan Male 1990
188. Nadia Female 2000
189. Natalie Female 2000
190. Natasha Female 1980
191. Nate Male never a top-1000 name (you’re special)
192. Nick Male 1910
193. Nicole Female 1980
194. Noor Male 2000
195. Olivia Female 2010
196. Omar Male never a top-1000 name (you’re special)
197. Oscar Male 1890
198. Paige Female 1990
199. Pat Female 1930
200. Paul Male 1960
201. Peter Male 1950
202. Phil Male 1940
203. Rachel Female 1980
204. Ray Male 1920
205. Rebecca Female 1970
206. Ricardo Male 1990
207. Rick Male 1950
208. Rob Male 1960
209. Rosa Female 1930
210. Ryan Male 1980
211. Sam Female never a top-1000 name (you’re special)
212. Sandy Female 1960
213. Sara Female 1980
214. Sarah Female 1970
215. Scott Male 1960
216. Sean Male 1980
217. Seth Male 2000
218. Shane Male 1970
219. Shannon Female 1970
220. Shawn Male 1970
221. Shawna Female 1970
222. Shayla Female 2010
223. Shelby Female 1990
224. Spencer Male 1990
225. Stacy Female 1970
226. Stephanie Female 1980
227. Steve Male 1960
228. Sue Female 1940
229. Sydney Female 2000
230. Syed Female 1970
231. Tamika Female 1970
232. Tara Female 1990
233. Taylor Female 1990
234. Tiara Female 1990
235. Tiffany Female 1980
236. Tim Male 1960
237. Tom Male 1940
238. Tony Male 1960
239. Travis Male 1980
240. Trevor Male 1990
241. Trey Male 2000
242. Tyler Male 1990
243. Vanessa Female 1980
244. Veronica Female 1970
245. Vic Male 1960
246. Wes Male 1960
247. Whitney Female 1980
248. Will Male 1920
249. Yesenia Female 1990
250. Zach Male never a top-1000 name (you’re special)

I am in no way endorsed by the Coca Cola company. All thoughts and opinions are my own.

Is Product Placement in a Digital Age Bad?

Share Button

ofcom product placement PFor a Consumer Behavior course I am taking over the summer, in our forum discussions we were prompted with a UK regulation from 2010 concerning product placement in British programming. Put into effect in February 2011, the UK regulator Ofcom now permits product placement in programming only with the prominent display of their designated “P” (alliteration unintended) at the beginning of each segment, as a heads up to viewers that there will be product placement at some point during the show.

As a class, we were challenged with discussing why this was an appropriate time for this new regulation to occur, and whether or not it was justified. Working in Price Per Click advertising for a little while, and an active consumer of online content with all of its delights and woes, I feel elated at seeing trends of how online advertising can become more and more precise in how with available data, content can become more and more targeted for a relevant audience. Of course this comes with privacy concerns from borderline intrusive messaging, but as an optimist I think that it is a very bright time both for marketers to share their content and messages, and for consumers to be open to relevant products in their lives.

Is Product Placement Really a Desire of the Public?

From the verbiage in The Independent, their language suggests that lobbying from advertisers pressured the government to allow for product placement for the first time.  This suggests that the prevailing British preference was to not have outside brand messages interfere with the programming that they are watching, and that they see that their goods and services as removed from the experiences they watch in their shows.  This would be the only point of concern, but I think that this regulatory change was implemented in a historic time that makes this concern irrelevant.

For values towards brands outside of the United States, this situation reminded me of a passage I read (written in 1989) about neighborhood businesses called ‘Third Places,’ much like a local Starbucks or neighborhood bar.  In discussing the French culture of cafés, Ray Oldenburg (The Great Good Place) points out that in their concern for the invasion of market forces, they don’t even name many of their local businesses. He more-eloquently describes their dynamic:

“Naming something is the first step toward advertising it, and the French have always been admirably suspicious of advertising—only in recent years have they permitted it on television.  But the major reason for not naming a bistro is simply that the neighborhood café does not need a name.  Its patron has filled a local niche and is content with his small, steady business.”

For the sake of contrast, I would argue that television is fundamentally different.  Television programming spans distance of neighborhoods, and of course now with the internet, we are no longer limited to media catering to the local tastes and desires in the city of our residence.  We are now able to pick and choose the cultures that we desire to reinforce or be influenced by, so theoretically every channel and programming block will only become more and more relevant to particular audiences.

As part of our course outlining literature on self perception through consumption, “symbolic self-completion” theory and the concept of the “extended self” both suggest that “we are what we consume.”  Since I am not very familiar with British culture, I am unable to make assessments on what types of products Britons use and prefer, but given the backlash of at least The Independent, it seems as though the British (at least in 2011) preferred to live the way they did and use the products they already used.  However, assuming that the nature of the networked world will influence consumer behavior in the UK as it has in the United States, these consumers will understand that new possibilities extend past their own local customs and products, and that to a certain extent they already participate in ‘cultural selection’.  Also, assuming that marketers in Britain are conscious of their target markets, then they will find ways to interject their messages only in the places where they will effectively influence those who will be likely to benefit from that messaging and use of their product.  This should not make product placement much less an issue of unwanted obtrusion, and sure enough it seems most of the critical articles on product placement center only around 2011 when this change first occurred.

As one last point of potential contention, there may also be concerns that product placement would start invading every area of life.  From the same article in The Independent, this interjection of product placement is restricted only to programs other than “children’s programmes, news and current affairs, consumer advice and religious programmes made for UK audiences.”  Each of these prohibited spheres I think are reasonable ‘socially sacred‘ mediums where commercial interests are not appropriate and should not influence civic life, and I think that this was a very tactful decision from Ofcom.

‘Product Placement’ Can Now be a Celebrated Thing

So justifying of product placement aside, I think that on the contrary the cultural lifestyles promoted in media is actually welcoming of product placement, just not in the way we traditionally think.  With nearly every human activity revolving around using products that others manufacture, it is arguable that every form of media is some type of advocacy for a certain lifestyle with certain products.  Whether this is overt or covert is one thing, but the line of what products are intentionally promoted and which are accidentally featured is only going to be more and more blurred.


Top Gear, from

More recently in September of 2013, the BBC show Top Gear had inadvertently crossed this line.  As a show that praises the craft and even art of various automobile manufacturers, their use of man-made products is very apparent.  In one episode, brands of seats and harnesses were unintentionally displayed, and following from Ofcom’s 2011 regulation they were penalized for their failure to display the product placement P. In the producer’s words “We have never given them prominence over the years because we have different manufacturers for Star in a Reasonably Priced car.  This slipped under the radar.”  This suggests that his use of brands is so engrained, that their use wasn’t even thought of as product placement in his eyes.

More generally, how often do sophisticated crime/law/government TV shows feature a curiously slim aluminum-and-glass laptop, sans a glowing Apple in the back?  Or banner ads with people using a smartphone with a cover, that has a hole for the camera lens distinctively in the far top-left corner?  To identify with their customers, marketers acknowledge that Apple holds a 41.4% marketshare on smartphones in the US.  And how often have you seen younger models in ads wearing a certain type of plain, dual-paneled canvas shoe (TOMS)?  Or a professional carrying a white coffee cup with green highlights (Starbucks)?  These products are simply part of our culture’s lifestyle, and in connecting with consumers, this is a great chance for marketers to help reinforce what their brand’s culture stands for, and how it can fit in with what their consumers already do.  It is only when a brand is overly blatant about it, or it doesn’t match the environment that product placement seems off-putting and irrelevant, but the ‘invisible hand’ seems to ensure that brand use is done tactfully for the most part, not requiring legislation to prevent its abuse.

There has recently been contention around advertising on Facebook, since it is now grown to such prominence to even be referred to as a ‘social utility.’  But as advertising can get more and more targeted, we will have less and less ads shown to us about mortgage refinancing etc., but will have more relevant ads shown to us.  I think YouTube has already proven that targeting is possible, and most video ads that are presented to me on YouTube I have found to be quite relevant… and in some cases even enjoyable!

As long as we as marketers can find ways to take advantage of Big Data becoming more and more available to us, the more effective we can be… and the less annoying we will be to other consumers by taking them out of our line of fire.

Ideation Occurs In-Person, Make It Your Mission

Share Button

Living a couple blocks away from a downtown, I have begun making strides to take advantage of the surrounding amenities of professional talks, restaurants, and other cultural events that are now all within walking distance. I had a great evening at a Meetup event, where we helped each other move some projects along, and with two other guys we brainstormed and chiseled away at an idea until we conceived not only the concept for an application but the monetization strategy as well!  This back-and-forth ideation is an experience that I’ve grown to cherish, realizing that such high-level discussions fully utilizing college-educated knowledge and sensibilities are not possible in just any arena.

The other of the two guys was an intern from the University of Waterloo, who as a first-generation Indian immigrant.  As we began discussing a team for this new app idea, this ‘director’ from Chicago revealed that he was a one-man shop.  For most of his work, he simply does the design and outsources the rest, as he justifies that it is virtually equivalent work.  The student–of course perhaps being biased, studying computer science–disagreed, responding “I think that the top talent will always be here in the United States.”  “Yeah, maybe the top .001% of talent is here, but for half the price I can get someone in Asia to do it that is still 90% as good.”  What seemed most telling to me however, was that nonetheless he admitted that the concept that we had realized in that 45 minute discussion was better than what he could come up with in four months on his own.

Weird Al Mission Statement synergy ideationPerhaps our great bard of modernity is really onto something, as sarcastic as he is in his new video on company mission statements.  As we are all social creatures, the beauty of being able to seek out others that are working in similar circumstances is that we can shed light on what they don’t know, and in some case we can work together.  Networking soon will bring seamless integration.  We’ll leverage our core competency in order to holistically administrate exceptional… synergy.  (oh just go watch the video, it’s great.)

Another Thought on In-Person Ideation: The Importance of Place

A little while ago, I finished Richard Florida’s book “Who’s Your City?” as a follow-up to his previous book on the emerging ‘creative class.’ His ideas have been bouncing around in my head, as in the past year I have had the opportunity to visit some of the best cities he has highlighted of Omaha, Des Moines, Denver, Boulder, San Francisco, and my current city of Ann Arbor, MI. And seeing a good handful of satellite cities and suburbs in-between, I have had an interesting journey talking with different people and experiencing their different lifestyles, which has been both inspiring yet startling how wide of a spread there is between the cultural engagement of different cities. Over the past year, this passage has become more and more visceral to me:

“To be sure, globalization is powerful.  Places that never had a chance to participate in the world economy are seeing some action.  But not all of them are able to participate and benefit equally. Innovation and economic resources remain highly concentrated.  As a result, the really significant locations in the world economy remain limited in number.


In one analysis, plotted on the y axis is the locations of the most-sited scientists in research.

The reality is that global-ization has two sides.  The first and more obvious one is the geographic spread of routine economic functions such as simple manufacturing or service work (for example, making or answering telephone calls).  The second, less obvious side to globalization is the tendency for higher-level economic activities such as innovation, design, finance, and media to cluster in a relatively small number of locations.”

These two book on the role of place in our new economy have had a profound impact on me, and I am very grateful as it turns out to have selected a university that is in a city near one of these clusters, these “creative centers.”  Unburdened by any other concern, we were able to all experience a bit of self-actualization that night in an exercise of dreaming up what could be.

And as I continue to learn the value of these interactions, I am more and more motivated to carry out my own personal mission statement that I had devised for NYE 2013: To encourage individual and institutional adoption of technologies and processes to minimize barriers of collaboration.

How to Tag A Google Analytics Campaign

Share Button

tagged google analytics campaign

Ready to get started measuring campaigns driving traffic to your site? For each link you put out—whether from email or social media properties—append your link with a query string using Google’s server syntax to identify up to 4 different content parameters of categorization, all of which can be tracked within Google Analytics.  You can find these reports under Acquisition > Campaigns.

What Does Tagging Enable Me To Do?

Think of tagging your campaigns as putting up turnstiles on paths in a park.  But not only do you get a count of how many people used that path, but you also put a stamp on the hand of everyone who comes through, so you can then see what attractions they visited, how long they spent in the park, and even if they bought ice cream.

Google Analytics can already tell you who your referrers are: whether they came from search, a social media platform, another website, or even directly.  But what about that one special announcement that you made?  Campaign tagging allows you to separate out traffic from specific sources, and see specific behavior that you wouldn’t have been able to observe before.

With this knowledge, you can evaluate landing page performance not in general, but from a specific set of ad copy.  You can track one Facebook link post versus just a picture, and even count conversions from various email campaigns.

So How Do I Tag My Google Analytics Campaign?

Google has a built-in URL builder worksheet that you can use to tag your campaigns, but for any large amount of links to be tagged they even recommend using a spreadsheet to create the same syntax. Well, I have built one already, and I’m willing to share! This past winter, I helped set up my first campaign for a not-for-profit’s conference, with over 120,000 site visits over the course of three months.

For our workflow, we used a collaborative Google Spreadsheet so that any member of the marketing team could tag their own URLs, whether it was the Marketing Manager for a big keynote announcement, or the Digital Communications Manager for a quick tweet. The sheet has five fields to enter text, including a URL and 4 content parameters. The last column simply concatenates each cell together–with the required character syntax in between–and we were able to quickly iterate tagging different messages for our campaign.

Click to view this free template that is publicly available, where you can copy the formulas and even the whole sheet if you’d like. Enjoy, and have fun splicing and dicing your data!

Google Analytics Campaign tagging Google Spreadsheet

Eyes On The Prize (No, Not To Maximize Shareholder Wealth)

Share Button

As a twenty-something trying to find his way in the world, I have been reading a book on work called “Every Good Endeavor,” by Timothy Keller.  The book talks a lot beyond the goals of individuals and building your career, and ultimately drives to the heart of why we’re organized to produce good: to serve the interests of a culture, and the sets of values surrounding that culture.  He warns, however:

“We should expect, then, that each culture’s emphases have some beneficial influences on work and yet at the same time harmfully distort it.”

When we drift away from what we are building and constructing–e.g. focusing simply on maximizing shareholder profits–we neglect the very value of what we are creating.


“For the love of money is the root of all evil.”

Think past the cliché for a brief moment, and think about the Great Recession on Wall Street.  Enron.  Watergate.  These were all efforts of individuals and organizations that were in their own right relatively successful, but over time as they started to push the boundaries of what they could extract from it personally, their schemes started to blow out of proportion because their constituents no longer were receiving the value that was once promised to them.

-Getting new Industry partners: why not simply show them where we’re at, and have them help accomplish our mission? Is it membership revenue?

Once you start heading towards the line, it’s hard to know when you cross it

Corruption is sneaky, because it’s a very subtle distortion of absolute truth.

So: Are You A Brand, Or Do You Sell Widgets?

Our post-industrial economy is moving past simply having the cheapest gadget or the most convenient location.  We are moving into an era of Seth Godin’s idea of tribes, where transparent values are what will connect companies’ products with their users.  This is the new value proposition.  “Here we are at the core, here’s how we’re similar, and here’s how we can help you come alive.”

KEEP VALUES (organizational as well as personal) IN VIEW AT ALL TIMES

Testing: Twitter Cards for Analytics (and Google Tag Manager)

Share Button

Now that I am taking website analytics for granted, my appetite for data is whetted.  I knew of YouTube analytics, so I thought surely Twitter would have some sort of analytics API.

Apparently the way Twitter measures inbound traffic is through Twitter Cards, which are activated every time someone clicks a tweet button and that pop-up-compose-tweet window appears.  As I thought about it, I’d seen this type of information graph before, and it was time to start learning more of these tools and uses of data APIs.

Also, I have implemented Google Tag Manager on this property, so don’t mind me tracking your external link clicks. :]

How to Make a Weekly Heatmap From Time Data

Share Button

Do YOU have timestamps of some series of events?  Are you looking for a better way to view them than A-Z sorting, glazing over a stack of hundreds of numbers all bleary-eyed? Well, why don’t you try a heatmap!

This is beyond a simple graph.  A heatmap allows for three dimensions of data to be viewed, and the coloring of a heatmap will allow for quick identification of trends and clusters in points of time.

Most likely, your data will be retrieved from some type of streaming feed.  In this case, our registration database returned the timestamps that the “Submit” button was clicked for a completed (and verified) registration for a conference.  In this use case, the three dimensions will be:

  • Day of the week
  • Hour of the day
  • Site visits per hour

You can put them on timestamps, days of the week, frequencies of the digits of pi, or any other time-based metric you can think of.

I have broken this down into 5 steps:

  1. Prep data into desired units of granularity
  2. Stage data for counting in an array
  3. Create the display fields with bins
  4. Add counting “=frequency” formulas
  5. Add conditional formatting to display heatmap zones

1) Prep data into desired units of granularity.

We’ll need to break up the time and date in order to sort them into their desired bins of “day of the week” and “hour of the day.”  Like a Riemann Sum (remember those?), we will break up an otherwise continuous stream into chunks that we can identify.  These formulas are based on 24 periods in a day, but you could modify these into any size that you desire.

Days of the week

For days of the week

Hours of the day formula

For hours of the day:

2) Stage data for counting in an array

Don’t worry, we’re not going to count these ourselves.  We just need to arrange the data into organized columns that we can point a formula to count for us!

Arrange data into columns by days of the week:

Column into array

3) Create the display fields with bins

Now, create a graphical representation of a week, plotting ‘days of the week’ and ‘hours of the day.’  I just made the table a couple columns over from the data, but you could plan wherever you’d like to put this and link it as desired.

We have already sorted our data by one dimension (or axis) already, days of the week.  Now, the counting and sorting criteria (or “bins”) that we will select will be the vertical axis, ‘hour of the day.’

Days and hours array

4) Add counting “=frequency” formulas

Now comes the fun part, adding the formulas to do the work.  The goal here is to look back at the data array of the timestamps, count how many of each hour there are, and then report that number back for each unit that we have in our table.  This can be a little tricky, and I got a lot of help for this method from Eugene O’Loughlin’s video on YouTube.

The syntax for this formula is as follows:

=frequency(array, bins)

  • array: the column of data we will reference, in this case ‘day of the week’
  • bins: the counting and sorting criteria, in the case ‘hour of the day’

We will A. add this formula in the top cell of each day of the week, and then B. apply this formula to the rest of the column.

A. Add “=frequency” formula to first cell (click the image for a larger view)

Click for a larger view





B. Apply to the whole column: select whole row, click into formula bar, then type Cmd + Enter (or Ctl + Shift + Enter for PC).  Watch here if you would like the visual.

Then rinse and repeat for each day of the week.

5) Add conditional formatting to display heatmap zones

You will probably have something like this so far.  I included Sums rows on each side to ensure validity of data adding correctly.

You will probably have something like this so far.

So now you have a lovely table of numbers, but wouldn’t it be nice if all that black and white actually meant something?  Conditional formatting allows for you to quickly identify outliers to your data.

Before I got started, I included rows of Sums on each side for quick data validity to ensure adding was done correctly.

Select the data array, then select a Conditional Formatting ‘Color Scale’ option you like.  With my 2011 version of Excel (Version 14.3.8, from Office 365), there were several templates available.  No need to reinvent the wheel here, we just need some color to highlight our data.

Heatmap - conditional formatting






Conclusions and Applications

Heatmap - final


So there you have it!  From this heatmap, we now have solid data to suggest when the best times to convert are (in our case, obtaining registrations for our conferences and events).  I applied this same technique to our pageviews, and the results ended up being vastly different from when people actually decided to finalize their registrations.

Like any tool, you must understand what limitations the data have.  Ask your own application question before building this… or if you are a —P type personality, just make it and see what conclusions you might come up with.  After all, you’re the creative analysis guru!

Blog Take 2

Share Button

Since creating this blog for the first time, I have upgraded to a better web server, a revamped personal website, and a much more sane frame of mind now that one of my most difficult semesters is over. And I also accidentally deleted what I had worked on so far.

Additionally, all the quirks I learned about web hosting and how to run this blog have been figured out, and having redone the process of re-setting this blog back up, I hope that you will appreciate its efficiency and organization! There is much more to come, and here is my list of what I desire to write on soon:


What I wouldn’t have learned if I WASN’T at Eastern
  • Strong bonds/connections/reliance on family is not weakness
  • When it all shakes down, it’s not about doing… it’s about being (with people, and with God)


An Entrepreneurship project–more conceptual than anything at this point


Ray Oldenburg’s “third places
Two papers for my Finance class:
  • Ethics, a trader getting into medical marijuana
  • International business


How does Twitter work, and what’s it good for?
Becoming a thought leader through Twitter and blogs