Amazon Marketing Cloud
Advanced analytics, audience insights, and SQL query templates
Data Points Analyzed
24.8M
Real-time processing
Active Segments
68
+12 new segments
Avg. Path Length
4.2
Stable
Query Executions
1,247
This month
Path to Conversion Analysis
Conversions by Touchpoint Frequency
Key Insights
Cross-Device Journey
68% of conversions involve multiple devices, with mobile being the primary research device
High Impact
Optimal Frequency
5-7 ad exposures yield the highest conversion rate at 13.8%
High Impact
Audience Overlap
42% overlap between SP and DSP audiences, indicating opportunity for frequency capping
Medium Impact
Time to Conversion
Average 14 days from first touchpoint to conversion for health & wellness products
Medium Impact
Audience Composition
| Segment | Percentage | Users | Trend |
|---|---|---|---|
| New-to-Brand | 34% | 425,000 | +5.2% |
| Repeat Purchasers | 28% | 350,000 | +3.8% |
| High-Value Customers | 18% | 225,000 | +2.1% |
| At-Risk Customers | 12% | 150,000 | -1.5% |
| Lapsed Customers | 8% | 100,000 | -0.8% |
SQL Query Templates
Ready-to-use SQL templates for common AMC analyses
New-to-Brand Customers
Audience AnalysisIdentify customers who made their first purchase in the last 30 days
SELECT
user_id,
first_purchase_date,
product_category,
order_value
FROM user_purchases
WHERE first_purchase_date >= CURRENT_DATE - INTERVAL '30' DAY
AND purchase_count = 1
GROUP BY user_id
ORDER BY first_purchase_date DESC;Multi-Touch Attribution
AttributionAnalyze the customer journey with multiple touchpoints before conversion
SELECT
user_id,
COUNT(DISTINCT touchpoint_type) as touchpoint_count,
LISTAGG(touchpoint_type, ' → ') as journey_path,
conversion_date,
revenue
FROM user_touchpoints
WHERE conversion_date >= CURRENT_DATE - INTERVAL '90' DAY
GROUP BY user_id, conversion_date, revenue
HAVING COUNT(DISTINCT touchpoint_type) >= 3
ORDER BY touchpoint_count DESC;High-Value Customer Segments
SegmentationIdentify customers with lifetime value above €500
SELECT
user_id,
SUM(order_value) as lifetime_value,
COUNT(order_id) as purchase_frequency,
AVG(order_value) as avg_order_value,
MAX(order_date) as last_purchase_date
FROM orders
GROUP BY user_id
HAVING SUM(order_value) > 500
ORDER BY lifetime_value DESC;Cross-Device Journey Analysis
Device AnalysisTrack customer interactions across multiple devices
SELECT
user_id,
COUNT(DISTINCT device_type) as device_count,
LISTAGG(DISTINCT device_type, ', ') as devices_used,
COUNT(session_id) as total_sessions,
conversion_flag
FROM user_sessions
WHERE session_date >= CURRENT_DATE - INTERVAL '60' DAY
GROUP BY user_id, conversion_flag
HAVING COUNT(DISTINCT device_type) > 1
ORDER BY device_count DESC;Optimal Frequency Analysis
FrequencyDetermine the optimal ad exposure frequency for conversions
SELECT
CASE
WHEN impression_count BETWEEN 1 AND 3 THEN '1-3'
WHEN impression_count BETWEEN 4 AND 7 THEN '4-7'
WHEN impression_count BETWEEN 8 AND 12 THEN '8-12'
ELSE '13+'
END as frequency_bucket,
COUNT(DISTINCT user_id) as users,
SUM(conversion_flag) as conversions,
ROUND(SUM(conversion_flag) * 100.0 / COUNT(DISTINCT user_id), 2) as cvr
FROM user_impressions
WHERE impression_date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY frequency_bucket
ORDER BY frequency_bucket;Audience Overlap Analysis
Audience AnalysisFind overlap between different campaign audiences
SELECT
a.campaign_id as campaign_a,
b.campaign_id as campaign_b,
COUNT(DISTINCT a.user_id) as overlap_count,
ROUND(COUNT(DISTINCT a.user_id) * 100.0 /
(SELECT COUNT(DISTINCT user_id) FROM campaign_audience WHERE campaign_id = a.campaign_id), 2) as overlap_percentage
FROM campaign_audience a
INNER JOIN campaign_audience b
ON a.user_id = b.user_id
AND a.campaign_id < b.campaign_id
GROUP BY a.campaign_id, b.campaign_id
HAVING COUNT(DISTINCT a.user_id) > 1000
ORDER BY overlap_count DESC;Time to Conversion
AttributionCalculate average time from first touchpoint to conversion
SELECT
product_category,
AVG(DATEDIFF(day, first_touchpoint_date, conversion_date)) as avg_days_to_conversion,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF(day, first_touchpoint_date, conversion_date)) as median_days,
COUNT(DISTINCT user_id) as converters
FROM (
SELECT
user_id,
product_category,
MIN(touchpoint_date) as first_touchpoint_date,
MAX(CASE WHEN conversion_flag = 1 THEN touchpoint_date END) as conversion_date
FROM user_journey
WHERE touchpoint_date >= CURRENT_DATE - INTERVAL '180' DAY
GROUP BY user_id, product_category
HAVING conversion_date IS NOT NULL
)
GROUP BY product_category
ORDER BY avg_days_to_conversion;Incremental Reach Analysis
Reach AnalysisMeasure incremental reach from DSP campaigns
SELECT
campaign_type,
COUNT(DISTINCT CASE WHEN dsp_exposed = 1 AND sponsored_exposed = 0 THEN user_id END) as dsp_only,
COUNT(DISTINCT CASE WHEN dsp_exposed = 0 AND sponsored_exposed = 1 THEN user_id END) as sponsored_only,
COUNT(DISTINCT CASE WHEN dsp_exposed = 1 AND sponsored_exposed = 1 THEN user_id END) as both,
COUNT(DISTINCT user_id) as total_reach
FROM user_exposure
WHERE exposure_date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY campaign_type;