2019: Week 31 Solution

You can view our full solution workflow below and download it here!

Out full solution workflow.

1. General preparation: pivoting statuses.

The first stage towards all four outputs is to get all the [Date]s for a single [Order] onto the same row, giving each [Date] the appropriate [Status] field name.

To achieve this, simply use a Row-to-Columns pivot. Put [Status] onto ‘Pivoted Fields’, and put the MIN or MAX of [Date] onto the aggregation section We can take MIN or MAX as there is only one [Date] per [Status] per [Order], so MIN & MAX are one and the same.

Pivoting the statuses.

2. Get preparation: getting the time between dates.

The second stage of general preparation is to calculate the [Time to Send] and the [Time to Review from Sending Order]. To calculate these we can use the DATEDIFF() function to get the days between [Purchased], [Sent], and [Reviewed]

[Time to Send]	[Time to Review from Sending Order]
DATEDIFF('day', [Purchased], [Sent])	DATEDIFF('day', [Sent], [Reviewed])

We can now move onto our four separate outputs.

3. Output 1: Aggregate to get the average ‘Time to Send’.

Use an aggregation step which:

• GROUPS by [Customer];
• AVGs the [Time to Send].

That’s it!

4. Output 2: Aggregate to get the average ‘Time to Review’.

Very similar to the previous output; use another aggregation step which:

• GROUPS by [Customer];
• AVGs the [Time to Review from Sending Order].

The only extra step required afterwards is to exclude the rows which contain a NULL average [Time to Review from Sending Order].

5. Output 3: Filter to get the cities failing to send orders.

No fancy business here: just create a separate branch and then:

• Right-click on the [Sent] field.
• Select the ‘Null Values’ filter option.
• Check the ’Null values’ radio button under the ‘Keep Only’ section.
• Click ‘Done’.

Keeping only NULLs.

Now just remove any unnecessary fields and you’re done.

6. Output 4: Calculating the ‘send’ KPI success rate per city.

Unlike the other outputs, this one requires slightly more work. It could be doe in a single Aggregation step as you can do calculations within one, however it’s much easier to follow and maintain a workflow if you try and separate calculations out into clean steps. Our process is as follows.

a. Clean Step 1: flag orders that have met the 3-day send goal.

An IF-ELSE statement will suffice here. If the [Time to Send] is under 3 then return a value of 1, else return a value of 0. We use 1 & 0 here so we can sum the numbers up in the next aggregation step.

[Time to Send]

IF [Time to Send] <= 3     THEN 1     ELSE 0 END

b. Aggregate KPI values and order numbers per city.

Next add a new aggregation step where we:

• GROUP by City;
• SUM up [Time to Send];
• SUM up [Number of Rows…].

We then rename [Time to Send] to [Time to Send KPI] and [Number of Rows…] to [Orders per City] as there is one order per row.

Aggregating KPIs & renaming them.

c. Clean Step 2: calculate the % of KPI success per city.

Finally, with all the values prepared we can create our final field, [% of Orders meeting 3 Day KPI], by dividing the number of orders that met the 3-day KPI by the overall number of orders for the city.

[% of Orders meeting 3 Day KPI]

[Time to Send KPI] / [Orders per City]