ETL Process for the Campaign Data Import DTS Task
The Campaign Data Import DTS task imports campaign information such as advertisers, campaigns, and campaign data. The Campaign Data Import task should be run before running reports that summarize campaign data.
You use campaign data to analyze the success of your campaigns. You use Commerce Server Marketing Manager to set up and run campaigns.
The following tables list the columns extracted from the source tables in the <SiteName>_Marketing database, the transformation made, and the tables to which the data is saved in the Data Warehouse.
Source Columns from the ad_item table of the <SiteName>_Marketing database |
Transformation |
Target columns from the AdItem table in the Data Warehouse |
|---|---|---|
i_aditem_id |
Copy Column |
AdItemID |
u_aditem_customer_order_num |
Copy Column |
AdItemCustomerOrderNum |
i_aditem_days_of_week_mask |
Copy Column |
AdItemDaysOfWeekMask |
i_aditem_events_scheduled |
Copy Column |
AdItemEventsScheduled |
i_aditem_tiem_of_day_end |
Copy Column |
AdItemTimeOfDayEnd |
i_aditem_time_of_day_start |
Copy Column |
AdItemTiemOfDayStart |
i_aditem_type |
Copy Column |
AdItemType |
i_aditem_weight |
Copy Column |
AdItemWeight |
i_campitem_id |
Copy Column |
CampaignItemID |
i_industry_id |
Copy Column |
IndustryCodeID |
i_aditem_id |
Copy Column |
AdItemNum |
Source columns from the campaign table of the <SiteName>_Marketing database |
Transformation |
Target columns from the Campaign table in the Data Warehouse |
|---|---|---|
b_camp_active |
Copy Column |
CampActive |
dt_campaign_exported |
Copy Column |
CampaignExported |
dt_campaign_modified |
Copy Column |
CampaignModified |
u_campaign_modified_by |
Copy Column |
CampaignModifiedBy |
dt_camp_archived |
Copy Column |
CampArchived |
dt_camp_end |
Copy Column |
CampEnd |
i_camp_events_sched |
Copy Column |
CampEventsSched |
b_camp_level_goal |
Copy Column |
CampLevelGoal |
u_camp_name |
Copy Column |
CampName |
i_camp_id |
Copy Column |
CampNum |
dt_camp_start |
Copy Column |
CampStart |
u_camp_comments |
Copy Column |
CustomerComments |
i_customer_id |
Copy Column |
CustomerID |
i_event_type_id |
Copy Column |
EventTypeID |
Source columns from the campaign_item table of the <SiteName>_Marketing database |
Transformation |
Target columns from the CampaignItem table in the Data Warehouse |
|---|---|---|
u_campitem_active |
Copy Column |
Active |
dt_campitem_archived |
Copy Column |
ArchivedDate |
i_camp_id |
Copy Column |
CampItemNum |
u_campitem_comments |
Copy Column |
Comments |
dt_campitem_end |
Copy Column |
EndDate |
dt_campitem_exported |
Copy Column |
ExportedDate |
i_campitem_exposure_limit |
Copy Column |
ExposureLimit |
u_campitem_modified_by |
Copy Column |
ModifiedBy |
dt_campitem_modified |
Copy Column |
ModifiedDate |
u_campitem_name |
Copy Column |
Name |
dt_campitem_start |
Copy Column |
StartDate |
i_campitem_id |
Copy Column |
CampaignID |
guid_campitem_type |
Copy Column |
CampaignItemTypesID |
i_creative_id |
Copy Column |
CreativeID |
Source columns from the campaign_item_types table of the <SiteName>_Marketing database |
Transformation |
Target columns from the CampaignItemTypes table in the Data Warehouse |
|---|---|---|
u_campitem_type_name |
Copy Column |
CampItemTypeName |
u_campitem_type_table |
Copy Column |
CampItemTypeTable |
guid_campitem_type |
Copy Column |
GUIDCampItemType |
Source columns from the Creative table of the <SiteName>_Marketing database |
Transformation |
Target columns from the Creative table in the Data Warehouse |
|---|---|---|
i_creative_id |
Copy Column |
CreativeNum |
dt_creative_received |
Copy Column |
CreativeReceived |
i_creative_size_id |
Copy Column |
CreativeSizeID |
i_creative_type_id |
Copy Column |
CreativeTypeID |
i_customer_id |
Copy Column |
CustomerID |
Source columns from the creative_size table of the <SiteName>_Marketing database |
Transformation |
Target columns from the CreativeSize Report table in the Data Warehouse |
|---|---|---|
i_creative_size_id |
Copy Column |
CreativeSizeNum |
i_size_height |
Copy Column |
SizeHeight |
u_size_name |
Copy Column |
SizeName |
u_size_tag |
Copy Column |
SizeTag |
i_size_width |
Copy Column |
SizeWidth |
Source columns from the creative_type table of the <SiteName>_Marketing database |
Transformation |
Target creativetypes from the Report table in the Data Warehouse |
|---|---|---|
u_ct_name |
Copy Column |
CreativeTypeName |
i_creative_type_id |
Copy Column |
CreativeTypeNum |
text_ct_template |
Copy Column |
CreativeTypeTemplate |
Source columns from the creative_type_xref table of the <SiteName>_Marketing database |
Transformation |
Target columns from the CreativeTypeXref table in the Data Warehouse |
|---|---|---|
guid_campitem_type |
Copy Column |
CampaignItemTypesID |
i_creative_type_id |
Copy Column |
CreativeTypeID |
Source columns from the Customer table of the <SiteName>_Marketing database |
Transformation |
Target columns from the Customer table in the Data Warehouse |
|---|---|---|
u_customer_address |
Copy Column |
Address |
dt_customer_archived |
Copy Column |
Archived |
u_customer_comments |
Copy Column |
Comments |
u_customer_contact |
Copy Column |
Contact |
i_customer_id |
Copy Column |
CustomerNum |
i_customer_type |
Copy Column |
CustomerTypeNum |
u_customer_email |
Copy Column |
|
dt_customer_exported |
Copy Column |
Exported |
u_customer_fax |
Copy Column |
Fax |
dt_customer_modified |
Copy Column |
Modified |
u_customer_modifiedby |
Copy Column |
ModifiedBy |
u_customer_name |
Copy Column |
Name |
u_customer_password |
Copy Column |
PassWord |
u_customer_phone |
Copy Column |
Phone |
u_customer_url |
Copy Column |
URL |
u_customer_username |
Copy Column |
UserName |
i_industry_id |
Copy Column |
IndustryCodeID |
Source columns from the dm_item table of the <SiteName>_Marketing database |
Transformation |
Target columns from the DmItem table in the Data Warehouse |
|---|---|---|
u_dmitem_attachment |
Copy Column |
DmItemAttachment |
l_dmitem_defformat |
Copy Column |
DmItemDefformat |
u_dmitem_desc |
Copy Column |
DmItemDesc |
u_dmitem_from |
Copy Column |
DmItemFrom |
i_dmitem_id |
Copy Column |
DmItemNum |
b_dmitem_personalized |
Copy Column |
DmItemPersonalized |
u_dmitem_replyto |
Copy Column |
DmItemReplyTo |
u_dmitem_scheduleid |
Copy Column |
DmItmeScheduleId |
u_dmitem_source |
Copy Column |
DmItemSource |
u_dmitem_subj |
Copy Column |
DmItemSubj |
i_dmitem_userflags |
Copy Column |
DmItemUserFlags |
g_dmitem_listid |
Copy Column |
GUIDDmItemListId |
g_dmitem_optout |
Copy Column |
GUIDDmItemOptout |
g_dmitem_testlistid |
Copy Column |
GUIDDmItemTestListId |
i_campitem_id |
Copy Column |
CampaignItemID |
Source columns from the event_type table of the <SiteName>_Marketing database |
Transformation |
Target columns from the EventType table in the Data Warehouse |
|---|---|---|
u_event_description |
Copy Column |
EventDescription |
u_event_name |
Copy Column |
EventName |
i_event_type_id |
Copy Column |
EventTypeID |
Source columns from the industry_code table of the <SiteName>_Marketing database |
Transformation |
Target columns from the IndustryCode table in the Data Warehouse |
|---|---|---|
u_industry_code |
Copy Column |
IndustryCode |
i_industry_id |
Copy Column |
IndustryNum |
Source columns from the order_discount table of the <SiteName>_Marketing database |
Transformation |
Target columns from the OrderDiscount table in the Data Warehouse |
|---|---|---|
i_disc_award_expr |
Copy Column |
DiscAwardExpr |
i_disc_award_max |
Copy Column |
DiscAwardMax |
b_disc_click_required |
Copy Column |
DiscClickRequired |
i_disc_cond_basis |
Copy Column |
DiscCondBasis |
i_disc_cond_expr |
Copy Column |
DiscCondExpr |
mny_disc_cond_min |
Copy Column |
DiscCondMin |
u_disc_description |
Copy Column |
DiscDescription |
b_disc_disjoin_cond_award |
Copy Column |
DiscDisjointCondAward |
b_disc_expressions_used_for_display |
Copy Column |
DiscExprUsedForDisplay |
i_disc_limit |
Copy Column |
DiscLimit |
i_disc_id |
Copy Column |
DiscNum |
i_disc_offer_type |
Copy Column |
DiscOfferType |
mny_disc_offer_value |
Copy Column |
DiscOfferValue |
b_disc_order_level_discount |
Copy Column |
DiscOrderLevelDiscount |
i_disc_qty_cond_min |
Copy Column |
DiscQtyCondMin |
i_disc_rank |
Copy Column |
DiscRank |
u_disc_special_offer_type |
Copy Column |
DiscSpecialOfferType |
i_disc_template |
Copy Column |
DiscTemplate |
i_campitem_id |
Copy Column |
CampaignItemID |
Source columns from the page_group table of the <SiteName>_Marketing database |
Transformation |
Target columns from the PageGroup table in the Data Warehouse |
|---|---|---|
u_pg_description |
Copy Column |
PgDescription |
i_pg_id |
Copy Column |
PgNum |
u_pg_tag |
Copy Column |
PgTag |
Source columns from the page_group_xref table of the <SiteName>_Marketing database |
Transformation |
Target columns from the PageGroupXref table in the Data Warehouse |
|---|---|---|
i_campitem_id |
Copy Column |
CampaignItemID |
i_pg_id |
Copy Column |
PageGroupID |
Source columns from the target table of the <SiteName>_Marketing database |
Transformation |
Target columns from the Target table in the Data Warehouse |
|---|---|---|
i_target_action |
Copy Column |
TargetAction |
i_target_expr_id |
Copy Column |
TargetExprId |
i_target_id |
Copy Column |
TargetNum |
i_targroup_id |
Copy Column |
TargetGroupID |
Source columns from the target_group table of the <SiteName>_Marketing database |
Transformation |
Target columns from the TargetGroup table in the Data Warehouse |
|---|---|---|
u_targroup_name |
Copy Column |
TargetGroupName |
i_targroup_id |
Copy Column |
TargetGroupNum |
Source columns from the target_group_xref table of the <SiteName>_Marketing database |
Transformation |
Target columns from the TargetGroupXref table in the Data Warehouse |
|---|---|---|
i_campitem_id |
Copy Column |
CampaignItemID |
i_targroup_id |
Copy Column |
TargetGroupID |