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

Live.getLastVisitsDetails TSV export can have thousands of columns #14371

Closed
mattab opened this issue Apr 25, 2019 · 3 comments
Closed

Live.getLastVisitsDetails TSV export can have thousands of columns #14371

mattab opened this issue Apr 25, 2019 · 3 comments
Labels
wontfix If you can reproduce this issue, please reopen the issue or create a new one describing it.
Milestone
Backlog (Help wan...

Comments

@mattab
Copy link
Member

mattab commented Apr 25, 2019

Live.getLastVisitsDetails TSV export can have thousands of columns (for example when some users have 500 actions in the visit, and each action has 10 custom dimensions there can quickly be 5000+ columns.) which is problematic because Libreoffice calc or Excel will only show so many columns (there is a limit around 2000 or so).

This limitation wouldn't be a problem so much, but the issue is that in the data export, the "Visitor columns" such as

Reproduce

  • URL in TSV (and equivalent in XML)

  • What we get: most of the visitor columns (full list below) are hidden, because actionDetails list of actions appears early in the list of visit attributes, and it can have hundreds/thousands of columns.

  • Expected instead: the visitor details to be all visible, and the actionDetails to be truncated by spreadsheet when the export has too many columns

Click to see Dimensions cut off and not visible are these ones after `actionDetails` 


		<goalConversions>0</goalConversions>
		<siteCurrency>USD</siteCurrency>
		<siteCurrencySymbol>$</siteCurrencySymbol>
		<serverDate>2019-04-25</serverDate>
		<visitServerHour>5</visitServerHour>
		<lastActionTimestamp>1556169886</lastActionTimestamp>
		<lastActionDateTime>2019-04-25 05:24:46</lastActionDateTime>
		<siteName>Divezone</siteName>
		<serverTimestamp>1556169886</serverTimestamp>
		<firstActionTimestamp>1556169886</firstActionTimestamp>
		<serverTimePretty>07:24:46</serverTimePretty>
		<serverDatePretty>Thursday, April 25, 2019</serverDatePretty>
		<serverDatePrettyFirstAction>Thursday, April 25, 2019</serverDatePrettyFirstAction>
		<serverTimePrettyFirstAction>07:24:46</serverTimePrettyFirstAction>
		<userId />
		<visitorType>returning</visitorType>
		<visitorTypeIcon>plugins/Live/images/returningVisitor.png</visitorTypeIcon>
		<visitConverted>0</visitConverted>
		<visitConvertedIcon />
		<visitCount>2</visitCount>
		<visitEcommerceStatus>none</visitEcommerceStatus>
		<visitEcommerceStatusIcon />
		<daysSinceFirstVisit>463</daysSinceFirstVisit>
		<daysSinceLastEcommerceOrder>0</daysSinceLastEcommerceOrder>
		<visitDuration>0</visitDuration>
		<visitDurationPretty>0s</visitDurationPretty>
		<searches>0</searches>
		<actions>1</actions>
		<interactions>1</interactions>
		<referrerType>search</referrerType>
		<referrerTypeName>Search Engines</referrerTypeName>
		<referrerName>Google</referrerName>
		<referrerKeyword>Keyword not defined</referrerKeyword>
		<referrerKeywordPosition>2</referrerKeywordPosition>
		<referrerUrl>https://www.google.de/url?sa=t&amp;rct=j&amp;q=&amp;esrc=s&amp;source=web&amp;cd=2&amp;ved=0ahUKEwiUwMGbj-LbAhXP2qQKHRTpCfYQFgg-MAE&amp;url=https://divezone.net/diving/philippines&amp;usg=AOvVaw15iOAo7a0OvKQIY-1YBM-p</referrerUrl>
		<referrerSearchEngineUrl>http://google.com</referrerSearchEngineUrl>
		<referrerSearchEngineIcon>plugins/Morpheus/icons/dist/searchEngines/google.com.png</referrerSearchEngineIcon>
		<referrerSocialNetworkUrl />
		<referrerSocialNetworkIcon />
		<languageCode />
		<language>Unknown</language>
		<deviceType>Desktop</deviceType>
		<deviceTypeIcon>plugins/Morpheus/icons/dist/devices/desktop.png</deviceTypeIcon>
		<deviceBrand>Unknown</deviceBrand>
		<deviceModel>Generic Desktop</deviceModel>
		<operatingSystem>Windows 7</operatingSystem>
		<operatingSystemName>Windows</operatingSystemName>
		<operatingSystemIcon>plugins/Morpheus/icons/dist/os/WIN.png</operatingSystemIcon>
		<operatingSystemCode>WIN</operatingSystemCode>
		<operatingSystemVersion>7</operatingSystemVersion>
		<browserFamily>Trident</browserFamily>
		<browserFamilyDescription>Trident (IE)</browserFamilyDescription>
		<browser>Internet Explorer 11.0</browser>
		<browserName>Internet Explorer</browserName>
		<browserIcon>plugins/Morpheus/icons/dist/browsers/IE.png</browserIcon>
		<browserCode>IE</browserCode>
		<browserVersion>11.0</browserVersion>
		<totalEcommerceRevenue>0.00</totalEcommerceRevenue>
		<totalEcommerceConversions>0</totalEcommerceConversions>
		<totalEcommerceItems>0</totalEcommerceItems>
		<totalAbandonedCartsRevenue>0.00</totalAbandonedCartsRevenue>
		<totalAbandonedCarts>0</totalAbandonedCarts>
		<totalAbandonedCartsItems>0</totalAbandonedCartsItems>
		<events>0</events>
		<continent>Europe</continent>
		<continentCode>eur</continentCode>
		<country>Denmark</country>
		<countryCode>dk</countryCode>
		<countryFlag>plugins/Morpheus/icons/dist/flags/dk.png</countryFlag>
		<region>Syddanmark</region>
		<regionCode>83</regionCode>
		<city>Sønderborg</city>
		<location>Sønderborg, Syddanmark, Denmark</location>
		<latitude>54.909000</latitude>
		<longitude>9.807000</longitude>
		<visitLocalTime>13:18:38</visitLocalTime>
		<visitLocalHour>13</visitLocalHour>
		<daysSinceLastVisit>309</daysSinceLastVisit>
		<customVariables>
			<row>
				<customVariableName1>type</customVariableName1>
				<customVariableValue1>diving</customVariableValue1>
			</row>
			<row>
				<customVariableName2>location</customVariableName2>
				<customVariableValue2>Philippines</customVariableValue2>
			</row>
			<row>
				<customVariableName5>diving-rating</customVariableName5>
				<customVariableValue5>3.7</customVariableValue5>
			</row>
		</customVariables>
		<resolution>1280x1024</resolution>
		<plugins>cookie, flash, java</plugins>
		<pluginsIcons>
			<row>
				<pluginIcon>plugins/Morpheus/icons/dist/plugins/cookie.png</pluginIcon>
				<pluginName>cookie</pluginName>
			</row>
			<row>
				<pluginIcon>plugins/Morpheus/icons/dist/plugins/flash.png</pluginIcon>
				<pluginName>flash</pluginName>
			</row>
			<row>
				<pluginIcon>plugins/Morpheus/icons/dist/plugins/java.png</pluginIcon>
				<pluginName>java</pluginName>
			</row>
		</pluginsIcons>
		<experiments>
			<row>
				<idexperiment>9</idexperiment>
				<name>AffiliateLinkEffectiveness</name>
				<variation>
						<idvariation>0</idvariation>
						<name>Original</name>
				</variation>
			</row>
			<row>
				<idexperiment>10</idexperiment>
				<name>IDivedThereLinkMove</name>
				<variation>
						<idvariation>0</idvariation>
						<name>Original</name>
				</variation>
			</row>
			<row>
				<idexperiment>13</idexperiment>
				<name>DivingPageTitles</name>
				<variation>
						<idvariation>27</idvariation>
						<name>Plain</name>
				</variation>
			</row>
		</experiments>
		<dimension1>guest</dimension1>
		<formConversions>0</formConversions>
		<sessionReplayUrl />
		<campaignId />
		<campaignContent />
		<campaignKeyword />
		<campaignMedium />
		<campaignName />
		<campaignSource />

To solve this problem maybe we could move actionDetails to the last position in each visit in the API output?
@mattab mattab added the Bug For errors / faults / flaws / inconsistencies etc. label Apr 25, 2019
@mattab
Copy link
Member Author

mattab commented Apr 25, 2019

This could also be worked around by manually adding the hideColumns parameter for example to the following value:

&hideColumns=dimension1,dimension2,dimension3,dimension4,dimension5,dimension6,dimension7,dimension8,dimension9,dimension10,dimension11,dimension12,dimension13,pageIdAction,idpageview,serverTimePretty,pageId,eventCategory,eventAction,timeSpent,interactionPosition,timestamp,icon

But currently hideColumns is not applied recursively (will be done in Matomo 4 in #11115)

@fdellwing
Copy link
Contributor

which is problematic because Libreoffice calc or Excel will only show so many columns (there is a limit around 2000 or so)

The limit for Excel is 1,048,576 rows by 16,384 columns.
For LO it is currently 1,048,576 rows by 1,024 (!!) columns.

LO is actively working on increasing this limit. With LO 6.3.0 the column count will be dynamic and if everything works fine, the limit will be increased afterwards.

Keep an eye on https://bugs.documentfoundation.org/show_bug.cgi?id=50916 for more information in the future.

@tsteur tsteur added Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. and removed Bug For errors / faults / flaws / inconsistencies etc. labels Apr 25, 2019
@tsteur tsteur added this to the Backlog (Help wanted) milestone Apr 25, 2019
@tsteur
Copy link
Member

tsteur commented Apr 25, 2019

adding hideColumns=actionDetails works for me. Closing this issue as it seems it'll be fixed in LO eventually and by the time this becomes a priority it will be fixed in LO very likely.

@tsteur tsteur closed this as completed Apr 25, 2019
@tsteur tsteur added wontfix If you can reproduce this issue, please reopen the issue or create a new one describing it. and removed Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. labels Apr 25, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
wontfix If you can reproduce this issue, please reopen the issue or create a new one describing it.
Projects
None yet
Milestone
Backlog (Help wanted)
Development

No branches or pull requests
3 participants
@tsteur @mattab @fdellwing