Posted By: Mike Moore, Averetek /
When I joined Averetek in 2014, it was around the same time that Marketo announced the Marketo Institute, their initiative to take the data from all of the global users of the Marketo marketing automation platform and share it with the world to enable marketers to gain insights and understand best practices.
The move by Marketo inspired the Averetek team to develop our report, the State of Channel Marketing 2015. Our report analyzed the performance of more than 43,000 channel partner organizations and more than 158,000 users executing a variety of marketing tactics on our marketing automation platform in more than 150 countries.
This year, we’ve continued our work by developing the State of Inbound Channel Marketing 2016. The latest dataset was even bigger than last year with more than 59,000 channel partner organizations with 239,000 users utilizing the Averetek platform in 164 countries.
The process of going from tens of thousands of rows of data to a report with fourteen charts and a list of insights is time-consuming and complicated, but some tricks to pulling the data together can be used in your day to day work.
We track every action on the Averetek platform, from login to logout. By collecting all of this data, we’re able to analyze the platform use in many ways. For your work, I’d recommend thinking ahead to the dimensions you want to examine after the activity has taken place. If you don’t capture the data, you can’t report on it later. Think through all of the items you want to analyze later and be sure that your forms, systems, or other data capture vehicles will collect what you need.
Scrub the Data
To perform the analysis for our report, I spend around five hours scrubbing the data to make sure it’s ready for analysis. Scrubbing can involve removing bad data or test data. It can also involve normalizing the data that was entered in varied ways. As you gather the data, you want to analyze, plan to spend time scrubbing the data. As an example, it’s not uncommon to see United States, United States of America, USA, and U.S.A. and the same can happen with other countries. Taking the time to clean up these values will allow you to perform an accurate analysis later on.
Summarize and Compare
The workbook I use for the State of Inbound Channel Marketing has a tab with the source data, the clean data that I’ve scrubbed, and more than a dozen tabs with pivot tables and charts. Pivot tables in Microsoft Excel are the key to summarizing and analyzing any dataset. To be effective in your use of pivot tables, you need to understand how they work. Consider using the self-paced training on the Microsoft Office Training Center for some fundamental and advanced courses that will help you develop your skills.
Once you are proficient in pivot tables, you’ll want to group items when you need to consolidate data into logical groups. You can find an explanation of the group feature here. I often using grouping for things that naturally go together like dates (group by month, quarter, and year) and for less obvious things like pulling together similar items. In the case of the State of Inbound Channel Marketing, our various clients may call different downloadable resources by various names, so I manually group them into categories to allow for comparison.
The primary focus of our report is pulling data together from our user base to see what trends emerge. There is a risk in summarizing data. You can miss important details. As you build your pivot tables, I’d recommend taking some time to drill-down into the data to understand where the numbers are coming from.
For example, we always look at the population size of partner users on the Averetek platform and compare the year over year data. In 2015, there was a 22% increase in the size of the population. Drilling-down on the data, I was able to confirm the user growth, but I also noticed that there was a population who had been active in the prior year who didn’t use the platform in 2015. Another segment of partners used the platform in 2013, skipped 2014, and came back to use the platform in 2015. Having this kind of information enables our clients to target specific segments of partners with retention campaigns to keep them engaged and actively marketing.
In your work, putting together the pivot table and pivot chart views that tell the story of your business is important. But you also must have what I refer to as the “double-click” into that data ready to discuss. For example, if you’re presenting your partner marketing activity summarized by your geographic regions, your leaders will likely ask what campaign or activity is driving the results. Having answers to the double-click questions on the tip of your tongue will save you a lot of work gathering those insights after your executive meeting. Additionally, there are likely some good stories hidden in the drill-down data. Numbers are good, but your executives like stories. Try to find a story or two for every pivot table or pivot chart that will help provide evidence of the impact of your program.