> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nekt.com/llms.txt
> Use this file to discover all available pages before exploring further.
# RD Station as a data source
> Bring data from RD Station to Nekt.
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](https://app.nekt.ai/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
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](https://docs.nekt.com/get-started/core-concepts/catalog-layers) 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](https://docs.nekt.com/get-started/core-concepts/types-of-sync).
Click **Next**.
### 4. Configure data source
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.
To define your [Trigger](https://docs.nekt.com/runs/scheduling-and-triggers), 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).
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](https://docs.nekt.com/get-started/core-concepts/resource-control).
* Determine when to execute an **Additional [Full Sync](https://docs.nekt.com/get-started/core-concepts/types-of-sync#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.
Click **Next** to finalize the setup.
### 5. Check your new source
You can view your new source on the [Sources](https://app.nekt.ai/sources) page. If needed, manually trigger the source extraction by clicking on the arrow button. Once executed, your data will appear in your Catalog.
For you to be able to see it on your [Catalog](https://app.nekt.ai/catalog), you need at least one successful source run.
# Streams and Fields
Below you'll find all available data streams from RD Station and their corresponding fields:
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:**
| Field | Type | Description |
| :--------------- | :-------- | :---------------------------------------------- |
| `id` | String | Unique identifier for the segmentation |
| `name` | String | Name of the segmentation list |
| `standard` | Boolean | Whether this is a standard/default segmentation |
| `process_status` | String | Current processing status of the segmentation |
| `created_at` | Timestamp | When the segmentation was created |
| `updated_at` | Timestamp | When the segmentation was last updated |
| `links` | Array | Array of API links related to the segmentation |
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:**
| Field | Type | Description |
| :--------------------- | :-------- | :---------------------------------------- |
| `uuid` | String | Unique identifier for the contact |
| `name` | String | Full name of the contact |
| `email` | String | Email address of the contact |
| `last_conversion_date` | Timestamp | Date of the last conversion event |
| `created_at` | Timestamp | When the contact was created |
| `updated_at` | Timestamp | When the contact was last updated |
| `links` | Array | Array of API links related to the contact |
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:**
| Field | Type | Description |
| :--------------------- | :-------- | :--------------------------------------------------- |
| `uuid` | String | Unique identifier for the contact |
| `name` | String | Full name of the contact |
| `email` | String | Primary email address |
| `job_title` | String | Job title of the contact |
| `birthdate` | String | Date of birth |
| `bio` | String | Biography or description |
| `website` | String | Personal or company website |
| `personal_phone` | String | Personal phone number |
| `mobile_phone` | String | Mobile phone number |
| `city` | String | City of residence |
| `state` | String | State or province |
| `country` | String | Country |
| `twitter` | String | Twitter handle |
| `facebook` | String | Facebook profile |
| `linkedin` | String | LinkedIn profile |
| `tags` | Array | Array of tags assigned to the contact |
| `extra_emails` | Array | Additional email addresses |
| `legal_bases` | Array | Array of legal consent information |
| `legal_bases.category` | String | Category of the legal base |
| `legal_bases.type` | String | Type of consent |
| `legal_bases.status` | String | Consent status |
| `links` | Array | Array of API links related to the contact |
| `updated_at` | Timestamp | When the contact was last updated |
| `+ Custom Fields` | Varies | Any custom fields defined in your RD Station account |
Stream for extracting conversion events associated with contacts. This stream is only available when extracting the entire lead base.
**Key Fields:**
| Field | Type | Description |
| :------------------------------ | :-------- | :------------------------------------------- |
| `id` | String | Unique identifier for the event |
| `contact_uuid` | String | UUID of the associated contact |
| `event_type` | String | Type of event (e.g., CONVERSION) |
| `event_family` | String | Family/category of the event |
| `event_identifier` | String | Identifier of the conversion asset |
| `event_timestamp` | Timestamp | When the event occurred |
| `payload.conversion_identifier` | String | Identifier of the conversion point |
| `payload.traffic_source` | String | Source of the traffic (e.g., organic, paid) |
| `payload.traffic_medium` | String | Medium of the traffic (e.g., cpc, email) |
| `payload.traffic_campaign` | String | Campaign associated with the traffic |
| `payload.+ Custom Fields` | Varies | Any custom fields captured during conversion |
Stream for managing email marketing campaigns and their settings.
**Key Fields:**
| Field | Type | Description |
| :------------ | :-------- | :------------------------------------------------------- |
| `id` | String | Unique identifier for the campaign |
| `name` | String | Name of the campaign |
| `status` | String | Current status of the campaign |
| `total_items` | Integer | Number of items/emails in the campaign |
| `created_at` | Timestamp | When the campaign was created |
| `updated_at` | Timestamp | When the campaign was last updated |
| `user` | Object | Object containing information about the campaign creator |
| `user.email` | String | Email of the user who created the campaign |
| `user.links` | Array | API links related to the user |
Stream for managing individual email communications within campaigns.
**Key Fields:**
| Field | Type | Description |
| :---------------------------------- | :-------- | :---------------------------------------- |
| `id` | String | Unique identifier for the email |
| `name` | String | Name of the email |
| `status` | String | Current status of the email |
| `type` | String | Type of email |
| `leads_count` | Integer | Number of leads targeted by this email |
| `send_at` | Timestamp | Scheduled send time |
| `created_at` | Timestamp | When the email was created |
| `updated_at` | Timestamp | When the email was last updated |
| `campaign_id` | String | ID of the parent campaign |
| `component_template_id` | String | ID of the template used |
| `is_predictive_sending` | Boolean | Whether predictive sending is enabled |
| `sending_is_imminent` | Boolean | Whether sending is about to happen |
| `behavior_score_info` | Object | Object containing behavior score settings |
| `behavior_score_info.disengaged` | Boolean | Whether to include disengaged contacts |
| `behavior_score_info.engaged` | Boolean | Whether to include engaged contacts |
| `behavior_score_info.indeterminate` | Boolean | Whether to include indeterminate contacts |
Stream for managing marketing automation workflows.
**Key Fields:**
| Field | Type | Description |
| :---------------------- | :-------- | :---------------------------------------------- |
| `id` | String | Unique identifier for the workflow |
| `name` | String | Name of the workflow |
| `user_email_created` | String | Email of the user who created the workflow |
| `user_email_updated` | String | Email of the user who last updated the workflow |
| `created_at` | Timestamp | When the workflow was created |
| `updated_at` | Timestamp | When the workflow was last updated |
| `configurations` | Object | Object containing workflow settings |
| `configurations.status` | String | Current status of the workflow |
Stream for managing landing pages used for lead capture.
**Key Fields:**
| Field | Type | Description |
| :---------------------- | :-------- | :--------------------------------------------- |
| `id` | String | Unique identifier for the landing page |
| `title` | String | Title of the landing page |
| `status` | String | Current status (e.g., published, draft) |
| `conversion_identifier` | String | Identifier used for tracking conversions |
| `has_active_experiment` | Boolean | Whether an A/B test is currently active |
| `had_experiment` | Boolean | Whether the page has had A/B tests in the past |
| `created_at` | Timestamp | When the landing page was created |
| `updated_at` | Timestamp | When the landing page was last updated |
Stream for managing embeddable forms that can be placed on external websites.
**Key Fields:**
| Field | Type | Description |
| :---------------------- | :-------- | :--------------------------------------- |
| `id` | String | Unique identifier for the form |
| `title` | String | Title of the form |
| `status` | String | Current status of the form |
| `conversion_identifier` | String | Identifier used for tracking conversions |
| `created_at` | Timestamp | When the form was created |
| `updated_at` | Timestamp | When the form was last updated |
Stream for managing popup forms and lead capture overlays.
**Key Fields:**
| Field | Type | Description |
| :---------------------- | :-------- | :----------------------------------------- |
| `id` | String | Unique identifier for the popup |
| `title` | String | Title of the popup |
| `status` | String | Current status of the popup |
| `conversion_identifier` | String | Identifier used for tracking conversions |
| `trigger` | String | Trigger condition for displaying the popup |
| `created_at` | Timestamp | When the popup was created |
| `updated_at` | Timestamp | When the popup was last updated |
Daily funnel analytics showing lead progression through marketing stages. Only available for accounts with the Advanced plan.
**Key Fields:**
| Field | Type | Description |
| :------------------------- | :-------- | :---------------------------------- |
| `reference_day` | Timestamp | Date of the analytics data |
| `visitors_count` | Integer | Number of website visitors |
| `contacts_count` | Integer | Number of new contacts generated |
| `qualified_contacts_count` | Integer | Number of qualified contacts (MQLs) |
| `opportunities_count` | Integer | Number of opportunities created |
| `sales_count` | Integer | Number of closed sales |
Email campaign performance statistics. Only available for accounts with the Professional plan.
**Key Fields:**
| Field | Type | Description |
| :-------------------------- | :-------- | :----------------------------- |
| `query_date_start` | Timestamp | Start date of the query period |
| `query_date_end` | Timestamp | End date of the query period |
| `campaign_id` | String | ID of the campaign |
| `campaign_name` | String | Name of the campaign |
| `send_at` | Timestamp | When the email was sent |
| `contacts_count` | Integer | Number of contacts targeted |
| `email_dropped_count` | Integer | Number of emails dropped |
| `email_delivered_count` | Integer | Number of emails delivered |
| `email_bounced_count` | Integer | Number of emails bounced |
| `email_opened_count` | Integer | Number of emails opened |
| `email_clicked_count` | Integer | Number of emails clicked |
| `email_unsubscribed_count` | Integer | Number of unsubscribes |
| `email_spam_reported_count` | Integer | Number of spam reports |
| `email_delivered_rate` | Float | Delivery rate |
| `email_opened_rate` | Float | Open rate |
| `email_clicked_rate` | Float | Click rate |
| `email_spam_reported_rate` | Float | Spam report rate |
Email performance statistics for workflow automation emails. Only available for accounts with the Professional plan.
**Key Fields:**
| Field | Type | Description |
| :-------------------------------- | :-------- | :--------------------------------- |
| `query_date_start` | Timestamp | Start date of the query period |
| `query_date_end` | Timestamp | End date of the query period |
| `workflow_id` | String | ID of the workflow |
| `workflow_name` | String | Name of the workflow |
| `workflow_action_id` | String | ID of the workflow action |
| `email_name` | String | Name of the email |
| `created_at` | Timestamp | When the workflow was created |
| `updated_at` | Timestamp | When the workflow was last updated |
| `contacts_count` | Integer | Number of contacts processed |
| `count_processed` | Integer | Total processed count |
| `email_dropped_count` | Integer | Number of emails dropped |
| `email_delivered_count` | Integer | Number of emails delivered |
| `email_hard_bounced_unique_count` | Integer | Unique hard bounces |
| `email_soft_bounced_unique_count` | Integer | Unique soft bounces |
| `email_bounced_unique_count` | Integer | Total unique bounces |
| `email_opened_unique_count` | Integer | Unique opens |
| `email_clicked_unique_count` | Integer | Unique clicks |
| `email_unsubscribed_count` | Integer | Number of unsubscribes |
| `email_spam_reported_count` | Integer | Number of spam reports |
| `email_delivered_rate` | Float | Delivery rate |
| `email_opened_rate` | Float | Open rate |
| `email_clicked_rate` | Float | Click rate |
| `email_spam_reported_rate` | Float | Spam report rate |
Performance statistics for conversion assets (landing pages, forms, popups). Only available for accounts with the Professional plan.
**Key Fields:**
| Field | Type | Description |
| :----------------- | :-------- | :----------------------------------------- |
| `query_date_start` | Timestamp | Start date of the query period |
| `query_date_end` | Timestamp | End date of the query period |
| `asset_id` | String | Unique identifier of the asset |
| `asset_identifier` | String | Conversion identifier of the asset |
| `asset_type` | String | Type of asset (landing\_page, form, popup) |
| `asset_created_at` | Timestamp | When the asset was created |
| `asset_updated_at` | Timestamp | When the asset was last updated |
| `visits_count` | Integer | Number of visits to the asset |
| `conversion_count` | Integer | Number of conversions |
| `conversion_rate` | Float | 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](https://app.nekt.ai/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 theme={null}
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
```
```sql theme={null}
WITH
daily_funnel AS (
SELECT
DATE(reference_day) AS reference_date,
visitors_count,
contacts_count,
qualified_contacts_count,
opportunities_count,
sales_count,
SAFE_DIVIDE(CAST(contacts_count AS FLOAT64) * 100.0, visitors_count) AS visitor_to_contact_rate,
SAFE_DIVIDE(CAST(qualified_contacts_count AS FLOAT64) * 100.0, contacts_count) AS contact_to_mql_rate,
SAFE_DIVIDE(CAST(opportunities_count AS FLOAT64) * 100.0, qualified_contacts_count) AS mql_to_opportunity_rate,
SAFE_DIVIDE(CAST(sales_count AS FLOAT64) * 100.0, opportunities_count) AS opportunity_to_sale_rate
FROM
`nekt_raw.rd_station_analytics_funnel`
WHERE
DATE(reference_day) >= DATE_SUB(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
```
| reference\_date | visitors\_count | contacts\_count | qualified\_contacts\_count | opportunities\_count | sales\_count | visitor\_to\_contact\_pct | contact\_to\_mql\_pct | mql\_to\_opportunity\_pct | opportunity\_to\_sale\_pct |
| --------------- | --------------- | --------------- | -------------------------- | -------------------- | ------------ | ------------------------- | --------------------- | ------------------------- | -------------------------- |
| 2024-11-27 | 12,450 | 234 | 45 | 12 | 3 | 1.88 | 19.23 | 26.67 | 25.00 |
| 2024-11-26 | 11,890 | 212 | 38 | 10 | 2 | 1.78 | 17.92 | 26.32 | 20.00 |
| 2024-11-25 | 10,234 | 189 | 32 | 8 | 2 | 1.85 | 16.93 | 25.00 | 25.00 |
| 2024-11-24 | 8,920 | 145 | 28 | 6 | 1 | 1.63 | 19.31 | 21.43 | 16.67 |
| 2024-11-23 | 9,456 | 167 | 31 | 7 | 2 | 1.77 | 18.56 | 22.58 | 28.57 |
| 2024-11-22 | 14,230 | 289 | 52 | 14 | 4 | 2.03 | 17.99 | 26.92 | 28.57 |
### 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 theme={null}
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
```
```sql theme={null}
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) >= DATE_SUB(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(SAFE_DIVIDE(CAST(total_conversions AS FLOAT64), unique_leads), 2) AS conversions_per_lead
FROM
source_conversions
ORDER BY
unique_leads DESC
```
| traffic\_source | traffic\_medium | traffic\_campaign | unique\_leads | total\_conversions | conversions\_per\_lead |
| --------------- | --------------- | ------------------- | ------------- | ------------------ | ---------------------- |
| google | cpc | black\_friday\_2024 | 456 | 892 | 1.96 |
| google | organic | None | 312 | 423 | 1.36 |
| facebook | paid | product\_launch | 234 | 345 | 1.47 |
| Direct | None | None | 189 | 234 | 1.24 |
| instagram | paid | brand\_awareness | 156 | 189 | 1.21 |
| linkedin | cpc | b2b\_campaign | 89 | 112 | 1.26 |
| email | newsletter | weekly\_digest | 78 | 145 | 1.86 |
## 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 theme={null}
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
```
```sql theme={null}
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) >= DATE_SUB(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(
SAFE_DIVIDE(CAST(total_clicks AS FLOAT64) * 100.0, total_opens),
2
) AS click_to_open_rate
FROM
campaign_metrics
ORDER BY
total_delivered DESC
```
| campaign\_name | total\_contacts | total\_delivered | total\_opens | total\_clicks | delivery\_rate\_pct | open\_rate\_pct | click\_rate\_pct | total\_bounces | total\_unsubscribes | click\_to\_open\_rate |
| ------------------ | --------------- | ---------------- | ------------ | ------------- | ------------------- | --------------- | ---------------- | -------------- | ------------------- | --------------------- |
| Black Friday 2024 | 45,230 | 43,890 | 18,920 | 4,560 | 97.04 | 43.11 | 10.39 | 1,340 | 89 | 24.10 |
| Product Launch | 28,450 | 27,560 | 12,340 | 3,890 | 96.87 | 44.77 | 14.12 | 890 | 56 | 31.53 |
| Weekly Newsletter | 34,560 | 33,450 | 8,920 | 1,234 | 96.79 | 26.67 | 3.69 | 1,110 | 123 | 13.83 |
| Nurturing Sequence | 12,890 | 12,450 | 5,670 | 1,890 | 96.58 | 45.54 | 15.18 | 440 | 34 | 33.33 |
| Re-engagement | 8,450 | 7,890 | 2,340 | 456 | 93.37 | 29.66 | 5.78 | 560 | 78 | 19.49 |
### 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 theme={null}
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
```
```sql theme={null}
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) >= DATE_SUB(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(
SAFE_DIVIDE(CAST(unique_clicks AS FLOAT64) * 100.0, unique_opens),
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
```
| workflow\_name | email\_name | total\_contacts | total\_delivered | unique\_opens | unique\_clicks | open\_rate\_pct | click\_rate\_pct | click\_to\_open\_rate | total\_bounces | total\_unsubscribes |
| ------------------ | --------------- | --------------- | ---------------- | ------------- | -------------- | --------------- | ---------------- | --------------------- | -------------- | ------------------- |
| Onboarding Flow | Welcome Email | 8,920 | 8,670 | 5,230 | 1,890 | 60.32 | 21.80 | 36.14 | 250 | 12 |
| Onboarding Flow | Getting Started | 7,450 | 7,230 | 3,890 | 1,234 | 53.81 | 17.07 | 31.72 | 220 | 18 |
| Onboarding Flow | Pro Tips | 6,120 | 5,980 | 2,890 | 890 | 48.33 | 14.88 | 30.80 | 140 | 23 |
| Nurturing Campaign | Case Study | 4,560 | 4,430 | 1,890 | 567 | 42.66 | 12.80 | 30.00 | 130 | 8 |
| Nurturing Campaign | Product Demo | 3,890 | 3,780 | 1,670 | 489 | 44.18 | 12.94 | 29.28 | 110 | 11 |
| Re-activation | We Miss You | 2,340 | 2,120 | 567 | 123 | 26.75 | 5.80 | 21.69 | 220 | 45 |
## 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 theme={null}
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
```
```sql theme={null}
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) >= DATE_SUB(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
```
| asset\_type | asset\_identifier | total\_visits | total\_conversions | conversion\_rate\_pct | recommendation |
| ------------- | -------------------- | ------------- | ------------------ | --------------------: | ---------------------------------------- |
| landing\_page | black-friday-2024 | 12,450 | 1,890 | 15.18 | High performer |
| landing\_page | product-demo-request | 8,920 | 567 | 6.36 | High performer |
| form | newsletter-signup | 5,670 | 456 | 8.04 | High performer |
| landing\_page | ebook-download | 4,230 | 78 | 1.84 | High traffic, low conversion - optimize! |
| popup | exit-intent-offer | 3,890 | 234 | 6.01 | High performer |
| form | contact-us | 2,340 | 189 | 8.08 | Good conversion, needs more traffic |
| landing\_page | webinar-registration | 1,890 | 145 | 7.67 | Good conversion, needs more traffic |
### 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 theme={null}
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
```
```sql theme={null}
WITH
contact_stats AS (
SELECT
DATE_TRUNC(created_at, MONTH) 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(created_at, MONTH)
)
SELECT
acquisition_month,
new_contacts,
contacts_with_conversion,
ROUND(
SAFE_DIVIDE(CAST(contacts_with_conversion AS FLOAT64) * 100.0, new_contacts),
2
) AS conversion_rate_pct,
SUM(new_contacts) OVER (ORDER BY acquisition_month) AS cumulative_contacts
FROM
contact_stats
ORDER BY
acquisition_month DESC
```
| acquisition\_month | new\_contacts | contacts\_with\_conversion | conversion\_rate\_pct | cumulative\_contacts |
| ------------------ | ------------- | -------------------------- | --------------------: | -------------------- |
| 2024-11-01 | 2,890 | 2,450 | 84.78 | 45,670 |
| 2024-10-01 | 3,120 | 2,780 | 89.10 | 42,780 |
| 2024-09-01 | 2,780 | 2,340 | 84.17 | 39,660 |
| 2024-08-01 | 2,450 | 2,120 | 86.53 | 36,880 |
| 2024-07-01 | 2,670 | 2,230 | 83.52 | 34,430 |
| 2024-06-01 | 2,340 | 1,980 | 84.62 | 31,760 |
## 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 theme={null}
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
```
```sql theme={null}
WITH
daily_conversions AS (
SELECT
DATE(event_timestamp) AS conversion_date,
FORMAT_DATE('%A', DATE(event_timestamp)) 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) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY
DATE(event_timestamp),
FORMAT_DATE('%A', DATE(event_timestamp))
),
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
```
| day\_of\_week | avg\_daily\_conversions | avg\_daily\_leads | avg\_assets\_used | days\_counted | day\_classification |
| ------------- | ----------------------- | ----------------- | ----------------- | ------------- | ------------------- |
| Monday | 45.2 | 38.4 | 8.2 | 4 | Average |
| Tuesday | 52.8 | 44.6 | 9.1 | 4 | Peak Day |
| Wednesday | 56.4 | 47.2 | 9.4 | 4 | Peak Day |
| Thursday | 48.6 | 41.8 | 8.6 | 4 | Average |
| Friday | 42.1 | 36.2 | 7.8 | 4 | Average |
| Saturday | 18.4 | 15.6 | 4.2 | 4 | Low Day |
| Sunday | 12.8 | 10.4 | 3.1 | 4 | Low Day |
## Implementation Notes
### API Behaviors & Limits
* The connector automatically handles API rate limits (HTTP 429) and server errors (HTTP 5xx) by retrying failed requests up to 5 times. It intelligently respects the `Retry-After-Quotas` and `Retry-After` headers provided by the RD Station API to pause extractions when limits are reached, falling back to exponential backoff for other transient network errors.
### 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.
## Troubleshooting
| Issue | Possible cause | Solution |
| :----------------------------------------- | :----------------------------------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------- |
| `Error getting access token` in logs | Invalid or expired credentials, or network issue | Re-authenticate your RD Station account. If the issue persists, check the exact error message logged by Nekt to identify HTTP or connection failures. |
| `Error getting segmentation lists` in logs | Insufficient permissions or API downtime | Ensure your connected account has the correct permissions to fetch segmentations. Review the detailed error logged in Nekt for specific API responses. |
## Skills for agents
RD Station connector documentation as plain markdown, for use in AI agent contexts.