Big data has been a hot topic for years and affects organizations across every industry. But most organizations, especially in the non-profit world, don’t have the resources for data analysis in-house or the budget for fancy data tools.

The truth is that most organizations don’t need a major financial investment in data tools. You can adopt technology and big data strategies to make data-driven decisions using Microsoft Excel paired with intuitive data analysis.

Williams Whittle uses data visualization as a strategic planning tool for all our major clients. From uncovering correlations of media spend to brand awareness to predicting future actions based on seasonality, data visualization is an essential tool for every marketer.

How Do You Get Started?

The first thing you need to do is to define the problem you are trying to solve.

Here are some sample questions that we have answered using data visualization.

Why has ad recall increased or decreased in a specific time period with the same ad spend?

When and how frequently should we be in market with television next year based on what our competitors are doing?

Is there a correlation of the seasonality of our services state by state and how does paid and/or earned media impact usage?

Once you know what question you need answered, you need to collect all the Key Performance Indicator (KPI) data associated with it.

In the questions listed above, the data collected may be the following:

  • Advertising recall or aided and unaided awareness, likelihood to choose (year over year, quarterly as collected)
  • Competitor spending overall and by medium
  • Advertising spending (by media type, by month, year) for paid media
  • Earned media value
    • PSA
    • Google Grant
    • In-kind media bonus
    • Social media impressions and engagement (views, shares, followers, fans)
    • PR
  • Website visits (by month, year)
  • Community events
    • Brochures/materials distributed
    • Emails/surveys/pledges collected
    • Number of people reached (by event)
  • Donations or sales data (by month, by state/location)
  • Customer service phone calls, emails, social media questions

Next, all of data can be charted on a calendar for the specific time period you want to cover. The months of the year should be noted at the bottom, and the axis on either side will vary based on what you are trying to correlate.

For example, this chart correlates paid media with website visits for the Mattress Recycling Council campaign “Bye Bye Mattress”. It’s a mattress recycling program operating in California, Connecticut and Rhode Island. The program was established by law and is operated by Mattress Recycling Council, a nonprofit that uses a recycling fee collected on each mattress and box spring sold to implement its mattress recycling programs for the three states that have enacted laws. The life cycle of mattress recycling and the program itself, is dependent on mattress sales. Once a new mattress is purchased, the old one is recycled (sometimes immediately, sometimes not).

As you can see from the chart, there is an obvious correlation between increased website visits when we are in market with traditional media. The second wave of spending included community events in key markets in California, which also contributed to increases in web visits.

The spikes in customer service calls and emails in December 2016, March and April 2017 overlay well with the previous chart of website visits correlated with paid media. In November and December and March through May of 2017, we received the highest spike in customer service phone calls and emails. This is a direct reflection of the launch of broadcast television. The drops correspond to when broadcast television is off the air for this client.

In this chart, the Mattress Recycling Council wanted to focus on mattress collections by state (currently, there are only three states that mandate mattress recycling) and by month over a 1-1/2 year period. The introduction of broadcast TV, radio, print and outdoor in November 2016 created an increase in website visits in the winter of 2016. And our next flight was focused around the high period of mattress sales from President’s Day to Memorial Day, so the media started in February and built through April 2017.

In March and April of 2017 we saw large spikes in mattress collections in all three states, which leveled off in May and increased again in June, which is frequently correlated as a high time period for people to move and discard old mattresses.

You should also note data in the chart that may have affected the increases or decreases. One example that is relevant to this chart as noted above is the cycle of when (time of year) people move. 61.56% of all moves are from May through September, which adds another element of seasonality outside of mattress sales for this client.

Other examples of data to consider are things that may affect the success of a media plan, such as high competitive spending or other outlying factors. The election cycle in 2016 affected airtime as time slots in the fall and winter of 2016 were pre-empted by political ads. The political windows begin 45 days prior to primaries, and they usually affect spending by brands during these time periods. This also happened in Q1 and Q2 of 2017, when the GOP’s attempt to repeal Obamacare (i.e. the Affordable Care Act) impacted the TV market with a slew of national and local market commercials from PACs, SuperPacs, and other groups grappling for airtime, which created a lot of “noise” in the marketplace and made it harder for brands to get their message out.

In terms of presentation of the data, we have rediscovered an “old school” method using charts printed on transparency paper that overlay with each other so that different sets of data can be analyzed at the same time.

So how does Mattress Recycling Council use this data?  They have used it to support our media plan weighting and seasonality. They have also used it to staff their customer service centers and social media channels during periods when high call and email volume and social media engagement is anticipated. And they have used it to correlate recycling volume with similar industries such as solid waste.

You don’t need to spend $60,000 plus a year on fancy data visualization tools such as Tableau or Zoho. An accelerated knowledge of Microsoft Excel combined with a curious, explorative, and analytical mindset can create data visualizations that inform your clients of valuable trends and data correlations that will turn you into a data scientist.