نمذجة بيانات التسويق
جداول نظيفة ومضمومة
نمذجة بيانات التسويق درس مجاني في Digital Marketing Academy على CoddyKit. هذا هو الدرس 3 من أصل 4. يمكنك قراءة الدرس كاملاً أدناه مجاناً — ثم تمرن عليه مباشرة في المتصفح باستخدام محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7. هذا الدرس جزء من مسار التعلم في Digital Marketing Academy، وتقدمك يتزامن عبر الويب وتطبيق CoddyKit. تتضمن دورة Digital Marketing Academy 4 دروس في المجموع.
لماذا ننشئ نماذج البيانات؟
تكون جداول الموصلات الخام فوضوية: أسماء أعمدة غير متسقة، وعملات مختلطة، ومستويات حبيبية مختلفة، وخصائص خاصة بكل منصة. ويؤدي الاستعلام عنها مباشرة إلى أرقام خاطئة يتعذر إعادة إنتاجها.
نمذجة البيانات هي منهجية تحويل الصفوف الخام إلى جداول نظيفة ومتسقة وجاهزة للاستخدام التجاري. وفيها تُعرّف مقاييس ROAS والتحويلات والإيرادات مرة واحدة وبصورة صحيحة، حتى تتفق كل التقارير.
مخطط النجمة
النموذج التحليلي السائد هو مخطط النجمة: جدول وقائع مركزي للأحداث القابلة للقياس، تحيط به جداول أبعاد تصفها. تحتوي الوقائع على الأرقام، مثل الإنفاق والنقرات والإيرادات، بينما تحتوي الأبعاد على السياق، مثل الحملة والتاريخ والقناة والعميل.
هذا الشكل سهل الفهم للمسوقين وفعال لأدوات BI، التي تربط واقعة واحدة بعدة أبعاد لتقسيم المقاييس بحسب أي سمة.
dim_date
|
dim_channel -- fct_ad_spend -- dim_campaign
|
dim_account
fct_ad_spend (facts): impressions, clicks, cost, conversions
dims: who / what / when contextجداول الوقائع مقابل جداول الأبعاد
يكون جدول الوقائع طويلًا وقابلًا للجمع: صف واحد لكل حدث أو لكل حملة في كل يوم، مع مقاييس رقمية تجمعونها. أما جدول الأبعاد فيكون واسعًا ووَصفيًا: صف واحد لكل حملة أو عميل، مع سمات تُستخدم للتصفية والتجميع.
الاختبار هو: إذا كنتم ستستخدمون SUM عليه، فهو واقعة؛ وإذا كنتم ستستخدمون GROUP BY عليه، فهو بُعد. الإنفاق واقعة، واسم الحملة بُعد.
fct_ad_spend dim_campaign
---------------- ----------------
date campaign_id (PK)
campaign_id (FK) campaign_name
cost <-SUM-> channel
clicks <-SUM-> objective
conversions start_dateالحبيبية: القرار الأول
تحدد الحبيبية ما يمثله صف واحد من جدول الوقائع. ويُعد إعلانها أولًا أهم قرار نمذجة على الإطلاق. فخلط مستويات الحبيبية، مثل صفوف يومية مع إجماليات تراكمية، يؤدي إلى العد المزدوج وإفساد كل مقياس لاحقًا.
حددوا الحبيبية بلغة واضحة: صف واحد لكل حملة في كل يوم. ويجب أن يكون كل عمود صحيحًا عند هذا المستوى، كما يجب أن يحترم كل تحميل هذه الحبيبية.
Declared grain: one row per campaign per day
-- enforce uniqueness on the grain
SELECT date, campaign_id, COUNT(*)
FROM fct_ad_spend
GROUP BY 1,2
HAVING COUNT(*) > 1; -- must return 0 rowsنماذج التجهيز
قبل إنشاء الوقائع والأبعاد، أنشئوا نماذج التجهيز: نموذجًا واحدًا لكل جدول مصدر، يعيد تسمية الأعمدة وفق معيار موحد، ويحوّل الأنواع، ويوحد الوحدات، مثل تحويل السنتات إلى دولارات وتوحيد التواريخ بتوقيت UTC. يطابق كل نموذج تجهيز جدولًا خامًا واحدًا ولا يتجاوز ذلك.
التجهيز هو طبقة التنظيف. فهو يعزل خصائص المصادر حتى لا تضطر أسواق البيانات اللاحقة إلى معرفة أن Meta تسميه إنفاقًا وأن Google تسميه تكلفة.
-- stg_google_ads__spend
SELECT
date AS spend_date,
campaign_id,
'google' AS channel,
cost_micros / 1000000 AS cost, -- micros -> dollars
clicks,
conversions
FROM raw.google_ads__campaign_stats;دمج القنوات
تعرض كل منصة إعلانية تقاريرها بطريقة مختلفة، لكنها تشترك بعد التجهيز في بنية واحدة. ويدمج النموذج التالي هذه القنوات في واقعة إنفاق واحدة متعددة القنوات، لتشكل أساس التقارير المدمجة.
هذا الجدول الواحد هو ما يجعل حساب إجمالي ROAS ممكنًا. فعندما تتوافق كل قناة مع الأعمدة نفسها، يمكن لاستعلام واحد جمع الإنفاق عبر Google وMeta وTikTok دفعة واحدة.
-- fct_ad_spend: union all channels
SELECT * FROM stg_google_ads__spend
UNION ALL
SELECT * FROM stg_meta_ads__spend
UNION ALL
SELECT * FROM stg_tiktok_ads__spend;
-- now: SUM(cost) GROUP BY channel worksالأبعاد المتوافقة
يجب أن تكون الأبعاد متوافقة للتحليل متعدد القنوات: dim_date وdim_channel مشتركان، وتنضم إليهما كل جداول الوقائع بالطريقة نفسها. وعندها تعني عبارة "الإيرادات حسب الشهر وحسب القناة" الشيء نفسه، سواء كان المصدر إعلانات أو بريدًا إلكترونيًا أو ويب.
الأبعاد المتوافقة هي التي تتيح وضع الإنفاق والإيرادات جنبًا إلى جنب في مخطط واحد. ومن دونها، لا تتطابق عمليات الربط وتختلف الإجماليات دون ظهور واضح.
Conformed dims shared across facts:
dim_date -> joined by every fact on date
dim_channel -> 'google','meta','email','organic'
dim_campaign -> unified campaign keys
-> spend and revenue line up on the same axesالإسناد في SQL
يُسنِد الإسناد الفضل في التحويلات إلى نقاط التماس. وإسناد النقرة الأخيرة هو الأبسط: يحصل آخر مصدر تسويقي قبل التحويل على الفضل كاملًا. أما النقرة الأولى والإسناد الخطي والإسناد القائم على الموضع فتوزع الفضل بطرق مختلفة.
في مستودع البيانات، تنفذون الإسناد على هيئة نموذج، لا كصندوق أسود تابع لمنصة. ومع بيانات الأحداث على مستوى الأحداث من GA4، يمكنكم تطبيق دوال نافذة على نقاط التماس لكل مستخدم، وتطبيق أي قاعدة، ثم مقارنة النتائج بين النماذج بموضوعية.
-- last non-direct click per conversion
WITH touches AS (
SELECT user_id, channel, event_time,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY event_time DESC) AS rn
FROM web_touchpoints
WHERE channel <> 'direct'
)
SELECT channel, COUNT(*) FROM touches WHERE rn=1
GROUP BY 1;الأبعاد المتغيرة ببطء
تتغير سمات الأبعاد بمرور الوقت: فقد يتغير المسؤول عن ميزانية حملة، أو ترتقي فئة عميل. ويحافظ البُعد المتغير ببطء من النوع Type 2 على السجل التاريخي بإضافة صف جديد ذي تواريخ سريان بدلًا من الكتابة فوق الصف القديم.
وهذا مهم لإعداد تقارير دقيقة عن نقطة زمنية محددة. فلكي تعرفوا الشريحة التي كان العميل ينتمي إليها عند إجرائه التحويل، تحتاجون إلى إصدار البُعد الذي كان ساريًا آنذاك، لا إصدار اليوم.
dim_customer (SCD Type 2)
cust_id tier valid_from valid_to is_current
101 free 2026-01-01 2026-04-01 false
101 pro 2026-04-01 9999-12-31 true
-- join on event_date BETWEEN valid_from AND valid_toالاختبار والتوثيق
النماذج عبارة عن شيفرة، لذا اختبروها. تتيح لكم أدوات مثل dbt التحقق من أن المفاتيح فريدة وليست NULL، وأن قيم القنوات تقع ضمن مجموعة مقبولة، وأن العلاقات بين الجداول صحيحة.
تكتشف الاختبارات انجراف المخطط وعمليات الربط الخاطئة قبل وصولها إلى لوحة المعلومات. وبالاقتران مع التوثيق وتتبّع مصدر البيانات المُنشأين تلقائيًا، تجعل هذه الاختبارات النموذج موثوقًا وسهل ضم أعضاء جدد إلى العمل، بدلًا من كونه صندوقًا أسود هشًا.
# dbt schema test
models:
- name: fct_ad_spend
columns:
- name: campaign_id
tests: [not_null]
- name: channel
tests:
- accepted_values:
values: ['google','meta','tiktok']أسواق البيانات: الطبقة النهائية
الطبقة العليا هي أسواق البيانات: جداول جاهزة للاستخدام التجاري ومهيأة لجماهير محددة، مثل سوق marketing_performance الذي يربط الإنفاق بالإيرادات مسبقًا ويحسب ROAS لكل قناة في كل يوم.
تقرأ أدوات BI من أسواق البيانات فقط. ومن خلال إجراء الربط والتجميع مسبقًا هنا، تظل لوحات المعلومات سريعة ومنخفضة التكلفة، ويرث كل محلل التعريفات الصحيحة نفسها.
-- marts.marketing_performance (1 row / day / channel)
SELECT s.spend_date, s.channel,
SUM(s.cost) AS spend,
SUM(r.revenue) AS revenue,
SAFE_DIVIDE(SUM(r.revenue), SUM(s.cost)) AS roas
FROM fct_ad_spend s
LEFT JOIN fct_revenue r USING (spend_date, channel)
GROUP BY 1,2;تحقق سريع
أنتم تبنون جدول وقائع لأداء الإعلانات، ويجب أن تتجنبوا العد المزدوج. ما أهم شيء يجب إعلانه قبل كتابة أي أعمدة؟
مراجعة
تحوّل النمذجة الجداول الخام الفوضوية إلى بيانات موثوقة وجاهزة للاستخدام في الأعمال عبر طبقات: تنظّف طبقة staging كل مصدر وتوحّد بنيته، وتشكّل جداول الوقائع والأبعاد المتوافقة مخططًا نجميًا، بينما تنفّذ طبقة data marts عمليات الربط مسبقًا لاستخدامها في BI.
حدّد مستوى التفاصيل أولًا، واجمع القنوات باستخدام union للمقاييس المدمجة، ونفّذ الإسناد وسجلّ SCD Type 2 التاريخي في SQL، واختبر كل نموذج حتى تُكتشف الأرقام الخاطئة بوضوح بدلًا من وصولها إلى لوحة المعلومات.
تعلم Digital Marketing Academy مع معلم ذكاء اصطناعي — مجانًا
اكتب وقم بتشغيل أكوادك الفعلية في المتصفح، واحصل على مساعدة فورية من معلم ذكاء اصطناعي متاح 24/7، واستمر من حيث توقفت على الويب أو في التطبيق.
- الدورات
- 63
- الدروس
- 239
الأسئلة الشائعة
هل درس «نمذجة بيانات التسويق» مجاني؟
نعم — نص درس «نمذجة بيانات التسويق» كامل متاح مجاناً هنا على الويب. لتمرينه بشكل تفاعلي (محرر أكواد مدمج ومدرس ذكاء اصطناعي متاح 24/7) وفتح باقي دورة Digital Marketing Academy، انتقل إلى CoddyKit PRO. تتضمن دورة Digital Marketing Academy 4 دروس في المجموع.
ماذا ستتعلم في «نمذجة بيانات التسويق»؟
جداول نظيفة ومضمومة تتمرن على Digital Marketing Academy مع أكواد عملية تشغلها مباشرة في المتصفح، ومدرس ذكاء اصطناعي متاح 24/7 يجيب على أسئلتك أثناء عملك.
هل أحتاج إلى خبرة سابقة لأبدأ Digital Marketing Academy؟
لا تُشترط خبرة سابقة. Digital Marketing Academy على CoddyKit منظم للمبتدئين حتى المتقدمين، لذا يمكنك البدء من هنا أو من البداية والتقدم بسرعتك الخاصة. هذا هو الدرس 3 من أصل 4.
كم من الوقت يستغرق درس «نمذجة بيانات التسويق»؟
معظم دروس CoddyKit تستغرق حوالي 5–10 دقائق. كل منها موجز وتفاعلي، لذا تحرز تقدماً مستمراً وتستأنف من حيث توقفت عبر الويب والتطبيق.
هل يمكنني كتابة وتشغيل أكواد في درس Digital Marketing Academy هذا؟
نعم. كل درس في Digital Marketing Academy يتضمن محرر أكواد مدمج، لذا تكتب وتشغل أكواداً حقيقية مباشرة في متصفحك وتحصل على تعليقات فورية من الذكاء الاصطناعي — بدون إعداد محلي.
جميع الدروس في هذه الدورة
- لماذا نستخدم مستودع البيانات
- ETL وConnectors
- نمذجة بيانات التسويق
- لوحات معلومات تدفع إلى اتخاذ إجراء