Data Model

Supported Data Models

Per-entity Data Model

When jobs are created using the connector’s wizard, a default per-entity data model is used. The default model creates one schema for each top-level entity contained in the account. In the case of the Amazon Advertising API, these top-level entities are customer profiles. This section describes the per-entity data model in detail.

The connector stores common information (e.g., profiles) in the amazon_advertising schema. Currently, profiles are the only common information kept by the connector.

Each profile in the account will have its own schema which will contain tables with data for the profile. The name of each profile’s schema begins with amazon_advertising, followed by the customer name associated with the profile, followed by the profile_id associated with the profile. For example:

amazon_advertising_acme_tool_837662895682018

The tables contained in a profile’s schema can vary, depending upon the jobs that are run for the profile. Generally, each job creates one table. Table names are uniform across schemas. For example, if a Sponsored Products: Targets - query job is run for each profile in the account, each profile’s schema will contain a table named report__products__targets__query.

Whenever a new profile is created in the Amazon Advertising account, the connector’s wizard must be run to create the jobs for that entity.

Unified Data Model

The connector also supports a unified data model. Unlike the default per-entity model, there is a single schema named amazon_advertising which contains all data for all profiles. Table names contained by the schema are identical to those used in the per-entity model.

To use the unified data model, jobs must be created manually. The primary advantage of using the unified model is that jobs will automatically be run for new profiles as they added to the Amazon Advertising account – it is not necessary to manually create new jobs for them. Disadvantages of the unified model are that: a) the same jobs are run for all profiles, and b) the co-mingling of data from multiple customers in a single schema.

Schema Design

Using the per-entity model, the plugin stores reports and snapshots in a Postgres schema associated with their profile id. The format used for Postgres schema names is:

amazon_advertising_{profile_name}_{profile_id}

profile_name is replaced by the name associated with the profile and profile_id is the profile’s id. The prefix of amazon_advertising can be overridden when creating jobs with the wizard.

A schema for the fictional Max Technology with a profile id of 123456788901 would be:

amazon_advertising_max_technology_123456788901

Table Naming

The remainder of this section describes the table names used to store reports and snapshots within a Postgres schema.