This post is part of the Hero Conf Los Angeles Speaker Blog Series. Andrew Miller will join 50+ PPC experts sharing their paid search and social expertise at the World’s Largest All-PPC Event, April 18-20 in Los Angeles, CA. Like what you read? Find out more about Hero Conf.
PPC pros know how to use data to make better decisions and optimize campaigns for better results. But many agencies and large advertisers spread their campaigns across multiple accounts and analyzing all of this data can lead to greatness if done well….or migraines if done poorly.
Follow these five steps to make sure your analysis leads to hero status.
Step 1: Aggregate Data
Depending on your campaign structures and analytics strategies, you will likely be consuming and storing data from multiple sources (PPC platforms, Google Analytics, your CRM, etc.). Of course, you could manually pull the data from each source, drop into Excel or Tableau, and start crunching, but that process doesn’t scale. Each report or optimization would require repeating the same drudgery. That gets old very quickly.
Plan ahead and work with a developer to consume campaign performance and conversion data from each platform’s API. The less tech-savvy approach is to pull the data manually and upload to a database, but it is much easier, in the long run, to automate as much as possible. We pull daily data from each platform’s API and store it in a SQL database with table(s) for each PPC and analytics platform.
At this point in your journey, your most important task is to define your data structures. Which fields do you need to store, and how do you want to relate your data tables to each other? Basically, which data do you need to do your job? Planning this in advance will save you many headaches later when you discover you don’t have all the data you need to perform your analysis.
Don’t forget to think ahead to the analysis tools you plan to use (more on this in Step 4). Be sure your data structures and databases allow for secure external connections and are structured properly to make sense in an external environment.
In our case, because we are an agency, we ensure every row in every table has a “client ID” field so we can join the data at the client level. This allows us to mash up data and create reports for each client.
Step 2: Normalize Data
We’ve all heard the expression, “Garbage in, garbage out.” Analyzing data across multiple accounts is no exception. It is imperative that your accounts’ data is normalized, or made more consistent, to allow for apples-to-apples comparisons.
To truly compare and trend data accurately, you have to think “meta,” as in metadata. Metadata is simply data about data. Think of AdWords labels as metadata. For example, keywords in your AdWords campaigns can be branded or non-branded. Agency clients can fall into one or more industry categories.
Try to store as much metadata in your database as possible to allow for more consistent comparisons and analysis. For example, you could compare the effects of AdWords’ removal of right side ads on non-brand keywords for all clients in the healthcare industry pretty quickly if your data structures allow for it. Another of my favorite types of cross-account analysis is comparing pre and post-launch data for new clients based on the day we launch their new campaigns. It becomes very easy to develop case studies and spot anomalies when we can chart 30, 60, and 90-day performance and compare to a previous agency’s results.
Take the time to develop a robust, consistent tagging strategy so your analysis is not tainted with garbage data.
Step 3: Democratize Data
We can’t assume that all of our end users are going to be proficient in writing mySQL queries. Nor can we build dashboards or apps that can possibly predict all the ways our analysts could want to slice and dice the data.
Instead of trying to train everybody on database queries, take a more democratic approach to setting the data free. Make your data structures and sample data sets available in more common formats such as Excel or Google Sheets. This way, anybody can see what data are available and think up ways to use it.
Non-developers can still formulate questions that a data analyst can translate into a database query. For example, an Account Manager recently approached me with the question, “How does Client X’s CPC and CPA compare to other clients in the same industry?” Pulling this benchmark data from our database took just a few minutes, saving hours of manual data pulls and pivot tables.
Bottom line, don’t lock up your data! Set it free so that more people can find ways to put it to use.
Step 4: Analyze Data
Most PPC folk simply jump ahead to this step. It may work in the short-run or for an ad-hoc analysis, but be warned that skipping steps 1–3 could lead to inconclusive results, murky data, or decisions based on inaccurate data. Take the time to do it right so that future analyses will be faster and more insightful.
Now that you have multiple accounts worth of data in one place, it’s time to start analyzing. This is where the magic happens! Analysis can lead to insights and insights to optimization.
Develop a hypothesis
Start by developing and testing hypotheses about your data. Don’t just wade into mountains of data hoping to find a gem. Go in with a map and an idea of what you are searching for.
Select the right tools for the job
First off, find the tools needed to do the job right. A more savvy user might be able to write her own SQL queries and export the data to Excel or Google Sheets, but other users might benefit from a business intelligence tool such as Tableau or Google Data Studio for faster analysis.
Begin segmenting and filtering
Next, take advantage of the data and metadata you set up in step 2. You didn’t skip step 2, did you? These facets allow you to segment your data even further to find nuggets of info that can lead to insights.
Just as in Google Analytics, try toggling different segments on and off to look for anomalies or outliers. This is where clean, consistent data structures come in handy. Make sure that you are controlling for variables that might skew results among multiple PPC accounts. Are some accounts using different tagging methodologies for brand vs. non-brand keywords? Are all of your accounts using the same time zones and currency formats?
Establish benchmarks and trends
Once you find the right data, it becomes easy to compare cross-account performance to spot trends or anomalies. From this macro perspective, you can easily look for over- or under-performing accounts, watch for daily/weekly/monthly/quarterly changes, and proactively identify when you are off pace to achieve your KPI targets.
If you are really advanced (or have a great BI team), pattern detection or statistical analysis tools can do much of the heavy lifting here. Want to take it to 11? Invest in machine learning to spot and escalate outliers for further analysis.
Step 5: Exploit Opportunities
Finally, after all the hard work is done, you get to play hero. With the right data, hypotheses, tools, and time, you will be able to quickly and easily analyze campaign performance data across multiple PPC accounts.
Comparing and acting on data from multiple accounts is fundamentally the same as working on one account, with the exception of having more variables to control for and more potential dimensions for segmentation.
However you decide to set up your cross-account analysis, take the time to plan ahead. The upfront effort to plan ahead will result in many hours of saved time and increased accuracy down the road.
