Keeping Uber honest – Using Excel to keep track of earnings, points and so on

So i’ve had a bit of a fustrating issue lately, where my account is caught by a bug causing my Momentum card not to have correct status or points.

As part of digging into this, I was looking into how to easily verify this. None of the 3rd party solutions I could find had any capacity for this kind of audit – so had to build my own, which I ended up doing in Excel.

This post will walk you through how to do it, and covers the small bits of setup and learning to get you started.

Once you’ve set this up once, it almost becomes set and forget. You just need to add your data each week, and Excel does the rest.

If you already have intermediate or greater level expereicnce using Microsoft Excel, this should take no more than 5-10 minutes to get going. Beginners should put aside an extra 10-20 minutes for some reading and concentration.

After that, you should be able to add new data and get your analysis after a payout with no more than 2min of effort (and that includes the time to login to the partner website, and download the file).

 

In this post:

  • What this does
  • What this does not do
  • What it looks like
  • Before you begin
  • Import the macro
  • Update the points grid
  • Clean up your single CSV file (and this is the start point each time you have new data to import after the initial setup steps)
  • Copy over the clean data
  • Refresh the pivot table, and see the magic
  • Questions? Comments? Expressions of gratitude?

 

What this does (use cases)

With this spreadsheet, you can:

  1. Keep track of all your partner data by:
    • Importing your statements into a single sheet within an Excel workbook, and
    • Using Macros, cleans up the data for consistent recording and data analysis
  2. Keep track of your Uber Pro/Momentum status by:
    • Updating the Points Grid to match your market, and
    • Using VLOOKUP and MATCH formulas, correctly calculate the points that should have been earned for this activity.
  3. Analyse and verify your Uber status and payments by:
    • Using PivotTables to quickly present and view your data, and
    • Perform mathmatical checks to cross-check payment amounts and other information.

 

What this does not do (things you’ll need to be aware of)

Because Uber Partner data differs all around the world, and even some programs can differ, this spreadsheet template and method covered in this article does not cover:

  • Removing currencies other than $A:
    If currencies in your exports are expressed in something other than $A, then modify the macro code accordingly for its find and replace functions.
  • Handling columns beyond a specific set:
    The spreadsheet template and macros have been designed to handle a specific column header set from the Uber exports (Specficially: Driver name, Phone number, Email, Date/Time, Trip ID, Type, Fare, Service Fee, Tip, Quest Promotion, and Total). Again, if some of your exports have different columns, learn more about Excel, Macros, and Pivot Tables for how to ensure a consistent set of clean data and reporting.
  • Automatically adding the program points grid for your market:
    Again, this isn’t something Uber publishes as a data table or via API to be easily accessed. As noted in the Before you begin section, you’ll need to locate the points grid on Uber’s website, and add this information into the right place on the workbook.
  • Handling quarterly qualifying periods:
    Where partner programs (such as Uber Pro Driver) use a quarterly period for qualification, you can learn more about Pivot Tables and Grouping to render the data that way.
  • Tracking and reporting cancellation rates:
    I’m not aware of any data export available to partners which allows you to get access to information on your cancellation rate. And as I’m only a delivery partner and not a driver partner, I don’t know how you currently get access to this information. You could build a column to track this yourself, and learn more about Pivot Tables to present this data.
  • Tracking and reporting feedback:
    Again, as there’s no way I know of to get your ride-by-ride feedback data, reporting and analysing this in the spreadsheet isn’t already baked in. Again, you could build a column to track this yourself, and learn more about Pivot Tables to present this data.

 

What it looks like

Here’s some examples of the data analysis performed by the Excel workbook:

Example of analysis of my earnings, just with parts of the data hidden because not all of the information is appropriate to share

Example of analysis of my earnings (parts of the data hidden because not all of the information is appropriate to share).

Screen Shot 2019-12-25 at 6.31.42 pm

Example of the Momentum points earned by Month.

 

Before you begin

  1. Have access to a desktop computer, with Microsoft Office installed.
  2. Get a copy of the points grid for your market and reward program (Momentum or Pro) to your computer: For my market – the Momentum points grid is at http://t.uber.com/AUSMomentumPoints; or the Uber Pro grid is here.
  3. Download your recent driver statements into a Single folder on your computer; then consolidate all the CSV’s into a single CSV file (Instructions for Windows, Mac).
    (Depending on how many files you have to download, this might take a while, unless you can cook up a script to download them all)
  4. Download my template file. (Download link)
  5. Enable the Developer tab in Excel (Windows, Mac).

Import the macro

One of the key steps in making this process seamless is the macro that cleans up your weekly CSV file for future data imports.

  1. In Excel, go to the Developer tab.
  2. Follow these steps to create a Personal Macro Workbook.
  3. Still on the Developer tab, press Visual Basic.
  4. In the Project window, expand VBAProject (PERSONAL.XLSB) > Modules.
  5. Right-click on Module1, and select View Code.
  6. Copy the text below, and Paste the following into the window that now appears:
    Sub FormatUberCSVFile()
    '
    ' FormatUberCSVFile Macro
    ' Clean up an Uber Driver Partner CSV export for import into a master file
    '
    ' Keyboard Shortcut: Ctrl+Shift+U
    '
        Columns("D:D").Select
        Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
            Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
            TrailingMinusNumbers:=True
        Columns("G:K").Select
    
    'Remove currency symbols - Update what's inbetween the quotation marks for What to adjust the currency code to be removed from the file
        Selection.Replace What:="-A$", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, FormulaVersion:= _
            xlReplaceFormula2
        Selection.Replace What:="A$", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, FormulaVersion:= _
            xlReplaceFormula2
        
    'Format as currency
        Selection.Style = "Currency"
        
    'Select and sort data
        Range("A:P").Select
        ActiveWorkbook.Worksheets("statement_f0a85ede-d1df-b7ed-cc").Sort.SortFields. _
            Clear
        ActiveWorkbook.Worksheets("statement_f0a85ede-d1df-b7ed-cc").Sort.SortFields. _
            Add2 Key:=Range("M:M"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("statement_f0a85ede-d1df-b7ed-cc").Sort.SortFields. _
            Add2 Key:=Range("P:P"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("statement_f0a85ede-d1df-b7ed-cc").Sort
            .SetRange Range("A:P")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
  7. If your currency is shown in a value other than A$ within the CSV exports, make sure you update the macro at the right places so it removes these values.
  8. Press the Save button.
  9. Close the Visual Basic window and return back to your Excel workbook.

 

Update the points grid

Now the macro is done, we need to add the points values by hour for your market. Otherwise, it can’t correctly calculate your Uber Pro/Momentum points earned for each activity in your market.

  1. With the existing Excel Tracking file (Tracking.xslb), change to the Points Grid tab.
  2. Using the

 

Clean up your single CSV file

Now, a reminder – each time you have new single data file to import, just open the file and start at Step 4.

  1. Still in Excel, open the single CSV file you created earlier (Step 3 in before you begin).
  2. Clean up the columns, so they go from this:
    Screenshot of a CSV file, shown in Excel, that shows the results of multiple CSV files merged into a single file

    Before cleaning up the data in the single file

    to this:

    Screenshot of a CSV file, shown in Excel, that shows the results of multiple CSV files merged into a single file

    After cleaning up the file

    Specifically

    1. Make sure that all the excessive header rows (Driver name, Phone number, etc) are removed from the file.
    2. Ensure that each data column has the correct data in it. Due to the way Uber creates its export CSV files, it just doesn’t ensure each column has the same data from file to file – so some files may have the Quest Promotion column if you had a quest that week, or the tip column will be removed if you received no tips that week.
  3. Save this file, but keep it open.
  4. Important: Run the macro to clean up the split up the Date column by pressing Control + Shift + U.

    Macro didn’t run?
    Using the ribbon, go to Developer, Press Macros, select the macro PERSONAL.XLSB!FormatUberCSVFile, then press the Run button.
  5. Save the file.

 

Copy over the clean data

Congratulations, you’ve cleaned up all your data.

Copy it from the CSV file, and paste it into the Tracking tab within your existing Excel Tracking file (Tracking.xslb).

Refresh the pivot table, and see the magic

And now that the data is copied over:

  1. Click on the Analysis – Earnings sheet at the bottom of the Excel workbook.
  2. Click within the Pivot Table on the worksheet.
  3. From the Ribbon, select Pivot Table Analyse > Refresh > Refresh All.

And that’s it, you have your data neatly presented and analyised, ready for you to do as you wish.

 

Questions? Comments? Expressions of gratitude?

Ask in the comments below. I’ll do my best to reply as and when I can.

And if this made things a little easier for you, don’t hesitate to send a couple of dollars my way as a token of appreciation.

A word on using the phone to sell

You’re a small business. Potential customers call you by phone to ask questions before buying things.

How hard is it to teach your staff basic phone skills that, I don’t know, might help bring customers to your business so they give you their hard earned money!

There are a number of bike shops in Perth, who all either carry, or can order, the bike I am going to buy (in the Radioactive Yellow colour if anyone’s interested).

Of those I called this morning, four  of them lost a guaranteed sale because their staff displayed an embarrassing ignorance towards a customer, terrible phone manner, or communicated with the customer in a way that made them feel like an idiot.

Each time, there was an opportunity for recovery, but none of these staff recognised or took it.

And you wonder why your customers are buying online? The phone manner of your staff is likely a big driver for it.

These stores lost an opportunity for $650 in immediate revenue today, in addition to ongoing income and loyalty from parts, repairs and accessories.

Now that might not seem a lot, especially when these stores have bikes worth $2,000-$4,000 plus sitting on the shop floor. But bikes of that value don’t sell every day, nor do the expensive parts that go with them. So constant sales of smaller price ticket items, like a $550 bike and the accessories the customer initially buys, are what keeps the doors open, the wages paid, money in the bank to buy new stock, and the tax man off your back.

Each and every phone call you receive is an opportunity – one to build your brand reputation, recognition, and most importantly convert callers into money in your bank account.

If you have a phone for your business, make sure you, and your staff, learn how to use it properly.

Some simple tips to do this:

  1. Treat each customer as knowledgeable, and when they don’t know a given answer, empower them the information in a respectful way that will help them answer or set them on the right path.
  2. Don’t have people to answer the phone who aren’t trained to sell, or understand your products.
  3. Don’t use cordless phones, unless they have an actual Hold option (preferably with music so the customer doesn’t think the line has gone dead). Nobody wants to hear Assistant X talking to Assistant Y to get an answer about something, or inane workplace chatter, whilst you’re waiting for information.
  4. Get your friends to visit your home one day, have them make some test calls to your stores. Do it on a speaker phone so you can observe and listen to what your staff say and sound like when talking to regular customers, so you can give your employees specific and actionable feedback to coach them, and drive them to do better.

And please, if one of your staff persistently displays an awful attitude or phone manner – ban them from answering the phones. They are going to be one of the key reasons that your phone calls aren’t converting into sales.

And credit where credit is due – to two Bike Force stores – Gerrard from their Subiaco franchisee, and Joondalup (can’t remember the staff member’s name there sadly. The individual staff who answered the phones at these locations were professional and personable. They knew and understood that phone calls mean sales, and took the time to answer my reasonable questions. If Joondalup wasn’t closer to me, Gerrard and Subiaco would have got the sale.