In previous posts in the series “About working in Excel for software specialists” contextual advertising"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 based on individual expanded audience segments, and so on. But any multifunctional tool requires certain operating skills. Analytics Edge interface for entry level users may find it quite complicated. In this article I will tell you about an analogue of Analytics Edge. It is much simpler, but it also imports all the information necessary for analysis from Google Analytics into Excel. We will talk 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 intuitive 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 you can import. If the result of your Google Analytics data queries does not exceed this limit, you will be more comfortable with this add-on. Disadvantage 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 queries 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 sufficient. I would encourage contextual professionals to start using Analytics Edge right away. Even if until now the volume 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 volume of downloaded data will significantly exceed this limit. At the same time, it will be more difficult to adapt to Excellent Analytics, since the main parameters (dimenisions) and indicators (metrics) in the settings are called a little differently.

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 for the number of visits and the number of goal achievements, instead of XXXX goal ID
metrics= ym:s:visits,ym:s:goaXXXXreaches

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

# filters for organic traffic and exclusion of branded queries via regular expression
filter= ym:s:lastSourceEngine=="organic.yandex" AND ym:s:lastSearchPhrase!~"brandQuery1|brandQuery2" AND ym:s:lastSearchPhrase!=null

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

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

Yandex is the main source of traffic for us, so from Google search We will not consider it within the scope of 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 K50 service

We 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 values keywords from the lemmas table.

That's it, task completed!

Semantic core filtering, 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 phrases to Key Collector and click on the filter icon in the “Phrase” column

Yandex Direct does not accept words with more than 7 words or 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 from our list phrases that contain stop words. A good collection of safe words can be found here

Exporting average bill and conversion by URL from Google Analytics

Theory

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

Value per click = Average check * Margin share in check * Website conversion

There is also a portfolio theory of setting bets, it allows you to increase profits by 10-20%, but we are not considering it within the scope of the article, so as not to complicate it.

What does this mean for us? - We need to collect historical data on conversion and average bill by site URL and key phrases. We will use this data to set bids.

Can't figure out what's what? Yes, it’s a little complicated, but you’ll understand everything when we combine all the data in one formula in the final article. Therefore, first things first.

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

Create 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 there are not enough explanations for the given parameters, but this may take us too far from the topic of the article. Detailed information you can find it in the documentation

In our request we used the following metrics and parameters:

ga:sessions- number of visits

ga:transactions- number of transactions

ga:transactionRevenue- revenue

ga:sourceMedium- attraction 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 we can later open the document in Excel - we change it.

For numerical values set the number format.

Since the ga:sourceMedium parameter duplicates some URLs, we build a summary table. At the same time, we clean it from unwanted values ​​and duplicates.

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

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

We carry out similar operations with the URL conversion table.

The entire document can be downloaded in Excel.

Exporting Yandex Direct data from Google Analytics

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

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

Start Index- initial line of the report

Max Results- last line of the report

The thing 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.

The output is the following:

That's it, we have collected data that we will work with in subsequent stages.

Write questions in the comments, what topics would be interesting to cover in more detail? If you have ideas or tips, please share!

In the last article, I talked about how to customize visualization if you are faced with Google Analytics limitations. This post 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 Excel add-in - 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, a sensible job description of this instrument you won't find it even on the official website. After installing Analytics Edge in Excel, you will have new inset 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. To add an account, on the Analytics Edge tab in the Connectors group, open the Free Google Analytics drop-down menu and select the Accounts command.
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 Gmail username - you can enter any name) so that your account will be displayed in the list of available ones in the future.
3.3. Next, click on Add Account and enter your email address and password to log into your Google Account (to which the Google Analytics account from which you plan to pull data in the future is linked). 3.4. In the dialog box that appears, click “Accept”.
3.5. If you did everything correctly, 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 box.

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 all the tabs in turn. 4.2.1 On the tab View You can select the Google Analytics view. When you select the account created in step 3, the default view that you set in step 3.6 will be selected. If you skipped step 3.6 and did not set default views, then the first view by ID will be selected from all those associated with your account. 4.2.2. Go to the tab Segments: Here you can select any advanced segment that exists in your selected Google Analytics view. In addition, you can choose between system and user segments, as well as the ability to create a dynamic segment. All system segments are listed after the System Segments header. In the Segment drop-down menu, you can select any of the system segments. All custom segments are located under the Segments drop-down list (under the System Segments heading). You can select any of the custom segments you created in Google Analytics that are available in the view you selected under the View tab.
If the created segments are in Google account Analytics isn't enough, you can create a dynamic segment right in the Analytics Edge interface. To do this, select DYNAMIC from the Segment drop-down menu, it will activate the Edit button. Click it to configure the dynamic segment.
After clicking Edit, the dynamic segment settings dialog box will open, which contains six more tabs. Let's look at each briefly:

• Demographics— segments traffic by demographic characteristics, such as age, gender, language, location of the user;
  
• Technology— segments traffic according to various technological criteria, for example, operating system user ( Operating System), browser (Browser), device type (Device category);
  
• Behavior— sorts users who have completed a certain number of sessions or 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 set ago (Days since last session);
  
• Date of first session— displays users who visited the site for the first time during the period you selected. For example, users who first visited the site in the period 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 sesseions) or user level (filter users). The difference between these modes is as follows: when filtering by sessions (and specifying organic as a channel), you will select all sessions that were made from the channel organic. If, with the same conditions (organic channel), you select the filtering mode by users, then you will select all user sessions that reached the site at least once through the organic channel:
  
• Ecommerce— designed to filter traffic by visitors who made transactions. In addition, you can select an individual transaction by number (Transaction ID) or by a certain level of income (Revenue), filter traffic with a certain number of days between visiting the site and completing the 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 intended for selecting dimensions and metrics. Because there are a number of limitations in the Google Analytics API, importing data using Analytics Edge also has some limitations. As for the selection of uploaded fields, you can select 7 dimensions (dimensions) and 10 metrics (metrics) in 1 request. Base architecture Google data Analytics also has a number of limitations in the various options for combining dimensions and metrics. When you select the required indicators and parameters, some of the items in the list of fields will be colored gray. This means that this field is not compatible with the dimensions and metrics you previously selected.
For example, if you select “Product” as a dimension, you cannot select “Clicks” as a metric because clicks occur on ads that are assigned to specific advertising campaigns, ad groups and keywords, but you can’t click on a specific product.

I will describe the key parameters (dimensions) and metrics (metrics) at the end of the article, in the directory of name correspondence in Google Analytics, Analytics Edge, Excellent Analytics and in the directory of dimensions (dimensions) and metrics (metrics) of Google Analytics. 4.2.4. Tab Filters Its meaning is similar to the Segment tab. The difference between filters and segments is that filters set on the Segment tab check the selection parameters you set for each session, and filters set on the Filters tab are applied to the resulting 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 match the condition “Session duration > 6000 seconds” . You will get the following result: A filter with the same condition “Session duration > 6000 seconds” 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 days in which the total number of seconds spent on the site by visitors is less than 6000. If you compare the results, the difference is obvious, since these filters have different areas of application. 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 the conditions are ultimately applied to the final data. More clearly about the operation of the Filters tab. If you add the “total duration of sessions” indicator 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 like this: 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 was 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 metrics (metrics). You can also combine conditions by placing different logical dependencies between them and/or (and/or).
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 select 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 sorting parameters 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 sorting options: Ascending or 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 period), you can specify limit quantity rows in the MaxResults field. The default value is 0, which means no limit.
4.2.7. Tab Options is intended mainly for setting the format for transferring data to Excel. With Rates/Percent, you can choose to display relative metrics, such as bounce rate, in numeric rather than percentage format. The Dates clause solves the problem of transmitting date data. By Google default Analytics passes the date value as an eight-digit number. For example, the date 10/01/2015 will be transmitted as 20151001. After setting the switch to Excel Date, Analytics Edge will automatically transform dates into the usual Excel format. Numeric dimention is responsible for parameters that contain numeric elements. For example, the Count of session parameter shows how many sessions a particular user has currently had, and is transmitted as a number. But, since this field is a parameter and not an indicator, you will not be able to perform any calculations with it; it is used for comparative analysis user behavior depending on the number of previously completed sessions. This field is most convenient to use in text rather than numeric form. To do this, set the switch to the String position. With Time metrics you can translate time metrics such as Session duration from number format in time format. By default, a session with a two-minute duration will be imported into Excel as the number 120, which means 120 seconds. If you set the switch to Days, reports loaded using Analytics Edge will show a two-minute session as 00:02:00. The Sampled data item is intended for notifications when data is sampled, as well as to minimize sampling. If you check the box next to Warn if resultcontain sampled data, you will receive a notification (if 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 upload data by month, a separate subquery 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-on, you can set up data visualization and use the full power of the toolkit Microsoft Excel. P.S.: As promised, I provide a reference table of the main parameters and Google metrics Analytics in Analytics Edge. Directory 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	Ad Group	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:No. (reached transitions to goal No.)	Goal № completions	ga:goalXXCompletions
Achieved goals	Goal Completions	ga:goalCompletionsAll
Users	Users	ga:users
New users	New Users	ga:newUsers
Transactions	Transactions	ga:transactions
Product income	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 manual on the service website as an official Russian-language manual.

To export a report, follow these steps:

1. Open the desired report. Google Analytics reports export the content you see on your screen. Therefore, make sure that the date range and other settings are correct.
2. Click Export(under the report title).
3. Select 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.

Not added to export file line graph, which is created when the animated graph runs.

Share the report

For each user and view, the number of scheduled reports sent by e-mail, cannot exceed 400.

To send a report by email, follow these steps:

The set of data in the letter depends on the time zone that you specified in the presentation settings. The email itself is sent after midnight in the selected time zone, but the exact delivery time cannot be guaranteed.

Greetings!

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

Data Display

In any report, for clarity, data can be displayed in different ways. For this there is 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 take a closer look at them.

1) Tabular view

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

This diagram shows the ratio of shares. Convenient to use for estimating traffic volumes with search engines. In general, a pie chart is great for displaying shares of data 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 diagram it is convenient to compare data according to 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 diagram 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 much the average conversion rate for the site differs from the conversion rate for each keyword.

If the color is green, then the keyword's conversion rate 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 breaks down the data you need into several dimensions. In the “Summary by” menu (1), simply select the parameter for which you want to see detailed statistics. This picture shows a keyword report with 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.

This report also allows for double selection (2). For example, if here you specify the conversion level as the second parameter, you will be able to immediately see the volume of traffic and its effectiveness for each keyword for each search engine. A report like this will help you identify quality and efficiency search engine optimization(SEO).

The last type of data display is simply the total values ​​of metrics for current traffic for a selected period of time.

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

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

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

Update:
What to do if you need to download a large amount of data? For example, a table of 5000 keywords. It is very tedious to leaf through 500 pieces and export each sheet separately. But there is a simple solution, one secret trick.

It works like this:
1. Open the report you need to export
2. IN address bar browser, add the text “&limit=5000” (without quotes) to the current url
3. Press Enter, the page will reload, but visually nothing will change
4. After that, export the data only in "CSV" format
5. As a result, you will receive 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, this can be any number up to 20000. This technique only works for exporting in the “CSV” format (for “CSV for Excel” it will not work).

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

If you need additional data processing, or some calculations based on it, then it is better to export the data to Excel. If you have Excel version before 2007, it is better to use CSV formats. At the same time, the “CSV for Excel” format is already formatted as required and can be immediately opened in Excel. But if you download a report in 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 upload, Google Analytics uses a dot as a separator for 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, you need to indicate in the last window of the Excel Export Wizard that the file uses a dot as a separator between the integer and fractional parts.

In the circled window 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 the 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 in the required format You can send it 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 (the “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 results of your activities in a visual form (comparing results between the two previous weeks). Just keep in mind that Google Analytics sends emails according to the time zone selected in your account. Therefore, if the belt chosen is not yours, then the letters will arrive at a different time.

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

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

Tell your friends about this, in case they find it useful.