Pepperjam integration summary

Stitch’s Pepperjam integration replicates data using the Pepperjam Advertiser API v20120402. Refer to the Schema section for a list of objects available for replication.

Pepperjam feature snapshot

A high-level look at Stitch's Pepperjam (v1) integration, including release status, useful links, and the features supported in Stitch.

STITCH
Release status

Beta

Supported by

Singer Community

Stitch plan

Free

API availability

Available

Singer GitHub repository

singer-io/tap-pepperjam

REPLICATION SETTINGS
Anchor Scheduling

Supported

Advanced Scheduling

Supported

Table-level reset

Unsupported

Configurable Replication Methods

Unsupported

DATA SELECTION
Table selection

Supported

Column selection

Supported

TRANSPARENCY
Extraction Logs

Supported

Loading Reports

Supported

Connecting Pepperjam

Step 1: Obtain your API key

  1. Login to your Pepperjam account.
  2. In the Developer Kit menu, click API Keys.
  3. Click Generate New Key.
  4. Your API key will display.

Keep this page open - you’ll need it to complete the next step.

Step 2: Add Pepperjam as a Stitch data source

  1. Sign into your Stitch account.
  2. On the Stitch Dashboard page, click the Add Integration button.

  3. Click the Pepperjam icon.

  4. Enter a name for the integration. This is the name that will display on the Stitch Dashboard for the integration; it’ll also be used to create the schema in your destination.

    For example, the name “Stitch Pepperjam” would create a schema called stitch_pepperjam in the destination. Note: Schema names cannot be changed after you save the integration.

  5. In the API Key field, paste the API key you obtained in step 1.

Step 3: Define the historical sync

The Sync Historical Data setting defines the starting date for your Pepperjam integration. This means that:

  • For tables using Key-based Incremental Replication, data equal to or newer than this date will be replicated to your destination.
  • For tables using Full Table Replication, all data - including records that are older, equal to, or newer than this date - will be replicated to your destination.

Change this setting if you want to replicate data beyond Pepperjam’s default setting of 1 year. For a detailed look at historical replication jobs, check out the Syncing Historical SaaS Data guide.

Step 4: Create a replication schedule

In the Replication Frequency section, you’ll create the integration’s replication schedule. An integration’s replication schedule determines how often Stitch runs a replication job, and the time that job begins.

Pepperjam integrations support the following replication scheduling methods:

To keep your row usage low, consider setting the integration to replicate less frequently. See the Understanding and Reducing Your Row Usage guide for tips on reducing your usage.

Step 5: Set tables and columns to replicate

To complete the setup, you’ll need to select the tables and columns you want to replicate to your destination.

Check out the Schema section to learn more about the available tables in Pepperjam and how they replicate.

  1. In the list of tables that displays - or in the Tables to Replicate tab, if you skipped this step during setup - locate a table you want to replicate.
  2. To track a table, click the checkbox next to the table’s name. A green checkmark means the table is set to replicate.

  3. To track a column, click the checkbox next to the column’s name. A green checkmark means the column is set to replicate.

  4. Repeat this process for all the tables and columns you want to replicate.
  5. When finished, click the Finalize Your Selections button at the bottom of the screen to save your selections.

Note: If you change these settings while a replication job is still in progress, they will not be used until the next job starts.

Initial and historical replication jobs

After you finish setting up Pepperjam, its Sync Status may show as Pending on either the Stitch Dashboard or in the Integration Details page.

For a new integration, a Pending status indicates that Stitch is in the process of scheduling the initial replication job for the integration. This may take some time to complete.

Free historical data loads

The first seven days of replication, beginning when data is first replicated, are free. Rows replicated from the new integration during this time won’t count towards your quota. Stitch offers this as a way of testing new integrations, measuring usage, and ensuring historical data volumes don’t quickly consume your quota.


Pepperjam replication

Attribution windows and data extraction

Every time Stitch runs a replication job for Pepperjam, the last 28-30 days’ worth of data will be replicated for the following tables:

  • creative_performance - 30 days

  • creative_performance_by_publisher - 30 days

  • publisher_performance - 30 days

  • transaction_details - 30 days

  • transaction_history - 28 days

Stitch replicates data in this way to account for updates made to existing records within an attribution window, thus ensuring you won’t make decisions based on stale (or false) data. As a result, you may see a higher number of replicated rows than what’s being generated in Pepperjam.

Setting the Replication Frequency to a higher frequency - like 30 minutes - can result in re-replicating recent data and contribute to greater row usage. Replicating fewer tables or selecting a lower frequency can help keep your row count low.

In the sections below are examples of how attribution windows impact how Stitch extracts data during historical and ongoing replication jobs.

For historical and full re-replications of Pepperjam data, Stitch will query for and extract data newer than or equal to the date defined in the Start Date field in the Integration Settings page.

The Start Date, in conjunction with the Attribution Window, defines the minimum date Stitch should query for when extracting historical data. This is calculated as:

Start Date - Attribution Window = Minimum Extraction Date

Example

During the initial set up, the Start Date field is set to July 3, 2017, or 2017-07-03 00:00:00.

To account for the Attribution Window, Stitch would calculate the Minimum Extraction Date value as: 2017-07-03 00:00:00 - 28-30 days = 2017-06-03 00:00:00

If you were to write a SQL query using this date for the transaction_details table, it might look like this:

  SELECT *
    FROM pepperjam.transaction_details
   WHERE sale_date >= '2017-06-03 00:00:00'   /* Min. Extraction Date */
ORDER BY sale_date

For ongoing replication jobs, Stitch will query for and extract data using the last saved maximum value in the table’s Replication Key column and the Attribution Window for the table.

Note: This applies to every replication job that takes place after the historical replication job.

Example

The last maximum saved Replication Key value for the transaction_details table is 2017-10-01 00:00:00.

To account for the Attribution Window of 28-30 days, we’d subtract this from the last maximum saved Replication Key value:

2017-10-01 00:00:00 - 28-30 days = 2017-09-01 00:00:00

In this case, Stitch would query for and extract data that is newer than or equal to 2017-09-01 00:00:00 and older than or equal to 2017-10-01 00:00:00.

If this were a SQL query, it might look like this:

  SELECT *
    FROM transaction_details
   WHERE sale_date >= '2017-09-01 00:00:00'
                              /* max Replication Key value - Attribution Window */
     AND sale_date <= '2017-10-01 00:00:00'
                              /* max Replication Key value from previous job */
ORDER BY sale_date

Pepperjam table reference

creative_advanced

Replication Method :

Key-based Incremental

Replication Key :

modified

Primary Key :

id

API endpoint :

getAdvancedLinksCreative

The creative_advanced table contains information about advanced link creatives in your Pepperjam account.

id
INTEGER

The advanced link creative ID.

modified
DATE-TIME

The time the advanced link was last modified.

code
STRING

created
DATE-TIME

description
STRING

end_date
DATE-TIME

flash_url
STRING

name
STRING

personalized
INTEGER

private
INTEGER

private_affiliates
ARRAY

creative_advanced (table), private_affiliates (attribute)

promotions
ARRAY

start_date
DATE-TIME

status
STRING

sub_type
STRING

supression_url
STRING

type
STRING

verified
STRING

view_date
DATE-TIME


Replication Method :

Key-based Incremental

Replication Key :

modified

Primary Key :

id

API endpoint :

getBannerCreative

The creative_banner table contains information about banner creatives in your Pepperjam account.

id
INTEGER

The banner ID.

modified
DATE-TIME

The time the banner was last modified.

allow_deep_link
INTEGER

alt_text
STRING

banner_url
STRING

created
DATE-TIME

description
STRING

destination_url
STRING

end_date
DATE-TIME

height
INTEGER

name
STRING

private
INTEGER

private_affiliates
ARRAY

creative_banner (table), private_affiliates (attribute)

promotions
ARRAY

start_date
DATE-TIME

status
STRING

type
STRING

view_date
DATE-TIME

width
INTEGER


Replication Method :

Key-based Incremental

Replication Key :

modified

Primary Key :

id

API endpoint :

getCouponCreative

The creative_coupon table contains information about coupon creatives in your Pepperjam account.

id
INTEGER

The coupon ID.

modified
DATE-TIME

The time the coupon was last modified.

coupon_code
STRING

created
DATE-TIME

description
STRING

end_date
DATE-TIME

name
STRING

personalized
INTEGER

private
INTEGER

private_affiliates
ARRAY

creative_coupon (table), private_affiliates (attribute)

promotions
ARRAY

start_date
DATE-TIME

status
STRING

type
STRING

url
STRING

view_date
DATE-TIME


Replication Method :

Key-based Incremental

Replication Key :

modified

Primary Key :

type

API endpoint :

getGenericLinkCreative

The creative_generic table contains information about generic link creatives in your Pepperjam account.

type
STRING

The type of generic link.

modified
DATE-TIME

The last time the generic link was modified.

allow_deep_link
INTEGER

url
STRING


creative_performance

Replication Method :

Key-based Incremental

Replication Key :

datetime

Primary Key :

creative_id : creative_type : date

API endpoint :

getCreativeDetails

The creative_performance table contains information about your Pepperjam creatives’ performance within a 30-day time frame from the date of the last table replication.

Note: During every replication job, Stitch will replicate the last 30 days’ worth of data for this table. Refer to the Attribution windows and data extraction section for more info.

creative_id
INTEGER

The creative ID.

Reference:

creative_type
STRING

The type of creative.

date
DATE

The date the creative was published.

datetime
DATE-TIME

The duration of the creative.

affiliate_usage
INTEGER

click_through_rate
NUMBER

clicks
INTEGER

commission
NUMBER

creative_name
STRING

earnings_per_click
NUMBER

group_id
INTEGER

Reference:

impressions
INTEGER

items
INTEGER

sales
NUMBER

transactions
INTEGER


creative_performance_by_publisher

Replication Method :

Key-based Incremental

Replication Key :

datetime

Primary Key :

creative_id : creative_type : date : publisher_id

API endpoint :

getCreativePerformanceByPublisher

The creative_performance_by_publisher table contains information about your Pepperjam creatives’ performance, per publisher, within a 30-day time frame from the date of the last table replication.

Note: During every replication job, Stitch will replicate the last 30 days’ worth of data for this table. Refer to the Attribution windows and data extraction section for more info.

creative_id
INTEGER

The creative ID.

Reference:

creative_type
STRING

The type of creative.

date
DATE

The date the creative was published.

publisher_id
INTEGER

The publisher ID.

Reference:

datetime
DATE-TIME

The duration of the creative.

affiliate_usage
INTEGER

click_through_rate
NUMBER

clicks
INTEGER

commission
NUMBER

creative_name
STRING

earnings_per_click
NUMBER

group_id
INTEGER

Reference:

impressions
INTEGER

items
INTEGER

publisher
STRING

sales
NUMBER

transactions
INTEGER


Replication Method :

Full Table

Primary Key :

id

API endpoint :

getProductCreative

The creative_product table contains information about product creatives in your Pepperjam account.

id
INTEGER

The product creative ID.

age_range
STRING

artist
STRING

aspect_ratio
STRING

author
STRING

battery_life
STRING

binding
STRING

buy_url
STRING

category_network
STRING

category_program
STRING

color
STRING

color_output
STRING

condition
STRING

description_long
STRING

description_short
STRING

director
STRING

discontinued
STRING

display_type
STRING

edition
STRING

expiration_date
STRING

features
STRING

focus_type
STRING

functions
STRING

genre
STRING

heel_height
STRING

height
STRING

image_thumb_url
STRING

image_url
STRING

in_stock
STRING

installation
STRING

isbn
STRING

keywords
STRING

length
STRING

load_type
STRING

location
STRING

made_in
STRING

manufacturer
STRING

material
STRING

megapixels
STRING

memory_capacity
STRING

memory_card_slot
STRING

memory_type
STRING

model_number
STRING

mpn
STRING

name
STRING

occasion
STRING

operating_system
STRING

optical_drive
STRING

pages
INTEGER

payment_accepted
STRING

payment_notes
STRING

platform
STRING

price
NUMBER

price_retail
NUMBER

price_sale
NUMBER

price_shipping
NUMBER

processor
STRING

product_format
STRING

publisher
STRING

quantity_in_stock
INTEGER

rating
STRING

recommended_usage
STRING

resolution
STRING

screen_size
STRING

shipping_method
STRING

shoe_size
STRING

shoe_width
STRING

size
STRING

sku
STRING

staring
STRING

style
STRING

tech_spec_url
STRING

tracks
INTEGER

upc
STRING

weight
STRING

width
STRING

wireless_interface
STRING

year
STRING

zoom
STRING


creative_promotion

Replication Method :

Full Table

Primary Key :

id

API endpoint :

getPromotionCreative

The creative_promotion table contains information about promotion creatives in your Pepperjam name account.


Replication Method :

Key-based Incremental

Replication Key :

modified

Primary Key :

id

API endpoint :

getTextCreative

The creative_text table contains information about text creatives in your Pepperjam account.

id
INTEGER

The text ID.

modified
DATE-TIME

The time the text was last modified.

allow_deep_link
INTEGER

created
DATE-TIME

description
STRING

end_date
DATE-TIME

link_anchor_text
STRING

private
INTEGER

private_affiliates
ARRAY

creative_text (table), private_affiliates (attribute)

promotions
ARRAY

start_date
DATE-TIME

status
STRING

type
STRING

url
STRING

view_date
DATE-TIME


Replication Method :

Full Table

Primary Key :

id

API endpoint :

getGroup

The group table contains information about groups in your Pepperjam account.


Replication Method :

Full Table

Primary Key :

id

API endpoint :

getGroupMember

The group_member table contains information about members within groups in your Pepperjam account.


Replication Method :

Full Table

Primary Key :

id

API endpoint :

getItemizedList

The itemized_list table contains information about your itemized lists in your Pepperjam account.

id
INTEGER

The itemized list ID.

Reference:

default
STRING

name
STRING

product_count
INTEGER


itemized_list_product

Replication Method :

Full Table

Primary Key :

id

API endpoint :

getItemizedListProducts

The itemized_list_product table contains information about products within your itemized lists in your Pepperjam account.

id
STRING

The product ID.

list_id
INTEGER

Reference:

name
STRING


Replication Method :

Full Table

Primary Key :

id

API endpoint :

getPublisher

The publisher table contains information about the publishers, the publishers’ status, and the publishers’ term in your Pepperjam account.

id
INTEGER

The publisher ID.

category
ARRAY

id
INTEGER

name
STRING

publisher (table), category (attribute)

company
STRING

country
STRING

first_name
STRING

group
ARRAY

join_date
DATE-TIME

last_name
STRING

promotional_method
ARRAY

id
INTEGER

name
STRING

publisher (table), promotional_method (attribute)

request_date
DATE-TIME

state
STRING

status
STRING

term
ARRAY

id
INTEGER

Reference:

name
STRING

publisher (table), term (attribute)

transparency
INTEGER

website
STRING


publisher_performance

Replication Method :

Key-based Incremental

Replication Key :

sale_date

Primary Key :

sale_date

API endpoint :

getPublisherPerformance

The publisher_performance table contains information about publishers’ performance within a 30-day time frame from the date of the last table replication.

Note: During every replication job, Stitch will replicate the last 30 days’ worth of data for this table. Refer to the Attribution windows and data extraction section for more info.

publisher_id
INTEGER

The publisher ID.

Reference:

order_id
INTEGER

The order ID.

Reference:

sale_date
DATE-TIME

The date the sale was made.

bonus_amount
NUMBER

clicks
INTEGER

company
STRING

earnings_per_click
NUMBER

group_id
INTEGER

Reference:

impressions
INTEGER

leads
INTEGER

publisher
STRING

publisher_bonus
NUMBER

publisher_commission
NUMBER

publisher_type
STRING

sale_lead_amount
NUMBER

sales
INTEGER

site_bonus
NUMBER

site_commission
NUMBER

state
STRING

total_commission
NUMBER

website
STRING


Replication Method :

Full Table

Primary Key :

id

API endpoint :

getTerm

The term table contains information about the terms for your program in your Pepperjam account.

id
INTEGER

The term ID.

Reference:

cookie_duration
STRING

created
DATE

default
STRING

name
STRING


transaction_details

Replication Method :

Key-based Incremental

Replication Key :

sale_date

Primary Key :

transaction_id

API endpoint :

getTransactionDetails

The transaction_details table contains all publisher transaction details, within a 30-day time frame from the date of the last table replication.

Note: During every replication job, Stitch will replicate the last 30 days’ worth of data for this table. Refer to the Attribution windows and data extraction section for more info.

transaction_id
INTEGER

The transaction ID.

Reference:

sale_date
DATE-TIME

The date the publisher’s sale was made.

click_id
STRING

commission
NUMBER

commission_publisher
NUMBER

commission_site
NUMBER

company
STRING

coupons
STRING

creative_id
STRING

Reference:

device_type
STRING

group_id
INTEGER

Reference:

link_type
STRING

new_to_file
STRING

order_id
STRING

Reference:

publisher
STRING

publisher_id
INTEGER

Reference:

publisher_type
STRING

sale_amount
NUMBER

sale_date
DATE-TIME

status
STRING

transaction_type
STRING


transaction_history

Replication Method :

Key-based Incremental

Replication Key :

sale_date

Primary Key :

transaction_id : process_date : sale_date

API endpoint :

getTransactionHistory

The transaction_history table contains all historical publisher transactions, within a 28-day time frame from the date of the last table replication.

Note: During every replication job, Stitch will replicate the last 28 days’ worth of data for this table.

transaction_id
INTEGER

The transaction ID.

Reference:

process_date
DATE-TIME

The date the transaction was processed.

sale_date
DATE-TIME

The date the sale was made.

commission
NUMBER

company
STRING

group_id
INTEGER

Reference:

item_id
STRING

link_type
STRING

order_id
STRING

Reference:

publisher
STRING

publisher_commission
NUMBER

publisher_id
INTEGER

Reference:

publisher_type
STRING

revision
STRING

sale_amount
NUMBER

site_commission
NUMBER

status
STRING

transaction_type
STRING

website_url
STRING



Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.