Rewriting Dates in Sitecore Analytics for Demo Data

Fri Sep 09 2016

Hello everyone! It's been awhile since I've blogged so I thought I'd post this useful bit before next week's MVP Summit and Symposium. Two plugs first though!

Core Sampler

If you haven't yet, be sure to check out the new Sitecore podcast from Derek Dysart, Core Sampler. Episode 1 is everyone's favorite Sitecore junkie, Mike Reynolds, and Episode 2 will be someone else very familiar...

#SitecoreSymGives

The Sitecore MVPs are leading an effort to help the Sitecore Community give back to the people of Louisiana, who are so graciously hosting us next week. Many many people not far from New Orleans have experienced horrific flooding recently, and are facing a long recovery.

You can contribute to the effort through a financial donation or by volunteering the day before Symposium.

And with that, let's get on to the post.

Adjusting Dates in your xDB Demo Data

In preparation for demoing Active Commerce at Symposium, we are busy creating JMeter scripts which simulate various user behaviors, conversions, traffic sources, etc. We are really hoping to show the power of the Sitecore marketing and analytics tools for e-commerce sites powered by our product. I won't get into the JMeter scripts here -- that's perhaps a topic for another day. But the result of running these scripts is a huge number of visits, but all on the same day. Not very exemplary of real site traffic. So I set about creating a PowerShell script that allowed us to "rewrite history" and shape the traffic over a given time period.

The script can be found below. It uses a string of integers to represent the traffic shape, and updates the mongodb analytics data in place. You'll obviously need to rebuild your reporting database (and maybe your analytics indexes??) after running this. It is dependent on the Mdbc (MongoDB Cmdlets for PowerShell) Module.

If you are looking to generate dummy or demo data for use with Sitecore Analytics, this may be useful for you. Enjoy!

	$ErrorActionPreference = "Stop"
	import-module mdbc

	#
	# Configuration Values
	#

	# The date you created the traffic which you wish to rewrite. Likely today.
	$rewriteDate = Get-Date "9/8/2016"

	# The start and end dates for the range of days to which the traffic should be rewritten.
	$startDate = Get-Date "6/24/2016"
	$endDate = Get-Date "9/7/2016"

	# A string describing the distribution of visits over those days. Each integer is a 'slot' of days, higher value = more visits.
	# The number of integers in this string must be divisible into the total number of days.
	$trafficShape = "111233222311111"

	#
	# Functions
	#

	Function Adjust-DateTime($DateTime, $ToDate) {
	return Get-Date -Year $ToDate.Year -Month $ToDate.Month -Day $ToDate.Day -Hour $DateTime.Hour -Minute $DateTime.Minute -Second $DateTime.Second
	}


	#
	# THE SCRIPT
	#

	# Determine total days, slots, days per slot
	$totalDays = ($endDate - $startDate).Days
	$trafficSlots = $trafficShape.Length
	if ($totalDays % $trafficSlots -ne 0) {
	Write-Host "Total days $totalDays not evenly divisble by $trafficSlots. Total days must be evenly divisble by traffic shape slots."
	Exit
	}
	$daysPerSlot = $totalDays/$trafficSlots
	Write-Host "Days per traffic slot: $daysPerSlot"

	# Connect to the mongo db and find all the visits from the rewrite day
	Write-Host "Finding visits from $rewriteDate..."
	Connect-Mdbc . ActiveCommerceDemo_analytics Interactions
	$rewriteDateEnd = $rewriteDate + (New-TimeSpan -Hours 24)
	$visits = Get-MdbcData (New-MdbcQuery -And (New-MdbcQuery StartDateTime -gte $rewriteDate), (New-MdbcQuery StartDateTime -lt $rewriteDateEnd))
	$rewriteCount = $visits.Count
	Write-Host "$rewriteCount visits found"

	# Determine the number of visits that will be allocated for each "point" in the traffic shape
	$trafficSlots = $trafficShape.ToCharArray() \| % { [Int32]::Parse($_) }
	$totalPoints = ($trafficSlots \| Measure-Object -Sum).Sum
	$orphanVisits = $rewriteCount % $totalPoints
	$visitsPerPoint = ($rewriteCount-$orphanVisits) / $totalPoints
	Write-Host "Will allocate $visitsPerPoint visits per point in traffic shape."
	Write-Host "This will leave $orphanVisits visits, we will try to distribute them."

	# Put all rewrite visits into a queue
	$visitQueue = New-Object System.Collections.Queue
	$visits \| % { $visitQueue.Enqueue($_) }

	# Loops through all the slots
	$currentDate = $startDate
	$slotCount = 1
	foreach ($slot in $trafficSlots) {
	# Determine the number of visits in this slot
	$slotVisits = $slot * $visitsPerPoint
	if ($orphanVisits -gt 0) {
	$slotVisits++
	$orphanVisits--
	}
	$slotOrphanVisits = $slotVisits % $daysPerSlot
	$slotVisitsPerDay = ($slotVisits-$slotOrphanVisits) / $daysPerSlot
	Write-Host "Slot $slotCount ($slot points) has $slotVisits visits over $daysPerSlot days"
	Write-Host "That's $slotVisitsPerDay visits per day with $slotOrphanVisits visits left over. We'll try to distribute them too."

	# Loop through the days in this slot
	for ($i = 0; $i -lt $daysPerSlot; $i++) {
	# Determine the number of visits on this day
	$visitsThisDay = $slotVisitsPerDay
	if ($slotOrphanVisits -gt 0) {
	$visitsThisDay++
	$slotOrphanVisits--
	}
	Write-Host "Moving $visitsThisDay to $currentDate"

	# Loop through the visits on this day
	for ($j = 0; $j -lt $visitsThisDay; $j++) {
	# Grab a visit from the queue and adjust its dates
	$visit = $visitQueue.Dequeue();
	$visitStart = Adjust-DateTime -DateTime $visit.StartDateTime -ToDate $currentDate
	$visitEnd = Adjust-DateTime -DateTime $visit.EndDateTime -ToDate $currentDate
	$visitSave = Adjust-DateTime -DateTime $visit.SaveDateTime -ToDate $currentDate
	foreach ($page in $visit.Pages) {
	$page.DateTime = Adjust-DateTime -DateTime $page.DateTime -ToDate $currentDate
	}
	$visit._id \| Update-MdbcData (New-MdbcUpdate @{
	StartDateTime = $visitStart
	EndDateTime = $visitEnd
	SaveDateTime = $visitSave
	Pages = $visit.Pages
	})
	}
	$currentDate = $currentDate + (New-TimeSpan -Days 1)
	}
	$slotCount++
	}
	$leftoverVisits = $visitQueue.Count
	Write-Host "There were $leftoverVisits visits left. This should be 0."

view raw rewrite-dates.ps1 hosted with ❤ by GitHub

	Days per traffic slot: 5
	Finding visits from 09/08/2016 00:00:00...
	2232 visits found
	Will allocate 89 visits per point in traffic shape.
	This will leave 7 visits, we will try to distribute them.
	Slot 1 (1 points) has 90 visits over 5 days
	That's 18 visits per day with 0 visits left over. We'll try to distribute them too.
	Moving 18 to 06/24/2016 00:00:00
	Moving 18 to 06/25/2016 00:00:00
	Moving 18 to 06/26/2016 00:00:00
	Moving 18 to 06/27/2016 00:00:00
	Moving 18 to 06/28/2016 00:00:00
	Slot 2 (1 points) has 90 visits over 5 days
	That's 18 visits per day with 0 visits left over. We'll try to distribute them too.
	Moving 18 to 06/29/2016 00:00:00
	Moving 18 to 06/30/2016 00:00:00
	Moving 18 to 07/01/2016 00:00:00
	Moving 18 to 07/02/2016 00:00:00
	Moving 18 to 07/03/2016 00:00:00
	Slot 3 (1 points) has 90 visits over 5 days
	That's 18 visits per day with 0 visits left over. We'll try to distribute them too.
	Moving 18 to 07/04/2016 00:00:00
	Moving 18 to 07/05/2016 00:00:00
	Moving 18 to 07/06/2016 00:00:00
	Moving 18 to 07/07/2016 00:00:00
	Moving 18 to 07/08/2016 00:00:00
	Slot 4 (2 points) has 179 visits over 5 days
	That's 35 visits per day with 4 visits left over. We'll try to distribute them too.
	Moving 36 to 07/09/2016 00:00:00
	Moving 36 to 07/10/2016 00:00:00
	Moving 36 to 07/11/2016 00:00:00
	Moving 36 to 07/12/2016 00:00:00
	Moving 35 to 07/13/2016 00:00:00
	Slot 5 (3 points) has 268 visits over 5 days
	That's 53 visits per day with 3 visits left over. We'll try to distribute them too.
	Moving 54 to 07/14/2016 00:00:00
	Moving 54 to 07/15/2016 00:00:00
	Moving 54 to 07/16/2016 00:00:00
	Moving 53 to 07/17/2016 00:00:00
	Moving 53 to 07/18/2016 00:00:00
	Slot 6 (3 points) has 268 visits over 5 days
	That's 53 visits per day with 3 visits left over. We'll try to distribute them too.
	Moving 54 to 07/19/2016 00:00:00
	Moving 54 to 07/20/2016 00:00:00
	Moving 54 to 07/21/2016 00:00:00
	Moving 53 to 07/22/2016 00:00:00
	Moving 53 to 07/23/2016 00:00:00
	Slot 7 (2 points) has 179 visits over 5 days
	That's 35 visits per day with 4 visits left over. We'll try to distribute them too.
	Moving 36 to 07/24/2016 00:00:00
	Moving 36 to 07/25/2016 00:00:00
	Moving 36 to 07/26/2016 00:00:00
	Moving 36 to 07/27/2016 00:00:00
	Moving 35 to 07/28/2016 00:00:00
	Slot 8 (2 points) has 178 visits over 5 days
	That's 35 visits per day with 3 visits left over. We'll try to distribute them too.
	Moving 36 to 07/29/2016 00:00:00
	Moving 36 to 07/30/2016 00:00:00
	Moving 36 to 07/31/2016 00:00:00
	Moving 35 to 08/01/2016 00:00:00
	Moving 35 to 08/02/2016 00:00:00
	Slot 9 (2 points) has 178 visits over 5 days
	That's 35 visits per day with 3 visits left over. We'll try to distribute them too.
	Moving 36 to 08/03/2016 00:00:00
	Moving 36 to 08/04/2016 00:00:00
	Moving 36 to 08/05/2016 00:00:00
	Moving 35 to 08/06/2016 00:00:00
	Moving 35 to 08/07/2016 00:00:00
	Slot 10 (3 points) has 267 visits over 5 days
	That's 53 visits per day with 2 visits left over. We'll try to distribute them too.
	Moving 54 to 08/08/2016 00:00:00
	Moving 54 to 08/09/2016 00:00:00
	Moving 53 to 08/10/2016 00:00:00
	Moving 53 to 08/11/2016 00:00:00
	Moving 53 to 08/12/2016 00:00:00
	Slot 11 (1 points) has 89 visits over 5 days
	That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
	Moving 18 to 08/13/2016 00:00:00
	Moving 18 to 08/14/2016 00:00:00
	Moving 18 to 08/15/2016 00:00:00
	Moving 18 to 08/16/2016 00:00:00
	Moving 17 to 08/17/2016 00:00:00
	Slot 12 (1 points) has 89 visits over 5 days
	That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
	Moving 18 to 08/18/2016 00:00:00
	Moving 18 to 08/19/2016 00:00:00
	Moving 18 to 08/20/2016 00:00:00
	Moving 18 to 08/21/2016 00:00:00
	Moving 17 to 08/22/2016 00:00:00
	Slot 13 (1 points) has 89 visits over 5 days
	That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
	Moving 18 to 08/23/2016 00:00:00
	Moving 18 to 08/24/2016 00:00:00
	Moving 18 to 08/25/2016 00:00:00
	Moving 18 to 08/26/2016 00:00:00
	Moving 17 to 08/27/2016 00:00:00
	Slot 14 (1 points) has 89 visits over 5 days
	That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
	Moving 18 to 08/28/2016 00:00:00
	Moving 18 to 08/29/2016 00:00:00
	Moving 18 to 08/30/2016 00:00:00
	Moving 18 to 08/31/2016 00:00:00
	Moving 17 to 09/01/2016 00:00:00
	Slot 15 (1 points) has 89 visits over 5 days
	That's 17 visits per day with 4 visits left over. We'll try to distribute them too.
	Moving 18 to 09/02/2016 00:00:00
	Moving 18 to 09/03/2016 00:00:00
	Moving 18 to 09/04/2016 00:00:00
	Moving 18 to 09/05/2016 00:00:00
	Moving 17 to 09/06/2016 00:00:00
	There were 0 visits left. This should be 0.