RD Station as a data source

Bring data from RD Station to your Lakehouse.

RD Station is a marketing automation and lead generation platform that helps businesses attract, convert, and nurture leads. It provides tools for email marketing, landing page creation, lead scoring, and marketing analytics to improve conversion rates and customer acquisition.

Configuring RD Station as a Source

In the Sources tab, click on the “Add source” button located on the top right of your screen. Then, select the RD Station option from the list of connectors.

Click Next and you’ll be prompted to add your access.

1. Add account access

1. Click Next and you’ll be prompted to login with your account to grant permissions for Nekt to extract data from your RD Station Marketing account.

2. After successful authentication, you’ll be prompted to configure additional settings:

   • Start Date: The earliest record date to sync. This defines the starting point for data extraction - only records created or modified after this date will be synchronized.

   • Enable Contact Custom Fields: Define whether the user should have access to details about contacts or not. Should be used with caution since the extraction time can increase significantly when enabled, as it requires an additional request for each contact.

   • Contacts Extraction Mode: Define which segmentation lists the connector should extract. Each segmentation will be extracted to a separate table in your Lakehouse.

     • Entire Lead Base: Extract all contacts from your account.
     • Segmentation Lists: Extract only contacts from specific segmentation lists.
     • Entire Lead Base and Segmentation Lists: Extract contacts from both the entire base and from specified segmentation lists.
   Please note contacts who are in different segmentation lists may be extracted multiple times.

   • Segmentation List IDs: The IDs of the segmentation lists to extract contacts from if you want to filter only specific lists to speed up extraction. If not provided, contacts from all segmentation lists will be extracted.

3. Click Next.

2. Select streams

1. The next step is letting us know which streams you want to bring. You can select entire groups of streams or only a subset of them.

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

   The analytics_funnel stream is only available for RD accounts with access to the Advanced plan.

   The analytics_email_stats, analytics_workflow_email_stats and analytics_conversion_assets streams are only available for RD accounts with access to the Professional plan.

   The contact_events and contact_details streams are only available if you extract the entire lead base, either by selecting Entire Lead Base or Entire Lead Base and Segmentation Lists.

2. Click Next.

3. Configure data streams

1. Customize how you want your data to appear in your catalog. Select the layer, a name for each table (which will contain the fetched data) and the type of sync.

• Layer: companies in the Growth plan can choose in which layer the tables with the extracted data will be placed.
• 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: depending on the data you are bringing to the lake, you can choose between INCREMENTAL and FULL_TABLE. Read more about Sync Types here.

2. Click Next.

4. Configure data source

1. Describe your data source for easy identification within your organization. You can inform things like what data it brings, to which team it belongs, etc.

2. To define your Trigger, consider how often you want data to be extracted from this source. This decision usually depends on how frequently you need the new table data updated (every day, once a week, or only at specific times).

3. Optionally, you can define some additional settings (if available).

• Configure Delta Log Retention and determine for how log 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.

Check your new source!

1. Click Next to finalize the setup. Once completed, you’ll receive confirmation that your new source is set up!

2. You can view your new source on the Sources page. Now, for you to be able to see it on your Catalog, you have to wait for the pipeline to run. You can now monitor it on the Sources page to see its execution and completion. If needed, manually trigger the pipeline by clicking on the refresh icon. Once executed, your new table will appear in the Catalog section.

If you encounter any issues, reach out to us via Slack, and we’ll gladly assist you!

Streams and Fields

Below you’ll find all available data streams from RD Station and their corresponding fields:

Segmentations

Stream for managing segmentation lists in your RD Station account. Segmentations allow you to group contacts based on specific criteria for targeted marketing campaigns.

Key Fields:

• id - Unique identifier for the segmentation
• name - Name of the segmentation list
• standard - Whether this is a standard/default segmentation
• process_status - Current processing status of the segmentation
• created_at - When the segmentation was created
• updated_at - When the segmentation was last updated
• links - Array of API links related to the segmentation

Segmentation Contacts

Stream for extracting contacts from segmentation lists. When extracting from the entire lead base, a single segmentation_contacts table is created. When extracting from specific segmentation lists, separate tables are created with the naming pattern segmentation_contacts_{segmentation_id}.

Key Fields:

• uuid - Unique identifier for the contact
• name - Full name of the contact
• email - Email address of the contact
• last_conversion_date - Date of the last conversion event
• created_at - When the contact was created
• updated_at - When the contact was last updated
• links - Array of API links related to the contact

Contact Details

Detailed contact information including custom fields. This stream is only available when extracting the entire lead base and requires enabling the “Contact Custom Fields” option.

Note: Enabling this stream significantly increases extraction time as it requires an additional API request for each contact.

Key Fields:

• uuid - Unique identifier for the contact
• name - Full name of the contact
• email - Primary email address
• job_title - Job title of the contact
• birthdate - Date of birth
• bio - Biography or description
• website - Personal or company website
• personal_phone - Personal phone number
• mobile_phone - Mobile phone number
• city - City of residence
• state - State or province
• country - Country
• twitter - Twitter handle
• facebook - Facebook profile
• linkedin - LinkedIn profile
• tags - Array of tags assigned to the contact
• extra_emails - Additional email addresses
• legal_bases - Array of legal consent information containing:
  • category - Category of the legal base
  • type - Type of consent
  • status - Consent status
• links - Array of API links related to the contact
• updated_at - When the contact was last updated
• + Custom Fields - Any custom fields defined in your RD Station account

Contact Events

Stream for extracting conversion events associated with contacts. This stream is only available when extracting the entire lead base.

Key Fields:

• id - Unique identifier for the event
• contact_uuid - UUID of the associated contact
• event_type - Type of event (e.g., CONVERSION)
• event_family - Family/category of the event
• event_identifier - Identifier of the conversion asset
• event_timestamp - When the event occurred

Payload Fields:

• conversion_identifier - Identifier of the conversion point
• traffic_source - Source of the traffic (e.g., organic, paid)
• traffic_medium - Medium of the traffic (e.g., cpc, email)
• traffic_campaign - Campaign associated with the traffic
• + Custom Fields - Any custom fields captured during conversion

Campaigns

Stream for managing email marketing campaigns and their settings.

Key Fields:

• id - Unique identifier for the campaign
• name - Name of the campaign
• status - Current status of the campaign
• total_items - Number of items/emails in the campaign
• created_at - When the campaign was created
• updated_at - When the campaign was last updated
• user - Object containing information about the campaign creator:
  • email - Email of the user who created the campaign
  • links - API links related to the user

Emails

Stream for managing individual email communications within campaigns.

Key Fields:

• id - Unique identifier for the email
• name - Name of the email
• status - Current status of the email
• type - Type of email
• leads_count - Number of leads targeted by this email
• send_at - Scheduled send time
• created_at - When the email was created
• updated_at - When the email was last updated
• campaign_id - ID of the parent campaign
• component_template_id - ID of the template used
• is_predictive_sending - Whether predictive sending is enabled
• sending_is_imminent - Whether sending is about to happen
• behavior_score_info - Object containing behavior score settings:
  • disengaged - Whether to include disengaged contacts
  • engaged - Whether to include engaged contacts
  • indeterminate - Whether to include indeterminate contacts

Workflows (Automated Workflows)

Stream for managing marketing automation workflows.

Key Fields:

• id - Unique identifier for the workflow
• name - Name of the workflow
• user_email_created - Email of the user who created the workflow
• user_email_updated - Email of the user who last updated the workflow
• created_at - When the workflow was created
• updated_at - When the workflow was last updated
• configurations - Object containing workflow settings:
  • status - Current status of the workflow

Landing Pages

Stream for managing landing pages used for lead capture.

Key Fields:

• id - Unique identifier for the landing page
• title - Title of the landing page
• status - Current status (e.g., published, draft)
• conversion_identifier - Identifier used for tracking conversions
• has_active_experiment - Whether an A/B test is currently active
• had_experiment - Whether the page has had A/B tests in the past
• created_at - When the landing page was created
• updated_at - When the landing page was last updated

Embeddables (Forms)

Stream for managing embeddable forms that can be placed on external websites.

Key Fields:

• id - Unique identifier for the form
• title - Title of the form
• status - Current status of the form
• conversion_identifier - Identifier used for tracking conversions
• created_at - When the form was created
• updated_at - When the form was last updated

Popups

Stream for managing popup forms and lead capture overlays.

Key Fields:

• id - Unique identifier for the popup
• title - Title of the popup
• status - Current status of the popup
• conversion_identifier - Identifier used for tracking conversions
• trigger - Trigger condition for displaying the popup
• created_at - When the popup was created
• updated_at - When the popup was last updated

Analytics Funnel (Advanced Plan)

Daily funnel analytics showing lead progression through marketing stages. Only available for accounts with the Advanced plan.

Key Fields:

• reference_day - Date of the analytics data
• visitors_count - Number of website visitors
• contacts_count - Number of new contacts generated
• qualified_contacts_count - Number of qualified contacts (MQLs)
• opportunities_count - Number of opportunities created
• sales_count - Number of closed sales

Analytics Email Stats (Professional Plan)

Email campaign performance statistics. Only available for accounts with the Professional plan.

Key Fields:

• query_date_start - Start date of the query period
• query_date_end - End date of the query period
• campaign_id - ID of the campaign
• campaign_name - Name of the campaign
• send_at - When the email was sent
• contacts_count - Number of contacts targeted

Email Metrics:

• email_dropped_count - Number of emails dropped
• email_delivered_count - Number of emails delivered
• email_bounced_count - Number of emails bounced
• email_opened_count - Number of emails opened
• email_clicked_count - Number of emails clicked
• email_unsubscribed_count - Number of unsubscribes
• email_spam_reported_count - Number of spam reports

Rates:

• email_delivered_rate - Delivery rate
• email_opened_rate - Open rate
• email_clicked_rate - Click rate
• email_spam_reported_rate - Spam report rate

Analytics Workflow Email Stats (Professional Plan)

Email performance statistics for workflow automation emails. Only available for accounts with the Professional plan.

Key Fields:

• query_date_start - Start date of the query period
• query_date_end - End date of the query period
• workflow_id - ID of the workflow
• workflow_name - Name of the workflow
• workflow_action_id - ID of the workflow action
• email_name - Name of the email
• created_at - When the workflow was created
• updated_at - When the workflow was last updated

Email Metrics:

• contacts_count - Number of contacts processed
• count_processed - Total processed count
• email_dropped_count - Number of emails dropped
• email_delivered_count - Number of emails delivered
• email_hard_bounced_unique_count - Unique hard bounces
• email_soft_bounced_unique_count - Unique soft bounces
• email_bounced_unique_count - Total unique bounces
• email_opened_unique_count - Unique opens
• email_clicked_unique_count - Unique clicks
• email_unsubscribed_count - Number of unsubscribes
• email_spam_reported_count - Number of spam reports

Rates:

• email_delivered_rate - Delivery rate
• email_opened_rate - Open rate
• email_clicked_rate - Click rate
• email_spam_reported_rate - Spam report rate

Analytics Conversion Assets (Professional Plan)

Performance statistics for conversion assets (landing pages, forms, popups). Only available for accounts with the Professional plan.

Key Fields:

• query_date_start - Start date of the query period
• query_date_end - End date of the query period
• asset_id - Unique identifier of the asset
• asset_identifier - Conversion identifier of the asset
• asset_type - Type of asset (landing_page, form, popup)
• asset_created_at - When the asset was created
• asset_updated_at - When the asset was last updated

Performance Metrics:

• visits_count - Number of visits to the asset
• conversion_count - Number of conversions
• conversion_rate - Conversion rate (conversions/visits)

Use Cases for Data Analysis

This guide outlines valuable business intelligence use cases when consolidating RD Station data, along with ready-to-use SQL queries that you can run on Explorer.

Lead Generation Analysis

1. Marketing Funnel Performance

Track the progression of leads through your marketing funnel over time to identify bottlenecks and opportunities.

Business Value:

• Identify which funnel stages have the highest drop-off rates
• Track conversion rates between funnel stages
• Monitor trends in lead generation and sales performance
• Optimize marketing strategies based on funnel insights

SQL query

AWS

WITH
    daily_funnel AS (
        SELECT
            DATE(reference_day) AS reference_date,
            visitors_count,
            contacts_count,
            qualified_contacts_count,
            opportunities_count,
            sales_count,
            CAST(contacts_count AS DOUBLE) * 100.0 / NULLIF(visitors_count, 0) AS visitor_to_contact_rate,
            CAST(qualified_contacts_count AS DOUBLE) * 100.0 / NULLIF(contacts_count, 0) AS contact_to_mql_rate,
            CAST(opportunities_count AS DOUBLE) * 100.0 / NULLIF(qualified_contacts_count, 0) AS mql_to_opportunity_rate,
            CAST(sales_count AS DOUBLE) * 100.0 / NULLIF(opportunities_count, 0) AS opportunity_to_sale_rate
        FROM
            nekt_raw.rd_station_analytics_funnel
        WHERE
            DATE(reference_day) >= CURRENT_DATE - INTERVAL '30' DAY
    )
SELECT
    reference_date,
    visitors_count,
    contacts_count,
    qualified_contacts_count,
    opportunities_count,
    sales_count,
    ROUND(visitor_to_contact_rate, 2) AS visitor_to_contact_pct,
    ROUND(contact_to_mql_rate, 2) AS contact_to_mql_pct,
    ROUND(mql_to_opportunity_rate, 2) AS mql_to_opportunity_pct,
    ROUND(opportunity_to_sale_rate, 2) AS opportunity_to_sale_pct
FROM
    daily_funnel
ORDER BY
    reference_date DESC

2. Lead Source Attribution

Analyze which traffic sources and campaigns generate the most conversions to optimize marketing spend.

Business Value:

• Identify the most effective traffic sources for lead generation
• Understand which campaigns drive the highest conversion rates
• Allocate marketing budget more effectively
• Track ROI by marketing channel

SQL query

AWS

WITH
    source_conversions AS (
        SELECT
            COALESCE(payload.traffic_source, 'Direct') AS traffic_source,
            COALESCE(payload.traffic_medium, 'None') AS traffic_medium,
            COALESCE(payload.traffic_campaign, 'None') AS traffic_campaign,
            COUNT(DISTINCT contact_uuid) AS unique_leads,
            COUNT(*) AS total_conversions
        FROM
            nekt_raw.rd_station_contact_events
        WHERE
            event_type = 'CONVERSION'
            AND DATE(event_timestamp) >= CURRENT_DATE - INTERVAL '30' DAY
        GROUP BY
            payload.traffic_source,
            payload.traffic_medium,
            payload.traffic_campaign
    )
SELECT
    traffic_source,
    traffic_medium,
    traffic_campaign,
    unique_leads,
    total_conversions,
    ROUND(CAST(total_conversions AS FLOAT64) / unique_leads, 2) AS conversions_per_lead
FROM
    source_conversions
ORDER BY
    unique_leads DESC

Email Marketing Performance

3. Campaign Email Performance

Analyze the performance of email campaigns to optimize email marketing strategies.

Business Value:

• Identify top-performing email campaigns
• Track engagement metrics over time
• Reduce unsubscribe and spam report rates
• Improve email deliverability and click-through rates

SQL query

AWS

WITH
    campaign_metrics AS (
        SELECT
            campaign_name,
            campaign_id,
            SUM(contacts_count) AS total_contacts,
            SUM(email_delivered_count) AS total_delivered,
            SUM(email_opened_count) AS total_opens,
            SUM(email_clicked_count) AS total_clicks,
            SUM(email_bounced_count) AS total_bounces,
            SUM(email_unsubscribed_count) AS total_unsubscribes,
            SUM(email_spam_reported_count) AS total_spam_reports,
            AVG(email_delivered_rate) AS avg_delivery_rate,
            AVG(email_opened_rate) AS avg_open_rate,
            AVG(email_clicked_rate) AS avg_click_rate
        FROM
            nekt_raw.rd_station_analytics_email_stats
        WHERE
            DATE(query_date_start) >= CURRENT_DATE - INTERVAL '30' DAY
        GROUP BY
            campaign_name,
            campaign_id
    )
SELECT
    campaign_name,
    total_contacts,
    total_delivered,
    total_opens,
    total_clicks,
    ROUND(avg_delivery_rate * 100, 2) AS delivery_rate_pct,
    ROUND(avg_open_rate * 100, 2) AS open_rate_pct,
    ROUND(avg_click_rate * 100, 2) AS click_rate_pct,
    total_bounces,
    total_unsubscribes,
    ROUND(
        CAST(total_clicks AS DOUBLE) * 100.0 / NULLIF(total_opens, 0),
        2
    ) AS click_to_open_rate
FROM
    campaign_metrics
ORDER BY
    total_delivered DESC

4. Workflow Automation Performance

Track the effectiveness of automated email workflows to optimize nurturing sequences.

Business Value:

• Identify high-performing automation workflows
• Compare email performance across different workflow steps
• Optimize workflow sequences based on engagement data
• Reduce drop-off in nurturing campaigns

SQL query

AWS

WITH
    workflow_metrics AS (
        SELECT
            workflow_name,
            workflow_id,
            email_name,
            SUM(contacts_count) AS total_contacts,
            SUM(email_delivered_count) AS total_delivered,
            SUM(email_opened_unique_count) AS unique_opens,
            SUM(email_clicked_unique_count) AS unique_clicks,
            SUM(email_hard_bounced_unique_count) AS hard_bounces,
            SUM(email_soft_bounced_unique_count) AS soft_bounces,
            SUM(email_unsubscribed_count) AS total_unsubscribes,
            AVG(email_opened_rate) AS avg_open_rate,
            AVG(email_clicked_rate) AS avg_click_rate
        FROM
            nekt_raw.rd_station_analytics_workflow_email_stats
        WHERE
            DATE(query_date_start) >= CURRENT_DATE - INTERVAL '30' DAY
        GROUP BY
            workflow_name,
            workflow_id,
            email_name
    )
SELECT
    workflow_name,
    email_name,
    total_contacts,
    total_delivered,
    unique_opens,
    unique_clicks,
    ROUND(avg_open_rate * 100, 2) AS open_rate_pct,
    ROUND(avg_click_rate * 100, 2) AS click_rate_pct,
    ROUND(
        CAST(unique_clicks AS DOUBLE) * 100.0 / NULLIF(unique_opens, 0),
        2
    ) AS click_to_open_rate,
    hard_bounces + soft_bounces AS total_bounces,
    total_unsubscribes
FROM
    workflow_metrics
ORDER BY
    workflow_name,
    total_delivered DESC

Conversion Asset Analysis

5. Landing Page and Form Performance

Analyze the performance of landing pages, forms, and popups to optimize conversion rates.

Business Value:

• Identify top-performing conversion assets
• Track conversion rates across different asset types
• Prioritize optimization efforts on high-traffic, low-conversion assets
• Compare performance across different time periods

SQL query

AWS

WITH
    asset_performance AS (
        SELECT
            asset_type,
            asset_identifier,
            asset_id,
            SUM(visits_count) AS total_visits,
            SUM(conversion_count) AS total_conversions,
            AVG(conversion_rate) AS avg_conversion_rate
        FROM
            nekt_raw.rd_station_analytics_conversion_assets
        WHERE
            DATE(query_date_start) >= CURRENT_DATE - INTERVAL '30' DAY
        GROUP BY
            asset_type,
            asset_identifier,
            asset_id
    )
SELECT
    asset_type,
    asset_identifier,
    total_visits,
    total_conversions,
    ROUND(avg_conversion_rate * 100, 2) AS conversion_rate_pct,
    CASE
        WHEN total_visits > 100 AND avg_conversion_rate < 0.02 THEN 'High traffic, low conversion - optimize!'
        WHEN total_visits > 100 AND avg_conversion_rate >= 0.05 THEN 'High performer'
        WHEN total_visits <= 100 AND avg_conversion_rate >= 0.05 THEN 'Good conversion, needs more traffic'
        ELSE 'Monitor'
    END AS recommendation
FROM
    asset_performance
ORDER BY
    total_visits DESC

6. Contact Base Analysis

Analyze your contact base to understand lead acquisition trends and engagement patterns.

Business Value:

• Understand lead acquisition trends over time
• Identify periods of high contact growth
• Track conversion activity patterns
• Monitor contact database health

SQL query

AWS

WITH
    contact_stats AS (
        SELECT
            DATE_TRUNC('month', created_at) AS acquisition_month,
            COUNT(DISTINCT uuid) AS new_contacts,
            COUNT(DISTINCT CASE 
                WHEN last_conversion_date IS NOT NULL 
                THEN uuid 
            END) AS contacts_with_conversion,
            MIN(created_at) AS earliest_contact,
            MAX(last_conversion_date) AS latest_conversion
        FROM
            nekt_raw.rd_station_segmentation_contacts
        WHERE
            created_at IS NOT NULL
        GROUP BY
            DATE_TRUNC('month', created_at)
    )
SELECT
    acquisition_month,
    new_contacts,
    contacts_with_conversion,
    ROUND(
        CAST(contacts_with_conversion AS DOUBLE) * 100.0 / NULLIF(new_contacts, 0),
        2
    ) AS conversion_rate_pct,
    SUM(new_contacts) OVER (ORDER BY acquisition_month) AS cumulative_contacts
FROM
    contact_stats
ORDER BY
    acquisition_month DESC

Time-Based Analysis

7. Conversion Trends by Day of Week

Identify patterns in lead generation and conversion activity throughout the week.

Business Value:

• Optimize campaign scheduling based on engagement patterns
• Identify best days for email sends
• Plan content publication strategy
• Allocate resources effectively based on expected activity

SQL query

AWS

WITH
    daily_conversions AS (
        SELECT
            DATE(event_timestamp) AS conversion_date,
            DATE_FORMAT(DATE(event_timestamp), '%W') AS day_of_week,
            COUNT(*) AS total_conversions,
            COUNT(DISTINCT contact_uuid) AS unique_leads,
            COUNT(DISTINCT payload.conversion_identifier) AS unique_assets
        FROM
            nekt_raw.rd_station_contact_events
        WHERE
            event_type = 'CONVERSION'
            AND DATE(event_timestamp) >= CURRENT_DATE - INTERVAL '30' DAY
        GROUP BY
            DATE(event_timestamp),
            DATE_FORMAT(DATE(event_timestamp), '%W')
    ),
    weekly_averages AS (
        SELECT
            day_of_week,
            AVG(total_conversions) AS avg_conversions,
            AVG(unique_leads) AS avg_leads,
            AVG(unique_assets) AS avg_assets,
            COUNT(*) AS days_counted
        FROM
            daily_conversions
        GROUP BY
            day_of_week
    )
SELECT
    day_of_week,
    ROUND(avg_conversions, 1) AS avg_daily_conversions,
    ROUND(avg_leads, 1) AS avg_daily_leads,
    ROUND(avg_assets, 1) AS avg_assets_used,
    days_counted,
    CASE
        WHEN avg_conversions >= (SELECT MAX(avg_conversions) * 0.8 FROM weekly_averages) THEN 'Peak Day'
        WHEN avg_conversions <= (SELECT MIN(avg_conversions) * 1.2 FROM weekly_averages) THEN 'Low Day'
        ELSE 'Average'
    END AS day_classification
FROM
    weekly_averages
ORDER BY
    CASE day_of_week
        WHEN 'Monday' THEN 1
        WHEN 'Tuesday' THEN 2
        WHEN 'Wednesday' THEN 3
        WHEN 'Thursday' THEN 4
        WHEN 'Friday' THEN 5
        WHEN 'Saturday' THEN 6
        WHEN 'Sunday' THEN 7
    END

Implementation Notes

Data Quality Considerations

• The contact_events and contacts_details streams require extracting the entire lead base. Plan for longer extraction times if your database is large.
• Analytics streams (funnel, email stats, workflow stats, conversion assets) are only available with Professional or Advanced plans.
• When using the “Enable Contact Custom Fields” option, extraction time increases significantly due to individual API calls per contact.

Best Practices

• Start with the core streams (segmentations, segmentation_contacts, campaigns, emails) before enabling advanced analytics.
• Use segmentation list filtering if you only need specific segments to reduce extraction time.
• Schedule extractions during off-peak hours if you have a large contact database.
• For real-time reporting needs, consider using incremental syncs with daily triggers.