Sending Form Submission Data from OE to Celonis

See how you can transfer form submission data from OE to Celonis using dedicated extractors.

You can manage importing form submission data into Celonis through Form Extractor with different kinds of filtering. Filtering might include form IDs that enable precise data retrieval, or employing the extractor to handle submissions from all form types.

Prerequisite

To extract the data from OE to Celonis, you have to create a Data Pool in your Celonis account. Celonis uses Data Pools to collect and cluster data information to set up an integration flow. To learn about the details, see the Celonis Data Pools documentation.

Extracting data from Form submissions

Creating a data connection

You can create your own data extractor based on an Emporix template. The steps below show how to work with the extractor template and how to use it for your custom configuration.

Start with creating a connection to your Celonis Data Pool that links your OE as the Data Source:

1. In your Celonis account, go to Data Integration -> Your Data Pool -> Data Connections and choose Add Data Connection.

2. Choose Connect to Data Source.

1. Go to the Custom section and choose Build custom connection -> Import from file.

2. Enter the Name for your custom extractor. Optionally, you can also add a Description for it.

3. Download the Forms Extractor and then upload it as a JSON file in your Celonis extractor builder:

13KB

Form_Extractor.json

1. Choose Save and Continue.

2. Check the parameters that should be used for the connection and then continue to the next step. The parameters were configured automatically with the uploaded extractor JSON file.

1. Check the authentication method; for Emporix it's the OAuth2 (Client Credentials) method with the following endpoint and type details:

   • Get Token Endpoint: {Connection.AUTH_URL}/oauth/token

   • Grant Type: client_credentials Don't modify this configuration. Continue to the next step to finish the process.

At the end you can see the Defined Endpoints.

1. Having configured the data connection, we now need to establish the connection with Emporix system. Go to the Data Connections list again and choose Add Data Connection -> Connect to Data Source -> Custom. The data connection you created with the form extractor is visible there, under Custom connections.

2. Choose your connection and check its configuration details to make sure all the authorization details like Client ID or Client Secret are added. Additionally, provide the form slug information to configure the right data filtering. Save your changes.

• You can find the Client ID and Client Secret in Emporix Developer Portal - Manage API Keys.
• The slug details can be checked in two ways:

  • within the Management Dashboard in the Forms view

  

  • with an API call, for example by calling the Get a Form Make module.

1. When the connection to Emporix is established, you can now configure the responses that you get. In the endpoints configuration, choose the Form endpoint to check and customize its configuration.

The Configure Response that is visible in the endpoint configuration can be done automatically.

Use the Configure response using samples from your source system option to generate the example from the source system. Expand the field -> Choose Use existing Data Source -> Choose your data source -> Choose Build Response. This autogenerates the response configuration, which you can then see in the JSON response.

1. Similarly as for the Forms endpoint, choose the Form Submission endpoint to check and customize its configuration.

The Configure Response that is visible in the endpoint configuration can be done automatically.

Make sure the id is checked as a Primary Key.

Use the Configure response using samples from your source system option to generate the example from the source system. Expand the field -> Choose Use existing Data Source -> Choose your data source -> Choose Build Response.

This autogenerates the response configuration. You can check that the JSON was updated with submission data configuration, for example:

"data" : {
    "visiting" : [
        0
    ],
    "select" : 0,
    "starship" : 0,
    "requestPermissionToLand" : false,
    "shipCategory" : "",
    "doomed" : false,
    "_magicLinkCustomAttributes" : {
        "planet" : [
        {
            "name" : "",
            "residents" : [
            {
            "id" : "",
            "name" : ""
            }
            ]
        }
        ]
    }
}

1. Choose Finish to save your connection configuration.

Data jobs

With the forms extractor, you can configure the data jobs to either upload data related to form submissions only, or to form submissions with magic links.

Data jobs for form submissions

1. To start with the data job configuration, in your Data Pool go to Data Jobs and choose Add Data Job. Add a name for the Data Job and choose the Data Connection you created for the forms.

For example, a data job named Forms extraction and a related orbit-control extraction to it.

After creating the data job, you can add the forms extractions. 2. Choose Add in the Extractions section, add a Name for the extraction and save it. 3. Choose Add Extraction in the Table Configuration. 4. Select form from the list of available tables. Save the selection.

As a result, you should see the form table added to the extraction.

1. Go to Extraction Settings, choose option B as the load configuration and save it.

1. Go back to the Data Jobs view. Choose Execute Data Job. This starts a new task and populates a schema that is later visible in the Transformations. To check logs after a job execution, go to Logs tab and select the execution task that you want to view. You can see all the details related to the executed job.

1. Go back to the Data Jobs view and choose Add in the Transformations section. Enter a name for the transformation and save it. The schema is automatically uploaded.

You can now add Transformation Statements and execute them. For example: select * from "form_submission$data$visiting" or select * from "form_submission".

Whenever a transformation is executed you can see the Output of the job.

Data jobs for form submissions with magic links

The forms extractor includes magic links extractor, which enhances its functionality. Using the extractor, you can extract form submissions and also track who has been sent a form, regardless of whether they have completed or submitted it. By specifying the form type, the extractor loads relevant magic links, providing visibility into who has received the form, not just the responses they have submitted.

To have magic links data included in the extraction:

1. Choose Add in the Extractions section, add a Name for the extraction and save it.

2. Choose Add Extraction in the Table Configuration.

3. Select form from the list of available tables. Save the selection.

As a result, you should see the form table added to the extraction. 4. Go to Extraction Settings, choose option B as the load configuration and save it.

1. Go back to the Data Jobs view. Choose Execute Data Job. This starts a new task and populates a schema that is later visible in the Transformations. To check logs after a job execution, go to Logs tab and select the execution task that you want to view. You can see all the details related to the executed job.

1. Go back to the Data Jobs view and choose Add in the Transformations section. Enter a name for the transformation and save it. The schema is automatically uploaded.

You can now add Transformation Statement and execute it:

    create table if not exists magic_link_submissions (
    magic_link_id varchar2(26),
    num_submissions integer,
    PRIMARY KEY(magic_link_id) 
    );


    DROP TABLE IF EXISTS magic_link_submissions_new;
    create table if not exists magic_link_submissions_new (
    magic_link_id varchar2(26),
    num_submissions integer,
    PRIMARY KEY(magic_link_id) 
    );

    INSERT into magic_link_submissions_new
    select ml.id, count(submissions."submittedAt") 
    from magic_link as ml left outer join "magic_link$submissions" as submissions on submissions."magic_link_id" = ml.id
    group by ml.id;

    merge into magic_link_submissions
    using magic_link_submissions_new
    on magic_link_submissions.magic_link_id = magic_link_submissions_new.magic_link_id
    WHEN NOT MATCHED THEN 
    insert (magic_link_id, num_submissions)
    VALUES (magic_link_submissions_new.magic_link_id, magic_link_submissions_new.num_submissions);

To more details on Data Jobs, how to work with extractions and transformations, see the Celonis Extracting and transforming data documentation.

Data model

With the forms extractor, you can configure the data model to either upload data related to form submissions only, or to form submissions with magic links.

Form submissions

Set up the Data Model to establish relations between all of the extractor's components.

1. Go to Your Extractor -> Data Model and choose Add Data Model.

2. Select forms related items for the table and choose Next.

You can skip the activity table configuration. 3. Set up all the Foreign Keys for your activity table.

For example, set the ID key for the form:

• Choose New foreign key in the form settings.

• Connect the id field from a Dimension table to a form$id in a Fact table.

The next relations to set up are:

• Link form (Dimension table) with form_submission (Fact table) by linking id and form$id.

• Link form (Dimension table) with form$customAttributes (Fact table) by linking id and form_id.

• Link form$customAttributes (Dimension) with form$customAttributes$values (Fact) by linking form_id and form$customAttributes_form_id.

To see an example of a configured data model, check the below sample Data Model:

1. After setting up the relations, go to Data loads tab and choose Load Data Model.

When the load is finished you can check the details of the currently loaded data model.

Form submissions with magic links

Set up the Data Model to establish relations between all of the extractor's components including magic links data.

1. Go to Your Extractor -> Data Model and choose Add Data Model.

2. Select the items related to forms and magic links for the table and choose Next.

You can skip the activity table configuration. 3. Set up all the Foreign Keys for your activity table.

For example, set the ID key for the form:

• Choose New foreign key in the form settings.

• Connect the id field from a Dimension table to a form$id in a Fact table.

The next relations to set up are:

• Link form (Dimension table) with form_submission$data$visiting (Fact table) by linking id and form_submission_id.

• Link form (Dimension table) with form_submission (Fact table) by linking id and form$id.

• Link magic_link (Dimension table) with form_submission (Fact table) by linking id and magicLinkId.

• Link magic_link (Dimension table) with magic_link_submissions (Fact table) by linking id and magic_link_id.

To see an example of a configured data model, check the below sample Data Model:

1. After setting up the relations, go to Data loads tab and choose Load Data Model.

When the load is finished you can check the details of the currently loaded data model.

To learn more about the configuration of Data Models in Celonis, see the Data Model Denifinition documentation.

Executing a data load

Execute a data load based on your form submission configuration and created connection.

1. Go to Data Pools -> Data Jobs and choose Execute Data Job. You can select to execute a Delta or a Full Load. Delta loads only the part of data that changed since the last upload.

2. Choose Execute Selection. The job starts and you can already see the process logs.

When the process is finished, you can check the logs details.

Additional extractor configurations

Delta loads

To enable delta loads submissions, use the submittedAtGte filtering. You can configure the executions to make the submitted at column visible and to upload data only from the specified time.

1. Go to Data Jobs -> Your extraction task -> Table Configuration.

2. Enable the creation date filter.

1. Create a new parameter for the modified time - <%=max_modified_time%>.

   Go to Your Extraction -> Parameters tab and choose New Parameter and provide the following values in the fields:

   • type: dynamic

   • table: form_submission

   • column: submittedAt

   • operation type: FIND_MAX

   • data type: date

   • default date: any date from the past

1. Use the Delta filter statement to define the parameter settings.

Delta loads for magic links

Similar to form submission, you can the Enable change date filter to configure your delta loads for magic links. You can set up a date from when the updated data would be fetched with the updatedAt filtering. You can also configure the executions to make the updated at column visible and to upload data only from the specified time.

1. Go to Data Jobs -> Your extraction task -> Table Configuration.

2. Create a new parameter for the modified time - <%=magic_link_max_modified_time%>.

   Go to Your Extraction -> Parameters tab and choose New Parameter and provide the following values in the fields:

   • type: dynamic

   • table: magic_link

   • column: updatedAt

   • operation type: FIND_MAX

   • data type: date

   • default date: any date from the past

Extracting all submissions of a single form

By default, the forms extractor uploads data from the latest submitted version of a form associated with a given magic link. If users might submit the same form more than once, and you want to upload data covering all of its submitted versions, you can override the default behavior by changing the latest parameter settings.

To change the default setting:

1. Go to the Defined Endpoints and choose the Form Submission endpoint to open its configuration.

1. Go to the Configure Request section and expand Request parameters.

1. Choose to edit the latest parameter and change the value from true to false. By doing this, you make sure that not only the latest submission of a form is uploaded, but all the versions of it submitted by a specific user using a single magic link.

4. Go to **Data Jobs** and execute a task. Afterwards, you can view the execution logs with the updated setup and the number of loaded records.

To learn how to create forms, see the Forms documentation.