dim活动表
2023-02-22 16:34:07 0 举报AI智能生成
dim活动表
作者其他创作
大纲/内容
老活动（通过activity_id筛选）
ods_leadsgeneration_leads_generation_entity
ods_leadsgeneration_leads_generation_entity
创建时间
dim.created_timestamp = ods.created_at
dim.created_timestamp = ods.created_at
创建人id
dim.owner_id = ods.owner_id
dim.owner_id = ods.owner_id
活动类型code
dim.activity_type_code = ods.type
dim.activity_type_code = ods.type
活动类型名称
dim.activity_type_name = code解析(1-webinar/直播，2-seminar/会议)
dim.activity_type_name = code解析(1-webinar/直播，2-seminar/会议)
case type when 'webinar' then 1 when 'seminar' then 2 else -127 end as activity_type_code,
case type when 'webinar' then '直播' when 'seminar' then '会议' else '' end as activity_type_name
case type when 'webinar' then '直播' when 'seminar' then '会议' else '' end as activity_type_name
活动的内部名称
dim.activity_name = ods.name
dim.activity_name = ods.name
活动的外部名称 = 活动内部名称
活动开始时间
dim.started_timestamp = ods.webinar_real_started_at
dim.started_timestamp = ods.webinar_real_started_at
备注，老活动开始和结束时间可能为空（草稿状态），这样分析选择开始和结束时间时无法命中该活动
活动结束时间
dim.end_timestamp = ods.webinar_real_ended_at
dim.end_timestamp = ods.webinar_real_ended_at
活动所属部门id
dim.owner_department_id = ods.owner_department_id
dim.owner_department_id = ods.owner_department_id
通过id查name和level是否正确
备注：id查不到对应name和level时置为空
活动所属部门名称
dim.owner_department_name
dim.owner_department_name
owner_department_id去查ods_omniuser_jing_omni_org.name
活动所属部门层级
dim.owner_department_level
dim.owner_department_level
owner_department_id去查ods_omniuser_jing_omni_org.level
一级分类id置空
一级分类name
二级分类id置空
二级分类name置空
一级分类name
二级分类id置空
二级分类name置空
'' as activity_classify_1_id,
'' as activity_classify_1_name,
'' as activity_classify_2_id,
'' as activity_classify_2_name
'' as activity_classify_1_name,
'' as activity_classify_2_id,
'' as activity_classify_2_name
成本置为0.0
0.0 as activity_cost
活动封面图置空
'' cover_picture
操作人id = ownerid
leads_generation_entity.owner_id as operator_id
草稿标识code置为-127
-127 as draft_code
草稿标识name置为空
'' as draft_name
是否为复制内容code置为-127
-127 as is_copy_code
是否为复制内容name
dim.is_copy_name
dim.is_copy_name
'' as is_copy_name
发布状态code置为-127
-127 as release_status_code
发布状态name置为空
'' as release_status_name
活动状态code
dim.ems_status_code
dim.ems_status_code
通过code查name是否正确
活动状态name
dim.ems_status_name
dim.ems_status_name
started_timestamp>当前时间，code = 1，待开始
当前时间>=starttime，且 当前时间<=endtime，code=2，进行中
备注
end_timestamp < 当前时间，code = 3，已结束
业务类型id置为-127
'' as business_unit_id
业务类型name置空
'' as business_unit_name
意向标签置空
'' as intent_tags
新活动（通过activity_id筛选）
ods_leadsgeneration_jing_activity
ods_leadsgeneration_jing_activity
创建时间
dim.created_timestamp = ods.created_timestamp
dim.created_timestamp = ods.created_timestamp
活动的内部名称
dim.activity_name = ods.activity_name
dim.activity_name = ods.activity_name
活动的外部名称
dim.activity_external_name = ods.activity_external_name
dim.activity_external_name = ods.activity_external_name
创建人id
dim.owner_id = ods.owner_id
dim.owner_id = ods.owner_id
活动类型编码
dim.activity_type_code = ods.type
dim.activity_type_code = ods.type
活动类型名称
dim.activity_type_name = code解析(1-webinar/直播，2-seminar/会议)
dim.activity_type_name = code解析(1-webinar/直播，2-seminar/会议)
case type when 'webinar' then 1 when 'seminar' then 2 else -127 end as activity_type_code,
case type when 'webinar' then '直播' when 'seminar' then '会议' else '' end as activity_type_name
case type when 'webinar' then '直播' when 'seminar' then '会议' else '' end as activity_type_name
一级分类id
dim.activity_classify_1_id
dim.activity_classify_1_id
ods_leadsgeneration_jing_activity.activity_classify 一二级 分类按照"_"隔开，分别塞在classify_id中1和2
备注：ods层classify有一个id值时，默认为一级分类，有两个id值拼接时，默认第一个id为一级分类
一级分类name
dim.activity_classify_1_name
dim.activity_classify_1_name
ods_leadsgeneration_jing_common_cate.name
其中level = 1
其中level = 1
二级分类id
dim.activity_classify_2_id
dim.activity_classify_2_id
二级分类name
dim.activity_classify_2_name
dim.activity_classify_2_name
ods_leadsgeneration_jing_common_cate.name
其中level = 2 且parentID=一级分类id
其中level = 2 且parentID=一级分类id
成本
dim.activity_cost
dim.activity_cost
取自ods层中extra字段中"cost"
活动封面图
dim.cover_picture = ods.cover_picture
dim.cover_picture = ods.cover_picture
操作人id
dim.operator_id = ods.operator_id
dim.operator_id = ods.operator_id
活动所属部门id
dim.owner_department_id = ods.owner_department_id
dim.owner_department_id = ods.owner_department_id
通过id查name和level是否正确
活动所属部门名称
dim.owner_department_name
dim.owner_department_name
owner_department_id去查ods_omniuser_jing_omni_org.name
活动所属部门层级
dim.owner_department_level
dim.owner_department_level
owner_department_id去查ods_omniuser_jing_omni_org.level
草稿标识code
dim.draft_code
dim.draft_code
通过code查name是否正确
草稿标识name
dim.draft_name
dim.draft_name
case draft when 0 then '草稿' when 1 then '非草稿' else '' end as draft_name
是否为复制内容code
dim.is_copy_code
dim.is_copy_code
通过code查name是否正确
是否为复制内容name
dim.is_copy_name
dim.is_copy_name
case is_copy when 0 then '非复制' when 1 then '复制' else '' end as is_copy_name
发布状态code
dim.release_status_code
dim.release_status_code
通过code查name是否正确
发布状态name
dim.release_status_name
dim.release_status_name
case release_status when 0 then '待发布' when 1 then '已发布' else '' end as release_status_name
活动状态code
dim.ems_status_code
dim.ems_status_code
通过code查name是否正确
活动状态name
dim.ems_status_name
dim.ems_status_name
case ems_status when 1 then '待开始' when 2 then '进行中' when 3 then '已结束' when 4 then '回放中' else '' end as ems_status_name
活动开始时间
dim.started_timestamp = ods.start_time
dim.started_timestamp = ods.start_time
活动结束时间
dim.end_timestamp = ods.end_time
dim.end_timestamp = ods.end_time
业务类型id
dim.business_unit_id = ods.business_unit
dim.business_unit_id = ods.business_unit
通过id查name是否正确
业务类型name
dim.business_unit_name
dim.business_unit_name
ods_omniuser_jing_business_unit（jing_uuid）.name
意向标签
dim.intent_tags = ods.intent_tags
dim.intent_tags = ods.intent_tags
dim.mid
dim.activity_uuid
dim.activity_uuid
mid 租户id
jing_uuid 活动id
0 条评论
下一页
