Sending Forms Magic Links Data from OE to Celonis

See how you can transfer forms magic links from OE to Celonis using dedicated extractors.

You can manage retrieving data from Emporix OE to track which forms were sent to suppliers or contacts using the Magic Links extractor. The extractor allows for precise data retrieval or comprehensive tracking of all sent forms. This ensures accurate visibility into communication history with your suppliers and contacts.

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 Magic Links data

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.

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

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

5. Download the Magic Link Extractor and then upload it as a JSON file in your Celonis extractor builder:

7KB

magic_link_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.

   
3. 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 endpoint defined for this extractor - Get Magic Links.

   
4. 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. Choose the data connection that you created with the magic links extractor. It's visible there, under Custom connections.

5. Choose your connection and check its configuration to make sure all the authorization details like Client ID or Client Secret are added.

   
6. When the connection to Emporix is established, you can now configure the responses that you get. In the endpoints configuration, choose the Get Magic Links endpoint.

   

   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 view in the JSON below.

   

   You can check that the JSON was updated with magic links configuration, for example:

   {
   "id" : "",
   "name" : "",
   "expirationDate" : "",
   "stylesheet" : "",
   "submissionEventType" : "",
   "canBeSubmittedOnlyOnce" : false,
   "instanceId" : "",
   "scheduleId" : "",
   "createdAt" : "",
   "updatedAt" : "",
   "recipient" : {
     "company" : {
     "id" : "",
     "name" : ""
     },
     "contacts" : [
     {
         "id" : "",
         "name" : "",
         "value" : "",
         "language" : ""
     }
     ]
   },
   "mixins" : {
     "topicsNames" : "",
     "component" : {
     "escalated" : "",
     "name" : ""
     },
     "internalContact" : {
     "email" : "",
     "name" : "",
     "preferredLanguage" : ""
     },
     "internalContact2" : {
     "email" : "",
     "name" : "",
     "preferredLanguage" : ""
     }
   },
   "forms" : [
     {
     "id" : ""
     }
   ],
   "submissions" : [
     {
     "submittedAt" : ""
     }
   ],
   "customAttributes" : [
     {
     "name" : "",
     "values" : [
         ""
     ]
     }
   ]
   }

Make sure the id is checked as a Primary Key.

1. Choose Finish to save your connection configuration.

Data jobs

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 magic links.

   For example, a data job named Load Magic Links and a related Magic Link Extractor 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 magic_link from the list of available tables. Save the selection.

   

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

   
5. 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.

6. Go back to the Data Jobs view and choose and Add two transformations in the Transformations section.

   • view the magic links transformation - with select * from "magic_link" as the transformation statement

   

   • create submission table transformation - with the following statement:

    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);

   

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

Data model

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 magic_link 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 magic_link.

   • Choose New foreign key in the form settings.

   

Set up the relations:

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

• Link magic_link_submissions (Dimension) with form_submission (Fact) by linking magic_link_id and magicLinkId.

• Link form_submission (Dimension) with form_submission$data$visiting (Fact) by linking id and form_submission_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.

Restricting initial loads

Use the Enable creation date filter to set up a date from which the data should be loaded. This is used for setting the timestamp for the initial load and late in the delta loads as well.

To enable set up the initial load, use the createdAtGte filtering.

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

2. Enable the Creation date filter and select the createdAtGte filter.

3. Set up the custom date and save your configuration.

Delta loads

Use the Enable change date filter to configure your delta loads. You can set up a date from when the updated data would be fetched with the updatedAtGte 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. Enable the Change 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: magic_link

   • column: updatedAt

   • 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 - updatedAtGte = <%=max_modified_time%>.