Tag Archives: Functions

Number Format

Analytics Edge supports Microsoft .NET Framework format codes which are slightly different from Excel format codes. The common variants are listed below, but see the references at the end for a full listing.

Format specifier Name Description Examples
“0” Zero placeholder Replaces the zero with the corresponding digit if one is present; otherwise, zero appears in the result string. 1234.5678 (“00000”) -> 01235
0.45678 (“0.00”, en-US) -> 0.46
0.45678 (“0.00”, fr-FR) -> 0,46
“#” Digit placeholder Replaces the “#” symbol with the corresponding digit if one is present; otherwise, no digit appears in the result string. 1234.5678 (“#####”) -> 1235
0.45678 (“#.##”, en-US) -> .46
0.45678 (“#.##”, fr-FR) -> ,46
“.” Decimal point Determines the location of the decimal separator in the result string. 0.45678 (“0.00”, en-US) -> 0.46
0.45678 (“0.00”, fr-FR) -> 0,46
“,” Group separator and number scaling Serves as both a group separator and a number scaling specifier. As a group separator, it inserts a localized group separator character between each group. As a number scaling specifier, it divides a number by 1000 for each comma specified. Group separator specifier:
2147483647 (“##,#”, en-US) -> 2,147,483,647
2147483647 (“##,#”, es-ES) -> 2.147.483.647
Scaling specifier:
2147483647 (“#,#,,”, en-US) -> 2,147
2147483647 (“#,#,,”, es-ES) -> 2.147
“%” Percentage placeholder Multiplies a number by 100 and inserts a localized percentage symbol in the result string. 0.3697 (“%#0.00”, en-US) -> %36.97
0.3697 (“%#0.00”, el-GR) -> %36,97
0.3697 (“##.0 %”, en-US) -> 37.0 %
0.3697 (“##.0 %”, el-GR) -> 37,0 %
“‰” Per mille placeholder Multiplies a number by 1000 and inserts a localized per mille symbol in the result string. 0.03697 (“#0.00‰”, en-US) -> 36.97‰
0.03697 (“#0.00‰”, ru-RU) -> 36,97‰
“E0”
“E+0”
“E-0”
“e0”
“e+0”
“e-0”
Exponential notation If followed by at least one 0 (zero), formats the result using exponential notation. The case of “E” or “e” indicates the case of the exponent symbol in the result string. The number of zeros following the “E” or “e” character determines the minimum number of digits in the exponent. A plus sign (+) indicates that a sign character always precedes the exponent. A minus sign (-) indicates that a sign character precedes only negative exponents. 987654 (“#0.0e0”) -> 98.8e4
1503.92311 (“0.0##e+00”) -> 1.504e+03
1.8901385E-16 (“0.0e+00”) -> 1.9e-16
\ Escape character Causes the next character to be interpreted as a literal rather than as a custom format specifier. 987654 (“\###00\#”) -> #987654#
‘string’
“string”
Literal string delimiter Indicates that the enclosed characters should be copied to the result string unchanged. 68 (“# ‘ degrees'”) -> 68 degrees
68 (“#’ degrees'”) -> 68 degrees
; Section separator Defines sections with separate format strings for positive, negative, and zero numbers. 12.345 (“#0.0#;(#0.0#);-\0-“) -> 12.35
0 (“#0.0#;(#0.0#);-\0-“) -> -0-
-12.345 (“#0.0#;(#0.0#);-\0-“) -> (12.35)
12.345 (“#0.0#;(#0.0#)”) -> 12.35
0 (“#0.0#;(#0.0#)”) -> 0.0
-12.345 (“#0.0#;(#0.0#)”) -> (12.35)
Other All other characters The character is copied to the result string unchanged. 68 (“# °”) -> 68 °

Continue reading

Date Format

Analytics Edge supports Microsoft .NET Framework date format codes which are slightly different from Excel date format codes. The common variants are listed below, but see the references at the end for a full listing.

Format specifier Description Examples
“d” The day of the month, from 1 through 31. 6/1/2009 1:45:30 PM -> 1
6/15/2009 1:45:30 PM -> 15
“dd” The day of the month, from 01 through 31. 6/1/2009 1:45:30 PM -> 01
6/15/2009 1:45:30 PM -> 15
“ddd” The abbreviated name of the day of the week. 6/15/2009 1:45:30 PM -> Mon (en-US)
6/15/2009 1:45:30 PM -> Пн (ru-RU)
6/15/2009 1:45:30 PM -> lun. (fr-FR)
“dddd” The full name of the day of the week. 6/15/2009 1:45:30 PM -> Monday (en-US)
6/15/2009 1:45:30 PM -> понедельник (ru-RU)
6/15/2009 1:45:30 PM -> lundi (fr-FR)
“h” The hour, using a 12-hour clock from 1 to 12. 6/15/2009 1:45:30 AM -> 1
6/15/2009 1:45:30 PM -> 1
“hh” The hour, using a 12-hour clock from 01 to 12. 6/15/2009 1:45:30 AM -> 01
6/15/2009 1:45:30 PM -> 01
“H” The hour, using a 24-hour clock from 0 to 23. 6/15/2009 1:45:30 AM -> 1
6/15/2009 1:45:30 PM -> 13
“HH” The hour, using a 24-hour clock from 00 to 23. 6/15/2009 1:45:30 AM -> 01
6/15/2009 1:45:30 PM -> 13
“K” Time zone information. 6/15/2009 1:45:30 PM, Kind Unspecified ->
6/15/2009 1:45:30 PM, Kind Utc -> Z
6/15/2009 1:45:30 PM, Kind Local -> -07:00 (depends on local computer settings)
“m” The minute, from 0 through 59. 6/15/2009 1:09:30 AM -> 9
6/15/2009 1:09:30 PM -> 9
“mm” The minute, from 00 through 59. 6/15/2009 1:09:30 AM -> 09
6/15/2009 1:09:30 PM -> 09
“M” The month, from 1 through 12. 6/15/2009 1:45:30 PM -> 6
“MM” The month, from 01 through 12. 6/15/2009 1:45:30 PM -> 06
“MMM” The abbreviated name of the month. 6/15/2009 1:45:30 PM -> Jun (en-US)
6/15/2009 1:45:30 PM -> juin (fr-FR)
6/15/2009 1:45:30 PM -> Jun (zu-ZA)
“MMMM” The full name of the month. 6/15/2009 1:45:30 PM -> June (en-US)
6/15/2009 1:45:30 PM -> juni (da-DK)
6/15/2009 1:45:30 PM -> uJuni (zu-ZA)
“s” The second, from 0 through 59. 6/15/2009 1:45:09 PM -> 9
“ss” The second, from 00 through 59. 6/15/2009 1:45:09 PM -> 09
“t” The first character of the AM/PM designator. 6/15/2009 1:45:30 PM -> P (en-US)
6/15/2009 1:45:30 PM -> 午 (ja-JP)
6/15/2009 1:45:30 PM -> (fr-FR)
“tt” The AM/PM designator. 6/15/2009 1:45:30 PM -> PM (en-US)
6/15/2009 1:45:30 PM -> 午後 (ja-JP)
6/15/2009 1:45:30 PM -> (fr-FR)
“y” The year, from 0 to 99. 1/1/0001 12:00:00 AM -> 1
1/1/0900 12:00:00 AM -> 0
1/1/1900 12:00:00 AM -> 0
6/15/2009 1:45:30 PM -> 9
“yy” The year, from 00 to 99. 1/1/0001 12:00:00 AM -> 01
1/1/0900 12:00:00 AM -> 00
1/1/1900 12:00:00 AM -> 00
6/15/2009 1:45:30 PM -> 09
“yyyy” The year as a four-digit number. 1/1/0001 12:00:00 AM -> 0001
1/1/0900 12:00:00 AM -> 0900
1/1/1900 12:00:00 AM -> 1900
6/15/2009 1:45:30 PM -> 2009
“z” Hours offset from UTC, with no leading zeros. 6/15/2009 1:45:30 PM -07:00 -> -7
“zz” Hours offset from UTC, with a leading zero for a single-digit value. 6/15/2009 1:45:30 PM -07:00 -> -07
“zzz” Hours and minutes offset from UTC. 6/15/2009 1:45:30 PM -07:00 -> -07:00
“:” The time separator. 6/15/2009 1:45:30 PM -> : (en-US)
6/15/2009 1:45:30 PM -> . (it-IT)
6/15/2009 1:45:30 PM -> : (ja-JP)
“/” The date separator. 6/15/2009 1:45:30 PM -> / (en-US)
6/15/2009 1:45:30 PM -> – (ar-DZ)
6/15/2009 1:45:30 PM -> . (tr-TR)
“string”
‘string’
Literal string delimiter. 6/15/2009 1:45:30 PM (“arr:” h:m t) -> arr: 1:45 P
6/15/2009 1:45:30 PM (‘arr:’ h:m t) -> arr: 1:45 P

Continue reading

WriteToWorksheet

writetoworksheetThis Analytics Edge wizard is used to write the results of an analysis to a worksheet. It can either overwrite an entire sheet, or be written to a specific location.

It is normally the last step in an analysis sequence, but the macro can continue processing data and write to other worksheets or files.

Worksheet – enter a name for the worksheet to write to (located in the current workbook). If the worksheet does not exist, it will be created.

Replace entire worksheet – removes all data and formatting before writing the contents of the table to the worksheet. Prevents old data from remaining in a worksheet if the new results do not use as many rows or columns.

Insert into worksheet – specific the top-left cell for the results to be written to. This option will only clear a range big enough to write the data from the table.

Do not include header – check this to prevent the column names from being included in the first row of the results written.

Do not clear cell formatting – this option will only change the cell values and not affect any cell formatting in place. Note that this can cause problems if the format of the cell does not match the data.

Transpose – check this to write each column as a row in the worksheet.

Add hyperlinks to web addresses – if the results include web addresses, they will be converted to hyperlinks that can be clicked on.

Format as Excel table – format the output as an Excel table using one of the predefined Excel formats. Select from the formats listed or pick the empty entry for no color formatting.

Updated in version 1.3.1

 

WriteToTextFile

write a table to a text fileThis Analytics Edge wizard lets you write the current table to a delimited text file, specifying the format to be used for dates.

It is usually used to produce a file for uploading to another system, but can also be used to export data that will not fit into an Excel worksheet.

File – enter or browse to the specific file to be saved to.

Delimiter – select or enter a delimiter to place between values as the file is written. Note that if ‘Comma’ is selected, Analytics Edge will use smart delimiting, wrapping text strings that include commas in double quotes and escaping double quotes by pairing them, similar to Excel saving as CSV (MS-DOS).

Do not write the header row – check this to prevent the column names from being included in the first row of the file.

Transpose – check this to write each column as a row in the file.

Desired date format – enter date format codes to determine how dates should be written to the file.

 

Update

update a table from another tableThis Analytics Edge wizard lets you update the current table from entries with matching rows in another table or worksheet.

It is typically used to update a master list with transactional items, and includes options to ignore empty update fields and to only update a field if it is empty.

Update from Table – select a worksheet or previously named table from the drop down list.  The worksheet must be in the same workbook, and data must start in cell A1 with a header row. If you want to use a table in memory, use Table Name to create the reference.

Select Key Columns – select columns that will be used to align the two tables together. All values from all selected columns must match for a row to match.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Replace only empty cells – do not update existing values unless they are empty.

Ignore update if empty – if the reference table contains empty cells, do not modify the matching row in the current table.

Add new columns – add any columns from the reference table that do not exist in the current table.

Add new rows – add rows from the reference table with values in the key columns that only exist there.

Blog article: Using the Update Function

Unpivot

unpivotThis Analytics Edge wizard is used to perform a reverse pivot operation, converting multiple columns of data into two; one with the original column name and the other with the value.

It is typically used to convert a trend report to transactional format for uploading into a database.

Pick Columns – select all of the columns to be unpivoted. For every non-blank cell, a row will be created with the column name and cell value.

Column Title for Column Names – enter a name for the column that will hold the names of the columns in the original table.

Column Title for Cell Values – enter a name for the column that will hold the values from the cells in the original table.

Floating Right Column – select the special column ZZZ in the list if the data may change over time and you want to include any extra columns to the right of those already selected, should they appear.

Total

totalThis Analytics Edge wizard is used to create an aggregate row or column, like Total or Average. You can choose to aggregate only the first or last group of rows or columns, and position the results before or after the data, or to remove the summarized data completely.

This simple wizard lets you do common reporting tasks like creating a total row at the end of your table, or to display the top 10 rows and an average of the rest.

Summarize – select whether you want to aggregate rows or columns in the current table.

Select Rows / Columns – allows you to choose all rows, just the top N rows, or everything expect the top N rows. This last option is useful for top 10 and average of the rest reports.

Aggregate – select whether to use the Sum, Average or other summary function.

Position – if all rows or columns are not removed, choose whether to position the summary row or column before or after the numeric data. Note that if you have multiple text or date columns in the table, a summary column positioned ‘Before’ will appear where the first numeric column was.

Name – assign a name for the new row or column.

Do not remove selected rows / columns – check this box to add the summary row / column to the table, otherwise the selected rows / columns will be replaced with the summary.

New in version 1.3.1

 

 

Top / Bottom

topThis Analytics Edge wizard lets you keep or remove a specific number of the top or bottom rows.

Select whether you want to keep or remove the rows, whether you want to pick the top or bottom, and enter a count for the number of rows.

 

New in version 1.3.1

 

Table Name

table nameThis Analytics Edge wizard lets you save the current table for reference later in the macro, such as with appending or comparing tables. You can also switch back to that table to perform multiple calculations from the same data. You can even use the values in the table as predefined ranges in other functions.

Assign a name to the current table – This allows you to lock the current table in memory and assign it a name for reference later in the macro. It is typically used with one of the comparison or combination functions (Append, Combine, Compare, Match, Update).

Switch to a previously named table – This allows you to switch the focus to a previously named table (as above). It is typically used to go back to an intermediate point in a macro and perform a different analysis on the same source data, such as calculating percentage growth and the calculating difference.

Assign range names to each column of the current table – Analytics Edge can sometimes use defined ranges instead of fixed values. This is typically used to read configuration options or selections from a worksheet, and then to customize the analytics based on the values, such as filtering by a date range or value from a specific cell. Each column in the current table is saved as a range, using the name of the column.

Subtotal

subtotalThis Analytics Edge wizard allows you to subtotal a table based on unique values in selected columns.

It is typically used to produce summary reports from detailed data.

Columns to Subtotal – select columns to be subtotalled by position or By Name. All unique combinations of values from the selected columns will be grouped.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Aggregation – select the aggregation to use when there are duplicate values.

Split

splitThis Analytics Edge wizard lets you split a text column into several columns. You can split on a delimiter or a fixed-width sequence.

It can be used to split name fields apart (use the smart name split function), or to split product codes or URLs.

Pick Column – select a column by position letter (optionally By Name) to be split.

Split on delimiter – select a delimiter that separates the values in the column. Optionally enter your own sequence of characters (may be more than one).

Split fixed width – if the column is to be split at specific character positions, use this option.

Smart name split – select this option to split a column into various name parts.

Split into Columns – splits the selected column into multiple columns, named with suffix -1, -2, etc.

Remove original column – to remove the column that has been split, check this option.

Split into Rows – splits multiple values in the selected column into multiple rows.

Ignore duplicates – if the column contains multiple but identical values, ignore the duplicates and create only one row for each unique value.

split-by-rows

split-into-rows-results

Sort

sort3This Analytics Edge wizard lets you create one or more sorting rules which will be applied in sequence to order your data.

If two rows are equal in value for the first rule, the next rule is used to determine the order. You can specify as many rules as required.

Column – select a column by column letter position (optionally by Name).

Sort On – select a sorting option to use, optionally check Case sensitive to order upper and lower case letters differently.

Order – select whether to sort ascending or descending order.

Add Rule – adds the selections above to the sort rules list. When multiple rules exist, they will be executed in order; if there is a tie in the first rule, the second rule is used to determine order.

Delete – remove the selected rule from the list.

Up / Down – move the selected rule up or down in the list. The rules will be run in order, so the first rule has highest priority.

Continue reading

Save/Email Workbook

save-email-workbookThis wizard lets you save a copy of the Excel workbook and optionally email it to someone.

You can save the copy to the same folder as the original file, to the default report folder, or to a custom folder location.The default report folder is shared across all macros, and would server as a single location to find updated reports, especially useful for those generated by a scheduled refresh.

The file name can be the same as the original with the current date appended to it (the copy MUST have a different file name from the original), or you can assign a custom file name to be used. With the custom option, you can also have Analytics Edge dynamically add the current date to the file name, automatically preserving previous versions.

Emailing reports requires you to configure your email server in the FileOptions wizard.

To send the report via email, check the Send Email box and enter the email addresses of the recipients (separated by commas). Enter a subject and optional message text. You can also receive a copy of your own by checking the Bcc me box. Note that creating or stepping through the macro will NOT send emails, allowing you to test the macro without annoying recipients.

Save/Email PDF

save-email-pdfThis wizard lets you save an Adobe PDF copy of the Excel workbook and optionally email it to someone.

You can save the copy to the same folder as the original file, to the default report folder, or to a custom folder location.The default report folder is shared across all macros, and would server as a single location to find updated reports, especially useful for those generated by a scheduled refresh.

The file name can be the same as the original with the current date appended to it, or you can assign a custom file name to be used. With the custom option, you can also have Analytics Edge dynamically add the current date to the file name, automatically preserving previous versions.

Emailing reports requires you to configure your email server in the FileOptions wizard.

To send the report via email, check the Send Email box and enter the email addresses of the recipients (separated by commas). Enter a subject and optional message text. You can also receive a copy of your own by checking the Bcc me box. Note that creating or stepping through the macro will NOT send emails, allowing you to test the macro without annoying recipients.

Run Macro Function

run-macro-functionAnalytics Edge macros that start with an underscore (e.g. “_setup”) will NOT run when you click the Refresh All button, nor during a Scheduled Refresh. This wizard lets you run those special underscore macros from inside another macro.

This can be used to create a ‘master’ macro that runs other macros in a specific order. It is especially powerful if the ‘master’ macro is a Repeat Macro.

Replace

replaceThis Analytics Edge wizard lets you replace text in selected columns. It supports the usual * and ? wildcard matching.

It is usually used to clean up data for reporting purposes, and you can use this wizard repeatedly to perform a series of replacements.

Pick Columns – select the columns to be modified either by column letter position or By Name.

Replace matching characters – this is the simplest form of the function. Characters you enter in the Find Text field will be replaced with characters in the Replace With field. It will replace characters anywhere in the cell value, even if they occur more than once.

Replace entire cell – this option allows you to use a wildcard match (* for any number of characters, ? for a single character), and only cells that match the whole cell value will be changed. If you use a * in the Replace With field, the original cell value will be inserted (useful for adding text before or after the original cell value).

Use regular expressions – you can also use a regular expression match and replacement. This is an advanced option, and knowledge of regular expressions in needed.
ref: http://msdn.microsoft.com/en-us/library/az24scfc(v=vs.100).aspx

Ignore case – check this option to match both upper and lower case letters.

 

Repeat Macro

repeatmacroThis wizard converts the current macro into a Repeating Macro. Repeating macros allow you to load a tables of values from a worksheet to be used as Analytics Edge named ranges while running the rest of the macro.

By default, the macro will be repeated for each row in the table loaded. For example, if the selected worksheet contains a column labelled ‘id’, other functions in the macro can refer to the range name [id] in place of a website view id number or email campaign id number. Each pass through the macro would process a different view or campaign.

Workbook / Worksheet: The referenced worksheet can be from a separate workbook, so that the list of account information is not included in the refreshed workbook. This is especially useful when refreshing a template report for a list of clients, so the client list is not part of the workbook you send to the client.

Rows to use: The function also allows you to process more than 1 row per cycle. The rest of the macro will have to know how to process more than one value in the ranges.

Setup – Clear Worksheet: Before the first row is processed, a selected worksheet can be cleared of all data. This can be used with Append To Worksheet to process all rows and combine the results to a target worksheet, clearing the data before the macro is run.

Keep header row: option to keep the header row of the target worksheet when clearing the data. This is useful to maintain the order of the columns when using the AppendToWorksheet function.

For additional information, see Using the Repeat Macro Function

Read Worksheet

readworksheetThis Analytics Edge wizard allows you to read an Excel worksheet into a table. The worksheet can be in any open workbook, and it is possible to read part of a worksheet if desired.

You can use this function to read data downloaded or imported form other data sources, or to consolidate data from other workbooks.

Workbook/Worksheet – select the workbook/worksheet combination you want to read from. Workbooks must be open to record the macro, but do not need to be open to run the macro.

Read the entire worksheet – select this option if the data is located starting in cell A1, and the whole worksheet is data.

Read a table in the worksheet – select this option, and pick a top left cell, to read a table located somewhere in a worksheet that may contain other data. The table must contain continuous data in the first row and column (no blanks).

Read a specific region only – if the data might contain blank cells, select this option and identify the entire range of cells to be read.

Data does not include a header – check this if the top row of data is not the column headers (column names).

Data is transposed – if the data is oriented by rows instead of columns, check this option to transpose the data as it is read in.

Load direct to a named table – this option lets you load a reference table from a worksheet in one step, as opposed to loading it and using Table Name to establish the reference.

Assign range names to each column – assigns Analytics Edge range names to each column that is read in. These range names can be used in the place of fixed values in other Analytics Edge wizards by enclosing them in square brackets [Column Name].

Blog article : Using the Read Worksheet Function

Updated in version 1.3.1

Read Text File

readtextfileThis Analytics Edge wizard allows you to import a text file into a table. I accepts both delimited files as well as fixed-width fields.

While the date format of the source file can be specified, you can follow up with a Convert function to handle any text, number or date conversions desired.

File/URL – enter or browse to the specific file to be loaded, or enter a direct URL to a web-based file.

Split on delimiter – select or enter a delimiter to use when reading the file.

Split into fixed width fields – if the file uses fixed width fields, select this option and continue to the next wizard panel (below).

File type – read a special file type. Currently only JSON files can be read.

File does not contain a header row – select this if the data does not contain column names.

Transpose – read the data in, swapping rows of data into columns. The first column in the row will be treated as the transposed column name.

Date format in file – enter codes to represent the exact date format used in the source file. Analytics Edge will recognize columns as dates if all of the values match that format. Pay specific attention to matching single or two-digit days, months or time components.

Continue reading

Rank

rank order data in ExcelThis Analytics Edge wizard is used to convert the selected column into a number representing either the rank order or it’s percentile, decile or quartile position.

It is typically used to segment data or to establish a normalized value for further analysis, such as using Filter and Match to obtain details for the top 10%.

Change Column Into – select a ranking option to use.

Rank – convert the value to a rank number starting at 1 and counting up. Duplicate rows will have the same value, and the next number will be skipped.

Percentile – split the column into 100 ordered groups and convert the value to a number between 1 and 100.

Decile – split the column into 10 ordered groups and convert the value to a number between 1 and 10.

Quartile – split the column into 4 ordered groups and convert the value to a number between 1 and 4.

Order – select whether larger values will rank higher (e.g. counting up), or whether larger values will rank lower (e.g. top 10).

Pick Columns – select the columns by position letter (optionally By Name) to change into a numeric rank column.

Pivot

pivot-bynameThis Analytics Edge wizard is used to pivot a column of data similar to Excel’s pivot table. If you are pivoting by date, you can select the date range and automatically fill in any missing values.

This function is typically used to produce time trend reports, converting tabular data into a more usable form.

Arrange Columns to Keep – select and rearrange the columns you want to keep in the resulting table.

By Name – select the columns by the name of the column instead of selecting them by column position (A, B, C, etc).

Move Up or Down – move the selected column up or down in the list.

Remove – remove the selected columns from the list.

Ignore Case – when combining duplicate rows, ignore differences in upper or lower case.

Reset – reset the list of columns to the original.

Pivot to Columns – select a column to pivot, so a new column will be created for each unique value in the selected column.

Ignore blanks – if the pivoted column contains blank cells, ignore them and do not create a column with a blank name.

Cell Values – select a column to be used for the values in the cells in the pivoted table.

Aggregation – select the aggregation to use when there are duplicate values for a specific cell position.

Continue reading

Options

This wizard lets you configure Excel Pivot Tables refresh options and Email server settings.

Pivot Tables

You can have Analytics Edge automatically refresh Excel Pivot Tables in your workbooks after a macro runs. Normally they would be need to be refreshed manually after your data changes.

options-autorefresh-pivot-tables

If you want to send emails from your macros, you will need to set your email server settings. Analytics Edge supports most SMTP servers, and defaults to the values used by Google’s Gmail servers.

Email

Start by entering the Email Address your messages will be from, and the Name to be displayed. Then enter the SMTP server settings for your server. Most servers require authentication, so you will need to enter your email account and password.

Gmail users: must enable ‘Access for less secure apps‘ in your account, and enter your user name (email address ) and password in the Options > Email form.

options-email

Privacy Note: your email account and password are stored, strongly encrypted, on your computer. They are not included in any Excel workbooks, nor are they shared or passed to any other application.

 

Match

matchThis Analytics Edge wizard is used to keep or remove rows based on whether matching row values exist in another table or worksheet.

It is usually used by taking the result of one analysis (top product sales, most popular links) and filtering a detail report to see what contributed to the ranking.

Match with Table – select a worksheet or previously named table from the drop down list.  The worksheet must be in the same workbook, and data must start in cell A1 with a header row. If you want to use a table in memory, use Table Name to create the reference.

Select Key Columns – select columns that will be used to align the two tables together. All values from all selected columns must match for a row to match.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Keep matching rows – keep only rows that have matching rows in the reference table.

Remove matching rows – remove rows that have matching rows in the reference table.

Blog article: Using the Match Function

Join

join

Typical uses include combining name or address components into a single column for uploading into a database.

Pick Column – select a column by position letter (optionally By Name) to be included in the Join operation.

>> – add the selected column(s) to the list of Columns to be joined.

<< – remove the selected column(s) from the list of Columns to be joined.

New Column Name – enter a name for the new column that will be created containing the joined contents.

Using Delimiter – select a delimiter to use between values of the columns being joined. Optionally enter your own sequence of characters (may be more than one).

Ignore missing values – do not include a delimiter when one of the columns has no value.

Remove original columns – after creating a new column with the joined values, remove the original columns from the table.

 

Formula

add a calculated column to ExcelThis Analytics Edge wizard, used at the end of a macro, allows you to populate a column with an Excel formula, giving you full access to Excel’s advanced functions. It must be the last transformation in the sequence.

The formulas are created with column references only, and Analytics Edge will automatically populate the row numbers as it writes to the worksheet (WriteToWorksheet).

Name – enter a name for the new column that will be created.

Formula – enter an Excel formula to be used in each row of the column. Reference other columns by picking them from the list at right. Do not enter cell references unless they are absolute cell references (e.g. $A$1).

Pick Column – select a column to add to the formula (optionally By Name). This will insert a column reference into the formula.

Analytics Edge will expand the formula, filling in cell references with the appropriate column and row coordinates, when the data is written out to a worksheet. Note that formula columns will be dropped if you apply other functions before writing to a worksheet, so add this as your next-to-last step in the macro.

Filter

filterThis Analytics Edge wizard lets you construct one or more filters to be applied to the current table. Filters can be combined using AND-OR logic, and you can choose to remove or keep the matching rows.

Typically used to remove unwanted data from a report, it can also selectively keep rows of interest.

Column – select a column by column letter position (optionally by Name).

Criteria – select a filter criteria or test to use.

Value – enter a value to use for the filter test. For text columns, optionally check Case sensitive to match upper and lower case letter exactly.

Add AND Filter – add the filter rules (Column, Criteria and Value) to the list using AND logic, meaning both this rule in addition to a previous rule entered are required to cause a match.

Add OR Filter – add the filter rules (Column, Criteria and Value) to the list using OR logic, meaning either this rule or a previous rule entered will cause a match.

Delete – delete a selected filter rule from the list shown.

Move Up or Down – move a selected filter rule up or down in the list shown.

Keep Matches – keep rows that match all of the filter rules.

Remove Matches – remove rows that match all of the filter rules.

Remove Empty Rows – if a row contains no information in any of the columns, remove it.

Remove Empty Columns – if a column contains no information in any of the rows, remove it.

Stop macro if empty – if the table is empty, then stop the macro. This does not cause an error condition, so other macros will continue to run if Refresh All or Scheduled refresh was used.

Blog article: Using the Filter Function

Duplicate

duplicatesThis Analytics Edge widget recognizes duplicate rows in your data and lets you choose whether to keep them, remove them or combine them.

Typically used to remove duplications in lists, it can also summarize your data for unique values in specific columns.

Select Columns – select columns by position (shown with column letter for reference), or By Name, Duplicate rows are rows with the same values as another row in all of the selected columns.

Duplicates – select to keep or remove all rows that have duplicates, or to keep a single row for each, aggregating values in the other, unselected columns. For text columns, the ‘All (list)’ option will contain a comma-separated list of all values.

Singles – select to keep or remove rows that do not have duplicate rows in the table.

Ignore case – when performing the duplicate check, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the duplicate check, match rows even if they differ because of different spacing (leading, trailing or within the text).

Add Count column – add a column to the table, named ‘Count’, with a count of the number of rows represented.

Blog article: Using the Duplicates Function

Convert

convertThis Analytics Edge wizard is used to convert columns from one data type (text, number, or date) into another. It can also convert to a variant of the same type, like converting dates to first of the month.

Typically used to force imported data into the correct type (such as dates with odd formats), it can also be used to convert dates and numbers into specific formats for reporting purposes.

Select Column – select a column in the list, shown with the column letter for position reference or optionally By Name, then click the Next button to choose options. Depending on the type of column, you will be given additional options to convert the selected column from text, number or date formats into different text, number or date formats. You will be redirected back here, where you can either convert another column or click Finish.

Note that you can convert within a type, such as truncating text, rounding numbers, and converting to first of the month.

Continue reading

Compare

This Analytics Edge wizard is used to compare the current table with another, computing the difference between matching rows.

compare-tablesCompare Tables

This option tab replaces the numbers and dates in the table with the calculated comparisons.

Compare to Table – select a worksheet or previously named table from the drop down list.  The worksheet must be in the same workbook, and data must start in cell A1 with a header row. If you want to use a table in memory, use Table Name to create the reference.

Select Key Columns – select columns that will be used to align the two tables together. All values from all selected columns must match for a row to match.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Handling Matches – where both tables have matching values in the key columns, compare the values of the remaining number and date columns using the selected options.

Blog article: Using the Compare Function

compare-columnsCompare Columns

This option is used to create a new column containing the comparison between a specific column in the current table with the reference table.

Combine

combineThis Analytics Edge wizard is used to combine two sets of data, merging the values from a worksheet or previously named table.

It is generally used with two similar data sets, totalling the numbers, retaining the most recent date, and even preserving a list of reference values.

Combine with Table – select a worksheet or a previously named table from the drop down list. The worksheet must be in the same workbook, and data must start in cell A1 with a header row. If you want to use a table in memory, use Table Name to create the reference.

Match by Row Number – select this option to combine the tables by row number. This is useful if the tables have no common columns.

Select Key Columns – select columns that will be used to align the two tables together. All values from all selected columns must match for a row to match.

Ignore case – when performing the comparison, match rows even if they have differing upper or lower case letters.

Ignore spaces – when performing the comparison, match rows even if they differ because of different spacing (leading, trailing or within the text).

Add new rows – automatically add rows that are only in the referenced table.

Add new columns – automatically add columns that are only in the referenced table.

Handling Duplicates – where both tables have matching values in the key columns, combine the values of the remaining columns using the selected options. The ‘All (list)’ option will produce a comma-separated list of values in each cell.

Blog article: Using the Combine Function

Calculate

calculate1This Analytics Edge wizard lets you perform typical math calculations without formulas, including a number of series calculations across rows or down columns.

Common uses include calculating period-to-period growth rates, levelling out spikes with rolling averages, and scaling numbers to report in thousands or millions.

Add a New Column – You can choose to add a new column to your table, and the new column will contain the results of the calculation.

Replace Existing Values – Alternatively, you can choose to replace the values in the table with the results of the calculation, such as a rolling average or percent of column total.

calculate2New Column Name – enter a name for the column to be added.

1-Column Math Functions – these functions allow you to use the value from the selected column and a number, such as add 1 or multiply by 100, and put the result in the new column. Note that the order is important for functions like subtract and divide, so functions with both arrangements are available.

1-Column Relative Functions – these functions allow you to use the value from the selected column and and express it relative to an aggregate of that column, such as percent of total (Divide by Sum). Note that the order is important for functions like subtract and divide, so functions with both arrangements are available.

2-Column Math Functions – these functions allow you to use the values from two selected columns in the calculation, and put the result in the new column. Note that some

Multi-Column Math Functions – these functions allow you to use values from a range of columns in the calculation, such as the sum of column C through F.

calculate3Pick Columns – The columns in the table are listed with their column letter (A, B, C, etc) for reference. Select one or more columns (they will be highlighted in yellow), then select one of the match operations to apply.

To select multiple individual columns, you can hold the Ctrl-key down and click. To select a range, pick one then hold the Shift-key down and select the other end of the range, or hold the mouse key down and drag down the list.

By Name – check this box to select the columns using the name of the column instead of its position. This is useful if the column order might change over time. Note that if the column names might change (dates, for example), do not use this option.

Math – select an operation and enter a value. Adds, subtracts, multiplies or divides the selected columns by the value entered. For example, divide by 1000 could be used to shows numbers in thousands.

Cumulative – select a function and a direction. Performs a cumulative operation down each column selected, or across the rows of the selected columns. For example, a cumulative sum down each column is also referred to as a running total where each cell would contain the sum of all of the cells above it.

Repeating – select a function and a direction. Performs the operation down each column selected, or across the rows of the selected columns. For example, repeating difference down the column would replace each cell value with the difference from the cell above it.

Rolling (moving) – enter a value and select a function and direction. Performs a rolling function down each column selected, or across the rows of the selected columns.

Relative – select the type of comparison, the axis to compare to, and the aggregate to compare with. Each of the selected columns is changed to that new comparison value.

Floating Right Column – automatically selects columns to the right of any selected columns. This is typically used with reports that have an unknown or inconsistent number of columns, such as days of the month.

Updated in version 1.3.1

 

Arrange

This Analytics Edge wizard is used to rearrange and/or rename columns in a table.

Typically used to change the column order or names for a report, it can also be used to make column names consistent with other tables so that comparison functions like Compare, Match, or Combine can recognize matching columns.

Blog article: Using the Arrange Function

arrange-by-positionBy Position

Use this option to rearrange the columns based on the original column positions (Excel columns A, B, C, etc).

Column Names and Order – The columns in the table are listed with their original column letter (A, B, C, etc) for reference. Select a column to modify (it will be highlighted in yellow), then select one of the actions.

Move selected up – move the selected column up in the list (will move it to the left in the table) The original column letter stays in the listing for reference.

Remove selected column – remove the selected column from the listing. Use Reset to get all of the original columns back if necessary.

Move selected down – move the selected column down in the list (will move it to the right in the table) The original column letter stays in the listing for reference.

Rename – select a column in the list, then enter a new column name in the text box beside the button and click the button to assign a new name to the selected column. The original column letter remains in the list for reference.

Reverse – completely reverse the order of all of the columns in the list from top to bottom (left to right in the table).

Reset – reset the list of columns back to the original. This removes all reorder and name changes previously made.

arrange-by-nameBy Name

Use this option to rearrange the columns based on the names of the columns. This is useful when you get imported data that may have additional, missing, or out-of-order columns.

Column Names and Order – The columns in the table are listed by name. Select a column to modify (it will be highlighted in yellow), then select one of the actions.

The options on this tab are similar to By Position above.

Stop macro if column missing – is used to stop process of the macro if a named column is missing in the table. This is useful to verify downloaded or imported data before processing.

Append – select a column in the list, then enter a new column name in the text box beside the button and click the button to create a new column following the selected column. This can be used to force certain columns in a report even if the source data may not contain those columns.

arrange-insertInsert Column

Use this option to insert a new (empty) column with a specific name at a specific location. The new column is inserted at the selected location, so the column selected is shifted to the right.

Before/After – insert the new column before or after the selected column.

By Name – use the name of the selected column instead of the column letter (position).

New Column Name – the title for the new empty column.

Initial Text Value – used to prepopulate the entire column with a specific text value.

arrange-deleteDelete Column

Use this option to delete one or more columns in the current table.

By Name – allows you select the column by name instead of position.

arrange-sort-by-nameSort by Name

Use this option tab to sort the columns by the names of the columns themselves. Select whether to sort alphabetically, numerically, or by date/time order.

Date/time sorting will recognize several date formats depending on the locale settings of your computer.

arrange-multi-row-recordsMultiRow Records

Downloads and file imports will typically create one row per record, but if a record contains multiple items, like a phone number, they may be loaded into separate columns. This function will align columns with the same name (but having -1, -2, -3 endings), but will result in multiple rows for a single record.

Append

appendThis Analytics Edge wizard makes it easy to merge two tables together, keeping all of the rows and columns of the original tables.

It would typically be used by loading data from a file (ReadTextFile) or worksheet (ReadWorksheet), and appending more data directly from a worksheet or from a previously named table in memory (TableName).

Append From Table – select a worksheet name or a previously named table from the drop down list. If you select a worksheet, the worksheet must contain data in columns with a header row starting in cell A1.

Blog article: Using the Append Function

* Analytics Edge Core Add-in

The Analytics Edge Core Add-in does not work like the Analytics Edge Basic Add-in.
See the Orientation page for the important differences.

FIRST: REGISTER THE ADD-IN

Your first step is to Register the add-in you have installed. Just start Excel, select the Analytics Edge ribbon, and click the Register button. The Register button will change to Check License once the add-in is activated.  Don’t see the ribbon?

register

Click the Activate 30-Day License to start your free trial period. No payment is necessary and the product is fully functional. OPTIONAL: Enter your email address to stay informed of updates to the product and make it easier to reset or transfer your product licenses after computer updates.

 LOOKING FOR MORE DATA?

See the Orientation page for simple instructions.

ae-step-results

Schedule Refresh

schedule-refresh-2This Analytics Edge wizard helps you to schedule unattended refreshes for the workbook you have open.

Conditions required for the refresh to occur:

  • your computer must be turned on
  • you must be logged in (screen lock/screen saver is ok)
  • the Analytics Edge Core Add-in must be enabled and licensed in Excel
  • the workbook must be available but not opened

The Analytics Edge Core Add-in includes a Scheduled Refresh that can be used to trigger unattended refreshes of specific workbooks. The Scheduled Refresh runs on your computer (not a server), so your computer must be turned on for it to work.

Only one schedule can be created per workbook. If you open the Schedule Refresh wizard from a workbook that already has a schedule, the wizard will edit the existing schedule.

Abort Refresh after # minutes

The scheduler will wait a period of time to allow a scheduled refresh to complete and prevent other refreshes from running. You can specify how long the scheduler should wait before abandoning the process and starting another refresh.

One-Time Refresh

Select the One Time radio button, select the First Run At date and time for the refresh, and click the Set button.

Daily Refresh

To refresh at a specific time every few days, select the Daily radio button, and enter the number of days between refreshes (every day = 1; every second day = 2). Select the First Run At date and time for the refresh, and click the Set button.

Weekly Refresh

To refresh on specific days of the week, select the Weekly radio button, select the days you want the refresh to occur on and the number of weeks between refreshes (e.g. every second Tuesday). To select multiple days, hold the Ctrl-key down while clicking on the days. Select the First Run At date and time for the refresh, and click the Set button.

Monthly Refresh

To refresh on a monthly basis, select the Monthly radio button, and select the months desired. If you want specific days of specific weeks of the month, select the ‘on’ radio button, select the weeks and days desired.

If you want to refresh on a specific day of the month (2nd of the month, every 3 months), then select the ‘days’ radio button and select the days desired. Hold the Crtl-key down to select multiple days or months.

Select the First Run At date and time for the refresh, and click the Set button.

Other Scheduled Refreshes

Any number of workbooks can be scheduled, and they can be managed individually from the Schedule Refresh wizard when the workbook is open. You can also launch the Schedule Manager to see all scheduled refreshes.

You may discover that the scheduled refreshes are trigger via the Windows Task Scheduler. If you manually edit the entries in the Task Scheduler directly, they may not run correctly.

Enter Data Source Schema

ga-enter-schemaEntering the Google Analytics data source schema into Analytics Edge makes it easier to upload data by automatically populating the target column names to match the schema.

Copy the schema from the Google Analytics Admin interface for the specific data source, and paste it into the box provided.

Upload Data into Google Analytics

ga-upload-dataCAUTION: read the information in Google Analytics Help regarding data upload before using the Analytics Edge upload feature.

To upload data into Google Analytics, select the Property and Custom Data Source (see Google Analytics help for details about custom data sources and uploading data).

You can enter the schema for the data source by clicking the button provided. You can also import any custom dimension and metric definitions to make it easier to map the columns.

Select the source of the data to be uploaded, then map the various columns from the source to the columns in the schema. NOTE: you need to match the schema of your data source; by default, Analytics Edge will list all possible fields. if you use fields that are not part of the selected data source schema, the extra columns will not be uploaded.

Once you have mapped all of the fields, click Finish. A dialog will appear giving you a 5-second countdown to cancel the upload.  To delete a upload after it has been completed, you will need to do so from the Google Analytics Admin interface.

Management Lists

ga-management-listsThe Management Lists wizard allows you to download information about your account(s), web properties and views. Select the Login to use for the query, then pick the Account, Web Property and View (Profile) to use for the query. Then select the report to run from the list at the left.

If you select ALL for the account, property or view, you will get listings for all your accounts, properties and/or views that match.

Analytics Reporting – Dynamic Segments

dynamic-segmentsAnalytics Edge makes it possible to create dynamic segments using a a wizard interface similar to that in the web version of Google Analytics. Select the category to the left, then select the options you want in your segment to the right.

You can add multiple conditions in a single segment, and all conditions must be met for a session to be included in the condition.

It is also possible to enter a Custom expression to pass direct to the API &segment= parameter (must be properly formatted). This can entered directly into the wizard, or an Excel cell reference can be used to a worksheet location.

Analytics Reporting – Options

optionsThe Options tab provides access to several options that affect how the Google Analytics data is presented in Excel.

Rates/Percents: By default, Analytics Edge will convert rate and percentage metrics into fractions of 1 (50% = 0.5) so that the cells can be formatted using Excel’s % formatting. If you would rather see 50 in the cell, select Number.

Dates: Analytics Edge automatically converts dates into Excel dates. If you would rather get the non-standard response (20140725) format, select String/Number.

Numeric Dimensions: By default, all dimensions are delivered to Excel in text-formatted cells. Some dimensions, like Count of Sessions are numeric in nature. If you would like to see these “numeric” dimensions presented as numbers, select Number.

Time Metrics: by default, time metrics like Time on Page and Sessions Duration are expressed in seconds. Select Days to make it possible to format the cell in Excel using a Time format to get hh:mm:ss formatted times.

Include Empty Rows: by default the GA API returns rows in some queries with zero results. You can suppress these rows with this option.

Sampled Data Options 

If your query is subject to data sampling, you can choose to be warned of this happening by checking the box ‘warn if results contain sampled data‘.

You can minimize sampling errors by reducing the time-span of the query, or include a time dimension and check the box to ‘minimize sampling‘. Doing this will cause Analytics Edge to make queries for the smallest time dimension in your query. If you include Date ( or Day Index, or Day of Month) then it will query for each day. Similarly for any Week, ISO Week, Month or Year dimension. If you include more than one time dimension, the smallest one will determine the query resolution. Note that there is no restriction or special handling required with other dimensions or metrics in your query. Be aware that there is a daily limit of 10,000 requests per View.

minimize-sampling

 

Convert Excellent Analytics

[Only available in the Free Google Analytics connector]

Use this wizard to convert existing queries built with Excellent Analytics into Analytics Edge queries. Select the cell below the Excellent Analytics query cell and open the wizard.

convert-excellent-analytics-1

The Excellent Analytics query will be detected and displayed. Select the Google Analytics Login that has access to the web property in the query, and click Convert.

convert-excellent-analytics-2

With the Analytics Edge Basic Addin

analytics-edge-conversion-of-excellent-analyticsProgress of the conversion will be displayed. If successful, Analytics will run the query and update the results in the worksheet when you click the Close button.

To edit your query, select the cell with the comment and click the Edit Query button on the Analytics Edge ribbon bar.

With the Analytics Edge Core Addin

convert-excellent-analytics-4Progress of the conversion will be displayed. If successful, Analytics will run the query and update the results in a temporary worksheet when you click the Close button.  From the Analytics Edge ribbon bar, click File – Write to Current Cell and the results will be put into your report.

 

Google Analytics Accounts

Use this wizard to log in to your Google Analytics account and authorize Analytics Edge to access your data. Enter a name for the account you want to login with, then click the Add Account button.

new-account

Multiple accounts can be stored, and you can change which account gets used by default. If your reports make multiple queries and always refer to the ‘Default account’, you can switch the default account to report on on different account.

[Pro version: if you want to be able to Upload Data into the account or Save Filters, check the box below the reference name before you Add the account to obtain elevated authorizations necessary.]

Select an account from the list, select a web property and view, and click Make Default to set that view to use as default for that login.

Use the ‘Save Default in Macro’ button to force a specific workbook macro to always use a specific account as the default.

MANUAL LOGIN

If you experience a problem with the login, click the Manual link in the dialog. A new browser window will open. Login to your Google Analytics account and Accept the access request. You will be taken to a web page on the Analytics Edge web site, and there should be a code in the URL of the browser. Copy the code part of the URL, close the browser, and paste the code into the dialog box provided.

ga-manual-auth

 

* Salesforce Help

salesforce-menuThe Salesforce Connector for Analytics Edge makes it easy to download Salesforce data directly into Microsoft Excel.

Before you can make a query, you need to log in through the Accounts wizard.

* Bing Webmaster Help

bingThe Bing Webmaster Tools Connector for Analytics Edge makes it possible to download data directly from the Bing Webmaster Tools API into Microsoft Excel. The connector provides access to a variety of data, including Page Traffic, Search Keywords, Inbound Links and Crawl Statistics.

Before you can make a query, you must log in through the Accounts wizard.

 

Accounts

accounts Enter a reference name for your account and enter the API key found in your Bing Webmaster Tools interface.

Select the account in the list, select a default web site from the drop down at the bottom, then click the Make Default button to establish a default account.

The default account can be changed in an Analytics Edge Core macro by opening the Accounts wizard while a macro is open in the Task Pane. Select the new account and click Save Default in Macro button.

Moz Accounts

accountUse this Analytics Edge wizard to enter your Moz account credentials. Using the Mozscape API requires that you get an Access ID and Secret Key associated with your Moz community login. Get them from here: http://apiwiki.moz.com/create-and-manage-your-account

Note that the name you enter for your account is reference only, and appears in the listing and drop-down selectors in other Analytics Edge wizards, so use a meaningful name.

One of your accounts can be identified as the Default account to use, which makes it easier to build generic queries and template workbooks.

 

Anchor Text

anchor-text-1This Analytics Edge wizard returns information about anchor text for inbound links.  [Paid Moz API access required]

Enter the link URL (e.g. moz.com/blog) and select the scope and filter options. If you want more than the default 25 rows, enter a limit number.

You can get the URL from a worksheet cell by clicking the button and selecting the cell.

anchor-text-2On the Fields tab, select the fields desired from the Link section.

Metrics can be added by double-clicking in the list, or by selecting and clicking the >> button. Drag-and-drop to reorder the list of selected metrics.

Top Pages

top-pages-1

Click image to enlarge

This Analytics Edge wizard returns metrics for many URLs on a subdomain you specify. [Paid Moz API access required]

Enter the link URL (e.g. moz.com/blog) and select the sort and filter options.If you want more than the default 25 rows, enter a limit number.

You can get the URL from a worksheet cell by clicking the button and selecting the cell.

top-pages-2

Click image to enlarge

On the Fields tab, select the fields desired from the left side list.

Metrics can be added by double-clicking in the list, or by selecting and clicking the >> button. Drag-and-drop to reorder the list of selected metrics.

Link Metrics

link-metrics-2

Click image to enlarge

This Analytics Edge wizard returns information about links between a target URL and other pages and domains. Parameters let you receive a wide array or a narrow subset of results, with multiple options for how results are sorted. 

Enter the link URL (e.g. moz.com/blog) and select the scope and sorting options. Select a filter if desired. If you want more than the default 25 rows, enter a limit number.

You can get the URL from a worksheet cell by clicking the button and selecting the cell.

link-metrics-2

Click image to enlarge

On the Fields tab, select the fields desired from the Source, Links and Target sections.

Metrics can be added by double-clicking in the list, or by selecting and clicking the >> button. Drag-and-drop to reorder the list of selected metrics.

Url Metrics

url-metrics

click image to enlarge

This Analytics Edge wizard returns metrics for a URL or set of URLs you specify. Specify which metrics to return (e.g. moz.com/blog) and select the metrics from the list.

Metrics can be added by double-clicking in the list, or by selecting and clicking the >> button. Drag-and-drop to reorder the list of selected metrics.

You can also get the URLs from a worksheet cell range, by clicking the button and selecting the range.

* Moz Connector Help

moz-menuThe Moz Connector for Analytics Edge makes it easy to automate downloads from Moz directly into Microsoft Excel.

Before you can make a query, you must log in through the Accounts wizard.

The connector is available from both the free Analytics Edge Basic Add-in and the powerful Analytics Edge Core Add-in.

 

Watch Fullsceen

Analytics Reporting – Segments

segmentsThis Analytics Edge wizard is used to select a predefined Google Analytics segment from your account, or to define a dynamic one to be used solely for this query.

All system-provided segments are listed first, followed by your custom segments, alphabetically sorted.

When you select DYNAMIC from the drop-down, a second wizard will open to guide you through the next steps. To edit a dynamic segment, click the button provided.

 

MCF Reporting – Dates

ga-mcf-datesUse this Analytics Edge wizard to establish the date range for the report.

You can select a preset date range, like Past 7 days, or you can select a combination of start date, duration and/or end date.

For the dates, you can select a specific calendar date, pick from one of several preset dynamic dates like ‘start of last month’, or enter a date value.

When entering a date manually, you can type in the date (2015-01-25 format), use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date (Excel date or text in format of 2015-01-25).

Analytics Reporting – Sort

sortUse this Analytics Edge wizard to select the sort order to use for the query. Additionally, you can restrict the query to download a limited number of rows i.e. the top 10.

To sort on multiple columns, select each column in turn and click either Ascending or Descending. Columns will be sorted in the order entered.

Although Google’s API returns only 10,000 rows per query, Analytics Edge will transparently make multiple queries to get all your results. You can limit the query by entering a number other than zero.

Analytics Reporting – Dates

datesUse this Analytics Edge wizard to establish the date range for the report.

You can select a preset date range, like Past 7 days, or you can select a combination of start date, duration and/or end date.

For the dates, you can select a specific calendar date, pick from one of several preset dynamic dates like ‘start of last month’, or enter a date value.

When entering a date manually, you can type in the date (2015-01-25 format), use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date (Excel date or text in format of 2015-01-25).

Finally, you can select to use the date range chosen, or use the same duration period previous to the chosen date range, or use the same period from the previous year.

Analytics Reporting – Filters

filtersUse this Analytics Edge wizard to define filters to use with the query.

Select a Dimension or Metric, then pick a Comparison operator and enter a Value. Click the Add button. The filter will appear in the list at the bottom.

Use AND or OR logic to join multiple conditions in a single filter.

Note that all the metrics and dimensions used must be valid when used in combination with the metrics and dimensions of the query itself.

Use a Cell Reference

For advanced users, you can use a filter expression stored in a worksheet, BUT the expression must be encoded exactly as the API expects it to be.  See the API documentation for details. For example, to filter for City equals Ottawa, the expression would be ‘ga:city%3D%3DOttawa’.

To use a cell reference, click the button to the right of the entry box, then click the cell in the worksheet where the filter expression is stored.

 

Analytics Reporting – Fields

fieldsUse this Analytics Edge wizard to select the dimensions and metrics to be retrieved in the query.

Add/Remove Fields: Select a field in the listing on the left and click the Add button, or double-click on the field name to add it to the selected field list on the right. Dimensions and metrics are managed separately. You can add up to 7 dimensions and 10 metrics in a query.

Invalid Combinations: Not all combinations are valid, and Analytics Edge will warn you of invalid combinations by graying out some entries as you add fields.

Deprecated: Items marked with an asterisk (*) are deprecated by Google: this means that while they still work for now, they will stop working at a future date. By default, deprecated fields are hidden but they can be included by selecting the check box.

Descriptions: Selecting any field will show the associate API field name and description at the bottom.

Filter: to filter the list of dimensions and metrics, simply type a few characters into the filter box provided. The filter can match the display name or the actual API field name.

Pivot (Pro Connector Only): After selecting your dimensions for the query, you can select one of those dimensions to segment the results into columns. For example, you can select the Date dimension to obtain one column per Date in the results.

Note that the columns will be created in the order in which they appear in the query, so changing your sort order may affect the order of the columns. Also, there will be one column for each unique value, so absolute column position may change from query to query.

For more control over pivoted resulted, use the Pivot function in the Analytics Edge Core Add-in.

 

Analytics Reporting – Views

viewsUse this Analytics Edge wizard to select the Login account and the reporting View to use for the query.

You can enter a view (profile) id directly, or click the cell reference button and select a cell in the workbook to get the value from. View ID’s are the numbers displayed in the View (profile) selector. e.g. 76063423.

[Pro version: you can also enter an Analytics Edge [range] reference (Core Add-in required) to a column of view id values, and the connector will query multiple views and deliver the results in a single response, prefixed by a View ID and View Name column.]

* Google Analytics Help

google-analytics-pro-menuThe Analytics Edge connector for Google Analytics comes in 2 variants: Free and Pro. They share the same account and configuration files, so it is possible to switch between them or to start with the Free version and upgrade to the Pro without having to rebuild your reports.

Before you can make a query, you must log in through the Accounts wizard.

The Free version has some amazing capabilities, but it cannot do some things. The Pro version can also:

  • perform multi-view queries
  • access the Management Lists
  • access Multi-Channel Funnel data
  • Upload data
  • Save Filters
  • has full support of custom dimensions and metrics
  • can access the extra custom dimensions and metric available to GA Premium users

 

Configuration – Accounts

Before you can use the connector, you MUST log in to a valid a Google Analytics identity. Both connectors support multiple logins, and you can mix queries from different logins in the same workbook. One of the logins is designated as a Default, and is identified with an asterisk (*).

Select Accounts from the connector’s menu to open the Accounts wizard.

Enter a Reference Name for the account and click the Add Account button. You will step through a Google Analytics login sequence to authorize Analytics Edge to access your account. *

new-account

Select your login in the list, then pick an Account/Property/View combination and click the Make Default button.

* A note about security: your account credentials are stored, strongly encrypted, in a file on your computer and are sent direct to Google’s servers using an encrypted https link. Your credentials are not stored in your workbooks, making them safe to share. Analytics Edge (the company) has no access to your account information.

Configuration – Purchased License

google-analytics-pro-licenseThe connector will automatically register. The Pro version will operate free for 30 days, after which time a license must be purchased.

To enter a license code, select License from the connector’s menu and enter your code.