Competitive ads reports are helpful for clients to keep track of their rivals’ spending and ad prominence. Did they boost spend? Are there new competitors on market also buying our keywords? There’s lots to discover so its a valuable report.
Updating this data over time provides a good picture of a rivals as well as our own client’s market share – which we want to keep in the sweetspot for ROAS. Generally speaking, we want to be between 15 and 35% ad market share. (Aka “Impression Share” in Ads-speak). If a client is below that sweetspot level for a given market, we’d recommend they spend more. And visa versa.
We can learn how to pull and update these reports with a written how-to. It’s basically just a matter of pulling in the new data from Ads, and dropping into each report’s data tab, and right-clicking ‘refresh data.’
1. open the Excel document of the report. Using traditional file is required because Excel online has limited formatting.
- the latest Pam files are here. Be sure to click the three dots icon to the right of the file, and download the file to your computer. Re-save it onto the drive when done.
2. Open google ads to download the latest data.
- In this example for Pam Harrington, we have to run two different data pulls from same account. When in the Auction Insights tab in that account’s Ads interface (where this data lives) we have to specify the RE or Rentals campaigns only via the filters on top. I’ve done this for us via links, but be sure and check the dates to ensure the latest month or timeframe is included.
-
- the below links should open – please email tom.burkart@q4launch.com if not.
- Here’s the real-estate data pull link.
- be sure to check the dates, then click download once it refreshes.
- Contrastingly, the Rentals data pull link includes all others (non-RE). Branded is in rentals!
3. Once data is downloaded, add the latest month to column A and paste in the rest into the sheet below the existing data – its all the same column setups for columns b onward
-
- Once these data tabs are updated – you need to do an Edit/Replace on the values in main impression share column that show up as ‘less than 10%’ (< 10%). Please swap this to 9%.
- The ‘less than 10’ indicates spend below the threshold. However, We need a hard number for the data, and 9 is different than a pure 10.4% score that’s actually a 10, for example, so this keeps below-threshold spend levels distinct
- Once these data tabs are updated – you need to do an Edit/Replace on the values in main impression share column that show up as ‘less than 10%’ (< 10%). Please swap this to 9%.
4. Next, go into first tab’s pivot table and right-click ‘refresh data’
-
- once updated, select the dropdown triangle at the top of the ‘months’ column, and scroll to bottom of month options to select the latest month
-
-
-
- smooth out any formatting probs
-
-
-
- once updated, select the dropdown triangle at the top of the ‘months’ column, and scroll to bottom of month options to select the latest month
5. Next, we’ll Update color scales via the Conditional Formatting button
-
-
- highlight the cells for the ‘You’ scores column, not including the bottom average.
- then select the ‘home’ toolbar in excel to find the “conditional formatting” button (as shown above). in there, choose the color-scheme that matches the existing
- next, do the same thing for all the other data scores from competitors (highlight all cells except the bottom averages.
- choose the color scale for shades of green
- highlight the cells for the ‘You’ scores column, not including the bottom average.
-
6. look at data for insights and notable updates to share
- things to look out for include new rivals coming onto or leaving the ads market, changes in rival share, and/or what has changed vs previous month or YoY
- We can talk through a few insights based on the data, as needed
- If there is a large change in share for our clients, we can investigate further – just give me (Tom) a heads up and we’ll figure it out. This data from google has occasional hiccups where one week wont register correctly for nearly all advertisers.
- we saw this specifically with Pam Harrington’s reports a few months ago
7. Save excels with new dates and send share link my way or email tom.burkart@q4launch.com
8. Take screenshots of the report and save the files using existing naming conventions (with the new date range specified).
- These make it easier to send report to folks without Office
-
- You did it! woot~~
-
- =]