Exporting Reports using Infinity API

 

Data for the reports you view in Infinity Analytics can be exported using the Infinity API.  From the reporting user interface, click to export report data in your desired format, such as CSV, JSON, or XML.  The initial export request processes the data and packages the export file, and then a subsequent request retrieves the export file.

How To Export Report Data

  1. When viewing a report in Infinity Analytics, click the Exporting and Sharing icon above the chart.
    Help_exports1-5_Image-1
  2. Choose the desired format.  CSV, JSON, and XML are supported.
    Help_exports1-5_Image-2
  3. After selecting the format, the Infinity API’s REST URL will display.  You can choose to open this URL in a new browser window or copy it to clipboard to paste elsewhere.
    Help_exports1-5_Image-3
  4. Opening the REST URL in a new browser window, for example, will prompt you to enter your username and password.
    Note, you must have the “View Only” access right for Infinity Analytics, at a minimum, to successfully export data.  You must also have access to the account, collection, and report.  An error results without proper access.
  5. After entering your username and password, the request for data is submitted and you will see a response message:
    "state":"Executing",
    "message":"This report is processing.  Export processing time depends on the amount of data requested.  If you are viewing this message in a browser, hit Refresh to get the latest status.  If processing is complete, Refreshing the browser will return the export file."
  6. Submit the request again (or refresh) to retrieve and download the export file.

Note, the date range of the export is the same date range as seen when viewing the report in the user interface.  The REST URL can be edited to change the export’s date range.  See “Date Range” in the “Optional Settings and Parameters” table below.

How to Change the Dimensional Row Distribution in Exports

A maximum of 3,000,000 dimensional rows of data can be exported.  The first dimension will default to 1,000 rows and the remaining rows are distributed across the sub-dimensions in your report. You can change the distribution of how many rows are exported per dimension by using the “limit” parameter.

Exported data is distributed across dimensions in your report using the following default logic:

One Dimension Reports 1st Dimension – limit to 1,000 rows
Two Dimension Reports 1st Dimension – limit to 1,000 rows

2nd Dimension – limit to 3,000 rows

Equation for Two Dimension Report

[Max Rows] / [1st Dimension Limit]

3,000,000 / 1,000 = 3,000

Three Dimension Reports 1st Dimension – limit to 1,000

2nd Dimension – limit to 54 rows

3rd Dimension – limit to 54 rows

Equation for Two Dimension Report

[Max Rows] / [1st Dimension Limit]

3,000,000 / 1,000 = 3,000

Square Root of 3,000 = 54

Four Dimension Reports 1st Dimension – limit to 1,000

2nd Dimension – limit to 14 rows

3rd Dimension – limit to 14 rows

4th Dimension – limit to 14 rows

Equation for Three Dimension Report

[Max Rows] / [1st Dimension Limit]

3,000,000 / 1,000 = 3,000

Cube Root of 3,000 = 14

And so on with additional dimensions…

If you want to change the distribution of exported rows across dimensions, use the “limit” parameter.  This parameter allows you to specify the number of rows exported for at each dimensional level.

If the first dimension in the report has low cardinality and the second dimension has high cardinality (such as Device Type > Page Titles), consider using the “limit” parameter with a lower value for the first dimension, allowing for greater distribution on sub-dimensions.

See “Limit” in the “Optional Settings and Parameters” table below.

Optional Settings and Parameters

Type Description Parameter Syntax & Examples
Format Specify the data export format, such as CSV, JSON, or XML. format=json

format=csv

format=xml

format=html

Date Range Specify the range of days to be exported.

  • If time zone is not specified, UTC is used by default
  • If the “begin” and “end” parameters are not specified, the export defaults to current day
  • Both “begin” and “end” parameters must be included when either is used or the export will result in an error
begin=yyyy/mm/dd/hh

end=yyyy/mm/dd/hh

To export data for January 20, 2016:

begin=2016/01/20/00&end=2016/01/20/23

To export data for the 8:00 hour only on January 20, 2016:

begin=2016/01/20/08&end=2016/01/20/08

Relative Date Ranges Specify a date range relative to today.

  • If time zone is not specified, UTC is used by default.
  • The parameter is case-sensitive.
dateRange=currentday

dateRange=currentweek

dateRange=currentmonth

dateRange=currentquarter

dateRange=currentyear

dateRange=previousday

dateRange=previousweek

dateRange=previousmonth

dateRange=previousquarter

dateRange=previousyear

Time Zone Specify the time zone.  Data is stored in UTC in the Infinity platform, and specifying a time zone will offset the data from UTC. timezone=[string]

The following reference provides a list of time zones:

http://joda-time.sourceforge.net/timezones.html

If you are located in Los Angeles, CA, USA (UTC -8:00):

timezone=America/Los_Angeles

If you are located in Paris, France (UTC +1:00):

timezone=Europe/Paris

If you are located in Melbourne, Australia (UTC +10:00):

timezone=Australia/Melbourne

Totals Specify whether to include or exclude measure totals in your export.

  • By default, the parameter is set to false if not specified in the REST URL.
totals=[true|false]

To include measure totals:

totals=true

To exclude measure totals:

totals=false

Limit Specify the number of rows to be exported for each dimensional level.

  • A maximum of 3,000,000 dimensional rows of data can be exported.
  • The first dimension defaults to 1,000 rows if the limit parameter is not specified.

 

limit=[1-3000000],[1-3000000],[1-3000000],...

Comma separated values may be applied for each dimension in your report.

limit=100

100 rows are exported for the 1st dimension.

  • 2D Reports – Sub-dimensions will export 30,000 rows
  • 3D Reports – Sub-dimensions will export 173 rows
  • 4D Reports – Sub-dimensions will export 31 rows

limit=5000

5,000 rows are exported for the 1st dimension.

  • 2D Reports – Sub-dimensions will export 600 rows
  • 3D Reports – Sub-dimensions will export 24 rows
  • 4D Reports – Sub-dimensions will export 8 rows

limit=3000000

3,000,000 rows are exported for the 1st dimension.

  • 2D Reports – Sub-dimensions will export 1 row
  • 3D Reports – Sub-dimensions will export 1 row
  • 4D Reports – Sub-dimensions will export 1 row

Additional examples:

limit=100,100

  • 2D Reports – 1st dimension exports 100 rows, 2nd exports 100 rows
  • 3D Reports – 1st dimension exports 100 rows, 2nd exports 100 rows, 3rd exports 300 rows
  • 4D Reports – 1st dimension exports 100 rows, 2nd exports 100 rows, 3rd and 4th export 17 rows

limit=-,100

  • 2D Reports – 1st dimension exports 30,000 rows, 2nd exports 100 rows
  • 3D Reports – 1st dimension exports 173 rows, 2nd exports 100 rows, 3rd exports 173 rows
  • 4D Reports – 1st dimension exports 31 rows, 2nd exports 100 rows, 3rd and 4th export 31 rows

limit=100,-,100

  • 2D Reports – 1st dimension exports 100 rows, 2nd exports 30,000 rows
  • 3D Reports – 1st dimension exports 100 rows, 2nd exports 300 rows, 3rd exports 100 rows
  • 4D Reports – 1st dimension exports 100 rows, 2nd exports 17 rows, 3rd exports 100 rows, 4th exports 17 rows

limit=100,-,30,100

  • 2D Reports – 1st dimension exports 100 rows, 2nd exports 30,000 rows
  • 3D Reports – 1st dimension exports 100 rows, 2nd exports 1,000 rows, 3rd exports 30 rows
  • 4D Reports – 1st dimension exports 100 rows, 2nd exports 10 rows, 3rd exports 30 rows, 4th exports 100 rows

limit=100,-,-,100

  • 2D Reports – 1st dimension exports 100 rows, 2nd exports 30,000 rows
  • 3D Reports – 1st dimension exports 100 rows, 2nd exports 173 rows, 3rd exports 173 rows
  • 4D Reports – 1st dimension exports 100 rows, 2nd exports 17 rows, 3rd exports 17 rows, 4th exports 100 rows
Download Specify whether exported data is saved to a file and downloaded or displayed directly in the browser.

  • By default, this parameter is set to true if not specified in the REST URL.
  • In Chrome browsers, JSON and XML formats will display in the browser when download is set to false, but CSV format will result in a file download.
  • This optional parameter controls whether or not a header is returned, which instructs the browser to trigger a file download.
  • When using the HTML format or when importing into Excel, set download to false.
download=[true|false]

To save exported data to file and download:

download=true

To display exported data directly in the browser (for JSON and XML when using Chrome):

download=false

Refresh Cancel an existing request that is less than two hours old and refresh the request with the latest data, including any new open sessions.

  • By default, this parameter is set to false if not specified in the REST URL.
  • If the last request is more than two hours old, subsequent requests automatically retrieve the latest data.
  • This option is only needed when you want to refresh the request within two hours of the previous request.
refresh=[true|false]

To refresh the REST request to retrieve the latest data:

refresh=true

If the previous request is more than two hours old, this optional parameter is not needed.

Auto Download Automatically download your export file once it’s completed processing.  When set to true, the request will retry every 30 seconds until a status code is returned indicating processing is complete, and then the export file automatically begins downloading.

  • By default, this parameter is set to false if not specified in the REST URL.
  • The parameter is case-sensitive.
  • For Excel web queries, manually add this parameter and set to true, and the download parameter must be set to false.
    autoDownload=true&download=false
autoDownload=[true|false]

To automatically download your export file when processing is complete:

autoDownload=true

Suppress Error Codes Suppress error codes when importing into Excel.  This is needed for Excel imports, because requests that return any HTTP status code other than 200 causes Excel to disable the connection.  Adding this parameter ensures that all errors return a 200 status code to Excel, but the message text associated with the error still appears.

  • By default, this parameter is set to false if not specified in the REST URL.
suppressErrorCodes=[true|false]

To import data into Excel:

suppressErrorCodes=true

 

Authorization

When a request is made to export data using a REST URL, you will be prompted to enter your username and password.  It is also possible to pass encrypted authorization details as part of the header, depending on the application used to execute the request.

If your user account does not have access to the account or the report, a “404, not found” error results.  Additionally, any data permissions configured in Account Settings for the user account will be applied to the API export.

 

Exported Report Values May Differ From the Reporting User Interface

Exports of reports for “current” or “today” date ranges will include any open sessions available at the time when the export request is initiated.  Open sessions are continuously streaming into the Infinity platform and these open sessions are visible both in the reporting user interface and report exports.  Because of this, it is possible for export files to have slight differences when compared to the reporting user interface.

Let’s look at the following example:

  1. At 8:05, I view a report for “This Week”.  The reporting UI shows a session value of 12,814.
  2. At 8:05, I click to initiate an export of this report including all open sessions up to 8:05.
  3. At 8:08, the export file has completed processing and is ready to be retrieved.  I click to open the file and see a session value of 12,814.  This value matches sessions from 8:05 when the export was initially requested.
  4. At 8:10, I compare the export file’s session value of 12,814 to the reporting UI, which now shows 13,007 sessions.  The difference is the result of new sessions streaming into the Infinity platform.

For two hours after the initial export request, any requests of the same report by the same user will result in the same export file, unless the “refresh” parameter is set to true.  Using the example above, export requests at 9:00 and 10:00 will return the same export file as the 8:05 request.  Export files are available for users to retrieve for up to two hours.  After two hours, subsequent export requests will reprocess and retrieve the most up to date data.  This two hour buffer is in place to allow sufficient time for longer running queries to complete and for you to retrieve the export file.

If “refresh=true” is added to the REST request, any previous request made less than two hours ago will be canceled and a new request will be initiated to retrieve the most up to date data including any new open sessions since the previous request.  See “Refresh” in the table.

 

Parameterizing Values in Excel Imports

Parameterized requests allow you to link a parameterized cell in Excel to a parameter in the API request.

  1. In the Infinity Analytics reporting user interface, select to export your report and select “Copy to clipboard”.
  2. Open Excel and select the “From Web” option.
  3. Paste the REST URL you copied to clipboard into the “Address” field, and modify the URL to include the following:
    • Add suppresserrorcodes=true.
    • Change the format parameter to html.
    • Either change the download parameter to false or add download=false.
    • Add autoDownload=true.
  4. Next to the “Address” field, click “Save Query”.
  5. In the “Save Query” window, enter a path where you want to save the IQY file and click “Save”.
  6. Open the IQY file and modify the REST URL to include the parameterized values you want.  For example:
    https://api.webtrends.io/v1/account/mv0nfs6p/dataexport/dtov3huh/data?begin["begin"]&format=html&suppresserrorcodes=true&download=false&autoDownload=true
  7. In Excel, choose “Connections” and “Add”, and choose your modified IQY file.
  8. In Excel, choose “Existing Connections” and point to the connection you just created.
  9. You are then prompted to enter a value for the parameters you added to the IQY file (for example, enter a “begin” value) and your report data populates Excel.