Generating Custom Reports

    Build a custom reporting tool using the Stripe API.

    The Dashboard provides access to real-time information about your Stripe account’s activity, some of which can be downloaded as reports in CSV format. Sometimes there is need for more specialized reporting, beyond what is available in the Dashboard.

    You can create your own reporting tool and access all of your account information using the API. This provides far more flexibility on what information is included, as well as how, and when, these reports are created.

    The first part of this recipe creates a payments report using the API, similar to what’s available in the Dashboard. The remainder of this recipe builds upon this to create a few example reports. All of the reports created are in CSV format and can be opened in any spreadsheet app.

    Creating a payments report

    The Dashboard itself is built on top of the API, and each built-in report is actually an API request that’s performed in the background. You can replicate and build upon the functionality of these reports by creating your own API requests instead. To start, the following API request retrieves a list of 50 Charge objects.

    curl https://api.stripe.com/v1/charges?limit=50 \
       -u sk_test_BQokikJOvBiI2HlWgH4olfQ2: \
       -G 
    

    The charges are found inside the data attribute and are returned in date descending order, so the most recent charge is returned first.

    "data": [
      {
        "id": "ch_17xIdKFWjDUFYSdS0WngworI",
        "object": "charge",
        "amount": 9900,
        "amount_refunded": 0,
        "application_fee": null,
        "balance_transaction": "txn_17xIdKFWjDUFYSdSP5nLYiyI",
        "captured": true,
        "created": 1460389015,
        ...
      }
    ]

    The information returned is the basis of a payments report, so the next step is to format the response into a structure that works better for reporting purposes. You can do this by expanding the API request to loop through each of the retrieved charges, identify the information you want to include, and then output it into a suitable format. This example PHP script does this by performing the following actions:

    1. Creates a CSV file containing necessary header information.
    2. Makes an API request to retrieve the 50 most recent charges.
    3. Loops through each Charge object to identify its id, description, created date, amount and currency.
    4. Saves the charge information to the CSV file.
    <?php
    
    // If you're using Composer, use Composer's autoload
    require __DIR__ . '/vendor/autoload.php';
    
    // Set your secret key: remember to change this to your live secret key in production
    // See your keys here https://dashboard.stripe.com/account/apikeys
    \Stripe\Stripe::setApiKey('sk_test_BQokikJOvBiI2HlWgH4olfQ2');
    
    // Create/open CSV
    $csv = fopen('payments.csv', 'w');
    
    // Create the headers for the CSV file, then write them to the CSV
    $headers = array(
                 'ID',
                 'Description',
                 'Creation Date',
                 'Amount',
                 'Currency'
               );
    
    fputcsv($csv, $headers);
    
    // Retrieve a list of 50 most recent charges
    $charges = \Stripe\Charge::all(array(
                 'limit' => 50
               ));
    
    // Loop through each charge
    foreach ($charges->data as $charge) {
    
        // Get the required charge information and assign to variables
        $id = $charge->id;
        $description = $charge->description;
        $created = gmdate('Y-m-d H:i', $charge->created); // Format the time
        $amount = $charge->amount/100; // Convert amount from cents to dollars
        $currency = $charge->currency;
    
        // Create an array of the above charge information
        $report = array(
                    $id,
                    $description,
                    $created,
                    $amount,
                    $currency
                  );
    
        // Write this information into the CSV file
        fputcsv($csv, $report);
    }
    
    // Close the CSV
    fclose($csv);
    ?>

    The resulting CSV file is a basic version of the payments report that’s available through the Dashboard. With a little more development, it can replace the Dashboard’s report completely, allowing you full control over how the data is interpreted and formatted.

    Suggested improvements

    There are any number of changes you can make to improve upon this concept:

    • Make use of the Charge object’s status parameter to only report on successful charges
    • Include a timestamp within the CSV’s filename so an existing report is not overwritten
    • Instead of saving the charge information to a CSV file, store it inside a database or pass it into a dedicated reporting system

    Retrieval limits

    The maximum number of objects that can be returned when using the limit parameter is 100. For instances where you may need to retrieve more than this, paginate the request by passing the final charge retrieved by the API request as the starting_after parameter in a subsequent request to retrieve the next list of charges.

    $startcharge = 'ch_17xIXOFWjDUFYSdSaEfrORic';
    
    // Retrieve a list of 50 charges, starting after this charge
    $charges = \Stripe\Charge::all(array(
                 'limit' => 50,
                 'starting_after' => $startcharge
               ));

    As your reporting needs grow, your reporting tool may need to regularly paginate results, resulting in multiple API requests. We recommend that you store any returned data on your side so that your API requests only need to fetch new or updated data, preventing any performance issues.

    Customer payments report

    The API request to retrieve a list of objects can include some additional filters to refine what data is returned. You can edit the above API request and provide a Customer object ID when listing charges, which will only return charges associated to that customer.

    // Customer ID
    $customer = 'cus_8Dk1wM5p10wq0e';
    
    // List all charges for that customer (limited to 50)
    $charges = \Stripe\Charge::all(array(
                 'limit' => 50,
                 'customer' => $customer
               ));

    Debit and prepaid card payments report

    It can be useful to understand the types of cards that your customers use when making a payment. You can determine what type of card was used to create a charge by looking at the funding parameter of the source hash, located within each charge. The PHP script can be modified so that the foreach loop includes an if clause to only report on charges created using either a debit or prepaid card.

    $funding = $charge->source->funding;
    if(($funding == "debit") || ($funding == "prepaid")) {
      ...
    }

    Refunds report

    A payments report is useful to keep track of charges that are coming into your Stripe account. To keep track of refunds, a custom report can be created that lists all refunds made within a certain date range. This can include information about the related charge that isn’t included in the Dashboard’s balance report.

    <?php
    
    // If you're using Composer, use Composer's autoload
    require __DIR__ . '/vendor/autoload.php';
    
    // Set your secret key: remember to change this to your live secret key in production
    // See your keys here https://dashboard.stripe.com/account/apikeys
    \Stripe\Stripe::setApiKey('sk_test_BQokikJOvBiI2HlWgH4olfQ2');
    
    // Create/open CSV
    $csv = fopen('refunds.csv', 'w');
    
    $headers = array(
                 'Refund ID',
                 'Refund Date',
                 'Refund Amount',
                 'Charge ID',
                 'Charge Creation Date',
                 'Charge Description'
               );
    
    fputcsv($csv, $headers);
    
    // Retrieve a list of 50 most recent refunds
    $refunds = \Stripe\Refund::all(array(
                 'limit' => 50
               ));
    
    // Loop through each refund
    foreach ($refunds->data as $refund) {
    
      // If the refund was created before this date, proceed
      if ($refund->created <= strtotime('1 April 2016')) {
    
        $id = $refund->id;
        $created = gmdate('Y-m-d H:i', $refund->created); // Format the time
        $amount = $refund->amount;
        $chargeid = $refund->charge;
    
        // Perform a charge lookup using the charge ID
        $charge = \Stripe\Charge::retrieve($chargeid);
        $chargecreated = gmdate('Y-m-d H:i', $charge->created);
        $chargedescription = $charge->description;
    
        // Create an array of the refund information, including any charge information
        $re = array(
                $id,
                $created,
                $amount,
                $chargeid,
                $chargecreated,
                $chargedescription
              );
    
        // Write this into the CSV file
        fputcsv($csv, $re);
      }
    
    }
    
    // Close the CSV
    fclose($csv);
    ?>

    An API request retrieves a list of Refund objects, which is then looped through by the reporting tool. The returned data is filtered, based upon the refund’s creation date, and another API request is performed during each loop to retrieve the information of the related charge. All of this information is then brought together to provide a report on refunds that have been made within a specific date range, along with additional information about the charge they relate to.

    Further uses of custom reports

    The focus of the examples in this recipe is on payment and refund reporting. You can create reports using similar API requests for any other data type in your Stripe account, including:

    Connect applications can also retrieve connected account activity from the API. Express and Custom account platforms can create user-facing reports that provides detailed account information to their users. You can find out more about providing analytical information to your users in our recipe for building an analytics service.