Smart Connector - Delivery Types overview

What are Smart Connector Delivery types?

When connecting your data using a Smart Connector, one of the first steps is to choose where TapClicks should look to find your data. The Delivery Type will tell the system the location of your files. 

 

There are four broad methods of getting data into TapClicks using a Smart Connector, each which consist of several Delivery Types. The methods are:

1. Manual
2. Database
3. Hosted
4. Direct To Service

Each method has several delivery Types associated with them, which are found in the Delivery Type dropdown in the application. There are shown here:

As of Jan 2021

 

Manual Delivery Types

The most common method for adding data to TapClicks is using the Manual File Upload. This is when you add files directly to the system. For example, if you have a .csv file located on your computer and want to have that data show up in your TapClicks marketing database or in reports, you can simply find the file on your computer and add it directly. 

Another Manual Delivery Type is called Email. When using the email feature, you can send files directly to TapClicks using a special email address generated by the system. When you toggle the Use Email Service button, you'll be provided an email address. Simply send your data as an attachment to that email address, and the system will add it to your database. 

 

Example of Email Delivery Method

 

Database Delivery Types

Many teams store their marketing, operations, and sales data in a database. These are used by Business Intelligence teams to create analysis and insights. The Smart Connector allows you to access these databases directly and pull that data into the system for use in your reports. 

The TapClicks system will both a credential to access the database as well as a description of where to look for the data you'd like to pull in. To accomplish this, you will need to provide login instructions and then write a query. 

These query instructions vary based on the database you are accessing. You can see in the example below that when you want to access a file using FTP, you'll provide your credentials and additional details for the TapClicks system to use. 

Often you may need to get these credentials from another team or resource in your org. 

 

FTP Delivery Type example credentials

 

 Hosted Delivery Types

Another very common Delivery Type is used when you have your files located in a folder. In this case, you want to have the system check that folder location for new data every day. When new files are added, the data in them will be brought into TapClicks (provided it is formatted in the same way as you mapped the sample data during Smart Connector setup).

Some common examples of Hosted folder locations are: Google Drive, DropBox, and Google Sheets. 

Similar to a Database Delivery Type, you will need to provide credentials and instructions on where the system should look. When using a Google Sheet, you will also need to provide additional details about where in the document the system should look to find the data you want to pull in. 

The first step is always to authenticate your connection. 

 

Example of Hosted Delivery Type using Dropbox

 

 

Direct-to-Service Delivery Types

In TapClicks terminology, a "Service" often means an application or Data Source. So a "Direct-to-Service" Delivery Type means the system is connecting directly to an internal or third party application. 

One of the Direct-to-Service Delivery Types is called TapAccess. Conventionally, you may also hear this referred to as "Data Sharing". TapAccess allows you to share data between TapClicks instances. In this case, the "service" is actually another instance. Not all TapClicks accounts or users have this feature enabled. 

To setup a TapAccess connection, you will be asked to provide the providing instance details and then authenticate your ownership of that account. There are additional steps involving creation and management of data profiles to ensure only the desired data is shared.

This solution is often used for managing client accounts.

 

TapAccess is one of the Direct-To-Service Delivery Types

It shares data between instances

 

 

 

Will the system look for new data automatically?

With the exception of the Manual Delivery Types, once you establish a connection to the location where your data is kept and turn your Smart Connector on, the system will automatically check that location for new data on a daily basis.

 

 

 

 

 

 

Step-by-Step instructions for Database Delivery Types

 

 

MySQL Connection

The MySQL connection using the Smart Connector enables a new source of data in the system. Data is fetched based on a predefined SQL query and would work similar to a MySQL workbench , allowing only data read operation. The requirements and functionality is similar to  other Smart Connector Connection Type / Delivery Type options.

To Create a Smart Connector:

1. Open TapClicks and in the left pane, click Administrator.
2. Click Smart Connector under Data Settings.
3. Click the  icon next to Smart Connector on the top of the screen.
4. Enter a name and select MySQL from the Delivery type dropdown.

5. After selecting the MySQL, the user will be prompted to connect to the database using credentials and other necessary information. Please enter the information as given below.  

MySQL Host	IP address of the host on which the database is running / hosted
Port	Port number on which the database can be accessed | Default : 3306 for MySQL
Database Name	Name of database from which data has to be fetched
Username	Username used to access the database
Password	Password used to access the database

 

Only MYSQL Databases for which external connections over TCP/IP are allowed can be connected. The server on which the database rests must allow external connection.

6. Before creating an actual connection, check if the database is accessible by clicking test connection. Once the connection is established, the check sign on the test connection turns green.

7. If the connection is unsuccessful, an error message appears on the screen. Some common things to check if this occurs are:

• Required Fields: Host, Username, Password, Database Name, Port Number
• Permissions: Check if you have the necessary permissions to access the host on which the Database rests

8. After you enter the information for Smart Connector, fields to create a Query request becomes visible. You need to create a Query request to get the required data from the remote MySQL Database. Smart Connectors support both basic and advanced READ queries, like SELECT and CALL (this procedure is permitted, but please inform your Customer Success Manager if you intend to use this command to ensure that the Smart Connector meets this advanced use case).

 

Note: Only columns selected in the query will be fetched by the Smart Connector. Therefore, users need to make sure they are including all necessary columns in the query.

9. The only commands that are not supported include INSERT, UPDATE and DELETE. In order to achieve memory optimization, the system will automatically paginate the data in batches of rows. Adding a LIMIT at the end of your query will prompt the system to have that many number of rows in the batch. However, because the order in which the rows are fetched from the database could vary from query to query, it’s best to add in your SQL statement an ORDER BY to ensure the data integrity. For example : SELECT campaign_name, calls, leads, date_created FROM my_campaigns WHERE `date_created` BETWEEN '%START_DATE%' AND '%END_DATE%'", we would suggest you to update it that way : "SELECT campaign_name, call, leads, date_created FROM my_campaigns WHERE `date_created` BETWEEN '%START_DATE%' AND '%END_DATE%' ORDER BY id

10. Data types of all columns are detected automatically. It is recommended that users check all the data types before saving the configuration to avoid incorrect data types. Please see the Field Type column in the image below:

 

For example: Auto detection may select a column with number based on what is in that first row of data but that might not be how you intend the field to be used. Id - as data type Number since it comes through as a 3 in the sample file, but it should be stored as Text as no mathematical operations such as Sum/Average is expected on an Id.

11. Frequency of fetch

• Historic Fetch and Daily fetches.
  • Full: data within the last 6 months is fetched
  • Partial: data within the last 3 days is fetched.
    This is consistent with all Smart Connector delivery type options.

Note: This will depend on whether your Smart Connector is configured to fetch data based on Date or based on the Last Modified Date.

12. Data Update

If certain fields in the query needs to be modified (unique fields, dates, and assigning configurations), the user would need to delete the data loaded to date through this Smart Connector before making the modifications.  This is done through the Delete which is placed next to the Smart Connector names in the Smart Connector list.

However, a user can include additional fields on the query similar to if we were to add a new field from a .csv file to a Smart Connector. User can do this without deleting the previously loaded data.  Standard partial / full fetch apply to existing and new assigning in regards to filling the database with these new fields.  This functionality is in line with all other Connection Type / Delivery Type options in the system today.

After creating the Smart Connector, users will have to map their Smart Connector data to clients in TapAnalytics and click Update Dashboard before the data starts pouring in the system.

 

 

 

Salesforce SOQL Connections

Salesforce SOQL  Smart Connector enables you to integrate data from Salesforce SOQL in  TapClicks platform.  

To Create a Salesforce SOQL  Smart Connector,

1. Open TapClicks and in the left pane, click Administrator.
2. Click Smart Connector under Data Settings.
3. Click the icon next to Smart Connector on the top of the screen.
4. Enter a name and select Salesforce SOQL from the Delivery type drop down.   Note: The Salesforce Object Query Language (SOQL) can be used to search organization’s Salesforce data for specific information. SOQL is similar to the SELECT statement in the widely used Structured Query Language (SQL) but is designed specifically for Salesforce data.
5. After selecting the Salesforce SOQL option, the users will be redirected to the Salesforce login screen to authenticate the connection.
6. Enter your valid Salesforce credentials and click Log In. If you are unable to connect, please contact your Account Manager.
7. After entering the information for Smart Connector, fields to create a Query request becomes visible. You need to create a Query request to get the required data from the remote MySQL Database. Smart Connectors support only READ queries, like queries with SELECT.     Note: Only columns selected in the query will be fetched by the Smart Connectors so users need to make sure that they are including all necessary columns in the query.
8. In order to achieve memory optimization , the system will automatically paginate the data in batches of rows. Adding a LIMIT at the end of your query will prompt the system to have that many number of rows in the batch. However, because the order in which the rows are fetched from the database could vary from query to query, it’s best to add in your SQL statement in ORDER BY to ensure the data integrity.
   For example,
   Select Name FROM Account WHERE Industry = Media ORDER BY Account.Name LIMIT10000
9. Data types of all columns are detected automatically. It is recommended that users should check all the data types before saving the configuration to avoid incorrect data types. Please see the  Field Type column in the image below: Note: Auto detection may select a column with number based on what is in that first row of data but that might not be how you intend the field to be used. For example, Id - as data type Number since it comes through as a 3 in the sample file, but it should be stored as Text as no mathematical operations such as  Sum/Average is expected on an Id.
10. Frequency of fetch
    • Historic and Daily fetches.
      • Full: data within the last 6 months is fetched
      • Partial: data within the last 3 days is fetched
    • This is consistent with all Smart Connector delivery type options.

Note: This will depend on whether your Smart Connector is configured to fetch data based on Date or based on Last Modified Date

11. Data Update
    If certain fields in the query needs to be modified (unique fields, dates, and assigning configurations), the user would need to delete the data loaded to date through this Smart Connector before making the modifications. This can be done by clicking Delete, which is next to the Smart Connector names in the Smart Connector list.
    
    However, a user can include additional fields on the query similar to if we were to add a new field from a .csv file to a  Smart Connector. User can do this without deleting the previously loaded data.
    Partial / full fetch apply to existing and new assigns in regards to filling the database with these new fields.  This functionality is in line with all other Connection Type / Delivery Type options in the system today.

After creating the Smart Connector, users will have to map their Smart Connector data to clients in TapAnalytics and click Update Dashboard before the data starts getting in the system.

 

 

 

PostgreSQL Connection

The PostgreSQL connection using the Smart Connector enables a new source of data in the system. Data is fetched based on a predefined SQL query and would work similar to a pgAdmin SQL client but allowing only data read operation. The requirements and functionality is similar to other Smart Connector Connection Type / Delivery Type options.

To Create a PostgreSQL Smart Connector,

1. Open TapClicks and in the left pane, click Administrator.
2. Click Smart Connector under Data Settings.
3. Click the  icon next to Smart Connector on the top of the screen.
4. Enter a name and select PostgreSQL from the Delivery type drop down.

5. After selecting the PostgreSQL option, the user will be prompted to connect to the database using credentials and other necessary information. Please enter the information as given in the table below

PostgreSQL Host	IP address of the host on which the database is running / hosted
Port	Port number on which the database can be accessed | Default Port : 5432
Database Name	Name of database from which data has to be fetched
Username	Username used to access the database
Password	Password used to access the database

Note: Only Postgresql Databases for which external connections over TCP/IP are allowed can be connected. The server on which the database rests must allow external connection

6. Before creating an actual connection, check if the database is accessible by clicking test connection. Once the connection is established, the check sign on the test connection turns green.

Unverified Connection ​

Verified Connection ( Verified by the Green tick next to the TEST CONNECTION Button)

If the connection is unsuccessful, an error message will appear on the top-right corner.

7. In case of unsuccessful connection, some common things to check:

• Required Fields: Host Address, Port Name, Database Name, Username, Password
• Permissions on your host to access the database externally from other sources

8. After you enter the information for Smart Connector, fields to create a Query request becomes visible. You need to create a Query request to get the required data from the remote PostgreSQL Database. Smart Connectors support both basic and advanced READ queries using SELECT statement.

• SELECT
• CALL (this procedure is permitted, but please inform your Customer Success Manager if you intend to use this command to ensure that the Smart Connector meets this advanced use case)

Note: Only columns selected in the query will be fetched by the Smart Connector. Therefore, users need to make sure they are including all necessary columns in the query.

10.The only commands that are not supported include INSERT , UPDATE and DELETE. In order to achieve memory optimization, the system will automatically paginate the data in batches of rows. Adding a LIMIT at the end of your query will prompt the system to have that many number of rows in the batch. However, because the order in which the rows are fetched from the database could vary from query to query, it’s best to add in your SQL statement an ORDER BY to ensure the data integrity.

For example: SELECT * FROM customer_value WHERE (effective_to_date BETWEEN '%START_DATE%' AND '%END_DATE%') OR 1=1 ORDER BY effective_to_date

11. Data types of all columns are detected automatically. It is recommended that users check all the data types before saving the configuration to avoid incorrect data types. Please see the Field Type column in the image below:

For example, Auto detection may select a column with number based on what is in that first row of data but that might not be how you intend the field to be used.   For example: Id - as data type Number since it comes through as a 3 in the sample file, but it should be stored as Text as no mathematical operations such as Sum/Average is expected on a Id

12. Frequency of fetch

• Historic fetch and Daily fetches.
  • Full: data within the last 6 months is fetched
  • Partial: data within the last 3 days is fetched  

Note : This will depend on whether your Smart Connector is configured to fetch data based on Date or based on Last Modified Date.

13. Data Update

If certain fields in the query needs to be modified (unique fields, dates, and assigning configurations), the user would need to delete the data loaded to date through this Smart Connector before making the modifications. This can be done by clicking  Delete , which is next to the Smart Connector names in the Smart Connector list.

However, a user can include additional fields on the query similar to if we were to add a new field from a .csv file to a Smart Connector. User can do this without deleting the previously loaded data.  

Partial / full fetch apply to existing and new assigning in regards to filling the database with these new fields. This functionality is in line with all other Connection Type / Delivery Type options in the system today.

After creating the Smart Connector, users will have to map their Smart Connector data to clients in TapAnalytics and click Update Dashboard before the data starts getting in the system.