Search the docs, videos, blog, apps, teams, skills and release notes.
At a glance
What it is
Analytics engineering expertise covering dbt best practices, metrics layer design, semantic modeling, dashboard design principles, KPI definition, A/B test analysis, cohort analysis, funnel analysis, retention analysis, and SQL style guide for building trusted, well-documented analytical systems.
A skill is a written procedure a bot loads when a job calls for it. This one is a single file, SKILL.md, and the whole file is on this page.
The whole file, exactly as it downloads. Read it before you rely on it.
SKILL.md
490 lines · 1,948 words
Analytics engineering sits at the intersection of data engineering and data analysis. It is the discipline of building reliable, tested, documented transformation layers that turn raw data into trusted datasets for analysis and decision-making. The analytics engineer owns the transformation layer and ensures that every metric has a single, verified definition.
Analytics engineering expertise covering dbt best practices, metrics layer design, semantic modeling, dashboard design principles, KPI definition, A/B test analysis, cohort analysis, funnel analysis, retention analysis, and SQL style guide for building trusted, well-documented analytical systems.
Nothing. It ships with Murage, which is open source. Your bots run on the AI plan you already have.
Yes. Open it in Settings → Skills to read, edit or duplicate it. Skill Guard scans it again after every edit.
Download the app, connect the AI you already pay for, and tell your Chief of Staff what needs doing. Plan on about ten minutes from install to a working team.
No account needed · Runs on the AI plan you already pay for
Paste it into your agent's instructions (CLAUDE.md, AGENTS.md or a custom GPT), or add it to a bot in Murage.
Ask Ember for the bot, then switch the skill on in its settings.
Step 1
Send your Chief of Staff this message. It proposes the bot and its role first, and creates it when you say go.
Send this to Ember
“Create a bot that uses the Analytics Engineer skill, and show me the plan first.”
You're done when your Chief proposes the bot and its role, and the bot appears in your sidebar once you say go.
Step 2
You're done when you see the bot’s skills and the library.
Step 3
You're done when the skill shows as on for that bot.
Step 4
You're done when the bot takes the job and opens the skill when it needs it.
Prefer to do it yourself? Choose + in the sidebar, then New Bot.Read the full guide to skills and Skill Guard
What it teaches a bot, what it covers, and the whole file to read.
What it covers
Overview
dbt Best Practices
Metrics Layer
Dashboard Design Principles
A/B Test Analysis
Cohort Analysis
Funnel Analysis
Retention Analysis
SQL Style Guide
Decision Framework for Analytics Engineers
When to Use
Output Format
Example
Edge Cases
Raw Layer (sources):
- Defined in YAML, not modified
- Tested with source freshness checks
- 1:1 mapping with source tables
Staging Layer (stg_):
- One model per source table
- Rename columns to business-friendly names
- Cast types, standardize formats
- Light filtering (remove test/internal data)
- Materialized as views (lightweight)
Intermediate Layer (int_):
- Business logic, joins, complex transforms
- Named by entity and action: int_orders_pivoted_to_customers
- Often ephemeral (inlined into downstream)
- Break complex transforms into composable steps
Marts Layer (dim_, fct_, rpt_):
- Final business entities consumed by BI tools
- Materialized as tables or incremental
- Heavily tested and documented
- Organized by business domain (finance/, marketing/, product/)
Metrics Layer:
- Metric definitions in YAML
- Single source of truth for KPIs
- Used by BI tools via metrics API# models/staging/stg_stripe__charges.sql
# Pattern: stg_{source}__{entity}
# models/intermediate/int_payments_pivoted_to_orders.sql
# Pattern: int_{entity}_{transformation}
# models/marts/finance/fct_monthly_revenue.sql
# Pattern: {prefix}_{entity} where prefix = dim (dimension), fct (fact), rpt (report)-- models/marts/core/fct_events.sql
{{
config(
materialized='incremental',
unique_key='event_id',
incremental_strategy='merge',
partition_by={
'field': 'event_date',
'data_type': 'date',
'granularity': 'day'
},
cluster_by=['event_type', 'user_id'],
)
}}
WITH source_events AS (
SELECT * FROM {{ ref('stg_amplitude__events') }}
# ... (condensed) ...
event_properties,
user_properties,
platform,
app_version,
_loaded_at
FROM deduped
WHERE row_num = 1-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, precision=2) %}
ROUND({{ column_name }}::NUMERIC / 100, {{ precision }})
{% endmacro %}
-- macros/date_spine.sql
{% macro date_spine(start_date, end_date) %}
WITH date_spine AS (
{{ dbt_utils.date_spine(
datepart="day",
start_date="'" ~ start_date ~ "'",
end_date="'" ~ end_date ~ "'"
) }}
)
SELECT date_day AS dt FROM date_spine
{% endmacro %}
-- macros/customer_segment.sql
# ... (condensed) ...
-- Usage in model:
SELECT
customer_id,
lifetime_revenue,
total_orders,
{{ customer_segment('lifetime_revenue', 'total_orders') }} AS segment
FROM {{ ref('int_customer_orders_summary') }}# models/metrics/revenue_metrics.yml
version: 2
metrics:
- name: monthly_recurring_revenue
label: Monthly Recurring Revenue (MRR)
description: >
Sum of all active subscription monthly values. Annual subscriptions
are divided by 12. Excludes one-time charges and credits.
type: derived
type_params:
expr: "mrr_new + mrr_expansion - mrr_contraction - mrr_churn"
metrics:
- name: mrr_new
- name: mrr_expansion
- name: mrr_contraction
- name: mrr_churn
# ... (condensed) ...
label: Net Revenue Retention (NRR)
description: >
Revenue from existing customers this period / revenue from
same cohort in prior period. >100% means expansion exceeds churn.
type: derived
type_params:
expr: "current_period_revenue / prior_period_revenue * 100"Every KPI should have these components documented:
kpi:
name: Customer Acquisition Cost (CAC)
owner: marketing-analytics
definition: >
Total sales and marketing spend in a period divided by the number
of new customers acquired in that period.
formula: >
(SUM(marketing_spend) + SUM(sales_spend)) / COUNT(DISTINCT new_customer_id)
data_sources:
- table: fct_marketing_spend
columns: [spend_amount, spend_date, channel]
- table: fct_sales_spend
columns: [spend_amount, spend_date, rep_id]
- table: dim_customer
columns: [customer_id, first_purchase_date]
grain: monthly
dimensions:
- channel (organic, paid_search, social, referral)
- region (us, eu, apac)
- segment (enterprise, mid_market, smb)
targets:
- period: Q1 2024
target: 150.00
currency: USD
caveats:
- Excludes free trial users who never converted
- Marketing spend is allocated by attribution model (last-touch)
- Seasonal variations expected in Q4 (holiday spend)Level 1: Executive Summary (top of dashboard)
- 3-5 KPIs with sparklines showing trend
- Red/yellow/green indicators against targets
- Time period selector
Level 2: Trend Analysis (middle)
- Time series charts for key metrics
- Comparison overlays (this period vs prior period)
- Annotations for significant events
Level 3: Dimensional Breakdown (bottom)
- Bar/column charts by segment
- Tables with sortable columns
- Drill-down capability to detail| Data Pattern | Chart Type | When to Use |
|---|---|---|
| Trend over time | Line chart | Continuous metrics over sequential time |
| Comparison | Bar chart (horizontal) | Comparing categories |
| Composition | Stacked bar | Part-to-whole over categories |
| Distribution | Histogram / box plot | Understanding data spread |
| Relationship | Scatter plot | Correlation between two measures |
| Part-to-whole (static) | Donut / treemap | Showing proportions at one point in time |
| Geographic | Choropleth map | Regional variation |
| Progress to goal | Bullet chart / gauge | KPI vs target |
| Funnel | Funnel chart | Sequential step conversion |
3D charts: Distort perception; always use 2D
Dual axes: Misleading scale comparisons; use two separate charts
Pie charts with >5 slices: Unreadable; use horizontal bar
Truncated Y-axis: Exaggerates differences; always start at 0 for bar charts
Red/green only: ~8% of males are color-blind; use shapes/patterns too
Too many metrics: >10 metrics on one dashboard causes cognitive overload
-- A/B test analysis framework
-- Step 1: Define experiment population
WITH experiment_users AS (
SELECT
user_id,
variant, -- 'control' or 'treatment'
assignment_date
FROM experiment_assignments
WHERE experiment_id = 'pricing_page_v2'
AND assignment_date BETWEEN '2024-06-01' AND '2024-06-30'
),
-- Step 2: Compute primary metric per user
user_metrics AS (
SELECT
eu.user_id,
eu.variant,
# ... (condensed) ...
SELECT * FROM variant_summary;
-- Step 4: Statistical significance (computed in Python)
-- For proportions (conversion rate): use chi-squared or z-test
-- For continuous metrics (revenue): use t-test or Mann-Whitney U
-- Always check: sample size sufficient? AA test validates randomization?from scipy import stats
import numpy as np
def ab_test_significance(control_data, treatment_data, metric_type='continuous'):
"""
Calculate statistical significance of A/B test.
metric_type: 'continuous' for revenue, 'binary' for conversion
"""
if metric_type == 'continuous':
# Welch's t-test (does not assume equal variance)
t_stat, p_value = stats.ttest_ind(
control_data, treatment_data, equal_var=False
)
effect_size = (treatment_data.mean() - control_data.mean()) / control_data.std()
elif metric_type == 'binary':
# Z-test for proportions
# ... (condensed) ...
'effect_size': effect_size,
'confidence_level': 0.95,
'control_mean': control_data.mean(),
'treatment_mean': treatment_data.mean(),
'relative_lift': (treatment_data.mean() - control_data.mean()) / control_data.mean() * 100,
'sample_size': {'control': len(control_data), 'treatment': len(treatment_data)},
}-- Monthly retention cohort analysis
WITH user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', first_purchase_date) AS cohort_month
FROM dim_customer
),
user_activity AS (
SELECT
o.user_id,
DATE_TRUNC('month', o.order_date) AS activity_month
FROM fct_orders o
),
cohort_retention AS (
SELECT
c.cohort_month,
# ... (condensed) ...
cr.months_since_signup,
cr.active_users,
ROUND(cr.active_users::NUMERIC / cs.cohort_size * 100, 1) AS retention_pct
FROM cohort_retention cr
JOIN cohort_sizes cs ON cr.cohort_month = cs.cohort_month
WHERE cr.months_since_signup BETWEEN 0 AND 12
ORDER BY cr.cohort_month, cr.months_since_signup;-- Conversion funnel: signup -> activation -> first purchase -> repeat purchase
WITH funnel AS (
SELECT
u.user_id,
u.signup_date,
u.signup_channel,
1 AS step_1_signup,
CASE WHEN u.activation_date IS NOT NULL THEN 1 ELSE 0 END AS step_2_activated,
CASE WHEN fp.first_purchase_date IS NOT NULL THEN 1 ELSE 0 END AS step_3_purchased,
CASE WHEN rp.second_purchase_date IS NOT NULL THEN 1 ELSE 0 END AS step_4_repeat
FROM dim_user u
LEFT JOIN (
SELECT user_id, MIN(order_date) AS first_purchase_date
FROM fct_orders GROUP BY 1
) fp ON u.user_id = fp.user_id
LEFT JOIN (
SELECT user_id, MIN(order_date) AS second_purchase_date
FROM (
# ... (condensed) ...
SUM(step_3_purchased) AS purchased,
ROUND(SUM(step_3_purchased)::NUMERIC / SUM(step_2_activated) * 100, 1) AS purchase_rate,
SUM(step_4_repeat) AS repeat_purchasers,
ROUND(SUM(step_4_repeat)::NUMERIC / NULLIF(SUM(step_3_purchased), 0) * 100, 1) AS repeat_rate
FROM funnel
GROUP BY 1
ORDER BY signups DESC;-- Dollar-based Net Revenue Retention
WITH monthly_cohort_revenue AS (
SELECT
c.cohort_month,
DATE_TRUNC('month', o.order_date) AS revenue_month,
DATEDIFF('month', c.cohort_month, DATE_TRUNC('month', o.order_date)) AS month_number,
SUM(o.net_amount) AS revenue
FROM (
SELECT user_id, DATE_TRUNC('month', MIN(order_date)) AS cohort_month
FROM fct_orders GROUP BY 1
) c
JOIN fct_orders o ON c.user_id = o.user_id
GROUP BY 1, 2, 3
)
SELECT
cohort_month,
month_number,
revenue,
FIRST_VALUE(revenue) OVER (PARTITION BY cohort_month ORDER BY month_number) AS month_0_revenue,
ROUND(revenue / FIRST_VALUE(revenue) OVER (
PARTITION BY cohort_month ORDER BY month_number
) * 100, 1) AS revenue_retention_pct
FROM monthly_cohort_revenue
WHERE month_number <= 12
ORDER BY cohort_month, month_number;-- FORMATTING RULES:
-- 1. Keywords in UPPERCASE
-- 2. One clause per line (SELECT, FROM, WHERE, GROUP BY, etc.)
-- 3. Indentation: 4 spaces
-- 4. Comma-leading style (easier to comment out columns)
-- 5. CTEs named with meaningful names (not cte1, cte2)
-- 6. Table aliases: full words, not single letters (orders, not o)
-- 7. Explicit JOIN type (INNER JOIN, LEFT JOIN - never implicit joins)
-- 8. Column aliases with AS keyword
-- EXAMPLE:
WITH monthly_orders AS (
SELECT
customer_id
,DATE_TRUNC('month', order_date) AS order_month
,COUNT(DISTINCT order_id) AS order_count
,SUM(net_amount) AS total_revenue
,AVG(net_amount) AS avg_order_value
# ... (condensed) ...
,AVG(orders.avg_order_value) AS avg_order_value
FROM monthly_orders AS orders
INNER JOIN customer_segments AS segments
ON orders.customer_id = segments.customer_id
GROUP BY 1, 2
HAVING SUM(orders.total_revenue) > 0
ORDER BY 1, 2Where does the logic live? -> dbt models, not BI tool calculated fields
How is it tested? -> dbt tests (generic + singular) on every model
Is the metric defined once? -> Metrics layer, referenced everywhere
Can a stakeholder find the definition? -> dbt docs, data dictionary
Is the pipeline observable? -> dbt run results, freshness checks, alerts
Can it be reproduced? -> Version-controlled SQL, deterministic transforms
Use this skill when:
Designing or implementing analytics engineer solutions
Reviewing or improving existing analytics engineer approaches
Making architectural or implementation decisions about analytics engineer
Learning analytics engineer patterns and best practices
Troubleshooting analytics engineer-related issues
Do NOT use this skill when:
The question is about a fundamentally different technology domain
A more specific sibling skill covers the exact topic needed
The user needs a complete hands-on tutorial rather than expert guidance
# Analytics Engineer Analysis
## Context Assessment
[Situation summary and constraints]
## Recommended Approach
[Primary recommendation with rationale]
## Implementation Steps
1. [Step with specific details]
2. [Step with specific details]
3. [Step with specific details]
## Trade-offs and Considerations
- [Key trade-off 1]
- [Key trade-off 2]
## Next Steps
- [Immediate action item]
- [Follow-up action item]Input: "Help me implement analytics engineer for a medium-scale production application"
Output: A structured analysis covering current state assessment, recommended analytics engineer approach with specific patterns, implementation roadmap with milestones, and risk mitigation strategies tailored to the application scale and constraints.
Legacy system integration: When analytics engineer must coexist with legacy approaches, provide a gradual migration path rather than a complete rewrite
Scale mismatch: When the solution complexity exceeds the project scale, recommend a simpler approach and note when to revisit
Team skill gaps: When the team lacks experience with the recommended approach, include learning resources and simpler alternatives
Conflicting requirements: When constraints conflict (e.g., performance vs. maintainability), explicitly state the trade-off and recommend based on stated priorities
SKILL.md
490 lines · 1,948 words
The text of SKILL.md is shown as it downloads, under the Apache-2.0 license, by Ferrox Labs. The license is the one in the file's own frontmatter.
Your new bot
Analytics Engineer bot
Waiting in your sidebar once you say go.
No account needed · Runs on the AI plan you already pay for