Publisher Report

The Publisher Reports consist of the Clicks, Orders and Returns reports.

These reports contain detailed information about clicks, purchases, returns, attribution, and earnings.

The reports can be published to an FTP/SFTP location or cloud storage bucket (S3/GCS) accessible by the partner.

The reports are published on configurable interval which defaults to 24 hours.

Each interval publishes data for the last 7 days.

The naming convention for each of the reports is:

  • Prefix: /<key>/YYYY/MM/ (defaults to slug)

  • Clicks: narrativ_log_files_clicks_YYYYMMDD.csv. The date in the filename indicates the clicks in the report took place on that date

  • Orders: narrativ_log_files_orders_YYYYMMDD.csv. The date in the filename indicates the orders/checkouts in the report took place on that date

  • Returns: narrativ_log_files_returns_YYYYMMDD.csv. The date in the filename indicates the returns in the report were processed on that date

The partner’s report reader should be compatible with Python’s csv.DictWriter

Contact your account manager to get started.

Schema and File Format

Clicks

The Click File contains information about Clicks and Impressions for that day, separated by each event occurrence. For example, each click or impression will be a new row.

This table can be used to identify:

  • Information about Clicks (ex. Number of clicks a given day)

  • Information about Impressions (ex. Number of impressions on a given day)

  • Earnings from CPC campaigns and clients

What to expect in the Clicks report

Let’s break it down with an example:

  • Link 987 received 2 clicks on day 1 and 4 clicks on day 2

The Clicks reports would look like:

  • On Day 1 two rows for link_id 987 with associated click information including earnings (if any)

  • On Day 2 four rows for link_id 987 with associated click information including earnings (if any)

Column

Description

click_id

Unique identifier for each click event

click_timestamp

Date Time of click in GMT

cookie_id

The cookie ID

article_url

For clicks: this is the URL which has the link that was clicked on; For impressions: the page URL that was viewed

is_clean_article_url

Bool: true if article url was passed to Howl during link creation; False if no url was passed to Howl and we read url from the page

referrer

Referrer from web browser

user_agent

User agent from web browser

is_click

Boolean flag to indicate click vs impressions Is_click will equal 1 when the record is a click, and 0 when the record is an impression

click_product_url

Product URL of the link that was clicked on

earnings

Earning for publisher. Please note this includes all payment models

click_product_category

Category of the product that was clicked on

click_is_brand_page

Boolean: true is the link that was clicked on linked to a brand page

click_u1_param

U1 parameter of the click

click_product_brand

Brand of the product that was clicked on

click_product_price

Price of the product that was clicked on

click_product_name

Name of the product that was clicked on

article_name

For clicks: this is the name of the article which has the link that was clicked on; For impressions: the Name of article that was viewed

link_id

Unique identifier for each Howl link, sometimes referred to as auction ID.

network

This identifies the payment model associated with the record - the values can be CPC, CPA, CPM, EPP

merchant

Merchant name of link

publication

Publisher name

link_type

Indicates if the link was created through a manual or programmatic process

channel

Digital channel of the event i.e. desktop

device_type

Device event occurred on

country_code

ISO 3166-2 Country code

link_source

Detailed information on how link was created.

Orders

The Orders File contains information about orders (checkouts) for that day. Each product purchased has its own row in this file, thus there may be multiple records associated with one checkout in the file.

This table can be used to identify -

  • Information about Orders (ex. Number of orders a given day)

  • Earnings from EPP campaigns and clients

What to expect in the Orders report

Let’s break it down with an example:

  • Checkout 123 is attributed to a publication, and included :

  • product A1 with a quantity of 4, earnings 40, revenue 400, and

  • product B1 with a quantity of 1, earning 15 and revenue 150

and

  • Checkout 321 is attributed to a publication, and included :

  • product A1 with a quantity of 1, earnings 10, revenue 100, and

The Orders reports would look like:

  • One row for checkout ID 123 and product ID A1 with number_products_purchased 4, earnings 40, revenue 400.

  • One row for checkout ID 123 and product ID B1 with number_products_purchased 1, earnings 15, revenue 150.

  • One row for the checkout ID 321 and product ID A1 with number_products_purchased 1, earnings 10, revenue 100.

Column

Description

order_product_id

Unique identifier for each record in the orders file, which is a combination of the order_id and the

product_id

Unique identifier of the order product

order_id

Unique identifier of the order generated by Howl

checkout_timestamp

Date time of the checkout in GMT

cookie_id

The cookie ID

article_url

URL of the page that has the link clicked attributed to this checkout

link_id

Unique identifier for each Howl link, sometimes referred to as auction ID. This refers to the link attributed to this checkout. Please note this can be used to join the orders file.

referrer

Referrer from web browser

user_agent

User agent from web browser

network_type

Type of network from where the checkout data is obtained - ‘Howl - No returns’ or ‘Rakuten - Returns Apply’

click_product_url

Product URL of the link that was clicked on, attributed to this checkout

click_product_category

Category of the product that was clicked on

click_is_brand_page

Boolean: true is the attributed link that was clicked on linked to a brand page

days_since_attributed_click

Number of days since the click event attributed to this checkout

purchased_product_id

Product ID of the product purchased. This is the Merchant’s product ID

purchased_product_name

Name of the product purchased

purchased_product_brand

Brand of the product purchased

purchased_product_price

Price of the product purchased

click_u1_param

U1 parameter of the click attributed to this checkout

checkout_id

Identifier of the checkout from the Merchant

earnings

This contains EPP and Linkshare earnings. Please note for orders with multiple products, the earnings column for each row is the earnings for the total order, not that individual product within the order.

revenue

Revenue driven by the order

number_products_purchased

Quantity of products purchased

click_product_brand

Brand of the product that was clicked on attributed to this checkout

click_product_price

Price of the product that was clicked on attributed to this checkout

click_product_name

Name of the product that was clicked on attributed to this checkout

article_name

name of the article which has the link that was clicked on attributed to this checkout

network

This identifies the payment model associated with the record - the values can be CPC, CPA, CPM, EPP. This can be used to pivot the earnings and identify how much the EPP earnings are, and how much the CPA earnings are

merchant

Merchant name

publication

Publisher Name

channel

Digital channel of the event i.e. desktop

device_type

Device event occurred on

country_code

ISO 3166-2 Country code

click_id

Unique identifier for each click event attributed to this checkout

click_timestamp

Date time in GMT of the click attributed to this checkout

Returns

The Returns File contains information about returns processed on that day from orders (checkouts) attributed to the publisher at specific merchant partners in the past. This report captures the returns processed on a particular day, regardless of when the original checkout occurred. The returns can be partial or full, and can be broken over multiple days.

The report contains unique rows for each product returned in the order as they are processed, even if multiple products from the same checkout are returned and processed separately. Each row will include information about the number of items returned(quantity), the amount of earnings borne from the items returned, and the revenue associated with the returned items. Since the data in these reports pertains to returns, the earnings, revenue and quantity values are negative.

To gain a comprehensive view of returns, we recommend aggregating the values for each unique combination of checkout_id, merchant and product_id (denoted by the order_product_id column) across all return files.

Since this report is designed to be used to complement the data in the orders reports, the column ‘order_product_id’ can be used to join to the orders report.

This table can be used to identify -

  • Quantity of products returned

  • Earnings and revenue from the returned products

What to expect in the Returns Report

Let’s break it down with an example:

Checkout 123 is attributed to a publication, and originally included :

  • product A1 with a quantity of 4, earnings 40, revenue 400, and

  • product B1 with a quantity of 1, earning 15 and revenue 150

The customer returns:

  • 2 quantity of product A1 on day 1, and

  • 1 quantity of product A1 and 1 quantity of product B1 on day 2.

The Returns reports would look like:

  • One row in the report for day 1 for checkout ID 123 and product ID A1 with quantity -2, earnings -20, revenue -200.

  • One row in the report for day 2 for the checkout ID 123 and product ID A1 with quantity -1, earnings -10, revenue -100 and one row for the checkout ID 123 and product ID B1 with quantity -1, earnings -15, revenue -150.

When combining the returns information with the orders information, make sure to group the returns information by order_product_id or checkout_id-merchant-product_id first. Then, join it with the orders data, to avoid missing data regarding partial returns that may be processed over multiple days.

Column

Description

order_product_id

Foreign key to join to Orders Report, unique to checkout_id,merchant and purchased_product_id combination

checkout_timestamp

Timestamp of checkout

purchased_product_id

Unique identifier for product returned

checkout_id

Identifier of the checkout returned from the Merchant

merchant

Merchant name

publication

Publisher Name

return_revenue

Revenue associated with product items returned

return_earnings

Earnings associated with product items returned

return_quantity

Quantity of product items returned

return_datetime

Datetime of return’s/partial return’s processing

return_id

Unique identifier for the return/partial returns for this checkout on this day

Example Data Queries

Calculate earnings by payment model

To calculate the CPC earnings, use the Clicks file, and sum up the earnings column. This file contains only the CPC earnings as only CPC pays on click.

To calculate CPA earnings, use the Orders file, and apply a filter for Network = CPA. After that dedupe to only consider unique order_id and sum up the filtered earnings column. This is because for orders with multiple products, the earnings column for each row is the earnings for the total order, not that individual product within the order. (Note these earnings are subject to return and will not be finalized for 90 days)

To calculate EPP earnings, use the Orders file, and apply a filter for Network = EPP. After that dedupe to only consider unique order_id and sum up the filtered earnings column. This is because for orders with multiple products, the earnings column for each row is the earnings for the total order, not that individual product within the order. Calculate total earnings

To calculate the total earnings, you would need to sum up the earnings columns of both the Clicks and Orders files. This would allow you to get the CPC earnings (from the earnings column in clicks file) * 0.01 and EPP + CPA earnings (from the earnings column in orders file) Calculate clicks and impressions To calculate clicks based on merchants, you would use the clicks file. The field is_click can be used to identify whether the record corresponds to a click (is_click=1) or an impression (is_click = 0)

SELECT merchant, COUNT(*) FROM clicks_file WHERE is_click = 1 group by 1

To get count of click/impressions/earnings based on posts or articles, the data can be split based article_url, however we recommend using a clean version of the URL (probably without query and fragment portions).

Similarly, clicked_product_name can be used to pivot data on a product basis.

Calculate earnings per product

The earnings per product in an order can be calculated using the formula below - earnings_per_product = total_order_earnings * (purchased_product_price * number_products_purchased / total_order_revenue) These can then be summed up based on the product ID, if needed. Since certain merchants honor returns, including the earnings returned due to returns is pertinent.