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 time zones. 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. For more on the variations between the UI and API, see Amazon Advertising Console vs Advertising API.
Using Reporting Exports, Not the UI, For Testing
One of the helpful things 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. Run a Reporting data export for Sponsored Products in the Amazon Advertising Reporting UI to do so.
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 must 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 and cross-check against the reporting interface charts.
Do the numbers in the UI align with the numbers in the export? For example, do the impressions count in the export for a given date that matches the UI? In many cases, the numbers will vary. Why does Amazon's own reporting export not match the UI?
There are several reasons, but the primary reason is the display logic for the UI is defined by Amazon. They choose the logic behind what to display and how to display it. For example, the UI may not show data for any disabled campaigns, but this data IS present in the exports. If you are running a total for all campaigns in the export, it 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 presenting 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 following 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 differ 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 constantly restates attribution values, so we approach our sync process the way we do, with a 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 suggest using the most recent record for a given ob_date in these cases and others like it. Here is an example query:
SELECT * FROM (
SELECT *, ROW_NUMBER () OVER
(
PARTITION BY ob_transaction_id
ORDER BY ob_processed_at ASC
) AS row
FROM `mydata.amazon.amzadvertising_hsa_campaigns_master`
)
WHERE campaign_id = 1234567890 and row = 1
ORDER BY ob_date ASC
If you are using Tableau, Power BI, or Google Data Studio... there are built-in operators for using the MAX dates, allowing you to count the most recent record for a date.
Lastly, creating a custom view is another option if you are uncomfortable doing this in Data Studio. 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
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
The cadence defines the data dates if you activated an Amazon Advertising Profile on 4/15. For example, for a 4/15 activation date, the first sync date is 4/16.
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 active period. 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 whether this is a data or 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 does not fall under the support agreement. If you need help building your testing models, please contact us for a paid consulting service engagement where we will undertake collaborative design, development, and testing models with your team.