It is common for people to try to tie out the API data to the UI. In many cases, the numbers will vary because the UI has its own internal modeling in presenting the numbers. Also, the UI and the API use different timezones. Variations in timezones (PST vs UTC), how the data is presented in the interface, and how data is linked within Amazon's internal systems can shift how information is reported.

Using Reporting Exports, Not the UI, For Testing

One of the things that are helpful is to attempt to cross-check the numbers in the Amazon UI with the Amazon supplied reporting exports. The reports are a close (not exact!) reflection of the outputs from the API. This makes reports a good candidate for testing.

Creating Reports

As a test, remove Openbridge from the process for now. To do so, run a Reporting data export for Sponsored Products in the Amazon Advertising Reporting UI.

First, in Seller Central go to Advertising Reports:

In the Amazon Advertising interface, Reports are accessed here:

NOTE: If you do not see"Reports" or can not create a report, you likely have permissions issues that need to be resolved before proceeding. Without the correct permissions, it will impact your ability to test and our ability to collect data on your behalf!

Configure and run a report export. Run Sponsored Products reports for campaigns, with daily intervals, for the last 30 days.:

Now, open the report export, cross-check against the reporting interface charts.

Do the numbers in the UI align with numbers in the export? For example, does the impressions count in the export for a given date match the UI? In many cases, the numbers will vary. Why does Amazon's own reporting export not match the UI?

There are a number of reasons, but the primary reason is the display logic for the UI is defined by Amazon. They make choices on the logic behind what to display and how to display it. For example, the UI may not show data for any campaigns that are disabled, but this data IS present in the exports. If you are running a total for all campaigns in the export will allow you to calculate a value regardless of status while the UI only shows where campaign status = enabled. This is no different than someone using Tableau or Power BI making choices on how to present metrics and dimensions.

While we understand the rationale to use the Amazon user interface as a cross-check, it will generally not be an accurate test.

Understanding timing and timezones

The API syncs in accordance with Amazon standards. This means that data from a report might be slightly different based on the date and time of an API call. For example, the API call happened at 6 AM and you are looking at something in the UI or reporting export at 5 PM. The numbers may be different because Amazon's internal systems integrate differently than those external to Amazon. It does not mean the numbers are wrong, they just reflect a different point in time.

Attribution updates change prior reporting periods

Amazon is constantly restating attribution values, which is why we approach our sync process the way we do, with a rolling 60-day attribution window. For more details on this process, see:

De-Duplication and dealing with attribution updates over a long time horizon

In some cases, there may be duplicate entries for a given date. Why does this occur? Schema changes at Amazon and attribution updates for a record.

While we typically will attempt to de-duplicate any possible duplicate rows, there are cases where we may not.

For example, data was supplied on 4/19 for a Sponsored Brand campaign. On May 1, Amazon updated the schema, which triggered an update in our system. Amazon then had an update for the Sponsored Brand campaign on 5/19. As a result, the new 5/19 row for the Sponsored Brand campaign came in after an Amazon schema update. This means the latest Sponsored Brand campaign data is resident in the new schema v11, whereas the old 4/19 record is still resident in v10.

We would suggest using the most recent record for a given ob_date in these cases and others like it. Here is an example query:

PARTITION BY ob_transaction_id
ORDER BY ob_processed_at ASC
) AS row
WHERE campaign_id = 1234567890 and row = 1
ORDER BY ob_date ASC

If you are using Tableau, Power BI, Google Data Studio... there are built-in operators for using the MAX dates, which would allow you to count the most recent record for a date.

Lastly, if you are not comfortable doing this in Data Studio, another option is creating a custom view. The custom view can be specifically tailored to the analysis. A view looks like a table but runs custom SQL behind the scenes.

Example queries to check the data in your destination against the reporting exports

Let's take a look at a Sponsored Display campaign called "Foo For You."

Getting the reporting extract says there is a .35 cost and 1 click. You can create a test that acts as a cross-check:

SELECT * FROM `mydata.openbridge.amzadvertising_sd_campaigns_master` where profile_id = 123456789 and campaign_id = 9999999999
and ob_date between '2021-02-06' and '2021-02-28'and campaign_status = "ENABLED"

Result? .35 cost and 1 click. The data and report match.

How about the Sponsored Display campaign "Never Foo for Boo"? The reporting export says there is a cost of $173.30 between February 6 and February 28.

SELECT * FROM `mydata.openbridge.amzadvertising_sd_campaigns_master` where profile_id = 123456789 and campaign_id = 0987654321
and ob_date between '2021-02-06' and '2021-02-28'and campaign_status = "ENABLED"

BigQuery cost for this campaign? $173.30. This matches the report export. As previously suggested, you should closely review the queries you are making to align with the data.

Is data missing? I can't seem to locate specific date ranges

If you activated an Amazon Advertising Profile on 4/15, the cadence defines the data dates. For example, for a 4/15 activation date, the first sync date is 4/16.

For example, let's assume we want to check for a profile ID (e.g., a number like 12345678902345), there is data for 3/16, 3/17, 3/18, 3/19, and so forth going back 60 days. This 100% aligns with the rolling attribution window processing in the documentation. We only go back 60 days per the API mechanics documented in that link. As such, nothing is missing.

Also, while it may seem obvious, if a campaign was recently created, there will only be data present for the period it was active. The same is true for your campaign status as something may be deactivated. You can check the status in your various data feeds.

Lastly, if data is missing it might be due to account-related issues such as funding/balance issues. Once your advertising account is paused, there will not be any data for that time period, even if you resolve your funding issues.

Defining your reporting tests

Constructing minor, testable propositions will help your troubleshooting efforts to validate this is a data issue or a query issue.

NOTE: Please note that query design, testing, and data modeling are beyond the scope of the Openbridge service. While we attempt to illustrate a few queries to show how you can build tests, it is not something that falls under the support agreement. If you are looking for help in building out your testing models, please contact us for a paid consulting service engagement where we would undertake a collaborative design, development, and testing models with your team. โ€‹

Did this answer your question?