Beeswax

Reporting

The Buzz UI and API include Report Builder, a full-featured reporting engine for aggregated data about campaign performance. Reports are requested asynchronously and may be saved and scheduled using the API.

📘

Migration Notes from 0.5 API

  • The Report Builder 2.0 API is entirely different from the 0.5 report_queue API. The new API is more powerful and flexible than the previous version and should require fewer requests and overhead.
  • Please check with your account manager to assure you have access to Report Builder through the 2.0 API as it may need to be enabled.

Getting a list of available reports and fields

To retrieve a list of available reports make a GET request to the /reporting/reports resource.

This will return a list of Reports by name, ID and view -- the view is important as it is used as an input into other queries. The list of reports will only include those permissioned to the user.

To retrieve a list of accessible fields in a Report, make a GET request to the /reporting/reports/{report_name} resource with the{report_name} replaced by the view. For example, Performance Report fields should be fetched at /reporting/reports/performance_agg.

The name field in the returned response should be used to specify the fields in a given report in subsequent requests.

Running ad hoc queries

To run queries, POST requests should be made to the /reporting/run-query endpoint. The general interface of the POST request is shown below, along with descriptions of the fields.

{
    "view": "performance_agg",
  "pivots": ["environment_type"],
    "fields": ["campaign_id","environment_type","impression"],
    "limit": 100,
    "filters": {
        "bid_day": "after 2019-01-01"
    },
    "result_format": "csv",
    "query_timezone": "America/New_York",
    "sorts": ["impression DESC", "campaign_id ASC"]
}

Field

Description

view (Required)

Specifies the report to be queried.

pivots

Pivots are an array of fields that can be optionally set. When set, pivots will distribute metrics horizontally with a column for the pivoting field, similar to a pivot table in a spreadsheet.

For a field to be pivoted, it must be added to both the pivots array and the fields array specified below.

fields (Required)

Fields can be either dimensions (qualitative, descriptive data used to group rows in reports -- for example, “Campaign Name” or “Environment Type”) or measures (quantitative metrics that are the actual numbers in a report -- for example, “Impressions” or “Spend”).

Fields expects an array of strings that will be all the requested dimensions or measures in the report. Unlike the 0.5 API, you can put both “fields” and “metrics” in the same property of the API request.

limit

This specifies the row limit in the report, the maximum accepted value is 30,000.

filters

Accepts an object containing key/value pairs to filter, where the key specifies the field or metric and the value specifies the filtering condition. Multiple filters can be added by adding new key/value pairs to the object.

NOTE: Filtering does not support the general API filtering described in GET-ting Data from the API.

If a bid_day filter is not specified a default filter set to “last 7 days” will be applied.

result_format

Specifies the file format of the response. Acceptable formats are:

  • json (default)
  • csv
  • xlsx
  • txt (tab-delimited)
  • png
  • md
  • html

query_timezone

The timezone to run the query in, if different than the Account’s timezone. If unspecified the Account setting is used. The format should use tz syntax such as Asia/Kuala_Lumpur.

sorts

An array of strings specifying the sort conditions of the report. The proper syntax for each string is field_name ASC/DESC.

Upon successful post to the async query endpoint, the user will receive a response containing their task_id for future retrieval.

{
    "task_id": "d865ec8efa6c4a22dc07bc0ff79684d6",
    "message": "We are are processing your report. Make a GET request to http://canary.api.beeswax.com/rest/v2/reporting/async-query/d865ec8efa6c4a22dc07bc0ff79684d6 to check the report's status and retrieve the results of your query."
}

Fetching a Report's Results

By making a GET request to the URL provided in the response of the POST request you can check the status and fetch the results of the query. The resource used is /reporting/async-results/{id}.

If the query is still processing the response code will be a 204. A completed query will return a 200 with the response containing the requested data.

Report results

For JSON reports, the results will be returned as an array of JSON objects where each object represents a row:

[{
        "Line Item ID": 1892,
        "Impressions": 75450
    },
    {
        "Line Item ID": 2363,
        "Impressions": 49859
    },
    {
        "Line Item ID": 2364,
        "Impressions": 49733
    }
]

Pivoted results will return will return the pivoted dimension nested in the JSON object for each "row" of data:

[
    {
        "Hour": "2020-06-30 23:00:00",
        "Line Item ID": 2724,
        "Impressions": {
            "Environment Type": {
                "APP": null,
                "WEB": 1835
            }
        }
    },
    {
        "Hour": "2020-06-30 22:00:00",
        "Line Item ID": 2724,
        "Impressions": {
            "Environment Type": {
                "APP": null,
                "WEB": 2231
            }
        }
    },
    {
        "Hour": "2020-06-30 22:00:00",
        "Line Item ID": 2731,
        "Impressions": {
            "Environment Type": {
                "APP": 1,
                "WEB": 1069
            }
        }
    }
]

Other reports will return the results in the requested file format.

Folders

In Report Builder users have two sets of “folders” they can save their reports. The first is their personal folder and the second is a folder shared with all users in the same Account. The IDs for these folders are used when saving reports and may be retrieved with a GET request to the /reporting/folders resource. Sample response:

{
    "count": 2,
    "next": null,
    "previous": null,
    "results": [{
            "id": "72",
            "name": "canary-17",
            "is_personal": false
        },
        {
            "id": "130",
            "name": "Harry Houdini",
            "is_personal": true
        }
    ]
}

Saving reports

📘

Migration Notes from 0.5 API

  • Reports saved in the 0.5 API are not automatically migrated to the 2.0 API, they must be recreated.

Creating a new saved report entails making a POST request to the /reporting/saved-reports resource. The request structure is similar to the ad hoc query described above, with a few additional available fields. Example shown below:

{
    "title": "My saved API example!",
    "description": "I'm a helpful description",
    "query": {
        "view": "geo_agg",
        "fields": [
            "geo_zip", "bid_hour", "inventory_source", "impression", "line_item_id"
        ],
        "limit": 100,
        "filters": {
            "bid_day": "after 2020-06-01"
        },
        "sorts": ["bid_hour ASC"],
        "query_timezone": "Pacific/Auckland"
    },
    "folder_id": 72
}

Field

Description

title (Required)

A title for the Saved Report.

description

Optional description.

query (Required)

This specifies the query using the same syntax as the ad hoc query. Saved reports have an enforced limit of 5,000 rows. At run-time this limit can be overwritten.

folder_id

This specifies the folder in which to save the saved report. If not specified, the report will be saved in the user’s personal folder.

A GET request can also be made to this endpoint to fetch a list of existing saved reports.

Running a saved report

To run an existing saved report, a POST request should be made to reporting/run-saved-report. A saved_report_id must be passed to specify which saved report to run. Two optional parameters that can be passed in this request, result_format and limit.

{
  "saved_report_id": 121
    "result_format": "csv",
    "limit": 10000
}

Field

Description

saved_report_id

The ID of Saved Report. This will be returned when a saved report is created or can be found by making a GET request to the /saved-reports endpoint.

result_format

Optionally specify a result format to return the results. Saved Reports cannot be saved with a result format associated. Instead, the format is specified when the report is run.

limit

Users can set a limit when running the saved report. This limit will override the row limit set on the saved report, and can exceed the 5,000 row limit set there to a maximum of 30,000 rows.

Once a saved report is run, its results are fetched in the same way as an ad hoc report: a task_id will be returned that can be used to fetch results from the /reporting/async-results/{id} endpoint.

Scheduling reports

You can schedule reports to run automatically and output in various ways. New reports are created by making a POST request to the reporting/report-schedules resource. The body of this endpoint looks like the following (example includes both an S3 and email destination though in a normal scenario both cannot be scheduled in the same schedule object):

{
    "crontab": "0 15 * * *",
    "saved_report_id": 120,
    "name": "My Example Schedule",
    "scheduled_plan_destination": [{
            "format": "csv",
            "address": "[email protected]",
            "type": "email",
            "message": "Hello, world"
        },
        {
            "format": "json",
            "address": "s3://beeswax-tmp/",
            "type": "s3",
            "parameters": {
                "access_key_id": "access_key_here",
                "region": "us-east-1"
            },
            "secret_parameters": {
                "secret_access_key": "secret_key_here"
            }
        }
    ],
    "send_all_results": false,
    "timezone": "America/New_York"
}

Field

Description

crontab (Required)

The crontab is the schedule for the report specified in Vixie Cron syntax. If you are unfamiliar with cron schedules you can play around with cron schedules here.

The first value in the cron schedule must be a single integer and cannot be a wildcard, step value or range of values. This ensures all schedules run at most once an hour.

saved_report_id (Required)

The Saved Report you wish to schedule -- all schedules must use a previously existing saved report.

name (Required)

A name for the schedule. This is used to define file names and subject lines for email-based delivery.

scheduled_plan_destination

This object array holds the necessary parameters for the location to send the scheduled report. To send to multiple email addresses create an object for each email address. When sending to S3, only one destination may be specified.

When setting a format in a scheduled report it is worth noting that the format parameters in the scheduled_plan_destination object are slightly different. For example, to receive a png of the visualization you will need to specify “inline_visualizations” instead.

Emailed Reports
Requires the email address and file format. Specify the type as email. An optional message property can be set that will be added to the body of the email.

S3 Reports
Will require the S3 bucket location and file format. Specify the type as s3. Additionally, to ensure proper permissions in S3 are enforced, the user must specify an Access Key and Secret Key of an IAM resource that has the ability to write to the S3 location specified. For security reasons, these keys will not be exposed in plaintext when fetching the report schedule information.

send_all_results

Set to true or false. If false, the report will send the specified row limit set on the saved report. If true, the schedule will send all the results specified by the query. This is the proper way to run a large report that would’ve previously required pagination.

timezone

Timezone to run the report in -- this timezone also applies to the cron schedule of the report, so a schedule of * 6 * * * would run at 6AM EST if America/New_York is set as the timezone

A GET request can also be made to this endpoint to fetch a list of existing schedules.

Running a scheduled report once

There are use cases where running a scheduled report one time is desirable. There are two supported workflows for this:

First, an existing scheduled report can be run a single time by making a POST request to /reporting/report-schedules/{id}/run-once. This can be useful for testing an existing schedule and making sure credentials and delivery parameters are setup correctly without needing to wait for the next scheduled run.

Second, a report that has no existing schedule can be run a single time by making a POST request to /reporting/report-schedules/run-once with a schedule_plan_destination and name fields in the body of the request. A crontab is not required. This can be useful for sending a one-time report to a destination such as S3 or email but does not need to be run on a recurring schedule.

Updated about 4 hours ago


Reporting


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.