Dear Lucy supports integration to MySQL, Microsoft SQL Server and Microsoft SQL Azure
In order to use Dear Lucy's sales dashboards through a database integration, all you need to do is to structure your sales data in a specific format (see example in attachment) and authenticate access for Dear Lucy to your database.
The data structure has been developed so that it works seamlessly with Dear Lucy's sales dashboards. In other words, providing Dear Lucy with the data in this format will provide you with 25+ sales KPIs in 5 different sales dashboards almost instantly.
There are three main concepts in an integration: fetching the initial data, mapping it to the correct schema in Dear Lucy and keeping it up-to-date.
Each row in a database is an independent unit of data, other rows do not affect how it is interpreted.
Initial data is fetched from the beginning of the previous year up to the current date. There should be a way to retrieve this range of data when needed, eg. if there have been significant changes to the contents of the data that require all of it to be re-imported.
Updates to Existing Data
Each unit of data should have a unique ID so that it can be updated with new information when needed. There should be a method to fetch the latest changes to data.
Deletion of Existing Data
Deleted data in the source system should also be marked as deleted in the transferred data. This should use the same unique ID which is used to update existing data.
|id text PRIMARY KEY,||active boolean,|
|close_date date,||business_unit text,|
|expected_value real,||competitor text,|
|name text,||country text,|
|open_date date,||customer text,|
|probability real,||customer_type text,|
|state text (funnel state),||deal_type text,|
|status text (won, lost, open),||lead_source text,|
|value real,||product text,|
|sales_person text,||region text,|
|updated_at timestamp,||sales_person text,|
|lost_reason text||state_change_date date,|
|updated_at timestamp without time zone,|
|id text PRIMARY KEY,||company text,|
|active boolean,||owner_id text,|
|completed_date date,||source text,|
|created_date date,||business_unit text,|
|date date,||category text,|
|description text,||contact text,|
Integrate your database with Dear Lucy
Once your sales data is structured in the required format (see format example in attachment) you'll need to integrate it to your dashboards to see your data. The process requires input from Dear Lucy. Please follow the steps below and see the next article in this section to complete the integration:
1. Open up your database to the following IP addresses: 126.96.36.199, 188.8.131.52, 184.108.40.206 and 220.127.116.11. The Dear Lucy integration service runs in AWS.
2. Dear Lucy will provide you with a link within your Dear Lucy environment which allows you to authenticate the database connection.
3. Add the Database credentials: i. Host, ii. Username and iii. Password
4. Once authenticated, Dear Lucy will receive a notification that your database has been authenticated. As a final step, you need to send the name of the SQL view / table to Dear Lucy, you can send this to firstname.lastname@example.org.