Windsor brings all your Rakuten Advertising data into Power BI, Looker Studio, Google Sheets, Excel, BigQuery, Snowflake, Tableau, PostgreSQL, MySQL, Python, Amazon S3, Azure, Azure MS SQL
Rakuten Advertising Field Reference
Sample
The Rakuten Advertising connector retrieves Rakuten Advertising data from the following report types:
- Gross Sales and Commissions Report
- Individual Item Report
- Link Type Report
- Media Optimization Report
- Multi-Touch Commissioning Report
- Non-Commissionable Sales Report
- Product Success Report
- Sales and Activity Report
- Signature Activity Report
- Signature Orders Report
A sample URL on how to query data:
https://connectors.windsor.ai/rakuten_advertising?=[API_KEY]&date_preset=last_7d&fields=impressions,publishername,transaction_date,clicks,orders,sales,link_name,total_commission
Fields
| ID | Type | Name | Description |
|---|---|---|---|
| adjusted_commission | NUMERIC | Adjusted Commission | For tiered commission structures, this is the commission that is added to the baseline commission when a publisher reaches a certain level of sales. For example, an advertiser will set an offer that is 3% from $0-$100 in sales but will be 6% for any sales over $100. Cumulative sales are calculated on a per-month basis and the additional commission is added (the difference between 3% and 6%) in the Adjusted Commission field. This value is populated only at the end of the month when the Publisher Invoices are generated. |
| advertisername | TEXT | Advertiser Name | Select this column when running a report for more than one advertiser to see which row of data pertains to which advertiser. |
| average_item_value | TEXT | Average Item Value | The sum of your sales divided by the total number of items sold. |
| average_order_value | NUMERIC | Average Order Value | Total sales divided by the number of orders. |
| baseline_commission | TEXT | Baseline Commission | The commission amount that is calculated before adjustments are made at the end of the month for tiered offers. |
| booking_consumed_date | DATE | Booking Consumed Date | Often associated with the Travel vertical, this date is provided by the advertiser indicating when a reservation was fulfilled. |
| browser | TEXT | Browser | The browser a consumer used when clicking your link. |
| browser_major_version | TEXT | Browser Major Version | The major version of the browser a consumer used when clicking one of your links. For instance, if the browser is Firefox 20.0.1, this column will show 20. |
| browser_name | TEXT | Browser Name | The name of the browser a consumer used when clicking your link. For instance, if the browser is Firefox 20.0.1, this column will show Firefox. |
| browserversion | TEXT | Browser Version | The version of the browser a consumer used when clicking your link. For instance, if the browser is Firefox 20.0.1, this column will show 20.0.1. |
| canceled_items | TEXT | Canceled Items | The number of items canceled or returned in the selected date range. |
| category_id | TEXT | Category ID | The ID value of a particular category or service. |
| click_date | DATE | Click Date | This is the date that the first click or last click was recorded. |
| click_ip_address | TEXT | Click IP Address | The IP address of the customer who clicked your link. |
| click_position | TEXT | Click Position | This reflects the point in the timeline that is credited with the commission. |
| click_through_rate_ctr | TEXT | Click-Through Rate (CTR) | The number of clicks divided by the number of impressions. |
| clicks | NUMERIC | Clicks | The total number of clicks in the selected date range. |
| commission_status | TEXT | Commission Status | The status indicates whether the commission for an order is approved, pending, or canceled. (Note: This is currently only used by the Brazil network.) |
| commission_type | TEXT | Commission Type | This correlates with the advertiser's selection on the Create Offer form. The options are: % of Sale, $ per Action or Item, $ per Click, $ per 1,000 Impressions. |
| commissioning_list_id | TEXT | Commissioning List ID | This is the alphanumeric ID associated with a Dynamic Commissioning list. The Commissioning List ID is created by our system. This field will be blank for advertisers not using Dynamic Commissioning. |
| commissioning_list_name | TEXT | Commissioning List Name | This is the Dynamic Commissioning List applied to a transaction as part of the commissioning rules. Using the Commissioning List Name column allows for greater transparency into the status of Dynamic Commissioning offers. This field will be blank for advertisers not using Dynamic Commissioning. |
| consumer_city | CITY | Consumer City | The city that the consumer was in when they clicked your link. |
| consumer_connection_speed | TEXT | Consumer Connection Speed | The consumer’s connection speed when they clicked your link. |
| consumer_country | COUNTRY | Consumer Country | The country the consumer was in when they clicked your link. |
| consumer_region | REGION | Consumer Region | The region or state the consumer was in when they clicked your link. |
| converting_site_name | TEXT | Converting Site Name | Advertisers may have one or more websites. This value is the site name identified by the advertiser as the site where the conversion took place. |
| currency | TEXT | Currency | The currency that the reported transaction was in. |
| customer_score | TEXT | Customer Score | The value, provided by the advertiser, that represents their rank of the customer. |
| customer_status | TEXT | Customer Status | The status assigned by the advertiser to the consumer for a specific order. This is based on the customer's order history with the advertiser. |
| datasource | TEXT | Data Source | The name of the Windsor connector returning the row |
| date | DATE | Date | Alias for Transaction Date. |
| day_of_month | TEXT | Day of month | Day of the month |
| device | TEXT | Device | The device a consumer used when clicking your link. For example, PC, Tablet, Phone. |
| earnings_per_click_epc | TEXT | Earnings Per Click (EPC) | The total commissions generated divided by the number of clicks. Values multiplied by 100 for display purposes. |
| gross_commissions | TEXT | Gross Commissions | This column gives you a total of all commissions (not including CPC commissions) without taking cancellations into account. It is calculated by adding the sum of all commissions with values greater than 0. |
| gross_sales | NUMERIC | Gross Sales | This column gives you a total of all sales without taking cancellations into account. It is calculated by adding the sum of all sales with values greater than 0, plus any sales marked as "Discounts." Note that Discounts have a value that is less than 0 but is considered to be part of Gross Sales because they are taken into account as part of the total sale price at checkout. |
| gross_total_commissions | TEXT | Gross Total Commissions | This column gives you a total of all commissions (including CPC commissions) without taking cancellations into account. It is calculated by adding the sum of all commissions with values greater than 0. |
| group_offer_id | TEXT | Group Offer ID | The Group Offer ID is a single number assigned by our system that connects the base commission, SKU lists, and commissioning rules for more complex offers. |
| impressions | NUMERIC | Impressions | The number of times an ad was served to customers from a publisher’s site. |
| in_store_address | TEXT | In-Store Address | An address provided by the advertiser for the in-store location of the purchase. |
| in_store_city | CITY | In-Store City | The city, provided by the advertiser, for the in-store location of the purchase. |
| in_store_country | COUNTRY | In-Store Country | The country, provided by the advertiser, for the in-store location of the purchase. |
| in_store_id | TEXT | In-Store ID | An identifier assigned by the advertiser to the in-store location of the purchase. |
| in_store_name | TEXT | In-Store Name | The name assigned by the advertiser to the in-store location of the purchase. |
| in_store_state | TEXT | In-Store State | The state, or province provided by the advertiser, for the in-store location of the purchase. |
| in_store_zip_code | TEXT | In-Store Zip Code | The zip code, provided by the advertiser, for the in-store location of the purchase. |
| item_coupon_code | TEXT | Item Coupon Code | The coupon code applied to an item, as defined by the advertiser. Multiple codes are separated by commas. |
| item_coupon_codes | TEXT | Item Coupon Code(s) | The coupon code applied to an item, as defined by the advertiser. Multiple codes are separated by commas. |
| item_discount_amount | NUMERIC | Item Discount Amount | The amount discounted by the advertiser for an item in an order. |
| itemquantity | NUMERIC | Quantity | Similar to the # of Items column, Quantity is the number of items sold, excluding canceled items. |
| items | TEXT | Items | The number of items sold, including canceled items, in the selected date range. |
| landing_url | TEXT | Landing URL | This is the URL of the page on the advertiser's site where the consumer lands after clicking the affiliate link on the publisher's site. |
| link_id | TEXT | Link ID | The unique ID number for a link. |
| link_name | TEXT | Link Name | The name assigned to a link by the advertiser. |
| link_size | TEXT | Link Size | The size of a banner, DRM, Flex Link, or other creative types. This does not apply to text links. |
| link_type | TEXT | Link Type | The creative type: Banner, Text, Flex Link, and more. |
| member_id_u1 | TEXT | Member ID (u1) | A field the publisher appends to the Rakuten Advertising affiliate link codes. For instance, a rewards or loyalty publisher can use this field to capture member information. |
| mid | TEXT | MID | The identification number associated with an advertiser's account. Select this column when running a report for more than one advertiser to see which row of data pertains to which MID. |
| mobile_operating_system | TEXT | Mobile Operating System | The operating system of the device a consumer used when clicking your link. For instance, iOS or Android. |
| month | TEXT | Month | Number of the month |
| net_items | TEXT | Net Items | The number of items you sold after canceled items are subtracted from the gross number of items. |
| net_items_per_order | TEXT | Net Items Per Order | The number of items you sold divided by the number of orders that were placed. |
| network | TEXT | Network | The Rakuten Advertising network the advertiser/transaction is part of. |
| network_id | TEXT | Network ID | The identification number of the advertiser network the advertiser/transaction is part of. |
| non_commissionable_items | NUMERIC | Non-Commissionable Items | The number of items referred by a publisher that was deemed as non-commissionable based on the terms of the offer. |
| non_commissionable_order_id | TEXT | Non-Commissionable Order ID | The Order ID for a non-commissionable transaction. |
| non_commissionable_reason | TEXT | Non-Commissionable Reason | The reason a transaction was non-commissionable. It may be that the product was not part of an offer, the return days were exceeded, or some other circumstance. |
| non_commissionable_sales | TEXT | Non-Commissionable Sales | The sale amount referred by a publisher that was deemed as non-commissionable based on the terms of the offer. |
| offer_name | TEXT | Offer Name | The name given to an offer by an advertiser. |
| offer_number | TEXT | Offer Number | The Offer Number is a sequential number assigned by our system to each base commission, SKU list, and commissioning rule. |
| order_coupon_codes | TEXT | Order Coupon Code(s) | The coupon code applied to an order, as defined by the advertiser. Multiple codes are separated by commas. |
| order_discount_amount | TEXT | Order Discount Amount | The amount discounted by an advertiser for an order. |
| order_id | TEXT | Order ID | The ID of the order as reported by the advertiser. |
| order_ship_date | DATE | Order Ship Date | Advertiser-defined value that would hold a date. Represents when the item is shipped (or expected to ship). |
| order_status | TEXT | Order Status | The status of the order, as defined by the advertiser. |
| ordercouponcode | TEXT | Order Coupon Code | The coupon code applied to an order by the advertiser. Multiple codes are separated by commas. |
| orders | NUMERIC | Orders | The number of orders reported to Rakuten Advertising in the selected date range. |
| orders/click | TEXT | Orders/Click | The number of orders divided by the number of clicks. |
| orders/clicks | NUMERIC | Orders/Clicks | The number of orders divided by the number of clicks. |
| process_date | DATE | Process Date | The date the advertiser reported a transaction to us. |
| process_day_of_week | TEXT | Process Day of Week | The day of the week the advertiser reported a transaction to us. |
| process_month | TEXT | Process Month | The month the advertiser reported a transaction to us. |
| process_month_and_year | TEXT | Process Month and Year | The month and year the advertiser reported a transaction to us. |
| process_month_day | TEXT | Process Month-Day | The month and date the advertiser reported a transaction to us. |
| process_time | TIMESTAMP | Process Time | The time the advertiser reported a transaction to us. |
| process_week_begin_date | DATE | Process Week Begin Date | The date for the beginning of the week, always a Sunday, that the advertiser reported a transaction to us. |
| process_week_end_date | DATE | Process Week End Date | The date for the end of the week, always a Saturday, that the advertiser reported a transaction to us. |
| process_year | TEXT | Process Year | The year the advertiser reported a transaction to us. |
| product_brand | TEXT | Product Brand | The brand associated with the product or service, as defined by the advertiser. |
| product_category_2 | TEXT | Product Category 2 | The second category of the product or service, as defined by the advertiser. |
| product_category_3 | TEXT | Product Category 3 | The third category of the product or service, as defined by the advertiser. |
| product_category_4 | TEXT | Product Category 4 | The fourth category of the product or service, as defined by the advertiser. |
| product_category_5 | TEXT | Product Category 5 | The fifth category of the product or service, as defined by the advertiser. |
| product_category_id | TEXT | Product Category ID | The category ID of the product or service, as defined by the advertiser. |
| product_category_id_2 | TEXT | Product Category ID 2 | The second category level ID of the product or service, as defined by the advertiser. |
| product_category_id_3 | TEXT | Product Category ID 3 | The third category level ID of the product or service, as defined by the advertiser. |
| product_category_id_4 | TEXT | Product Category ID 4 | The fourth category level ID of the product or service, as defined by the advertiser. |
| product_category_id_5 | TEXT | Product Category ID 5 | The fifth category level ID of the product or service, as defined by the advertiser. |
| product_name | TEXT | Product Name | The name of the product sold. |
| productcategory | TEXT | Product Category | The category of the product or service, as defined by the advertiser. |
| publishername | TEXT | Publisher Name | Publisher Name. |
| referrer_url | TEXT | Referrer URL | The URL of the publisher's site that referred a customer to the advertiser's site. This field is useful for investigating the source of traffic. |
| sales | NUMERIC | Sales | Total sales in the selected period. |
| ship_to_country | COUNTRY | Ship-To Country | The country, provided by the advertiser, for where the order will be shipped. |
| signature_match_date | DATE | Signature Match Date | The date that our system matched the referring Signature information to a transaction. |
| signature_match_time | TIMESTAMP | Signature Match Time | The time our system matched the referring Signature information to a transaction. |
| sku | TEXT | SKU | The stock-keeping unit (SKU) number of the product sold. |
| source | TEXT | Source | The name of the Windsor connector returning the row |
| tax_exempt | TEXT | Tax Exempt | Some advertisers include tax within the Sales value. The Item Tax Exempt flag will be set to 'Y' by the advertiser to identify if the item is tax-exempt. The flag will be set to 'N' by the advertiser if the item is not tax-exempt. |
| today | DATE | Today | Today's date |
| total_commission | TEXT | Total Commission | The sum of the baseline commission and adjusted commission. |
| transaction_created_on_date | DATE | Transaction Created On Date | The date of the referring clickthrough for a transaction. This date is reported by the advertiser. |
| transaction_created_on_time | TIMESTAMP | Transaction Created On Time | The time of the referring clickthrough for a transaction. This timestamp is reported by the advertiser. |
| transaction_date | DATE | Transaction Date | The date that a consumer completed a transaction. |
| transaction_day_of_week | TEXT | Transaction Day of Week | The day of the week a consumer completed a transaction. |
| transaction_id | TEXT | Unique Transaction ID | A unique ID assigned to a transaction. |
| transaction_month | TEXT | Transaction Month | The month that a consumer created a transaction. |
| transaction_month_and_year | TEXT | Transaction Month and Year | The month and year when a consumer created a transaction. |
| transaction_month_day | TEXT | Transaction Month-Day | The month and date when a consumer created a transaction. |
| transaction_time | TIMESTAMP | Transaction Time | The time of day that a consumer completed a transaction. |
| transaction_week_begin_date | DATE | Transaction Week Begin Date | The date of the beginning of the week, always a Sunday, that a consumer completed a transaction. |
| transaction_week_end_date | DATE | Transaction Week End Date | The date for the end of the week, always a Saturday, that a consumer completed a transaction. |
| transaction_year | TEXT | Transaction Year | The year that a consumer completed a transaction. |
| week | TEXT | Week | Week (Sun-Sat). |
| week_day | TEXT | Day of week and day number | Weekday number and name combined (Sun-Sat).Numbered from 0 Sunday to 6 Saturday. |
| week_day_iso | TEXT | Day of week and day number, ISO | Weekday number and name combined, ISO format (Mon-Sun).Numbered from 1 Monday to 7 |
| week_iso | TEXT | Week ISO | Week, ISO format (Mon-Sun). |
| year | TEXT | Year | Year |
| year_month | TEXT | Yearmonth | Year and month, e.g. 2024|3 |
| year_of_week | TEXT | Year of week | The year that contains first day of the week (Sun-Sat). |
| year_of_week_iso | TEXT | Year of week, ISO | The year that contains first day of the ISO week (Mon-Sun). |
| year_week | TEXT | Year week | Year and week for US weeks (Sun-Sat), e.g. 2024|15 |
| year_week_iso | TEXT | Year week ISO | Year and week for ISO weeks (Mon-Sun), e.g. 2024|20 |