You have finished your campaign. You see all the data you’ve collected during the campaign. To your delight, everything looks great.
As you open the csv’s and xslx’s you have exported from Google Adwords, which tool should you use to create PPC reports: Google Sheets or Microsoft Excel?
We recently polled our customers and found that our customers use both Sheets and Excel. In fact, 78% of the respondents said they used Sheets, while 74% said they used Excel.
So why are marketers using, and paying for, both tools?!
To answer this question, I will rank Excel and Google Sheets on the following six parameters:
- Design and formatting
- Data visualization (charts)
- Google Analytics integration
- Team collaboration
Let’s dive into it.
Design and Formatting
The way you structure, organize, and display your report can make or break your presentation success. You can have the best data and insights, but if don’t present it well, your boss won’t understand it.
You could design a report from scratch and make it look perfect for your executive team, but that will take you hours of your valuable time. Instead, you can use a template. Both Excel and Sheets offer many template designs.
As you open Excel, you can find 30 template designs ranging from business to family budget. If you need more designs, you can go to the Excel templates pages. You can also download template designs made by other users (Smartsheet has an excellent collection of free Excel spreadsheet templates).
Google Sheets offers about 20 reporting designs, equally split between those for personal and professional use.
When it comes to formatting, Excel is a winner: it offers 60 table formatting options which can give your tables a more elegant look.
Verdict: When it comes to templates and formatting, Excel wins by a large margin.
Creating a report takes more than dumping data into a spreadsheet. It’s your job to give meaning to your data. An effective way to do so is by using charts to visualize data. Both Excel and Google Sheets offer many ways to create charts with just a few clicks.
To create a chart with Excel, you only need to highlight all the data you want to use in your chart, click the “Insert” tab, and choose one of the many recommended charts you have at your disposal. The charts include 17 options such as bar, line, and pie charts, histograms, and plot charts, among others. You can also create different variations of each chart to give them a more personalized look.
With the chart created, you can add text, format the colors of the chart, add borders and fills, among many more customizations. To edit a chart, click on “Format” tab, and you will see all the options available to you.
Google Sheets has a simpler set of tools to create charts. To create a chart with Sheets, you need to do the same as you did with Excel: select the data, go to Insert > Chart, and select the chart you want to use to display your data. Once you create a chart, Sheets offers you the ability to customize its look.
Google Sheets offers a wide selection of charts, including the same ones mentioned above with Excel, such as the pie, line, bar, area and column charts, among others.
Verdict: Excel wins thanks to the larger variety of chart formatting and editing options.
Google Analytics Integration
Anyone who has ever developed a PPC report knows about the importance of Google Analytics, especially for those who specialize in Google Adwords. Accessing its data can be crucial for developing in-depth reports.
Since Google owns both Sheets and Analytics, they have made it extremely easy for anyone to integrate both services with their API.
In contrast, Excel doesn’t offer a direct integration with Google Analytics. The best way to integrate both services is by using an add-on like Analytics Edge.
AdStage’s Google Sheets add-on is the easiest way to pull your paid search and social advertising data into your spreadsheets. With this add-on, you can automatically fetch your PPC performance data from Google AdWords, Facebook Ads, Bing Ads, Twitter Ads, LinkedIn Ads, Yahoo Gemini, and Google Analytics.
Verdict: Learning to integrate Google Analytics with either Sheets or Excel isn’t simple if you don’t know much about how APIs work. Still, if you take into consideration the installation process and the way the analytics tool and the reporting one are integrated, Sheets wins in this category.
Long gone are the days when someone created a report, took it to the executive team, and called it a day. Nowadays, multiple stakeholders can participate in the creation of a report, so collaboration features are critical.
To create a comment on an Excel spreadsheet, first, select the cell you are interested in, go to the Review tab on the Ribbon, and click on “New Comment.” Once you create the comment, you will see in the cell a text box with a yellow background featuring your name at the top. Unfortunately, comments have no threading capabilities (i.e., you can’t have a back-and-forth conversation among several people). In other words, each comment works separately from the rest, making collaboration harder.
Google has made collaboration much easier. If you put your cursor in a cell on a Sheets report and select Insert > Comment, you will find a box with your name at the top. Then, you’d type your comment into a text box and click Comment. If you take a look back at the comment in the cell you selected, you will see an orange triangle in the upper-right corner of the cell which shows that a comment is there. To read the comment, you only need to click the cell or hover the cursor.
Anyone with access to the Google spreadsheet can click on the comment and reply to it, creating a real-time conversation with any stakeholder involved in the report. You can also create a link to any comment and then send that link to someone else. Finally, after you finish a conversation, the original commenter can click the “Resolve” button and the original comment, along with its replies, will disappear.
Verdict: Thanks to its simplicity and ease-of-use, Sheets is a clear winner in this category.
Both Excel and Sheets are relatively cheap compared to more advanced PPC reporting solutions.
Microsoft Excel is sold as part of Microsoft Office, which is categorized by personal or business use and is available on an annual subscription and as a one-time purchase. Currently, Office 365 Home costs $100 per year and can be used on up to five PCs or Macs, five tablets, and five phones; including Word, Excel, PowerPoint, OneNote, Outlook, Publisher and Access, along with 1TB of OneDrive cloud storage. As a one-time purchase, it costs $149.
Office 365 for Business offers the same applications and features as Home, at $10 per user (up to 300) paid monthly, or $100 paid annually. Office 365 for Business Premium includes access to more services, like Skype, Yammer, Exchange, and more, priced at $15 per user paid monthly or $12.50 per user paid annually.
While Microsoft offers a great deal for Excel, Google offers Sheets as a part of their G Suite package for businesses with prices per user at $5 paid monthly for their Basic plan and $10 per user paid monthly for its Business plan. The main difference between both plans is the cloud storage: 30GB on a Basic plan versus unlimited storage and complete access to all the tools from the G Suite for Business.
Verdict: On a head-to-head comparison, Sheets is the clear winner in this category, costing half as much as Excel.
With the increase of international hack scandals and data breaches, security has become one of the most pressing issues. Both Excel and Sheets offer security protection.
Google has recently switched their entire services protocol encryption to HTTPS, the securely encrypted version of HTTP. However, having HTTPS doesn’t mean the data you have on your Google Drive account is encrypted; only the connection is. If you want to increase the safety of your data, you can add a 2-Step Verification for Google Drive and get a security key.
Excel also uses HTTPS on their OneDrive service, making all the data in transit encrypted. They also offer a 2-step Verification for those who want to take extra precautions. So far, both services feature the same safety benefits. Things get better in their business plan option, where you can add a per-file encryption for your data at rest, meaning if one file is hacked, all the other ones would still be safe.
Verdict: Excel offers more advanced security capabilities than Sheets.
After our careful analysis of the most important features of both tools, the result shows us a tie, with 3 wins for Sheets, and 3 for Excel.
You have seen Excel is more powerful in features but may be too complex to use for some of you who have simpler reporting needs. Also, its collaboration features need work and improvement.
In contrast, Sheets can be of great help for those who want to create a report without bells and whistles but with great collaboration features.
For more advanced PPC teams at larger companies and agencies, an automated PPC reporting solution like AdStage may work better than Excel or Google Sheets. With the time saved on reporting across multiple accounts and networks, automated tools provide a clear return on investment.
Google Data Studio is a data visualization tool that allows you to create custom dashboards and reports that are easy to read and share.
Over the last month, we put Google Data Studio’s PPC reporting features to the test. We found that diving in was easy, the tool itself is free, many of the data connectors are free, and the other connectors have free trials. Learning the interface was mostly a breeze.
On a mission to create a PPC report, I used AdStage’s Data Studio Connector to bring my paid search and paid social campaigns (AdWords, Bing, Facebook, LinkedIn, and Twitter) into Data Studio. After playing around with the new tool for a while, I finally got a good grasp on how it works and thought I’d share what I learned.
In this tutorial, I cover how to create a PPC report with Google Data Studio.
1. Building Charts
Data Studio offers about a dozen chart options. In this video, I call out high-level KPIs with the Scorecard widget, show spend across networks with an area chart and pie chart, and visualize conversions over time with a bar chart.
2. Customizing Your Report
More than just styling, which I do cover, Data Studio offers filter control where viewers of your report can adjust key aspects even if they don’t have edit access. I expose date range and ad networks, so viewers can adjust the global date range and the specific networks included in the report.
3. Filtering Data
Data Studio has a lot of great filtering options, from Spend > 0, to Ad URL does not contain ‘blog’. You can create multiple filters using OR and AND and save them for future widgets.
Explore AdStage’s Google Data Studio Connector
Getting started with AdStage’s Google Data Studio Connector is easy — sign up today for our 14-day free trial.
One of the advantages of working at an agency is getting an unfettered look into the data of many different businesses. Not only does this helps leaders and directors shore up the financial wellness of the agency, but it also guides them in improving their clients’ business, and highlights when an adjustment to a team might be a good idea.
In a recent post, Why Your Agency Needs Branded PPC Reports, we highlighted how clients want to feel like they’re the only one you’re servicing. But from the perspective of an agency director, the agency is a business, and you want to have a pulse on how each and every customer is affecting profit and success. An encompassing look at all accounts and their PPC performance via a custom dashboard is a necessity for all agencies, for a number of reasons, including the power of comparison.
When looking at an account individually, you can pretty easily make some comparisons, like year over year, month over month, or even week over week on fast moving campaigns. But you’ll only ever know about that account. This approach could be compared to the Silo Mentality, when departments within an organization refuse to share info or work together, which Forbes says contributes to the demise of business.
Let’s dive into several reasons why agencies need custom agency health reports.
1. Know Who Your Best Customers Are
Great businesses know which customers are most valuable and treat them accordingly. They also know they must figure out ways to transform average customers into their best customers. But it’s impossible to know who’s a top performer unless you’re looking at your entire customer pool. While it might not always be apples to apples, a client comparison dashboard should show you which clients have seasonal budgets, consistent monthly budgets, and which ones are small spenders but still require a lot of account manager time.
As a leader in an agency, formulating a monthly PPC report template across accounts is the easiest and most conclusive way to check in on the health of the agency without manually pulling multiple reports across several accounts or bugging account managers. Using PPC reporting software filters, you can adjust client views and dates in a few clicks to get a comparison view.
2. Share Learnings
How do you get ok clients to be some of your best customers? You figure out what’s working on your top accounts and replicate. If one client is seeing killer performance in one area, take a look at the marketing plan for a lagging client and see what insights you can recommend or incorporate to boost their performance. That’s why agencies are super careful about who they take on as clients, and the good agencies would never take on competing businesses.
For example, let’s say your cosmetics client sees a bump in conversions after reallocating some spend to Instagram video. You might want to make the same recommendation to your women’s clothing client since you can make the assumption the audience the two are going after is largely the same and would respond similarly.
If you’re sharing monthly PPC reports internally, there are shared learnings to be had across team members at every level, too. Staff gets a glimpse into what their colleagues are working on, what tactics are seeing success, and who to turn to for advice when working on similar projects or challenges.
How do you get ok clients to be some of your best customers? You figure out what’s working on your top accounts and replicate.
3. Determine Trends in the Marketplace
If you remember way back to middle school science or statistics classes, you know it’s much easier to come to a conclusion if you have a large sample size. This goes for spotting trends in your clients’ PPC data, too.
In this case, your sample size is all of your clients’ performance data put together. You might see historical trends within one client’s performance, but aggregating all accounts against each other will give you a look at what’s happening now. For example, it’s much easier to spot the effects of Facebook’s latest algorithm change when you can look at the results across multiple accounts.
4. Spot Benchmarks and Red Flags
Think of it as a case of “one of these things is not like the others.” If you know you’re spending roughly the same amount for two (or more) clients in AdWords, but one is getting drastically lower CPCs, you can immediately catch that something’s amiss and take a closer look at what’s going on. Without comparison, you rely on historical data from that account, or any benchmarks you could scrounge up from studies or blog posts from other digital marketers.
Related, knowing the lowest and highest performance numbers for comparable accounts will help you set quantitative KPIs that are rooted in results that are more assured than calculated projections.
5. Check In On Staff
As a manager, you want to know all teams are operating at full capacity for clients. If two accounts have similar profiles, spends, etc., but one is always lagging, a comparison look at the accounts will help you surface potential staff issues and make necessary adjustments.
Eager to get a look at what’s happening across your agency? AdStage’s Dynamic Dashboards have capabilities that allow you to customize dashboards as needed. Throw in graphs and tables and fine-tune views with custom metric options to get the most concise account comparison possible. However you decide to put your dashboard together, there are a few basic data points you’ll want to make sure to include:
- Quality score – created by Google to measure how relevant your ad content is. The better the ad, the less you pay for advertising
- Conversion rate
- Average position on page for search results
- Budget attainment – how close you came to the originally set budget despite constant fluctuations in the PPC auction
- LTV – customer lifetime value. Companies who retain customers longer make more revenue
Check out our post on KPIs that matter to CMOs, directors, and managers for more pointers on what a PPC report should include at certain levels.
And as our post “What Makes A Great Monthly PPC Report?” suggested, all great PPC reports should end by looking forward to the future.
Based on everything that comes up in the report, you’ll want to ask yourself (and/or your team):
- What are the strategies for the following month?
- Do we need to stay the course or make big swings in strategy?
- What actionable steps are we going to take to improve performance next month?
Take advantage of being able to see into multiple accounts at one time. Not only will it help you give clients a leg up, no doubt it will accelerate your learning, too.
The National Retail Federation predicts that online is going to be the major driver of growth for retail in the next years to come. No surprises here: consumers spend more and more time on mobile discovering and comparing products — a perfect opportunity for businesses to capture buyer’s intent in the “micro-moments.”
Yet as retail is undergoing this massive shift, many stores are teetering on the brink of bankruptcy — and the competition gets tougher for the young disruptors. To grow business and increase sales, e-commerce marketers need to ensure they are tracking web conversions and measuring the right KPIs.
Here are 44 essential metrics every e-commerce business should be measuring.
Business Health Metrics
|Customer acquisition cost (CAC)||Calculate how much it costs you to acquire a customer|
|Average order value||Calculate how much revenue each order brings to your business|
|Customer lifetime value (LTV)||Predict how much money a customer will bring over the entire future relationship with your business|
|Percentage of returning customers||Calculate the percentage of people who come back and buy more after making the initial purchase|
|Sales conversion rate||Calculate the percentage of visitors who become your customers|
|Revenue generated from marketing campaigns (marketing ROI)||Calculate how much revenue is generated for all marketing activity|
Website and App Metrics
|Site availability||Determine if you can access a website by going to its URL and view the site’s content as expected|
|YoY traffic changes||Compare the number of visitors for one period to the same period the previous year|
|Time on site||Measure how much time visitors spend on your website|
|Number of pages visited||Measure how many pages a visitor views per session|
|Number of app downloads||Calculate how many people downloaded your app from Apple’s App Store or Google Play|
|Sales conversion rate||Calculate the percentage of website visitors who become your customers|
|Average order value||Calculate how much revenue each order brings to your business|
|Orders by session||Measure how many orders each session (visit) brings to your business|
|Shopping cart abandonment rate||Calculate the ratio of the number of abandoned shopping carts to the number of initiated transactions|
Customer Experience Metrics
|Website loading time||Measure how long it takes for your website pages to load|
|App daily active users (DAU)||How many people use your app daily|
|Pageviews per user||Measure how easy it is to navigate your website|
|Page abandonment||Measure how many visitors leave a page before completing the desired action|
|New users/repeat users by pages||Measure how many new visitors your website pages get versus the number of repeat visitors|
|Bounce rate||Calculate the percentage of visitors to a particular website who navigate away from the site after viewing just one page|
|Customer lifetime value (CLV)||Predict how much profit a customer will bring over the entire relationship with your business|
|Net promoter score (NPS Score)||Measure the willingness of your customers to recommend your company’s products or services to others|
|Repeat purchase rate||See the percentage of your current customers that come back to shop again|
Paid Search Advertising Metrics
|Spend||Calculate how much money was spent on advertising for a given date range|
|Clicks||Measure many times users click on your ad and get to the landing page|
|Impressions||Measure how many times users saw your ad|
|Click-through rate (CTR)||Calculate the number of clicks divided by the number of impressions|
|Average cost per click (CPC)||Measure how much on average you were charged for a click (calculated by dividing the total cost of your clicks by the total number of clicks)|
|Conversions||Measure how many times a click turned into a business result (a sign-up, a sale, or any other action taken by the user)|
|Conversion rate||Calculate the number of conversions divided by the number of clicks|
|Cost per conversion||Measure how much you pay for a conversion|
Social Media Advertising Metrics
|Spend||Calculate how much money was spent on advertising for a given date range|
|Network referrals||Track which social networks are driving most traffic|
|Social media conversions||Measure conversion by social networks to understand which ones bring the most valuable shoppers|
|Clicks||Measure many times users click on your ad and get to the landing page|
|Frequency||Understand when to set a frequency cap to prevent your audience from getting fatigued|
|Relevance score||See how well your current ads are performing and whether you should make any changes|
|Open rate||Calculate the percentage of email recipients who open a given email|
|Clickthrough rate||Calculate the percentage of email recipients who clicked on links in a given email|
|Conversion rate||Calculate how many people completed the desired action|
|Bounce rate||Measure the percentage of your emails that could not be delivered to the recipient’s inbox|
|List growth rate||Calculate the rate at which your email list is growing|
|Email forwarding rate||Calculate the percentage of people who clicked on a “forward to a friend” button|
Ready to create a performance dashboard for your e-commerce business? At AdStage, we’ve built Universal Data API to help e-commerce brands pull in ad performance data, automate paid search and paid social campaigns, and map customer data back to campaigns so you can see the ROI and scale your business faster.
Try AdStage Free for 14 Days — no credit card required
Nearly everyone’s been in a meeting where your boss asks for a specific piece of information and your mind goes blank as you fumble through your notes and try desperately to log in to your analytics dashboard. And on the flip side, even the best leaders and managers have second-guessed if they’re paying attention to the most relevant numbers.
Whether you’re the one pulling the report or the one tasked with making sure everything’s on track, knowing the PPC KPIs to keep track of is crucial. From the CMO, to director, to manager, each will want to see a different set of numbers, but it’s safe to assume the higher up the chain, the more high-level view will be appropriate.
CMOs want an overall pulse on performance while managers want to dive into individual ad pieces to see what’s working and what could be improved now. Here are some other points to consider as you make sure the KPIs you’ve chosen are serving you well:
What Everyone Will Want To See
Generally, the PPC KPIs a CMO, director, and manager are going to care about are: where we’re at now, and a benchmark to determine if you’re on track. It’s hard to judge success without some sort of context. Benchmarks could be anything from total budget spent, to a comparison of last quarter or last year’s data, to conversions vs CPA.
Again, CMOs, directors, and managers will find different information to be helpful to their specific job function, but as a starting point, Search Engine Journal suggests there are ten basic PPC KPIs everyone should be tracking:
- Quality score – created by Google to measure how relevant your ad content is. The better the ad, the less you pay in advertising
- Conversion rate
- Average position on page for search results
- Budget attainment – how close you came to the originally set budget despite constant fluctuations in the PPC auction
- LTV – customer lifetime value. Companies who retain customers longer make more revenue
Though all the above is important, CMOs, directors, and managers may want to pick and choose from this list depending on what goals they’re focused on, and some will want to see these numbers measured against historical data for more context, not just an update on the current climate of ad campaigns.
PPC Reporting: In-house vs. Agency
One huge and important differentiator of what the recipient will care about is if he/she is in-house or agency. In-house people will obviously only care about their business, while the business of an agency is the health of all their clients. For that reason, agency folks will want an overview of several different accounts, broken down in a way that allows them to not only compare historical data for each account but also pits accounts each other.
Running an agency is running a business, and they’ll want to know who their best customers are. Agency staff may also want insight into which ads are performing the best across all clients, and if any networks or platforms are performing better than others.
By the Numbers
If you’re the person pulling a report for any of the key players, anticipate what questions the recipient might have, and answer them with the data you pull and the way you present it. This post on PPC reporting has some great breakdown questions to help you get into the mind of the end reader.
- Again, a high-level figure like a CMO is going to want the bigger picture of overall health, which is most often accomplished with year over year comparisons like 2017 vs. 2016 for spend trends, conversion trends, and CPA trends
- They’ll probably also want to see a brief monthly breakdown to make sure current performance is what they expected. That breakdown might look similar to SEJ’s suggestion: Clicks, Impressions, CTR, Average CPC, Spend, Conversions, Conversion Rate, Cost per Conversion.
- He or she is also going to want to know how much money is being spent – what portion of the overall allotted budget has already made its way out the door, and where it’s going (networks/platforms, search vs. social)
- He or she may also want to know how larger ad campaigns have impacted the overall business all the way down to revenue numbers. While a manager will be interested in the individual pieces of the campaign, a CMO wants a broader summary of what each has done for business.
- Directors are the bridge between what the managers are accomplishing on the ground with teams, and what the CMO is expecting from the company overall. They’ll need to have a pulse on what’s going on below them so they can effectively interpret progress to CMOs.
- For the above reason, they will want to see everything the CMO wants to see and more
- They’re going to want a closer pulse on the day-to-day of campaigns than a CMO, but expect the managers have a handle on the nitty-gritty of say, each ad
- KPIs they’re interested in are going to be current monthly data as well as historical data for the preceding month or quarters. They’ll use this to make sure everything is charting positively and to spot any red flags.
- A manager is going to want to see all the information down to the most detailed level – per ad performance
- He or she will want to know exactly what’s working and what’s not so that immediate adjustments can be made
- Managers will be more interested in the quality of the leads (MQL) and ad quality scores since they can make an immediate impact here by switching up creative
- They’ll also want to know how things are going in relation to more immediate historical data like preceding month and quarter. While the CMO is interested in looking at performance year over year, a manager will be more interested in information that tells them if changes need to be made today.
Again, ask the recipient if they’re looking for specific KPIs before you go digging up the information. It’s better to be on the same page than to take stabs in the dark and get it all wrong.
You’ll also want to be sure you’re showing the information in the form that’s most helpful and revealing. A line graph might show results a lot more clearly than a bar graph. For more tips on when and how to use data visualization, check out this post on how to use graphics for PPC reporting.
With AdStage’s PPC Reporting Software, it’s easy to create custom reports depending on who’s asking for numbers. And it never hurts to have your AskAdStage slackbot open in meetings just in case a curveball question suddenly pops up.
Whether you’re in an agency role or part of an in-house team, there will inevitably be a time when you’ll be asked: “What’s the business impact of our advertising campaigns?”
For many digital marketers, our Pavlov reaction will be to open up the network ads manager or a spreadsheet and point to individual network conversion performance (total conversions, cost per conversion, conversion rate) to justify the media spend. However well-intended, this approach is often misguided.
There are a few key issues with using ad network conversion tracking to reveal business impact.
1. It Defaults to Last-Click Attribution
There’s a reason why your branded search themed ad groups or your social retargeting ad sets post the lowest cost per acquisition trends. Your ideal prospect has likely already engaged with a marketing campaign in the same ad account or within a different network, yet you don’t see the full picture.
To see this for yourself, open the conversion window options in the network ad platform.
2. It Doesn’t Account for Marketing Touches From Other Channels
According to the Online Marketing Institute, “it takes 7 to 13+ touches to deliver a sales qualified lead.”
Pull a Conversion Path Report from Google Analytics, and it likely will reveal that many of the common conversion paths include multiple touch points and sources that aided in driving the conversion action.
3. Conversions Aren’t People
Have you ever ran a demand generation or acquisition campaign only to find the total amount of conversions reported across all your ad networks are higher than the number of new prospects added into your database? Your retargeting campaigns are likely leading to double counting.
For example: someone clicked on your search ad on Google, visited your website, and then converted on a retargeted Facebook newsfeed ad by filling out a demo form. In this scenario, AdWords would report a conversion – as will Facebook Ads – despite there only being one person who filled out the form.
In the chart above, notice the difference in the total amount of conversions and cost per conversion trends reported by the native networks, versus the cost per new prospect and cost per acquired new prospect trends reported out of the marketing automation system.
4. Online Conversions Only Track Web Actions, While Sales Often Happen Outside of the Website
E-commerce marketers have the luxury of being able to track all of their customer journey metrics, from the initial touch to the purchase, captured in web analytics. They can easily understand the impact of marketing campaigns and channels, and associated return for each digital advertising dollar spent.
For the rest of us, especially for those in B2B businesses, a customer journey includes various touch points: sales phone calls, emails, on-site events, and the occasional steak dinner to land the deal. With sales cycles ranging from weeks to months versus e-commerce purchases that happen in hours or days, it can be very tricky to understand the impact of ad campaigns on business metrics such as opportunities created and closed-won revenue.
5. Lead Generation and Sales are Often Two Different Things
Forrester revealed in a report that less than 1% of marketing leads turn into new customers for B2B businesses. A core reason for this trend is a lack of reporting visibility into marketing campaigns effectiveness after the initial interaction occurs. Which makes sense, as marketers optimize what they can track: total leads and cost per lead.
However, if you have been in the game for awhile, you know that web conversions don’t account for a crucial component: lead quality. Online tracking stops at the web action, while your CRM tracks the entire customer lifecycle. Customer data is crucial when understanding if your ad campaigns are leading to more opportunities and sales.
There are four core stages in a B2B sale:
*Note: Individual company stages, models, and definitions can fluctuate, but they are typically rooted in this design.
|Lead||A prospect has an interaction on your website or offline touch point such as an event. Their information is added to the business CRM. Most network conversions track up to here.|
|Sales Qualified Contact||A prospect meets certain criteria: company size, revenue, geography, budget, product need, or demo request.|
|Opportunity||A need and fit for your product or service is established. This is the negotiation stage to see if the prospect and company will buy.|
|Closed Won or Lost Customer||The outcome of the negotiation stage, hopefully resulting in a new customer and closed-won sales contract.|
If you track performance down to the opportunity and sale level, it can often unearth areas where relying on conversion tracking or cost per lead metrics alone are short-sighted. For example, take the table below. Both campaign A and B have a total budget of $3,000.
Relying on web conversion tracking, it appears that Campaign B is outperforming Campaign A with an average cost per lead of $88 versus $94 trending. However, as we track the performance deeper into the sales funnel, it reveals that Campaign A not only provides a lower cost per opportunity, but a higher overall return on ad spend.
Conversions reported by the native networks are still very helpful. Before you have a large amount of sales data, they should be used to aid with bid optimization (especially Facebook’s objective bidding or oCPM). They can also be used as the initial line of defense to quickly spot trends of what is or is not performing.
However, marketers should not rely on them solely. Instead, they should combine data from web analytics, ad performance metrics, and their CRMs to reveal the true impact of advertising on the business.
Let’s review the steps to getting full-funnel tracking for your advertising set-up.
Track Down Funnel Sales Impact with Uploaded Conversions
There are four key steps needed to report, analyze, and optimize for deeper metrics such as opportunities, sales, or revenue with your ad campaigns:
Step #1: Add URL Tracking Parameters to All Your Ad Creative Destination URLs
Adding additional tracking parameters to each new ad creative will help inform both your CRM and web analytics exactly which ad account, campaign, and ad variation led to a desired action (and which did not).
Here’s an example of a destination URL with applied custom tracking parameters:
Learn more about the process in our post on tracking conversions with Google Analytics.
There are a few ways to add tracking parameters to your destination URLs:
- One-by-one using the Google Custom URL builder.
- Use templates such as Google’s ValueTrack parameters or Facebook’s URL parameters section when creating an ad.
- Use an Excel or G-Sheets template.
I personally like to use Effin Amazing’s Google Chrome plug-in with presets which pipe into a central G-Sheet as they are made.
Step #2: Add Hidden Fields to Your Forms
Out of the box, your web analytics and ad destination tracking parameters will not talk to your CRM. With hidden fields in forms, this information can automatically be captured and mapped in systems like Salesforce.
Hidden fields are invisible to web visitors, but pass tracking attributes to your marketing automation database.
Within your marketing automation system, create a form that includes a hidden field that will capture the tracking elements in your URL and transcribe them into common fields in your marketing automation and CRM systems, such as lead source and lead source details.
Learn how to add a hidden field to your forms in HubSpot, Marketo, or Pardot:
Step #3: Add Custom Fields to the Account and Contact Records in Your CRM
On the contact (prospect) and account (company) record pass through and map the hidden fields collected in your CRM.
Step #4: Upload and Map the Data Back to Ad Campaigns
To add and view your down-funnel sales metrics next to your advertising performance metrics for networks like Facebook and Google, you must map the data to “offline conversions” columns. Offline conversions are essentially any key metrics that are not captured using web tracking pixels. Learn more about Google and Facebook’s offline conversion offerings.
You can either manually download your campaign and ad performance for each network alongside your Salesforce data, map it in Google Sheets or Excel, and then upload the combined data as separate lists for each network. Alternatively, you could pull it all into Google Sheets and update your offline conversions across all networks in one swoop with access to network APIs.
How We Upload Offline Conversions Across All Networks in One Batch
As official advertising partners of Google, Bing, Yahoo, Facebook, LinkedIn, and Twitter, we knew there had to be a way to better streamline this process for our own marketing ad accounts.
Here are the steps we took to map down-funnel business metrics to offline conversion columns across all networks:
- We use the AdStage Google Sheets data connector add-on to pull in all of our ad campaign data quickly and efficiently.
- Learn more about AdStage for G-Sheets.
- We use the G-Connector for Salesforce add-on to pull in Salesforce reports into Google Sheets.
- Then we use some VLOOKUP formula magic in G-Sheets to merge campaign and account IDs with sales metrics.
- We download our Google Sheet as a .csv, then convert it to JSON (a data exchange format read by our Universal Data API) using the CSVJSON online converter.
- This information is sent to AdStage’s Data API and revealed in the platform as AdStage Custom Conversions.
Introducing AdStage Custom Conversions
Speaking to others who market businesses with off-website sales touches, we found that many advertisers struggle with multi-touch attribution. They are flying blind or dealing with very manual and error-prone processes to simply understand the business impact of their search and social advertising campaigns.
We’re excited to announce the release of AdStage Custom Conversions, allowing customers to map their down-funnel sales data from sources like their CRM next to their advertising campaign and ad level metrics to make better, more informed decisions on optimizations.
Find your AdStage Custom Conversions as available metrics to in our Report and Automate products, allowing you to analyze the impact of your ad campaigns, then take action by deploying optimization rules and monitoring alerts.
Analyze and build reports using AdStage Custom Conversion metrics, then take action by creating campaign monitoring alerts and optimization rules using your custom data.
Time is a scarce resource, and especially at the C-level. A face-to-face meeting with the company’s senior executives is a rare opportunity to highlight your team’s successes and prove your department’s business impact. Here’s how to make the most out of it.
When presenting to the C-suite, marketers can borrow some techniques from sales. Take the “3Cs” framework (context, content, and contact). Top consultants use the “3Cs” when selling to top-level decision makers, and this framework applies just as well to a marketing presentation.
- Set the context by framing your data into a cohesive narrative
- Structure the content to support your insights with the right data and metrics
- Contact your audience by connecting with their goals and values
Obviously, a PPC dashboard for an e-Commerce business will differ from that one of an in-house B2B software company. But no matter the case: solid data, convincing logic, and an engaging story will score you a strong “A” in your PPC report card.
1. Present Your PPC Metrics Within the Right Context
A PPC report for the C-suite should tell a story that is actionable at the executive manager’s level. Of course, you need a high-level summary. But don’t leave out the core PPC metrics; help your audience to make comparisons, see trends over time, analyze distribution, and understand relationships between metrics.
Here’re the foundational PPC reporting metrics to get started:
|Spend||How much money was spent on advertising for a given date range|
|Clicks||How many times users click on your ad and get to the landing page|
|Impressions||How many times users saw your ad|
|Click-through rate (CTR)||The number of clicks divided by the number of impressions|
|Average cost per click (CPC)||How much on average you were charged for a click (calculated by dividing the total cost of your clicks by the total number of clicks)|
|Conversions||How many times a click turned into a business result (a sign-up, a sale, or any other action taken by the user)|
|Conversion rate||The number of conversions divided by the number of clicks|
|Cost per conversion||How much you pay for a conversion|
As you work with these metrics, focus on how this data can provide insight into your PPC performance in a way that makes sense to the C-level executive. For example:
- Show spend against your budget goal
- Compare CVR (conversions) and cost per conversion at one time period with that of a comparable time period to see trends over time. Year over year (YOY) and quarter on quarter comparisons are very common (2016 vs. 2017, Q3 vs. Q4)
- Visualize spend distribution to show percentage by channel and network
- Show spend and cost per conversion to explain how increasing your budget could help drive conversions at a lower cost.
2. Structure Your Content Using the Minto Pyramid Principle
Developed by Barbara Minto, McKinsey’s first ever female consultant, the Minto Pyramid Principle is used to help business people to structure their writing, thinking, presenting, and problem-solving. The goal of the framework is to make it easy for the audience to consume information. The idea is that you start on top of the pyramid (your executive summary) and move down to your key insights and then down to supporting facts.
- Start with your key recommendation.By giving an executive manager your advice upfront, you’ll help her focus on the areas of your report that are most relevant.
For example, “My key recommendation is to increase our testing budget. The learnings will help us inform our holiday budget allocation in the next quarter. Platform X and Y recently launched Z options for targeting and creative, and I’d like to explore how they will work for our brand. This quarter, we tested user-generated content vs. in-house produced ads, and UGC outperformed against ads by 8 percent. ”
- Group your metrics and insights.The data in each grouping should reflect one key idea or insight. For example, if you discuss your spend against the budget goal, you can group your spend by channels. Use visuals such as pie charts and graphs.
- Organize your metrics in a logical order.
When analyzing things, people usually seek to identify cause/effect relationships, divide a whole into its parts, or classify similar things. So when you zoom in on your metrics, structure your thoughts logically. Say, you want to discuss an uplift in impressions. Start with the effect (impressions went up) and discuss the cause (we tested this new channel; we refreshed creatives and copy; we narrowed ad targeting). Or if you wanted to talk about spend across channels, you could visualize your paid search and paid social budget as a whole and talk about the ad networks as its parts.
You may want to follow a different approach for an interim check-in report, where you just walk your boss or client through your data and some of the insights. You can also adjust your presentation and go more granular when you share your report in writing. But for an in-person presentation for the C-level, the Minto Pyramid has proven to be effective by major consulting firms.
3. Contact and Build Rapport by “De-geeking” the PPC Lingo
When PPCers meet, their language seems foreign to an outsider. The subject of the conversation may shift from the latest Facebook Ads Manager update to discussing the early results of testing a new AdWords extension. These things are all exciting, but they don’t speak to the problems, values, plans, and desires of a C-level executive. So bring out the graphs and build rapport with the C-suite by learning their language.
AdStage’s Marketing Director, Mike McEuen, has great advice on translating PPC metrics into common business terms. Three more “C’s” from Mike will help you “de-geek” the report:
Take clicks. What are these clicks? Business people will relate better if you bring up website visitors or buyers and describe the audiences you’re targeting.
While “cost” makes much more sense to anyone outside of the PPC industry, frame it as “budget spend” and talk about relevant goals and metrics, such as percent spend, projected needs, year over year change, etc.
When you talk about conversions, use descriptive terms that show business value: “leads,” “sign-ups,” or “purchases.”
Showcase performance trends across channels, accounts, and campaigns with graphs, charts, or tables. Pull in your custom business metrics to paint the full picture, not just pay-per-click dollars. With tools like AdStage, you can upload data from your CRM, marketing automation systems, apps, BI tools, and billing systems to get a full view of how your teams are driving value for the business.
After the presentation, leave some time for questions. When you don’t know the answer to a follow-up question, just say it and ask to get back to the executive via email.
As you’re using the “3Cs” framework to present your PPC report the executive audience, structure your report in a way that provides just enough context to grasp the essential insights. Use logical order to frame your content into a cohesive narrative. Finally, contact your audience by connecting with them through effective visuals and common language.
Sign up to receive our weekly newsletter.
Picture this: You just have finished running your PPC campaign. You are happy because it performed well. You can’t wait for your boss to see the results. You grab all your data and start creating the dashboard to show your boss.
After a few hours of work, your dashboard is done. Everything is looking good, so you go to your boss’s office and show her the results. Once you are done talking about the results of the campaign, your boss looks at you, unimpressed, and says, “OK, what’s the impact to our business?”
You mutter a few words, trying to explain the significance of your findings, but to no avail.
You lowered the CPC of your top-performing keywords and increased their CTR. You also tested new ad copies and found new keywords to bid. Why didn’t she like the results?
The problem is simple: your boss doesn’t care about the details of your PPC campaign. She cares about the results it brings to the business. Your PPC report wasn’t for your boss; it was for you.
In this article, you will see how to build dashboards that will win your boss’s attention (and make you look great).
Add Context with Insights, Impact, and Action
Many marketers assume that dashboards should be reserved strictly for graphing numerical data. For these marketers, text is for reports; data is for dashboards. This misconception leaves aside all sorts of information which can help your boss understand your report all within one dashboard.
Your boss is unimpressed every time you show her a PPC dashboard because she doesn’t get the context of your data. If you can make the abstract concrete, your boss will understand what each metric and graphic mean for her.
Analytics guru, Avinash Kaushik, recommends using the following three elements in any dashboard.
Metrics and graphics show what happened during a given period. What your boss wants to know is why that happened. As a marketer, you should be able to explain what could have caused each metric to go up or down. You want your boss to have an “aha” moment that can make her see what’s going on with your PPC campaigns.
2. Recommendations for Action
Once you have explained why things happened they way they did, you need to follow up with a recommendation for action. After you answered the “why,” you need to give them the “what now?“
3. Business Impact
Once your boss knows why something happened and what they can do about it, the final piece of the puzzle is explaining why your boss should do what you recommended her to do. Your boss wants to know what impact your recommendations will have on the company’s performance.
Use Benchmarks and Trends
Insights, impact, and actions are all helpful ways to amplify your metrics with some context. No metric reported works on its own. If you show your revenue, it’s likely there are associated metrics that have affected it, like conversion rate and average order value, among others.
Another way to show context is benchmarks. You can show three kinds of benchmarks in your dashboards.
- Own data benchmarks. It’s much simpler to create your own set of benchmarks using data from your analytics provider. You can find those benchmarks by looking at month over month trends for each year and across years.
- Industry analyst data benchmarks. Industry analysts and companies like Gartner or Forrester, Consumer Electronics Association, and eMarketer sell this kind of information, which can be useful but expensive. If you decide to use one of these companies, consider the data collection methodology before purchasing their data.
- Competitor data benchmarks. You can use tools such as SEMrush and Spyfu to analyze your competitors’ performance. These tools give you a real view of how much they are paying per click, what keywords they are bidding on, and more.
Another way to add context to your dashboards is by contrasting segmented metrics with goals. You can also show the trends of each metric and goal, so your boss can see the performance throughout a specific period (usually from a month to a year).
Without context, even the most important metric won’t provide any value on the dashboard.
Choose the Right Kind of Dashboard
Depending on your needs, you can add different elements to your dashboard, broader in scope or more narrow, strategic or operational, real-time or predictive. In the table below, you’ll see example options from Juice Analytics. As you build your dashboard, however, remember to focus on insights, recommendations, and their expected impact on the business.
Source: Juice Analytics
Each of these options will help you distinguish what useful, interesting, and productive information to show or leave out.
Show Business Metrics
As you may recall, your boss wants to see results, not numbers. She may get impatient because her time is scarce. Senior-level executives are responsible for making the business grow, so they care about everything related to that goal, not your department’s one.
To make both your boss’s and your life easier, you need to understand the difference between external and internal metrics. Internal metrics are those that matter to your department. Since you work as a PPC specialist or manager, your internal metrics include:
- Quality score or Relevance score
All these metrics are important for you. But how can your boss translate those metrics into actual business results? Instead of waiting for him to discover that by himself, you need to show him the “external metrics,” the ones that affect the business. These metrics include:
- Number of acquired customers
- Acquisition costs
- Average order value
- Number of leads
- Number of trials
- Revenue generated
- Sales pipeline contribution
- Lifetime value
- Goal conversions
- Task completion rate
Even if you show your boss your external metrics like the ones mentioned before, you can’t show him 20 different ones. That will make it hard for him to understand which ones matter and which ones don’t.
As a rule of thumb, your dashboard should contain fewer than ten metrics. Each metric should have a specific goal associated with it. Also, you should segment most, if not all, of them.
Segmentation Is King
You have seen how important it is for your dashboard to explain the performance of a given metric and recommended actions to improve it. Aggregated data can help you find if there’s an issue with a given metric, but it won’t explain which segments that influence that metric are over or underperforming.
Segmentation is thus a key way to make it easier to understand why a metric performs above or below the desired threshold. Once you find which segments are driving an average metric down (or up), you can then recommend actions to fix (or optimize) that segment.
In the case of a PPC dashboard, you can segment by a wide range of attributes, including:
- Mobile device
- Number of clicks
- Spend of ad account
- Ad network
Your boss and other decision-makers don’t have a clear idea of what happens with their website. Showing them segmented metrics, trends, and results is a useful communication tool.
There’s nothing more important in any analysis than segmentation because your visitors aren’t one-dimensional. They all have different attributes, whether they are demographic, behavioral, or transactional. Unfortunately, most of the PPC reporting and analysis are done in an aggregate way. You must have an effective and persistent segmentation strategy as part of your analytics process.
Creating useful PPC dashboards can help you position yourself as a savvy business member of the marketing team. You have seen what specific elements you need to add to your dashboards to make your boss happy. Not only everything you learned today will help you stand out; it will help your boss drive better results, which in turn can help you progress within your company.
If you’re looking for a way to automate your cross-network PPC reports with business goals, check out our free 14-day trial of AdStage.