Overview: Data import queries

To configure data import, you need to write a data query for importing this data. Depending on the type of data you want to import, you can use different data query templates that act as a basis for your query. You can further edit and customize the templates to match your requirements. In addition, query templates differ:

  • For the specific set of data you want to extract
    Generally speaking, data can be classified as interaction data and contact data
  • For the different data source types
    For more information, see Data sources and data integration.

SQL query requirements

The following table summarizes the requirements your SQL data import query must fulfill.

You use SQL queries to import data from Salesforce. 

Some requirements apply to all types of data of data you import and others are specific for the type of data.

Type of data Query requirements
All types of data  Result from the queries must not contain duplicated rows. Thus, a query should have a column or a combination of columns that provide unique identification of the selected records. 
All types of data  Queries must provide paging by the unique column. 
All types of data  Queries must provide an ORDER BY clause on the unique column. In case the unique column is of an alpha-numerical type (string type), you need to call the LOWER function on its value. 
All types of data  The queries must provide placeholders (for example, @Take and @LastRead) for the value of the page size parameter and the parameter in the WHERE clause. 
Interactions 
  • The alias of the unique column must be UniqueId
  • The sentence queries must provide at least two columns in addition to the unique column. Their aliases must be Subject and Object
  • All the aliases in the query must be in quotes ""
Contact mapping 

The subject mapping queries must provide 2 columns: Subject1 and Subject2 that correspond to the contact IDs in the first and second data source, respectively. These contact IDs need to be mapped to each other.

Contact metadata  Queries must provide a column with alias Subject, which contains the value of the contact’s identification in the origin data source. 

JSON template requirements

The following table summarizes the requirements your JSON template data query must fulfill. You use JSON template queries to import data from Oracle Eloqua.

You can write your own query from scratch, which Digital Experience Cloud then validates.

You can consult the Eloqua-specific activity types for more details on the corresponding property values in the JSON templates. For details, see the official Oracle Eloqua's documentation for source types: Activity Fields.

Type of data  Query requirements
Source type  The source type refers to the Eloqua-specific activities, consisting of source type and field name. The sourcetype value is encapsulated by quotation marks only "".
For details, see Activity Fields.  
Interactions 
  • The subject value corresponds to the ContactID, extracted from the respective Eloqua table column name. The subject value is in curly braces {}.
  • The predicate value is a string value, indicating the action completed by the subject.  
    The value is encapsulated in quotation marks only: "" and provided by Digital Experience Cloud. For example, "SubmitForm".
  • The object value is the entity with which the contact interacted. The value is encapsulated in curly braces "{}", for example, "{CampaignName}"
  • The timestamp property value provides the information about the time when the interaction was created. The value is encapsulated in curly braces "{}". For example, "{ActivityDate}"
If you do not want to use the default value of properties, you need to stick to the Eloqua specific property values. For more information, see Activity Fields
Changed since 

The value of this property specifies the starting date and time, from which data is fetched. This value is used only on first data import. For subsequent runs, only data created or modified after the previous successful run is obtained. If the specified string value does not have timezone information, the system uses the local time on the server.

NOTE:We recommend using UTC standard.

Filter 

The filter expression narrows down the results obtained by the data query. The expression contains:  {Eloqua field name} + operator (+-=!, etc.) + 'property value'.

For example, to get all form submits for a particular form, you can use the expression  “{AssetId} = '6'”, instead of “{{Activity.Asset.Id}} = '6'" 

For more information, see Oracle Eloqua documentation: Filtering.
Contact mapping 

The subject mapping queries must provide 2 columns named:

  • Subject1 : ”{Eloqua Contact ID}” – the ID of the contact in Oracle Eloqua
  • Subject2 : “{ContactID}” – the ID of the contact in the second data source  
Contact metadata 
  • The subject value corresponds to the Eloqua Contact ID, extracted from the respective Eloqua table field name. The subject value is in curly braces “{ }”.
  • All fields values are fetched from the respective Eloqua fields for this specific Eloqua Contact ID. 

NOTE: In case the Oracle Eloqua field names are too long, you can specify an alias to use instead. For example, "Email" : "{Email Address}" 

 

 

Was this article helpful?