Author Archives: functions

Refreshing Queries

Refresh All Queries

Click the Refresh All button on the Analytics Edge ribbon to refresh all of your queries in the workbook at once.

refresh-all-queries

Refresh Current Query

click image to enlarge

click image to enlarge

To refresh only one query, select the worksheet cell where the query is based and click the Refresh – Current menu item from the drop-down below the Refresh All button.

Refresh Selected Queries

click image to enlarge

click image to enlarge

If you want to select which queries to refresh, click the Refresh – Selected menu item from the drop-down on the Refresh All button. In the dialog that opens, select the queries you want to refresh and click OK.

Auto-Refresh Pivot Tables

click image to enlarge

click image to enlarge

Microsoft Excel does not automatically update Pivot Tables in your workbook when you bring in new data, forcing you to manually refresh them from the Data ribbon. Analytics Edge has an option (turned on by default) that will trigger Pivot Table refreshes automatically whenever an Analytics Edge Refresh occurs. You can turn this feature off from the Refresh – Options menu item.

Moving a Query

Move a Query in the Same Workbook

To move an Analytics Edge query, simply move the comment to another location in the same worksheet, or another worksheet in the same workbook.

Move to Another Workbook – Cut and Paste

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Cut from the Analytics Edge ribbon.

remove-a-query

Then select the new location and select Edit Query – Paste from the Analytics Edge ribbon.

Edit a Query

To edit a specific query, click on the cell where the query is located (remember a comment was placed in that cell so you could find it easily). Click on the Edit button on the Analytics Edge ribbon bar. The original query wizard will open with all of your previous settings.

edit-a-query

Make changes as desired and save the query. See the new results in your worksheet.

Delete a Query

To delete an Analytics Edge query from your worksheet, you must select the cell where the query is located, then either delete the comment or select the Edit QueryCut menu item from the Analytics Edge ribbon bar.

remove-a-query

Copy a Query

Copy in the Same Workbook

To make a copy of an Analytics Edge query, simply copy the comment to another location in the same worksheet, or another worksheet in the same workbook.

Copy to Another Workbook

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Copy from the Analytics Edge ribbon.

edit-copy

Then select the new location for the copy and select Edit Query – Paste from the Analytics Edge ribbon.

Adding Queries

With the Analytics Edge Standard Add-in and optional Connectors, you can automate the download of data from a number of sources directly into Microsoft Excel worksheets. You can mix queries from multiple sources, and use different accounts for each query if you want; Analytics Edge makes it easy to refresh them all with a single click.

Here is a simple example of adding 2 Google Analytics queries to a worksheet. [images of Basic Add-in shown; Standard Add-in works the same]

Configuration

setup-google-analytics-account

click the image to enlarge

If you haven’t already logged in to the Google Analytics connector, you need to set up an account to use (see Accounts).

Make Your First Query

google-analytics-query

click the image to enlarge

Click on the Excel worksheet cell where you want your query results, and create a new Google Analytics query (see Your First Query).

See The Results

google-analytics-query-results

click the image to enlarge

The results will appear in the worksheet at the cell you had selected. Note there is a comment on the cell to remind you of the location of the query. Do NOT remove the comment or you will delete the query.

Make Another Query

click image to enlarge

click image to enlarge

Select a different cell to place the second query.

Create the new query and see the results in your worksheet.

Optional Free Trials

The Analytics Edge Standard Add-in comes with two free connectors by default, but you can also install free trials of many others with a simple click. You can start as many free trials as you want, but remember: the clock starts ticking right away so make sure you have to to evaluate them all.

ribbon-free-trials-available

Once the connectors have been installed, restart Microsoft Excel to activate them. They will automatically self-register for a fully functional trial period, and you can start using them right away.

Functions Wizard

Functions Wizard

The Analytics Edge Standard Add-in allows you to make several transformations to your data before you write it into your worksheet. Once your query has been created, a Functions Wizard pops up offering you the ability to Pivot, Arrange, Sort or Filter your data as well as options for when you Write To Worksheet.

Pivot Function

Columns to Keep – select and rearrange the columns you want to keep in the resulting table. Select ‘– No Pivot‘ to leave the data as-is.

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

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.

Arrange Function

No Change – do not change the order of the columns.

Arrange By 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.

Sort Selected – Use this option tab to sort the columns by the names of the columns themselves. Select the columns you want sorted

Sort Function

Select Columns and Order – 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.

Sorting Rules – you can change the order or remove any rules.

Filter Function

Add Filter Rule – construct one or more filters to be applied to the current table. Filters will be combined using AND logic, and you can choose to Keep or Remove the matching rows. While typically used to remove unwanted data from a report, it can also be used to selectively keep rows of interest.

Filter Rules – you can change the order or remove any rules.

Write to Worksheet Function

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 (numeric versus text versus date).

 

 

* Analytics Edge Standard Add-in

Love the product? Leave a comment in the Google Analytics Partners Gallery or share

Share on LinkedInTweet about this on TwitterShare on FacebookPin on PinterestShare on Google+Email this to someone

Your first step is to Register the add-in you have installed. Just start Excel and click the Register button on the Analytics Edge ribbon!

Installation problems?

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.

Help Is Just a Click Away

Click the HELP! button on any wizard or on the ribbon bar menus for additional information about any specific function or wizard feature. If the information available doesn’t answer your question, you can look in the Analytics Edge Simply Free support Forum for assistance, or send an email to [email protected]

query-commentStandard Add-in Operation

The Analytics Edge Standard Add-in works very much like the Analytics Edge Basic Add-in; it allows you to assign queries to cells in your worksheets. Simply select a cell and open a Connector wizard. The results of the query will be written into the worksheet starting with the selected cell. A comment will be placed on the cell as a reminder of the query location.

Note: the comment contains a pointer to the query. Copy, delete or move the comment within the same workbook and you will copy, delete or move the query.

The Standard Add-in also allows you to Pivot your results, Arrange the columns, Sort and Filter the results before Writing to a Worksheet. When your Connector query is finished, the Standard Add-in will open a Functions Wizard allowing you to make these transformations to your data.

A temporary worksheet (ᴁ Step Results) highlighted in green will appear with the interim results, so you can see the impact of your selections on a sample of the results. When you click the Write to Worksheet button, the temporary worksheet will disappear and the transformed results will be written to your worksheet.

Formatting your worksheet

The results are written to the worksheet, overwriting any previous content. You can format the results and subsequent refreshes will not alter the formatting — you are free to put your own personal look to the data. CAUTION: if you change the query, you will be asked if you want to clear the formatting; this is useful if you want to insert a column and do not want to use the  previous formatting in the cells.

Refresh All, Current or Selected

Click Refresh All to re-run all the queries in the workbook. To refresh only the query for the selected cell, select the drop-down menu below the Refresh button and pick Current. To refresh several queries at once, pick Selected from the drop-down menu.

Edit the Query at Any Time

You can modify the query by simply selecting the cell and clicking Edit. The previous settings are automatically inserted into the wizard, and you can make changes or cancel.

cut-copy-paste-queriesCut, Copy and Paste

If you want to delete, copy or move any of the queries, simply delete, copy or move the comments. If you want to copy or move the query to a different workbook, the Edit Query button has a drop-down menu with Cut, Copy and Paste functions that work across workbooks.

Sync Comments and Queries

If you copy or move any queries, or if you insert columns or rows causing the queries to move, the comments will be out-of-sync with the new locations. Simply click on one of the query cells to re-synchronize them.

Sort

The Sort tab lets you determine the sort order of the results of your query. Select the column and click the Ascending or Descending button to add a sorting rule. Sorting rules are applied in order, where matching values in the first sorted column are ordered by the next rule.

Dates

You can select from preset date ranges similar to what is available in the Bing Ads web interface, or you can pick a combination of start and end, start and duration, or end and duration.

Dates can be selected by specific calendar dates using pop-up calendars, by selecting a preset date offset, or by entering a date value. Date values can also be obtained from Excel cell references by clicking the button at the right.

The preset date offset is useful for making reports relative to today’s date: the most recent calendar month, for example.

Specific date values could be manually entered in the format of yyyy-mm-dd, or click the button to the right of the field to use an Excel cell reference to a date in your worksheet.

Filters

To add filters to your report, select an attribute to filter, then select values desired and add them to the filter by clicking the [ >> ] button. Multiple filters will be AND-ed, meaning the report will contains rows that pass all the filters.

Note that for fields that use a fixed set of values, a selector drop-down will appear. For fields that can accept free-form text, a text box will appear. To enter multiple values in a text box, enter them on separate lines.

Scope

The Scope tab allows you to restrict the report to specific accounts, campaigns or ad groups. Selecting an Ad Account will cause a Campaigns tab to appear with the campaigns in that ad account. Likewise, selecting a campaign will cause an Ad Groups tab to appear.

Selecting an item on the left side, then clicking the [ >> ] button will add it to the scope list on the right. The report will be restricted to the combination of items selected. Note that selecting a combination of items of different levels (account, campaign, ad group) may yield unexpected results.

Fields

The fields available for each report are listed in two groupings: Attributes and Metrics (numerical performance data). When you select a report, the default fields will be selected for you (listed at the right). You can add or remove fields from this list as desired, as well as reset to the default selections.

Note that the display name for each field is shown, consistent with the web interface, and they are listed in alphabetical order. The list of available fields varies depending on the report your selected.

Select from the list at the left and click the [ >> ] button to add the field name to the selected list at the right. To reorder the list of fields, simply drag and drop them in the list at the right.

Reports

The Analytics Edge Bing Ads Connector provides access to all the reports available from the Bing Ads API. When you open the Reports wizard, you will see a number of tabs, the first of which is Reports.

 

The Reports tab organizes the available reports in a similar fashion to the Bing Ads web interface, and consists of side tabs and selection buttons.

Down the left hand side, you will see some side tabs that group reports. When you select a report, the default Fields will be automatically selected on the other tab. You can modify those selections if you want.

Accounts

For security reasons, Analytics Edge stores your account information on your computer, not in the workbooks. Accounts are stored using Reference names, and the queries use a Reference name to identify which account to use.

Before you can log into an account, you must enter a Reference name in the field provided, then click the Add Account button.

After your have logged in, the account credentials will be stored (encrypted) on your computer so you can refresh reports without having to login each time.

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.

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

Customers

This Analytics Edge wizard lets you download the list of customer ids for your account.

The only option for this report is to select your account.

* Bing Ads Help

The Bing Ads Connector for Analytics Edge makes it possible to download data directly from the Bing Ads API into Microsoft Excel. The connector provides access to a variety of standard and customized reports.

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

List Reports

list-reportsUse this Analytics Edge wizard to get reports on your MailChimp contact lists.

Select the type of report desired, and enter a list ID (or click the right button to pop up a listing of contacts lists to choose from).

Depending on the report selected, additional options and selections may be necessary or optionally offered.

 

 

Lists

listsUse this Analytics Edge wizard to get information about all MailChimp contact lists, or about an individual list.

Additional selection criteria is offered depending on the options you choose.

Campaigns

campaignsUse this Analytics Edge wizard to get information about all MailChimp campaigns, or about an individual campaign.

Depending on your selections, additional criteria may be available.

Campaign Reports

campaign-reportsUse this Analytics Edge wizard to get reports for a selected MailChimp campaign.

Enter the campaign ID, or click the right button to pop up a list of available campaigns.

Depending on your selections, additional criteria may be available.

Page Posts – Dates

posts-datesThis Analytics Edge wizard lets you select the date range to be used for the query.

You can pick from several preset values, or pick a combination of start date, duration and/or end date.

Note that some metrics are not available for several days.

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

When entering a date manually, you can type in the date, use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date.

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.

Page Posts – Pages

posts-pagesThis Analytics Edge wizard allows you to select a Facebook account and page, and get back details about the posts on that page.

You can choose the account and page, or link to a worksheet cell for the account name and/or Facebook Page ID (the number portion displayed in the selector).

URL Shares

urlsharesThis Analytics Edge wizard is used to get the Facebook share metrics for any URL or list of URLs.

Single URL

Select a valid account to use (any account is ok), and enter the full URL that you want metrics for.

List of URLs

Enter * (an asterisk) for the URL to read a list of URLs from a worksheet. With the Analytics Edge Basic Add-in, the list will be obtained from the first column of the worksheet that the query is on. With the Analytics Edge Core Add-in, the list will be obtained from the first column of a worksheet with the same name as the macro that the query is in.

urlshares-output

The Metrics

The numbers that appear in the Facebook Like and Share buttons are shown as the Facebook Share Count.  The number is usually the same as the Facebook Engagement Count, which is a total of shares and likes. If you want just the Likes, use the Facebook Engagement Like Count number. Total comments are shown in the Facebook Engagement Comment Count metric, but that includes private and deleted comments; the public comment count is shown as Facebook Share Comments.

The old Facebook share and like metrics delivered by the old API (which was shut down Aug 7, 2016) may have been different, and Facebook has implied that they were less accurate. if you simply want a number for Likes, Comments and Shares, then use:

Likes = Facebook Engagement Like Count
Comments = Facebook Share Comments
Shares = Facebook Engagement Count – Facebook Engagement Like Count

Sort

Use this Analytics Edge wizard to select sorting options for your Facebook Ads query.

Note that only some columns can be sorted by the API. Sorting of other columns will need to be done in Excel or using the Analytics Edge Sore Add-in’s Sort function.

You can only sort on one selected column, in either Ascending or Descending order. Selecting No Sort will clear the selection.

insights-sort

Filter

Use this Analytics Edge wizard to set filter options for the Facebook Ads query.

By selecting options at the left, the filters will be update at the right. To enter metric-based filters, select the column, comparison and value then click Add to add the expression to the current filter list below.

insights-filter

Attributions

This Analytics Edge wizard lets you pick different attribution windows for your Facebook Ads Insights query. When you select attributions, new columns will appear in your report for each metric that has values for that attribution option.

insights-attribution

 

Analytics Reporting – Output

outputThe Output tab (available only in the Google Analytics Pro Connector) offers the ability to download direct to a CSV (comma-separated) file instead of returning the results to Excel. This may be useful for extra large downloads that might exceed the capacity of Excel.

Note: the Pivot capability is not supported for CSV downloads, and any selection will be ignored.

Caution: large downloads with many dimensions are subject to server overload errors (500 error code) which will stop the download.

Accounts

Use this wizard to log in to your Facebook Ads account and authorize Analytics Edge to access your data.

accounts

Enter a name for the account you want to login with, then click the Add Account button. Multiple accounts can be stored, and you can change which account gets used by default by selecting it and clicking the Make Default button.

 

Dates Tab

This Analytics Edge wizard lets you pick the date range for your Facebook Ads Insights query.

NOTE: only dates where the campaign/ad set/ad were active will appear.

You can pick from several preset values, or pick a combination of start date, duration and/or end date. With the start/duration/end selections, you can also offset the selected dates for the previous period or the same period in the previous year.

insights-dates

 

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

When entering a date manually, you can type in the date, use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date.

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.

Columns Tab

This Analytics Edge wizard lets you select the Facebook Ads Insights metrics you want.

Select the field you want on the left, then double click or click the Add button to copy it to the selected fields list on the right. Repeat for all the fields you want. Note that the Dimensions are determined by the selections you made on the Campaigns/Ads tab.

 

insights-columns

Campaigns/Ads Tab

Use this Analytics Edge wizard to select one of your saved logins, and the Facebook Ad Account, Campaign, Ad Set, or Ad you want to use for this query.

Login – Select your login account (set in the Accounts wizard), or use a cell reference to get the value from a worksheet.

Ad Account – select the ad account to use. Once selected, the list of campaigns will be displayed below. If you use the cell reference feature, use the account id in the format of “act_######”.

Select Item – select the Campaign, Ad Set or Ad that you want to report on, or use a cell reference to get the value from a worksheet. The value should be the id of the item (campaign_####, adset_####, or ad_####).

insights-campaigns

Reporting Level – you can select a Campaign above, and report at the campaign, ad set or ad level producing new report lines for each ad, for example.

Breakdown – for each item, break down the results by the selections made. Combinations can be made but some combinations are not allowed and will result in an error.

* Facebook Ads Help

The Facebook Ads Connector for Analytics Edge makes it possible to automate your Facebook Ads Insights downloads into Microsoft Excel. Get just the metrics you want, formatted for use in Excel.

Before you can use the connector, you MUST log in to a valid Facebook account (with the Accounts wizard) and have ads_read access.

ribbon

Automations

automationsUse this Analytics Edge wizard to get a listing of the MailChimp Automations running in your account.

You can request a list of all Automations, or enter a workflow ID to get a specific one.

Social Shares Free

social-shares-freeThis Analytics Edge wizard lets you collect social share counts from various services.

Enter a URL and select the social sources that you want to see metrics for. The query will return counts from each service selected.

To obtain counts for a number of URLs, enter the URLs in column A, and enter ‘*’ for the URL.

Note: Facebook and Twitter counts are no longer publicly available, and are no longer available from the connector.

Append To Worksheet

appendtoworksheetThis Analytics Edge wizard is used to append the current table of data to the end of an existing worksheet.

If the worksheet is empty, the function will create a new header row.

If there is already data on the worksheet, data will be appended to the bottom of matching columns (by the caption/label in the header row). If a matching column does not exist, a new one will be created.

 

Comment

comment-wizardCreate a comment in the current macro.

The short comment will appear in the Task Pane with a leading # symbol. Comments are for documentation purposes only and serve no operational function.

The long comment is only visible when you edit the function step.

SiteAdmin URL Crawl Errors Details

wt-siteadmin-urlcrawlerrorsdetailsDownload a sample (up to 9) of the URLs pointing to a specific page URL with crawl errors.

Account – Either select from the dropdown list provided, or enter a value or an Excel cell reference. You can use the button provided to select a cell in an existing worksheet.

Site – Enter the website URL or an Excel cell reference. You can use the button provided to select a cell in an existing worksheet, or the second button to lookup your available websites.

Category – Select a crawl error category or ‘all’ for all categories.

Platform – Select the user agent type (platform) or ‘all’ for all categories.

PageUrl – enter the URL of the page (without the site). It must be one of the pageUrl values returned by the SiteAdmin URL Crawl Errors Samples query.

Notes about usage:

Using the Analytics Edge Core Add-in, this query would typically be used following a SiteAdmin URL Crawl Errors Samples query, and you would enter the Analytics Edge range name ‘[pageUrl]’ to download details for all of the pages returned by the previous query.

crawl-errors-details

Task Pane

taskpaneThe “heart” of the Analytics Edge Core Add-in is the Task Pane, which opens automatically to let you create, run and edit Analytics Edge macros.

Analytics Edge macros are not the same as Excel macros; they are stored in a hidden worksheet and can only be run if the Analytics Edge Core Add-in is installed with the necessary connectors.

When you create an Analytics Edge macro, you must assign it a name. If you do not assign a name (leave the field empty and click OK), then Analytics Edge will assign it the name of the currently selected worksheet cell. This is consistent with how the Analytics Edge Basic Add-in works.

Macro Execution

When a macro runs, progress will be displayed in the task pane, coloring completed functions green and the current function yellow. If you Step through a macro, intermediate step results will be displayed in the temporary AE Step Results worksheet (green background). Clicking Stop or executing a SaveToWorksheet function will remove that temporary worksheet.

Macro Buttons

macro-buttons

Macros are selected from the drop-down, and can be run by clicking the Run button. You can also step through a macro one function at a time, or stop a macro in mid-execution. Note that the current step may complete before the execution is stopped.

Changes to macros can be undone (or re-done) for as long as you keep the workbook open. Once you close the workbook, all changes to the macro are saved and previous versions are lost.

The macro can also be renamed or deleted by clicking the appropriate button.

Function Buttons

function-buttons

Individual function steps in your macro can be selected by clicking on them. You can select multiple steps by holding Ctrl of Shift down while clicking, on by click-dragging down a range of functions.

Once selected, functions can be rearranged, edited, deleted, copied or pasted by clicking the appropriate button.

Copy / Paste

You can select any number of function steps and click the Copy button, then select the destination macro and click Paste. You can copy within the same macro, within macros in the same workbook, and even between macros in different workbooks. Note: the destination macro must exist before you start the copy/paste operation. If the Paste button does not appear enabled when you switch to the destination macro, try re-selecting that macro in the drop-down selector.

 

 

Sort

The Sort tab lets you determine the sort order of the results of your query. Select the column and click the Ascending or Descending button to add a sorting rule. Sorting rules are applied in order, where matching values in the first sorted column are ordered by the next rule.

ad-sort

By default, the report should sort in the order that the columns appear.

Search Analytics

search-analytics-fieldsThis Analytics Edge wizard lets you make a query for Google Search Analytics data.

Account – pick the Login account to use.(set by the Accounts wizard).

Site – pick the website you want to report on.

Dimensions – select the dimensions you want in your results. Note that date cannot be combined with any other dimension.

Metrics – all metrics are always included and the results are always sorted in order they appear. You can change the order of the metrics by dragging them in the list. Note that queries by date are always sorted by date.

search-analytics-filtersFilter – enter or select the filtering options you want with this query. They can include any combination of:

  • Queries that contain certain phrases
  • Pages with certain terms in them
  • Country
  • Device type (desktop/mobile/tablet)
  • Type of query (web/image/video)

search-analytics-datesDates – pick a date range for the query. Note that you are limited to about the last 90 days, and there is no data for the most recent couple of days. The range will be adjusted automatically if data is not available for the dates you request.

 

The Google Search Analytics API is still undergoing development and new features/capabilities may be added over time.

The query will currently return all available rows, which could be as many as 5000 or less depending on the dimensions selected.

Customers

ad-customersThis Analytics Edge wizard lets you download the list of customer ids for your account. With regular AdWords accounts, it will list your account, but for MCC accounts, it will list all customer accounts associated with the MCC account.

The only option for this report is to select your account.

Save Filter

ga-save-filter[Pro connector] Use this Analytics Edge wizard to save one or more view filters to one or more Google Analytics views.

The filter(s) must be defined in a worksheet using columns that match what is generated by the Management Lists – Filter function.

The Login to be used must either be selected or obtained from a worksheet link. NOTE: only logins that have been created with Manage access checked in the Account wizard will work!

The target Google Analytics accounts and views must be listed in a worksheet using columns that match what is generated by the Management Lists – Account Summary function.

Overwrite existing filter in Account – this options looks for a filter with the same name in the target Account, and if found, overwrite the filter definition with a new one. If the filter has not yet been linked to the selected View, a new link is created. This options is useful when you want the same filter everywhere.

Overwrite existing filter in View – this option looks for a filter with the same name in the target Account AND a link to the selected View. If you are saving to multiple Views in the same Account, this option will create a unique copy of the filter (with the same name) in the target Account with a link to a specific View. This option is useful when you might want different filters in some Views.

Do not overwrite existing filter – this option creates a new filter in the Account and links it to the View for each entry. This option is useful when adding a filter and do not want to mistakenly overwrite a previous filter that just happens to have the same name.

For more information, see Using the Save Filter Function

 

Bulk Queries – Queries

ga-bulk-queries

Click image to enlarge

Use this Analytics Edge wizard to select the Query Configuration Worksheet for Bulk (multiple) Google Analytics queries. Each column on the query configuration worksheet identifies a different query to be run and the worksheet / cell location where the results should be written.

+ New Query – create a new query using the wizard. Queries can also be created manually in the worksheet.

Name (click to edit) – lists the existing queries. Click on the link (name) to open the query for editing using the wizard interface. Queries can also be edited manually in the worksheet.

Location – identifies where the query results will be written, in the form of ‘worksheet’!cell.

ga-bulk-query-configurationQuery Configuration Worksheet

The Query Configuration Worksheet contains a separate column for each query. You can create and edit the queries using the wizard, or you can manually edit the worksheet as you require. Cell values can be manually entered or you can use an Excel formula (such as =TODAY()-1 for an end date).

Note that if you use formulas, the wizard will not modify them if you edit the query using the wizard interface.

To understand what values can be used for each parameter, use the wizard to construct your query. Some of the more advanced parameters may require understanding of the Google Analytics API.

Reference: https://developers.google.com/analytics/devguides/reporting/core/v3/reference

Bulk Queries – Options

ga-bulk-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.

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.

Bulk Queries – Segments

ga-bulk-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, you can enter a segment expression in the text box below. Any expression can be entered — click the reference link in the wizard to open the Google Analytics API reference guide for segments.

Bulk Queries – Sort

ga-bulk-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.

Bulk Queries – Dates

ga-bulk-dates[Pro connector] Use 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.

Bulk Queries – Filters

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

Select the 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 metrics and dimensions used must be valid when used in combination with the metrics and dimensions of the query itself.

Bulk Queries – Fields

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

Select a field in the listing on the left and click the [ >> ] 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.

Not all combinations are valid. To see Google’s authoritative listing, click the link provided on the wizard. Invalid selections will appear gray in the list.

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. In most cases, there is a second metric or dimension with the same or similar name. e.g. “Users” refers to the API field ga:users, but “Users*” refers to the old ga:visitors API field, which has been replaced by ga:users.

Pivot

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.

Bulk Queries – Views

ga-bulk-viewsUse this Analytics Edge wizard to select the Login account to use for the query, plus the Account, Property, and View.

Click Add> to add the selected View to the query. Multiple Views can be added, but they must all be available from the same Login.

 

Lookup

lookupThis Analytics Edge wizard lets you quickly lookup values from another Table or Worksheet that share an index value with the current table.

Select the column in the current table in memory to use an an index value (like a campaign_id), then select the reference Table or Worksheet as well as the index column that contains matching values (like id).

Then select the columns you want to add to the current table from the reference table. To select more than one column, hold the Ctrl-key down while you click.

If more than one row in the reference source has a particular matching index value, you can select how you want to handle those duplicates.

Example

You may have a table of email campaign clicks with a “campaign_id” column:

clicks

And you want more information about that campaign. You also have a worksheet containing Campaign data, but the campaign id column is labelled simply “id“:

campaigns

You can select the campaign_id and id columns as the Index, then select the name and modified_date columns from the list (as per the image above).

*Payment and Access to Your Google Account

 

If you want the personalized spam filter service for your web site, you need to:

  1. Pay here:   fastspring.com
  2. Give me access to your Google Analytics Account (as follows)

Granting Access to Your Google Analytics Account

Do NOT send me your account and password! Instead, follow these simple steps:

  • log into Google Analytics
  • select the Admin section
  • select the Google Analytics ACCOUNT to be filtered
  • click User Management in the left (ACCOUNT) column

user-management

  • Add permissions for: [email protected](note this is different from the image)
  • Select Edit in the list of access permissions
  • Check the box to Notify this user by email
  • Click Add

Providing access

If you have multiple websites in your Account, please send me an email identifying which ONE property/website you want me to process: [email protected]

 

What Will Happen Next

After payment and proper access is granted. I will:

  • analyze your site
  • add 2 new Views (unfiltered and test) if needed
  • install spam filters into the test view
  • send you an email explaining what I found, what filters I installed
  • send you a new Custom Segment that you can use immediately with your reports

After a week, I will confirm that the new filters are working properly in the test view, and if there are no issues, I will apply them to your main website view.

At any time, you are free to ask any questions you want about what has been done.

 

Removing Access

When your service period is ended, you should remove my access to your account.  (I will remove myself eventually if you don’t)

In the Google Analytics Admin panel, select the User Management section for your account, identify the entry for [email protected] and click the delete link.

removing-access

All Users (property-name) Segment

Filters will prevent new spam referrals from appearing in your account, but they do not work retroactively. As part of the service, I create a new advanced segment called All Users (website-name). It can be used for historical reporting, eliminating the spam traffic and giving you clean metrics.

Getting Your Spam Filter Segment

Segments are associated with login accounts. To get the new Segment, you will be provided with a link by email. Log into Google Analytics, then click the link provided. Select the option Any viewNote: your segment will appear with a different name than shown.

shared-segment

The click the Save button to save the segment in your account.

 

Using the New Spam Filter Segment

To use it, open your Google Analytics report and click on the All Users box at the top. In the list of Segment Names, you should see that All Users is selected (checked) and a new entry just below it All Users (<your-website-name>).

Uncheck All Users and check All Users (<your-website-name>). Your reports now reflect only real traffic to your website. Note: your segment will appear with a different name than shown.

spam-segment-selection

 

AdWords Users:  note that AdWords data is imported into Google Analytics without a hostname value, so the Valid Hostname filter will cause the segment to drop all AdWords cost and ad information if you apply it. View the AdWords reports with the All Sessions segment.

*Personalized Spam Filter Service

Proactive filtering, daily monitoring, personalized service
Only $75 for 12 months of protection!  Start Now!
“Thank you, Mike.  This was clearly worth the small investment.”

spam-summary-2016

Spam sources appearing in Google Analytics in 2016

If you have Google Analytics, you have referral spam (and language spam, landing page spam, and even keyword spam). I will make it go away! I install and maintain the filters you need to keep spam out of your reports. I will even send you a custom segment you can use to clean up your existing Google Analytics reports. Includes protection from language spam, event spam, landing page spam, and plain old referral spam.

spam-filtered-report

FAST: Results usually in the same day. I will analyze the traffic to your website and identify valid host names and spammy crawlers, then build the filters and advanced segment appropriate for your website.

SAFE: I will not put your website data at risk. I will create an Unfiltered view before I apply any filters. I will install the filters into a new Test view, so you can see exactly what the impact of the filters will be. After a week of testing, I will apply the filters to your main view (usually called All Web Site Data).

PERSONALIZED: This isn’t a cookie-cutter solution — I review your website configuration and make sure everything will work for you. Multiple domains, ecommerce, and/or call tracking events all have subtle requirements that need to be taken into account when filtering traffic.

EXPERIENCED: I coined the term “ghost visit” and wrote the Definitive Guide to Removing All Google Analytics Spam and have updated it regularly since January 2015. I have helped thousands of people eliminate the spam from their Google Analytics accounts, and I can help you!

TRUSTED: Michael-Sullivan-150x150My identity and contact information is no secret. Check me out on Google: “Mike Sullivan Analytics Edge”. Read my LinkedIn profile. Send me an email; ask me questions. This is my business. This is what I do.

You are in good hands, so jump right in.  Start Now!

Perfect! Thank you so much!

…thank you very much, this was the fastest service I have ever received online.

“…your segment works…which means I can report on accurate data for the first time…

Wow.  Clean data.  This feels good.

Real traffic from real websites — orange: with spam, blue: without spam.

spam-combo

Contact – Add Contacts

add-contactUse this wizard to add contacts to your Constant Contact account. This function is not fast and should only be used for small numbers of contact additions.

Action By – Selecting ACTION_BY_OWNER allows you to add contacts to your lists without triggering any autoresponders in your account. ACTION_BY_OWNER will trigger autoresponders and welcome messages if you have them.

Source of Data – requires use of the Analytics Edge Core Add-in to create an in-memory Table to use as a data source. The selected Analytics Edge Table should have columns similar to those you would get when downloading your contacts list with Analytics Edge.

Account Information

This wizard (actually the bottom of the Constant Contact Accounts wizard) is used to get information about a Constant Contact account you have authorized.

account-information

Select the account or reference a worksheet cell (click the button provided) where an account name has been entered, then click the Save button to save this function.

 

Schedule Manager

schedule-manager

click image to enlarge

This Analytics Edge utility is part of the Core Add-in, and is used to manage all of the Analytics Edge scheduled refreshes. If can be launched from the Schedule Refresh wizard, and will open automatically following any scheduled refresh.

Contents can be sorted and filtered in multiple combinations, and selected workbooks can be bulk refreshed (Run Now), or have their schedules changed (Reschedule), Disabled, Enabled or Deleted. Select the workbooks by checking the boxes in the left column, then pick the action to be performed at the top and click the Go button. Individual schedules can be modified by clicking the edit link.

Individual workbooks can be opened by clicking on the File name. Columns can be sorted by clicking on the column titles. You can filter for characters in the file name by typing in the Filter box, and further restrict the view by selecting the Folder, Type or Status values. The view can be refreshed by clicking the refresh button.

Details of the most recent refreshes can be seen by clicking the Log button.

Files can be scheduled in bulk by clicking the +Add Scheduled Refresh and selecting multiple files. The refreshes will be scheduled 1 minute apart starting at the time selected.

Status Messages

Each scheduled refresh is assigned a status based on the results of the last attempted automatic refresh. Note that if you manually open and refresh a workbook, it will not update the status, but if you select the workbook and pick Run NowGo, it will update the status.

ok – the refresh completed without error.

New – the refresh has not yet been run to completion. Note: if the computer is restarted after the scheduled First Start date, the refresh will run even if it is not scheduled to run that day. It is assumed the schedule was missed.

#Running – the scheduled refresh is currently running. Note that the process may not be visible in Windows. If a scheduled refresh remains in this state for more than an hour, it may be necessary to reboot your computer to clear the background process. If you do this, note that Excel may disable the Analytics Edge add-in, and you may have to re-enable it manually.

?See Log (macro error) – the refresh did not report an ok status upon completion. Usually this is caused by a macro error. See the Log in case there is indication of the failure.

?See Log (refresh crash) – the refresh did not complete withing the timeout period and may have failed unexpectedly. Check the workbook and manually refresh to see what the problem could be.

?See Log (no response) – the refresh ended without reporting success or failure. Check the file and refresh manually to see whether there is a problem.

!Disabled – the scheduled refresh has been disabled and will not run until re-enabled. Use this state to pause refreshes without deleting them.

!Not logged in – Windows attempted to run the scheduled refresh but the user was not logged in at the time. Run the Refresh at your earliest convenience.

!Problem with Scheduled Item – the scheduled task is not recognized as an Analytics Edge Scheduled Refresh. Recreate the scheduled item or delete the task.

!Not an AnalyticsEdgeRefresh – the scheduled refresh is not a properly configured Analytics Edge refresh. Delete and re-create the refresh schedule.

!Excel cannot be found – the instance of Excel on your system has moved since the refresh was scheduled. You will need to reschedule the refresh.

!Not an Excel workbook – the scheduled refresh does not point to a supported Excel file type (xls, xlsx, xlsm, xlsb).

!File does not exist – the schedule points to a workbook file that has been moved or deleted. Note that it may not be possible to refresh workbooks on network drives.

!Queue File Locked – if the refresh was unable to get access to the refresh queue file after several attempts, the file may be locked by a failure of you may have too many refreshes scheduled at the same time. It may be necessary to reboot your computer to clear the file lock.

!Aborted – the refresh was somehow aborted and will need to be rerun.

!Error <message> – the refresh reported an error. Open the workbook and refresh manually to confirm the error was not intermittent (server/service unavailable).

 

 

SiteAdmin URL Crawl Errors Samples

wt-siteadmin-urlcrawlerrorssamplesDownload samples of the crawl errors for the selected web property.

Account – Either select from the dropdown list provided, or enter a value or an Excel cell reference. You can use the button provided to select a cell in an existing worksheet.

Site – Enter the website URL or an Excel cell reference. You can use the button provided to select a cell in an existing worksheet, or the second button to lookup your available websites.

Category – Select a crawl error category or ‘all’ for all categories.

Platform – Select the user agent type (platform) or ‘all’ for all categories.

SiteAdmin URL Crawl Errors Counts

wt-siteadmin-urlcrawlerrorscountsDownload the crawl error counts for the selected web property.

Account – Either select from the dropdown list provided, or enter a value or an Excel cell reference. You can use the button provided to select a cell in an existing worksheet.

Site – Enter the website URL or an Excel cell reference. You can use the button provided to select a cell in an existing worksheet, or the second button to lookup your available websites.

Latest counts only – check this box to restrict the results to the most recent results only. Uncheck the box to get the past 3 months of daily data.

Category – Select a crawl error category or ‘all’ for all categories.

Platform – Select the user agent type (platform) or ‘all’ for all categories.

 

SiteAdmin Sitemaps

wt-siteadmin-sitemapsDownload a list of the sitemaps that have been detected.

Account – Either select from the dropdown list provided, or enter a value or an Excel cell reference. You can use the button provided to select a cell in an existing worksheet.

Site – Enter the website URL or an Excel cell reference. You can use the button provided to select a cell in an existing worksheet, or the second button to lookup your available websites.

 

SiteAdmin Sites

wt-siteadmin-sitesDownload a list of the sites and permission levels for the selected login.

Account – Either select from the dropdown list provided, or enter a value or an Excel cell reference. You can use the button provided to select a cell in an existing worksheet.

 

Post Insights – Pages

fb-post-insights-pagesUse this Analytics Edge wizard to select one of your saved logins, and the Facebook Page you want to use for this query.

You have the option of selecting from the drop-down lists or identifying  a worksheet cell that contains the information. Note that Pages are identified only by their ID number.

Post Insights – Fields

fb-page-insights-fieldsThis Analytics Edge wizard lets you select the Facebook Post Insights metrics you want.

Multiple unsegmented metrics can be selected at once, and they will be shown side-by-side in the report, listed by date.

Segmented metrics must be individually selected since they create a table of values by date.

All post metrics are only available as lifetime totals to date.

Post Insights – Dates

fb-post-instghts-datesThis Analytics Edge wizard lets you pick the date range for your Facebook Post Insights query. All posts created in this date range will be included in the results.

You can pick from several preset values, or pick 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 dynamic dates like ‘start of last month’, or enter a date value.

When entering a date manually, you can type in the date, use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date.

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.

* GA Management Help

ALPHA SOFTWARE WARNING

This release of the Google Analytics Management connector is early in the product development lifecycle. It does not have all of the planned features implemented, nor has it been extensively tested. USE WITH CAUTION.

The GA Management connector is intended for individuals managing a number of Google Analytics properties (logins, accounts, properties and views). In this initial release, the connector makes it possible to easily copy filter definitions from one account to another. Additional functionality is coming in future releases.

Please contact [email protected] with any feedback.

 

Copy Filter

gm-copyUse this Analytics Edge wizard to copy the selected filter between accounts and to assign it to other views in the same account.

Select the filter, then pick the account/property/views you want the filter applied to, and click the Copy Filter button.

The filter is copied to other accounts using the filter name as a unique identifier. So if you copy a filter called “spam” to an account that already has a filter called “spam”, the existing filter will be replaced. Note that Google Analytics allows you to have multiple filters with the same name, but Analytics Edge uses only the name when you copy between accounts, so it is strongly recommended to avoid having multiple filters with the same name.

CAUTION: THIS IS ALPHA-VERSION SOFTWARE; IT IS NOT FEATURE COMPLETE AND IS SUBJECT TO CHANGE WITHOUT NOTICE.

Accounts

accountsUse this wizard to log in to your Facebook 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. Multiple accounts can be stored, and you can change which account gets used by default by selecting it and clicking the Make Default button.

 

Insights – Dates

datesThis Analytics Edge wizard lets you pick the date range for your Facebook Insights query.

You can pick from several preset values, or pick a combination of start date, duration and/or end date.

Note that some metrics are not available for several days, so there is a preset for 4_DAYS_AGO and an end date option for 4 Days Ago, which should get you the most recent values.

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

When entering a date manually, you can type in the date, use an Analytics Edge range name (like [Date]), or enter a cell reference to a worksheet containing a date.

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.

Insights – Fields

fieldsThis Analytics Edge wizard lets you select the Facebook Insights metrics you want.

Multiple Totals metrics can be selected at one, and they will be shown side-by-side in the report, listed by date.

Segmented metrics must be individually selected since they create a table of values by date.

There are daily totals (day period) available, as well as running 7-day totals (week period) and running 28-day totals (days_28 period). Some metrics are only available as lifetime totals. Selecting a period of all will get you all available periods for the metrics selected.

Insights – Pages

pagesUse this Analytics Edge wizard to select one of your saved logins, and the Facebook Page you want to use for this query.

You have the option of selecting from the drop-down lists or identifying  a worksheet cell that contains the information. Note that Pages are identified only by their ID number.

* Facebook Help

The Facebook Connector for Analytics Edge makes it possible to automate your Facebook Insights downloads into Microsoft Excel. Get just the metrics you want, formatted for use in Excel.

facebook-connnector

Before you can use the connector, you MUST log in to a valid Facebook account (with the Accounts wizard) and have read_insights access for at least one Facebook Page.

Optional Free Trials

The Analytics Edge Basic Add-in comes with two free connectors by default, but you can also install free trials of many others with a simple click. You can start as many free trials as you want, but remember: the clock starts ticking right away so make sure you have to to evaluate them all.

ribbon-free-trials-available

Once the connectors have been installed, restart Microsoft Excel to activate them. They will automatically self-register for a fully functional trial period, and you can start using them right away.

Moving a Query

Move a Query in the Same Workbook

To move an Analytics Edge query, simply move the comment to another location in the same worksheet, or another worksheet in the same workbook.

Move to Another Workbook – Cut and Paste

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Cut from the Analytics Edge ribbon.

 

remove-a-query

Then select the new location and select Edit Query – Paste from the Analytics Edge ribbon.

 

Copy a Query

Copy in the Same Workbook

To make a copy of an Analytics Edge query, simply copy the comment to another location in the same worksheet, or another worksheet in the same workbook.

Copy to Another Workbook

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Copy from the Analytics Edge ribbon.

edit-copy

Then select the new location for the copy and select Edit Query – Paste from the Analytics Edge ribbon.

 

Refreshing Queries

Refresh All Queries

Click the Refresh All button on the Analytics Edge ribbon to refresh all of your queries in the workbook at once.

refresh-all-queries

Refresh Current Query

click image to enlarge

click image to enlarge

To refresh only one query, select the worksheet cell where the query is based and click the Refresh – Current menu item from the drop-down below the Refresh All button.

Refresh Selected Queries

click image to enlarge

click image to enlarge

If you want to select which queries to refresh, click the Refresh – Selected menu item from the drop-down on the Refresh All button. In the dialog that opens, select the queries you want to refresh and click OK.

Auto-Refresh Pivot Tables

click image to enlarge

click image to enlarge

Microsoft Excel does not automatically update Pivot Tables in your workbook when you bring in new data, forcing you to manually refresh them from the Data ribbon. Analytics Edge has an option (turned on by default) that will trigger Pivot Table refreshes automatically whenever an Analytics Edge Refresh occurs. You can turn this feature off from the Refresh – Options menu item.

Edit a Query

To edit a specific query, click on the cell where the query is located (remember a comment was placed in that cell so you could find it easily). Click on the Edit button on the Analytics Edge ribbon bar. The original query wizard will open with all of your previous settings.

edit-a-query

Make changes as desired and save the query. See the new results in your worksheet.

Delete a Query

To delete an Analytics Edge query from your worksheet, you must select the cell where the query is located, then either delete the comment or select the Edit QueryCut menu item from the Analytics Edge ribbon bar.

remove-a-query

Adding Queries

With the Analytics Edge Basic Add-in and optional Connectors, you can automate the download of data from a number of sources directly into Microsoft Excel worksheets. You can mix queries from multiple sources, and use different accounts for each query if you want; Analytics Edge makes it easy to refresh them all with a single click.

Here is a simple example of adding 2 Google Analytics queries to a worksheet.

Configuration

setup-google-analytics-account

click the image to enlarge

If you haven’t already logged in to the Google Analytics connector, you need to set up an account to use (see Accounts).

Make Your First Query

google-analytics-query

click the image to enlarge

Click on the Excel worksheet cell where you want your query results, and create a new Google Analytics query (see Your First Query).

See The Results

google-analytics-query-results

click the image to enlarge

The results will appear in the worksheet at the cell you had selected. Note there is a comment on the cell to remind you of the location of the query. Do NOT remove the comment or you will delete the query.

Make Another Query

click image to enlarge

click image to enlarge

Select a different cell to place the second query.

Create the new query and see the results in your worksheet.

Write to Current Cell

The Core Add-in includes this shortcut menu item that mimics the functionality of the Basic Add-in. At any time during macro development, the current active table in memory can be quickly written to a specific cell on a specific worksheet.

Select the worksheet and cell, then choose the menu item to Write to Current Cell. A new Write to Worksheet command will be added to the macro to write the active table in memory to the specified location. You can edit the command to change the options if desired.

Manage Connectors

Get the list of available connectors by clicking the Manage Connectors button on the Analytics Edge ribbon bar in Excel.

manage-connectors-button

The Manage Connectors wizard will open, showing a list of the currently installed and available connectors. If an upgrade is available, you will see both the version of the installed connector and the newest version available. To see details of a specific connector release, click the row in the listing.

manage-connectors

 

To Install or Upgrade a Connector

Details about the current release will appear in the install wizard. The wizard will always show the most current release available. To install a new connector or upgrade to a new release, click the Install button. You will be notified that you need to restart Microsoft Excel for the changes to take effect. If you want to install other connectors, click the < Back button and repeat these steps. Restart Excel when you are done.

manage-connectors-install

 

To License a Paid Connector

When you purchase license(s) for Analytics Edge products, you will be sent a license code for each product (the add-ins and connectors have separate licenses). To activate a connector license, you must open the Manage Connectors wizard and click into the connector, then enter the New License Key and click the Activate button. You should get a “License activated!” response.  Restart Excel to start using the new license.

manage-connectors-activate-license

 

Uninstall a Connector Release

To remove a connector, open the Manage Connectors wizard and click on the connector entry. Click the Uninstall button. Restart Excel for the change to take effect. Note that if you have upgraded a connector, when you restart Excel you will be asked if you want to delete all the older versions as well.

 

* Analytics Edge Basic Add-in

Love the product? Leave a comment in the Google Analytics Partners Gallery or share

Share on LinkedInTweet about this on TwitterShare on FacebookPin on PinterestShare on Google+Email this to someone

Your first step is to Register the add-in you have installed. Just start Excel and click the Register button on the Analytics Edge ribbon!

ribbon

Installation problems
Руководство к Analytcs Edge на Русском

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.

register

Help Is Just a Click Away

help-and-supportClick the Help button on any wizard or on the ribbon bar menus for additional information about any specific function or wizard feature. If the information available doesn’t answer your question, post to the Analytics Edge Simply Free support forum for assistance. A button is provided right in the ribbon bar for your convenience. Note: email support is NOT provided for the free products.

query-commentBasic Add-in Operation

The Analytics Edge Basic Add-in allows you to assign queries to cells in your worksheets. Simply select a cell and open a Connector wizard. The results of the query will be written into the worksheet starting with the selected cell. A comment will be placed on the cell as a reminder of the query location.

Note: the comment contains a pointer to the query. Copy, delete or move the comment within the same workbook and you will copy, delete or move the query.

See the Results

The results are automatically written to the worksheet, overwriting any previous content. Note that you can format the results and refreshes will not alter the formatting — you are free to put your own personal look to the data. CAUTION: if you change the query, you will be asked if you want to clear the formatting; this is useful if you want to insert a column and do not want to use the  previous formatting in the cells.

Refresh All, Current or Selected

Click Refresh All to re-run all the queries in the workbook. To refresh only the query for the selected cell, select the drop-down menu below the Refresh button and pick Current. To refresh several queries at once, pick Selected from the drop-down menu.

Edit the Query at Any Time

You can modify the query by simply selecting the cell and clicking Edit. The previous settings are automatically inserted into the wizard, and you can make changes or cancel.

cut-copy-paste-queriesCut, Copy and Paste

If you want to delete, copy or move any of the queries, simply delete, copy or move the comments. If you want to copy or move the query to a different workbook, the Edit Query button has a drop-down menu with Cut, Copy and Paste functions that work across workbooks.

Sync Comments and Queries

If you copy or move any queries, or if you insert columns or rows causing the queries to move, the comments will be out-of-sync with the new locations. Simply click on one of the query cells to re-synchronize them.

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.