In previous posts in the "Working in Excel for PPC Professionals" series, I talked about the Google Analytics Add-on for Google Sheets and the capabilities of the Analytics Edge add-on. Of course, Edge is a very powerful tool that allows you to avoid data sampling, upload data taking into account certain extended audience segments, and so on. But any multifunctional tool requires certain skills. The Analytics Edge interface for entry-level users can seem quite complex. In this article, I will tell you about the analogue of Analytics Edge. It is much simpler, but it also imports all the information needed for analysis from Google Analytics into Excel. We are talking about the Excellent Analytics add-on.

How is Excellent Analytics different from Analytics Edge?

The difference between the services is that Excellent Analytics has a more convenient and understandable interface, but with a limit of 10,000 rows uploaded per request. Analytics Edge has a more complex interface, but there is no limit on the number of rows that can be imported. If the result of your queries against Google Analytics data does not exceed this limit, this add-on will be more convenient for you. Cons of Excellent Analytics: Unlike Analytics Edge, Excellent Analytics does not have the ability to create dynamic segments. Edge also has the ability to minimize data sampling by splitting requests by date, week, or month. Excellent Analytics does not have this option, but if your site's monthly traffic does not exceed 10,000 - 15,000 thousand users, then the functionality of this add-on will be enough. I would advise contextualists to get started using Analytics Edge right away. Even if until now the amount of data requested from Google Analytics has not exceeded 10,000 rows, it is likely that in the future you will encounter a task where the amount of data uploaded will significantly exceed this limit. At the same time, it will be more difficult to rebuild with Excellent Analytics, since the main parameters (dimenisions) and indicators (metrics) in the settings are called a little differently.

In the last article, I talked about how to customize visualization if you are faced with the limitations of Google Analytics. In this post, we will talk about how to do the same, but in Excel with its endless possibilities for visualizing information.

1. How to get started with Analytics Edge

To set up data import from Google Analytics, you will need to install the add-in for Excel - Analytics Edge. You can download it from the official website of the developer. Since the add-on is free, the developers didn't put much effort into writing detailed tutorials. Therefore, you will not find an explanatory description of the operation of this tool even on the official website. After installing Analytics Edge in Excel, you will have a new tab with the same name, it will look something like this:

2.1 Go to the Analytics Edge tab and in the Connectors group open the Free Google Analytics menu. Next, select License from the drop-down menu.
2.2. In the dialog box that appears on the Connector tab, click the Activate Free License button, after which the add-on will notify you of successful activation. You can start importing data.

3. Now you need to add a Google Analytics account from which you will import data

3.1. In order to add an account, on the Analytics Edge tab in the Connectors group, open the Free Google Analytics drop-down menu, select the Accounts command in it.
3.2. In the Analytics Accounts dialog box that opens, in the Reference name field, enter the name of the account (it is not necessary to enter the exact login on Gmail - you can enter any name) so that in the future your account will be displayed in the list of available ones.
3.3. Next, click on Add Account and enter the email address and password to enter your Google Account (to which the Google Analytics account is linked, from which you plan to pull data in the future). 3.4. In the dialog that appears, click "Accept".
3.5. If you did everything correctly, then when you return to the first Analytics accounts dialog box, the added account will appear in the Saved Google Analytics Logins group.
3.6. Next, you can specify the Google Analytics account, property, and view that will be set by default when you select the Google account you added.
3.7. Click Close to close the Analytics Account dialog.

4. Setting up data import from Google Analytics

4.2. The main Analytics Edge Wizard dialog box opens with seven main tabs.
Let's look at each tab in turn. 4.2.1 On the tab view you can select the Google Analytics view. When you select the account created in paragraph 3, the default view that you set in paragraph 3.6 will be selected. If you skipped paragraph 3.6 and did not set the default views, then the first view by ID will be selected from all those associated with your account. 4.2.2. Go to tab segments: Here you can select any rich segment that exists in your chosen Google Analytics view. In addition, there is a choice between system and user segments, as well as the ability to create a dynamic segment. All system segments are listed after the System Segments heading. In the Segment drop-down menu, you can select any of the system segments. All custom segments are under the Segments drop-down list (under the heading System Segments). You can select any of the Google Analytics custom segments that you have created that are available in the view of your choice on the View tab.
If you don't have enough segments in your Google Analytics account, you can create a dynamic segment directly from the Analytics Edge interface. To do this, select the DYNAMIC item from the Segment drop-down menu, it activates the Edit button. Click it to set up a dynamic segment.
After clicking Edit, the Dynamic Segment Settings dialog will open with six more tabs. Let's take a quick look at each:

Demographics- segments traffic by demographic characteristics, such as age (age), gender (gender), language (language), user location (location);
Technology- segments traffic according to various technological criteria, for example, by the user's operating system (Operating System), browser (Browser), device type (Device category);
Behavior- sorts users who have made a certain number of sessions (sessions) or transactions (transactions) on the site. You can also select sessions with a duration of more or less than the number of seconds you set (Session Duration), or select users who were on the site earlier or later than the number of days you specified (Days since last session);
Date of first session- displays users who first visited the site in the period you selected. For example, users who first visited the site from 01/10/2015 to 01/20/2015;
traffic sources- tracks traffic by advertising campaign (Campaign), channel (Medium), source (Source) and keyword (Keyword). The functionality of this tab allows you to apply a filter at the session level (filter sessions) or user (filter users). The difference between these modes is as follows: in filtering by sessions (and specifying organic as a channel), you will select all sessions that were committed from the channel organic. If with the same conditions (organic channel) you select the filtering mode by users, then you will select all sessions of users who at least once got to the site through the organic channel:
E-commerce— designed to filter traffic by visitors who have made transactions. In addition, you can select a separate transaction by number (Transaction ID) or by a certain income level (Revenue), filter traffic with a certain number of days between visiting the site and making a transaction (Days to Transaction). You can also sort transactions by a specific product (Product) or product category (Product Category).

4.2.3. Tab Fields is designed to select parameters (dimensions) and indicators (metrics). Since there are a number of restrictions in the Google Analytics API, importing data using Analytics Edge also has some restrictions. As for the choice of uploaded fields, it is possible to select 7 parameters (dimensions) and 10 indicators (metrics) in 1 request. The architecture of the Google Analytics database also has a number of limitations in various options for combining dimensions (dimensions) and indicators (metrics). When you select the required indicators and parameters, some of the items in the list of fields will be grayed out. This means that this field is not compatible with the dimensions and metrics you selected earlier.
For example, if you selected "Product" as a parameter, you cannot select "Clicks" as a metric, since clicks occur on ads that are assigned to certain advertising campaigns, ad groups and keywords, but you cannot click on a specific product .

I will describe the key dimensions (dimensions) and indicators (metrics) at the end of the article, in the directory of matching names in Google Analytics, Analytics Edge, Excellent Analytics and in the directory of dimensions (dimensions) and indicators (metrics) of Google Analytics. 4.2.4. Tab Filters similar in meaning to the Segment tab. The difference between filters and segments is that the filters set on the Segment tab check the selection parameters you set for each session, and the filters set on the Filters tab are applied to the final aggregated data. For example, the filter "Session duration > 6000 seconds", applied on the Segments tab, when receiving a report on the number of sessions by day for the period 03/01/205 to 03/10/2015, will select and show the number of sessions for each day that meet the condition "Session duration > 6000 seconds" . You will get the following result: A filter with the same "Session duration > 6000 seconds" condition on the Filters tab will work completely differently. In this case, the filter will initially calculate the number of all sessions per day and the total number of seconds spent by visitors for each day on the site, after which it will remove from the report the days in which the total amount of seconds spent on the site by visitors is less than 6000. If we compare the results, the difference is obvious, since these filters have different applications. In the case of Segments, the selected conditions are applied to each session, and in the case of Filters, the entire report is initially generated and, as a result, the conditions are applied to the final data. Even more clearly about the work of the Filters tab. If we add the indicator “total duration of sessions” to the number of sessions in the report and set the filter values not > 6000 but more than 12,000,000, then the result will be as follows: The screenshot shows that March 5, 6, 7 were not included in the report due to the fact that the total number of seconds spent by all visitors on the site for these dates is less than 12,000,000 seconds. The conclusion suggests itself: on the Filters tab, you can filter the final data by setting any values for any parameters (dimension) and indicators (metrics). You can also combine conditions by setting different logical dependencies and/or (and/or) between them.
4.2.5. On the tab dates you must specify the period for which you plan to import data. There are several options for choosing a period.

dynamic (preset) - you can select any period that will move daily (for example, the last 30 days (last_30_days), and with each update, the reports will display data for the last 30 days - so you can choose yesterday (yesterday), today (today ), last 7 and last 14 days);
static start date (start) — the number of days for which you plan to download the report, starting from the set start date (duration). Here you can also specify a static end date for the report (end).

4.2.6. On the tab Sort/Count you can set the sorting options for the output data and limit the number of rows displayed as a result of the query. To set sorting in the Sort by drop-down list, select any field. Then click one of the two options for sorting in ascending (Ascending) or descending (Descending). You can add any number of fields: the final query will be sorted in the same order as you specify on the Sort/Count tab. To limit the number of rows in a query result (very large queries require a longer processing time), you can specify a row limit in the MaxResults field. The default value is 0, which means no limit.
4.2.7. Tab Options is intended mainly for setting up the data transfer format in Excel. With Rates/Percent, you can customize the display of relative metrics, such as bounce rate, in a numeric format rather than a percentage. The Dates clause solves the problem of passing data about dates. By default, Google Analytics passes the date value as an eight-digit number. For example, the date 10/01/2015 will be passed as 20151001. After setting the switch to the Excel Date position, Analytics Edge will automatically convert the dates to the format familiar to Excel. Numeric dimention is responsible for parameters that contain numeric elements. For example, the parameter Count of session (the number of sessions) shows how many sessions have been completed by a certain user at the current moment, and is passed as a number. But since this field is a parameter, not a metric, you won't be able to perform any calculations with it, it serves to compare user behavior depending on the number of previous sessions. This field is most conveniently used in text form, not in numerical form. To do this, set the switch to String. Using Time metrics, you can convert temporal metrics such as Session duration from a number format to a time format. By default, a two-minute session will be imported into Excel as the number 120, which means 120 seconds. If you set the toggle to Days, reports loaded using Analytics Edge will display a two-minute session as 00:02:00. The Sampled data item is intended for notifications when sampling data, as well as for minimizing sampling. If you check the box next to Warn if resultcontain sampled data, you will receive a notification (in case your request contains sampled data). After checking the box next to Minimize sampling, Edge will minimize sampling and split your request into the maximum number of parts over time. If you are uploading data by month, a separate sub-request will be sent for each month. The same applies to detailing by dates and weeks.
Finally click on Finish and the data is loaded into the Excel sheet. Hooray! So, once you understand the functionality of the Analytics Edge add-in, you can set up data visualization and use the full power of the Microsoft Excel toolkit. P.S.: As promised, I am giving a reference table of the main parameters and indicators of Google Analytics in Analytics Edge. Reference book of basic parameters

Google Analytics	Analytics Edge	API Reference
Source	Source	ga:source
Channel	Medium	ga:medium
View Depth	Page Depth	ga:pageDepth
Region	region	ga:region
City	City	ga:city
Session duration	Session Durations	ga:sessionDurationBucket
Days since last session	Days Sinece Last Session	ga:daysSinceLastSession
User type	User Type	ga:userType
Device type	device category	ga:deviceCategory
Number of sessions	Count of Sessions	ga:sessionCount
Ad group	AdGroup	ga:adGroup
Campaign	Campaign	ga:campaign
Keyword	keyword	ga:keyword
Product Category	product category	ga:productCategory
Product	Product	ga:productName

Directory of key indicators

Google Analytics	Analytics Edge	API Reference
Sessions	sessions	ga:sessions
Failures	Bounces	ga:bounces
Session duration	Session Durations*	ga:sessionDuration
Goal:# (achieved transitions to goal#)	Goal No. completions	ga:goalXXCompletions
Achieved goals	Goal Completions	ga:goalCompletionsAll
Users	Users	ga:users
New Users	New Users	ga:newUsers
Transactions	Transactions	ga:transactions
Product Revenue	Product Revenue	ga:itemRevenue
Impressions	Impressions	ga:impressions
clicks	clicks	ga:adClick
Price	cost	ga:adCost

UPD. The creators of Analytics Edge have added a link to this guide on the service website as an official Russian-language manual.

Greetings!

Today is the third article on working with Google Analytics reports. In it, I will talk about the different types of data display in reports, and how to save them to your computer (this may be necessary for additional data processing). As an example, I will use reports to evaluate SEO.
For those who missed the previous articles on Google Analytics reports, here are the links:

Data display

In any report, for clarity, data can be displayed in different ways. To do this, there is a special menu in the upper right corner of each table.

It consists of 6 buttons, each of which switches the type of data display. Let's consider them in more detail.

1) Table View

This is the current table, all reports are presented in this form by default.

This chart shows the ratio of shares. It is convenient to use to estimate the amount of traffic from search engines. In general, a pie chart is great for displaying data shares if there are no more than 6 elements. By the way, when using any type of display, you can select separate indicators for the table (1) and for the chart (2):

With the help of such a chart, it is convenient to compare data on one indicator, the difference is clearly visible. For example, in this figure, the chart displays the bounce rate.

This chart allows you to quickly find significant deviations in values. For example, highlight the highest quality traffic. The chart shows the ratio of the indicator for the selected traffic segment to this indicator on average for the entire site. For example, in this figure, the chart shows how the average conversion rate for the site differs from the conversion rate for each keyword.

If green, then the conversion rate of the keyword is higher than the average for the site, and red means less. Thanks to this chart, you can quickly identify effective and ineffective keywords.

5) Pivot table

This type of data display decomposes the data you need according to several parameters. In the "Summary by" menu (1), simply select the parameter for which you want to see detailed statistics. In this picture, the keywords report has the source summary selected. As a result, we can see the volume of traffic from each search engine, for each keyword. This will determine the quality of SEO for each search engine.

You can also make a double sample (2) in this report. For example, if you specify the conversion level as the second parameter here, then you can immediately see the amount of traffic and its effectiveness for each keyword for each search engine. A report like this will help you identify the quality and effectiveness of search engine optimization (SEO).

The last type of data display is simply the totals of metrics for current traffic for the selected period of time.

If you need additional data processing from the report, then you can upload it to your computer via export. At the top of each report there is an "Export" button:

By clicking on it, you can choose the format in which the data will be saved to your computer.

In principle, everything is clear here. It should be noted that in PDF the report will be saved exactly in the form of display and in the volume (number of lines) in which it is currently presented on the screen (together with graphics). In other formats, a tabular presentation of data without graphics will be saved. Moreover, the table will contain all tabs, even if you currently have one open. And the number of rows in the table will be the same as on your screen. If you need to save more lines, then you need to display more of them on the screen.

update:
What if you need to upload a large amount of data? For example, a table of 5000 keywords. Scrolling through 500 pieces and exporting each sheet separately is very tedious. But there is a simple solution, one secret trick.

Open the report you want to export
In the address bar of the browser, add the text "&limit=5000" to the current url (without quotes)
Press Enter, the page will reload, but visually nothing will change
After that, export the data only in "CSV" format
As a result you will get a .csv file with 5000 table rows

That is, in fact, in the report url, through the limit parameter, you can specify the number of table rows to export, it can be any number up to 20000. This technique only works for exporting in the "CSV" format (it will not work for "CSV for Excel").

The PDF format is more suitable for printing reports. Or if you need to show them to someone (for example, bosses or a client), without access to Google Analytics.

If you need additional data processing, or some calculations based on them, then it is better to export the data to Excel. If you have a version of Excel before 2007, then it is better to use CSV formats. At the same time, the "CSV for Excel" format is already formatted as needed and can be immediately opened in Excel. But if you download the report in the “CSV” format, then you need to insert it into Excel through the menu “Data -> Import external data -> Import data”.

By the way, with any type of export for Excel, there is one problem that not everyone knows about. The fact is that when generating data unloading, Google Analytics uses a dot as a separator between the integer and fractional parts in numbers, and Excel uses a comma by default. Therefore, after exporting the data, Excel may not understand your numbers and refuse to use them in calculations.

To avoid this problem, in the last window of the Excel Export Wizard, specify that the file uses a dot as a decimal separator.

In the circled box, you need to select a point. If you are exporting data in the "CSV for Excel" format, then after opening the file in Excel, select the entire range with data and press Ctrl + H (AutoCorrect). In the window that appears, specify the replacement "." to "," and click "Replace All".

Email

Next to the Export button is an Email button. It is also available in all reports. By clicking on it, a window will open in front of you:

Using this form, the current report in the required format can be sent by e-mail, to yourself or to one of your colleagues. Moreover, you can make sure that the necessary reports are sent out at a certain frequency: daily, weekly, once a month, once a quarter (“Schedule” tab). In this case, the function of comparing data with previous periods will be available. This is very convenient, every Monday you can send your boss / client a neat PDF report on traffic from search engines, which will reflect the result of your activity in a visual form (comparison of results between the previous two weeks). Just keep in mind that Google Analytics sends emails according to the time zone that is selected in your account. Therefore, if the belt is not yours, then letters will come at a different time.

Well, basically, that's all I wanted to tell you about the reports. I hope the material was useful to you. If you have any questions, or I suddenly missed something, then write in the comments.

In the following notes on SEO assessment in Google Analytics, we will talk about specific techniques.

Tell your friends about it, they might need it.

To export a report, do the following:

Open the required report. The content you see on the screen is exported to Google Analytics reports. Therefore, make sure that the date range and other settings are correct.
Click Export(below the title of the report).
Choose one of the export formats:
- TSV (for Excel)
- Excel (XLSX)
- Google Sheets

The file will be automatically created in the downloads folder on your computer.

The export file does not include the line graph that is created when you run an animated graph.

Report Sharing

For each user and view, the number of scheduled email reports is limited to 400.

To send a report by email, follow these steps:

The time zone you specified in the view settings determines the data set in the email. The email itself is sent after midnight in the selected time zone, but the exact delivery time cannot be guaranteed.

After reading the articles, you will learn how to: optimize campaigns in Excel using methods that are used in conversion optimizers; automatically collect semantics, segment and create ads; predict conversion based on history and much more.

In our request, we used the following parameters:

# metrics of the number of visits and the number of goal achievement, instead of XXXX ID of the goal
metrics= ym:s:visits,ym:s:goaXXXXreaches

# source parameters, login page and search query
demensions= ym:s:lastSourceEngine,ym:s:startURLPathFull,ym:s:lastSearchPhrase

# filters for organic traffic and exclusion of branded queries through a regular expression
filter= ym:s:lastSourceEngine=="organic.yandex" AND ym:s:lastSearchPhrase!~"brandedQuery1|brandedQuery2" AND ym:s:lastSearchPhrase!=null

After clicking Invoke, you will see a preview of your data. If an error occurred during the request, then we can edit the request by clicking on the Source gear

If everything is fine, then click Close and load and load all the data into the table.

Yandex is the main source of traffic for us, so we will not consider Google search within the article, so as not to complicate

Normalization and filtering of the semantic core

Normalization is the reduction of all words to the singular nominative case, etc. For this we use the service K50

Copy the data from the lemmas.csv file to our main file in the Lemmas tab. Using the vlookup function (in Russian Excel VLOOKUP), we pull up the lemmatized keyword values from the lemmas table.

Everything, the task is completed!

Filtering the semantic core, cleaning

Now we have a lemmatized list of phrases and we need to clear it of phrases that do not meet the requirements of Yandex Direct. To do this, add all the phrases to the Key Collector and click on the filter icon in the "Phrase" column

Yandex Direct does not accept words with more than 7 words and phrases with special characters as phrases, so we delete them.

Next, we filter the words through the list of stop words, that is, we remove phrases that contain stop words from our list. A good collection of stop words is at the link

Downloading average check and conversion from Google Analytics by URL

Theory

According to the properties of the Yandex Direct and Google Adwords auction, in order to maximize profits, we need to set the value of a keyword click as a bid

Click Value = Average Check * Margin Share in Check * Site Conversion

There is also a portfolio theory of setting rates, it allows you to raise profits by 10-20%, but we will not consider it within the framework of the article so as not to complicate things.

What does this mean for us? - We need to collect historical data on conversion and average check in the context of site URL and key phrases. We will use this data to set bids.

Can't connect, what's what? Yes, it's complicated, but you will understand everything when we combine all the data in one formula in the final article. Therefore, about everything in order.

First, let's collect average checks and conversions for all site URLs, it's simple. We can take this data from the history of Google Analytics. To do this, you will need Google Spread Sheets and the Google Analytics Addon, which you can install in the addon store.

Creating a new report

Enter test, select your counter and Google Analytic view, and click "Create report"

Enter the report configuration, as in the picture, and click Run reports. I understand that explanations for the given parameters are not enough, but this may take us too far from the topic of the article. You can find detailed information in the documentation

In our query, we used the following metrics and parameters:

ga:sessions- number of visits

ga:transactions- number of transactions

ga:transactionRevenue- revenue

ga:sourceMedium- acquisition channel

ga:landingPagePath- login page

Now we copy the reports to new tabs and paste only the values. Now we need to change the dots to commas, so that later we can open the document in Excel - we change.

For numeric values, set the number format.

Since the ga:sourceMedium parameter duplicates some URLs, we build a pivot table. Along the way, we clean from unwanted values and duplicates.

Add a new calculated field = "ga:transactionRevenue" / "ga:transactions" , this is the average check.

As a result, we have a neat table with URLs and average checks.

We carry out similar operations with the conversion table by URL.

The entire document can be downloaded in Excel.

Uploading Yandex Direct data from Google Analytics

Upload from Google Analytics, as we did a few steps earlier. The screenshot shows an example of a report configuration. In the Filters field, we use regular expressions.

ga:adContent=~.*search_none.*- filter out only clicks from the search, excluding YAN; provided you have the corresponding parameter in the UTM tag

Start Index- start line report

Max Results- the last line of the report

The fact is that the report has a limit of 10,000 rows, if you have more data, then you call the same report several times and change the Start Index and Max Results to 10001 and 20000 and so on.

At the output we get the following