SEO SOP: Market Share Report

Market Share Report

Sample worksheet: Age of Sage - Market Share Report

I. Extract Data from Search Query Performance

Figure 1.

Head over to Brands and click on Brand Analytics. From there, click on View Search Analytics, which is located at the top-center of the page.

Figure 2.

Select the account, reporting range, year, as well as month from which you are pulling the data. For this training purpose, we shall be choosing monthly as the reporting range and 2022 as the target year.

Then, download the report, access the report, and paste all data into your worksheet. Use the current month and year as the title. For example, December 2022. See Figure 3.

When downloading data from the first month, download up to 100 search queries only. You can download up to 500 search queries for the succeeding months. This is because we want to compare the first month's data with the most recent data to determine any major differences.

Figure 3.

II. Search Query Organization

Using the template, on the raw monthly data tab, arrange the search queries using the search query score from A to Z. Then, copy all the figures in the following columns and paste them onto the Summary sheet:

• Search Query
• Rank 
• Search Query Volume
• Impressions - Brand Share (column F)
• Clicks - Brand Share (column J)
• Cart Adds - Brand Share (column S)
• Purchases - Brand Share (column AB)
  

The values in columns I-M will be calculated automatically since the formulas are already included. Be sure to do this for all the succeeding months. 

Furthermore, complete Search Query (column A) and the first rank data (column C) from the first month in the four brand share individual tabs. They must remain the same throughout the months/years, as they are static. Then, complete the data for the first month.

To continuously update the worksheet with the most recent month, apply the following formulas below:

•  [Current Rank]

    =IFERROR(VLOOKUP(firstsearchquery,'currentmonth'!A:B,2,false),"-")

• [Brand Share %  Increment]

=IFERROR(currentbrandshare-firstbrandshare,"-")

• [Search Query Volume] - for Impressions

=IFERROR(VLOOKUP(firstsearchquery,'currentmonth'!A:C,3,false),"-")

• [Total Count] - for Impressions

=IFERROR(VLOOKUP(firstsearchquery,'currentmonth'!A:D,4,FALSE),"-")

• [Brand Count] - for Impressions

=IFERROR(VLOOKUP(firstsearchquery,'currentmonth'!A:E,5,FALSE),"-")

• [Brand Share] - for Impressions

=iferror(vlookup(firstsearchquery,'currentmonth'!A:F,6,false),"-")

The formulas above are used to check the data in the current month column of a range and return the corresponding value from a target column if it exists. If there is an error, it will return a dash (-) instead.

Repeat the same process for the other data on Brand Share - Clicks, Cart Adds, and Purchases with the needed adjustments on the formulas. For example, on the Brand Share - Clicks tab, copy the same formulas applied in the Brand Share - Impressions tab, however, adjust the range from A:G and the target value from 4 to 7.

Figure 4.

Pro tip: For every Brand Share tab, use the following codes to guide you with the range and target values. Verify the placement of data at every start of the week to ensure the codes are still appropriate. 

• 4 5 6 – D E F  (Impressions)
• 7 9 10 – G I J  (Clicks)
• 16 18 19 – P R S  (Cart Adds)
• 25 27 28 – Y AA AB  (Purchases)
  

Next, on the Summary sheet, select the top search query with a significant average brand share difference. You can compare the most recent month vs. the previous month, or any from the previous months.

As a result of this training, you should be able to provide visibility into the performance of the top search terms associated with your brand, as well as provide an opportunity for development in other areas, such as Advertising or SEO.