This is part two of our three part EXCELlent series, showing you how to boost PPC performance with Excel. Yesterday, Jessica expanded on her list of Excel Tips for PPC Managers. Today we’ll be looking at how to use excel to project PPC account performance.
If there was one tool I wouldn’t want to do PPC without it’s the Projection Worksheet (PW), lovingly referred to here as the PDub. Using the PW, you can see how much above or below goal your account is projected to be at the end of the month as well as the projected performance of each search engine. It also helps you analyze the performance effects of any changes you make in your PPC account.
In this post I’ll explain the different parts of the Projection Worksheet and then walk through the steps to take when filling out the document for yourself. At the end of this post you can find a link to download your own Projection Worksheet template.
The Projection Worksheet
There are two main tabs in the Projection Worksheet. This first tab is the Variables Tab that has date data used in projections and a management table if there are multiple users working in the file. The second tab is the Account tab which contains all the information for an individual account. Let’s look at each tab in more detail.
Variables Tab
The variables tab contains what is probably the most important part of the projection worksheet: the date. This is the first thing you should change when updating the PW to ensure you have accurate monthly projections. Enter in today’s date in the corresponding cell. At the beginning of each month you’ll also update the last day of the month cell to get the correct number of totals days. All other date data will update automatically.
The variables tab also includes a list of people who have edited the PW. This is useful if you have multiple people working on this document. At Hanapin, we have one PW that contains the projections for all of our clients. When we update the data for our clients we enter in the date, our initials, and the time we completed the worksheet. This helps keep us organized and allows us to make sure everyone has filled out his or her projections.
Account Tabs
Each PPC account you manage should have it’s own tab in the PW. To copy the account tab go to Edit >> Move or Copy Sheet and create as many tabs as needed for all of your accounts.. Below is a screen shot of a completed account projection worksheet (click image to enlarge). I’ll go through each section individually.
Account Stats
The first part of the accounts tab is the account stats. You will manually fill in the click, conversion, and spend data for each search engine in which you are running PPC ads. You only need to fill in the yellow cells, the rest of the data will be calculated for you.
From the previous 7 days and the month to date data, projected stats are calculated using the following formula:
This allows you to see which search engines are projected to be above or below goal and helps you determine where you should focus your efforts first to make optimizations.
The numbers for each search engine are totaled at the bottom so you can easily see how your account is performing overall. If you add in another table for an additional search engine, be sure to update the sum formulas in this section.
Previous 7 Days Comparison
The previous 7 days comparison is useful in determining how changes you made the previous week affected your account. For example, if you increased bids in the last 7 days you can see what effect it had on conversion rate or CPA.
Before you update the previous 7 days account stats, you’ll want to copy the Previous 7 Days column from the account stats section and paste the values in this column. Make sure you use the Paste Values option and not Paste, otherwise the formulas will be copied and will update when you enter in the current P7D stats. You’ll know you’ve done it correctly if all of the cells in the % Difference column equal 0.00%.
Goal Projections
The blue box at the bottom of the worksheet shows the percent over or under the account’s month-t0-date stats and the projected stats are from the goal. The first thing you’ll want to do is enter in the account goals in the yellow cells. For this example account we have monthly goals based on conversions, budget, and CPA. If your account goals are click-based just change conversions to clicks and reference the MTD click totals and projected click totals.
Steps To Filling Out The PW
Below is a list of the steps to follow when filling out the PW. These instructions are also included on the Instructions tab in the template.
Step 1: Change the Date. On the variables tab enter in today’s date. If it is the beginning of the month, also update the last day of the month.
Step 2: Copy and Paste P7D Values. Click on your account tab. Copy the previous 7 days column and paste the values over on the second previous 7 days column. If this is the first time you’re filling out the PW for an account, there won’t be any P7D stats to copy.
Step 3: Update Search Engine Stats. Go into the interfaces and pull the data for the last 7 days and month to date. Only enter data into the yellow cells. All other cells will be updated automatically.
Step 4: Update Account Goals. Enter your account monthly goals into the yellow cells in the goals projection section. Make sure to update these numbers every time account goals change.
Download the PW
Download the Projection Worksheet template here: Projection Worksheet
(.xls version:Projection Worksheet.xls)
There are four tabs on the template: Instructions, Variables, Account, and Example. The Account tab is a blank template to use for your accounts. The example tab contains data so you can see how stats are projected. If you have any questions about how to use the worksheet please post in the comments section below.
