Intra day reporting solution
- Overview
- Solution overview
- Onboarding Instructions
- File processing considerations
- S3 Order Data Feed Schema
- De-Duplication & Upsert Logic
Overview
The intra-day reporting system processes orders data 4 times per hour, refreshing data on a rolling basis for orders in the preceding hour.
Each day, Amazon generates 96 individual files containing order data, which includes both same-day orders or from previous days that experienced delayed processing. This continuous, around-the-clock publishing cycle ensures merchants have access to up-to-date order data.
This solution leverages advanced AWS technologies to ensure data security and streamline data distribution. Specifically:
- Data encryption is handled securely using AWS Key Management Service (KMS).
- Merchants receive instant notifications about newly published files through Amazon Simple Notification Service (SNS) topics.
- For data consumption, merchants can choose between two methods: a. Directly accessing and copying data from the JWO S3 bucket using the AWS Command Line Interface (CLI). b. Implementing an event-driven approach that automatically copies data from the JWO S3 bucket, utilizing Amazon Simple Queue Service (SQS) triggers in conjunction with AWS Lambda functions.
Solution overview
1- Amazon SNS triggers a notification event to the customer of a new file available
2- When the message is received in the SQS queue a lambda function will be triggered to process the incoming message
{
"Type" : "Notification",
"MessageId" : "34920377-24ac-5f27-8172-5dadc2dd506e",
"TopicArn" : "<Source SNS Topic Arn>",
"Subject" : "Amazon S3 Notification",
"Message" : "{\"Records\":[{\"eventVersion\":\"2.1\",\"eventSource\":\"aws:s3\",\"awsRegion\":\"us-east-1\",\"eventTime\":\"2024-06-05T22:18:24.822Z\",\"eventName\":
\"ObjectCreated:CompleteMultipartUpload\",\"userIdentity\":{\"principalId\":\"AWS:AROA5G7Q7N2GZSXO3I4Q6:RedshiftIamRoleSession\"},\"requestParameters\":
{\"sourceIPAddress\":\"54.81.145.214\"},\"responseElements\":{\"x-amz-request-id\":\"F7P4FE2Q4QXCW3JH\",\"x-amz-id-2\":\"aFW9gjd1vmA5sb1UrGJa1F6ATsRO2HLzjya/
UjdjtXnTdlP6exi83YMQPZ2kLXF3296kr3sUgNSpeERH1L/CcayWUIOeGMVT\"},\"s3\":{\"s3SchemaVersion\":\"1.0\",\"configurationId\":\"<S3ReportNotifsConfigID>\",
\"bucket\":{\"name\":\"<S3BucketNameProvided>\",\"ownerIdentity\":{\"principalId\":\"AO07TJS0N6VFV\"},\"arn\":\"arn:aws:s3:::<S3BucketNameProvided>\"},\"object\":
{\"key\":\"Order/<ReportGenDate YYYY-MM-DD>/<ReportGenHour H24>/<ReportFileName>\",\"size\":166,\"eTag\":\"40dabe44630f69d6693151ea9d1aae44-1\",
\"versionId\":\"QywepIoqoFXvLzxs1vvAObS306zaU8e7\",\"sequencer\":\"006660E4307DDF229D\"}}}]}",
"Timestamp" : "2024-06-05T22:18:25.734Z",
"SignatureVersion" : "1",
"Signature" : "<SIGNATURE_VALUE>",
"SigningCertURL" : "<SIGNATURE_VALUE>",
"UnsubscribeURL" : "<UNSUSBSCRIBE_URL_VALUE>"
}
<Source SNS Topic Arn> = SNS topic that publishes the data. This is 1:1 mapped to a merchantID onboarded.
<S3ReportNotifsConfigID> = Event Generation name/ID maintained at the Amazon JWO end. This can be ignored by merchant.
<S3BucketNameProvided> = S3 that hosts the report files. This is 1:1 mapped to a merchantID onboarded.
<ReportGenDate YYYY-MM-DD> = This is the date to which report files corresponds to. Format of the date 'YYYY-MM-DD'
<ReportGenHour H24> = This is the hour to which report files corresponds to. This is in 24 hour format.
<ReportFileName> = Name of the reporting file. Generally it would be ALL<NN>.csv000, when <NN> represents the minute of the hour the file was generated
3- The lambda function will access the source file in the Amazon S3 bucket
4- The lambda function will copy the file from the Amazon S3 bucket and store it in the S3 in the customer bucket
Onboarding Instructions
Please see below instructions to onboard with Amazon JWO intra-day reporting. Note: The samples policies and AWS resource configuration below are for illustration purpose only and we recommend you follow your organization best practices for creating and securing AWS resources.
Step #1: Merchant resource creation
AWS Account Creation Please create an AWS account if the merchant doesn’t have one already. Example AWS Account ID : 012345678910 can be created using https://aws.amazon.com/resources/create-account/
Create IAM role Please create an AWS IAM role and share it with Amazon JWO team. This role will be used to set up the access with the JWO AWS account. Follow the link here to create an IAM Role.
IAM role Format: {Merchant_Name}_AMZN_JWOS_Role The generated Role ARN for the above IAM role would be as follows:
arn:aws:iam::012345678910:role/{MERCHANT_NAME}_AMZN_JWOS_Role
where {MERCHANT_NAME} is a placeholder to be replaced with the merchant’s name. As an example, Merchant Coffee Company will be setup as
arn:aws:iam::012345678910:role/COFFEE_COMPANY_AMZN_JWOS_Role
Step #2: Amazon merchant onboarding
In this step, Amazon JWO team will onboard the merchant using the AWS account id and the IAM role provided in steps 1 above. Once completed, Amazon JWO team will provide the following information to the merchant to complete next steps on the merchant’s end.
SNS topic ARN for the merchant to subscribe to. Make sure that you provide the SendMessage permissions on the SQS queue to the SNS topic provided.
KMS Key inline policy which is to be attached to merchant’s IAM role created in next step
A sample KMS inline policy will look like as below
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowUseOfKeyInAmazon3POrderAccount",
"Effect": "Allow",
"Action": [
"kms:Encrypt",
"kms:Decrypt",
"kms:GenerateDataKey*"
],
"Resource": [
"merchant-specific-psbi-kms-arn_1",
"merchant-specific-psbi-kms-arn_2"
]
}
]
}
S3 bucket(s) details where the files are published. Merchants’ IAM role should also have permissions to get files /objects from the S3.Sample IAM policy for S3 permissions.
{
"Version": "2012-10-17",
"Statement": [
{
"Action": [
"s3:GetObject",
"s3:GetObjectVersion",
"s3:ListBucket",
"s3:GetObjectTagging"
],
"Resource": [
"arn:aws:s3:::<AMAZON_S3-BUCKET-NAME_1>",
"arn:aws:s3:::<AMAZON_S3-BUCKET-NAME_1>/*",
"arn:aws:s3:::<AMAZON_S3-BUCKET-NAME_2>",
"arn:aws:s3:::<AMAZON_S3-BUCKET-NAME_2>/*"
],
"Effect": "Allow",
"Sid": "AllowJwoRoleToAccessJWObucket"
}
]
}
Step #3: Merchant JWO setup
Using information provided by the JWO team. Merchant will attach the above KMS key policy and S3 policy to access the S3 service to the IAM role created
Create SQS Queue For steps on how to create an SQS queue, see AWS documentation here
Set SQS policy A sample SQS Access policy that needs to be present to allow our SNS topic to send messages to your queue
{
"Sid": "AllowSNSTopictoSendSQSMessage",
"Effect": "Allow",
"Principal": {
"AWS": "*"
},
"Action": "SQS:SendMessage",
"Resource": "{YOUR_SQS_ARN}",
"Condition": {
"ArnLike": {
"aws:SourceArn": [
"SNS_TOPIC_PROVIDED_BY_AMAZON_1",
"SNS_TOPIC_PROVIDED_BY_AMAZON_2"
]
}
}
}
Create target S3 bucket Create an S3 bucket to act as a destination for the file received from Amazon. For steps on how to create an S3 bucket see AWS steps here
Create IAM policy for the destination bucket Below is an example of IAM policy
{
"Version": "2012-10-17",
"Statement": [
{
"Action": [
"s3:DeleteObject",
"s3:GetObject",
"s3:GetObjectVersion",
"s3:PutObject",
"s3:PutObjectAcl",
"s3:ListBucket",
"s3:GetObjectTagging"
],
"Resource": [
"arn:aws:s3:::<S3-BUCKET-NAME>",
"arn:aws:s3:::<S3-BUCKET-NAME>/*"
],
"Effect": "Allow",
"Sid": "AllowJWOroleToAccessTargetbucket"
}
]
}
Create lambda function to process new report notification Create an AWS lambda function to process the incoming SQS message and copy the Amazon report file. For steps to create a lambda function please see AWS docs here
Set SQS to lambda integration To setup SQS to lambda function integration, please follow AWS documentation steps here
Work with Amazon to get and accept the SNS to SQS integration To complete the end-to-end integration you will need to accept a subscription request from the Amazon SNS to the your SQS. You can follow step 2 in AWS documentation here
Code snippets/resources: For sample implementation and code samples please reach out to your Amazon team contact
File processing considerations
De-duplication: When processing data files, it's crucial to implement a robust mechanism to detect duplicate file data, as you may occasionally receive and process the same file multiple times. To prevent this, your application should incorporate duplicate file detection to prevent data redundancy and ensure accurate analytics.
Data flow patterns: When processing order data that is generated every 15 minutes, it's essential to implement robust logic in your application to handle situations where a single order's information may be split across multiple files. Your processing system should incorporate an upsert (update/insert) mechanism that can intelligently manage these scenarios.
When receiving a new file, the application should first check if the order already exists in your database. If it does, the system should update the existing record with any new or modified information from the current file. If the order doesn't exist, it should create a new record. This approach ensures data consistency and prevents duplicate entries while maintaining the most current order information.
Efficient file management: To optimize file management and resource utilization, the solution implements an efficient mechanism for handling new report files. Instead of relying on scheduled scans of the Amazon S3 bucket, which can be resource-intensive and potentially slow, the system leverages SNS (Simple Notification Service) messages. When a new report file is available, an SNS message is generated containing the file's metadata. The solution extracts the filename from this message and uses it to directly download the specific report file to the local environment.
S3 Order Data Feed Schema
The S3 order data feed supports three report types, each a superset of the previous. The report type assigned to a merchant determines which fields are included in their feed files.
Type 1 — Minimal Data Transfer
Type 1 provides core transactional fields sufficient for basic sales reporting.
| Field | Description |
|---|---|
| store_id | Store ID — the external store identifier for the location where the transaction occurred |
| order_id | A 17-digit identifier used to distinguish each unique order. Each customer checkout results in a unique order (e.g. 113-1234123-1231234) |
| timezone | Timezone of the store location (e.g. America/Chicago) |
| transaction_datetime | Timestamp indicating when the order was processed by Amazon and the receipt was generated. This is the store-local timestamp |
| product_title | Product name |
| sku | Product SKU |
| currency | Currency in which the purchase was made (e.g. USD) |
| price | Price of the product for one unit, in store-local currency |
| quantity | Units purchased |
| total_price | Total price of units purchased (price × quantity). Does not include taxes. In store-local currency |
| type_of_transaction | Transaction type (e.g. order, cart) |
| estimated_sales_tax | Total tax charged for the units of this product purchased, in store-local currency |
Type 2 — Promotion Data
Type 2 includes all Type 1 fields plus promotion-related fields.
| Field | Description |
|---|---|
| All Type 1 fields | See above |
| promotion_id | Unique identifier for the promotion applied to the line item |
| promotion_name | Name/description of the promotion |
| promotion_amount | Promotion discount amount. This value is added to total_price to derive net_price (typically a negative value, e.g. -1.50) |
| net_price | Derived: total_price + promotion_amount |
| net_tax | Total tax inclusive of promotional tax adjustments. Derived: estimated_sales_tax + promotion_amount_tax |
| gross_price | Derived: total_price + net_tax + promotion_amount |
Type 3 — Extended Data Set
Type 3 includes all Type 1 + Type 2 fields plus session and product-category fields.
| Field | Description |
|---|---|
| All Type 1 + Type 2 fields | See above |
| transaction_id | Customer order item ID — a unique identifier for each line item within an order |
| session_id | Unique identifier for the shopping session |
| product_category | Product category classification |
| product_subcategory | Product subcategory classification |
| entry_exit_method | Entry/exit method used by the customer (e.g. CREDIT_CARD) |
| trip_duration_mins | Session duration in minutes (difference between session start and end time) |
| group_size | Number of persons / customer exit count for the session |
| purchase_datetime | Session end time in store-local timezone — represents when the customer completed their shopping trip |
De-Duplication & Upsert Logic
The S3 feed is an append-only change feed — each 15-minute file is immutable and never rewritten. Within a
single 15-minute window, identical rows are de-duplicated, but genuine changes to an order (e.g. delayed item additions,
refund adjustments) are intentionally re-emitted across subsequent files or days. This means the same order_id may appear
in multiple files with updated information.
Stable De-Duplication Key
When ingesting S3 feed data, merchants must implement upsert logic using one of the following approaches:
Option 1 — Composite Stable Key (Recommended)
Use the composite key of:
transaction_id + order_id + session_id
This three-part key uniquely identifies each line item within an order and session. When performing upserts, always keep the latest row for a given key combination. This is the stable identity across all file windows and should be the primary de-duplication strategy.
Option 2 — Order ID
For merchants on Type 1 or Type 2 feeds (which do not include transaction_id or session_id), de-duplication can be
performed using order_id alone. However, note that a single order_id may contain multiple line items, so this approach
works best when combined with additional fields such as sku and quantity to distinguish rows within the same order.
Important: Some merchants generate their own ingestion-side key that auto-increments per row. Because this key is merchant-generated, two ingestion runs of the same source data will produce different key values. Do not rely on merchant-side auto-increment keys for de-duplication — always use the stable composite key (
transaction_id + order_id + session_id) ororder_id-based approach described above.
Recommended Ingestion Pattern
- Receive the 15-minute file via SNS notification (or scheduled S3 scan).
- Parse each row and extract the de-duplication key.
- Upsert into your data store — if the key already exists, update the record with the latest values; if not, insert a new record.
- Retain the latest — when the same key appears across multiple files, the most recent file contains the authoritative state of that order line item.
This approach ensures your local data store converges to the same state as the NRT dashboard while preserving the full audit trail of the append-only feed.
