Connect to Amazon Redshift

​

Requirements

• An AWS account
• Your Redshift region
• A user to configure IAM roles
• A user with Redshift admin access

​

Step 1: Create an IAM Role

1. Open the Warehousing setup page Rockerbox and choose Redshift → Connect to Redshift.
2. Go into IAM service in the AWS Console.
3. Select Roles from the Access Management sidebar on the left.
4. Click Create Role from the top right portion of the page.
5. Keep the trusted entity service as the default AWS Service. Towards the bottom of the page, select Redshift from the dropdown list of “Use Cases for Other AWS Services” section. Then select Redshift - Customizable from the options.
6. You’ll be directed to the Permissions page. Don’t add any permissions here, and click Next.
7. Name this role “RockerboxRedshiftRole” , and use “Assumes Rockerbox Role to Access Rockerbox Shared Tables” for the description field.
8. Find the “RockerboxRedshiftRole” role you just created in the AWS Roles page and click into it.
9. Copy the ARN value and return to the Redshift Setup page in the Rockerbox UI where you’ll need to enter this ARN value.

​

Step 2: Add an Inline Policy to the IAM Role

1. Find the “RockerboxRedshiftRole” role you previously created in the AWS Roles page and click into it.
2. Click on Add Permissions and select Create Inline Policy.
3. The default selection on the next screen is “Visual Editor”. Select “JSON” instead.
4. Copy the Inline Policy displayed on your Redshift Setup UI and paste it into the JSON field and then click “Review Policy”.
5. Name this policy “RockerboxAccess” and then click “Create Policy”.

​

Step 3: Associate IAM Role to Redshift cluster and run an External Schema Command.

1. Go into Amazon Redshift service in the AWS Console.
2. Select your Redshift cluster from the Provisioned Clusters Dashboard.
3. Go to the properties tab inside your Redshift cluster.
4. Go to the Cluster Permissions section and click on Manage IAM Roles and then select Associate IAM Roles.
5. Select the “RockerboxRedshiftRole” that you previously created in the pop up window and then click on Associate IAM Roles.
6. Wait for the role to sync in the Associated IAM Roles list under Cluster Permissions section.
7. Go to query editor v2.
8. Copy the External Schema Statement displayed on your Redshift Setup UI, paste it into the query editor, and click Run.

​

Step 4: Sync Rockerbox Data Sets

• Choose which datasets to share:
  • Platform Data: Select the ad platforms you actively use.
  • Rockerbox Data: Select Conversion and any Log Level MTA datasets for each conversion event that you need.
• Click Sync this dataset when ready.

​

⚠️ Backfill Notes

• Platform Performance Schemas: No data is automatically backfilled. This can be backfilled on a limited basis upon request to support@rockerbox.com.
• Rockerbox First Party Data Schemas: For each conversion dataset, Rockerbox will backfill based on the conversion event “First Reporting Date” in Rockerbox
  💡 If no date is set, Rockerbox backfills one day of data. This process can take up to 24 hours.

​

Step 5: Query your new data tables

​

Sample queries: aggregate_mta table

SELECT * FROM `<project>.<dataset>.aggregate_mta` LIMIT 1;

SELECT 
  conversion_event_name,
  tier_1,
  SUM(even) AS conversions_even,
  SUM(included_spend) AS spend
FROM `<project>.<dataset>.aggregate_mta`
WHERE 
  date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND date < CURRENT_DATE();