Database integration for Sales & Sales Activities dashboards – Dear Lucy

Dear Lucy supports integration to MySQL, Microsoft SQL Server, Microsoft SQL Azure and PostgreSQL

Screenshot 2024-03-21 at 13.57.05.png

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 hundreds of sales and sales activities KPIs in different sales dashboards almost instantly.

Data Requirements

General Concepts

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.

Data Interpretation

Each row in a database is an independent unit of data, other rows do not affect how it is interpreted.

Initial Data

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.

Schemas

Sales Cases

Mandatory	Optional
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,
	state_change_date date,
	team text,
	updated_at timestamp without time zone,
	lost_reason text,
	mrr value real

Sales Activites

Mandatory	Optional
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,
owner text,
type 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. 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: 34.255.17.121, 34.246.213.238, 34.247.172.200, 34.247.65.152 and 52.19.91.29 The Dear Lucy integration service runs in AWS.

2. Within your Dear Lucy account, access the Settings menu (the cogwheel) and navigate to Integrations > + Add New Integration.

3. Scroll down to the list of SQL server icons and select the Server in which your Database is located by clicking the Connect button that appears when holding your mouse over the icon.

4. When clicking Connect, a pop-up window will appear (see image) asking you to add the following credentials to access the database:

i. Host: This is the URL or IP address where the database is located.

ii. Username: The username that can access the database. In most cases a new user will be made for Dear Lucy that have the necessary access and privileges to the database and tables.

iii. Password: This is the password for the User that have access to the database.

iv. Database: This is the name of the database within the server where the Sales_cases and Sales_activities tables are located. Often, a database for integrations may already be in place, called e.g. "Shared" or similar. If not, we recommend creating a database for "Dearlucy".

v. Table: The name of the table that has the sales or sales activities data. We recommend naming the tables Sales_cases and Sales_activities,

5. Press Connect to authenticate and complete the integration and Sales or Sales activities dashboards will start working instantly.

Please note that you are only able to integrate one table at a time. In other words, if you have integrated your Sales_cases table, you need to repeat the steps above if you also wish to integrate Sales_activities.

Press here to read more about Dear Lucy's pre-built sales dashboards.