blob: f2873f4d3b401be6c1d02781026c5c1053d9a88e (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
#!/usr/bin/perl
use Text::CSV;
use Data::Dumper;
# cw_products
# 0 id 1 sku 2 product_name
# cw_skus
# 0 sku_id 1 sku 2 product_id
# cw_orders
# 0 order_id 1 date 2 status 3 customer_id
# cw_order_skus
# 0 id 1 order_id 2 sku_id
# cw_customers
# 0 customer_id 1 customer_type 2 date_added 3 date_modified 4 first_name 5 last_name
@products = load_csv("cw_products.csv");
@skus = load_csv("cw_skus.csv");
@orders = load_csv("cw_orders.csv");
@order_skus = load_csv("cw_order_skus.csv");
@customers = load_csv("cw_customers.csv");
$product_name_lookup = {};
for $product (@products) {
$sku = $product->[1];
$name = $product->[2];
$product_name_lookup->{ $sku } = $name;
}
$customer_name_lookup = {};
for $customer (@customers) {
$id = $customer->[0];
$name = $customer->[5] . " " . $customer->[4];
$customer_name_lookup->{ $id } = $name;
}
$sku_id_lookup = {};
for $sku (@skus) {
$sku_id = $sku->[0];
$sku_name = $sku->[1];
$sku_id_lookup->{ $sku_id } = $sku_name;
}
$customers_seen = {};
for $order (@orders) {
$order_id = $order->[0];
$customer_id = $order->[3];
if (exists( $customers_seen->{ $customer_id } )) {
# print $customer_name_lookup->{ $customer_id } . "\n";
$customers_seen->{ $customer_id } += 1;
}
else {
$customers_seen->{ $customer_id } = 1;
}
}
$repeat_customers = 0;
$total_customers = 0;
for $customer_id (keys %$customers_seen) {
$count = $customers_seen->{ $customer_id };
$total_customers += 1;
if ( $count > 1 ) {
# print $count . ": " . $customer_name_lookup->{ $customer_id } . "\n";
$repeat_customers += 1;
}
}
print "total customers: " . $total_customers . "\n";
print "repeat customers: " . $repeat_customers . "\n";
# print Dumper( $sku_id_lookup );
sub load_csv () {
my $filename = shift;
my @rows;
my $csv = Text::CSV->new ( { binary => 1 } ) # should set binary attribute.
or die "Cannot use CSV: ".Text::CSV->error_diag ();
open my $fh, "<:encoding(utf8)", $filename or die "$filename: $!";
while ( my $row = $csv->getline( $fh ) ) {
push @rows, $row;
}
$csv->eof or $csv->error_diag();
close $fh;
return @rows;
}
|
