1/2/2023 0 Comments Run data analysis excel![]() Results from an Adwords API call usually look like this. You should now have 12 months of historical search volumes and averages for all your keywords. =arrayGetAdWordsStats(KeywordList,”EXACT”,”US”,”WEB”) ![]() Your formula should look something like this: In a new sheet, use the Adwords API array formula called “arrayGetAdWordsStats” to pull in the average and seasonal monthly search volumes for your keyword table. I know the Adwords API access is a bit of an issue for some, so if you cannot use the API, utilize the Google Adwords Keyword Tool (gathering data from this tool will unfortunately require a lot more work). Thanks to the wonderful Richard Baxter, there are a couple articles on using and installing the Adwords API Plugin. Next up is pulling in search volumes for our keyword table. To Be, Or Not To Be Searched, That Is The Question Pivot tables don’t have the same referencing abilities as regular tables, so the table in column B is what you will reference in future steps. We now have the keyword table to reference for extracting Adwords data. Label this table “KeywordList” or whatever you like. In the adjacent column create a table where the cells equal the values in the pivot table column. Now select the whole keyword column and create a pivot table of the keyword list in another sheet. Google Analytics data called through the API in Excel. Your Analytics data should look something like this: To see what other metrics can be used, check out the Analytics API documentation. However, you can modify the parameters to your liking. ![]() The query I use for example, will output visits, average time on site, page views, and bounces for any keyword with 5 or more visits in the last 30 days. We will be using the more complex query to extract organic keyword visits for a specific date field and filter by the number of visits. Once you have your API token and the spreadsheet setup you can perform your first API call. ![]() Make sure to build off the GA data fetch file or a copy of it, as it has the proper VBA functions (the Visual Basic code that allows for the API to work) installed for API calls. You can follow the instructions, read the how to guide, and download the file here. I suggest using Mikael Thuneberg’s GA Data Fetch spreadsheet. I Got 99 Problems, But A Keyword Visit Ain't Oneįirst off we need to get our keyword traffic metrics through the Google Analytics API. Here are some resources for learning to use pivot tables in Excel: If you aren’t familiar with these tools, I have provided resources below and some steps to organizing this data. Utilizing these APIs and being consistent in the formatting of the data you put into your spreadsheet will make it easy to update. To start, you should be especially familiar with pivot tables, the Google Adwords API, the Google Analytics API, and keyword research of course. Then we will put it all together into one master report and one categorized pivot table report. What we will need to do is push Google Analytics, Webmaster Tools, Adwords, Ranking data, and Revenue data all into one excel spreadsheet. Then with all this data we can easily categorize segments of it to more quickly determine the better performing sets of keywords. With Microsoft Excel, we can create a report with all the keyword data you will need, all in one place, and fairly easy to update on a weekly or monthly basis. This post will walk you through a solution to these keyword analysis issues and provide some tips on how you can slice and dice your data in wonderful ways. In addition, dependent on your site’s business model, tying revenue metrics to keyword data is a whole other battle. With keyword metrics coming from all over the place (Analytics, Adwords, Webmaster Tools, etc.), it’s challenging to analyze all the data in one place regularly without having to do a decent amount of manual data manipulation. Analyzing keyword performance, discovering new keyword opportunities, and determining which keywords to focus efforts on can be painstaking when you have thousands of keywords to review.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |