Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add product name, product category, product sku, product price segments #15144

Merged
merged 7 commits into from Nov 18, 2019
Merged

Add product name, product category, product sku, product price segments #15144

merged 7 commits into from Nov 18, 2019

Conversation

diosmosis
Copy link
Member

Fixes #4519

@diosmosis diosmosis added not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. Needs Review PRs that need a code review labels Nov 11, 2019
@diosmosis diosmosis added this to the 3.13.0 milestone Nov 11, 2019
@sgiehl
Copy link
Member

sgiehl commented Nov 13, 2019

Some tests need to be updated

@tsteur
Copy link
Member

tsteur commented Nov 15, 2019

@diosmosis gave it a test and it worked except for one thing maybe. I was comparing the data shown in the UI with the raw data in my DB and noticed a difference. Eg it was showing 51 unique visitors and 51 visits, although there were only 42 matching visits and 42 unique visitors.

It turns out there is a deleted flag in the log_conversion_item column and it seems this may need to be considered in the archiver query to only look where deleted=0 and eg productName matches XYZ.

Here's some of my example data:

INSERT INTO `piwik_log_conversion_item` (`idsite`, `idvisitor`, `server_time`, `idvisit`, `idorder`, `idaction_sku`, `idaction_name`, `idaction_category`, `idaction_category2`, `idaction_category3`, `idaction_category4`, `idaction_category5`, `price`, `quantity`, `deleted`)
VALUES
	(1, X'9946AC9808FDB2EB', '2019-08-01 01:31:32', 5284756, '882e30fd5b407fa47ad7d0ca0671bb93b48ebe52', 542431, 533619, 533581, 0, 0, 0, 0, 49, 3, 0),
	(1, X'43ADCA4B6D879888', '2019-08-01 01:31:50', 5284759, '54cd1108dc68f897342675ada91ce72c7cdcb1f8', 542456, 533619, 533581, 0, 0, 0, 0, 18, 3, 1),
	(1, X'43ADCA4B6D879888', '2019-08-01 01:31:50', 5284759, '54cd1108dc68f897342675ada91ce72c7cdcb1f8', 542467, 533619, 533581, 0, 0, 0, 0, 43, 4, 1),
	(1, X'43ADCA4B6D879888', '2019-08-01 01:31:32', 5284759, 'c3376a56bfb9773c010cdc39cabcd4d25b02cd5b', 542443, 533619, 533581, 0, 0, 0, 0, 17, 2, 0),
	(1, X'4389A6346DBF18DD', '2019-08-01 01:31:50', 5284770, '32becf38b088f6ab4e65bd720ce3ef3ea78422c6', 542520, 533619, 533581, 0, 0, 0, 0, 20, 1, 0),
	(1, X'30021056B76DC355', '2019-08-01 01:31:50', 5284775, '56ae6449e7f8266c648c99bbd1d945d2e8d76841', 542545, 533619, 533581, 0, 0, 0, 0, 63, 2, 1),
	(1, X'F96ED901221B4F4C', '2019-08-01 01:32:05', 5284777, 'f23d2fb4924511d2fe3f01797e591cad89663ed8', 542617, 533619, 533581, 0, 0, 0, 0, 62, 3, 1),
	(1, X'2F46F57FF9383A46', '2019-08-01 01:32:24', 5284821, '0', 542863, 533619, 533581, 0, 0, 0, 0, 80, 1, 0),
	(1, X'2F46F57FF9383A46', '2019-08-01 01:32:24', 5284821, '77b47e5171e95285baade213dbd10c298d787d00', 542856, 533619, 533581, 0, 0, 0, 0, 90, 4, 1),
	(1, X'1ECCAB33E085C623', '2019-08-01 01:32:24', 5284822, '0', 542876, 533619, 533581, 0, 0, 0, 0, 39, 4, 0),
	(1, X'1ECCAB33E085C623', '2019-08-01 01:32:24', 5284822, '0add0433800f92cd85408449068b928d96ae972a', 542866, 533619, 533581, 0, 0, 0, 0, 61, 1, 0),
	(1, X'6E7E40902A730399', '2019-08-01 01:32:44', 5284826, '4e225123a938be05f5b69ca69d2e7e0736136651', 542901, 533619, 533581, 0, 0, 0, 0, 45, 4, 0),
	(1, X'822AB826849929C1', '2019-08-01 00:47:13', 5284833, '0', 542924, 533619, 533581, 0, 0, 0, 0, 52, 4, 0),
	(1, X'250AD0DACCFE6647', '2019-08-01 01:32:59', 5284841, 'c184d01e8e8f86f477484692e3d9c7db7c03fb47', 542967, 533619, 533581, 0, 0, 0, 0, 14, 2, 0),
	(1, X'D873916482EE827D', '2019-08-01 01:32:59', 5284843, '0', 543008, 533619, 533581, 0, 0, 0, 0, 58, 2, 1),
	(1, X'D873916482EE827D', '2019-08-01 01:32:59', 5284843, '5c399f1d3c1b6c0fc48f206851bdd70dd9f9ee6b', 542987, 533619, 533581, 0, 0, 0, 0, 12, 4, 1),
	(1, X'F47635C97EF9C3C4', '2019-08-01 01:32:59', 5284851, '0', 543099, 533619, 533581, 0, 0, 0, 0, 9, 4, 0),
	(1, X'85E4CEB3C5542139', '2019-08-01 01:11:20', 5284853, '0', 543131, 533619, 533581, 0, 0, 0, 0, 82, 3, 0),
	(1, X'BAFD0D49D7CB516C', '2019-08-01 01:33:46', 5284869, '0', 543233, 533619, 533581, 0, 0, 0, 0, 72, 4, 0),
	(1, X'2EC55881691A5D4D', '2019-08-01 01:33:46', 5284880, '69614e764a3563e1701b0c9e3185081c1e5b3891', 538540, 533619, 533581, 0, 0, 0, 0, 95, 2, 1),
	(1, X'5B3EADD67D09A79A', '2019-08-01 01:33:46', 5284882, '186ab00f8a52e0bed5fc1270087198362acd3cc6', 543265, 533619, 533581, 0, 0, 0, 0, 7, 1, 0),
	(1, X'6FBB05644B2277A0', '2019-08-01 01:33:46', 5284885, '0', 543280, 533619, 533581, 0, 0, 0, 0, 78, 1, 1),
	(1, X'6FBB05644B2277A0', '2019-08-01 01:33:46', 5284885, '0', 543284, 533619, 533581, 0, 0, 0, 0, 29, 2, 0),
	(1, X'DEE87A2EB6B2156B', '2019-08-01 01:33:46', 5284887, '0', 543297, 533619, 533581, 0, 0, 0, 0, 90, 1, 0),
	(1, X'153556637476AF27', '2019-08-01 01:33:46', 5284904, '32047ba433e70eca485ffa78123ac836fd7b4edf', 543331, 533619, 533581, 0, 0, 0, 0, 92, 4, 0),
	(1, X'52DB29BC9091D1B0', '2019-08-01 01:33:46', 5284905, '5d752eda71b0e23faea7d9884061418bbbc26d8d', 541429, 533619, 533581, 0, 0, 0, 0, 49, 4, 0),
	(1, X'65FCC9EE2CEC35B2', '2019-08-01 01:33:56', 5284923, '0', 543403, 533619, 533581, 0, 0, 0, 0, 34, 4, 1),
	(1, X'0F4355A8CB1A1F14', '2019-08-01 01:33:56', 5284928, '0', 543426, 533619, 533581, 0, 0, 0, 0, 19, 2, 0),
	(1, X'5BB087A08A7EAABC', '2019-08-01 01:34:09', 5284951, '0', 543533, 533619, 533581, 0, 0, 0, 0, 90, 1, 0),
	(1, X'BFF2FF16B8DBEBE1', '2019-08-01 01:34:09', 5284956, '0', 543579, 533619, 533581, 0, 0, 0, 0, 19, 1, 0),
	(1, X'4927C49C85B19923', '2019-08-01 01:34:09', 5284969, '0', 543644, 533619, 533581, 0, 0, 0, 0, 84, 2, 0),
	(1, X'2E59DC2338050DAB', '2019-08-01 01:34:09', 5284970, 'd66fdcb22a73cedf667cb149110fdb672fb7506b', 543662, 533619, 533581, 0, 0, 0, 0, 84, 1, 0),
	(1, X'3019FBE971F99E38', '2019-08-01 01:34:28', 5284973, '86b9949036673271ebca755d07b5ac5e7e3eada3', 543677, 533619, 533581, 0, 0, 0, 0, 98, 3, 0),
	(1, X'78D91B783488D7A4', '2019-08-01 01:36:35', 5284995, '0', 543826, 533619, 533581, 0, 0, 0, 0, 78, 3, 0),
	(1, X'94ECABF03CD0E94B', '2019-08-01 01:36:45', 5285015, 'eb011be127674b271258a04c603eb6c970725521', 543964, 533619, 533581, 0, 0, 0, 0, 39, 2, 0),
	(1, X'05153BF196934B56', '2019-08-01 01:36:45', 5285023, '468b7678f3aa4a0404a2b032ad3f7f0c2eb63591', 544008, 533619, 533581, 0, 0, 0, 0, 32, 3, 0),
	(1, X'A94AF09C45524370', '2019-08-01 01:36:45', 5285027, '42bac7cc848cdf2fb0c8767cf5f3af26304f4fc7', 544029, 533619, 533581, 0, 0, 0, 0, 86, 3, 1),
	(1, X'EAE839D6E4DF34C2', '2019-08-01 01:38:20', 5285032, 'e8f21bc38c2a386c66cb3e8060fcdf0ecca7b943', 544068, 533619, 533581, 0, 0, 0, 0, 61, 1, 1),
	(1, X'EAE839D6E4DF34C2', '2019-08-01 01:38:20', 5285032, 'e8f21bc38c2a386c66cb3e8060fcdf0ecca7b943', 544075, 533619, 533581, 0, 0, 0, 0, 80, 1, 0),
	(1, X'00C4065D628489E6', '2019-08-01 00:31:29', 5285039, '0', 544115, 533619, 533581, 0, 0, 0, 0, 77, 2, 0),
	(1, X'D8663727CA0BE42E', '2019-08-01 01:42:13', 5285047, '0', 544178, 533619, 533581, 0, 0, 0, 0, 68, 2, 0),
	(1, X'BCBBF35E2E121F87', '2019-08-01 01:42:13', 5285059, '0', 544228, 533619, 533581, 0, 0, 0, 0, 17, 4, 0),
	(1, X'908D36CB5CF6A6A2', '2019-08-01 01:42:13', 5285062, '400f3e17ad77161bd7c9db2ff5cde2aff6b20e32', 544252, 533619, 533581, 0, 0, 0, 0, 23, 2, 0),
	(1, X'8A6622009BF9BF11', '2019-08-01 01:42:13', 5285065, '0', 535594, 533619, 533581, 0, 0, 0, 0, 31, 4, 0),
	(1, X'9D2C981493CE6C3A', '2019-08-01 01:42:13', 5285066, '0', 544301, 533619, 533581, 0, 0, 0, 0, 80, 1, 0),
	(1, X'1139E3A32EAECC7F', '2019-08-01 01:42:13', 5285072, '0', 544315, 533619, 533581, 0, 0, 0, 0, 66, 1, 1),
	(1, X'F15F359F7DF04218', '2019-08-01 01:42:31', 5285076, '02710293a055eb46bd5bf2764e3fe999c82d7e92', 544377, 533619, 533581, 0, 0, 0, 0, 19, 4, 0),
	(1, X'1CE4AA98FDBEBC95', '2019-08-01 01:42:43', 5285086, 'e92f316cd4ffaa232e1de75af16a71ad69751a94', 544420, 533619, 533581, 0, 0, 0, 0, 55, 3, 0),
	(1, X'96B79364E8909A4C', '2019-08-01 01:42:43', 5285090, '3b62105ca14ead0294129972955ddf253bcccf50', 544443, 533619, 533581, 0, 0, 0, 0, 21, 1, 0),
	(1, X'30E72B77524C696E', '2019-08-01 01:42:43', 5285097, '832e0962e99b8bb7f95c686099bee27c562c034e', 544470, 533619, 533581, 0, 0, 0, 0, 86, 2, 1),
	(1, X'730C925A60049F08', '2019-08-01 01:42:43', 5285101, '75dfa5bb9ceb8eed651dc7be3b388312897f3cbe', 544495, 533619, 533581, 0, 0, 0, 0, 75, 4, 0),
	(1, X'588ECE45383F6A93', '2019-08-01 01:42:43', 5285103, '0', 544509, 533619, 533581, 0, 0, 0, 0, 5, 2, 0),
	(1, X'8A24C3762EC974B5', '2019-08-01 01:42:43', 5285105, '0', 544528, 533619, 533581, 0, 0, 0, 0, 18, 2, 0),
	(1, X'62BF1FE1C89E6C1F', '2019-08-01 01:42:43', 5285122, '77777c4cbd0028ef21972b02ebe8cbe9e07ef3bc', 544584, 533619, 533581, 0, 0, 0, 0, 73, 2, 0),
	(1, X'7CF9632400130BCC', '2019-08-01 01:42:43', 5285127, '99808c25ddaa78e09b9911a027101d94ed17808d', 544615, 533619, 533581, 0, 0, 0, 0, 49, 1, 1),
	(1, X'377389028E10DA82', '2019-08-01 01:43:12', 5285136, '0', 544670, 533619, 533581, 0, 0, 0, 0, 52, 2, 0),
	(1, X'377389028E10DA82', '2019-08-01 01:43:12', 5285136, '1ca97cbf8db55a2073364a214cca92f2cb2dc966', 544665, 533619, 533581, 0, 0, 0, 0, 32, 1, 0),
	(1, X'63BBD4FEC0366487', '2019-08-01 01:07:49', 5285145, '0', 542136, 533619, 533581, 0, 0, 0, 0, 52, 3, 0),
	(1, X'8DDC51E21A858EE9', '2019-08-01 01:43:12', 5285150, 'eaca12595abf2a5062cbe51973489a72775ab03f', 544727, 533619, 533581, 0, 0, 0, 0, 51, 2, 0);

@diosmosis
Copy link
Member Author

👍 didn't know that column existed

@tsteur
Copy link
Member

tsteur commented Nov 15, 2019

Me neither.

@diosmosis
Do not select conversion items that are deleted in segments + fix bug…
d01496d 
… in ecommerce item insert (do not fail whole insert if one duplicate primary key is found).
@diosmosis
Copy link
Member Author

@tsteur fixed the issue + added a test + fixed a bug in GoalManager (w/ it's own test).

@tsteur
Copy link
Member

tsteur commented Nov 17, 2019

LGTM @diosmosis . Will need to fix though some system tests first like the AutoSuggestApiTest: https://travis-ci.org/matomo-org/matomo/jobs/612291123#L925

@diosmosis diosmosis merged commit 6000931 into 3.x-dev Nov 18, 2019
@diosmosis diosmosis deleted the 4519-product-segments branch November 18, 2019 07:08
@mattab
Copy link
Member

mattab commented Nov 27, 2019

fyi Some tests still failing in https://travis-ci.org/matomo-org/matomo/jobs/616817416 @diosmosis

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@sgiehl sgiehl sgiehl left review comments

Assignees
No one assigned
Labels
Needs Review PRs that need a code review not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Projects
None yet
Milestone
3.13.0
Development

Successfully merging this pull request may close these issues.

Segment by Product Category, Product name, Product SKU, Product prices
4 participants
@diosmosis @sgiehl @tsteur @mattab