.png){kind=logo}
Pardot Extract
-
DarkLight
Pardot Extract
-
DarkLight
This article is specific to the following platforms - Snowflake - Redshift - BigQuery.
Warning
From version 1.53 of Matillion ETL, the original Pardot Extract component has been deprecated. This connector will still be available for existing use (users who already have this component included in their Orchestration Jobs).
Additionally, from version 1.53 of Matillion ETL, a new version of the Pardot Extract component is available for use. This component uses an OAuth property for authentication. The User Key, Email Address, and Password properties have all been deprecated.
Pardot Extract
The Pardot Extract component calls the Salesforce Pardot API to retrieve and store data to be either referenced by an external table or loaded into a table, depending on the user's cloud data warehouse. Users can then transform their data with the Matillion ETL library of transformation components.
Using this component may return structured data that requires flattening. For help with flattening such data, we recommend using the Nested Data Load Component for Amazon Redshift and the Extract Nested Data Component for Snowflake or Google BigQuery.
Properties
Snowflake Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | String | A human-readable name for the component. |
| Data Source | Select | Select a data source. As noted above, once you have configured the Data Source property, one or more properties specific to that data source will become available to configure. These properties are not optional and must be configured. Please refer to the "Data Source Properties" table in this documentation for guidance with these additional properties. |
| Auth Method | Select | Select the authentication method. Users can choose OAuth or User Key. OAuth will then require an OAuth entry in the OAuth property; User Key will require completion of the User Key, Email Address, and Password properties. |
| OAuth | Select | Select an OAuth entry to authenticate this component. An OAuth entry must be set up in advance. To learn how to create and authorise a fresh OAuth, please read Pardot Authentication Guide. |
| User Key | String | (Deprecated component only as of v1.53) The user key string that corresponds to the email address and password credentials that are used to log in to Pardot. For help acquiring a Pardot user key, read Pardot Authentication Guide. |
| Email Address | String | (Deprecated component only as of v1.53) Please provide the email address for your Salesforce Pardot login. |
| Password | String | (Deprecated component only as of v1.53) Please provide the password for your Salesforce Pardot login. Passwords can be stored inside the component; however, it is highly recommend to use the Password Manager feature instead. |
| Business Unit ID | String | Input the Pardot Business Unit ID for your Pardot account. For more information, read Authentication. |
| Page Limit | Integer | Set the page limit for the amount of records to be returned and staged. You can use -1 to attempt to take all available data, but please be warned that this might take some time. |
| Location | Storage Location | Provide an S3 bucket path, GCS bucket path, or Azure Blob Storage path that will be used to store the data. Once on an S3 bucket, GCS bucket or Azure Blob, the data can be referenced by an external table. A folder will be created at this location with the same name as the Target Table. |
| Integration | Select | (GCP only) Choose your Google Cloud Storage Integration. Integrations are required to permit Snowflake to read data from and write to a Google Cloud Storage bucket. Integrations must be set up in advance of selecting them in Matillion ETL. To learn more about setting up a storage integration, read our Storage Integration Setup Guide. |
| Warehouse | Select | Choose a Snowflake warehouse that will run the load. |
| Database | Select | Choose a database to create the new table in. |
| Schema | Select | Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, please refer to this article. |
| Target Table | String | Provide a new table name. Warning: This table will be recreated and will drop any existing table of the same name. |
Redshift Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | String | A human-readable name for the component. |
| Data Source | Select | Select a data source. As noted above, once you have configured the Data Source property, one or more properties specific to that data source will become available to configure. These properties are not optional and must be configured. Please refer to the "Data Source Properties" table in this documentation for guidance with these additional properties. |
| Auth Method | Select | Select the authentication method. Users can choose OAuth or User Key. OAuth will then require an OAuth entry in the OAuth property; User Key will require completion of the User Key, Email Address, and Password properties. |
| OAuth | Select | Select an OAuth entry to authenticate this component. An OAuth entry must be set up in advance. To learn how to create and authorise a fresh OAuth, please read Pardot Authentication Guide. |
| User Key | String | (Deprecated component only as of v1.53) The user key string that corresponds to the email address and password credentials that are used to log in to Pardot. For help acquiring a Pardot user key, read Pardot Authentication Guide. |
| Email Address | String | (Deprecated component only as of v1.53) Please provide the email address for your Salesforce Pardot login. |
| Password | String | (Deprecated component only as of v1.53) Please provide the password for your Salesforce Pardot login. Passwords can be stored inside the component; however, it is highly recommend to use the Password Manager feature instead. |
| Business Unit ID | String | Input the Pardot Business Unit ID for your Pardot account. For more information, read Authentication. |
| Page Limit | Integer | Set the page limit for the amount of records to be returned and staged. You can use -1 to attempt to take all available data, but please be warned that this might take some time. |
| Location | Storage Location | Provide an S3 Bucket path that will be used to store the data. Once on an S3 bucket, the data can be referenced by an external table. A folder will be created at this location with the same name as the Target Table. |
| Type | Dropdown | Select between a standard table and an external table. |
| Standard Schema | Dropdown | Select the Redshift schema. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment. |
| External Schema | Select | Select the table's external schema. To learn more about external schemas, please consult the Configuring The Matillion ETL Client section of the Getting Started With Amazon Redshift Spectrum documentation. |
| Target Table | String | Provide a name for the external table to be used. Warning: This table will be recreated and will drop any existing table of the same name. |
BigQuery Properties | ||
|---|---|---|
| Property | Setting | Description |
| Name | String | A human-readable name for the component. |
| Data Source | Select | Select a data source. As noted above, once you have configured the Data Source property, one or more properties specific to that data source will become available to configure. These properties are not optional and must be configured. Please refer to the "Data Source Properties" table in this documentation for guidance with these additional properties. |
| Auth Method | Select | Select the authentication method. Users can choose OAuth or User Key. OAuth will then require an OAuth entry in the OAuth property; User Key will require completion of the User Key, Email Address, and Password properties. |
| OAuth | Select | Select an OAuth entry to authenticate this component. An OAuth entry must be set up in advance. To learn how to create and authorise a fresh OAuth, please read Pardot Authentication Guide. |
| User Key | String | (Deprecated component only as of v1.53) The user key string that corresponds to the email address and password credentials that are used to log in to Pardot. For help acquiring a Pardot user key, read Pardot Authentication Guide. |
| Email Address | String | (Deprecated component only as of v1.53) Please provide the email address for your Salesforce Pardot login. |
| Password | String | (Deprecated component only as of v1.53) Please provide the password for your Salesforce Pardot login. Passwords can be stored inside the component; however, it is highly recommend to use the Password Manager feature instead. |
| Business Unit ID | String | Input the Pardot Business Unit ID for your Pardot account. For more information, read Authentication. |
| Page Limit | Integer | Set the page limit for the amount of records to be returned and staged. You can use -1 to attempt to take all available data, but please be warned that this might take some time. |
| Table Type | Select | Select whether the table is Native (by default in BigQuery) or an external table. |
| Project | Select | Select the Google Bigquery project. The special value, [Environment Default], will use the project defined in the environment. For more information, refer to the BigQuery documentation. |
| Dataset | Select | Select the Google Bigquery dataset to load data into. The special value, [Environment Default], will use the dataset defined in the environment. For more information, refer to the BigQuery documentation. |
| Target Table | String | A name for the table. Warning: This table will be recreated and will drop any existing table of the same name. Only available when the table type is Native. |
| New Target Table | String | A name for the new external table. Only available when the table type is External. |
| Cloud Storage Staging Area | Cloud Storage Bucket | Specify the target Google Cloud Storage bucket to be used for staging the queried data. Users can either:
Only available when the table type is Native. |
| Location | Cloud Storage Bucket | Specify the target Google Cloud Storage bucket to be used for staging the queried data. Users can either:
|
| Load Options | Multiple Select | Clean Cloud Storage Files: Destroy staged files on Cloud Storage after loading data. Default is On. Cloud Storage File Prefix: Give staged file names a prefix of your choice. The default setting is an empty field. Recreate Target Table: Choose whether the component recreates its target table before the data load. If Off, the component will use an existing table or create one if it does not exist. Default is On. Use Grid Variable: Check this checkbox to use a grid variable. This box is unchecked by default. |
Data Source Properties
The following table lists any Data Source that requires one or more unique component properties for configuration. If a Data Source is missing from this table, it does NOT have any unique component properties.
| Data Source | Property Name | Type | Description |
|---|---|---|---|
| Email Stats | List Email ID | Integer | A single Email ID values. |
| Emails | Email ID | Integer | A single Email ID value. |
| List | Created before | Date | Include records created before a given GNU format data input. |
| Created after | Date | Include records created after a given GNU format data input | |
| Updated before | Date | Include records updated before a given GNU format data input | |
| Updated after | Date | Include records updated after a given GNU format data input | |
| List Membership | Created before | Date | Include records created before a given GNU format data input. |
| Created after | Date | Include records created after a given GNU format data input | |
| Updated before | Date | Include records updated before a given GNU format data input | |
| Updated after | Date | Include records updated after a given GNU format data input | |
| Prospects | Created before | Date | Include records created before a given GNU format data input. |
| Created after | Date | Include records created after a given GNU format data input | |
| Updated before | Date | Include records updated before a given GNU format data input | |
| Updated after | Date | Include records updated after a given GNU format data input | |
| Specific visitor details | ID | Integer | A single ID value or a comma-separated list of visitor ID values. |
| Visitor Activity | Created before | Date | Include visitor activity records created before a given GNU format data input. |
| Created after | Date | Include visitor activity records created after a given GNU format data input | |
| Updated before | Date | Include visitor activity records updated before a given GNU format data input | |
| Updated after | Date | Include visitor activity records updated after a given GNU format data input | |
| Visitors | Only identified visitors | true/false | Only include identified visitors in this query. |
| Created before | Date | Include records created before a given GNU format data input. | |
| Created after | Date | Include records created after a given GNU format data input | |
| Updated before | Date | Include records updated before a given GNU format data input | |
| Updated after | Date | Include records updated after a given GNU format data input | |
| Visits | Created before | Date | Include records created before a given GNU format data input. |
| Created after | Date | Include records created after a given GNU format data input | |
| Updated before | Date | Include records updated before a given GNU format data input | |
| Updated after | Date | Include records updated after a given GNU format data input | |
| IDs | Integer | A single ID value or comma-separated list of ID values. | |
| Visitor IDs | Integer | A single ID value or comma-separated list of Visitor ID values. | |
| Prospect IDs | Integer | A single ID value or comma-separated list of Prospect ID values. |
How to Obtain Your User Key
- Log in to your Salesforce Pardot account.
- Via the left-hand menu, go to Admin → User Management → Users.
- Click on the name of the user you want the User Key for.
- Copy the entry beside "API User Key".