This Analytics Edge wizard makes it easy to get your Google Search Analytics data into Excel.
Site – pick the website you want to report on. Optionally enter a value or use a worksheet cell reference (click the button).
Select – select any number of optional dimensions for your report. Add or remove selections from the Dimensions list. Note that Google will remove some of the data if you combine ‘query’ with other dimensions.
Dimensions – shows the dimensions you want in your results. You can drag-and-drop the entries to reorder them.
Metrics – all available metrics appear by default, but you can remove some if desired. You can also change the order of the metrics by dragging them in the list. Click the reset button to restore the default set of metrics.
Query – Add a filter for expressions that exactly match or contain certain phrases. Note that there is no wildcard match capability in the API. An optional worksheet cell reference can be used. [The filter does NOT accept regular expressions; only simple terms]
Page – Add a filter for expressions that exactly match or contains a portion of the page URL. An optional worksheet cell reference can be used. [The filter does NOT accept regular expressions; only simple terms]
Country – Select a country from the list. An optional worksheet cell reference can be used. You can use either the full country name or the short ISO-3116-1 alpha-3 code.
Device – Select a device (desktop/mobile/tablet) or use a worksheet cell reference to one.
Type – Select the type of query (web/image/video) or use a worksheet cell reference to one.
Appearance – Enter a search appearance term (run a query with the searchAppearance dimension to see which ones exist in your account). Optional worksheet cell reference can be used instead.
Start/Duration/End – Select a combination that defines the time period of interest. Optional worksheet cell references can be used. The combination must include 2 of the 3 options (start+end, start+duration or duration+end).
Compare – Check the box to add comparison columns for a second date range. You can select the date range as the previous period, same period in the previous year, or a custom start and end date.
Difference/% Change – Check these boxes to add a Difference and/or % Change column for each metric in your query.
Sort by – Select a field from the dropdown, then click Ascending or Descending to add that criteria to the list. Columns will be sorted by the first rule with duplicates sorted by the second rule, and so on. Note that the default sort is by clicks descending, with the rest of the columns unsorted (random).
Rows – Due to API limitations, all rows available will be downloaded with every query, but you can limit the number of rows written to your worksheet based on the sort criteria.
Date-based – Make multiple queries in the date range selected on the Dates tab. For example: ‘by 7-Day’ will make separate queries for each 7-day period. The ‘by Week…‘ queries will not return partial week periods. Combine this option with a date range of a multiple of 7 days (e.g. duration 12 weeks).
Note: The ‘by Month’ report will get monthly buckets based on your chosen date range. If you want calendar month reporting, make sure to select a start or end date at the beginning or end of a calendar month (e.g. end of Last Month).
By Device/Type – Make separate queries for each device or search type available.
By Search Appearance – This options runs a query to get all search appearance types for your website, then runs queries for each of them. Note that the results will vary by website and time period used.
By Top Pages/Queries/Countries – This option runs a query to obtain the top number of pages/queries/countries for the sorted metrics selected, then runs queries for each of the top entries. While you can enter any number, entering large numbers is not recommended because:
- the results must fit into your available memory and worksheet space
- each query counts against your daily limit for all reporting tools you use
- you increase the chances of a random (infrequent) server or network error
Layout Options – choose one of three layouts for your results.
- Vertical will append each query vertically, adding dimension columns for the multi-query dimension selected (e.g. start date and end date).
- Pivot – Group by Dimension will append each query horizontally, grouping all the metrics for each query together (by dimension). The column titles will include the [dimension] metric combination. e.g. [DESKTOP] clicks, [DESKTOP] impressions, [DESKTOP] ctr, …
- Pivot – Group by Metric will append the queries horizontally, but group similar metrics together. This would make it easier to create trend or comparison charts. e.g. [DESKTOP] clicks, [MOBILE] clicks, [TABLET} clicks, …