​

Configuring Clínica nas Nuvens as a Source

​

1. Add account access

• Client ID: The client ID to authenticate against the Clínica nas Nuvens API.
• Client Secret: The client secret to authenticate against the Clínica nas Nuvens API.
• Token: The token to authenticate against the Clínica nas Nuvens API.
• Start Date: The earliest date from which records will be synced. This is used for date-based streams like Agenda (appointments).

​

2. Select streams

Tip: The stream can be found more easily by typing its name.

​

3. Configure data streams

• Layer: choose between the existing layers on your catalog. This is where you will find your new extracted tables as the extraction runs successfully.
• Folder: a folder can be created inside the selected layer to group all tables being created from this new data source.
• Table name: we suggest a name, but feel free to customize it. You have the option to add a prefix to all tables at once and make this process faster!
• Sync Type: FULL_TABLE sync is used for all streams.

​

4. Configure data source

• Configure Delta Log Retention and determine for how long we should store old states of this table as it gets updated. Read more about this resource here.
• Determine when to execute an Additional Full Sync. This will complement the incremental data extractions, ensuring that your data is completely synchronized with your source every once in a while.

​

5. Check your new source

​

Streams and Fields

​

Data Model

​

Use Cases for Data Analysis

​

1. Appointment Volume Analysis

• Understand daily and weekly appointment patterns
• Identify peak hours and days for resource planning
• Monitor appointment status distribution

WITH appointment_metrics AS (
    SELECT
        DATE(a.data) AS appointment_date,
        DATE_FORMAT(DATE(a.data), '%W') AS day_of_week,
        a.status,
        COUNT(*) AS total_appointments,
        COUNT(DISTINCT a.idPaciente) AS unique_patients
    FROM
        nekt_raw.clinica_nas_nuvens_agenda a
    WHERE
        DATE(a.data) >= CURRENT_DATE - INTERVAL '30' DAY
    GROUP BY
        DATE(a.data),
        DATE_FORMAT(DATE(a.data), '%W'),
        a.status
)
SELECT
    appointment_date,
    day_of_week,
    status,
    total_appointments,
    unique_patients
FROM
    appointment_metrics
ORDER BY
    appointment_date DESC,
    total_appointments DESC

WITH appointment_metrics AS (
    SELECT
        DATE(a.data) AS appointment_date,
        FORMAT_DATE('%A', DATE(a.data)) AS day_of_week,
        a.status,
        COUNT(*) AS total_appointments,
        COUNT(DISTINCT a.idPaciente) AS unique_patients
    FROM
        `nekt_raw.clinica_nas_nuvens_agenda` a
    WHERE
        DATE(a.data) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    GROUP BY
        1, 2, 3
)
SELECT
    appointment_date,
    day_of_week,
    status,
    total_appointments,
    unique_patients
FROM
    appointment_metrics
ORDER BY
    appointment_date DESC,
    total_appointments DESC

​

2. Patient Origin Analysis

• Identify most effective patient acquisition channels
• Measure ROI of marketing initiatives
• Optimize patient acquisition strategy

SELECT
    o.nome AS origem,
    COUNT(DISTINCT p.id) AS total_patients,
    COUNT(DISTINCT CASE WHEN p.ativo = true THEN p.id END) AS active_patients,
    ROUND(
        COUNT(DISTINCT CASE WHEN p.ativo = true THEN p.id END) * 100.0 / 
        NULLIF(COUNT(DISTINCT p.id), 0), 
        1
    ) AS active_rate_pct
FROM
    nekt_raw.clinica_nas_nuvens_pacientes p
    LEFT JOIN nekt_raw.clinica_nas_nuvens_origem o ON p.idOrigem = o.id
GROUP BY
    o.nome
ORDER BY
    total_patients DESC

SELECT
    o.nome AS origem,
    COUNT(DISTINCT p.id) AS total_patients,
    COUNT(DISTINCT CASE WHEN p.ativo = true THEN p.id END) AS active_patients,
    ROUND(
        SAFE_DIVIDE(
            COUNT(DISTINCT CASE WHEN p.ativo = true THEN p.id END) * 100.0,
            COUNT(DISTINCT p.id)
        ), 
        1
    ) AS active_rate_pct
FROM
    `nekt_raw.clinica_nas_nuvens_pacientes` p
    LEFT JOIN `nekt_raw.clinica_nas_nuvens_origem` o ON p.idOrigem = o.id
GROUP BY
    o.nome
ORDER BY
    total_patients DESC

​

3. Procedure and Specialty Analysis

• Identify most popular procedures
• Understand specialty distribution
• Plan resource allocation based on demand

WITH procedure_data AS (
    SELECT
        p.id AS procedure_id,
        p.nome AS procedure_name,
        p.idEspecialidade AS specialty_id,
        p.quantidade AS quantity,
        a.id AS appointment_id,
        a.data AS appointment_date
    FROM
        nekt_raw.clinica_nas_nuvens_agenda a
        CROSS JOIN UNNEST(a.procedimentos) AS t(p)
    WHERE
        DATE(a.data) >= CURRENT_DATE - INTERVAL '30' DAY
)
SELECT
    e.nome AS specialty,
    tp.nome AS procedure_type,
    COUNT(DISTINCT pd.appointment_id) AS total_appointments,
    SUM(pd.quantity) AS total_procedures
FROM
    procedure_data pd
    LEFT JOIN nekt_raw.clinica_nas_nuvens_especialidades e ON pd.specialty_id = e.id
    LEFT JOIN nekt_raw.clinica_nas_nuvens_tipo_procedimento tp ON pd.procedure_id = tp.id
GROUP BY
    e.nome,
    tp.nome
ORDER BY
    total_appointments DESC

WITH procedure_data AS (
    SELECT
        p.id AS procedure_id,
        p.nome AS procedure_name,
        p.idEspecialidade AS specialty_id,
        p.quantidade AS quantity,
        a.id AS appointment_id,
        a.data AS appointment_date
    FROM
        `nekt_raw.clinica_nas_nuvens_agenda` a,
        UNNEST(a.procedimentos) AS p
    WHERE
        DATE(a.data) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
    e.nome AS specialty,
    tp.nome AS procedure_type,
    COUNT(DISTINCT pd.appointment_id) AS total_appointments,
    SUM(pd.quantity) AS total_procedures
FROM
    procedure_data pd
    LEFT JOIN `nekt_raw.clinica_nas_nuvens_especialidades` e ON pd.specialty_id = e.id
    LEFT JOIN `nekt_raw.clinica_nas_nuvens_tipo_procedimento` tp ON pd.procedure_id = tp.id
GROUP BY
    e.nome,
    tp.nome
ORDER BY
    total_appointments DESC

​

Implementation Notes

​

Data Quality Considerations

• The Agenda stream uses the start_date configuration to filter appointments by date
• Patient records may have null values for optional contact and address fields
• Use the lookup tables (Cidades, Especialidades, etc.) to enrich your analyses with descriptive names

​

Best Practices

• Always join with lookup tables to get descriptive names instead of just IDs
• Consider patient privacy when analyzing personal data