Export data with Stitch

RevenueStory lets you replicate the underlying and historical data into your BI-based warehouse using Stitch. It uses Stitch to establish secure connections between your data in RevenueStory servers and your destination sources for reliable and rapid movement of data.

Intended audience

The information described in this document is specific (but not limited) to developers responsible for Freshworks backend integration with Chargebee. This includes developers and administrators with the appropriate authorization to Freshworks' database and related resources.

How it works?

RevenueStory data model configuration with Stitch enables it to listen and process your data easily.

Once the pipeline is established, RevenueStory then leverages Stitch's import API feature to push your data.

This capability creates a secure ecosystem to transfer data from source to destination.

Steps to establish a pipeline

Step 1: Generate an API Key

Generate a Chargebee API Key for Stitch. This will allow Stitch to read data from your Chargebee account using the Chargebee API.

1. Sign into your Chargebee account.
2. Cick Settings > Configure Chargebee > API keys and webhooks.
3. On the API Keys page, click the + Add API Key button.
4. On the Create an API Key modal, select Read-Only Key as the API key type.
5. Select All to define the API key's access. This will grant read-only access to your Chargebee site.
6. In the Name the API key field, enter "stitch".
7. Click Create Key.

Chargebee creates the API key and redirect you back to the API Keys page.

Step 2: Integrate using Import API

1. Sign in to Stitch account.
2. On your Stitch Data Loader UI, click Add Integrations.
3. On the Add and Integration search box, search for "Import API" and select it.
4. On the Configure your Import API Integration screen, enter an Integration Name. For example, "sample-integration". Use an easily identifiable name specific for your Chargebee site. It is recommended that you follow a convention.
5. Click Save and Generate Tokens. This creates a Stitch API token which is used to authenticate data transfer from your Chargebee site to Stitch.
6. Copy the Stitch API Token and click Close and Continue. This adds the Stitch Import API integration to your account
7. Click All Done.

Configuring your warehouse

The following steps describe how you can configure your warehouse.

For the documentation purpose, we have taken the example of Amazon S3 server. If you wish to integrate Stitch for other storage resources, the settings may vary. Refer to the related documentation in Stitch to know more. Learn about details of choosing a destination for smooth data transfer.

1. On the Stitch Data Loader main menu, click Destination
2. On the Select a destination for your data screen, choose your destination server (in this case, Amazon S3)
3. On your destination storage resource, create a new Bucket (or Host end point), say stitch-sample-demo-docs
4. Enter the name of the storage resource (or Host end point).
5. (Optional) Under Store Data in this Format, select CSV.
6. Grant Stitch access to write to your storage resource (Host end point)
   • Click Copy Code
   • Navigate to your Amazon S3 instance, Buckets > stitch-sample-demo-docs > Permissions > Bucket Policy and enter the code copied to your clipboard.
   • Click Save.
7. Verify the connection, by uploading a test file.
8. Click Check and Save.

This will establish your Pipeline through which Stitch writes into your storage resource.

Change your Destination

If you wish to change your destination, follow the below steps:

1. On the Stitch Data Loader main menu, click Destination .
2. Under the Historical Data section, choose:
   • Replicate new data only: This will enable stitch to only receive new data from your Chargebee Site
   • Replicate historical data: This will initiate a full resync from the date specified at the Integration stage.
3. Click Continue. This will delete the current destination.
4. Click Remove.

This will remove the current destination and you can set up a new one from the configuring your warehouse step.

Installation

Copy and paste the following code snippet and save it as install.sh

python3 -m venv ~/.virtualenvs/target-stitch
source ~/.virtualenvs/target-stitch/bin/activate
pip install target-stitch
deactivate
python3 -m venv ~/.virtualenvs/tap-chargebee
wget -O tap-chargebee-RsFreshworksStitchIntegration.zip https://github.com/cb-preethy/tap-chargebee/archive/RsFreshworksStitchIntegration.zip 
unzip tap-chargebee-RsFreshworksStitchIntegration.zip
cd tap-chargebee-RsFreshworksStitchIntegration
source ~/.virtualenvs/tap-chargebee/bin/activate
pip install .
cd ..
cp tap-chargebee-RsFreshworksStitchIntegration/tap_chargebee/rs_resources_schema_meta.json ~/.virtualenvs/tap-chargebee/rs_resources_schema_meta.json
deactivate
rm -rf tap-chargebee-RsFreshworksStitchIntegration
rm tap-chargebee-RsFreshworksStitchIntegration.zip

Execute this saved file: sh install.sh

Data Synchronization

Copy and paste the following code snippet and save it as chargebee_revenuestory_sync.sh

~/.virtualenvs/tap-chargebee/bin/tap-chargebee --config $1 --catalog ~/.virtualenvs/tap-chargebee/rs_resources_schema_meta.json | ~/.virtualenvs/target-stitch/bin/target-stitch --config $2

Execute this saved file:

nohup sh chargebee_revenuestory_sync.sh <chargebee config file> <stitch config file> > <outputlogfilename> 2>&1 &

Daily sync

After the first sync is completed fully, please add a crontab entry to execute chargebee_revenuestory_sync.sh in the desired interval.

Ex: To setup sync everyday at 10 am, please add the following to crontab entry:

0 10 * * * sh chargebee_revenuestory_sync.sh <chargebee config file> <stitch config file> > <outputlogfilename> 2>&1

The following data (API columns) is synced from RevenueStory.

Applied_credits

• amount
• currency_code
• date
• invoice_id
• credit_note_id

Coupons

• id
• name
• invoice_name
• discount_type
• discount_percentage
• discount_amount
• currency_code
• duration_type
• duration_month
• valid_till
• max_redemptions
• status
• apply_on
• plan_constraint
• addon_constraint
• created_at
• archived_at
• redemptions

Credit note discount lines

• discount_amount
• currency_code
• discount_type
• invoice_id
• credit_note_id
• credit_note_line_item_id
• coupon_id

Credit note discounts

• description
• discount_amount
• currency_code
• discount_type
• invoice_id
• credit_note_id
• coupon_id

Credit note line items

• id
• date_from
• date_to
• unit_amount
• quantity
• is_taxed
• tax_amount
• tax_rate
• amount
• discount_amount
• item_level_discount_amount
• description
• entity_type
• tax_exempt_reason
• currency_code
• invoice_id
• credit_note_id
• credit_note_date

Credit note tax lines

• tax_name
• tax_rate
• tax_amount
• taxable_amount
• is_non_compliance_tax
• is_partial_tax_applied
• currency_code
• jurisdiction_type
• jurisdiction_name
• jurisdiction_code
• invoice_id
• credit_note_id
• credit_note_line_item_id

Credit note taxes

• tax_name
• tax_description
• tax_amount
• currency_code
• invoice_id
• credit_note_id

Credit note transactions

• currency_code
• applied_amount
• applied_at
• refund_id
• credit_note_id

Credit notes

• id
• type
• reason_code
• status
• vat_number
• date
• price_type
• currency_code
• total
• amount_allocated
• amount_refunded
• amount_available
• refunded_at
• voided_at
• sub_total
• round_off_amount
• related_customers
• related_subscriptions
• invoice_id

Customers

• id
• first_name
• last_name
• email
• country
• created_at
• business_type
• business_vertical
• acquisition_cost
• acquisition_cost_currency_code
• acquisition_channel
• acquisition_mode
• sales_agent
• customer_type
• phone
• company
• vat_number
• auto_collection
• net_term_days
• allow_direct_debit
• created_from_ip
• taxability
• exempt_category
• exempt_number
• consolidated_invoicing
• billing_day_of_month
• billing_day_of_week
• fraud_flag
• primary_payment_source_id
• secondary_payment_source_id
• account_manager
• customer_success_agent
• solution_partner
• freemium_blacklist_reason

Dunning details

• transaction_id
• dunning_type
• transaction_amount
• currency_code
• attempt
• created_at
• invoice_id

Invoice discount lines

• discount_amount
• currency_code
• discount_type
• invoice_id
• invoice_line_item_id
• coupon_id
• invoice_date

Invoice discounts

• description
• discount_amount
• currency_code
• discount_type
• invoice_id
• coupon_id
• invoice_date

Invoice line items

• id
• date_from
• date_to
• unit_amount
• quantity
• is_taxed
• tax_amount
• tax_rate
• amount
• discount_amount
• item_level_discount_amount
• description
• entity_type
• tax_exempt_reason
• is_metered
• currency_code
• invoice_id
• invoice_date

Invoice tax lines

• tax_name
• tax_rate
• tax_amount
• taxable_amount
• is_non_compliance_tax
• is_partial_tax_applied
• currency_code
• jurisdiction_type
• jurisdiction_name
• jurisdiction_code
• invoice_id
• invoice_line_item_id
• invoice_date

Invoice taxes

• tax_name
• tax_description
• tax_amount
• currency_code
• invoice_id
• invoice_date

Invoice transactions

• currency_code
• applied_amount
• applied_at
• payment_id
• invoice_id

Invoices

• id
• po_number
• is_recurring_invoice
• status
• vat_number
• price_type
• date
• due_date
• net_term_days
• currency_code
• total
• amount_paid
• amount_adjusted
• write_off_amount
• credits_applied
• amount_due
• paid_at
• dunning_status
• next_retry_at
• voided_at
• sub_total
• total_tax_amount
• is_first_invoice
• has_advance_charges
• expected_payment_date
• amount_to_collect
• round_off_amount
• term_finalized
• is_gifted
• related_customers
• related_subscriptions
• payment_owner
• customer_id
• subscription_id

Payment Sources

• id
• type
• reference_id
• status
• gateway
• gateway_account_id
• ip_address
• issuing_country
• card_first_name
• card_last_name
• card_iin
• card_last4_digit
• card_brand
• card_funding_type
• card_expiry_month
• card_expiry_year
• card_billing_addr1
• card_billing_addr2
• card_billing_city
• card_billing_state_code
• card_billing_state
• card_billing_country
• card_billing_zip
• card_masked_number
• bank_last4_digit
• bank_name_on_account
• bank_name
• bank_mandate_id
• bank_account_type
• bank_echeck_type
• bank_account_holder_type
• amazon_email
• amazon_agreement_id
• paypal_email
• paypal_agreement_id
• deleted
• customer_id

Payments

• id
• gateway_account_id
• payment_source_id
• payment_method
• reference_number
• gateway
• type
• date
• settled_at
• currency_code
• amount
• id_at_gateway
• status
• fraud_flag
• fraud_reason
• error_code
• voided_at
• masked_card_number
• subscription_id
• unused_amount
• capturable_amount
• authorization_reason

Products

• id
• name
• invoice_name
• product_type
• price
• currency_code
• period
• period_unit
• charge_model
• charge_type
• product_group
• trial_period
• trial_period_unit
• free_quantity
• setup_cost
• status
• billing_cycles
• enabled_in_hosted_pages
• enabled_in_portal
• sku
• accounting_code
• accounting_category1
• accounting_category2
• tax_code
• taxable

Refunds

• id
• gateway_account_id
• payment_source_id
• payment_method
• reference_number
• gateway
• type
• date
• settled_at
• currency_code
• amount
• id_at_gateway
• status
• fraud_flag
• fraud_reason
• error_code
• voided_at
• masked_card_number
• subscription_id

Subscriptions

• id
• initial_plan_id
• current_plan_id
• currency_code
• created_at
• activated_at
• mrr
• status
• plan_quantity
• plan_unit_price
• setup_fee
• billing_period
• billing_period_unit
• plan_free_quantity
• start_date
• trial_start
• trial_end
• current_term_start
• current_term_end
• next_billing_at
• remaining_billing_cycles
• po_number
• started_at
• cancelled_at
• cancel_reason
• created_from_ip
• has_scheduled_changes
• payment_source_id
• auto_collection
• due_invoice_count
• due_since
• total_dues
• initial_active_plan_id
• product_usage
• sales_agent
• solution_partner
• acquisition_cost
• customer_id
• next_billing_amount

Daily subscription histories

• mrr_sub_status
• mrr_status
• subscription_id
• status
• currency_code
• period_end
• period_start
• date
• mrr_diff
• discount
• mrr

Weekly subscription histories

• mrr_sub_status
• mrr_status
• subscription_id
• status
• currency_code
• period_end
• period_start
• date
• mrr_diff
• discount
• mrr

Monthly subscription histories

• mrr_sub_status
• mrr_status
• subscription_id
• status
• currency_code
• period_end
• period_start
• date
• mrr_diff
• discount
• mrr

quarterly_subscription_histories

• mrr_sub_status
• mrr_status
• subscription_id
• status
• currency_code
• period_end
• period_start
• date
• mrr_diff
• discount
• mrr

Yearly subscription histories

• mrr_sub_status
• mrr_status
• subscription_id
• status
• currency_code
• period_end
• period_start
• date
• mrr_diff
• discount
• mrr

Useful Resources

Following are some useful resources that will help you in your integration.