AdventureWorks - Predicting customer churn¶

NOTE: Due to featuretools' memory requirement, this notebook will not run on MyBinder.

In this notebook, we will demonstrate how getML can be used for a customer churn project using a synthetic dataset of a fictional company. We will also benchmark getML against featuretools.

Summary:

Prediction type: Classification model
Domain: Customer loyalty
Prediction target: churn
Population size: 19704

Author: Dr. Patrick Urbanke

Background¶

AdventureWorks is a fictional company that sells bicycles. It is used by Microsoft to showcase how its MS SQL Server can be used to manage business data. Since this dataset resembles a real-world customer database and it is open-source, we will use it to showcase how getML can be used for a classic customer churn project (real customer databases are not easily available for the purposes of showcasing and benchmarking, for reasons of data privacy).

The dataset has been downloaded from the CTU Prague relational learning repository (Motl and Schulte, 2015).

We will benchmark getML 's feature learning algorithms against featuretools, an open-source implementation of the propositionalization algorithm, similar to getML's FastProp.

A web frontend for getML¶

The getML monitor is a frontend built to support your work with getML. The getML monitor displays information such as the imported data frames, trained pipelines and allows easy data and feature exploration. You can launch the getML monitor here.

Where is this running?¶

Your getML live session is running inside a docker container on mybinder.org, a service built by the Jupyter community and funded by Google Cloud, OVH, GESIS Notebooks and the Turing Institute. As it is a free service, this session will shut down after 10 minutes of inactivity.

Analysis¶

Let's get started with the analysis and set up your session:

import copy
import os
from urllib import request

import numpy as np
import pandas as pd
from IPython.display import Image
import matplotlib.pyplot as plt
plt.style.use('seaborn')
%matplotlib inline  

import featuretools
import getml

getml.set_project('adventure_works')


Connected to project 'adventure_works'

1. Loading data¶

1.1 Download from source¶

We begin by downloading the data:

conn = getml.database.connect_mariadb(
    host="relational.fit.cvut.cz",
    dbname="AdventureWorks2014",
    port=3306,
    user="guest",
    password="relational"
)

conn

Connection(conn_id='default',
           dbname='AdventureWorks2014',
           dialect='mysql',
           host='relational.fit.cvut.cz',
           port=3306)

def load_if_needed(name):
    """
    Loads the data from the relational learning
    repository, if the data frame has not already
    been loaded.
    """
    if not getml.data.exists(name):
        data_frame = getml.DataFrame.from_db(
            name=name,
            table_name=name,
            conn=conn
        )
        data_frame.save()
    else:
        data_frame = getml.data.load_data_frame(name)
    return data_frame

product = load_if_needed("Product")
sales_order_detail = load_if_needed("SalesOrderDetail")
sales_order_header = load_if_needed("SalesOrderHeader")
sales_order_reason = load_if_needed("SalesOrderHeaderSalesReason")
special_offer = load_if_needed("SpecialOffer")
store = load_if_needed("Store")

product

sales_order_detail

sales_order_header

sales_order_reason

special_offer

store

1.2 Prepare data for getML¶

getML requires that we define roles for each of the columns.

product.set_role("ProductID", getml.data.roles.join_key)
product.set_role(["MakeFlag", "ProductSubcategoryID", "ProductModelID"], getml.data.roles.categorical)
product.set_role(["SafetyStockLevel", "ReorderPoint", "StandardCost", "ListPrice"], getml.data.roles.numerical)

product

sales_order_detail.set_role(["SalesOrderID", "SalesOrderDetailID", "ProductID", "SpecialOfferID"], getml.data.roles.join_key)
sales_order_detail.set_role(["OrderQty", "UnitPrice", "UnitPriceDiscount", "LineTotal"], getml.data.roles.numerical)
sales_order_detail.set_role("ModifiedDate", getml.data.roles.time_stamp)

sales_order_detail

sales_order_reason.set_role("SalesOrderID", getml.data.roles.join_key)
sales_order_reason.set_role("SalesReasonID", getml.data.roles.categorical)

sales_order_reason

special_offer.set_role(["SpecialOfferID"], getml.data.roles.join_key)
special_offer.set_role(["MinQty", "DiscountPct"], getml.data.roles.numerical)
special_offer.set_role(["Category", "Description", "Type"], getml.data.roles.categorical)
special_offer.set_role(["StartDate", "EndDate"], getml.data.roles.time_stamp)

special_offer

store.set_role(["SalesPersonID"], getml.data.roles.join_key)

store["test"] = store["ModifiedDate"].update(getml.data.random() > 0.5, "NULL")

store.set_role(["SalesPersonID"], getml.data.roles.join_key)
store.set_role(["test"], getml.data.roles.time_stamp)

store

store.to_pandas()

sales_order_header["SalesPersonIDCat"] = sales_order_header["SalesPersonID"]
sales_order_header["TerritoryIDCat"] = sales_order_header["TerritoryID"]

sales_order_header.set_role(["CustomerID", "SalesOrderID", "SalesPersonID", "TerritoryID"], getml.data.roles.join_key)
sales_order_header.set_role(
    ["RevisionNumber", "OnlineOrderFlag", "SalesPersonIDCat", "TerritoryIDCat", "ShipMethodID"], 
    getml.data.roles.categorical)
sales_order_header.set_role(["SubTotal", "TaxAmt", "Freight", "TotalDue"], getml.data.roles.numerical)
sales_order_header.set_role(["OrderDate", "DueDate", "ShipDate", "ModifiedDate"], getml.data.roles.time_stamp)

sales_order_header

We must also define customer churn. In this case, we define customer churn as a customer not making another purchase within 180 days of his or her last purchase.

Thus, the churn variable is defined as follows:

0, if another purchase by the same customer has been made within 180 days after OrderDate
1, if no purchase by the same customer has been made within 180 days after OrderDate
NULL, if max(OrderDate) - OrderDate <= 180 days

NULL targets can not be used in our analysis.

sales_order_header_pd = sales_order_header[["OrderDate", "CustomerID", "SalesOrderID"]].to_pandas()

repeat_purchases = sales_order_header_pd.merge(
    sales_order_header_pd[["OrderDate", "CustomerID"]],
    on="CustomerID",
    how="left",
)

repeat_purchases = repeat_purchases[
    repeat_purchases["OrderDate_y"] > repeat_purchases["OrderDate_x"]
]

repeat_purchases = repeat_purchases[
    repeat_purchases["OrderDate_y"] - repeat_purchases["OrderDate_x"] > pd.Timedelta('180 days')
]

repeat_purchases.groupby("SalesOrderID", as_index=False).aggregate({"CustomerID": "max"})

repeat_purchase_ids = {sid: True for sid in repeat_purchases["SalesOrderID"]}

cut_off_date = max(sales_order_header_pd["OrderDate"]) - pd.Timedelta('180 days')

churn = np.asarray([
    np.nan if order_date >= cut_off_date else 0 if order_id in repeat_purchase_ids else 1 
    for (order_date, order_id) in zip(sales_order_header_pd["OrderDate"], sales_order_header_pd["SalesOrderID"])
])

sales_order_header["churn"] = churn

sales_order_header = sales_order_header[~sales_order_header.churn.is_nan()].to_df("SalesOrderHeaderRefined")

sales_order_header.set_role("churn", getml.data.roles.target)

sales_order_header

split = getml.data.split.random(train=0.8, test=0.2)
split

container = getml.data.Container(population=sales_order_header, split=split)

container.add(
    product=product,
    sales_order_detail=sales_order_detail, 
    sales_order_header=sales_order_header, 
    sales_order_reason=sales_order_reason,
    special_offer=special_offer,
    store=store,
)

container

2. Predictive modeling¶

We loaded the data and defined the roles and units. Next, we create a getML pipeline for relational learning.

2.1 Define relational model¶

dm = getml.data.DataModel(sales_order_header.to_placeholder("population"))

dm.add(getml.data.to_placeholder(
    product=product,
    sales_order_detail=sales_order_detail, 
    sales_order_header=sales_order_header, 
    sales_order_reason=sales_order_reason,
    special_offer=special_offer,
    store=store,
))


dm.population.join(
    dm.sales_order_header,
    on="CustomerID",
    time_stamps="OrderDate",
    lagged_targets=True,
    horizon=getml.data.time.days(1),
)

dm.population.join(
    dm.sales_order_detail,
    on="SalesOrderID",
)

dm.population.join(
    dm.sales_order_reason,
    on="SalesOrderID", 
)

dm.population.join(
    dm.store,
    on="SalesPersonID", 
)

dm.sales_order_detail.join(
    dm.product,
    on="ProductID",
    relationship=getml.data.relationship.many_to_one,
)

dm.sales_order_detail.join(
    dm.special_offer,
    on="SpecialOfferID",
    relationship=getml.data.relationship.many_to_one,
)

dm

2.2 getML pipeline¶

Set-up the feature learner & predictor

seasonal = getml.preprocessors.Seasonal()

mapping = getml.preprocessors.Mapping()

fast_prop = getml.feature_learning.FastProp(
    loss_function=getml.feature_learning.loss_functions.CrossEntropyLoss,
    num_threads=1,    
    num_features=400,
)

relboost = getml.feature_learning.Relboost(
    loss_function=getml.feature_learning.loss_functions.CrossEntropyLoss,
    num_threads=1,    
)

predictor = getml.predictors.XGBoostClassifier(n_jobs=1)

Build the pipeline

pipe1 = getml.Pipeline(
    tags=['fast_prop'],
    data_model=dm,
    preprocessors=[mapping],
    feature_learners=[fast_prop],
    predictors=[predictor],
    include_categorical=True,
)

pipe1

Pipeline(data_model='population',
         feature_learners=['FastProp'],
         feature_selectors=[],
         include_categorical=True,
         loss_function=None,
         peripheral=['product', 'sales_order_detail', 'sales_order_header',
                     'sales_order_reason', 'special_offer', 'store'],
         predictors=['XGBoostClassifier'],
         preprocessors=['Mapping'],
         share_selected_features=0.5,
         tags=['fast_prop'])

pipe2 = getml.Pipeline(
    tags=['relboost'],
    data_model=dm,
    preprocessors=[seasonal, mapping],
    feature_learners=[relboost],
    predictors=[predictor],
    include_categorical=True,
)

pipe2

Pipeline(data_model='population',
         feature_learners=['Relboost'],
         feature_selectors=[],
         include_categorical=True,
         loss_function=None,
         peripheral=['product', 'sales_order_detail', 'sales_order_header',
                     'sales_order_reason', 'special_offer', 'store'],
         predictors=['XGBoostClassifier'],
         preprocessors=['Seasonal', 'Mapping'],
         share_selected_features=0.5,
         tags=['relboost'])

2.3 Model training¶

pipe1.check(container.train)

Checking data model...

Staging...
[========================================] 100%

Preprocessing...
[========================================] 100%
[========================================] 100%

Checking...
[========================================] 100%


INFO [FOREIGN KEYS NOT FOUND]: When joining POPULATION__STAGING_TABLE_1 and SALES_ORDER_REASON__STAGING_TABLE_4 over 'SalesOrderID' and 'SalesOrderID', there are no corresponding entries for 33.769352% of entries in 'SalesOrderID' in 'POPULATION__STAGING_TABLE_1'. You might want to double-check your join keys.
INFO [FOREIGN KEYS NOT FOUND]: When joining POPULATION__STAGING_TABLE_1 and STORE__STAGING_TABLE_5 over 'SalesPersonID' and 'SalesPersonID', there are no corresponding entries for 84.941548% of entries in 'SalesPersonID' in 'POPULATION__STAGING_TABLE_1'. You might want to double-check your join keys.

pipe1.fit(container.train)

Checking data model...

Staging...
[========================================] 100%


INFO [FOREIGN KEYS NOT FOUND]: When joining POPULATION__STAGING_TABLE_1 and SALES_ORDER_REASON__STAGING_TABLE_4 over 'SalesOrderID' and 'SalesOrderID', there are no corresponding entries for 33.769352% of entries in 'SalesOrderID' in 'POPULATION__STAGING_TABLE_1'. You might want to double-check your join keys.
INFO [FOREIGN KEYS NOT FOUND]: When joining POPULATION__STAGING_TABLE_1 and STORE__STAGING_TABLE_5 over 'SalesPersonID' and 'SalesPersonID', there are no corresponding entries for 84.941548% of entries in 'SalesPersonID' in 'POPULATION__STAGING_TABLE_1'. You might want to double-check your join keys.

Staging...
[========================================] 100%

Preprocessing...
[========================================] 100%
[========================================] 100%

FastProp: Trying 710 features...
[========================================] 100%

FastProp: Building features...
[========================================] 100%

XGBoost: Training as predictor...
[========================================] 100%


Trained pipeline.
Time taken: 0h:1m:7.794911

Pipeline(data_model='population',
         feature_learners=['FastProp'],
         feature_selectors=[],
         include_categorical=True,
         loss_function=None,
         peripheral=['product', 'sales_order_detail', 'sales_order_header',
                     'sales_order_reason', 'special_offer', 'store'],
         predictors=['XGBoostClassifier'],
         preprocessors=['Mapping'],
         share_selected_features=0.5,
         tags=['fast_prop', 'container-qlp0vY'])

url: http://localhost:1709/#/getpipeline/adventure_works/M49AEo/0/

pipe2.check(container.train)

Checking data model...

Staging...
[========================================] 100%

Preprocessing...
[========================================] 100%
[========================================] 100%

Checking...
[========================================] 100%


INFO [FOREIGN KEYS NOT FOUND]: When joining POPULATION__STAGING_TABLE_1 and SALES_ORDER_REASON__STAGING_TABLE_4 over 'SalesOrderID' and 'SalesOrderID', there are no corresponding entries for 33.769352% of entries in 'SalesOrderID' in 'POPULATION__STAGING_TABLE_1'. You might want to double-check your join keys.
INFO [FOREIGN KEYS NOT FOUND]: When joining POPULATION__STAGING_TABLE_1 and STORE__STAGING_TABLE_5 over 'SalesPersonID' and 'SalesPersonID', there are no corresponding entries for 84.941548% of entries in 'SalesPersonID' in 'POPULATION__STAGING_TABLE_1'. You might want to double-check your join keys.

pipe2.fit(container.train)

Checking data model...

Staging...
[========================================] 100%


INFO [FOREIGN KEYS NOT FOUND]: When joining POPULATION__STAGING_TABLE_1 and SALES_ORDER_REASON__STAGING_TABLE_4 over 'SalesOrderID' and 'SalesOrderID', there are no corresponding entries for 33.769352% of entries in 'SalesOrderID' in 'POPULATION__STAGING_TABLE_1'. You might want to double-check your join keys.
INFO [FOREIGN KEYS NOT FOUND]: When joining POPULATION__STAGING_TABLE_1 and STORE__STAGING_TABLE_5 over 'SalesPersonID' and 'SalesPersonID', there are no corresponding entries for 84.941548% of entries in 'SalesPersonID' in 'POPULATION__STAGING_TABLE_1'. You might want to double-check your join keys.

Staging...
[========================================] 100%

Preprocessing...
[========================================] 100%
[========================================] 100%

Relboost: Training features...
[========================================] 100%

Relboost: Building features...
[========================================] 100%

XGBoost: Training as predictor...
[========================================] 100%


Trained pipeline.
Time taken: 0h:1m:53.175772

Pipeline(data_model='population',
         feature_learners=['Relboost'],
         feature_selectors=[],
         include_categorical=True,
         loss_function=None,
         peripheral=['product', 'sales_order_detail', 'sales_order_header',
                     'sales_order_reason', 'special_offer', 'store'],
         predictors=['XGBoostClassifier'],
         preprocessors=['Seasonal', 'Mapping'],
         share_selected_features=0.5,
         tags=['relboost', 'container-qlp0vY'])

url: http://localhost:1709/#/getpipeline/adventure_works/xkgbq9/0/

2.4 Model evaluation¶

pipe1.score(container.test)

Staging...
[========================================] 100%

Preprocessing...
[========================================] 100%
[========================================] 100%

FastProp: Building features...
[========================================] 100%

pipe2.score(container.test)

Staging...
[========================================] 100%

Preprocessing...
[========================================] 100%
[========================================] 100%

Relboost: Building features...
[========================================] 100%

2.5 featuretools¶

population_train_pd = container.train.population.drop(container.train.population.roles.unused).to_pandas()
population_test_pd = container.test.population.drop(container.train.population.roles.unused).to_pandas()

product_pd = product.drop(product.roles.unused).to_pandas()
sales_order_detail_pd = sales_order_detail.drop(sales_order_detail.roles.unused).to_pandas()
sales_order_header_pd = sales_order_header.drop(sales_order_header.roles.unused).to_pandas()
sales_order_reason_pd = sales_order_reason.drop(sales_order_reason.roles.unused).to_pandas()
special_offer_pd = special_offer.drop(special_offer.roles.unused).to_pandas()
store_pd = store.drop(store.roles.unused).to_pandas()

featuretools does not support many-to-one joins. Therefore, we must manually merge sales_order_detail_pd, product_pd and special_offer_pd.

sales_order_detail_pd = sales_order_detail_pd.merge(
    product_pd,
    on="ProductID",
    how="left",
)

sales_order_detail_pd = sales_order_detail_pd.merge(
    special_offer_pd,
    on="SpecialOfferID",
    how="left",
)

del sales_order_detail_pd["SalesOrderDetailID"]
del sales_order_detail_pd["ProductID"]
del sales_order_detail_pd["SpecialOfferID"]

sales_order_detail_pd

def prepare_sales_order_header(peripheral_pd, train_or_test):
    """
    Helper function that imitates the behavior of 
    the data model defined above.
    """
    peripheral_new = peripheral_pd.merge(
        train_or_test[["CustomerID", "OrderDate", "SalesOrderID"]],
        on="CustomerID"
    )

    peripheral_new = peripheral_new[
        peripheral_new["OrderDate_x"] < peripheral_new["OrderDate_y"]
    ]
    
    del peripheral_new["SalesOrderID_x"]
    del peripheral_new["OrderDate_y"]
    del peripheral_new["CustomerID"]
    del peripheral_new["SalesPersonIDCat"]
    del peripheral_new["TerritoryIDCat"]

    return peripheral_new.rename(columns={"OrderDate_x": "OrderDate", "SalesOrderID_y": "SalesOrderID"})

def prepare_store(peripheral_pd, train_or_test):
    """
    Helper function that imitates the behavior of 
    the data model defined above.
    """
    peripheral_new = peripheral_pd.merge(
        train_or_test[["SalesPersonID", "SalesOrderID"]],
        on="SalesPersonID"
    )
    
    return peripheral_new

store_train_pd = prepare_store(store_pd, population_train_pd)
store_test_pd = prepare_store(store_pd, population_test_pd)
store_train_pd

sales_order_header_train_pd = prepare_sales_order_header(sales_order_header_pd, population_train_pd)
sales_order_header_test_pd = prepare_sales_order_header(sales_order_header_pd, population_test_pd)
sales_order_header_train_pd

del population_train_pd["CustomerID"]
del population_train_pd["SalesPersonIDCat"]

del population_test_pd["CustomerID"]
del population_test_pd["SalesPersonIDCat"]

featuretools's entities are similar for getML's container class.

entities_train = {
    "population" : (population_train_pd, "SalesOrderID"),
    "sales_order_header": (sales_order_header_train_pd, "index"),
    "sales_order_detail": (sales_order_detail_pd, "index"),
    "sales_order_reason": (sales_order_reason_pd, "index"),
    "store": (store_train_pd, "index"),
}

entities_test = {
    "population" : (population_test_pd, "SalesOrderID"),
    "sales_order_header": (sales_order_header_test_pd, "index"),
    "sales_order_detail": (sales_order_detail_pd, "index"),
    "sales_order_reason": (sales_order_reason_pd, "index"),
    "store": (store_test_pd, "index"),
}

featuretools's relationships are similar for getML's data model.

relationships = [
    ("population", "SalesOrderID", "sales_order_header", "SalesOrderID"),
    ("population", "SalesOrderID", "sales_order_detail", "SalesOrderID"),
    ("population", "SalesOrderID", "sales_order_reason", "SalesOrderID"),
    ("population", "SalesOrderID", "store", "SalesOrderID"),
]

featuretools_train_pd = featuretools.dfs(
    entities=entities_train,
    relationships=relationships,
    target_entity="population")[0]

/home/patrick/.local/lib/python3.9/site-packages/featuretools/entityset/entity.py:462: UserWarning: index index not found in dataframe, creating new integer column
  warnings.warn("index {} not found in dataframe, creating new "

featuretools_test_pd = featuretools.dfs(
    entities=entities_test,
    relationships=relationships,
    target_entity="population")[0]

featuretools_train = getml.data.DataFrame.from_pandas(featuretools_train_pd, "featuretools_train")
featuretools_test = getml.data.DataFrame.from_pandas(featuretools_test_pd, "featuretools_test")

featuretools_train.set_role("churn", getml.data.roles.target)
featuretools_train.set_role(featuretools_train.roles.unused_float, getml.data.roles.numerical)
featuretools_train.set_role(featuretools_train.roles.unused_string, getml.data.roles.categorical)

featuretools_train

91.014218% of all entries of column 'SKEW(sales_order_header.Freight)' are NULL values.
91.014218% of all entries of column 'SKEW(sales_order_header.SubTotal)' are NULL values.
91.014218% of all entries of column 'SKEW(sales_order_header.TaxAmt)' are NULL values.
91.014218% of all entries of column 'SKEW(sales_order_header.TotalDue)' are NULL values.
91.014218% of all entries of column 'SKEW(sales_order_header.churn)' are NULL values.

name	SalesOrderID	SalesOrderDetailID	OrderQty	ProductID	SpecialOfferID	CarrierTrackingNumber	UnitPrice	UnitPriceDiscount	LineTotal	rowguid	ModifiedDate
role	unused_float	unused_float	unused_float	unused_float	unused_float	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string
0	43659	1	1	776	1	4911-403C-98	2024.9940	0.0000	2024.994000	B207C96D-D9E6-402B-8470-2CC176C4...	2011-05-31 00:00:00
1	43659	2	3	777	1	4911-403C-98	2024.9940	0.0000	6074.982000	7ABB600D-1E77-41BE-9FE5-B9142CFC...	2011-05-31 00:00:00
2	43659	3	1	778	1	4911-403C-98	2024.9940	0.0000	2024.994000	475CF8C6-49F6-486E-B0AD-AFC6A50C...	2011-05-31 00:00:00
3	43659	4	1	771	1	4911-403C-98	2039.9940	0.0000	2039.994000	04C4DE91-5815-45D6-8670-F462719F...	2011-05-31 00:00:00
4	43659	5	1	772	1	4911-403C-98	2039.9940	0.0000	2039.994000	5A74C7D2-E641-438E-A7AC-37BF2328...	2011-05-31 00:00:00
	...	...	...	...	...	...	...	...	...	...	...
121312	75122	121313	1	878	1	NULL	21.9800	0.0000	21.980000	8CAD6675-18CC-4F47-8287-97B41A8E...	2014-06-30 00:00:00
121313	75122	121314	1	712	1	NULL	8.9900	0.0000	8.990000	84F1C363-1C50-4442-BE16-541C59B6...	2014-06-30 00:00:00
121314	75123	121315	1	878	1	NULL	21.9800	0.0000	21.980000	C18B6476-429F-4BB1-828E-2BE5F82A...	2014-06-30 00:00:00
121315	75123	121316	1	879	1	NULL	159.0000	0.0000	159.000000	75A89C6A-C60A-47EA-8A52-B52A9C43...	2014-06-30 00:00:00
121316	75123	121317	1	712	1	NULL	8.9900	0.0000	8.990000	73646D26-0461-450D-8019-2C6C8586...	2014-06-30 00:00:00

name	SalesOrderID	RevisionNumber	Status	OnlineOrderFlag	CustomerID	SalesPersonID	TerritoryID	BillToAddressID	ShipToAddressID	ShipMethodID	CreditCardID	CurrencyRateID	OrderDate	DueDate	ShipDate	SalesOrderNumber	PurchaseOrderNumber	AccountNumber	CreditCardApprovalCode	SubTotal	TaxAmt	Freight	TotalDue	Comment	rowguid	ModifiedDate
role	unused_float	unused_float	unused_float	unused_float	unused_float	unused_float	unused_float	unused_float	unused_float	unused_float	unused_float	unused_float	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string
0	43659	8	5	0	29825	279	5	985	985	5	16281	nan	2011-05-31 00:00:00	2011-06-12 00:00:00	2011-06-07 00:00:00	SO43659	PO522145787	10-4020-000676	105041Vi84182	20565.6206	1971.5149	616.0984	23153.2339	NULL	79B65321-39CA-4115-9CBA-8FE0903E...	2011-06-07 00:00:00
1	43660	8	5	0	29672	279	5	921	921	5	5618	nan	2011-05-31 00:00:00	2011-06-12 00:00:00	2011-06-07 00:00:00	SO43660	PO18850127500	10-4020-000117	115213Vi29411	1294.2529	124.2483	38.8276	1457.3288	NULL	738DC42D-D03B-48A1-9822-F95A67EA...	2011-06-07 00:00:00
2	43661	8	5	0	29734	282	6	517	517	5	1346	4	2011-05-31 00:00:00	2011-06-12 00:00:00	2011-06-07 00:00:00	SO43661	PO18473189620	10-4020-000442	85274Vi6854	32726.4786	3153.7696	985.5530	36865.8012	NULL	D91B9131-18A4-4A11-BC3A-90B6F53E...	2011-06-07 00:00:00
3	43662	8	5	0	29994	282	6	482	482	5	10456	4	2011-05-31 00:00:00	2011-06-12 00:00:00	2011-06-07 00:00:00	SO43662	PO18444174044	10-4020-000227	125295Vi53935	28832.5289	2775.1646	867.2389	32474.9324	NULL	4A1ECFC0-CC3A-4740-B028-1C50BB48...	2011-06-07 00:00:00
4	43663	8	5	0	29565	276	4	1073	1073	5	4322	nan	2011-05-31 00:00:00	2011-06-12 00:00:00	2011-06-07 00:00:00	SO43663	PO18009186470	10-4020-000510	45303Vi22691	419.4589	40.2681	12.5838	472.3108	NULL	9B1E7A40-6AE0-4AD3-811C-A6495185...	2011-06-07 00:00:00
	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
31460	75119	8	5	1	11981	nan	1	17649	17649	1	6761	nan	2014-06-30 00:00:00	2014-07-12 00:00:00	2014-07-07 00:00:00	SO75119	NULL	10-4030-011981	429826Vi35166	42.2800	3.3824	1.0570	46.7194	NULL	9382F1C9-383A-435F-9449-0EECEA21...	2014-07-07 00:00:00
31461	75120	8	5	1	18749	nan	6	28374	28374	1	8925	nan	2014-06-30 00:00:00	2014-07-12 00:00:00	2014-07-07 00:00:00	SO75120	NULL	10-4030-018749	929849Vi46003	84.9600	6.7968	2.1240	93.8808	NULL	AE6A4FCF-FF73-4CD4-AF2C-5993D00D...	2014-07-07 00:00:00
31462	75121	8	5	1	15251	nan	6	26553	26553	1	14220	nan	2014-06-30 00:00:00	2014-07-12 00:00:00	2014-07-07 00:00:00	SO75121	NULL	10-4030-015251	529864Vi73738	74.9800	5.9984	1.8745	82.8529	NULL	D7395C0E-00CB-4BFA-A238-0D6A9F49...	2014-07-07 00:00:00
31463	75122	8	5	1	15868	nan	6	14616	14616	1	18719	nan	2014-06-30 00:00:00	2014-07-12 00:00:00	2014-07-07 00:00:00	SO75122	NULL	10-4030-015868	330022Vi97312	30.9700	2.4776	0.7743	34.2219	NULL	4221035A-4159-492F-AF40-4363A64F...	2014-07-07 00:00:00
31464	75123	8	5	1	18759	nan	6	14024	14024	1	10084	nan	2014-06-30 00:00:00	2014-07-12 00:00:00	2014-07-07 00:00:00	SO75123	NULL	10-4030-018759	230370Vi51970	189.9700	15.1976	4.7493	209.9169	NULL	D54752FF-2B54-4BE5-95EA-3B72289C...	2014-07-07 00:00:00

name	SalesOrderID	SalesReasonID	ModifiedDate
role	unused_float	unused_float	unused_string
0	43697	5	2011-05-31 00:00:00
1	43697	9	2011-05-31 00:00:00
2	43702	5	2011-06-01 00:00:00
3	43702	9	2011-06-01 00:00:00
4	43703	5	2011-06-01 00:00:00
	...	...	...
27642	75119	1	2014-06-30 00:00:00
27643	75120	1	2014-06-30 00:00:00
27644	75121	1	2014-06-30 00:00:00
27645	75122	1	2014-06-30 00:00:00
27646	75123	1	2014-06-30 00:00:00

name	SpecialOfferID	MinQty	MaxQty	Description	DiscountPct	Type	Category	StartDate	EndDate	rowguid	ModifiedDate
role	unused_float	unused_float	unused_float	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string	unused_string
0	1	0	nan	No Discount	0.0000	No Discount	No Discount	2011-05-01 00:00:00	2014-11-30 00:00:00	0290C4F5-191F-4337-AB6B-0A2DDE03...	2011-04-01 00:00:00
1	2	11	14	Volume Discount 11 to 14	0.0200	Volume Discount	Reseller	2011-05-31 00:00:00	2014-05-30 00:00:00	D7542EE7-15DB-4541-985C-5CC27AEF...	2011-05-01 00:00:00
2	3	15	24	Volume Discount 15 to 24	0.0500	Volume Discount	Reseller	2011-05-31 00:00:00	2014-05-30 00:00:00	4BDBCC01-8CF7-40A9-B643-40EC5B71...	2011-05-01 00:00:00
3	4	25	40	Volume Discount 25 to 40	0.1000	Volume Discount	Reseller	2011-05-31 00:00:00	2014-05-30 00:00:00	504B5E85-8F3F-4EBC-9E1D-C1BC5DEA...	2011-05-01 00:00:00
4	5	41	60	Volume Discount 41 to 60	0.1500	Volume Discount	Reseller	2011-05-31 00:00:00	2014-05-30 00:00:00	677E1D9D-944F-4E81-90E8-47EB0A82...	2011-05-01 00:00:00
	...	...	...	...	...	...	...	...	...	...	...
11	12	0	nan	LL Road Frame Sale	0.3500	Excess Inventory	Reseller	2013-05-30 00:00:00	2013-07-14 00:00:00	C0AF1C89-9722-4235-9248-3FBA4D9E...	2013-04-30 00:00:00
12	13	0	nan	Touring-3000 Promotion	0.1500	New Product	Reseller	2013-05-30 00:00:00	2013-08-29 00:00:00	5061CCE4-E021-45A8-9A75-DFB36CBB...	2013-04-30 00:00:00
13	14	0	nan	Touring-1000 Promotion	0.2000	New Product	Reseller	2013-05-30 00:00:00	2013-08-29 00:00:00	1AF84A9E-A98C-4BD9-B48F-DC2B8B6B...	2013-04-30 00:00:00
14	15	0	nan	Half-Price Pedal Sale	0.5000	Seasonal Discount	Customer	2013-07-14 00:00:00	2013-08-14 00:00:00	03E3594D-6EBB-46A6-B8EE-A9289C0C...	2013-06-14 00:00:00
15	16	0	nan	Mountain-500 Silver Clearance Sa...	0.4000	Discontinued Product	Reseller	2014-03-31 00:00:00	2014-05-30 00:00:00	EB7CB484-BCCF-4D2D-BF73-521B2001...	2014-03-01 00:00:00

name	BusinessEntityID	SalesPersonID	Name	Demographics	rowguid	ModifiedDate
role	unused_float	unused_float	unused_string	unused_string	unused_string	unused_string
0	292	279	Next-Door Bike Store	<StoreSurvey xmlns="http://schem...	A22517E3-848D-4EBE-B9D9-7437F343...	2014-09-12 11:15:07
1	294	276	Professional Sales and Service	<StoreSurvey xmlns="http://schem...	B50CA50B-C601-4A13-B07E-2C63862D...	2014-09-12 11:15:07
2	296	277	Riders Company	<StoreSurvey xmlns="http://schem...	337C3688-1339-4E1A-A08A-B54B2356...	2014-09-12 11:15:07
3	298	275	The Bike Mechanics	<StoreSurvey xmlns="http://schem...	7894F278-F0C8-4D16-BD75-213FDBF1...	2014-09-12 11:15:07
4	300	286	Nationwide Supply	<StoreSurvey xmlns="http://schem...	C3FC9705-A8C4-4F3A-9550-EB2FA4B7...	2014-09-12 11:15:07
	...	...	...	...	...	...
696	1988	282	Retreat Inn	<StoreSurvey xmlns="http://schem...	EA21EC81-1BFA-4A07-9B4D-73D9852A...	2014-09-12 11:15:07
697	1990	281	Technical Parts Manufacturing	<StoreSurvey xmlns="http://schem...	C8E3C4ED-8F58-4DB2-B600-E0CD11D9...	2014-09-12 11:15:07
698	1992	277	Totes & Baskets Company	<StoreSurvey xmlns="http://schem...	CE860B58-643C-4567-BFD8-06E97969...	2014-09-12 11:15:07
699	1994	277	World of Bikes	<StoreSurvey xmlns="http://schem...	0C10F2B6-A13A-440C-9C25-5B28D482...	2014-09-12 11:15:07
700	2051	275	A Bicycle Association	<StoreSurvey xmlns="http://schem...	82237172-D3FE-4A95-82EF-636F6552...	2014-09-12 11:15:07