It was a turbulent year for the advertising industry. In June 2020, GroupM estimated that the advertising industry will decline 10% globally in 2020. Money is tight and it has become more important to develop an inexpensive advertising strategy that justifies every penny spent.
Fortunately, advanced tools based on big data or machine learning have improved the science of analytics. In this article, however, I’m going to explain the art of marketing analysis using just Excel to prepare you for a data-driven advertising strategy.
Step 1: examine the return on advertising spend (ROAS).
Before you write down your KPIs for the new year, it is helpful to think about the advertising strategy of the previous year. During such reviews, marketers sometimes overlook three major issues: ineffective ad campaigns, PPC budget establishment, and attribution models.
1. Weed out ineffective advertising campaigns
If you are spending more than you are making on an ad, the campaign is likely to be ineffective.
Find out how much you are spending on what is critical to optimizing your marketing budget. Focus on the simplest elements: the advertising costs and the resulting revenue. That way, you can immediately flag the ineffective ads and decide if you want to stop them.
Currently, many companies use more than one tool to monitor and review the values in their cost / income calculations.
2. Check your budget setting for PPC
Every marketer’s nightmare: spending an entire monthly budget on a pay-per-click (PPC) campaign in a single day due to anomalies or incorrect attitudes.
In the post-pandemic period, new players and media partners will add more price volatility. So, carefully examining your PPC settings will help you avoid your nightmare. The most important elements to check include negative keywords, target group, quality score, click rate and optimization of the ad copy.
Here is a snapshot of my pivot table in Excel to check data from Google AdWords.
3. Optimize your attribution model
Over 50% of marketers have done attribution reporting to determine which channel is having the greatest impact. The last-click assignment, with which the last ad click in a conversion path is credited 100%, was often used as the standard in Google AdWords. But does the last click match your business model?
Several new device types (smartphone and tablet) and an evolving search behavior (image and voice search) mean that the findings from the assignment of the last click may be inaccurate or incomplete. It is worth doing an ad hoc analysis of your campaign performance for 2020 using alternative attribution models: time decay, linear and data-driven attribution. This will allow you to update your attribution algorithm to avoid missing out on opportunities in 2021.
Step 2: Analyze data for more than just cost and ROI
A deeper look into analytics can reveal valuable information about audiences, trends, and design. Some campaigns may become less effective in 2021 as the target audience has changed.
Here’s a checklist to guide you into learning beyond cost and ROI:
- Clean up and normalize your data set.
- Categorize and segment data so that it makes business sense.
- Do a cohort analysis and a funnel analysis.
- Analyze the effectiveness of keywords, products and landing pages based on customer behavior data and change or remove those that reduce the overall ROAS.
- Analyze the interaction between channels to identify key points of contact.
- Try attribution analysis until you find the most effective model for improving your ROAS.
How can the value of non-monetary indicators be conveyed and the ROAS calculated?
I would like to introduce two valuation concepts from my business classes that have proven particularly useful in evaluating marketing campaigns: net present value (NPV) and efficient limit.
1. Present value
The present value is the sum of the cash flows (both positive and negative) associated with an investment, discounted to the present value:
Marketers should understand the principle – if not necessarily the formula – because you can use the NPV function in Excel.
A negative NPV means that the campaign will be ineffective in the long run.
2. Efficient frontier
After removing campaigns with a negative NPV from the table, how do you decide which campaigns are worth investing more in?
An efficient marginal model can help select the winning portfolio based on risk and return. In a simple two campaign scenario, you can use Excel (template provided) to show different expected returns on advertising investment and risk by varying the proportion of each campaign.
As shown in the graphic below, the goal is to find the combination of campaigns with minimal risk that will deliver the target return.
Step 3: Trust Data Analytics to Use in Budget Optimization
Some managers are skeptical of new data. Others trust him wholeheartedly because they believe that there may be groundbreaking discoveries there.
Use a nuanced approach to avoid using erroneous data or being judged on gut instinct: small A / B tests that will help increase conversions and save budget in the future. Use the test results to project future conversion levels and improve budget planning for your advertising campaigns.
Some popular A / B testing topics:
- Are consumers price sensitive (with discounts in ads)?
- Is location important (showing geographic features in ads)?
- Is the choice important (exclusivity, diversity, customization)?
- What’s the best CTA (call, shop, find, or discover)?
Step 4: automate budget management
At this stage there are several ways you can optimize your advertising budget to save overhead in 2021:
- Configure an automated bid management system that can be used to disable keywords or initiate other activities
- Set the automatic control of the daily budget limits
- Maximize remarketing and route communications via email
* * *
In the digital age, marketing without the use of data is like walking in the dark on the edge of a cliff. But don’t let the advanced statistical modeling and machine learning tools overwhelm you. Setting up actionable data analytics in an Excel spreadsheet can be enough to achieve successful marketing campaigns in the uncertain future.
More resources for data analysis in advertising
Flowchart: Use the right digital advertising strategies and metrics for your campaigns [Infographic]
Why Your Paid Social Media Is Failing and Social Advertising Best Practices
How Marketers Can Protect Their Online Advertising Dollars From Fraud