9-Steps Implementation

I’ve developed these steps that showcase key elements that your automation should handle.

1. Get User Consent

Before any data collection begins, make sure compliance with privacy regulations by:

1. Implementing consent management
2. Validating user consent status ad_user_data and ad_storage before proceeding with Lead data collection

2. Lead Collection

When a user signs up for a free trial through an affiliate link,

1. Set Behavior Tags that should fire when a Lead subscribes to the Free Trial
2. Capture these lead dimensions and metrics: Email, AWC parameter (click ID), the subscription name, the Order ID, and the value of the conversion
3. Send the data to two destinations: the conversion AWIN API endpoint and an SQL table with the name ‘leads’

3. Converted Lead Collection

When a Lead renews the subscription and becomes a paid customer

1. Set a Trigger with an Automation Tool that will capture customer data when the target subscription is renewed
2. Send these converted lead data: Date, Email, subscription name, and value
3. Store in a SQL table with the name ‘converted_leads’. The table should have a column named ‘processed_by_awin’ You will need this column for Steps 5, 6, and 9.

4. AWIN Transaction ID Collection

AWIN Transaction ID is required to Approve or Denny the affiliate commission. The challenge with the Transaction ID is that it is generated in the AWIN backend. It is generated when their system receives a Lead conversion with an Order ID on the website.

Furthermore, the Transaction ID is not accessible during the website session, therefore, we need a mechanism that pulls the Transaction ID associated with the Order ID of a Lead

1. Use a Cloud Function daily to pull the Orders ID and their associated Transaction ID
2. Insert the data in an SQL table with the name awin_transaction_ids

5. Converted Lead Data Processing

After setting the tables from Steps 2, 3, and 4.

1. create a Daily Scheduled Query that joins those tables.
2. Make sure to match the converted leads only, and make sure that the leads were not processed already in AWIN, you can achieve this by checking the ‘processed_by_awin’ column in the ‘converted_lead’ table.
3. The new table should be named ‘converted_leads_upload’ and should have all the columns from the three tables.
4. Overwrite the table each day, it will help to prevent sending false positive and false negative data to AWIN

6. Approve Affiliate Commission

Each day, after the table ‘converted_leads_upload’ is created

1. With an Automation Tool, set a trigger to fire when a New Row is added to the table ‘converted_leads_upload’
2. Send the converted lead data to AWIN via the Automation Tool, and send this info: Transaction ID, Approve
3. Wait 90 seconds within the Automation Tool flow, and then send an update to the ‘converted_lead’ table described in step 3, edit the column ‘processed_by_awin’, and add the value ‘true’ in the row related to the converted lead. We need this final step so we avoid sending false positives and negatives.

7. Expired Lead Subscription Collection

We also need to deny commissions in AWIN if the Leads do not renew their subscription. As you did in Step 3, when a Lead Expire their subscription, we need to do the following:

1. Set a Trigger in an Automation Tool, to catch when the subscription is expired.
2. Send Date of Expiration, Email, and Subscription name
3. Send the data to an SQL Table named ‘expired_subscription’

8. Expired Leads Subscriptions Data Processing

After setting the tables from Steps 2, 4, and 7.

1. Create a Daily Scheduled Query that joins those tables.
2. Make sure to match the leads with expired subscriptions only.
3. The new table should be named ‘expired_leads_upload’ and should have all the columns from the three tables.
4. Overwrite the table each day.

9. Deny Affiliate Commission

Each day, after the table ‘expired_leads_upload’ is created

1. With an Automation Tool, set a trigger to fire when a New Row is added to the table ‘expired_leads_upload’
2. Send the lead data to AWIN via the Automation Tool, send this info: Transaction ID, Deny
3. Wait 90 seconds within the Automation Tool flow, and then send an update to the ‘converted_lead’ table described in step 3, edit the column ‘processed_by_awin’, and add the value ‘true’ in the row related to the converted lead. We need this final step so we avoid sending false positives and negatives.