Automated Import - WIP

Owned by PeterF (Deactivated)
Sept 06, 2016

Noojee Campaigner NJC provides a number of methods to automate the import of campaign lists from an external system such as your companies Customer Relationship Management System (CRM).

SQL

Importing a campaign list using SQL is probably the easiest method of integrating NJC with your CRM.

To implement SQL Importing from your CRM into NJC we need the following:

  • Type of SQL Server (e.g. MySQL, Microsoft SQL, Web Service).
  • IP address/hostname of the SQL server
  • Database Schema to to be used
  • Username for access to the SQL server which has appropriate rights to execute the supplied SQL queries
  • Password for the above username
  • An SQL query that returns a list of 'campaigns' that are available for import.
  • An SQL query that returns a list of 'leads' for a given campaign.

When a NJC administrator goes to import a campaign NJC will first run the above 'campaigns' SQL query and display the results in a drop list.

The NJC administrator will then select one of the campaigns from the list and click the import button. NJC will then run the second query passing the unique id of the select campaign.

For example the two queries might look something like follows:

select name, id from tblCampaign;
select phone, firstname, surname, street, suburb, postcode from tblLeads where campaignid = <id>

At run time we would substitute <id> with the 'id' from the campaign selected by the user.

Campaign query

The campaign query MUST return two fields.

  • The first field MUST be a unique name for the campaign.
  • The second field MUST be a unique id for the campaign.

The Campaign name will be displayed to the user in a drop list. The id will be used in a filter (where) clause on the list query.

Ideally the query should be wrapped in a stored procedure as this will isolate NJC from any schema changes that may occur within your CRM.

List query

The second query would then return a row for each lead in the list. The 'lead' must return at least one field which must have a field name of 'phone' which MUST contain the phone number to be dialed. The phone field must be a full 10 digit phone number. The query may return any number of additional fields. Each field MUST have a unique field name.

For repeated imports, the set of fields returned by the query MUST match the full set of fields defined in for the Campaign Type.

Ideally the query should be wrapped in a stored procedure as this will isolate NJC from any schema changes that may occur within your CRM.